Марк Мур. Динамические диаграммы

Рубрика: 7. Полезняшки Excel

В последнее время меня интересует тема дашбордов. Динамические диаграммы, использующие элементы управления – это одна из типичных фишек наглядного представления данных. Ранее в заметке Диаграммы в Excel. Отображение части данных с использованием элементов управления я описал работу одного из элементов – полосы прокрутки. Представляю вам еще четыре элемента управления, которые способны сделать ваши диаграммы профессиональными и легко читаемыми: счетчик (Spin Button), флажок (CheckBox), переключатель (Option Button), камера. Изложение довольно подробное, так что материал доступен и пользователям начального уровня. Обзор основан на переводе книги

Mark Moore. Mastering Excel. Interactive Charts

Скачать заметку в формате Word или pdf, пошаговые инструкции и итоговые результаты в Excel

Вкладка Разработчик

При стандартной установке Excel вкладка Разработчик не активируется. Пройдите по меню Файл –> Параметры. В открывшемся окне Параметры Excel выберите закладку Настроить ленту и поставьте галочку Разработчик (рис. 1).

Рис. 1. Активация вкладки Разработчик; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

На ленте появится вкладка Разработчик. Если в области Элементы управления кликнуть на кнопке Вставить, увидите интересующие нас инструменты (рис. 2). Не используйте Элементы ActiveX. Хотя они и выглядят похоже, но используют код VBA.

Рис. 2. Элементы управления формы на вкладке Разработчик

Уровни Excel

Исходные данные представляют собой отчет о продажах (рис. 3). Данные преобразованы в таблицу. Такое представление нагляднее и удобнее для дальнейшей обработки. Главное – вы получаете возможность обращаться к массиву данных по имени таблицы.

Рис. 3. Исходные данные, преобразованные в таблицу

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

Изменяя один уровень, вы влияете на другие. Например, если вы добавляете 100 строк на уровень данных, уровень бизнес-логики (формулы) обработает эти новые строки, а уровень представления включит новые данные в диаграммы. Это произойдет автоматически, если вы организовали данные в Таблицу (как на рис. 3).

Но вы можете изменить один слой, не затрагивая другие. Например, нового менеджера не устраивает дизайн дашборда. Если вы построили модель на основе уровней, вам нужно будет изменить только уровень представления. Ваша работа, выполненная на уровне данных и бизнес-логики, не пропадет.

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

Даты

Excel хранит даты в виде чисел, даже если значение в ячейке или строке формул выглядит как дата (например, 18.02.2016). Если отформатировать ячейку, содержащую дату, как число, мы увидим истинное значение. Целая часть такого числа – количество дней, прошедших с 1 января 1900 года. Десятичная часть числа (если таковая имеется) – доля от 24 часов в сутках. Например, 42 372,5 соответствует 01.03.2016 12:00.

Формат даты инвойса (см. рис. 3) – ДД.ММ.ГГ. Когда же мы начнем строить диаграмму, мы захотим увидеть данные по месяцам, а не по дням. Чтобы добавить гибкости, я ввел столбец Краткая дата. В нем используется формула =ТЕКСТ(B2;"МММ-ГГ"), которая возвращает дату в текстовом формате.

Небольшой трюк. Промежуточные листы будут использовать формулу =СУММЕСЛИ(). Для суммирования дат, представленных в текстовом формате, дата должна быть введена в формулу тоже, как текст (с предварительным апострофом) ‘фев-16. Этот текст будет соответствовать тому, что находится в столбце Краткая дата.

Кнопка Счетчик (Spin Button)

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

Рис. 4. Фактические продажи и целевой уровень

Обратите внимание, что к заметке прикреплено два Excel-файла. Первый позволит вам выполнять пошаговые инструкции. Второй содержит итоговые результаты. Откройте первый файл. Перейдите на лист Счетчик. В ячейке C2 настроена проверка данных. Чтобы ее реализовать, выделите ячейку С2 и пройдите по меню Данные –> Работа с данными –> Проверка данных. В открывшемся окне Проверка вводимых данных выберите Тип данныхСписок, и укажите Источник =Поиск!$A$2:$A$11 (рис. 5).

Рис. 5. Список продавцов

