Формулы и функции в Excel 2007

Одним из основных достоинств электронной таблицы Excel является наличие мощного аппарата формул и функций. Любая обработка данных в Excel осуществляется при помощи этого аппарата. Можно складывать, умножать, делить числа, извлекать квадратные корни, вычислять синусы и косинусы, логарифмы и экспоненты. Помимо чисто вычислительных действий с отдельными числами в Excel имеется возможность обрабатывать отдельные строки или столбцы таблицы, а также целые блоки ячеек, в частности находить среднее арифметическое, максимальное и минимальное значения, среднеквадратичное отклонение, наиболее вероятное значение, доверительный интервал и др.

Формулой в Excel называется последовательность символов, начинающаяся со знака равенства «=», в которую могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы. Без этого знака все введенные символы рассматриваются как текст или число, если они образуют правильное числовое значение.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5) текстовые операторы &;

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

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

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

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

Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула. При перемещении формулы относительная ссылка изменяется, ориентируясь на ту позицию, в которую переносится формула. Например, если в клетке С1 записана формула =А1+В1,то при копировании ее в клетку С1 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1 - =В1+С1.

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

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

Ссылки на ячейки других листов книги имеют следующий формат: [имя книги]<имя листа>!ссылка на ячейку, например:[книга2]Лист 3!Е5:Е15.

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

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

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

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

В общем виде любая функция может быть записана в виде: =<имя_функции>(аргументы).

Для ввода функций модно использовать Мастер функций, вызываемый нажатием на кнопку Вставка функции fx (в строке формул), либо нажатием клавиши SHIFT+F3, либо перейти на вкладку Формулы, выбрать нужную категорию. Мастер функций позволяет выбрать нужную функцию из списка и выводит для нее панель формул. На панели формул отображаются имя и описание функций, количество и тип аргументов, возвращаемое значение. Далее выбрать необходимую функцию в поле Поиск функции.

Можно ввести запрос с описанием операции, которую требуется выполнить, (например, по словам «сложение чисел» будет найдена функция СУММ). Кроме того, можно выбрать категорию в поле Категория. После этого надо ввести аргументы. Для ввода в качестве аргументов ссылок на ячейки надо нажать кнопку свертывания диалогового окна (которая временно скрывает диалоговое окно), выделить ячейки на листе и нажать кнопку Развертывание диалогового окна. По завершении ввода формулы надо нажать клавишу ВВОД.

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

1) арифметические и тригонометрические;

2) инженерные, предназначенные для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую);

3) информационные, предназначенные для определения типа данных, хранимых в ячейках;

4) логические, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ);

5) статистические, предназначенные для выполнения статистического анализа данных;

6) финансовые, предназначенные для осуществления типичных финансовых расчетов, таких как вычисление суммы платежа по ссуде, объема периодической выплаты по вложению или ссуде, стоимости вложения или ссуды по завершении всех платежей;

7) функции без данных, предназначенные для анализа данных из списков или базы данных;

8) текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т.д.);

9) функции работы с датой и временем. Они позволяют анализировать и работать со значениями даты и времени в формулах;

10) нестандартные функции. Это функции, созданные пользователем для собственных нужд.

Создание функций осуществляется с помощью языка Visual Basik.

Если при наборе формулы были допущены ошибки, то в ячейку будет выведено значение ошибки. В Excel 2007 определено семь ошибочных значений:

1. #ДЕЛ/0! - попытка деления на ноль. Эта ошибка обычно возникает, если в формуле делитель ссылается на пустую ячейку.

2. #ИМЯ? - в формуле используется имя, отсутствующее в списке имен диалога Присвоение имени. Excel 2007 также вводит это ошибочное значение в том случае, когда строка символов не заключена в двойные кавычки.

3. #ЗНАЧ! - выводится при указании аргумента или операнда недопустимого типа, например, введена математическая формула, которая ссылается на текстовое значение, а также в том случае, когда Excel 2007 не может исправить формулу средствами автоисправления.

