План-факт анализ в сводной таблице с Power Pivot и Power Query

Постановка задачи

В исходных данных имеем две таблицы. Скромную в дизайне, большую по размеру, но удобную в работе таблицу с фактическими значениями продаж, выгруженную из какой-нибудь учетной системы:

Фактические продажи

И "красивую" таблицу с плановыми помесячными показателями от руководства:

Таблица с планом

Задача: каким-то образом объединить обе таблицы в одну, чтобы наглядно отобразить выполнение плана по каждому товару, региону, месяцу, кварталу и т.д.

Необходимая оговорка

Можно, конечно, не напрягаться, и решать это дело привычным образом "в лоб". Т.е. с помощью 144 функций СУММЕСЛИМН (SUMIFS) вычислять суммарные продажи по каждому месяцу, товару и городу, а потом с помощью еще 144 формул вручную считать процент выполнения плана.

Потом мысленно взвыть, когда шеф скажет, что хотел видеть динамику по кварталам, а не по месяцам. И лучше в рублях, а не в процентах. И города лучше расположить по столбцам, а месяцы по строчкам. И не ной, у тебя вся ночь впереди, к утру чтоб было готово.

И в нашем примере всего 3 города и 4 товара. А если будет больше?

Давайте-ка лучше мы пойдем другим путем - чуть более сложным, но гораздо более гибким и удобным в перспективе.

Что мы будем делать

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

Но как объединить в одной сводной две наших исходных таблицы? Плоскую таблицу продаж по дням и трехмерную таблицу плановых значений с детализацией по месяцам? Тут нам помогут 2 мастхэв надстройки для Excel:

  • Power Query - встроена в Excel, начиная с 2016-й версии, для более ранних Excel 2010-2013 её можно бесплатно скачать с сайта Microsoft.
  • Power Pivot - c 2013 года входит в состав большинства (но не всех, к сожалению) пакетов Microsoft Office. Для Excel 2010 (но не для более новых версий!) бесплатно качается, опять же, с сайта Microsoft.

Поехали, по шагам...

Шаг 1. Добавляем соединительные таблицы-справочники

Связать напрямую наши исходные таблицы факта и плана, к сожалению, никак не получится. Ни Power Pivot, ни, тем более, Excel не поддерживают пока связи "многие-ко-многим" (many-to-many), означающие, что в исходных таблицах могут встречаться дубликаты (а это как раз наш случай - названия товаров и городов встречаются в каждой таблице не по одному разу).

Поэтому нам потребуется создать "костыли" - промежуточные таблицы-справочники с уникальными значениями товаров, городов и дат, которые мы будем использовать для создания связей "один-ко-многим" (one-to-many), которые Power Pivot умеет делать на ура:

Соединительные таблицы

Для создания таблицы дат удобно использовать команду Главная - Заполнить - Прогрессия (Home - Fill - Progression):

Прогрессия для календаря

Шаг 2. Превращаем все таблицы в "умные" и даём им имена

Для загрузки таблиц в Power Pivot они должны быть "умными" (динамическими). Для этого с каждой таблицей проделываем следующее:

  1. Выделяем любую ячейку таблицы
  2. Жмем сочетание клавиш Ctrl+T или выбираем Главная - Форматировать как таблицу (Home - Format as Table).
  3. В открывшемся окне проверяем корректность выделения диапазона (особенно для таблицы плана!) и включена ли галочка Таблица с заголовками (My table has headers) и жмем ОК.
  4. На вкладке Конструктор (Design) в левом верхнем углу даем таблице осмысленное имя вместо стандартных безликих Таблица1,2,3...

Я назвал наши таблицы, соответственно:

  • таблПродажи
  • таблТовары
  • таблГеография
  • таблКалендарь
  • таблПлан

Шаг 3. Грузим первые 4 таблицы в Power Pivot

Первые четыре таблицы у нас в правильном виде, поэтому их можно смело загружать их в Модель данных - область памяти, с которой оперирует Power Pivot. Подключаем нашу надстройку через Файл - Параметры - Надстройки - Надстройки COM - Перейти (File - Options - Add-ins - COM Add-ins - Go) и убеждаемся, что на ленте появилась вкладка Power Pivot.

Теперь по очереди для каждой из первых четырёх таблиц, установив в неё активную ячейку, жмём на кнопку Добавить в модель данных (Add to Data Model):

Добавляем таблицы в Power Pivot

В старых версиях эта кнопка называлась Связанная таблица (Linked table).

В итоге все наши таблички должны загрузиться в открывшееся окно Power Pivot на отдельные вкладки:

Загруженные в Power Pivot таблицы

Шаг 4. Доводим до ума таблицу План