Перейдите на лист Счет-подготов. Он – промежуточный. На нем собраны данные, на основании которых будет построена диаграмма (рис. 6). Формула =СУММЕСЛИМН() находит сумму инвойсов за каждый месяц по продавцу, указанному в ячейке B2. Сама же ячейка В2 связана с ячейкой С2 на листе Счетчик. Т.е., на листе Счет-подготов в ячейке В2 будет имя продавца, выбранного в списке на листе Счетчик.

Рис. 6. Уровень бизнес-логики – промежуточный лист, как база для построения диаграммы

Вставьте график. Для чего выделите диапазон В1:N3 и пройдите по меню Вставить –> Диаграммы. Выберите пиктограмму Вставить график или диаграмму с областями (рис. 7).

Рис. 7. Вставить диаграмму

Получится следующая диаграмма:

Рис. 8. Начальный вид диаграммы

Чтобы упростить работу, вы сначала все манипуляции выполните на листе Счет-подготов, а потом переместите диаграмму и кнопку на лист Счетчик.

На вкладке Разработчик кликните на значок кнопки Счетчик (рис. 9).

Рис. 9. Значок Счетчик

Указатель мыши превратиться в тонкий крестик. Нарисуйте небольшой вертикальный прямоугольник, и отпустите клавишу мыши. Впоследствии вы сможете изменить размер кнопки Счетчик.

Зададим свойства кнопки Счетчик. Щелкните на кнопке правой кнопкой мыши и выберите опцию Формат объекта… (рис. 10).

Рис. 10. Формат кнопки Счетчик

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

Рис. 11. Формат элемента управления

Нажмите ОК. Кликните на любой ячейке, чтобы отменить выделение кнопки Счетчик. Несколько раз нажмите на верхнюю часть кнопки Счетчик, чтобы увеличить целевой объем продаж. Одновременно будет увеличиваться значение в ячейке А3 и значения в ячейках С3:N3. Линия Целевые продажи на графике поползет вверх. Щелкните правой кнопкой мыши на диаграмме и выберите Вырезать. Вставьте диаграмму на лист Счетчик. Вернитесь на лист Счет-подготов. Щелкните правой кнопкой мыши на кнопке Счетчик и выберите Вырезать. Вставьте кнопку Счетчик на лист Счетчик. Убедитесь, что кнопка по-прежнему связана с ячейкой ‘Счет-подготов’!$A$3. Преобразуйте линейную диаграмму в комбинированную. Отформатируйте столбики и линии. Воспользуйтесь рекомендациями Эдварда Тафти и минимизируйте количество элементов диаграммы.

Давайте еще раз рассмотрим, как работает эта диаграмма. Данные поступают из Таблицы на листе Данные. Основная часть бизнес-логики (работа формул) выполняется на листе Счет-подготов. Лист Счетчик содержит список продавцов, кнопку и диаграмму. Как работают эти два листа:

  • Формулы =СУММЕСЛИМН() во второй строке листа Счет-подготов суммируют продажи по месяцам для выбранного в ячейке B2 продавца.
  • Продавец в ячейке B2 связан с ячейкой С2 листа Счетчик, где он выбирается из списка.
  • Целевой уровень продаж задается в ячейке A3 листа Счет-подготов кнопкой Счетчик (но можно с клавиатуры ввести в ячейку произвольное значение).
  • Все значения в диапазоне С3:N3 листа Счет-подготов равны целевому уровню продаж.
  • Диаграмма основана на диапазоне В1:N3 листа Счет-подготов.
  • Сама диаграмма, кнопка Счетчик и список продавцов расположены на отдельном листе.

Если вы поэкспериментируете с тем, какие данные должны отражаться на диаграмме, вам станет более понятной польза от концепции уровней и от того, как мы организовали структуру решения задачи. Если вам нужно изменить бизнес-логику, вы меняете лист Счет-подготов. Вам не нужно менять, ни диаграмму, ни лист данных. Например, напишите формулы, которые будут устанавливать целевой уровень продаж в зависимости от продавца.

Инструмент Флажок (CheckBox)

В этом раздела мы построим диаграмму, отражающую объем продаж по одному или нескольким регионам (рис. 12). Для интерактивного скрытия / отображения региона используются флажки.

Рис. 12. Продажи по регионам

Откройте Excel-файл. Перейдите на лист Флажок, который пока содержит лишь уникальный список регионов. Перейдите на вкладку Разработчик. Выберите Вставить –> Флажок (рис. 13). Щелкните и перетащите курсор мыши на лист. Кликните на тексте по умолчанию Флажок 1 и удалить его. Перетащите и измените размер флажка, чтобы он поместился в ячейку А3 (рис. 14).

