Анализ товарной корзины средствами PowerPivot

Недавно я столкнулся с интересной задачей, делюсь решением.
Нужно немного терпения и excel с установленной надстройкой PowerPivot.

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

Что хотим получить:
— выбрать товар и посмотреть как часто он покупается вместе с другими

Готовим исходники

Реализации

Вот так выглядела выгрузка изначально — стандартный отчет из 1С. Он нас вполне устраивает.

Справочник номенклатуры

Задачей было смотреть на продажи в т.ч. в разрезе категорий, поэтому справочник номенклатуры необходим. Нам потребуются столбцы
— «Номенклатура»: наименование из 1С
— «Категория»: для фильтрации
— «Товар»: при нежелании просматривать различные цвета/вариации номенклатуры отдельно

Работа с данными

Загружаем таблицы

Загрузим сформированные данные в PowerPivot.

1. Переходим в закладку PowerPivot → Управление → Получение внешних данных → Из других источников → Файл Excel → Выбираем книгу с реализациями, загружаем и переименовываем таблицу в «Продажи».
2. Аналогичную операцию проделыванием со справочником номенклатуры, называем таблицу «Справочник».
3. Второй раз загружаем справочник номенклатуры, называем «ФильтрСправочник», столбцы в нем переименовываем в «ФильтрНоменклатура», «ФильтрКатегория» и «ФильтрТовар» соответственно.

Создаем таблицу дат

На вкладке «Конструктор» ищем кнопку «Таблица дат» → «Создать». Называем ее «Даты»

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

Теперь у нас 4 загруженные в модель данных таблицы, между ними нужно создать связи. Для этого кликаем на главном экране «Представление диаграммы» и создаем связи.

Связь всегда тянется от таблицы с уникальными значениями, к таблице с повторяющимися

1. Таблица Даты столбец Дата →
   Таблица Продажи столбец ДатаЗаказа
2. Таблица ФильтрСправочник столбец ФильтрНоменклатура →
   Таблица Продажи столбец Номенклатура
3. Таблица Справочник столбец Номенклатура →
   Таблица Продажи столбец Номенклатура

Связь между ФильтрСправочник и Продажи делаем неактивной.

Вычисляемые поля/Меры

Создаём несколько измерений (подробнее о мерах на сайте MS), для дальнейшей работы с ними в сводной таблице. Для этого в окне PowerPivot кликаем в нижнюю рабочую область и пишем формулы.
Для начала необходимо посчитать количество заказов.

Далее наше основное измерение. [Заказы с обоими продуктами] показывает сколько заказов содержат, по меньшей мере, один из товаров в сводной таблице и отфильтрованный товар.

Сводная таблица

Создаем сводную таблицу

На главной вкладке PowerPivot жмем «Сводная таблица» и выбираем лист, на котором хотим разместить отчет. Вставляем «Категорию» и «Товары» из таблицы Справочник в строки,  «Заказы» и «Заказы с другими продуктами» из таблицы Продажи в значения.

Создаем срезы

В MS Excel 2010 появился функционал срезов (Slicer), которые делают фильтры намного удобнее. Для создания среза кликаем на сводную таблицу → «Анализ» → «Вставить срез». Создаем срезы из таблиц Дата и ФильтрСправочник.

Отчет поможет определить, какие комплекты товаров чаще всего покупают ваши клиенты. Выбираете товар из слайсера, таблица показывает какие категории/товары покупались вместе с ним.

Другие применения

Если в таблице с заказами у вас есть информация по покупателям, можно также создавать меры анализирующие покупки, сделанные клиентами в различных заказах. Столбец с уникальным идентификатором покупателя назовём «IDПокупателя». Уникальным идентификатором может быть телефон, адрес почты или просто цифробуквенный набор.
Для начала считаем количество покупателей без повторений

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

За решение низкий поклон прекрасному DAX Patterns