Недавно я столкнулся с интересной задачей, делюсь решением.
Нужно немного терпения и excel с установленной надстройкой PowerPivot.
В наличии:
— выгрузка по реализациям товаров из 1С, где видим: номер заказа, номенклатуру, кол-во и сумму
— справочник номенклатуры, где видим: категорию номенклатуры и наименование продукта
— острое желание посмотреть какие товары покупают вместе.
Что хотим получить:
— выбрать товар и посмотреть как часто он покупается вместе с другими
Готовим исходники
Реализации
Вот так выглядела выгрузка изначально — стандартный отчет из 1С. Он нас вполне устраивает.
Справочник номенклатуры
Задачей было смотреть на продажи в т.ч. в разрезе категорий, поэтому справочник номенклатуры необходим. Нам потребуются столбцы
— «Номенклатура»: наименование из 1С
— «Категория»: для фильтрации
— «Товар»: при нежелании просматривать различные цвета/вариации номенклатуры отдельно
Работа с данными
Загружаем таблицы
Загрузим сформированные данные в PowerPivot.
- Переходим в закладку PowerPivot → Управление → Получение внешних данных → Из других источников → Файл Excel → Выбираем книгу с реализациями, загружаем и переименовываем таблицу в «Продажи».
- Аналогичную операцию проделыванием со справочником номенклатуры, называем таблицу «Справочник».
- Второй раз загружаем справочник номенклатуры, называем «ФильтрСправочник», столбцы в нем переименовываем в «ФильтрНоменклатура», «ФильтрКатегория» и «ФильтрТовар» соответственно.
Создаем таблицу дат
На вкладке «Конструктор» ищем кнопку «Таблица дат» → «Создать». Называем ее «Даты»
Создание связей
Теперь у нас 4 загруженные в модель данных таблицы, между ними нужно создать связи. Для этого кликаем на главном экране «Представление диаграммы» и создаем связи.
Связь всегда тянется от таблицы с уникальными значениями, к таблице с повторяющимися
- Таблица Даты столбец Дата →
Таблица Продажи столбец ДатаЗаказа - Таблица ФильтрСправочник столбец ФильтрНоменклатура →
Таблица Продажи столбец Номенклатура - Таблица Справочник столбец Номенклатура →
Таблица Продажи столбец Номенклатура
Связь между ФильтрСправочник и Продажи делаем неактивной.
Вычисляемые поля/Меры
Создаём несколько измерений (подробнее о мерах на сайте MS), для дальнейшей работы с ними в сводной таблице. Для этого в окне PowerPivot кликаем в нижнюю рабочую область и пишем формулы.
Для начала необходимо посчитать количество заказов.
1 |
[Заказы] := DISTINCTCOUNT( Продажи[НомерЗаказа] ) |
Далее наше основное измерение. [Заказы с обоими продуктами] показывает сколько заказов содержат, по меньшей мере, один из товаров в сводной таблице и отфильтрованный товар.
1 2 3 4 5 6 7 8 9 |
[Заказы с обоими продуктами] := CALCULATE ( DISTINCTCOUNT ( Продажи[НомерЗаказа] ); CALCULATETABLE ( SUMMARIZE ( Продажи; Продажи[НомерЗаказа] ); ALL ( Справочник ), USERELATIONSHIP ( Продажи[Номенклатура]; 'ФильтрСправочник'[ФильтрНоменклатура] ) ) ) |
Сводная таблица
Создаем сводную таблицу
На главной вкладке PowerPivot жмем «Сводная таблица» и выбираем лист, на котором хотим разместить отчет. Вставляем «Категорию» и «Товары» из таблицы Справочник в строки, «Заказы» и «Заказы с другими продуктами» из таблицы Продажи в значения.
Создаем срезы
В MS Excel 2010 появился функционал срезов (Slicer), которые делают фильтры намного удобнее. Для создания среза кликаем на сводную таблицу → «Анализ» → «Вставить срез». Создаем срезы из таблиц Дата и ФильтрСправочник.
Отчет поможет определить, какие комплекты товаров чаще всего покупают ваши клиенты. Выбираете товар из слайсера, таблица показывает какие категории/товары покупались вместе с ним.
Другие применения
Если в таблице с заказами у вас есть информация по покупателям, можно также создавать меры анализирующие покупки, сделанные клиентами в различных заказах. Столбец с уникальным идентификатором покупателя назовём «IDПокупателя». Уникальным идентификатором может быть телефон, адрес почты или просто цифробуквенный набор.
Для начала считаем количество покупателей без повторений
1 |
[Покупатели] := DISTINCTCOUNT( Продажи[IDПокупателя] ) |
Теперь по уже знакомой нам схеме создаем измерение, которое посчитает количество товаров в строках сводной таблице, которые приобрели покупатели, купившие отфильтрованный товар.
Получилось сложно, объясню на примере: я приобрел велосипед, через год приобрел сноуборд. Выбрав в срезе/фильтре сноуборд, будет видно, что один пользователь покупал и велосипед, даже если товары были в разных заказах.
1 2 3 4 5 6 7 8 9 |
[Покупатели обоих товаров] := CALCULATE ( DISTINCTCOUNT ( Продажи[IDПокупателя] ); CALCULATETABLE ( SUMMARIZE ( Продажи; Продажи[IDПокупателя] ); ALL ( Справочник ), USERELATIONSHIP ( Продажи[Номенклатура]; 'ФильтрСправочник'[ФильтрНоменклатура] ) ) ) |
За решение низкий поклон прекрасному DAX Patterns