Рис. 13. Вставка флажка

Рис. 14. Первый флажок создан

Трюк. Нажмите на ячейку A3, чтобы выбрать ячейку, а не флажок. Перетащите маркер заполнения (в правом нижнем углу ячейки) до А8. Флажок будет скопирован. К сожалению, свойства каждого флажка нужно установить по-отдельности.

Щелкните правой кнопкой мыши на первый флажок. Выберите Формат Объекта. Перейдите на вкладку Элемент управление (рис. 15). Нажмите кнопку установлен и свяжите с ячейкой ‘Флаг-подготов’!$A$13. Нажимать OK

Рис. 15. Свойства первого флажка

Проверьте, как это работает. При установленном флажке в ячейке А13 листа Флаг-подготов отражается значение ИСТИНА, при снятом флажке – ЛОЖЬ.

Установить свойства остальных флажков, последовательно связывая их с ячейками А14, А15, … Нижняя часть промежуточного листа Флаг-подготов будет источником данных для диаграммы. Добавьте формулу, которая будет отображать данные, если значение в столбце A = ИСТИНА. В ячейку С13 введите формулу =ЕСЛИ($A13;C3;0). Протащите формулу на диапазон С13:N18. Обратите внимание, как введена ссылка на ячейку $A13. Она смешанная. Это сделано с целью подготовит формулу для протаскивания по столбцам и строкам.

Снимите несколько флажков на листе Флажок, перейдите на лист Флаг-подготов. Вы увидите, что соответствующие строки в диапазоне С13:N18 заполнились нулями. Обратите внимание, что вам не обязательно нажимать флажки, вы можете просто ввести ИСТИНА (или 1) / ЛОЖЬ (или 0) в ячейках столбца A.

Выделите диапазон B12:N18. Вставьте линейный график (см. рис. 7). Щелкните правой кнопкой мыши на графике и выберите Вырезать. Вставьте график на лист Флажок. Проверьте, как меняется график при установке/снятии флажков. Отформатируйте диаграмму, придав ей более профессиональный вид.

Инструмент Переключатель (Option Button)

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

Рис. 16. Продажи по кварталам

Бизнес-логика для этого случая немного сложнее предыдущих. Вы будете использовать функцию ВПР, чтобы определить, какой вариант выбрал пользователь. Перейдите на лист Переключатель. Вставьте кнопку Переключатель в ячейку В3 (рис. 17). Скопируйте кнопку на ячейки В4:В6.

Рис. 17. Вставка переключателя

Выберите каждую кнопку и измените название по умолчанию на номер квартала (порядок имеет значение: первая, созданная вами кнопка, должна соответствовать 1-му кварталу и т.д.).

Вставьте групповой блок (рис. 18). При его создании убедитесь, что он полностью окружает все кнопки. Назовите группу Кварталы. Щелкните правой кнопкой мыши на одну из кнопок. Выберите Формат объекта. Перейдите на вкладку Элемент управления. Установите ссылку на ячейку A12 листа Перекл-подготов. Поскольку все кнопки являются частью группы, установка одной ссылки на ячейку устанавливает их все. Понажимайте на кнопки, чтобы увидеть изменения чисел в ячейке A12.

Рис. 18. Вставка группы

Рис. 19. Свойства Переключателя

На промежуточном листе Перекл-подготов в диапазоне С3:Н6 формулы =СУММЕСЛИМН() на основе исходных данных суммируют продажи по кварталам и регионам (рис. 20).

Рис. 20. Промежуточный лист бизнес-логики

Для построения диаграммы будет использована область В11:Н12, данные в которую будут отбираться из области В3:Н6 на основании значения в ячейке А12. Проблема с кнопками Переключателя заключается в том, что они возвращают только свой индекс в группе. Другими словами, первая кнопка вернет 1, вторая – 2 и т.д.

С задачей отбора по индексу справляется функция ВПР. Наберите в ячейке В12 формулу: =ВПР($A$12;$A$3:$H$6;СТОЛБЕЦ();ЛОЖЬ). Протащите ее на диапазон В12:Н12. Создайте объемную круговую диаграмму на основании диапазона В11:Н12. Перенесите ее на лист Переключатель. Протестируйте диаграмму, нажимая кнопки разных кварталов. Отформатируйте диаграмму. У вас должно получиться что-то близкое к рис. 16.

