Часть 9. Встроенные функции Excel

1. Каждая функция имеет свой синтаксис и порядок действия, который нужно соблюдать, чтобы вычисления были верными. Аргументы функции записываются в круглых скобках, причем функции могут иметь или не иметь аргументы, при их использовании необходимо учитывать соответствие типов аргументов. Функция может выступать в качестве аргумента для другой функции, в этом случае она называется вложенной функцией. При этом в формулах можно использовать до нескольких уровней вложения функций.

2. В Excel 2016 существуют математические, логические, финансовые, статистические, текстовые и другие функции. Имя функции в формуле можно вводить вручную с клавиатуры (при этом активируется средство Автозаполнение формул, позволяющее по первым введенным буквам выбрать нужную функцию), а можно выбирать в Ленте > Формулы

кнопку         или из групп функций на этой же панели, либо с помощью кнопки панели Редактирование вкладки Главная.

 

Рис. 24. Автозаполнение формул

 

3. Формулы можно отредактировать так же, как и содержимое любой другой ячейки. Чтобы отредактировать содержимое формулы: дважды щелкните по ячейке с формулой, либо нажмите F2, либо отредактируйте содержимое в строке ввода формул. Зафиксировать результат.

4. В Excel 2016 имеется полезная возможность присваивания имен ячейкам или диапазонам. Это бывает особенно удобно при составлении формул. Например, задав для какой-либо ячейки имя Итого_за_год, можно во всех формулах вместо адреса ячейки указывать это имя.

 

Рис. 25. Окно создания имени

 

Имя ячейки может действовать в пределах одного листа или одной книги, оно должно быть уникальным и не дублировать названия ячеек.

5. Чтобы присвоить имя ячейкам, нужно выделить ячейку или диапазон, вызвать контекстное меню на выделенных ячейках и нажать Присвоить имя… и в строке названия ввести новое имя. Либо воспользоваться кнопкой Присвоить имя панели Определенные имена вкладки Формулы и вызвать диалоговое окно (см. рис. 25), чтобы задать нужные параметры. Зафиксировать результат.

6. Для просмотра всех присвоенных имен используйте команду

Диспетчер имен. Также на листе можно получить список всех имен с

адресами ячеек по команде Использовать в формуле – Вставить имена

панели Определенные имена.

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

Примечание!

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

8. Отображение зависимостей в формулах.

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

8.2. Влияющая ячейка – это ячейка, которая ссылается на формулу в другой ячейке.

 

Рис. 26. Отображение влияющих ячеек

 

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

9. Режимы работы с формулами.

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

После внесения изменений нужно вызвать команду Произвести вычисления (для пересчета данных на листе книги) или Пересчет (для пересчета всей книги) панели Вычисление. Создайте любую таблицу с формулами и вычислениями и используйте режим автоматических вычислений и вычисление вручную. Зафиксировать результат.

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

9.3. Если формула возвращает ошибочное значение, Excel может помочь определить ячейку, которая вызывает ошибку. Для этого нужно активизировать команду Формулы – Зависимости формул – Проверка наличия ошибок – Источник ошибок. Команда Проверка наличия ошибок помогает выявить все ошибочные записи формул. Зафиксировать результат.

9.4. Для отладки формул существует средство вычисления формул, вызываемое командой Формулы – Зависимости формул – Вычислить формулу, которое показывает пошаговое вычисление в сложных формулах. Зафиксировать результат.