4. #ССЫЛКА! - отсутствует диапазон ячеек, на который ссылается формула (возможно, он удален).

5. #Н/Д - нет данных для вычислений. Аргумент функции или операнд формулы является ссылкой на ячейку, не содержащую данных. Любая формула, которая ссылается на ячейки, содержащие #Н/Д, возвращает значение #Н/Д.

6. #ЧИСЛО! - задан неправильный аргумент функции, например, v(-5). #ЧИСЛО!, может также указывать на то, что значение формулы слишком велико или слишком мало и не может быть представлено на листе.

7. #ПУСТО! - в формуле указано перечисление диапазонов, но эти диапазоны не имеют общих ячеек.

Вложенные функции - это функции, в качестве одного из аргументов которых заданы другие функции. В формулу можно вложить до 64 функций.

Одну и ту же формулу можно быстро ввести в диапазон ячеек. Надо выделить нужный диапазон, ввести формулу, а затем нажать сочетание клавиш CTRL-ВВОД.

Для упрощения создания и изменения формул, а также для снижения необходимости ввода формул вручную и возникновения синтаксических ошибок рекомендуется использовать возможность автозавершения формул.

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

Списки в Excel 2007

Списком в Excel является таблица, строки которой содержат однородную информацию. Список состоит из трех структурных элементов:

1) заглавная строка - это первая строка списка, состоящая из заголовков столбцов. Заголовки столбцов - это метки (названия)соответствующих полей;

2) запись - совокупность компонентов, составляющих описание конкретного элемента (строка таблицы)

3) поля - отдельные компоненты данных в записи (ячейки в столбце).

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

1. Рабочий лист должен содержать только один список, т.к. некоторые операции, например, фильтрование, могут работать только с одним списком.

2. Если на рабочем листе кроме списка необходимо хранить и другие данные, список необходимо отделить пустой строкой и пустым столбцом. Причем лучше не размещать другие данные слева и справа от списка, иначе они могут быть скрыты во время фильтрации списка.

3. Заглавную строку лучше дополнительно отформатировать, чтобы выделить среди строк списка (использовать форматы, отличные от тех, которые применены к данным списка).

4. Метки столбцов могут содержать до 255 символов.

5. Не следует отделять заглавную строку от записи пустыми строками или строкой, содержащей линию из символов «дефис».

6. Список должен быть составлен так, чтобы столбец содержал во всех строках однотипные значения.

7. При вводе значения поля нельзя вставлять ведущие пробелы, это может привести к проблемам при поиске и сортировке.

8. В списках можно использовать формулы. Списки могут обрабатываться, как обычные таблицы.

Значительно упростить работу с записями списка позволяет Форма. Использование формы данных позволяет:

1. Добавить записи в список.

2. Организовать поиск записей в списке.

3. Редактировать данные записи.

4. Удалять записи из списка.

Сортировка списков - это переупорядочивание одного или более столбцов. Для того, чтобы выполнить сортировку надо выбрать столбец с данными в диапазоне ячеек или убедиться, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные. На вкладке Главная в группе Редактирование надо выбрать пункт Сортировка и Фильтр.

Для сортировки в определенном пользователем порядке можно использовать пользовательские списки. В Excel 2007 предоставляются встроенные пользовательские списки дней недели и месяцев года, однако также могут создаваться собственные пользовательские списки. Для этого надо нажать кнопку Microsoft Office, нажать кнопку Параметры Excel, выбрать категорию Основные, а затем в группе Основные параметры работы с Excel нажать кнопку Изменить списки. В диалоговом окне Списки нажать кнопку Импорт, а затем дважды нажать кнопку ОК. Затем на вкладке Начальная страница, а выбрать в списке пункт Специальная сортировка. Отобразится диалоговое окно Сортировка. В группе Столбец и поле Сортировать по или Затем по надо указать столбец для сортировки по настраиваемую списку. В поле Порядок выбрать пункт Настраиваемый список. Выбрать необходимый список в диалоговом окне Списки и нажать кнопку ОК.

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

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