Инструмент Камера

Этот раздел чуть сложнее. Он включает несколько оригинальных трюков. Вот что у нас получится в результате:

Рис. 21. Общие продажи по регионам и менеджерам

Это небольшой дашборд. Однако, если нажать кнопку Переключатель, диаграмма изменит вид. И это всё без макросов.

Инструмент Камера является очень полезной функцией, которая давно представлена в Excel, но всегда была скрыта. Добавьте кнопку Камера на панель быстрого доступа. Нажмите на стрелку на панели (рис. 22). Выберите Другие команды…

Рис. 22. Настройка панели быстрого доступа

В открывшемся окне Параметры Excel (рис. 23) в верхнем поле выберите Команды не на ленте (1). На левой панели найдите и выберите кнопку Камера (2). Нажмите Добавить (3). В правом окне появится кнопка Камера (4). Нажмите OK.

Рис. 23. Параметры Excel

Кнопка Камера появилась на панели быстрого доступа (рис. 24).

Рис. 24. Кнопка Камера на панели быстрого доступа

Инструмент Камера делает снимок диапазона ячеек, который затем можно перемещать по листу и даже на другие листы. При этом изображение связывается с исходными ячейками. Это означает, что при изменении исходных ячеек изображение будет обновляться автоматически.

Покажем, как работает инструмент. На листе Камера-подготов выделите диапазон B3:C8:

Рис. 25. Выделение диапазона

Нажмите кнопку Камера. Вокруг выделенной области появилась мигающая дорожка. Щелкните в любом месте листа, чтобы создать изображение. Поменяйте в исходной области цвет шрифта / фон ячейки / значения, вставьте картинку поверх исходных ячеек. Изображение отразит все эти изменения. Верните данные к исходному состоянию (например, несколько раз нажав Ctrl+Z). К сожалению, нельзя создать изображение в Excel и вставить его в другое приложение Microsoft, например, PowerPoint. Это не сработает.

На листе Камера-подготов мы создадим четыре разные диаграммы. Выделите диапазон В2:В8. Вставьте круговую диаграмму (1 на рис. 26). Поместите ее в левой части листа. Снова выберите диапазон B2:C8. Вставьте объемную круговую диаграмму (2 на рис. 26). Поместите ее под первой диаграммой (рис. 27).

Рис. 26. Вставьте две круговые диаграммы

Рис. 27. Две круговые диаграммы

Можете добавить симпатичные выноски на объемную диаграмму. Для этого выберите диаграмму, нажмите на кнопку плюс; нажмите на Метки данных и выберите данные Выноски (рис. 28).

Рис. 28. Выноски на объемной диаграмме

Теперь добавьте две другие диаграммы. Выделите диапазон I2: J12. Вставьте гистограмму с группировкой. Переместите диаграмму под диапазон исходных данных. Снова выделите диапазон I2: J12. Вставьте линейчатую диаграмму с группировкой (рис. 29).

Рис. 29. Гистограмма и линейчатая диаграмма с группировкой

Отформатируйте диаграммы. Выберите первую диаграмму и перейдите на вкладку Конструктор (рис. 30). Выберите Стиль 1. Выберите стиль для второй диаграммы.

Рис. 30. Выбор стиля диаграммы

На листе Камера создайте две кнопки Переключателей, объедините их группой. Создайте еще две кнопки Переключателей, объедините их во вторую группу. Переименуйте группы (рис 31). Свяжите кнопки Переключатель для первой группы с ячейкой F2 листа Камера-подготов, для второй группы с ячейкой М2 Камера-подготов.

Рис. 31. Свойства левой группы

Теперь нужно создать четыре именованных диапазона, по одному для каждого графика. Именованный диапазон – это способ присвоить имя диапазону ячеек. Выберите область, которая полностью охватывает первую диаграмму (рис. 32). Для этого выберите ячейку В9. Нажмите и удерживайте клавишу Shift. Используйте клавишу со стрелкой вправо, чтобы расширить выделение до ячейки G9. Используйте клавишу со стрелкой вниз, чтобы расширить выделение на область В9:G23.

Рис. 32. Выделение области В9:G23

