С помощью предыдущего поста создание таблицы с полосой прокрутки для дашбордов мы начали серию статей создания интерактивных таблиц для дашбордов в Excel. Отображение небольшой части списка с большим количеством пунктов с возможностью прокрутки было первым шагом. Дашборд с более глубоким анализом нуждается в больших возможностях. Одним из самых простых, но в то же время наиболее популярных техник, является возможность сортировки. Опять же, мы хотим, чтобы пользователь, выбрав критерии сортировки, увидел результат незамедлительно, не выходя их дашборда. То есть, нет необходимости переходить на лист с исходными данными, нет необходимости выбирать диапазон, нет необходимости использовать команды сортировки в меню или на ленте. И конечно, мы хотим все это сделать без использования макросов.
Решение
Таблица с нашими KPI выглядит также, как и в первой статье, только к ней добавились дополнительно 5 переключателей, находящихся под заголовком таблицы и наличия затемнения отсортированной колонки.
Для лучшего понимания скачайте файл с примером добавления сортировки в таблицу
Реализация
Нам необходимо будет внести небольшие изменения в наш дашборд: добавить кнопки-переключатели, связать их с соответствующими ячейками и добавить условное форматирование к колонкам. Также интересная часть нас ждет на листе расчеты. В Excel существует много возможностей сортировки с помощью формул. Большинство из них реализуются с помощью формул массивов – безусловно, самый элегантный способ, но и самый трудный для понимания. Ниже представлено пошаговое руководство с подсказками (может не такими элегантными, как формулы массивов, но более доступные для понимания)
Вставляем переключатели. Находим Разработчик –> Элементы управления –> Вставить -> Элементы управления формы –> Переключатель. Вставляем переключатели в заголовки таблицы дашборда.
Добавляем критерий сортировки. На листе Расчеты добавляем еще одну строку, которая для нас будет являться критерием сортировки. Для удобства я присвоил ей имя критерий_сортировки.
Устанавливаем связь. Жмем правой кнопкой мыши на любом из переключателей, выбираем Формат объекта и заполняем диалоговое окно, как показано на рисунке
Добавляем условное форматирование. Для лучшей читабельности я хочу, чтобы столбец, по которому проводится сортировка, закрашивался с темный цвет. Для этого применяем условное форматирование, выделяем первый столбец с KPI, на панели инструментов находим Главная –> Стили –> Условное форматирование -> Создать правило… В появившемся окне выбираем Использовать формулу для определения форматирующих ячеек, вбиваем формулу (в зависимости от номера столбца таблицы, значение критерия сортировки для сравнения будет соответствующим, например, для второго столбца формула будет =критерий_сортировки=2). И определяем нужный нам формат.
Данную процедуру повторяем для оставшихся четырех столбцов таблицы (не забываем менять формулу условного форматирования, в зависимости от номера столбца таблицы)
Настраиваем сортирующую таблицу. Начинается самое интересное. В листе Расчеты добавляем таблицу, которая, в зависимости от критерия сортировки, будет менять свои значения.
Как работает сортировка в дашборде
- Получаем значение сортируемого KPI (в зависимости от критерия сортировка), используя формулу СМЕЩ() (столбец D)
- Делаем каждое значение KPI уникальным, путем добавления очень маленькой цифры к каждому значению (столбец E)
- Сортируем список уникальных значений с помощью формулы НАБОЛЬШЕЕ() (столбец F)
- Используем формулу ПОИСКПОЗ() для определения позиции каждого значения в неотсортированном списке (столбец G)
- Собираем всю таблицу вместе, отсортированную по какому-либо KPI, используя столбец G и формулу СМЕЩ() (столбцы I:N)
Осталось внести небольшое изменение в таблицу дашборда – поменять ссылку в формуле СМЕЩ() на ячейку листа Расчеты. У нас получиться формула =СМЕЩ(Расчеты!I9;Расчеты!$D$4;0;1;1) для первого пункта в таблице. Теперь формула возвращает отсортированные данные с листа Расчеты. Протяните эту формулу на все показатели KPI и вниз, чтобы заполнить таблицу корректными формулами.
Итог
Итак, мы рассмотрели возможность добавления сортировки в нашу таблицу. Теперь для более тщательного и глубокого анализа, она у нас может показывать данные от большего к меньшему.
В следующей статье мы рассмотрим выделение ключевых показателей на основе процентилей.
Ренат, спасибо большое за предоставленный материал. Скажите, пожалуйста, зачем добавлять с уникальным значением KPI и почему нельзя сортировать и искать позицию по столбцу «Сортируемый KPI» ?
Данный трюк позволяет избежать ошибок в случаях, когда в разных строках будут находится одинаковые значения. В таком случае при попытке вернуть позицию по столбцу Сортируемый KPI, формула ПОИСКПОЗ два раза вернет значение первого попавшегося совпадения. Добавление тысячной доли к KPI позволяет точно находить его позицию
А не может получиться ситуация, когда KPI (n) +номер (n) /10000 будет равен KPI (m) + номер (m) /10000?
И как это поможет повлиять на поведение сортировки?
Спасибо!
Спасибо.
Скажите, а как можно осуществить сортировку не только по значениям числа, а и по алфавиту (но только разные виды сортировки должны присутствовать в одной таблице)?
Если я вас правильно понял, вас интересует настраиваемая сортировка. Она находится во вкладке Главная, в группе Редактирование. Кнопка Сортировка и фильтр -> Настраиваемая сортировка
Ренат, добрый день!
Спасибо за подробный и интересный пример!
Скажите пожалуйста, каким способом можно добавлять новые строки на Лист «Данные» , чтобы они автоматически добавлялись на Листе «Расчеты»? Существует ли элегантный способ без применения макросов?!
Владислав, к сожалению, исправить без макроса свойства элементов управления невозможно. В данном случае изменение максимального значения ползунка является ключевым моментом в изменении