Типы данных

В ячейки рабочего листа можно вводить данные следующих типов: числовые, символьные, формулы и функции, а также даты.

Число в Excel может состоять только из следующих символов: цифры от 0 до 9, +, -, (,), /, $, %, (.), Е, е. Запятая в числе интерпретируется как разделитель десятичных разрядов. Символ разделителя может быть изменен в приложении Язык и стандарты панели управления Windows.

Лекция14

1. Работа с формулами в EXCEL.

2. Ссылки.

3. Мастер функции.

4. Ошибки при работе с формулами.

5. Списки и способы их обработки.

6. Фильтрация данных.

Работа с формулами в excel

Формула – это краткая запись некоторой последовательности действий, приводящих к конкретному результату. Формула может содержать не более 1024 символов. Структуру и порядок элементов в формуле определяет ее синтаксис.

Все формулы в Excel должны начинаться со знака равенства. Без этого знака все введенные символы рассматриваются как текст или число, если они образуют правильное числовое значение.

Формулы содержат вычисляемые элементы (операнды) и операторы. Операндами могут быть константы, ссылки или диапазоны ссылок, заголовки, имена, функции.

В Excel включено 4 вида операторов: арифметические, текстовые, операторы сравнения, адресные операторы.

К арифметическим операторам относятся: +, -, *, /, %, ^.

Операторы сравнения используются для обозначения операций сравнения двух чисел. К операторам сравнения относятся: =, >, <, >=, <=, <>. Логические формулы могут содержать указанные операторы сравнения, а также специальные логические операторы:

#NOT#-логическое отрицание «НЕ»;

#AND#-логическое «И»

#OR#-логическое «ИЛИ».

Логические формулы определяют истинно или ложно выражение.

Адресные операторы объединяют диапазоны ячеек для осуществления вычислений.

Приоритет выполнения операций:

• операторы ссылок (адресные) «:», «,», « »;

• знаковый минус «-»;

• вычисление процента %;

• арифметические ^, *, /, +, -;

• текстовый оператор &;

• операторы сравнений =, <, >, <=, >=, <>.

После ввода формулы в ячейку рабочего листа на экране в окне рабочего листа в ячейку выводится результат вычисления.

Ссылки

Ссылка является идентификатором ячейки или группы ячеек в книге. При создании формул, содержащих ссылки на ячейки, формула связывается с ячейками книги. Значение формулы зависит от содержимого ячеек, на которые указывают ссылки, и оно изменяется при изменении содержимого этих ячеек. С помощью ссылок в формулах можно использовать данные, находящиеся в различных местах листа, или использовать значение одной и той же ячейки в нескольких формулах. Кроме того, можно ссылаться на ячейки, находящиеся на других листах книги, или в другой книге, или даже на данные другого приложения. Ссылки на ячейки других книг называются внешними. Ссылки на данные других приложений называются удаленными.

В Excel существуют три типа ссылок: относительные, абсолютные, смешанные.

Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула. При перемещении формулы относительная ссылка изменяется, ориентируясь на ту позицию, в которую переносится формула. Например, если в клетке С1 записана формула

=А1+В1, то при копировании ее в клетку С2 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1 – =В1+С1.

Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки.

Смешанные ссылки – это ссылки, являющиеся комбинацией относительных и абсолютных ссылок. Например, фиксированный столбец и относительная строка: $D7.

Ссылки на ячейки других листов книги имеют следующий формат:

<имя раб.листа>!ссылка на ячейку, например, Лист2!А1:А10.

Excel позволяет ссылаться на диапазон ячеек нескольких рабочих листов. Такая ссылка называется объемной. Например, Лист1:Лист5!$А$1:$D$3.

Ссылки на ячейки других книг имеют следующий формат:

[имя книги]<имя листа>!ссылка на ячейку, например: [книга2]ЛистЗ!Е5:Е15.

Вычисление – это процесс расчета формул с последующим выводом результатов в виде значений в ячейках, содержащих формулы. При изменении значений в ячейках, на которые ссылаются формулы, значения формул обновляются (т. е. происходит повторное вычисление). Этот процесс называется перерасчетом. Он затрагивает только те ячейки, которые содержат ссылки на изменившиеся ячейки.

Циклическая ссылка – это формула, которая зависит от своего собственного значения. При обнаружении циклической ссылки Excel выдает сообщение об ошибке.

Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. В Microsoft Excel используют все листы, помещенные между начальным и конечным именами, указанными в ссылке.

Функция – это специальная, заранее подготовленная формула, которая выполняет операции над заданными значениями и возвращает результат. Значения, над которыми функция выполняет операции, называются аргументами. В качестве аргументов могут выступать числа, текст, логические значения, ссылки. Аргументы могут быть представлены константами или формулами. Формулы в свою очередь могут содержать другие функции, т. е. аргументы могут быть представлены функциями. Функция, которая используется в качестве аргумента, является вложенной функцией. Excel допускает до семи уровней вложения функций в одной формуле.

В общем виде любая функция может быть записана в виде:

=<имя_функции>(аргументы).