Выделение KPI используя перцентили в Excel

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

таблица дашборда с сортировкой

Представьте, что вы видите первые десять строк, отсортированных по первому KPI (рисунок слева). Мы видим, что «Продукт 36» является наиболее значимым для KPI 1. Но как он соотносится с KPI 3? Вероятно, значение 2% мало, но насколько мало? Конечно, мы можем изменить критерий сортировки KPI 3 и, прокрутив вниз, найти продукт 36. Но смена сортировок от одного к другому, занимает много времени и не удобно.

Решение

таблица дашборда с выделением

Одним из статических методов изучения списков данных является перцентиль. Перцентиль – это значение переменной, которая определяет границу между показателями находящимися выше и ниже этого значения. Т.е. 10-й перцентиль третьего KPI — это значение KPI, ниже которого расположено 10% результатов наблюдений. Мы будем использовать этот метод распределения для показателей, которые не были отсортированы, путем выделения 90-го перцентиля зеленым (10% наилучших показателей) и выделения 10-го перцентиля красным.

После того как мы выделим перцентили, мы сможем сказать, что продукт 36 является лучшим по первому показателю производительности, но одним из худших по показателям KPI 3.

Для понимания дальнейших шагов реализации, скачайте файл выделение KPI с использованием формулы ПЕРСЕНТИЛЬ()

Реализация

Для реализации нашей задумки нам понадобится условное форматирование и функция ПЕРСЕНТИЛЬ(). Синтаксис формулы: ПЕРСЕНТИЛЬ(массив; k), где ‘массив’ – диапазон значений и ‘k’ – значение перцентиля от 0 до 1. ПЕРСЕНТИЛЬ(A1:A100; 0,1) возвращает пороговое значение, при котором 10% всех значений в диапазоне меньше этого значения, а остальные 90% — больше.

Описание шагов по добавлению выделений в таблице дашборда:

  • Добавим две дополнительные строки на лист Данные, для определения верхнего и нижнего значения перцентиля.
  • К каждому столбцу с данными в таблице дашборда добавим еще дополнительные столбцы (справа)
  • Для упрощения формулы, добавим позицию столбца KPI в заголовки таблицы (E5 = 1; G5 = 2 и т.д.)
  • Заполняем новые колонки формулой (пример для ячейки F6):

=ЕСЛИ(критерий_сортировки=E$5;»»;ЕСЛИ(E6>ПЕРСЕНТИЛЬ(Расчеты!$J$10:$J$109;Данные!$D$2);»◄+»;ЕСЛИ(E6<ПЕРСЕНТИЛЬ(Расчеты!$J$10:$J$109;Данные!$D$3);»◄-«;»»)))

как расчитывается перцентиль в excelОписание формулы: если по текущей колонке проводится сортировка, возвращает пустую ячейку («»), в противном случае, если значение ячейки слева больше, чем 90-й перцентиль, возвращает «◄+», если значение меньше, чем 10-й перцентиль, возвращает «◄-«. Для остальных значений, возвращает пустую ячейку.

  • Определяем формат ячеек новых столбцов в виде красного шрифта и добавляем условное форматирование на закраску шрифта зеленым, если значение ячейки будет равняться «◄+».
  • И наконец, добавляем легенду внизу таблицы, чтобы пользователи поняли, что означают красные и зеленые треугольники.

условное форматирование для индикации перцентиля

Напоследок

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

Итог

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


4 комментария

  1. Лучше использовать для ввода символа ► сочетание клавиш Alt 16 (цифры — набираем на цифровой клавиатуре), а для ◄ соответственно Alt 17
    (так просто быстрее, чем заходить во вставку символов)

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *