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

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

ИНТЕРНЕТ

Файл Sales.xls, использованный в приведенном ниже примере, можно найти на Web-узле издательства «Питер» по адресу https://Vvww.piter-press.ru/download/.

СОВЕТ

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

Рисунок 6 - В формулах можно использовать заголовки столбцов и строк таблицы для ссылок на ячейки и диапазоны

При обычной записи формула в ячейке В9 выглядела бы следующим образом:

=СУММ(В4: В7)

При использовании заголовков вместо ссылок она имеет следующий вид:

=СУММ(Кв 1)

Примечание

Для включения в формулы заголовков необходимо в меню Сервис„ Параметры на вкладке Вычисление установить флажок Допускается название диапазонов

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

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

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

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

=Изделие 2 Кв 2

для ссылки на ячейку С5, находящуюся на пересечении строки таблицы Изделие 2 и столбца Кв 2. Обратите внимание на пробел между заголовками строки и столбца.

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

=СУММ(Всего)

Обратите внимание, что при копировании формулы, содержащей заголовки, в неподходящее место, Excel всегда сообщает об этом. Например, если скопировать формулу из ячейки Н9 в ячейку J9, Excel выведет в этой ячейке значение ИМЯ?, так как в столбце J нет заголовка.

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

=СУММ(Итого)

СОВЕТ

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