В сегодняшнем посте об индикаторах производительности дашбордов, мы рассмотрим, как можно визуализировать данные нашей таблицы. Возможности прокрутки, сортировки и выделения уже дают неплохие инструменты анализа (смотрим предыдущие статьи). Но они все еще отображают информацию в виде голых цифр. Пользователям будет сложно оценить относительные значения показателей с первого взгляда. Кроме того, зачастую приходится сравнивать значения KPI с другими показателями, например, средним значением или итоговой суммой.
Решение
Внедрение условно отформатированной линейчатой диаграммы непосредственно в ячейку дашборда, визуализирует данные и позволяет пользователю произвести быструю оценку ситуации. Диаграмма отображает относительное значение соответствующей ячейки, а условное форматирование позволяем сразу определить, какие значение ниже или выше целевого, и как они соотносятся со средним значением.
Для лучшего понимания, скачайте excel файл с визуализацией KPI дашборда
Реализация
- Необходимо подготовить рабочую книгу к новым возможностям, для этого добавляем пять дополнительных колонок к каждой колонке с KPI, две строчки на лист Данные для определения целевых значений и новые колонки в лист Расчеты.
- Добавьте 5 условно отформатированных диаграмм в лист Дашборд. Вы можете более подробно познакомиться с техникой создания диаграмм с условным форматированием статье. Используйте к качестве источников данных таблицы, созданные на листе Расчеты и целевое значение, как границу определяющую цвет заливки колонки (зеленый – выше цели, красный – ниже цели).
- Определите среднее значение на листе с расчетами и добавьте линию со средним значением в диаграммы, используя точечную диаграмму. Необходимые расчеты для 2-го и 3-го шагов вы сможете найти в таблицах, находящихся в колонках P:AP листа Расчеты.
- Отформатируйте диаграммы таким образом, чтобы видимыми остались только колонки и линия со средним значением, без осей, без линий сетки, без названия, без легенды, в общем, как сказал Эйнштейн: «Сделай настолько просто, насколько это возможно, но не проще».
- Отрегулируйте графики таким образом, чтобы они были в соответствии с диапазоном ячеек. Один совет: во время изменения размера диаграммы, удерживайте нажатой клавишу ALT, это позволяет задавать в качестве шага изменения размер ячейки, находящейся под диаграммой. Это упрощает процесс позиционирования.
Наша диаграмма почти готова, но есть один нежелательный эффект, когда мы прокручиваем таблицу дашборда вверх/вниз, максимальное значение горизонтальной оси меняется и кажется, что столбцы диаграммы «прыгают». Чтобы избежать этого, добавьте два дополнительных ряда данных с минимальным и максимальным значением KPI. Тем самым мы фиксируем горизонтальную ось. Чтобы не отображать эти фиктивные столбцы, уберите заливу и границу с этих графиков.
- Обновите подписи данных таблицы, чтобы объяснить смысл линии и цветов столбцов.
Итог
В сегодняшней статье мы визуализировали наш дашборд, добавив графики к каждому KPI. Теперь, глядя на него, пользователи смогут дать быструю оценку ситуации, не прибегая к дополнительным расчетам или переходам.
В следующей статье мы добавим возможность сравнения двух параметров, используя элементы управления формами.
Ренат, вы большой молодец! Пишите еще и почаще. У вас очень много всего полезного и интересного.
Единственный вопрос: в файле из данной статьи при прокрутке таблицы столбцы диаграмм не изменяются (они прикреплены только к первым десяти значениям). Нужно как-то усложнить формулу с использованием функции смещ в привязке к позиции ползунка.
Спасибо большое, Андрей. Приятно читать такие вещи.
Не совсем понял ваш вопрос, у меня при прокрутке ползунка графики тоже обновляются, так как источник данных для графиков зависит от значений в таблице
Полезная информация, спасибо, только не понял: как задать формат, чтобы график в таблице содержал только зеленую и красную колонку, у меня мелкий график отражается в каждой ячейке со средним значением?!
Если я правильно понял вас, Вячеслав, вам необходимо изменить вид графика со средним значением. Для этого выбираете правым щелчком мыши ряд данных со средним значением -> из выпадающего меню выбираете Изменить тип диаграммы для ряда -> в появившемся меню выбираете тип — график
спасибо, понял
Спасибо..
Скажите, а как менять подписи горизонтальной оси для каждого ряда по отдельности? Не получается, кликаю «изменить»(подписи горизонтальной оси ), меняю на нужное, а подписи автоматически меняются для всех. Что не так? Может что-то в параметрах поставить?
Чтобы задать подписи горизонтальной оси, создайте массив ячеек, где будут хранится значения подписей, затем укажите его в качестве источника, щелкнув по ИЗМЕНИТЬ (Подписи горизонтальной оси)
Ренат, здравствуйте!
Можете, пожалуйста, подробнее написать о процедуре введения «среднего значения» на график. Сколько не пытался, у меня при изменении типа диаграммы он меняется на горизонтальную линию.
Заранее благодарю.
Евгений, весь фокус в том, что после того, как вы добавите новый ряд данных, необходимо изменить тип диаграммы с График на Точечная с прямыми отрезками. Затем определить координаты по оси X и Y для ряда данных Среднее. По оси Y у вас должна быть последовательность от 1 до 10 (в нашем случае), а по оси X — ряд данных со средним значением. Также прикладываю файл, чтобы легче было разобраться
Файл: Vertical_average.xlsx