Календарный дашборд с 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, но оба варианта форматируют ВСЮ ячейку. Отдельные слова перекрасить или зачеркнуть не получится.
В Представлении данных Модели данных эта сортировка сохраняется.
Однако в дашборде внутри каждого дня время идет вразнобой. Например, в дате "4 дек" сперва показана запись от 12:15, а под ней - запись от 08:00.
Николай, подскажите, как это исправить.
Гугл и GPT не помогли.
Но вообще, конечно, круть невероятная, сколько вас читаю - столько поражаюсь. Пишите ещё!
Да, конечно можно календарь и встроенными средствами сделать - вопрос вкуса. То же самое с редактированием мер - кому как больше нравится.
Столкнулся с проблемой. Если добавить в календарь 2023 год, то при выборе месяца ЯНВАРЬ 2023 года 01.01.2023 помещается в самый низ сводной таблицы. И это логично т.к. 01.01.2023 это 52я неделя 2022года. Как можно справится с таким явлением кроме как, например "обнулять" значение формулы НОМНЕДЕЛИ.ISO ? Спасибо.
Целый год пользовался Данным календарем. Но теперь проблема - срез показывает только текущий 2024 год... Подскажите куда копать, чтобы появился 2025 год?
То есть глобально, как быть с разными годами?
Я так предложу решить Ваш вопрос.
1. Создаёте новый Лист, переименовываете его, например «Год».
2. На листе «Год» в любой ячейке, например «А1» - пишите нужный год «2025». Это Вам даст изменением одной ячейки изменить все даты.
3. В ячейке «А2» - пишите формулу «=--(1&". "&1&"."&A1)» - без кавычек. И выбираете формат ячейки «Дата».
4. На Листе «Календарь» в ячейке «А2», где начинается отсчёт даты – пишите «=Год!A2». А в ячейке «А3» – пишите «=A2+1», и протягиваете формулу до конца таблицы.
5. Также повторяете на Листе «Заказы».
6. После чего открываете Вкладку «Данные» и нажимаете кнопку «Обновить всё». Или кнопками «Ctrl+Alt+F5».
В последующем Вы можете менять номер года на Листе «Год» в ячейке «А1» и обновлять данные.
Чтобы избавиться от рутины Пункта 6, можно сохранить документ с поддержкой макросов.
Создаёте Кнопку, например «Обновить данные», и к этой кнопке назначаете макрос:
(для Excel 2013 и новее)
Или
В модуле самого Листа «Год» прописать другой макрос:
(который будет срабатывать при изменении значения в Ячейке «А1»)
Есть другая проблема.
К примеру, года:
2028 – Год начинается с Субботы
2033 – Год начинается с Субботы
2034 – Год начинается с Воскресенья
2038 – Год начинается с Пятницы
В этих случаях, когда год начинается с пятницы, первая неделя встаёт в конец Января.
На Листе «Календарь» видно по нумерации недели, что эти дни относятся к неделям прошлого года.
Это можно исправить на Листе «Календарь», в столбце «Неделя» изменить формулу на «=НОМНЕДЕЛИ([@Дата];2 )», где «2» - тип возрастания (подойдёт 2 или 11 или 21), где Понедельник будет первым.
Может в версии Excel 2019 дело?