Календарный дашборд с Power Pivot
Некоторое время назад я уже разбирал пару способов создания сводной таблицы с текстом в области значений - при помощи формул и Power Query. И, наверное, правильнее было бы назвать это всё-таки имитацией сводной, т.к. получившаяся в результате таблица полноценной сводной по факту не является, а значит и не позволяет нам использовать всё её возможности и преимущества типа фильтрации срезами, вычисляемых полей, стилей и гибкой настройки структуры.
Обойти эту трудность можно с помощью надстройки Power Pivot и сводной на основе Модели Данных, которая позволяет использовать в области значений меры - пользовательские формулы на языке DAX (что-то типа вычисляемых полей, но гораздо круче по возможностям). С помощью специальной хитрой меры, которую мы сделаем, возможно выводить в области значений сводной не только агрегированные числовые итоги (сумму, количество, среднее и т.д. как в обычных сводных), но и текст. Что нам как раз и требуется.
В результате всё будет выглядеть примерно так:
Исходные данные
В качестве рабочего сценария представим себе небольшую транспортную компанию, в парке которой есть машины разных типов (грузовая газель, пассажирский микроавтобус, лимузин). Эти машины в определённые дни назначаются на принятые заказы по заданной стоимости.
Главным исходником у нас будет таблица заказов на первые 3 месяца 2024 года (она могла бы выгружаться из какой-нибудь CRM или 1С), которую мы конвертируем в умную (Ctrl+T) и даём ей на вкладке Конструктор (Design) имя Orders:
Кроме этого нам потребуется ещё одна вспомогательная справочная таблица - таблица дат (календарь). Её тоже можно сделать динамической "умной" и дать ей имя Calendar. В ней в интересующем нас временном интервале (1 квартал 2024 года или даже больше, если делать про запас) детально расписываются параметры каждого дня: номер месяца и недели, название месяца и т.д., которые будут нужны нам впоследствии для фильтрации и формирования календаря. Вычислить эти параметры можно легко с помощью встроенных функций Excel:
- МЕСЯЦ(дата) - номер месяца
- НОМНЕДЕЛИ.ISO - номер недели по принятому в России стандарту
- ДЕНЬНЕД(дата;2) - номер дня недели (пн=1, вт=2... вс=7)
- ТЕКСТ(дата; "ДДД") - название дня недели текстом ("Пн", "Вт"... "Вс")
- ТЕКСТ(дата; "МММ") - название месяца текстом ("янв", "фев"... "дек")
Ещё одна важная причина использования таблицы дат состоит в том, что потенциально в таблице заказов в столбце дат могут быть пропуски - дни, когда не было ни одного заказа. И тогда эти дни не будут отображаться в нашем календаре-дашборде, что плохо. Если же использовать для построения календаря таблицу дат, то таких некрасивых "дырок" у нас не получится.
Загружаем таблицы в Модель Данных и связываем
Теперь нужно загрузить наши умные таблицы Orders и Calendar в Модель Данных текущего файла, чтобы дальше можно было работать с ними в надстройке Power Pivot.
Если она у вас ещё не подключена, то сделать это можно через Файл - Параметры - Надстройки - Надстройки COM - Перейти (File - Options - Add-ins - COM Add-ins - Go to), включив соответствующий флажок в списке:
Если такой опции у вас здесь нет, то это значит, что ваша версия Excel надстройку Power Pivot в своём составе не содержит в принципе. Это решается переходом на другую лицензию или переустановкой Office на вариант, где она внутри имеется.
В идеале, после подключения должна появиться вкладка Power Pivot, где с помощью кнопки Добавить в модель данных (Add to Data Model) мы по очереди загружаем наши таблицы заказов и дат в Модель:
После загрузки в открывшемся окне надстройки Power Pivot переключаемся в режим диаграммы на вкладке Главная - Представление диаграммы (Home - Diagram View) и связываем наши таблицы по полю Дата, перетащив его из одной таблицы в другую (в любом направлении):
Обратите внимание, что со стороны календаря на нашей связи будет единичка - символ того, что в этой таблице каждая дата встречается только 1 раз (т.к. это справочник), а со стороны таблицы заказов будет звёздочка - обозначение того, что здесь даты могут повторяться (т.к. это таблица факта). Стрелка на линии связи показывает направление распространения фильтров.
Здесь же сразу имеет смысл настроить сортировку, т.к. по умолчанию в сводной таблице текстовые названия месяцев и дней недели будут сортироваться тупо по алфавиту. Чтобы это починить, возвращаемся в режим таблицы на вкладке Главная - Представление данных (Home - Data View), выделяем столбец Месяц и жмём на кнопку Сортировка по столбцам (Sort by columns). В открывшемся окне выбираем столбец с текстовым названием месяца и сортируем его по номеру месяца:
Затем то же самое проделаем для столбца с текстовым названием дня недели, отсортировав его по номеру дня недели.
Строим сводную и пишем меры
Теперь можно построить сводную по созданной связке таблиц Orders и Calendar - для этого в окне Power Pivot выбираем команды Главная - Сводная таблица (Home - Pivot table) и задаем новый лист в качестве места вывода. Затем:
- закидываем номер недели - в область строк
- закидываем день недели текстом - в область столбцов
- общие итоги по строкам (внизу) и по столбцам (справа) убираем, щёлкнув по ним правой кнопкой мыши и выбрав команду Удалить общий итог (Remove Grand total)
- щёлкаем правой кнопкой мыши в списке полей справа по полям Месяц и Машина и выбираем команду Добавить как срез (Add as slicer)
В итоге должно получиться примерно так:
Дальше самое интересное. В обычной сводной таблице в области значений мы можем отображать только агрегированные числовые итоги (сумму, количество, среднее и т.п.) В сводной же построенной по Модели Данных, наши возможности гораздо шире - мы можем выводить что угодно.
Для отображения подробностей по заказам за каждый день в области значений нам нужно создать меру - специальную формулу на встроенном в Power Pivot языке DAX. Удобнее всего это сделать с помощью команды Меры - Создать меру на вкладке Power Pivot (Measures - Create measure). В открывшемся окне задаём любое подходящее имя меры, выбираем таблицу для хранения и - самое главное - вводим формулу DAX для расчёта:
Логика тут следующая:
-
Для того, чтобы собрать все заказы на каждую из дат и склеить их в единый текст нам потребуется функция CONCATENATEX. Первый аргумент этой функции - таблица, по которой мы проходим (Orders), а второй - выражение, которое нужно выполнить для каждой строки этой таблицы.
-
Мы хотим для каждого найденного заказа сцепить (с помощью символа &) между собой время заказа из столбца [Время], его описание из столбца [Событие] и деньги из столбца [Стоимость].
-
Если хочется, чтобы время отображалось без секунд, то ссылку на столбец Orders[Время] нужно завернуть в DAX-функцию FORMAT, которая умеет выводить исходное значение в заданном формате.
-
Третий аргумент функции CONCATENATEX - это разделитель, который должен быть вставлен между найденными событиями (если их на одну дату нашлось несколько). Чтобы вставить переход на новую строку, используем сочетание Alt+Enter, не забывая взять его в кавычки.
После нажатия на ОК и добавления созданной меры в сводную таблицу в область Значений, увидим следующую картину:
Видно, что мы на верном пути, но требуется немного допилить дизайн:
-
Чтобы стало видно добавленные переносы строк, выделим всю сводную (это можно быстро сделать, "поймав" чёрную стрелку на верхней границе ячейки B3) и включаем для неё перенос по словам кнопкой Переносить текст на вкладке Главная (Home - Wrap Text).
-
Если выбрать на срезе февраль, то можно заметить, что в нашем отчёте пропадает столбец Ср, а в марте не видно 12-й недели т.к. в эти периоды у нас не было ни одного заказа. Чтобы в сводной отображались даже те дни и недели из календаря, где не было данных, щёлкнем по сводной правой и выберем Параметры сводной таблицы - Вывод - Показывать элементы без данных в строках/столбцах (Pivot Table Options - Output - Show elements with no data).
-
Настроить ширину столбцов и зафиксировать её, щёлкнув по сводной правой кнопкой мыши и выбрав команды Параметры сводной таблицы - Автоматически изменять ширину столбцов при обновлении (Pivot Table Options - Autofit column widths on update).
Добавляем подписи дат
При желании, можно легко добавить в наш отчёт подписи дат для каждого дня, чередуя их с описаниями заказов через строку. Для этого лучше создать ещё одну меру со следующей формулой:
К сожалению, самый очевидный вариант формулы типа =Calendar[Дата] здесь не сработает, т.к. Power Pivot не понимает, что у нас в этом столбце все даты уникальные и ругается на попытку вывести несколько значений в одной ячейке. Но если мы завернём этот столбец в любую агрегирующую функцию, возвращающую одно скалярное значение (MIN, MAX, SUM, AVERAGE...), то ошибки уже не будет, а результат останется тем же. Ну, и функцию FORMAT можно добавить, если подходящего формата в выпадающем списке в нижней части окна нет.
После этого закидываем созданную меру в область значений над мерой Подробности и переносим появившееся после этого поле Σ Значения в область строк под недели:
Чтобы зафиксировать высоту строк с подробностями (а она у нас гуляет, т.к. в разные дни бывает разное количество заказов), просто выделим любую ячейку с названием поля в столбце C и вручную переименуем, добавив пару переносов строк Alt+Enter после названия в роли "распорки":
Наводим блеск и лоск
Для выделения дат заливкой можно включить флажок Чередующиеся строки на вкладке Конструктор (Design - Banded rows) и подобрать подходящий стиль там же. Особо дотошные могут даже создать свой стиль сводной таблицы и настроить цвета заливки и текста детально для каждого элемента, например, для выходных.
Столбцы B и C можно скрыть, конечно, как и 3-ю строку. В итоге должно получиться примерно так:
Для полного счастья можно ещё добавить правило условного форматирования для заливки ячеек, где есть заказы. Для этого нужно выделить несколько ячеек с заказами и выбрать Главная - Условное форматирование - Создать правило (Home - Conditional formatting - Create rule), а затем задать следующие параметры:
После нажатия на ОК подсветка начнёт работать не только в выделенных ячейках, но и во всей сводной таблице:
Добавляем карточки с итогами
В принципе, на этом уже можно было бы остановиться, но полного и окончательного счастья давайте добавим над нашим отчётом динамические итоги в виде карточек. Для этого создадим три простых меры с интересующими нас KPI:
Общая стоимость = SUM(Orders[Стоимость])
Заказов = COUNTROWS(Orders)
Загруженность = DIVIDE([Заказов];DISTINCTCOUNT(Calendar[Дата]))
Вставляем над таблицей несколько пустых строк и помещаем туда еще одну сводную таблицу, используя уже знакомую команду Сводная таблица в окне Power Pivot. В область значений закидываем нашу меру и настраиваем внешний вид:
Автоматический подбор ширины для неё лучше отключить, как мы делали ранее с большой таблицей.
Чтобы срезы по месяцам и машинам фильтровали не только нашу большую календарную таблицу, но и карточку, их нужно подключить к созданной мини-сводной карточки. Для этого выделите срез и на вкладке Срез используйте кнопку Подключение к отчетам (Slicer - Connect to reports).
После этого созданную мини-сводную можно смело копировать ещё два раза рядом и заменять в них меры на другие:
Подсветка VIP-заказов
Изощряться с дизайном можно практически бесконечно - насколько вам хватит фантазии и уровня владения Excel :)
Например, можно добавить визуализацию VIP-заказов (это столбец Статус в таблице Orders). Для этого возвращаемся в нашу меру Подробности через Power Pivot - Меры - Управление мерами - Изменить (Power Pivot - Measures - Manage measures - Edit) и добавляем проверку статуса функцией IF:
=CONCATENATEX(Orders; FORMAT(Orders[Время];"hh:mm")&" "&Orders[Событие]&" - "&Orders[Стоимость] & IF(Orders[Статус]="VIP";" ❤";"");"
")
Можно также прикрутить сюда спарклайны (мини-диаграммы внутри ячеек), стрелки или прелести условного форматирования в виде иконок - нет предела совершенству.
Красоты дашбордов Power BI это всё, конечно, не заменит, но простота и гибкость Excel тоже дорогого стоят, как считаете? :)
Ссылки по теме
- Календарь этапов проекта в виде диаграммы Ганта с помощью условного форматирования в Excel
- Мини-диаграммы в ячейках листа Excel
- Система учёта заказов на Google Calendar и Excel
Подскажите, пожалуйста, как Вы перенесли данные из одной таблицы в примечание другой таблицы календаре за 2024 год (calendar-2024.xlsm). В ячейках на листе "Календарь" появились примечания на основании данных из листа "Праздники", как это показано на прикрепленной картинке. Через макросы? К сожалению, в разосланном календаре ссылок на праздники нет. Можно конечно же вставить все вручную, но все же надеюсь, что есть возможность автоматизации процесса.
С искренним уважением.
Подскажите пожалуйста, как Вам удалось разместить друг под другом две сводные таблицы (Основной календарь и, расположенные над ним, карточки с итогами).
Заранее благодарю.
Ваши уроки очень спасают, когда начинаешь разбираться в возникающих вопросах.
У меня два вопроса.
1. Когда Заказы не отсортированы по времени в таблице Orders, возможно ли их отсортировать внутри меры Подробности.
2. Возможно ли настроить форматирование части записи меры Подробности (которая выводится на дашборд)?
Например, выполненные заказы будут иметь Статус выполнения = Выполнено
В дашборде часть записи, касающаяся данного времени, отображается зачеркнутым.
1. Я бы делал сортировку в Power Query при импорте таблицы в Модель данных.
2. К сожалению только макросом. Для форматирования можно использовать только функцию FORMAT из DAX или условное форматирование из Excel, но оба варианта форматируют ВСЮ ячейку. Отдельные слова перекрасить или зачеркнуть не получится.
Но вообще, конечно, круть невероятная, сколько вас читаю - столько поражаюсь. Пишите ещё!
Да, конечно можно календарь и встроенными средствами сделать - вопрос вкуса. То же самое с редактированием мер - кому как больше нравится.