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

Come generare tutte le combinazioni di 3 o più colonne in Excel?

Supponendo, ho 3 colonne di dati, ora, voglio generare o elencare tutte le combinazioni dei dati in queste 3 colonne come mostrato nell'immagine sottostante. Hai dei buoni metodi per risolvere questa attività in Excel?

Genera tutte le combinazioni in base a 3 colonne di dati utilizzando una formula

Genera tutte le combinazioni basate su 3 o più colonne di dati utilizzando il codice VBA

Genera tutte le combinazioni in base a 3 o più colonne di dati utilizzando una fantastica funzionalità


Genera tutte le combinazioni in base a 3 colonne di dati utilizzando una formula

La seguente formula lunga può aiutare a elencare tutte le combinazioni di 3 colonne, per favore fai come segue:

1. Per favore, fai clic su una cella in cui visualizzare il risultato, quindi copia e incolla la formula seguente:

=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),"")

Note:: In questa formula: A2: A4, B2: B6, C2: C5 sono gli intervalli di dati che desideri utilizzare.

2. Quindi, trascina il quadratino di riempimento verso il basso sulle celle finché non vengono visualizzate celle vuote, ciò significa che sono state elencate tutte le combinazioni delle 3 colonne, vedi screenshot:


Genera tutte le combinazioni basate su 3 o più colonne di dati utilizzando il codice VBA

La formula lunga sopra è alquanto difficile da usare, se ci sono più colonne che devono essere utilizzate i dati, sarà problematica la modifica. Qui, introdurrò un codice VBA per affrontarlo rapidamente.

1. Tieni premuto il ALT + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

2. Clic inserire > Moduloe incolla il codice seguente nella finestra del modulo.

Codice VBA: genera tutte le combinazioni di 3 o più colonne

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

Note:: Nel codice sopra, A2: A4, B2: B6, C2: C5 sono l'intervallo di dati che desideri utilizzare, E2 è la cella di output in cui si desidera individuare i risultati. Se desideri ottenere tutte le combinazioni di più colonne, modifica e aggiungi altri parametri al codice secondo le tue necessità.

3. Quindi, premere F5 chiave per eseguire questo codice e tutte le combinazioni delle 3 o più colonne verranno generate contemporaneamente, vedi screenshot:


Genera tutte le combinazioni in base a 3 o più colonne di dati utilizzando una fantastica funzionalità

Se si dispone di Kutools for Excel, con i suoi potenti Elenca tutte le combinazioni è possibile elencare tutte le combinazioni di più colonne in modo rapido e semplice.

Suggerimenti::Per applicare questo Elenca tutte le combinazioni in primo luogo, dovresti scaricare il file Kutools for Excele quindi applica la funzione in modo rapido e semplice.

Dopo l'installazione Kutools for Excel, per favore fai come questo:

1. Clic Kutools > inserire > Elenca tutte le combinazioni, vedi screenshot:

2. Nel Elenca tutte le combinazioni finestra di dialogo, specificare i dati delle colonne ei separatori per elencare le combinazioni come mostrato nella seguente schermata:

3. Dopo aver impostato i dati e il separatore, quindi fare clic su Ok pulsante, nella finestra di messaggio successiva, seleziona una cella per individuare il risultato, vedi screenshot:

4. Quindi fare clic su OK pulsante, tutte le combinazioni sono state generate immediatamente come mostrato nell'immagine sottostante:

Fai clic per scaricare Kutools per Excel e prova gratuita ora!


  • Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
  • Unisci celle / righe / colonne e conservazione dei dati; Contenuto delle celle divise; Combina righe duplicate e somma / media... Impedisci celle duplicate; Confronta intervalli...
  • Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
  • Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
  • Formule preferite e di inserimento rapido, Intervalli, grafici e immagini; Crittografa celle con password; Crea mailing list e invia email ...
  • Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
  • Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
  • Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
  • Raggruppamento tabelle pivot per numero della settimana, giorno della settimana e altro ... Mostra celle sbloccate e bloccate da diversi colori; Evidenzia le celle che hanno formula / nome...
