Как собрать регулярный управленческий отчёт с помощью Power Query, Power Pivot, DAX и ИИ
Подойдёт не только для магазинов. Эту же логику можно использовать для филиалов, менеджеров, ресторанов, клиник, складов, маркетплейсов и любых других задач, где регулярно появляются однотипные выгрузки
Если сейчас вы каждый день копируете данные руками, после этого гайда вы увидите, как эту задачу можно организовать совсем по-другому
Конечно же, рутинную работу! Каждый день вам приходится руками собирать данные из разных источников, копировать, вставлять, удалять, преобразовывать и тд
Можно каждый раз проделывать эти операции, а можно построить процесс иначе!
После этого новый файл просто добавляется в папку, а отчёт обновляется
Хороший дашборд должен помогать быстро понять:
Именно такую логику мы заложили в наш кейс
Прежде чем объединять файлы и писать меры, определите, что именно должен показывать ваш дашборд и какие вопросы он должен помогать решать
Здесь возможны два сценария:
Посмотрите, какие показатели и разрезы в них доступны, и подумайте, какие выводы из них можно получать
Сначала определите, что хотите видеть на дашборде, а уже потом сформируйте требования к исходным данным
Например, вы определили, что хотите видеть:
Попросите ИИ подготовить для вас макет дашборда. Пример первого промпта:
У меня сеть магазинов. Я ежедневно анализирую выручку, трафик и средний чек. Мне нужно быстро понимать, что изменилось, какие магазины дали рост/падение и какой фактор повлиял. Предложи структуру управленческого дашборда и сделай картинку с примером, который будет включать в себя карточки KPI, диаграмму с динамикой, факторный анализ, таблицы, в которых видны ТОП и Анти Топ-5 магазинов по выручке, динамика выручки. Мне нужно еще, чтобы на панели можно было выбрать любую дату и сравнение продаж на эту дату с предыдущим днем или с предыдущей неделей
После этого становится понятно, какие поля должны быть в исходных данных и какие меры нам понадобятся дальше
Структурируйте исходные файлы: создайте папку, в которой будут храниться все источники: CSV, PDF, JSON, Excel с данными на разных листах и тд
Сложите в одну папку все однотипные файлы, которые нужно объединить
Важно! Ничего с сырыми выгрузками не делайте, пусть они остаются в первозданном виде, Power Query всё сделает за вас
Цифры на скриншотах показывают порядок действий
Откройте вкладку Данные → Получить данные → Из файла → Из папки
1234Выберите папку Ежедневные отчёты и нажмите Открыть
12Нажмите Объединить → Объединить и преобразовать данные
12Отметьте лист Продажи и нажмите ОК
12В столбце Дата лежит название файла. Откройте «Замена значений»: в поле поиска введите Продажи за, поле «Заменить на» оставьте пустым и нажмите ОК
1234В столбце Дата снова откройте «Замена значений»: найдите .xlsx, поле «Заменить на» оставьте пустым и нажмите ОК
1234Щёлкните правой кнопкой по столбцу Дата → Изменить тип → Дата
123В практикуме «Автоматизированный дашборд с Power Excel и ИИ» мы вместе проходим весь путь от исходных файлов до готового отчёта со всеми возможными ошибками
Внутри 15 практических уроков:
После очистки и объединения данных добавьте таблицу в Power Pivot
В Power Query нажмите Закрыть и загрузить в…
1Выберите Только создать подключение, поставьте галочку «Добавить эти данные в модель данных» и нажмите ОК
123Создайте отдельную таблицу Calendar и свяжите её с продажами по дате
Например, простая мера
Выручка := SUM('Ежедневные отчёты'[Факт выручки, руб])Более сложные меры не обязательно писать самостоятельно. Опишите ИИ структуру модели, уже созданные меры и ожидаемый результат
Определите, с какими периодами должен сравниваться текущий результат. Например:
Создайте сводные таблицы из модели данных. Они станут источниками для KPI, таблиц и диаграмм. На техническом листе удобно собрать все промежуточные расчёты и элементы, которые пользователь видеть не должен
Проверьте, чтобы один выбор пользователя корректно менял все связанные показатели
Не стоит начинать разговор с ИИ с фразы: «Напиши мне меру на DAX»
Чем лучше вы объясните контекст, тем меньше вероятность, что потом придётся несколько раз переделывать решение
Например:
Я работаю в Excel Power Pivot. У меня есть таблица ежедневных продаж и отдельный календарь. Между ними создана связь по дате. Уже существует мера [Выручка]. Мне нужно рассчитать процентное изменение выручки относительно предыдущего календарного дня. Не создавай дополнительную меру предыдущего дня, рассчитай её внутри основной меры. Сначала дай готовую DAX-формулу, затем коротко объясни её логику
Это тоже нормальная ситуация. Не пытайтесь придумывать формулу за ИИ
Мне нужно разложить изменение выручки на влияние трафика и среднего чека. Я не знаю методику такого расчёта. Сначала предложи логику расчёта простыми словами. Пока не пиши DAX
То есть ИИ можно использовать как помощника на этапе проектирования решения
На первый взгляд кажется: собрали данные, написали меры и дашборд готов. Но именно на следующих этапах обычно и появляются ситуации, из-за которых приходится возвращаться к модели, проверять расчёты и дорабатывать логику
В практикуме мы специально разбираем такие кейсы - не только идеальный сценарий, когда всё заработало с первого раза
В уроках разбираемся, как диагностировать такую проблему, что проверить в первую очередь и как привести модель в рабочее состояние, не перебирая случайные варианты
Вы добавили сравнение с предыдущими периодами и вдруг на дашборде появились даты, которых в фактических данных вообще нет. При этом сами меры могут считать абсолютно правильно
На практикуме мы приводим срез в порядок так, чтобы пользователь видел только актуальные даты, а расчёты предыдущих периодов продолжали работать корректно
На дашборде хотелось видеть аккуратное 17.09 вместо 17.09.2026 и убрать даты, по которым нет продаж
Но обычной сменой формата вопрос не решился
И эту проблему мы решили путем изменения логики в календаре и адаптации мер на DAX вместе с ИИ, разумеется
Навели порядок в интерфейсе и внезапно часть расчётов начала работать не так, как раньше
В уроках мы разбираем, как адаптировать расчёты под новую логику фильтрации и сохранить корректное сравнение с предыдущим днём и прошлой неделей
Допустим, пользователю нужно выбирать «Сравнить с»: Предыдущим днём или Предыдущей неделей
Самый очевидный путь - создать отдельные технические таблицы для каждого сценария. Но это требует дополнительных таблиц на техническом листе и приводит к замедлению работы файла
Поэтому мы создаём маленькую таблицу:
Добавляем её в модель, а дальше DAX считывает выбор пользователя и с помощью мер отдаёт нужную меру
Тот же подход можно использовать, если пользователь должен переключать:
Один небольшой переключатель может заменить целую пачку технических таблиц
В практикуме мы собираем этот переключатель полностью и подключаем его к показателям дашборда
Перейти к практикумуДашборд - это не конечная точка анализа. Он помогает быстро поставить диагноз:
Например, выручка снизилась, а факторный анализ показывает, что основной вклад дал средний чек. Дальше уже начинается работа аналитика: Почему он снизился? Ассортимент, промо, наличие, структура корзины, конкуренты?
Если падает трафик - проверяем уже другие гипотезы: привлечение, удержание, посещаемость, отдельные магазины
Сильный специалист приходит не только с проблемой, но и с предложениями по дальнейшим действиям
И чем меньше времени вы тратите на ручной сбор данных и пересчёт отчётов, тем больше гипотез успеваете проверить и тем больше времени остаётся на анализ и поиск инсайтов
Настоящие изменения в работе начинаются тогда, когда данные перестают отнимать время и начинают помогать принимать решения
В этом файле вы увидели архитектуру решения. В практикуме мы уже собираем его полностью своими руками - от первого файла до работающего управленческого отчёта
Мы решаем один бизнес-кейс и по дороге разбираем Power Query, Power Pivot, DAX, работу с ИИ и техническую сборку дашборда
Внутри 15 уроков по работе с Power Excel + ИИ, включая блок по оформлению:
В практикуме показаны моменты, когда что-то приходится переделывать, искать ошибки, менять меры или по-другому строить техническую часть
Потому что именно с этим вы потом столкнётесь и на своих данных
Повторите весь путь на готовом кейсе, а потом перенесите эту логику на свои рабочие задачи. Возможно, после этого вы уже никогда не захотите собирать регулярный Excel-отчёт по-старому