Как сделать динамический рейтинговый график в Excel для дашборда

В данном примере мы рассмотрим пошаговые действия при создании динамического Rating Chart для использования на дашбордах Excel. Цель визуализации – показать на чарте рейтинг итоговых значений по годам. При переключении между годами разработкой подразумевается выборка данных за конкретный учетный период. При выборе конкретного года чарт должен информативно подсвечивать курсором выбранное значение. Каждое взаимодействие визуализации с пользователем должно давать информативную обратную связь.

Создание структуры динамического управления из исходных данных

Имеется таблица итоговых данных по годам. Необходимо сделать красивый чарт для дашборда с информативной анимацией при переключении между годами.

Итоговые ежегодные значения

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

Шаг 1. Выделяем диапазон ячеек A2:B6 и выбираем инструмент: Insert – Tables – Pivot Table. Во появившемся диалоговом окне параметров сводной таблицы в поле ввода «Existing WorkSheet» укажем ссылку Sheet1!$A$10 для вставки на текущем листе. Далее настраиваем поля сводной таблицы в дополнительном окне настроек «PivotTable Fields» так как показано на рисунке (в нижнем правом углу рисунка):

  • Rows – Year;
  • Columns – Sales.
Настройка сводной таблицы

Шаг 2. Помещаем курсор Excel на любую ячейку в области сводной таблицы и выбираем инструмент: Insert – Filters – Slicer. В появившемся окне «Insert Slicers» отмечаем флажком опцию «Year» и жмем «Ok». Так мы создали элемент управления сводной таблицей. При нажатии на соответствующую кнопку среза данные фильтруются автоматически.

Подготовка исходных данных для чарта

Продолжаем заполнять таблицу исходных данных новыми значениями. Создаем 6 столбцов и заполняем названия заголовков в диапазоне ячеек C1:H1. Последние два столбца заполняем формулами:

  1. Cursor:
    =IFERROR(INDEX($B$13:$B$16,MATCH(A2,$A$13:$A$16,0)),0)
  2. Labels:
    =IF(G2=0,NA(),G2)
Фильтрация и выборка данных

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

Вторая формула служит для подписи курсора соответственными значениями на чарте. Принцип действия формулы прост – проверяется диапазон ячеек курсора G2:G7. Если значение равно 0, то в диапазон H2:H7 возвращается функция NA(), иначе значение курсора.

Далее осталось лишь заполнить диапазон C2:F7 формулой: =IF(AND($A2=C$1,$G2=0),$B2,0)

Исходные данные чарта

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

Строим динамический чарт на основе исходных данных таблицы

Сначала выделите диапазон C2:G7 и выберите инструмент: Insert – Charts – 2-D Area.

Создание динамического чарта в Excel

Обратите внимание! Диапазон ячеек второй серии данных для второго треугольника D2:D7 (2023 год) не содержит значений кроме 0. Но чарт подрисовывает треугольник на втором месте – это треугольник курсора (фиолетовый цвет). Потому что на срезе выбран год 2023 и формула автоматически перерисовывает чарт. Логику отображения всех треугольников при текущей выборке данных из сводной таблицы легко понять посмотрев на легенду внизу чарта.

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

Добавляем на чарт подписи данных под курсоры

Чтобы над каждым треугольником курсора была подпись соответственного значения сначала добавим еще один ряд данных на чарт. Для этого сначала выделите одним кликом сам чарт чтобы появилось дополнительные вкладки меню и там выберите инструмент: Chart Design – Data – Select Data.

Добавление серии данных на чарт

В появившемся окне параметров чарта «Select Data Source» в разделе Legend Entries (Series) нажмите на кнопку Add для вызова дополнительного окна «Add (Edit) Series». В первом поле «Series name:» ссылаемся на заголовок последнего столбца таблицы с помощью ссылки: =Sheet1!$H$2:$H$7. Так нам будет легче определить необходимый рад данных для его выбора при дальнейших действиях.

Второе поле «Series Values:» - заполняем ссылкой на диапазон последнего столбца исходной таблицы: =Sheet1!$H$2:$H$7.

Теперь у нас добавился еще один треугольник. Этому ряду данных следует задать иной тип чарта. Одним кликом левой кнопкой мышки выделите чарт и выберите инструмент: Insert – Charts – Scatter.

Изменение типа комбинированного чарта

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

  1. Снова кликом выделите чарт и из дополнительных вкладок меню выберите инструмент: Chart Design – Data – Select Data.
  2. В появившемся окне параметров чарта «Select Data Source» воспользуйтесь кнопкой «Hidden and Empty Cells».
  3. В появившемся окне «Hidden and Empty Cells Settings» в разделе «Show empty cells as:» переключитесь на опцию «Zero».
  4. Снимите флажок с опции «Show #N/A as an empty cell».
Исключение нулевых и пустых значений

В результате точки Scatter Chart будут отображаться только над треугольниками курсоров. Рабочий шаблон для интерактивной визуализации готов! Осталось лишь украсить его стильным оформлением.

Настройка оформления дизайна внешнего вида визуализации данных

Чтобы создать динамический дизайн чарта используйте градиентные заливки фигур с элементами прозрачности цветов.

Настройка дизайна чарта

Это уже менее важное задание. Сделайте все так как показано на видеоуроке в начале этой статьи. У вас все получиться! Но даже если возникнут трудности бесплатно скачайте готовый шаблон Rating Chart в конце статьи и на готовом примере рассмотрите детально принципы построения дизайна визуализации в целом.

Презентация рейтинга данных на визуализации

Скачать динамический рейтинговый график в Excel download file

Приложение MS Excel предлагает своим пользователям достаточно широкий выбор по настройке дизайна чартов. Привлекательный внешний вид визуализации данных существенно повышает эффективность презентации. Более того обратите внимание на интерактивные возможности! Все они реализованы без использования макросов. Только стандартные инструменты: формулы, сводные таблицы, срезы данных.

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

skachat-dashbord-dlya-upravleniya-kpi-planami Дашборд для управления KPI планами в Excel.

en ru