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

Question

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

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

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

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 (из-за клиентского = 10 купил оба продукта)

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

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

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

Answer 1

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

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

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

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

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