Если Вы активно работаете с диаграммами в Excel, то наверняка знаете, что их в программе представлено достаточно много. Графики, гистограммы, линейчатые, круговые, лепестковые и т.д. Подходящий вариант для своих данных можно найти практически всегда. Однако, некоторые задачи визуализации решить стандартными средствами не получится.
Одна из самых распространенных проблем - отсутствие красивой и наглядной диаграммы для графического отображения единственного показателя (или, на языке диаграмм, единственной точки данных). Под это описание подходит, например, показатель KPI. Он обычно выражается в % в диапазоне от 0 до 100. Ни одна из встроенных диаграмм сходу не дает красивого и наглядного отображения такого показателя.
Но если проявить немного фантазии и навыков владения Excel, то можно создать собственные наглядные типы диаграмм на основе стандартных, которые красиво и легко решат поставленную задачу. Разберем построение трех типов таких диаграмм: Термометр, Круговая шкала и Спидометр. Если Вы не знаете как выполнить какое-то действие, указанное в статье, то можете посмотреть подробнейший пошаговый видеоурок по этой теме со всеми объяснениями. Видео найдете в конце статьи. Итоговый результат будет выглядеть вот так:
Термометр
Построение любой диаграммы начинается с подготовки исходных данных. В нашем случае у каждой диаграммы будет одна главная точка данных - показатель выполнения KPI в процентах от 0 до 100. Для наглядности мы также создадим цветную шкалу, которая будет обозначать зоны выполнения KPI (Плохо - Нормально - Хорошо). Это будет второй набор данных.
Таким образом, для построения диаграммы Термометр нам нужны следующие небольшие таблички с данными:
Обратите внимание на таблицу для шкалы. Проценты в ней указаны по следующему принципу:
- Плохая зона - первые 30% (то есть от 0% до 30%)
- Нормальная зона - следующие 40% (то есть от 30% до 70%)
- Хорошая зона - последние 30% (то есть от 70% до 100%)
Общая сумма процентов, очевидно, должна быть равна 100%.
Построение диаграммы
Теперь по этим данным можно строить диаграмму. Выделяем таблицу со шкалой и строим "Нормированную гистограмму с накоплением". Скорее всего, Excel построит три одинаковых столбца для каждого ряда: Плохо, Норма и Хорошо. Чтобы они приняли вид шкалы, надо выделить диаграмму и на вкладке "Конструктор" выбрать "Строка/столбец". После выполнения этой команды шкала обретет нормальный вид.
Далее нужно убрать с нее все лишнее: название, подпись ряда данных. Также необходимо настроить цвет каждой зоны на шкале, и ширину диаграммы в целом и самой шкалы. Для изменения ширины самой шкалы, кликаем на ней правой кнопкой мыши, выбираем "Формат ряда данных" и меняем значение ползунка "Боковой зазор". В итоге получится примерно следующее:
Теперь нужно на шкалу термометра добавить наш показатель KPI. Копируем его из нужной ячейки, выделяем диаграмму и нажимаем Ctrl+V. Скорее всего новые данные сольются с уже имеющейся шкалой и добавятся сверху зеленой зоны. Придется их донастроить вручную. Для начала меняем цвет самого верхнего сектора шкалы на черный. Затем кликаем правой кнопкой мыши на верхней части зеленой шкалы и выбираем "Изменить тип диаграммы для ряда". В этом окне надо будет установить для нового добавленного ряда галочку "Вспомогательная ось" и тип диаграммы - "Гистограмма с группировкой".
После нажатия кнопки OK нужно будет обязательно удалить вспомогательную ось с процентами и настроить ширину черного столбца ("ртутной полоски" нашего термометра). Останется только кликнуть на этом столбце правой кнопкой мыши и выбрать "Добавить подписи данных". Диаграмма-термометр готова!
Круговая шкала
Для построение диаграммы с круговой шкалой нам, помимо самого показателя KPI, также понадобятся дополнительные данные для построения шкалы. В данном случае все просто. Нам нужен столбец из единичек. Количество единиц = количество делений на нашей круговой шкале. Для создания двадцати делений нам нужен будет столбец из двадцати единиц. Кроме того, понадобится не только показатель выполнения KPI, но и количество процентов, которых не хватило до 100%. Его вычислить просто: 1 - KPI. Исходные данные имеют такой вид:
Построение диаграммы
Построение начинается с создания шкалы. Выделяем столбец с единицами и строим кольцевую диаграмму. Удаляем всё лишнее: название и легенду. По умолчанию, диаграмма будет иметь очень пестрый вид. Каждый из 20 секторов будет окрашен в свой цвет.
Нужно будет вручную перекрасить шкалу в правильные цвета. Кликаете на кольцо правой кнопкой мыши и применяете нужную заливку сразу ко всем секторам. Затем придется задать вручную цвет для некоторых секторов. Итак, у нас 20 секторов. Мы знаем, что первые 30% - плохая зона. То есть первые 20 * 30% = 6 секторов будут красные. Следующие 8 секторов (40%) - будут желтые. Последние 6 секторов - зеленые.
Чтобы покрасить один сектор, кликните на кольцо, чтобы выделились все сектора. Затем кликните еще раз прямо по нужному сектору. Выделится только он. Измените его заливку. Затем, нажимая Tab, перемещайтесь на каждый следующий сектор и выбирайте нужные цвета. В итоге должно получиться вот так:
Теперь добавим на шкалу второе кольцо поверх первого. У него будет всего 2 зоны: выполненный KPI и невыполненный KPI. Копируем два числа из таблицы, выделяем диаграмму и жмем Ctrl+V. Появится второе (внешнее) кольцо на диаграмме. Кликаете на него правой кнопкой мыши и выбираете "Изменить тип диаграммы для ряда". В окне изменения типа для Ряд2 включаете галочку "Вспомогательная ось". Второе кольцо наложится сверху на первое.
Останется поменять заливку верхнего кольца. Для зоны выполненного KPI полностью убираете всю заливку, а для зоны невыполненного - ставите белую и задаете прозрачность в районе 20% (это делается на панели "Формат точки данных"). Также не лишним будет вставить внутрь кольца надпись, которая будет отображать процент выполнения KPI числом (для большей наглядности).
Спидометр
Для построения диаграммы спидометр нам понадобится две вот такие таблички исходных данных:
В первой таблице данные для построения шкалы. Они такие же, как в диаграмме Термометр, но имеют дополнительную зону. Дело в том, что построить диаграмму-полукруг в Excel нельзя (а именно он нам нужен для шкалы спидометра). Но можно построить круг, половина которого будет просто пустая. Именной этой пустой половиной и является точка "Пусто" размером в 100%. Очевидно, что ее размер должен быть равен сумме трех зон шкалы: Плохо, Норма и Хорошо.
Для построения стрелки спидометра нам также понадобится построить круг из нескольких зон. В таблице они вычисляются так:
- До стрелки - зона, которая отвечает за положение стрелки. Равна показателю KPI;
- Стрелка - толщина самой стрелки. В итоге будет равна нулю, но при построении делаем равной 1-3% для удобства;
- После стрелки - зона от стрелки до конца шкалы спидометра. Рассчитывается так: 100% - До стрелки - Стрелка;
- Пусто - пустой полукруг. Всегда равен 100%.
Выделяем первую таблицу и строим кольцевую диаграмму. У самого большого "пустого" сектора убираем заливку, а трем другим задаем нужные цвета, которые соответствуют шкале. Также можно изменить толщину шкалы. Кликаете на ней правой кнопкой мыши и выбираете "Формат ряда данных". Затем двигаете ползунок "Диаметр отверстия, в % от общего диаметра" в нужное положение. Получаем вот такую шкалу (пока "поваленную" на бок).
Теперь надо добавить на нее стрелку. Кликаете на диаграмму правой кнопкой мыши и жмете "Выбрать данные". В окне выбора источника данных нажимаете "Добавить". Удаляете "={1}" и указываете, где расположены данные для нового ряда, а также задаете его имя (например, "Стрелка"). Закрываете окно нажатием ОК.
Теперь кликаете правой кнопкой мыши на внешнем кольце и выбираете "Изменить тип диаграммы для ряда". Для ряда, который назван "Стрелка", указываете тип "Круговая" и ставите галочку "Вспомогательная ось".
Для всех секторов внешней круговой диаграммы нужно поставить прозрачную заливку и убрать границы (контур). А для самого маленького сектора (наша стрелка), контур нужно сделать черным и толщиной, например, в 3 пт. Теперь наша стрелка отчетливо видна и в исходных данных можно установить ее размер на 0% (контур все равно останется). И не забудьте пересчитать размер сектора "После стрелки", если вы делали это вручную.
На данном этапе получаем вот такую диаграмму, уже очень похожую на то, что нужно.
Осталось развернуть ее на 90 градусов против часовой стрелки и немного украсить. Для разворота кликаете на любой сектор правой кнопкой мыши и выбираете "Формат ряд данных". На панели "Формат ряда данных" задаете "Угол поворота первого сектора" равным 270 градусов (поворачивать диаграмму можно только вправо, по часовой стрелке). Затем на вкладке "Формат" в группе команд "Текущий фрагмент" выберите в списке второй ряд (нашу шкалу). Задайте для нее такую же настройку поворота. Теперь и шкала, и стрелка развернуты в правильное горизонтальное положение. Для красоты можно добавить фигуру-круг в основание нашей стрелки. Получаем наглядную диаграмму в форме спидометра.
Таким образом, с помощью стандартных диаграмм и некоторых нехитрых приемов мы смогли создать в Excel сразу три новых типа визуализаций, которые отлично подходят для графического представления одного единственного показателя (KPI, % выполнения плана, загрузка мощностей и т.д.).
Если у Вас возникли трудности в процессе выполнения построений, описанных в статье, то можете посмотреть полное пошаговое видео с подробным разбором и объяснением каждой диаграммы.
Поддержать наш проект и его дальнейшее развитие можно вот здесь.
Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
С уважением, команда tDots.ru
