Трюк №52. Как создать диаграмму Excel с динамически изменяемыми данными
Новые данные могут автоматически добавляться и выводиться на диаграммах, как только вы поместите их в электронную таблицу.
Если вместо ссылок на диапазоны использовать динамические именованные диапазоны, новые данные на диаграммах будут выводиться при добавлении их на рабочий лист. Чтобы увидеть, как это работает, начните с чистого рабочего листа и добавьте какие-то данные (рис. 5.7).
Рис. 5.7. Данные, которые будут помещены на диаграмму
Для создания динамической диаграммы понадобятся два именованных диапазона: один для меток категорий (Dates), а второй — для точек фактических данных (Temperature).
[stextbox id=»info»]Если вы не помните, как создавать динамические именованные диапазоны, обратитесь к разделу «Трюк № 42. Создание увеличивающихся и уменьшающихся диапазонов».[/stextbox]
Создайте динамический именованный диапазон с именем TEMP_DATES для дат в столбце А, выбрав команду Вставка → Имя → Присвоить (Insert → Name → Define) и введя следующую формулу: =OFFSET($A$1;1;0;COUNTA($A:$A)-1;1), в русской версии Excel =СМЕЩ($A$1;1;0;СЧЁТЗ($А:$А)-1;1).
Обратите внимание, что сразу же за аргументом СЧЁТЗ (COUNTA) стоит -1. Это гарантирует, что заголовок не будет включен в именованный диапазон для определенного ряда.
[stextbox id=»warning»]В этом примере в качестве аргумента функции COUNTA (СЧЁТЗ) указан весь столбец А ($А:$А), В ранних версиях Excel лучше ограничивать его намного меньшей группой ячеек, чтобы не перегружать вычисления. Другими словами, указывая диапазон полностью, вы заставляете Excel просматривать тысячи ненужных ячеек. Некоторые функции Excel достаточно умны, чтобы определить, какие ячейки содержат данные, некоторые сделать это не могут. В новых версиях Excel не обязательно строго ограничивать диапазон, так как обработка больших диапазонов в них улучшена.[/stextbox]
Затем для температурных данных столбца В настройте еще один динамический диапазон с именем TEMP_READINGS, указав формулу =OFFSET($B$2;0;0;COUNTA($B:$B)-1;1), в русской версии Excel =СМЕЩ($В$2;0;0;СЧЁТЗ($В:$В)-1;1). Теперь можно создать диаграмму, которой сможе воспользоваться уголовный адвокат, используя созданные динамические именованные диапазоны вместо ссылок на ячейки.
Рис. 5.8. Динамические именованные диапазоны вместо статических ссылок на диапазоны
Выделите данные (диапазон $А$1:$В$11), затем на стандартной панели инструментов щелкните кнопку Мастер диаграмм (Chart Wizard). На первом шаге мастера выберите тип диаграммы (в этом примере мы используем гистограмму) и щелкните кнопку Далее (Next). На втором шаге вы увидите две вкладки: Диапазон данных (Data Range) и Ряд (Series). Вам нужна вкладка Ряд (Series). Удалите формулу, которая находится в поле Значения (Value), и введите следующую формулу: =Sheetl!TEMP_READINGS.
[stextbox id=»warning»]В ссылку в формуле важно включить имя листа рабочей книги. Не сделав этого, нельзя ввести в формуле именованный диапазон.[/stextbox]
Наконец, удалите формулу в поле Подписи оси X (Category X Labels) и введите следующее: =Sheet1!TEMP_DATES. Выполните остальные шаги мастера диаграмм, внеся необходимые изменения. Результат должен выглядеть, как на рис. 5.8. Новые записи в столбце A (Dates) или столбце В (Temperature) будут при добавлении автоматически появляться в настроенной диаграмме.
Построение диаграммы для нескольких последних данных
Еще один тип именованных диапазонов, который можно использовать с диаграммами, — это диапазоны, выбирающие только последние 10 значений (можно указать любое число) из ряда данных. Попробуйте выполнить следующее на тех же данных, которые использовались в первой части этого трюка. Для данных в столбце А создайте динамический именованный диапазон с именем TEMP_DATES_10DAYS, который ссылается на следующие данные: =OFFSET($A$1;COUNTA($A:$A)-10;0;10;1), в русской версии Excel =СМЕЩ($А$1;СЧЁТЗ($А:$А)-10;0;10;1).
Для данных в столбце В создайте еще один динамический именованный диапазон с именем TEMP_READINGS_10DAYS и введите следующую формулу: =OFFSET(Sheet1!$А$1;COUNTA(Sheet15!$А:$А)-10;1;10;1), в русской версии Excel =СМЕЩ(Sheet1!$А$1,CЧETЗ(Sheetl5!$А:$А)-10;1;10;1).
Если вы хотите изменить количество используемых значений, например, на 20, измените последнюю часть формулы, чтобы она выглядела так: =OFFSET(Sheet1!$А$1;COUNTA(Sheet15!$А:$А)-20;1;20;1), в русской версии Excel =СМЕЩ(Sheet1!$A$l;CЧETЗ(Sheet15!$A:$A)-20;1;20;1).
Использование динамических именованных диапазонов с диаграммами обеспечит исключительную гибкость и сэкономит огромное количество времени и усилий, которые вы потратили бы на настройку диаграмм после добавления еще одной записи к исходным данным.