☰

Подсчет продаж разных продуктов одним и тем же клиентам в Excel (перекрестные продажи)

tag-icon • tag-icon microsoft-excel
Подсчет продаж разных продуктов одним и тем же клиентам в Excel (перекрестные продажи)

У меня есть таблица транзакций, в которой есть все ID клиента и купленные продукты. Один клиент может купить несколько продуктов, которые могут быть как отдельными продуктами, так и дополнениями.

Теперь я пытаюсь создать таблицу перекрестных продаж, чтобы увидеть, какие дополнения чаще всего встречаются с какими продуктами, но не могу понять, как подсчитать совпадения по нескольким критериям (clientid, продукты и дополнения) в разных строках.

Таблица дат выглядит примерно так:

clientid   product   ...   ...
10         prod 1    ...
11         prod 2
13         prod 1
10         addon 1
...        ...

И вот что я хочу получить на выходе:

x         addon 1     addon 2      addon 3 ....
prod 1       #            #            #
prod 2
prod 3
...

Таким образом, используя таблицу данных примера, таблица перекрестных продаж будет иметь значение 1 для продукта 1 против дополнения 1 (из-за того, что clientid = 10 купил оба продукта)

Проблема, с которой я сталкиваюсь, заключается в подсчете, когда один и тот же идентификатор клиента соответствует критериям в двух или более строках (купленные продукты и дополнения). Таблица данных содержит почти 300 тыс. записей, поэтому я не могу сделать это вручную.

Кто-нибудь знает, как это сделать?

Огромное спасибо за помощь!

решение1

Я бы сделал это в два этапа.

  1. ТАБЛИЦА A: Выберите данные "insert->Pivot Table". Введите идентификатор клиента в поле строк и введите «Продукт» в поля столбцов и значений.

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

  1. ТАБЛИЦА B Теперь создайте вторую таблицу с названиями ваших продуктов в строках и названиями дополнений в столбцах. Чтобы заполнить таблицу, используйте sumproduct() для умножения столбцов для продукта n на столбцы для дополнения i.

К сожалению, я не могу придумать хорошего способа автоматически выбирать, какие столбцы в Таблице A должны просматривать формулы в Таблице B. Если в Таблице A есть все продукты, за которыми следуют все дополнения, вы можете вручную выбрать каждый продукт в каждой строке первого столбца Таблицы B, а затем перетащить его, чтобы он автоматически заполнил правильные столбцы дополнений из Таблицы A. Столбцы сводной таблицы должны быть отсортированы по алфавиту, так что это может сработать, в зависимости от того, как названы ваши продукты и дополнения. Вам все равно придется вручную выбирать все столбцы продуктов.

Связанный контент