Главная страница Случайная страница
КАТЕГОРИИ:
АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника
Создание формул с использованием заголовков
|
|
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. Обратите внимание на пробел между заголовками строки и столбца.
| О пересечениях диапазонов |
| Если в ячейку I4 листа, представленного на рис. 6, ввести формулу =Кв 1*4 то Excel решит, что мы хотим использовать только одно значение из диапазона В4: В7 — то, которое находится в той же строке, что и формула, содержащая эту ссылку. Это называется неявнымпересечением (implicit intersection). Поскольку мы ввели формулу в ячейку I4, Excel использует значение в ячейке В4. Если мы скопируем эту формулу в диапазон I5: I7, каждая ячейка в этом диапазоне будет содержать формулу, =Кв 1*4, но в I5 она будет ссыпаться на В5, в I6 — на В6 и т. д. Этот прием можно также использовать для ссылок на конкретные ячейки в строке. Явное пересечение (explicit intersection) указывает на конкретную ячейку и задается с помощью операторапересечения (intersection operator) — символа пробела. Если в любом месте этого же рабочего листа ввести формулу =Кв 1 Изделие 1*4 то Excel поймет, что мы хотим использовать значение, лежащее на пересечении диапазона Кв 1 и диапазона Изделие 1, то есть ячейку В4. |
Формулы, содержащие заголовки, можно копировать и вставлять, при этом Excel автоматически настраивает их на нужные столбцы и строки. В рассматриваемом примере формула из ячейки В9 была скопирована в диапазон C9: G9. Все формулы в этом диапазоне Excel изменяет так, чтобы в них использовался заголовок соответствующего столбца. Так, формула из ячейки В9 после копирования в ячейку G9 изменилась на следующую."
=СУММ(Всего)
Обратите внимание, что при копировании формулы, содержащей заголовки, в неподходящее место, Excel всегда сообщает об этом. Например, если скопировать формулу из ячейки Н9 в ячейку J9, Excel выведет в этой ячейке значение ИМЯ?, так как в столбце J нет заголовка.
Если вы измените заголовки столбцов и строк таблицы, то Excel автоматически изменит и заголовки, использовавшиеся в формулах. Например, если заголовок столбца Всего изменить на Итого, то формула в ячейке G9 примет следующий вид:
=СУММ(Итого)
СОВЕТ
Формулы, содержащие заголовки, можно применять только в том же листе, где находится таблица. Если вы хотите подобным образом ссылаться на ячейки, расположенных в других листах или книгах, используйте именованные диапазоны. Вы можете отключить режим использования заголовков в формулах. Для этого в меню Сервис (Tools) выберите команду Параметры (Options) и на вкладке Вычисления (Calculation) открывшегося окна диалога снимите флажок Допускать названия диапазонов (Accept Labels In Formulas)