Note: The other languages of the website are Google-translated. Back to English

Como gerar todas as combinações de 3 ou múltiplas colunas no Excel?

Supondo que eu tenha 3 colunas de dados, agora, quero gerar ou listar todas as combinações dos dados nessas 3 colunas, conforme a captura de tela abaixo mostrada. Você tem bons métodos para resolver essa tarefa no Excel?

Gere todas as combinações com base em 3 colunas de dados usando uma fórmula

Gere todas as combinações com base em 3 ou várias colunas de dados usando o código VBA

Gere todas as combinações com base em 3 ou várias colunas de dados usando um recurso incrível


Gere todas as combinações com base em 3 colunas de dados usando uma fórmula

A seguinte fórmula longa pode ajudar a listar todas as combinações de 3 colunas, faça o seguinte:

1. Clique em uma célula para a qual deseja gerar o resultado e, em seguida, copie e cole a fórmula abaixo:

=IFERROR(INDEX($A$2:$A$4,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$6)*(COUNTA($C$2:$C$5)))))+1)&"-"&INDEX($B$2:$B$6,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$5)),COUNTA($B$2:$B$6))+1)&"-"&INDEX($C$2:$C$5,MOD((ROW(1:1)-1),COUNTA($C$2:$C$5))+1),"")

Nota: Nesta fórmula: A2: A4, B2: B6, C2: C5 são os intervalos de dados que você deseja usar.

2. Em seguida, arraste a alça de preenchimento para baixo até as células até que as células em branco sejam exibidas, ou seja, todas as combinações das 3 colunas foram listadas, consulte a captura de tela:


Gere todas as combinações com base em 3 ou várias colunas de dados usando o código VBA

A longa fórmula acima é um tanto difícil de usar, se houver várias colunas de dados que precisam ser usados, será difícil modificá-la. Aqui, apresentarei um código VBA para lidar com isso rapidamente.

1. Segure o ALT + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o código a seguir na janela do módulo.

Código VBA: Gere todas as combinações de 3 ou várias colunas

Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg  As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A4")  'First column data
Set xDRg2 = Range("B2:B6")  'Second column data
Set xDRg3 = Range("C2:C5")  'Third column data
xStr = "-"   'Separator
Set xRg = Range("E2")  'Output cell
For xFN1 = 1 To xDRg1.Count
    xSV1 = xDRg1.Item(xFN1).Text
    For xFN2 = 1 To xDRg2.Count
        xSV2 = xDRg2.Item(xFN2).Text
      For xFN3 = 1 To xDRg3.Count
        xSV3 = xDRg3.Item(xFN3).Text
        xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
        Set xRg = xRg.Offset(1, 0)
       Next
    Next
Next
End Sub

Nota: No código acima, A2: A4, B2: B6, C2: C5 são o intervalo de dados que você deseja usar, E2 é a célula de saída em que você deseja localizar os resultados. Se você deseja obter todas as combinações de mais colunas, altere e adicione outros parâmetros ao código conforme sua necessidade.

3. Então aperte F5 para executar este código, e todas as combinações das 3 ou várias colunas serão geradas de uma vez, veja a captura de tela:


Gere todas as combinações com base em 3 ou várias colunas de dados usando um recurso incrível

Se você tem Kutools for Excel, com seu poderoso Listar todas as combinações recurso, você pode listar todas as combinações de várias colunas de forma rápida e fácil.

Tips:Para aplicar isso Listar todas as combinações recurso, em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique o recurso de forma rápida e fácil.

Depois de instalar Kutools for Excel, por favor, faça o seguinte:

1. Clique Kutools > inserção > Listar todas as combinações, veja a captura de tela:

2. No Listar todas as combinações caixa de diálogo, especifique os dados das colunas e separadores para listar as combinações conforme a seguinte captura de tela mostrada:

3. Depois de definir os dados e o separador, clique em Ok botão, na próxima caixa de prompt, selecione uma célula para localizar o resultado, veja a captura de tela:

4. E então, clique OK botão, todas as combinações foram geradas imediatamente conforme a captura de tela abaixo mostrada:

Clique para baixar Kutools para Excel e avaliação gratuita agora!


  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas e manutenção de dados; Dividir o conteúdo das células; Combine Linhas Duplicadas e Soma / Média... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Fórmulas favoritas e de inserção rápida, Intervalos, gráficos e imagens; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Agrupamento de tabela dinâmica por número da semana, dia da semana e mais ... Mostrar células desbloqueadas, bloqueadas por cores diferentes; Destacar células que possuem fórmula / nome...
