Ввод и использование формул

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

= СУММ(А1 :А15)/$С$3 + ЦЕНА*(150 + КОЛИЧ)

В этом примере СУММ - имя функции; А1, А15, $С$3 - ссылки; А1 : А15 - диапазон (массив) ячеек; ЦЕНА, КОЛИЧ - имена диапазонов ячеек; 150 - константа.

В формулах могут использоваться арифметические операторы +; -; *; /; % (определение значения процента); А (возведение в степень); операторы сравнения =; ;

154

>=; о (не равно) и оператор объединения текстовых данных &. Кроме того, Excel распознает три адресных оператора для указания диапазонов ячеек:

Оператор диапазона (двоеточие). Например, по формуле =СУММ(В1:01) вычисляется сумма содержимого ячеек В1, С1 и D1, входящих в состав диапазона В1 :D1. Оператор диапазона можно использовать для указания ссылок на ячейки всего столбца (например, А:А), на ячейки всей строки (например, 1:1), на ячейки всего рабочего листа (A:IV или 1:16384).

Оператор объединения диапазонов (точка с запятой). Например, по формуле =СУМ М(С5: С7; A3:СЗ) вычисляется сумма двух несмежных диапазонов С5: С7 и A3:СЗ.

Оператор пересечения диапазонов (пробел). Например, по формуле =СУММ(А5:В11 В8:С10) вычисляется сумма содержимого ячеек диапозонаВб:В10.

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

Абсолютная ссылка задает абсолютные координаты ячейки в рабочем листе. При перемещении или копировании формулы из одной ячейки в другие такая ссылка в формуле не изменяется и всегда указывает на одну и ту же ячейку таблицы. Для записи абсолютной ссылки в стиле "А1" (см. параграф 8.1) используется знак $, например, $В$5, $С$4 и т.д. Ссылка, записанная в стиле "R1C1" без квадратных скобок, является абсолютной, например, R5C2, R4C3.

Относительная ссылка в стило "А1" записывается без знака $, например: А1, В5, J3 и т.д.
При вычислении формулы такие ссылки, так же как и абсолютные, указывают на координаты ячеек таблицы, участвующих в операции. Например, по формуле =А2+В2+С2, так же как и по формуле =$А$2+$В$2+$С$2, осуществляется суммирование содержимого первых трех ячеек второй строки рабочего листа. Отличие заключается в том, что при копировании формулы с относительными ссылками в другое место последние изменяются. Изменения определяются новым положением формулы в таблице. При этом действует следующее правило: относительная ссылка изменяется таким образом, что маршрут (направление движения и расстояние) к адресуемой ячейке относительного нового положения формулы сохраняется. Чтобы задать относительную ссылку в стиле "R1C1", используются квадратные скобки. В скобках указывается нужное количество строк и столбцов относительно ячейки, содержащей формулу с такой ссылкой. Например, R[2]C[5] - ссылка на ячейку, расположенную на две строки ниже и на пять столбцов правее ячейки, в которой записана такая ссылка, R[-2]C[-1] - ссылка на ячейку, которая находится на две строки выше и на один столбец левее ячейки, содержащей ссылку.

Смешанная ссылка - это ссылка, в которой адресация строки отличается от адресации столбца. Например, А$3 - смешанная ссылка с указанием относительной адресации столбца и абсолютной адресации строки. Другие примеры смешанных ссылок: $В5, R3C[-2], R4C[-2].

Замечания.

Для выбора стиля представления ссылок необходимо в диалоговом окне Параметры (Options) на вкладке Вид (View) установить переключатель "А1" или "R1С1".

При записи относительных ссылок в стиле "R1C1" нулевое количество строк и столбцов в квадратных скобках допускается не указывать. Например, ссылка RC1 равнозначна ссылке R[0]C1.

В таблице невозможно использование двух стилей записи адресов одновременно.

155

Для пояснения смысла указанных типов ссылок рассмотрим фрагмент таблицы на рис.12.3.

	А	В	С	D
1	34	12	10	=А1+В1+С1
2	45	23
3	=А1+А2
4

<

/p> Рис.12.3. Пример использования ссылок

В ячейке A3 таблицы записана формула =А1 +А2, т.е. сумма элементов первого столбца, а в ячейке D1 - формула =А1 +В1+С1. т.е. сумма элементов первой строки. Заметим, что обе формулы содержат относительные ссылки. При вычислении по этим формулам в ячейках A3 и D1 будут отображены значения 79 и 56, соответственно. Если скопировать или переместить формулу из ячейки A3 в ячейки ВЗ и СЗ, то в них появятся формулы =В1+В2 и =С1+С2, т.е. суммы второго и третьего столбца соответственно.

При копировании формулы из ячейки D1 в ячейку D2 в последней будет записана формула =А2+В2+С2, т.е. сумма второй строки, равная 109.

Изменим ссылки в обеих исходных формулах. В ячейку A3 запишем формулу =$А$1+А2, а в ячейку D1 формулу =А1+$В$1+С$1. При копировании первой формулы в ячейки ВЗ и СЗ в них появятся записи =$А$1+В2 и =$А$1+С2, соответственно. При копировании второй формулы в ячейку D2 в последней появится запись =А2+$В$1 +С$1.

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

Выделить нужную ячейку.

Указать в качестве первого символа знак равенства.

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

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

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

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

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

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

156

Таблица 12.2

Сообщения об ошибках в формуле

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

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

с помощью контекстного меню любой видимой панели инструментов задают отображение на экране панели Зависимости (Auditing);

щелкают по кнопке Влияющие ячейки (Trace Precedents), расположенной на этой панели. Excel отобразит в рабочем листе стрелки только от непосредственно влияющих ячеек по направлению к активной ячейке;

еще один щелчок по этой же кнопке приводит к отображению влияющих ячеек следующего уровня (косвенно влияющих на содержимое текущей ячейки). Каждый последующий щелчок по кнопке будет приводить к отображению следующего уровня влияющих ячеек, если таковые имеются;

чтобы удалить стрелки к влияющим ячейкам низшего (самого дальнего) уровня, щелкают по кнопке Убрать стрелки к влияющим ячейкам (Remove Precedents Arrows).

Можно убрать стрелки всех уровней сразу с помощью кнопки Убрать все стрелки (Remove All Arrows).

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

157

По отношению к ячейке с текстом сообщения об ошибке на панели Зависимости (Auditing) существует кнопка с именем Источник ошибки (Trace Error). Щелчок по этой кнопке приводит к отображению на экране красной стрелки от ячейки, ставшей причиной возврата значения ошибки.

Замечание.

Установкой флажка Формулы (Formulas) на вкладке Вид (View) диалогового окна Параметры (Options) меню Сервис (Tools) можно задать отображение в ячейках формул вместо результатов. Для поиска ошибок таблицу с формулами можно вывести на печать.

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

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

'С:\ЕХСЕL\ДОКУМЕНТЫ\[Учебн.ХLS]Каф25'!$В$5,

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

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

Содержание раздела