При выделенном диапазоне В9:G23 щелкните внутри поля имя (рис. 33). В нем отражается адрес левой верхней ячейки выделенного диапазона. Введите новое имя – SalesbyRegion2D (без пробелов). Нажать Enter. (Это важно. Если вы кликнули мышкой где-либо за пределами диапазона, повторите всё сначала.) Если вы сделали всё правильно, новое имя появится в поле имя (рис. 34).

Рис. 33. Поле имя

Рис. 34. Новое имя

Выделите диапазон В24:G38, содержащий объемную круговую диаграмму. Поименуйте этот диапазон SalesbyRegion3D. Если что-то пошло не так, или в последующем диапазон «слетит», перейдите на ленте на вкладку Формулы, кликните на кнопку Диспетчер имен. В открывшемся окне выберите имя и поправьте диапазон.

Повторите эти действия и присвойте имена диапазонам под двумя другими диаграммами.

Теперь у вас есть четыре разных диаграммы, и каждая диаграмма находится внутри своего собственного диапазона с индивидуальным именем. Поскольку кнопки Переключатель возвращают числа, нужно построить формулу, которая преобразует 1 или 2 в соответствующее имя.

На листе Камера-подготов в ячейку F3 введите формулу:

=ЕСЛИ(F2=1;"SalesbyRegion2D""SalesbyRegion3D")

Как это работает:

  • В первой группе на листе Камера пользователь нажимает одну из кнопок Переключателя.
  • Кнопка помещает в ячейку F2 значение 1 или 2.
  • Формула ЕСЛИ() считывает число и возвращает в ячейку F3 имя диапазона для соответствующей диаграммы.

В ячейку M3 листа Камера-подготов введите формулу:

=ЕСЛИ(M2=1;"SalesbyPersonBar";"SalesbyPersonColumn")

Воспользуемся тем, что изображение камеры может использовать в качестве источника именованный диапазон. Перейдите на лист Камера. Выберите любой диапазон (например, B10:B20). Нажмите кнопку Камера на панели быстрого доступа. Щелкните в любом месте, чтобы создать изображение. Посмотрите на строку формул. Там указан выбранный диапазон: =$B$10:$B$20. Его нужно изменить на значение, возвращаемое оператором ЕСЛИ().

Перейдите на вкладку Формулы. Щелкните Диспетчер имен. В открывшемся окне Диспетчер имен кликните Создать. В окне Создание имени введите имя диапазона SwitchPie (рис. 35) и в поле Диапазон формулу:

=ДВССЫЛ(‘Камера-подготов’!$F$3)

Рис. 35. Создание нового именованного диапазона

Что делает функция ДВССЫЛ? Она извлекает значение из ячейки F3 и вместо использования текстового значения в ячейке вычисляет это значение… которое просто оказывается одним из именованных диапазонов. Это одна из самых интуитивно непонятных функций Excel. Если вы хотите разобраться, рекомендую Примеры использования функции ДВССЫЛ.

Нажмите кнопку Создать еще раз. Создайте именованный диапазон SwitchBarColumn с формулой: =ДВССЫЛ(‘Камера-подготов’!$M$3). Перейти на лист Переключатель. Выберите ранее созданное изображение. При выбранном изображении измените в строке формул =$B$10:$B$20 на =SwitchPie. Изображение камеры изменилось, и отобразило диаграмму! Создайте новое изображение камеры. При выбранном изображении измените формулу изображения на =SwitchBarColumn. Всё готово. Используйте кнопки Переключатель для изменения вида графиков.

Еще раз кратко о том, что мы сделали в последнем разделе. Изображение камеры показывает на листе именованный диапазон. Именованный диапазон содержит диаграмму. Поэтому именованный диапазон показывает диаграмму. Это здорово, но статично, вы хотите изменять картинку, отражаемую инструментом Камера. Вы создали два оператора ЕСЛИ(), которые возвращают имена диапазонов, в которых находятся диаграммы. Вы создали третий именованный диапазон, который взял текст имени диапазона и преобразовал его в фактический объект имени диапазона с помощью функции ДВССЫЛ(). Этот третий диапазон стал источником изображения для Камеры.

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

Комментарии: 2 комментария

В чем преимущество этих сложных выкрутасов перед сводными диаграммами?

Антон, насколько я понимаю, в сводных таблицах с помощью срезов можно реализовать только Флажок. Остальные инструменты для сводных недоступны. Ну или сводные могут служить неким базисом для дальнейших манипуляций.


Прокомментировать