guia kte 201905
  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Comentários (17)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Para gerar qualquer combinação aleatória podemos usar
=INDEX($A$2:$A$4,RANDBETWEEN(1,3))&"-"&INDEX($B$2:$B$6,RANDBETWEEN(1,5))&"-"&INDEX($C$2:$C$5,RANDBETWEEN(1,4))
Este comentário foi feito pelo moderador no site
Muito obrigado por compartilhar este post. Muito obrigado a @Balaji por sua fórmula
Este comentário foi feito pelo moderador no site
NÃO CONSIGO TE AGRADECER! ME economizou MUITO TEMPO!
Este comentário foi feito pelo moderador no site
Como fazer a fórmula abaixo para 5 colunas? Tentando descobrir, mas está dando erro
=IFERROR(INDEX($A$2:$A$4,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$6)*(COUNTA($C$2:$C$5)))))+1)&"-"&INDEX($B$2:$B$6,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$5)),COUNTA($B$2:$B$6))+1)&"-"&INDEX($C$2:$C$5,MOD((ROW(1:1)-1),COUNTA($C$2:$C$5))+1),"")
Este comentário foi feito pelo moderador no site
Olá, Nikhil, Para obter todas as combinações de 5 colunas, pode ser que o código VBA abaixo possa ajudá-lo, altere as referências de célula aos seus dados.
Sub ListAllCombinations()
'Atualizar por Extendoffice
Escurecer xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 como intervalo
Dim xRg como intervalo
Dim xStr As String
Escurecer xFN1, xFN2, xFN3, xFN4, xFN5 como inteiro
Escurecer xSV1, xSV2, xSV3, xSV4, xSV5 como string
Set xDRg1 = Range("A2:A7") 'Dados da primeira coluna
Set xDRg2 = Range("B2:B7") 'Dados da segunda coluna
Set xDRg3 = Range("C2:C7") 'Dados da terceira coluna
Set xDRg4 = Range("D2:D7") 'Dados da quarta coluna
Set xDRg5 = Range("E2:E7") 'Dados da quinta coluna
xStr = "-" 'Separador
Set xRg = Range("H2") 'Célula de saída
Para xFN1 = 1 Para xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Texto
Para xFN2 = 1 Para xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Texto
Para xFN3 = 1 Para xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Texto
Para xFN4 = 1 Para xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Texto
Para xFN5 = 1 Para xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Texto
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5
Definir xRg = xRg. Offset(1, 0)
Seguinte
Seguinte
Seguinte
Seguinte
Seguinte
End SubPor favor, tente, espero que possa ajudá-lo!
Este comentário foi feito pelo moderador no site
Olá skyyang, alterei o código para 7 rolos, mas vendo o Excel ter apenas 1,048,576 linhas o código VBA não consegue dar todas as combinações. Tem ideia de como posso continuar em outras colunas? Eu acho que este código - Set xRg = xRg.Offset(1, 0) precisava mudar
Este comentário foi feito pelo moderador no site
Olá skyyang, alterei o código para 7 rolos, mas vendo o Excel ter apenas 1,048,576 linhas o código VBA não consegue dar todas as combinações.
Você tem ideia de como posso continuar em outras colunas?
Eu acho que este código - Set xRg = xRg.Offset(1, 0)
precisava mudar 
Este comentário foi feito pelo moderador no site
=IFERROR(INDEX($A$2:$A$5,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$3)*(COUNTA($C$2:$C$3)* (COUNTA($D$2:$D$4)* (COUNTA($E$2:$E$6)* (COUNTA($H$2:$H$6)* (COUNTA($G$2:$G$6)* (COUNTA($H$2:$H$6))))))))))+1)&"-"&INDEX($B$2:$B$3,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$3)),COUNTA($B$2:$B$3))+1)&"-" &INDEX($C$2:$C$3,MOD(INT((ROW(1:1)-1)/COUNTA($D$2:$D$4)),COUNTA($C$2:$C$3))+1)&"-" &INDEX($D$2:$D$4,MOD(INT((ROW(1:1)-1)/COUNTA($E$2:$E$6)),COUNTA($D$2:$D$4))+1)&"-" &INDEX($E$2:$E$6,MOD(INT((ROW(1:1)-1)/COUNTA($F$2:$F$6)),COUNTA($E$2:$E$6))+1)&"-" &INDEX($F$2:$F$6,MOD(INT((ROW(1:1)-1)/COUNTA($G$2:$G$6)),COUNTA($F$2:$F$6))+1)&"-" &INDEX($G$2:$G$6,MOD(INT((ROW(1:1)-1)/COUNTA($H$2:$H$6)),COUNTA($G$2:$G$6))+1)&"-"&INDEX($H$2:$H$6,MOD((ROW(1:1)-1),COUNTA($H$2:$H$6))+1),"")
Este comentário foi feito pelo moderador no site
eu preciso desta fórmula para 4 colunas
Este comentário foi feito pelo moderador no site
Muito obrigado. Exatamente o que eu preciso :-)))
Este comentário foi feito pelo moderador no site
Muito obrigado por este código. Modifiquei o código para a quantidade de coluna que preciso (25). Obrigado,
Este comentário foi feito pelo moderador no site
HiIn código VBA eu usei quatro colunas e intervalo da coluna são E2:E75, B2:B267, C2:C195 & D2:D267. O alcance de saída é J2. Nesse caso, o resultado de saída foi superior ao limite de linha. Por favor, ajude a resolver o erro
Este comentário foi feito pelo moderador no site
Olá Então este é um código para 9 colunas :')
Sub ListaTodasCombinações()
'Atualizar por Extendoffice
Escurecer xDRg1, xDRg2, xDRg3, xDRg4, xDRg5, xDRg6, xDRg7, xDRg8, xDRg9 como intervalo
Dim xRg como intervalo
Dim xStr As String
Escurecer xFN1, xFN2, xFN3, xFN4, xFN5, xFN6, xFN7, xFN8, xFN9 como inteiro
Escurecer xSV1, xSV2, xSV3, xSV4, xSV5, xSV6, xSV7, xSV8, xSV9 como string
Set xDRg1 = Range("A2:A3") 'Dados da primeira coluna
Set xDRg2 = Range("B2:B3") 'Dados da segunda coluna
Set xDRg3 = Range("C2:C10") 'Dados da terceira coluna
Set xDRg4 = Range("D2:D2") 'Dados da terceira coluna
Set xDRg5 = Range("E2:E3") 'Dados da terceira coluna
Set xDRg6 = Range("F2:F3") 'Dados da terceira coluna
Set xDRg7 = Range("G2:G4") 'Dados da terceira coluna
Set xDRg8 = Range("H2:H3") 'Dados da terceira coluna
Set xDRg9 = Range("I2:I3") 'Dados da terceira coluna
xStr = "-" 'Separador
Set xRg = Range("K2") 'Célula de saída
Para xFN1 = 1 Para xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Texto
Para xFN2 = 1 Para xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Texto
Para xFN3 = 1 Para xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Texto
Para xFN4 = 1 Para xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Texto
Para xFN5 = 1 Para xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Texto
Para xFN6 = 1 Para xDRg6.Count
xSV6 = xDRg6.Item(xFN6).Texto
Para xFN7 = 1 Para xDRg7.Count
xSV7 = xDRg7.Item(xFN7).Texto
Para xFN8 = 1 Para xDRg8.Count
xSV8 = xDRg8.Item(xFN8).Texto
Para xFN9 = 1 Para xDRg9.Count
xSV9 = xDRg9.Item(xFN9).Texto
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9
Definir xRg = xRg. Offset(1, 0)
Seguinte
Seguinte
Seguinte
Seguinte
Seguinte
Seguinte
Seguinte
Seguinte
Seguinte
End Sub
Este comentário foi feito pelo moderador no site
조합의 나열을 "단어-단어-단어" 순으로 나열하는 법은 이해했습니다.
그런데 "단어"가 아니라 숫자일 경우,
즉 숫자의 조합을 단순 나열이 아닌 덧셈이나 곱셈으로 적용하려면 어떻게 해야하는지 알 수 알 어떻게 해야하는지 알 수 알?

'VBA 코드 : 3 개 또는 여러 열의 모든 조합 생성' 에서 말이죠.

"1-1-1" 로 엑셀에 결과 값이 표기되는 것이 아니고

-1 로 엑셀에 표기될 수 있게 말입니다.
Este comentário foi feito pelo moderador no site
Bonjour, comente justo para que cada valor esteja localizado em uma coluna distinta e não separada por um pneu ?
Este comentário foi feito pelo moderador no site
Esse ou eles me ajudam bastante e combinam um item de cada vez. Mas também precisa combinar dois itens de cada coluna, sem repetição. Alguém poderia me ajudar nisso?
Este comentário foi feito pelo moderador no site
Esse ou eles me ajudam bastante e combinam um item de cada vez. Mas também precisa combinar dois itens de cada coluna, sem repetição. Alguém poderia me ajudar nisso?
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL