Combine colunas no Excel e gere todas as combinações possíveis

Excel consolidar

Ter dois conjuntos de dados diferentes, por exemplo, uma lista de clientes e uma lista de produtos, e deseja combiná-los para obter uma nova lista com todas as combinações possíveis de clientes e produtos?

A solução é bastante simples, viável com o MS Excel em poucos minutos e, mesmo com milhares de entradas, não requer muito esforço - muito menos do que copiar a segunda lista para cada valor da primeira lista - essa última solução leve horas com centenas de entradas. Veja abaixo como combinar colunas do Excel e gerar todas as combinações possíveis do Excel.

Como gerar ou listar todas as permutações possíveis no Excel

• Crie um identificador numérico de 1 para o número de entradas para cada arquivo de origem e, no arquivo de resultado:
• Crie um identificador numérico (ID) na coluna A indo de 1 para a multiplicação da contagem das primeiras entradas do arquivo e das segundas entradas do arquivo, criando tantas linhas quanto a combinação de ambos os conjuntos de dados,
• Adicione uma coluna, insira esta fórmula e expanda-a para a última linha = ROUNDUP (A2 / [contagem de entradas do segundo arquivo]; 0),
• Adicione uma coluna, insira esta fórmula e expanda-a para a última linha = A2 - ([Contagem de entradas do segundo arquivo] * (B2-1)),
• Adicione quantas colunas quiser do primeiro e segundo arquivos e faça vlookups no identificador correspondente no arquivo de resultado e nos arquivos de origem.

Consolidar dados no Excel

Veja abaixo um exemplo completo para combinar colunas de planilhas do Excel, com uma lista de clientes e uma lista de produtos.

Comece criando identificadores em ambos os arquivos, adicionando uma coluna à esquerda, inserindo os valores 1 e 2 nas duas primeiras linhas, selecionando as duas células, movendo o cursor do mouse para o canto inferior direito da seleção e clicando duas vezes em o sinal + para expandir o incremento do identificador para a última linha.

Como resultado, os identificadores foram incrementados até a última linha.

Como combinar colunas no excel

Pegue o número máximo de identificadores dos clientes (C) e Produtos (P). Crie um novo arquivo, com um identificador de coluna, e repita a operação de expansão do identificador, até a linha (C * P) + 1. No exemplo abaixo, 7 valores diferentes para Clientes e 7 para Produtos, resultando em 7 * 7 = 49 combinações, + 1 linha para compensar a linha de cabeçalho.

Como combinar células no excel

Torne -se um Excel Pro: participe do nosso curso!

Eleve suas habilidades de iniciantes a heróis com nosso curso básico do Excel 365, projetado para torná -lo proficiente em apenas algumas sessões.

Inscreva -se aqui

Na coluna B, colocaremos as linhas P por cliente, pois cada uma delas terá uma linha por produto. Isso é feito usando a fórmula abaixo na segunda linha da planilha e expandindo-a para a parte inferior (lembre-se do clique duplo no ícone + no canto inferior da seleção da célula para fazer isso), onde X é a segunda contagem de entradas de arquivo

Na coluna C, colocaremos uma contagem para cada um dos clientes, de 1 para contagem de produtos. Mesma operação de antes, com outra fórmula (identificador de linha atual menos a contagem alcançada para a linha anterior do Cliente), sendo X a segunda contagem de entradas de arquivo

Combine várias colunas no excel em uma coluna

Verifique se funcionou. Os clientes são repetidos P vezes e, para cada um deles, o identificador de produtos é repetido de 1 para P

Gere todas as permutações

E é isso! Em seguida, para cada coluna que você deseja substituir do arquivo Clientes ou Produtos, adicione uma nova coluna e execute um Vlookup no identificador correspondente e no arquivo de origem.

Perguntas Frequentes

Como os usuários podem combinar com eficiência várias colunas no Excel para gerar uma lista de todas as combinações possíveis sem usar fórmulas complexas?

Os usuários podem utilizar o recurso de consulta de energia do Excel para mesclar colunas e criar combinações. Ao carregar as colunas como uma tabela para consultar, aplicando uma operação de mesclagem personalizada e, em seguida, expandindo a tabela resultante, todas as combinações possíveis podem ser sistematicamente geradas e devolvidas ao Excel.

Conclua o Excel 2019 para iniciantes em vídeo