Прежде, чем загрузить в Модель данных Power Pivot таблицу с плановыми значениями, её нужно сначала подрихтовать: убрать в ней пустые строки и итоги, развернуть в плоскую, заполнить пустые ячейки в первом столбце городами и т.д. Проще и легче всего это проделать с помощью надстройки Power Query.

Сначала загрузим таблицу с планами в редактор запросов Power Query, используя кнопку Из таблицы/диапазона (From Table/Range) на вкладке Данные (Data) или на вкладке Power Query (если у вас старая версия Excel 2010-2013 и вы установили Power Query как отдельную надстройку):

Загруженная в Power Query таблица плана

Затем в открывшемся окне Power Query делаем следующее:

1. Удаляем все пустые строки с null через Главная - Удалить строки - Удалить пустые строки (Home - Remove rows - Remove empty rows).

2. Удаляем строки с итогами, сняв соответствующую галочку в фильтре по столбцу Товар.

3. Удаляем ненужный последний столбец ИТОГО, щелкнув по его заголовку правой кнопкой мыши - Удалить (Remove).

4. Заполняем пустые ячейки в первом столбце названиями городов из вышестоящих ячеек, щелкнув по заголовку столбца Город правой и выбрав Заполнить - Вниз (Fill - Down).

5. Разворачиваем 12 столбцов-месяцев в два: название месяца и его значение. Для это выделяем первых два столбца Город и Товар (удерживая клавишу Ctrl), щёлкаем по их заголовку правой и выбираем команду Отменить свёртывание других столбцов (Unpivot Other Columns).

6. Чтобы преобразовать текстовые названия месяцев в нормальную даты - идём на хитрость:

  • Добавляем перед датами единички через пробел с помощью команды Преобразование - Формат - Добавить префикс (Transform - Format - Add prefix)
  • Аналогично добавляем после дат 2019 через Преобразование - Формат - Добавить суффикс (Transform - Format - Add suffix)
  • Теперь, когда текст в этом столбце стал уже гораздо больше похож на дату, конвертируем всё его содержимое в даты, используя выпадающий список типов в шапке столбца:

    Конвертируем месяцы в даты

7. Столбец Атрибут переименовываем в Дата (двойным щелчком по заголовку столбца).

8. Чтобы не путать исходную таблицу плана с преобразованной, изменим имя запроса на таблПлан2 в правой панели Power Query (впоследствии это будет именем таблицы в Power Pivot).

9. Выгружаем готовую таблицу в Модель данных Power Pivot, используя команды Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...) и выбираем затем в следующем окне опцию Только создать подключение (Only create connection) плюс, самое главное (!), включаем флажок Добавить эти данные в модель данных (Add this data to Data Model):

Загружаем результаты в Power Pivot                  Импорт данных

После этого наша последняя таблица таблПлан должна загрузиться в окошко Power Pivot.

Шаг 5. Связываем таблицы

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

Для связывания в окне Power Pivot лучше переключиться в режим диаграммы с помощью кнопки Главная - Представление диаграммы (Home - Diagram View) или значком Диаграмма (Diagram) в правом нижнем углу окна. Прямоугольные окошки таблиц можно перетащить за строку заголовка и разложить любым удобным вам образом.

Связь делается очень просто: хватаем мышью столбец в одной из соединительных таблиц (таблТовары, таблГеография, таблКалендарь), тянем и бросаем на соответствующий столбец в таблицах таблПродажи и таблПлан2:

Создание связи

Главный принцип: тянем от таблиц-справочников (Товары, География, Календарь) к таблицам факта и плана. Делаем 6 связей - каждый справочник должен быть связан двумя связями с таблицами плана и продаж. В итоге должна получиться вот такая картина:

Модель данных

Обратите особое внимание на положение единичек и звёздочек на концах связей - это как раз и есть те самые связи "один-ко-многим", где звёздочка обозначает множество вхождений одного и того же элемента, а единичка - уникальность.

Если всё получилось, то сохраняем файл и выдыхаем - дело почти сделано.

Шаг 6. Строим сводную

Теперь на основе созданной модели данных можно построить сводную - для этого в окне Power Pivot выбираем команду Главная - Сводная таблица - Сводная таблица (Home - Pivot table - Pivot table). Мы автоматически вернёмся в Excel, где увидим привычную панель для построения сводной таблицы в правой части экрана, но в ней будут видны уже все таблицы, а не только текущая (как обычно):

Строим сводную

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

Главные принципы здесь такие:

  • В области строк, столбцов и фильтра можно бросать только поля из таблиц-справочников (таблГеография, таблКалендарь, таблТовары).
  • В область значений, где идут вычисления, можно закидывать только поля из таблиц факта и плана (таблПродажи, таблПлан2)

Например, можно накидать так:

Переносим поля в сводную

Чтобы по столбцам даты шли не с шагом один день, а покрупнее - щёлкаем по любой дате в сводной правой кнопкой мыши и выбираем команду Группировать по (Group by), а затем любой нужный уровень группировки:

Группировка дат в сводной

В итоге должно получиться что-то уже очень похожее на то, что нам требуется:

Почти готовая сводная

Шаг 7. Добавляем меры для вычислений

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

Сумма по полю Выручка := SUM(таблПродажи[Выручка])

Но контролировать процесс создания неявных мер мы не можем - Excel сам решает как её назвать, какую именно функцию (SUM или COUNT) использовать и т.д. Поэтому лучше создавать явные меры для сводной самостоятельно - в этом случае мы сможем контролировать все их параметры.

Для этого на вкладке Power Pivot выберем команду Меры - Создать меру (Measure - New measure) и в открывшемся окне задаём:

Создаем меру

Здесь:

  • Имя таблицы - место для хранения меры (можно выбрать любую таблицу - это не играет роли).
  • Название меры - придумываем и вводим любое удобное название (можно на русском).
  • Описание - по желанию.
  • Формула - вводим формулу, по которой будет вычисляться мера. Можно использовать функции из встроенного в Power Pivot языка DAX (кнопка fx).
  • Проверить формулу - чекает вашу формулу на предмет ошибок и выдаёт рекомендации по их исправлению.
  • В нижней части окна можно сразу же задать числовой формат для меры, чтобы потом по 100 раз не настраивать его в сводной (как это бывает с обычными неявными мерами).

Повторяем процесс еще два раза:

  • Создаем меру с именем Факт с формулой =SUM('таблПродажи'[Выручка]) и числовым форматом без копеек и с разделителем.
  • Создаём меру Отклонение, которая использует две предыдущих созданных меры по формуле =[Факт]/[План]-1 и процентным форматом

Добавленные меры появятся в правой панели сводной таблицы с характерным значком:

Меры в панели сводной

Теперь их можно смело закидывать мышкой в нашу сводную и выполнять план-факт анализ в любых разрезах за считанные секунды:

Итоговая сводная

Обновляется вся созданная красота (модель данных Power Pivot, запрос Power Query и сама сводная) одним движением - на вкладке Данные (Data) с помощью кнопки Обновить все (Refresh All) или сочетания клавиш Ctrl+Alt+F5.

Возможные проблемы и их решения

В процессе реализации вы можете нарваться на несколько типичных "граблей":

  • Появляются странные ошибки в Power Pivot или сама вкладка Power Pivot неожиданно пропадает из Excel - отключите надстройку, перезапустите Excel и подключите её заново (см. Шаг 3). Обычно помогает.
  • Не получается создать связь - проверьте, нет ли повторов в справочниках. В столбцах, используемых для связывания не должно быть (в таблицах-справочниках) дубликатов - это жёсткое требование Power Pivot.
  • Какие-то странные результаты получаются в сводной - проверьте 1) правильно ли вы настроили связи 2) те ли поля вы используете для сводной (в области строк, столбцов и фильтра могут лежать только поля из справочников).

Если будут ещё какие-то сложности - пишите в комменты.

В любом случае, попробовать стоит - создав единожды такую обновляемую аналитическую систему, можно ещё долго радоваться ей в будущем :)

Ссылки по теме

 


11.05.2020 21:42:10
Возможно - опечатка?
В Шаге 5 "Делаем 6 связей - каждый справочник должен быть связан тремя связями с таблицами плана и продаж."
Каждый справочник связывается с таблицами плана и продаж двумя связями.
11.05.2020 22:44:07
Спасибо, Виктор! Подправил.
11.05.2020 22:44:55
Николай, добрый вечер. Дома офис 365, power pivot есть, а на работе офис 2016 профессионал коробочный (кастрированный), там в надстройках отсутствует power pivot. Если я на домашнем создам сводную с план факт анализом, используя power pivot, то смогу ли потом пользоваться этим файлом на рабочем компе с кастрированным офисом?
12.05.2020 09:57:23
Нет, к сожалению.
12.05.2020 14:33:05
Николай, уверены? На сколько я в курсе отчет откроется и можно будет даже со сводной поиграться, но доступа к модели данных не будет, это да.
12.05.2020 23:55:03
Тогда уточните, что вы понимаете под термином "пользоваться" :)
Я имел ввиду полную функциональность, включая обновление, а оно работать не будет.
Если нужно только поиграться (подвигать туда-сюда поля в сводной, пофильтровать и т.д.), то это работать будет, т.к. сводная кэширует в себе эти данные (если вы не сняли в её параметрах галочку Сохранять данные вместе с таблицей).
13.05.2020 11:07:13
Спасибо за ответы, попробовал менять не получается на урезанном офисе, работа только со сводной таблицей так как она в кэше все держит, Николай правильно все истолковал: пользоваться - это полная функциональность + обновление
12.05.2020 15:21:01
Николай, класс! Будет куда новичков с форума отправлять. Надеюсь вы будете углублять тему РР, очень он востребован, а на русском внятной инфы маловато.

Не с точки зрения критики, а дополнительной информации для (уверен вы это все знаете, просто не хотелось раздувать статью):
1. При создании связей без разницы от какой таблицы к какой её тащить, РР сам разберется где один, а где много. Причем если с обеих сторон будут уникальные значения по ключевым столбцам, то заранее вообще не очевидно какую из таблиц он сделает стороной один, частенько он им назначает таблицу с фактами.
2. Календарь можно создавать штатными средствами РР
3. В столбцах, используемых для связи не должно быть не только дубликатов на стороне один, а также пустых ячеек, иначе будет ошибка.
4. При удалении дубликатов силами Power Query перед загрузкой в модель необходимо пристальное внимание обратить на тот факт, что PQ чувствителен к регистру по умолчанию, а РР - нет. Так что могут быть казусы, когда думаешь что дубликаты удалил, а по факту - вот они! :)
5. Это уже мое личное мнение из полученного опыта, лучше всего файл с отчетом делать отдельным от источников, в самом файле имеет смысл держать только таблички с настройками только для этого отчета. Все остальное тащить исключительно извне.
12.05.2020 23:59:08
Спасибо за ценные уточнения, Алексей!

Очень правильные комментарии, поддерживаю по всем пунктам.

Единственное, по п.1 у меня, по опыту, PP часто лажает со связями, если тащить их в неправильном направлении. Поэтому и акцентировал, что тащить надо от справочников - для надежности.
13.05.2020 16:41:14
Просто, вот буквально вчера тащил от одного справочника к другому, с обеих сторон уникальные значения в столбцах, и все равно стороной один, РР назначил ту таблицу, к которой тащил связь. Такое у меня в 2016, и в О365. Так что все не так однозначно! :D
19.05.2020 10:51:43
Странно. Сейчас еще потестирую у себя.
20.05.2020 10:38:19
У меня есть стойкое подозрение что в таких случаях качестве справочника на стороне 1, он выбирает таблицу, в которой больше строк.
11.08.2020 01:35:30
Николай, здравствуйте!
Подскажите, как создать сводную табл из Power Pivot в другой книге?
В настройках можно выбрать только два варианта: на текущем листе или на новом, а в новой книге или в другой книге - нет выбора.
Это вопрос по п.5 в комментариях Алексея.
Здравствуйте!

Загрузите, пожалуйста, исходный файл для примеры - чтобы мы "руками" могли все сами проделать, руководствуясь видеоуроком. Тот пример, который загружен Вами, уже содержит все выполненные шаги. Хочется поучиться все это делать самой на исходных данных.

Спасибо огромное!
19.05.2020 10:51:18
Не вопрос - по ссылочке в заголовке теперь лежит архив с двумя файлами - исходным и сделанным.
Практика - это очень правильно :)
Спасибо Вам огромное!
Спасибо Вам большое!

Скажите, а если в данном примере в Плане города будут по английски например, а в факте по-русски?
Для связки нужно добавить в справочник второе поле?

Через 15 минут: проверил сам - да, второе поле работает.:)))

Так что теперь - просто спасибо большое!
07.06.2020 17:19:17
подскажите, пожалуйста, как лучше строить модель данных, если планы (прогнозы) делаются каждый период до конца года и сравнивать надо факт с разными планами. Допустим с планом прошлого периода, с планом два периода назад, и с планом, созданным в начале года (основа бюджета)?
а также текущий план (прогноз) с планом (прогнозом) прошлых периодов.
11.06.2020 11:21:08
Николай, спасибо.
Вот прям как раз думала над такого рода проблемой!
:)
KIP
29.07.2020 13:40:16
Добрый день, Николай!
Такая ситуация, на основе данных я создал сводную таблицу, (напрямую, без создания моделей), то я не смогу создать меру без модели?
30.07.2020 10:35:20
Не сможете, к сожалению. Меры работают только в сводных на основе Модели Данных.
KIP
31.07.2020 05:24:26
Я понял, спасибо! Следовательно нельзя в сводных таблицах произвести математические операции между ячейками, допустим есть некая ячейка "Районный коэффициент", которая меняется взависимости от вводимого значения пользователем, а некая колонка сводной тоблицы "Зарплата" высчитывается перемножением ячейки "Районный коэффициент" на колонку "Оклад" сводной таблици?  
25.08.2020 00:38:08
Если в строки св.табл. я вставляю поля из справочников, то получается, что во всех категории вставляются все подкатегории (справочники полностью вставляются). Даже если в категории 1 должно быть 10 подкатегорий, а в кат2 - 4 подкатегории. И срезы не работают в такой таблице. Или что я делаю не так?
Наверх