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

如何在Excel中生成3列或多列的所有組合?

假設我有3列數據,現在,我想在這3列中生成或列出數據的所有組合,如下圖所示。 您有什麼好的方法可以在Excel中解決此任務?

使用公式基於3列數據生成所有組合

使用VBA代碼基於3列或多列數據生成所有組合

使用超讚功能,根據3列或多列數據生成所有組合


使用公式基於3列數據生成所有組合

下面的長公式可以幫助列出3列的所有組合,請這樣做:

1。 請單擊要輸出結果的單元格,然後將以下公式複制並粘貼到其中:

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

備註:在此公式中: A2:A4, B2:B6, C2:C5 是您要使用的數據范圍。

2。 然後,將填充手柄向下拖動到單元格,直到顯示空白單元格,這意味著已列出3列的所有組合,請參見屏幕截圖:


使用VBA代碼基於3列或多列數據生成所有組合

上面的長公式很難使用,如果需要使用多列數據,則修改起來很麻煩。 在這裡,我將介紹一個VBA代碼來快速處理它。

1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,然後將以下代碼粘貼到“模塊窗口”中。

VBA代碼:生成3列或多列的所有組合

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

備註:在上面的代碼中, A2:A4, B2:B6, C2:C5 是您要使用的數據范圍, E2 是您要查找結果的輸出單元格。 如果要獲得更多列的所有組合,請根據需要更改並將其他參數添加到代碼中。

3。 然後按 F5 鍵來運行此代碼,並且將同時生成3列或多列的所有組合,請參見屏幕截圖:


使用超讚功能,根據3列或多列數據生成所有組合

如果你有 Excel的Kutools,其功能強大 列出所有組合 功能,您可以快速輕鬆地列出多列的所有組合。

保養竅門:要應用此 列出所有組合 功能,首先,您應該下載 Excel的Kutools,然後快速輕鬆地應用該功能。

安裝後 Excel的Kutools,請這樣做:

1。 點擊 庫工具 > 插入 > 列出所有組合,請參見屏幕截圖:

2。 在 列出所有組合 在對話框中,指定列數據和分隔符以列出組合,如下面的屏幕截圖所示:

3。 設置數據和分隔符後,然後單擊 Ok 按鈕,在下一個提示框中,選擇一個單元格以找到結果,請參見屏幕截圖:

4。 然後,單擊 OK 按鈕,將立即生成所有組合,如下圖所示:

點擊下載Kutools for Excel並立即免費試用!


  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 和保存數據; 拆分單元格內容; 合併重複的行和總和/平均值...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 收藏并快速插入公式,範圍,圖表和圖片; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 數據透視表分組依據 週號,週幾等 顯示未鎖定的單元格 用不同的顏色 突出顯示具有公式/名稱的單元格...
kte選項卡201905
  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部
按評論排序
留言 (17)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
要生成任何隨機組合,我們可以使用
=INDEX($A$2:$A$4,RANDBETWEEN(1,3))&"-"&INDEX($B$2:$B$6,RANDBETWEEN(1,5))&"-"&INDEX($C$2:$C$5,RANDBETWEEN(1,4))
網站主持人對此評論進行了最小化
非常感謝分享這篇文章。 非常感謝@Balaji 的公式
網站主持人對此評論進行了最小化
我不知道該怎麼感謝你才足夠! 為我節省了很多時間!
網站主持人對此評論進行了最小化
如何為 5 列執行以下公式? 試圖弄清楚但它給出的錯誤
=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),"")
網站主持人對此評論進行了最小化
您好,Nikhil,對於從 5 列中獲取所有組合,可能是下面的 VBA 代碼可以幫助您,請更改對您數據的單元格引用。
子列表AllCombinations()
'更新通過 Extendoffice
將 xDRg1、xDRg2、xDRg3、xDRg4、xDRg5 調暗為範圍
將 xRg 調暗為範圍
將 xStr 調暗為字符串
將 xFN1、xFN2、xFN3、xFN4、xFN5 調暗為整數
將 xSV1、xSV2、xSV3、xSV4、xSV5 調暗為字符串
Set xDRg1 = Range("A2:A7") '第一列數據
Set xDRg2 = Range("B2:B7") '第二列數據
Set xDRg3 = Range("C2:C7") '第三列數據
Set xDRg4 = Range("D2:D7") '第四列數據
Set xDRg5 = Range("E2:E7") '第五列數據
xStr = "-" '分隔符
Set xRg = Range("H2") '輸出單元格
對於 xFN1 = 1 至 xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Text
對於 xFN2 = 1 至 xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Text
對於 xFN3 = 1 至 xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Text
對於 xFN4 = 1 至 xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Text
對於 xFN5 = 1 至 xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5
設置 xRg = xRg.Offset(1, 0)
下一頁
下一頁
下一頁
下一頁
下一頁
完子請試一試,希望能幫到你!
網站主持人對此評論進行了最小化
你好 skyyang,我更改了 7 卷的代碼,但看到 Excel 只有 1,048,576 行 VBA 代碼無法給出所有組合。你知道我如何在其他列上繼續嗎? 我認為此代碼 - Set xRg = xRg.Offset(1, 0) 需要更改
網站主持人對此評論進行了最小化
你好 skyyang,我更改了 7 卷的代碼,但看到 Excel 只有 1,048,576 行,VBA 代碼無法給出所有組合。
你知道我如何繼續其他專欄嗎?
我認為這段代碼 - Set xRg = xRg.Offset(1, 0)
需要改變 
網站主持人對此評論進行了最小化
=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),"")
網站主持人對此評論進行了最小化
我需要這個公式 4 列
網站主持人對此評論進行了最小化
非常感謝。 正是我需要的:-)))
網站主持人對此評論進行了最小化
非常感謝您提供此代碼。 我已經修改了我需要的列數量的代碼(25)。 謝謝,
網站主持人對此評論進行了最小化
在 VBA 代碼中,我使用了四列,列的範圍是 E2:E75、B2:B267、C2:C195 和 D2:D267。 輸出範圍為 J2。 在這種情況下,輸出結果超出了行限制。 請幫助解決錯誤
網站主持人對此評論進行了最小化
你好所以這是9列的代碼:')
子列表AllCombinations()
'更新通過 Extendoffice
將 xDRg1、xDRg2、xDRg3、xDRg4、xDRg5、xDRg6、xDRg7、xDRg8、xDRg9 調暗為範圍
將 xRg 調暗為範圍
將 xStr 調暗為字符串
將 xFN1、xFN2、xFN3、xFN4、xFN5、xFN6、xFN7、xFN8、xFN9 調暗為整數
將 xSV1、xSV2、xSV3、xSV4、xSV5、xSV6、xSV7、xSV8、xSV9 調暗為字符串
Set xDRg1 = Range("A2:A3") '第一列數據
Set xDRg2 = Range("B2:B3") '第二列數據
set xDRg3 = Range("C2:C10") '第三列數據
Set xDRg4 = Range("D2:D2") '第三列數據
Set xDRg5 = Range("E2:E3") '第三列數據
set xDRg6 = Range("F2:F3") '第三列數據
set xDRg7 = Range("G2:G4") '第三列數據
set xDRg8 = Range("H2:H3") '第三列數據
set xDRg9 = Range("I2:I3") '第三列數據
xStr = "-" '分隔符
Set xRg = Range("K2") '輸出單元格
對於 xFN1 = 1 至 xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Text
對於 xFN2 = 1 至 xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Text
對於 xFN3 = 1 至 xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Text
對於 xFN4 = 1 至 xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Text
對於 xFN5 = 1 至 xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Text
對於 xFN6 = 1 至 xDRg6.Count
xSV6 = xDRg6.Item(xFN6).Text
對於 xFN7 = 1 至 xDRg7.Count
xSV7 = xDRg7.Item(xFN7).Text
對於 xFN8 = 1 至 xDRg8.Count
xSV8 = xDRg8.Item(xFN8).Text
對於 xFN9 = 1 至 xDRg9.Count
xSV9 = xDRg9.Item(xFN9).Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9
設置 xRg = xRg.Offset(1, 0)
下一頁
下一頁
下一頁
下一頁
下一頁
下一頁
下一頁
下一頁
下一頁
END SUB
網站主持人對此評論進行了最小化
조합의 나열을 "단어-단어-단어" 순으로 나열하는 법은 이해했습니다。
그런데 "단어"가 아니라 숫자일 경우,
즉 숫자의 조합을 단순 나열이 아닌 덧셈이나 곱셈으로 적용하려면 어떻게 해야하는지 알요 있을

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

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

-1 로 엑셀에 표기될 수 있게 말입니다。
網站主持人對此評論進行了最小化
Bonjour, comment faire pour que chaque valeurs soient placées dans une colne distincte en non séparées par untiret ?
網站主持人對此評論進行了最小化
Esse código me ajudou bastante e combinou um item de cada coluna entre elas。 Mas também preciso combinar dois itens de cada coluna, sem repetição。 Alguém poderia me ajudar nisso?
網站主持人對此評論進行了最小化
Esse código me ajudou bastante e combinou um item de cada coluna entre elas。 Mas também preciso combinar dois itens de cada coluna, sem repetição。 Alguém poderia me ajudar nisso?
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點

關注我們

版權所有©2009 - 萬維網。extendoffice.com。 | 版權所有。 供電 ExtendOffice。 |
Microsoft和Office徽標是Microsoft Corporation在美國和/或其他國家的商標或註冊商標。
受Sectigo SSL保護