Contando vendas de produtos diferentes para os mesmos clientes no Excel (venda cruzada)

Question

Eu tenho uma tabela de transações com todos os IDs de clientes e produtos comprados. Um cliente pode ter comprado vários produtos, que podem ser produtos independentes ou complementos.

Agora estou tentando criar uma tabela de venda cruzada para ver quais complementos são mais comuns com quais produtos, mas não consigo descobrir como contar a correspondência de vários critérios (clientid, produtos e complementos) em linhas diferentes.

A tabela de datas é mais ou menos assim:

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

E a saída que quero obter é:

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

Portanto, usando a tabela de dados de exemplo, a tabela de venda cruzada contaria 1 no produto 1 vs. complemento 1 (devido ao clientid = 10 ter comprado ambos os produtos)

O problema que tenho é contar quando o mesmo ID do cliente corresponde aos critérios em duas ou mais linhas (os produtos e complementos comprados). A tabela de dados tem quase 300 mil entradas, então não posso fazer isso manualmente.

Alguém sabe como eu poderia fazer isso?

Muito obrigado pela ajuda!

Answer 1

Eu faria isso em duas etapas.

1. TABELA A: Selecione seus dados, "insert->Pivot Table". Coloque o ID do cliente no campo de linhas e coloque "Produto" nos campos Colunas e Valores.

Agora você tem uma tabela com clientes únicos nas linhas e quais produtos e complementos cada um comprou.

2. TABELA B Agora crie uma segunda tabela com os nomes dos produtos nas linhas e os nomes dos complementos nas colunas. Para preencher a tabela, use sumproduct() para multiplicar as colunas do produto n pelas colunas do complemento i.

Infelizmente, não consigo pensar em uma ótima maneira de selecionar automaticamente quais colunas da Tabela A as fórmulas da Tabela B devem observar. Se a Tabela A tiver todos os produtos seguidos por todos os complementos, você poderá selecionar manualmente cada produto em cada linha da primeira coluna da Tabela B e, em seguida, arrastar para preencher automaticamente as colunas de complementos corretas da Tabela A. As colunas do pivô tabela deve ser classificada em ordem alfabética, para que isso funcione, dependendo de como seus produtos e complementos são nomeados. Você ainda teria que selecionar manualmente todas as colunas de produtos.