Добавление возможности сортировки по KPI для дашборда

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

Решение

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

Таблица с нашими KPI выглядит также, как и в первой статье, только к ней добавились дополнительно 5 переключателей, находящихся под заголовком таблицы и наличия затемнения отсортированной колонки.

Для лучшего понимания скачайте файл с примером добавления сортировки в таблицу

Реализация

Нам необходимо будет внести небольшие изменения в наш дашборд: добавить кнопки-переключатели, связать их с соответствующими ячейками и добавить условное форматирование к колонкам. Также интересная часть нас ждет на листе расчеты. В Excel существует много возможностей сортировки с помощью формул. Большинство из них реализуются с помощью формул массивов – безусловно, самый элегантный способ, но и самый трудный для понимания. Ниже представлено пошаговое руководство с подсказками (может не такими элегантными, как формулы массивов, но более доступные для понимания)

Вставляем переключатели. Находим Разработчик –> Элементы управления –> Вставить -> Элементы управления формы –> Переключатель. Вставляем переключатели в заголовки таблицы дашборда.

кнопки переключатели excel

Добавляем критерий сортировки. На листе Расчеты добавляем еще одну строку, которая для нас будет являться критерием сортировки. Для удобства я присвоил ей имя критерий_сортировки.

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

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

форматирование переключателей

Добавляем условное форматирование. Для лучшей читабельности я хочу, чтобы столбец, по которому проводится сортировка, закрашивался с темный цвет. Для этого применяем условное форматирование, выделяем первый столбец с KPI, на панели инструментов находим Главная –> Стили –> Условное форматирование -> Создать правило… В появившемся окне выбираем Использовать формулу для определения форматирующих ячеек, вбиваем формулу (в зависимости от номера столбца таблицы, значение критерия сортировки для сравнения будет соответствующим, например, для второго столбца формула будет =критерий_сортировки=2). И определяем нужный нам формат.

Данную процедуру повторяем для оставшихся четырех столбцов таблицы (не забываем менять формулу условного форматирования, в зависимости от номера столбца таблицы)

условное форматирование excel

Настраиваем сортирующую таблицу. Начинается самое интересное. В листе Расчеты добавляем таблицу, которая, в зависимости от критерия сортировки, будет менять свои значения.

Как работает сортировка в дашборде

как работает сортировка в дашборде

  • Получаем значение сортируемого KPI (в зависимости от критерия сортировка), используя формулу СМЕЩ() (столбец D)
  • Делаем каждое значение KPI уникальным, путем добавления очень маленькой цифры к каждому значению (столбец E)
  • Сортируем список уникальных значений с помощью формулы НАБОЛЬШЕЕ() (столбец F)
  • Используем формулу ПОИСКПОЗ() для определения позиции каждого значения в неотсортированном списке (столбец G)
  • Собираем всю таблицу вместе, отсортированную по какому-либо KPI, используя столбец G и формулу СМЕЩ() (столбцы I:N)

Осталось внести небольшое изменение в таблицу дашборда – поменять ссылку в формуле СМЕЩ() на ячейку листа Расчеты. У нас получиться формула =СМЕЩ(Расчеты!I9;Расчеты!$D$4;0;1;1) для первого пункта в таблице. Теперь формула возвращает отсортированные данные с листа Расчеты. Протяните эту формулу на все показатели KPI и вниз, чтобы заполнить таблицу корректными формулами.

Итог

Итак, мы рассмотрели возможность добавления сортировки в нашу таблицу. Теперь для более тщательного и глубокого анализа, она у нас может показывать данные от большего к меньшему.

В следующей статье мы рассмотрим выделение ключевых показателей на основе процентилей.


7 комментариев

  1. Ренат, спасибо большое за предоставленный материал. Скажите, пожалуйста, зачем добавлять с уникальным значением KPI и почему нельзя сортировать и искать позицию по столбцу «Сортируемый KPI» ?

    • Данный трюк позволяет избежать ошибок в случаях, когда в разных строках будут находится одинаковые значения. В таком случае при попытке вернуть позицию по столбцу Сортируемый KPI, формула ПОИСКПОЗ два раза вернет значение первого попавшегося совпадения. Добавление тысячной доли к KPI позволяет точно находить его позицию

      • А не может получиться ситуация, когда KPI (n) +номер (n) /10000 будет равен KPI (m) + номер (m) /10000?
        И как это поможет повлиять на поведение сортировки?

        Спасибо!

  2. Спасибо.
    Скажите, а как можно осуществить сортировку не только по значениям числа, а и по алфавиту (но только разные виды сортировки должны присутствовать в одной таблице)?

  3. Ренат, добрый день!

    Спасибо за подробный и интересный пример!
    Скажите пожалуйста, каким способом можно добавлять новые строки на Лист «Данные» , чтобы они автоматически добавлялись на Листе «Расчеты»? Существует ли элегантный способ без применения макросов?!

Добавить комментарий для Ренат Лотфуллин Отменить ответ

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