Criar um Crossjoin (todas as combinações) a partir de 2 colunas no Excel — Guia Completo
Ao trabalhar com duas listas no Excel — como nomes de produtos e tamanhos, regiões e representantes comerciais, ou alunos e cursos — poderá precisar de gerar todas as combinações possíveis entre elas. Essa operação é conhecida como *crossjoin* (também designada por produto cartesiano). Este tutorial oferece instruções passo a passo, vantagens e desvantagens de cada abordagem, e exemplos práticos, para que possa escolher o método mais adequado ao seu fluxo de trabalho.
O que é um Crossjoin?
Um Crossjoin (também conhecido como produto cartesiano) é uma operação que gera todas as combinações possíveis entre duas listas. No Excel, isso significa emparelhar cada item da Lista A com todos os itens da Lista B, criando uma matriz completa de combinações.
Os Crossjoins são extremamente úteis em muitos cenários reais de dados, tais como:
Variações de produtos
Combine cores × tamanhos × estilos e crie um catálogo completo de produtos.
Análise de vendas
Crie combinações entre regiões, representantes comerciais e trimestres.
Agendamento e planeamento
Gestione todas as combinações possíveis entre colaboradores × turnos ou alunos × cursos.
Testes e simulações
Crie combinações de cenários para modelação, previsão ou validação.
Exemplo:
Se tiver:
O resultado do Crossjoin será:
Realizar um Crossjoin no Excel
O Excel oferece várias formas de criar um crossjoin, e o método ideal depende da versão do Excel que utiliza, do seu grau de familiaridade com fórmulas ou ferramentas e do tamanho do seu conjunto de dados. Abaixo apresentamos quatro métodos práticos e eficientes — desde fórmulas simples até soluções mais avançadas, como Power Query e VBA. Cada abordagem tem as suas vantagens, permitindo-lhe escolher a que melhor se adapta ao seu fluxo de trabalho, à dimensão dos seus dados e às suas necessidades de automação.
Método 1: Cross Join utilizando uma fórmula (Excel 365)
1.Prepare os seus dados. Coloque a sua primeira lista numa coluna (por exemplo, A2:A5 para Produtos) e a segunda lista noutra coluna (por exemplo, C2:C5 para Cores).
2. Introduza a seguinte fórmula numa célula vazia onde deseja obter o resultado e, em seguida, prima a tecla Enter. A fórmula será preenchida automaticamente e devolverá todas as combinações geradas de uma só vez. Veja a captura de ecrã:
=TEXTSPLIT(TEXTJOIN(",", TRUE, A2:A5 & "|" & TRANSPOSE(C2:C5)), "|", ",")
Explicação desta fórmula:
- A2:A5 & «|» & TRANSPOSE(C2:C5): Combina cada valor da coluna A com todos os valores da coluna C.
- TEXTJOIN(",", TRUE, …): Junta todos os pares numa única cadeia de texto longa, separada por vírgulas.
- TEXTSPLIT(…, «|», ","): Divide novamente o texto numa tabela com duas colunas.
Em resumo, a fórmula cria pares A|C, junta-os numa única cadeia de texto e, em seguida, divide-os novamente numa tabela estruturada de duas colunas — gerando efetivamente todas as combinações possíveis.
Dicas:
Além da fórmula apresentada anteriormente, pode utilizar a seguinte para obter o mesmo resultado.
=LET(a,A2:A5,b,C2:C5,
MAKEARRAY(ROWS(a)*ROWS(b),2,
LAMBDA(r,c,
IF(c=1, INDEX(a, 1+INT((r-1)/ROWS(b))), INDEX(b, 1+MOD(r-1, ROWS(b))))
)
))
Vantagens
- Totalmente dinâmico
- Sem colunas auxiliares
- Atualiza-se automaticamente quando as listas de origem mudam
Desvantagens
- Requer Excel 365
- A fórmula parece complexa para principiantes
✨ Listar todas as combinações — Gere todas as possibilidades com um só clique!
Cansado de escrever fórmulas complexas para obter todas as combinações possíveis? Com o Kutools para Excel, pode listar instantaneamente todas as combinações de várias colunas ou valores — sem fórmulas, sem Power Query, apenas alguns cliques!
Método 2: Cross Join utilizando o Power Query
O Power Query oferece uma abordagem clara e sem código, compatível com todas as versões modernas do Excel. Permite-lhe criar um cross join de forma rápida e intuitiva, sem precisar escrever uma única fórmula. Siga as instruções passo a passo abaixo.
Passo 1: Criar tabelas para cada conjunto de dados das colunas
1. Selecione a primeira lista de dados, clique em Inserir > Tabela, na caixa de diálogo Criar Tabela, clique em OK. Obterá a primeira tabela.
2. No separador Estrutura da Tabela, renomeie a tabela com um nome significativo para facilitar a sua referência posterior.
3. Repita os mesmos passos para converter outra coluna de dados numa tabela e atribua-lhe um nome.
Passo 2: Importar as Tabelas e Carregar como Ligações
1.Selecione a primeira tabela e clique em Dados>A partir de Tabela/Intervalo, veja a captura de ecrã:
2. Na janela do Editor do Power Query aberta, clique em Fechar e Carregar > Fechar e Carregar no separador Base.
3. Aparecerá uma caixa de diálogo Importar Dados. Selecione a opção Apenas Criar Ligação e clique em OK.
4. O painel Consultas e Ligações aparecerá à direita, mostrando que uma consulta foi adicionada apenas como ligação.
5. Repita os mesmos passos para carregar a segunda tabela como uma consulta apenas com ligação, e verá que esta surge listada ao lado da primeira no painel Consultas e Ligações.
Passo 3: Criar uma Consulta de Referência e uma Coluna Personalizada
1.Clique com o botão direito do rato na consulta que pretende utilizar como primeira tabela na junção cruzada e selecione Referência.
2.Na janela do Editor do Power Query, aceda ao separador Adicionar Colunae clique em Coluna Personalizada, veja a captura de ecrã:
3. Na caixa de diálogo Coluna Personalizada, na caixa Fórmula da Coluna Personalizada, digite o nome da outra tabela que pretende utilizar na junção cruzada e, em seguida, clique no botão OK.
Nota:
Se o nome da sua consulta contiver espaços (por exemplo, Cor do Produto), deverá colocá-lo entre aspas utilizando a sintaxe #«Nome da Consulta» na caixa da fórmula da Coluna Personalizada. Por exemplo, para Cor do Produto, escreva #«Cor do Produto».
4. Aparecerá uma nova coluna personalizada; clique no botão Expandir para revelar o seu conteúdo.
5. No painel expandido, certifique-se de que o nome da coluna que pretende juntar está selecionado. Em seguida, clique em OK.
6.Agora, verá todas as combinações geradas a partir das duas tabelas.
Passo 4: Carregar os dados para a folha de cálculo
Aceda ao separador Base, clique em Fechar e Carregar > Fechar e Carregar. A tabela com todas as combinações será carregada numa nova folha de cálculo.
→
Vantagens
- Lida com Dados Grandes: Excelente desempenho mesmo com milhares de linhas.
- Reutilizável e Atualizável: ao adicionar mais dados ao intervalo de origem, basta atualizar a consulta para que os resultados sejam automaticamente atualizados.
Desvantagens
- Poucos passos adicionais
- Requer conhecimentos básicos do Power Query
Método 3: Cross Join utilizando o Tabela Dinâmica
Este método é indireto, mas pode revelar-se surpreendentemente eficaz quando precisa de gerar todas as combinações sem escrever uma única fórmula. É especialmente útil para utilizadores que preferem uma abordagem mais visual e baseada em cliques, ou para quem trabalha em versões do Excel que não suportam fórmulas de matrizes dinâmicas.
1. Crie duas tabelas separadas para as listas de dados e dê-lhes nomes seguindo os mesmos passos descritos no Passo 1 do Método 2.
2. Selecione a tabela que pretende utilizar como primeira coluna. Em seguida, aceda ao separador Inserir e clique em Tabela Dinâmica. Veja a captura de ecrã:
3. Na caixa de diálogo Tabela Dinâmica a partir de tabela ou intervalo, selecione Planilha existente, escolha a localização da Tabela Dinâmica, assinale Adicionar estes dados ao Modelo de Dados e, em seguida, clique em OK.
4. Quando o painel Campos da Tabela Dinâmica aparecer à direita, marque o nome da coluna da tabela e esta será adicionada automaticamente à área Linhas.
5. Em seguida, mude para o separador Todos no painel Campos da Tabela Dinâmica, selecione a tabela que pretende utilizar como segunda coluna do seu Crossjoin e marque o respetivo nome da coluna para a adicionar à área Linhas. Obterá a Tabela Dinâmica conforme mostrado na captura de ecrã abaixo:
6. Clique numa célula da Tabela Dinâmica, aceda ao separador Estrutura, selecione Esquema do Relatório > Mostrar em Forma Tabular e obterá a Tabela Dinâmica em formato tabular. Veja a captura de ecrã:
7. Continue clicando em Esquema do Relatório > Repetir Todas as Etiquetas dos Itens para apresentar todos os itens em cada linha.
8. Por fim, clique em Totais Gerais > Desativado para Linhas e Colunas.
Agora, a Tabela Dinâmica exibe uma lista clara de todas as combinações, sem linhas ou colunas de resumo.
Vantagens
- Não é necessário fórmula nem Power Query
- Muito fácil e visual
- Ideal para análises rápidas
Desvantagens
- Não é dinâmico
- Requer ações manuais
- O resultado não está ligado aos dados originais
Método 4: Cross Join utilizando uma Função Definida pelo Utilizador (Excel 365 / Excel 2021 e posterior)
Se precisar frequentemente de gerar todas as combinações possíveis entre duas listas, uma Função Definida pelo Utilizador (UDF) oferece uma solução clara, reutilizável e altamente flexível. Com o Excel 365 ou o Excel 2021, a saída em matriz preenchida torna os resultados totalmente dinâmicos, proporcionando-lhe uma forma poderosa e conveniente de automatizar operações de Cross Join.
1.Prima Alt + F11 para abrir o editor do VBA.
2. Em seguida, clique em Inserir > Módulo e copie e cole o código seguinte no módulo em branco.
Function CrossJoin(list1 As Range, list2 As Range)
'Updateby Extendoffice
Dim arr1, arr2, result()
Dim i As Long, j As Long, r As Long
arr1 = list1.Value
arr2 = list2.Value
ReDim result(1 To UBound(arr1, 1) * UBound(arr2, 1), 1 To 2)
r = 1
For i = 1 To UBound(arr1, 1)
For j = 1 To UBound(arr2, 1)
result(r, 1) = arr1(i, 1)
result(r, 2) = arr2(j, 1)
r = r + 1
Next j
Next i
CrossJoin = result
End Function
3. Regresse à folha de cálculo do Excel, introduza a fórmula abaixo e prima Enter. O Excel irá preencher automaticamente todas as combinações.
=CrossJoin(A2:A5, C2:C5)
Conclusão
Em resumo, realizar um Crossjoin no Excel oferece múltiplas soluções flexíveis e eficientes, permitindo-lhe escolher o método mais adequado às suas necessidades específicas e ao seu ambiente de trabalho:
- A fórmula utiliza fórmulas de matrizes dinâmicas no Excel 365 para gerar resultados rapidamente, sem necessidade de programação — ideal para utilizadores ocasionais que preferem soluções com fórmulas nativas.
- O Power Query oferece um processo claro e reutilizável, capaz de lidar com grandes conjuntos de dados, tornando-o a escolha ideal para limpeza de dados e relatórios automatizados.
- O método Tabela Dinâmica pode ser menos direto, mas revela-se altamente eficaz e intuitivo para quem já tem alguma familiaridade.
- A Função Definida pelo Utilizador em VBA proporciona o mais alto nível de personalização e é ideal para cenários que exijam integração em código macro complexo.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos