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

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

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

Если при создании формулы в качестве операнда используется одна ячейка текущего листа, то в формулу войдут ее координаты (адрес ячейки, ссылка на нее).

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

Ввод формулы начинается с ввода знака равенства (символа ‘=’), за которым вводятся операнды и операции.

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

По умолчанию Excel заново пересчитывает значения по всем формулам рабочего листа каждый раз, когда вносятся изменения в ячейки, указанные в формуле. При больших размерах таблиц это может снизить производительность. Поэтому пользователь может поменять режим пересчета значений по формулам. Для этого нужно выполнить команду Параметры меню Сервис, выбрать вкладку «Вычисления» открывшегося диалогового окна и установить режим пересчета «Вручную». Если установлен этот переключатель, то можно заставить Excel пересчитать все значения в любой момент, когда это необходимо, нажав клавишу F9.

При открытии и распечатке рабочих листов все значения пересчитываются автоматически независимо от установленного режима.

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

Если при попытке вычисления по формуле произошла ошибка, то вместо значения (результата вычисления по формуле) в ячейке выводится сообщение об ошибке (строка, начинающаяся символом #, например: #ДЕЛ/0! – сообщение о попытке выполнить деление на 0; #Н/Д! – отсутствуют данные, необходимые для расчетов по формуле; #ИМЯ? – в формуле используется ссылка на несуществующее имя; #ЧИСЛО! – в формуле используется недопустимый числовой аргумент; #ССЫЛКА! – неверно указаны координаты ячейки; #ЗНАЧ! – неверный тип данных для вычисления).

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

Формулу можно определить программно, задав соответствующее свойство объекта (ячейки). Например:

ActiveCell.FormulaR1C1 = "=5+R[2]C"

или

ActiveSheet.Cells(2, 1).Formula = "=sum(b1:b5)"

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