Формулы Microsoft Excel и их отладка

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

Некоторые функции не имеют параметров. В этом случае записываются пустые скобки. Например, =ПИ(), =СЕГОДНЯ().

Формулы могут содержать следующие арифметические действия:

+ сложение;

– вычитание;

* умножение;

/ деление;

^ возведение в степень.

В табличном процессоре Excel определены различные математические функции, некоторые из них представлены в табл. 10.2.

Таблица 10.2. Математические функции
табличного процессора Excel

Функция	Описание
КОРЕНЬ	Квадратный корень
ФАКТР	Факториал
СУММ	Сумма аргументов
МАКС	Максимальное значение аргументов
МИН	Минимальное значение аргументов
СРЗНАЧ	Среднее арифметическое аргументов

Эти и другие функции, их параметры и справку по ним можно найти с помощью Мастера функций (меню Вставка | Функция).

Пусть дано выражение

y = ,

и значение х содержится в ячейке А1, а y – в A2, тогда соответствующая запись в ячейке A2 будет иметь вид:

=КОРЕНЬ(SIN(A1+2)/(A1+3)+7)/LN(ABS(1+(A1+COS(A1/2)^2)/A1^3)).

Рассмотрим вычисление сложных формул. Пусть дано выражение

y = .

Воспользуемся функцией ЕСЛИ:

ЕСЛИ(Условие; Значение 1; Значение 2),

где Условие – логическое выражение; Значение 1 – значение, возвращаемое в случае истинности условия; Значение 2 – значение, возвращаемое в случае ложности условия.

Значение 1 и Значение 2 могут быть формулами. Если условие истинно или ложно, а соответствующее значение отсутствует, то возвращается значение 0.

Значение х содержится в ячейке А1, а y – в A2, тогда соответствующая запись в ячейке A2 будет иметь вид:

=ЕСЛИ(A1<0;A1+5;ЕСЛИ(A1<2;A1*A1;-1/A1)).

Чтобы изменить тип ссылки в формуле, необходимо выполнить следующие действия:

1) выбрать ссылку в формуле;

2) нажимать клавишу F4 до тех пор, пока ссылка не будет иметь нужный тип.

Формулы пересчитываются при вводе или изменении значений в ячейках, которые участвуют в вычислениях.

Чтобы принудительно пересчитать формулы во всех открытых книгах, нажмите клавишу F9.

Чтобы установить автоматический пересчет формул необходимо выполнить следующие действия:

1) выбрать пункт меню Сервис | Параметры;

2) выбрать вкладку Вычисления;

3) в группе Вычисления выбрать пункт Автоматически.

Чтобы просмотреть, какие формулы записаны в ячейках, необходимо перейти в режим проверки формул с помощью пункта меню Сервис | Зависимости формул | Режим проверки формул или сочетания клавиш CTRL + Ё.

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

Таблица 10.3. Сообщения об ошибках

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

Чтобы выявить ошибку, возникшую при вычислении формулы, необходимо выполнить следующие действия:

1) выбрать ячейку, где содержится формула с ошибкой;

2) выбрать пункт меню Сервис | Зависимости формул | Вычислить формулу;

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

4) повторять шаг 3 до тех пор, пока не появится сообщение об ошибке.

Чтобы выявить ячейки, влияющие (зависимые) на значение данной ячейки необходимо выполнить следующие действия:

1) выбрать ячейку, для которой необходимо выявить влияющие (зависимые) ячейки;

2) выбрать пункт меню Сервис | Зависимости формул | Влияющие ячейки (Зависимые ячейки).

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

Чтобы убрать стрелки зависимостей значений необходимо выбрать пункт меню Сервис | Зависимости формул | Убрать все стрелки.

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

M E±P,

где М – мантисса, находящаяся в пределах [0; 10); P – десятичный порядок.

Например, число 25 представляется, как 2,5E+01, что означает 2,5·10¹.