scheda kte 201905
  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
fondo officetab
Commenti (17)
Ancora nessuna valutazione. Puoi essere il primo a votare!
Questo commento è stato fatto dal moderatore sul sito
Per generare qualsiasi combinazione casuale possiamo usare
=INDEX($A$2:$A$4,RANDBETWEEN(1,3))&"-"&INDEX($B$2:$B$6,RANDBETWEEN(1,5))&"-"&INDEX($C$2:$C$5,RANDBETWEEN(1,4))
Questo commento è stato fatto dal moderatore sul sito
Grazie mille per aver condiviso questo post. Grazie mille a @Balaji per la sua formula
Questo commento è stato fatto dal moderatore sul sito
NON POSSO RINGRAZIARTI ABBASTANZA! MI HA SALVATO TANTO TEMPO!
Questo commento è stato fatto dal moderatore sul sito
Come fare sotto la formula per 5 colonne? Sto cercando di capire ma sta dando errore
=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),"")
Questo commento è stato fatto dal moderatore sul sito
Ciao, Nikhil, per ottenere tutte le combinazioni da 5 colonne, il codice VBA sottostante potrebbe aiutarti, per favore cambia i riferimenti di cella ai tuoi dati.
Sub ListAllCombinations()
'Aggiorna entro Extendoffice
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 come intervallo
Dim xRg come intervallo
Dim xStr come stringa
Dim xFN1, xFN2, xFN3, xFN4, xFN5 come intero
Dim xSV1, xSV2, xSV3, xSV4, xSV5 come stringa
Set xDRg1 = Range("A2:A7") 'Dati della prima colonna
Imposta xDRg2 = Range("B2:B7") 'Dati della seconda colonna
Set xDRg3 = Range("C2:C7") 'Dati della terza colonna
Set xDRg4 = Range("D2:D7") 'Dati della quarta colonna
Set xDRg5 = Range("E2:E7") 'Dati della quinta colonna
xStr = "-" 'Separatore
Imposta xRg = Range("H2") 'Cella di output
Per xFN1 = 1 Per xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Testo
Per xFN2 = 1 Per xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Testo
Per xFN3 = 1 Per xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Testo
Per xFN4 = 1 Per xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Testo
Per xFN5 = 1 Per xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Testo
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5
Imposta xRg = xRg.Offset(1, 0)
Successiva
Successiva
Successiva
Successiva
Successiva
End SubPer favore prova, spero che possa aiutarti!
Questo commento è stato fatto dal moderatore sul sito
Ciao skyyang, ho cambiato il codice per 7 rotoli, ma vedendo Excel ha solo 1,048,576 righe il codice VBA non può dare tutte le combinazioni. Hai un'idea di come posso continuare su altre colonne? Penso che questo codice - Set xRg = xRg.Offset(1, 0) debba essere cambiato
Questo commento è stato fatto dal moderatore sul sito
Ciao skyyang, ho cambiato il codice per 7 rotoli, ma vedendo Excel ha solo 1,048,576 righe il codice VBA non può dare tutte le combinazioni.
Hai un'idea di come posso continuare su altre colonne?
Penso che questo codice - Set xRg = xRg.Offset(1, 0)
necessario cambiare 
Questo commento è stato fatto dal moderatore sul sito
=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),"")
Questo commento è stato fatto dal moderatore sul sito
ho bisogno di questa formula per 4 colonne
Questo commento è stato fatto dal moderatore sul sito
Grazie mille. Esattamente quello che mi serve :-)))
Questo commento è stato fatto dal moderatore sul sito
Grazie mille per questo codice. Ho modificato il codice per la quantità di colonna di cui ho bisogno (25). Grazie,
Questo commento è stato fatto dal moderatore sul sito
HiIn codice VBA ho usato quattro colonne e l'intervallo della colonna sono E2: E75, B2: B267, C2: C195 e D2: D267. La gamma di uscita è J2. In questo caso, il risultato dell'output è stato superiore al limite di riga. Si prega di aiutare a risolvere l'errore
Questo commento è stato fatto dal moderatore sul sito
Ciao Quindi questo è un codice per 9 colonne :')
SottoelencoTutte le combinazioni()
'Aggiorna entro Extendoffice
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5, xDRg6, xDRg7, xDRg8, xDRg9 come intervallo
Dim xRg come intervallo
Dim xStr come stringa
Dim xFN1, xFN2, xFN3, xFN4, xFN5, xFN6, xFN7, xFN8, xFN9 Come Intero
Dim xSV1, xSV2, xSV3, xSV4, xSV5, xSV6, xSV7, xSV8, xSV9 come stringa
Set xDRg1 = Range("A2:A3") 'Dati della prima colonna
Imposta xDRg2 = Range("B2:B3") 'Dati della seconda colonna
Set xDRg3 = Range("C2:C10") 'Dati della terza colonna
Set xDRg4 = Range("D2:D2") 'Dati della terza colonna
Set xDRg5 = Range("E2:E3") 'Dati della terza colonna
Set xDRg6 = Range("F2:F3") 'Dati della terza colonna
Set xDRg7 = Range("G2:G4") 'Dati della terza colonna
Set xDRg8 = Range("H2:H3") 'Dati della terza colonna
Set xDRg9 = Range("I2:I3") 'Dati della terza colonna
xStr = "-" 'Separatore
Imposta xRg = Range("K2") 'Cella di output
Per xFN1 = 1 Per xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Testo
Per xFN2 = 1 Per xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Testo
Per xFN3 = 1 Per xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Testo
Per xFN4 = 1 Per xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Testo
Per xFN5 = 1 Per xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Testo
Per xFN6 = 1 Per xDRg6.Count
xSV6 = xDRg6.Item(xFN6).Testo
Per xFN7 = 1 Per xDRg7.Count
xSV7 = xDRg7.Item(xFN7).Testo
Per xFN8 = 1 Per xDRg8.Count
xSV8 = xDRg8.Item(xFN8).Testo
Per xFN9 = 1 Per xDRg9.Count
xSV9 = xDRg9.Item(xFN9).Testo
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9
Imposta xRg = xRg.Offset(1, 0)
Successiva
Successiva
Successiva
Successiva
Successiva
Successiva
Successiva
Successiva
Successiva
End Sub
Questo commento è stato fatto dal moderatore sul sito
조합의 나열을 "단어-단어-단어" 순으로 나열하는 법은 이해했습니다.
그런데 "단어" 가 아니라 숫자일 경우,
즉 숫자의 조합을 단순 나열이 아닌 덧셈이나 곱셈으로 적용하려면 어떻게 해야는지 안 섹Ꝙ 안 수 안섹겚

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

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

-1 로 엑셀에 표기될 수 있게 말입니다.
Questo commento è stato fatto dal moderatore sul sito
Bonjour, comment faire pour que chaque valeurs soient placees dans une colonne distinte en non séparées par un tiret ?
Questo commento è stato fatto dal moderatore sul sito
Esse code me ajudou bastante e combinou um item de cada coluna entre elas. Mas anche preciso combinare due elementi di ogni colonna, senza ripetizioni. Alguém poderia me ajudar nisso?
Questo commento è stato fatto dal moderatore sul sito
Esse code me ajudou bastante e combinou um item de cada coluna entre elas. Mas anche preciso combinare due elementi di ogni colonna, senza ripetizioni. Alguém poderia me ajudar nisso?
Non ci sono ancora commenti pubblicati qui
Lasciate i vostri commenti
Pubblicazione come ospite
×
Valuta questo post:
0   Personaggi
Posizioni suggerite

Seguici

Copyright © 2009 - www.extendoffice.com. | Tutti i diritti riservati. Offerto da ExtendOffice, | Mappa del sito
Microsoft e il logo Office sono marchi o marchi registrati di Microsoft Corporation negli Stati Uniti e / o in altri paesi.
Protetto da Sectigo SSL