Динамическая круговая диаграмма для KPI счетчика дашборда Excel

Как сделать счетчик KPI на дашборде в Excel, показывающий превышение плана? Круговые и лепестковые диаграммы не являются предпочтительными с точки зрения скорости обработки данных в Excel. Но их внешний вид наиболее привлекательный на информативных дашбордах. Поэтому во многих решениях Business Intelligence (таких, как Tableau или Power BI) пользователи чаще предпочитают использовать счетчики, чем комбинированную диаграмму. Тем не менее, они всегда сталкиваются с одной и той же проблемой – как показать с помощью счетчика перевыполнение плана более чем на 100%?

Пример как сделать круговую диаграмму KPI в процентах

Для этого графика не потребуется многого данных. Первое число – это показатель выполнения плана (в нашем примере принимающий значения от 0 до 199) - для простоты в ячейке B3, где указывается факт выполнение плана не используются проценты. Благодаря полю "Название" – B1, мы добавляем необходимый нам символ «%» для отображения показателей выполнения плана в процентах.

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

Фактически данная круговая диаграмма принимает только 2 значения, которые влияют на ее внешний вид и динамически могут изменяться – это очень удобно.

Подготовка данных для диаграммы KPI с процентами

Создание рядов данных на лепестковой диаграмме. Значения оси Х будут последовательными градусами на круге, поэтому вычисляем значения оси Y для 360 точек на оси Х (т.е. круга). Значения оси Y будут находиться в диапазоне от 0 до 1, ось Y будет радиусом большого, то есть внешнего круга.

Заполняем три ряда исходных данных:

1. Для внешнего круга.
2. Для среза данных.
3. Для линии, которая завершит большой круг (далее в статье будет объяснена цель ее использования).

Первый ряд данных

Это будет граница внешнего круга. Радиус этого круга будет постепенно уменьшаться от 1 до 0,8 - т.е. одного из параметров, установленного в начале (часть, которая будет составлять малый круг по отношению к большому).

Вводим следующую формулу в ячейку A1.

=1-(СТРОКА()-1)*(1-0,8)/360

Внимание! Ввод формулы следует выполоть в первой строке, потому что у нас есть вложенная функция СТРОКА(). Учитывайте следующие аспекты:

1. Результатом вычисления формулы является радиус круга на данной позиции (принимающей значения от 1 до 360, то есть на номере строки)
2. Из единицы 1 вычитаем для каждого положения (СТОКА()-1) соответствующую часть разности между большим и малым кругом ((1-0,08)/360).
3. В каждой последующей строке, т.е. с каждым новым положением на круге, мы приближаемся к значению 0,8.

Второй ряд

Создан только для того, чтобы дать первой и последней строке (360-ой) значение 1 для графического дополнения ряда данных большого круга.

В ячейку C1 вводим формулу:

=(СТРОКА()=1)+(СТРОКА()=360)

Функция возвращает значение 1 в первой строке (в 1-ом градусе круга), и в 360-ой.

Третий ряд

Будет являться участком с переоценкой, следовательно, формула должна возвращать значение только для части с переоценкой, что в нашем случае будет составлять 10%((110-100)/100=10%), то есть 36 градусов (360*10%). Только участок диапазона от 1 до 36 градусов будет окрашен другим цветом (только в этих строках и в строке 360 должны быть значения).

В ячейку B1 вводим формулу:

• (СТРОКА()<=3,6*(110-100)) – первая часть формулы гарантирует, что значения появятся только в первых 36 строках т.е. до 36 градусов;
• (0,8-СТРОКА()*((1-0,8)/360)) – также, как и в формуле для первого ряда данных, эта часть формулы показывает каким образом в зависимости от строки, в которой находится круг относительно малого фрагмента, будет пропорционально сдвигаться по отношению к большому кругу;
• (СТРОКА()=360)*(0,8-(1-0,8)/360) – эта часть формулы предназначена для последней (360-ой) строки, и обеспечивает ей то же значение (Y), что и в первой строке - для того чтобы фрагмент был заполнен и красиво залит цветом.

Создание диаграммы

Указанные формулы должны находиться в диапазоне от A1 до C360. (столбец A - первый ряд данных, столбец B - второй ряд данных, столбец C - третий ряд данных).

Выберите инструмент данные через меню: «ВСТАВКА»-«Диаграммы»-«Биржевая»-«Лепестковая»-«Заполненная лепестковая»

При увеличении вставленного графика четко видно 2-ой ряд данных – он по умолчанию выделен оранжевым цветом.

Примечание! Если удалить ряд номер 3 сразу будет видно небольшой промежуток между данными (360 и 1). Вот почему нужен ряд номер 3.

Значения вокруг круга – это значения оси X, а вертикальная ось с диапазоном от 0 до 1 – это ось Y соответственно. Чтобы не портить вид, удаляем метки значений категорий осей X и Y. Для этого:

1. Делаем двойной щелчок левой кнопкой мыши по первому ряду.
2. Выбираем инструмент «Формат ряда данных»-«Параметры ряда»-«Подписи категорий» и снимаем "галочку". Благодаря этой операции удалены параметры оси X.

3. Чтобы удалить параметры оси Y, делаем щелчок по самой оси и нажимаем клавишу «Delete» на клавиатуре. Чтобы удалить все остальные лишние элементы щелкаем по плюсу возле диаграммы и снимаем галочки на всех доступных элементах.

На этом этапе можно было закончить обсуждение темы, но важно рассмотреть еще один трюк.

Магический эффект - скрытие рядов данных под присвоенными именами

Если бы мы построили такую диаграмму обычным способом, наш лист с данными выглядел бы с переполненным ненужной информацией. Столбцы со значениями диапазона ячеек A1:C360 можно скрыть. Однако мы будем скрывать ряды данных в диспетчере имен под определенными именами, одновременно объединяя формулы. Благодаря этому они будут использовать только два входящих значений, упомянутых в начале:

1. Отношение малого круга к большому - «Параметр» (например, значение в ячейке B2=0,9).
2. Процент выполнения плана - «Факт» (например, значение в ячейке B3=125).

Заполним таблицу этими исходными значениями на новом листе Excel, как показано ниже на рисунке:

Сначала вводим ссылки на эти параметры непосредственно в диспетчере имен. Для этого выберите инструмент: «Формулы»-«Диспетчер имен». Или нажмите комбинацию горячих клавиш CTRL+F3:

После чего нажмите на кнопку создать.

Вводим в поле «Имя:» значение - Параметр и в поле «Диапазон:» ссылку на ячейку B2, а затем ОК. Те же самые шаги проделываем для второго параметра.

Как видно на рисунке ниже, в результате этих действий в Диспетчере имен появилось два диапазона с присвоенными именами:

Следующим шагом будет внесение таких изменений в ранее созданные формулы, которые позволят формулам ссылаться на созданные присвоенные имена и работать внутри Диспетчера имен, а не в диапазоне ячеек на листе.

Ряд данных 1

=1-(СТРОКА()-1)*(1-0,8)/360

1. СТРОКА() изменяем на СТРОКА($1:$360) – Зачем? В присвоенных именах, для вычисления значений в рядах данных будут использованы формулы массива, которые позволят не размещать формулы в последующих ячейках, а вместо СТРОКА($1:$360) появится таблица значений <1; 360>.
2. 0,8 изменяется на присвоенное имя, т.е. «Параметр» - это позволит динамически изменять диаграмму. Достаточно будет лишь поменять значение в ячейке B2 на которую ссылается данное имя «Параметр».

Формула после внесенных изменений будет иметь вид:

=1-(СТРОКА($1:$360)-1)*(1-Параметр)/360

Вносим новую формулу в Диспетчер имен (в поле «Диапазон») и присваиваем ей имя "Круг", не забывая, что после ввода формулы необходимо нажать Ctrl + Shift + Enter на клавиатуре, что запустит ее как формулу массива.

То, что формула работает как формула массива также будет видно в Диспетчере имен (в столбце "Значение" будут отображаться фигурные скобки {…}.

Аналогично, делаем с рядами данных 2 и 3.

Формула для второго ряда:

=(СТРОКА($1:$360)= 1)+(СТРОКА($1:$360)=360)

будет присвоено имя «Линия».

Формула для третьего ряда:

будет присвоено имя "Фрагмент".

Когда всем рядам присвоены имена, переходим к их размещению на диаграмме.

В нашей таблице еще недостает названия, которое будет отображать обычное числовое значение как проценты (без формата ячеек) с помощью простой формулы =Факт&"%". Она ссылается на одно из имен:

Теперь выделяем таблицы и выбираем, как и раньше, тот же самый вид диаграммы: «ВСТАВКА»-«Диаграммы»-«Биржевая»-«Лепестковая»-«Заполненная лепестковая».

Щелкаем левой кнопкой мышки по диаграмме чтобы активировать дополнительное меню: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР», в котором выбираем инструмент «Выбрать данные», как показано ниже на рисунке:

В появившемся диалоговом окне «Выбор источника данных», в левой секции «Элементы легенды (ряды)» все удаляем.

Затем размещаем новые ряды данных с присвоенными именами. Ниже приведен первый пример использования присвоенного имени в качестве ряда данных для диаграммы, в место ссылок на значения листа Excel.

ВНИМАНИЕ! Следует вспомнить конструкцию ссылки на имена – сначала указывается название файла рабочей книги Excel Счетчик KPI.xlsx, в котором определена переменная с именем «Линия», а между ними знак восклицания «!». В итоге ссылка имеет следующий вид ='Счетчик KPI.xlsx'!Линия:

Повторим те же шаги для последующих рядов данных.

В результате получена диаграмма такая же, как и та что выполнена первым способом. Ее конечно же следует еще отформатировать, но выше уже обсуждались этапы и возможности как это сделать. Тем не менее, диаграмма является динамически обновляемой, поскольку теперь для нее источником данных являются значения в ячейках B2 и B3. Можно легко проверить как она измениться в зависимости от значений этих параметров.

Используя эти же методы, в завершении был создан новый дополнительный ряд данных диаграммы, который отвечает за значения, возникающие в случае если план не был выполнен. Он называется «Фрагмент2» с формулой в имени:

По этой причине была изменена формула в имени «Фрагмент» на:

А также в имени «Круг»:

Далее добавляем к созданной таким образом лепестковой диаграмме фигуру: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Овал»:

Щелкните левой кнопкой мышки по фигуре, чтобы активировать ее и введите в строку формул ссылку на ячейку =$B$1 после чего нажмите Enter. В результате содержимое ячейки B1 будет отображаться в качестве надписи для фигуры. Фигура со значением показателя выполнения плана KPI в качестве надписи (ссылка на ячейку "Название") размещается на диаграмме и группируется с ней выделив их обоих и выбрав из контекстного меню опцию Группировать. Так мы получаем счетчик.

Пример использования шаблона диаграммы KPI на дашборде Excel

Скачать шаблон круговой диаграммы KPI в Excel

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

Диаграмма, которая представлена в данном уроке, является особенной т.к. обладает возможностью отображать перевыполнение планов в процентном соотношении. Более того, у нее также интересная конструкция, основанная на присвоенных именах диапазонам – для обычного пользователя Excel, созданная таким образом диаграмма будет очень удобной.