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

如何在Excel中列出或生成所有可能的組合?

假設我有以下兩列數據,現在,我想基於兩個值列表生成所有可能組合的列表,如左圖所示。 也許,如果值很少,則可以一一列出所有組合,但是,如果有幾列需要列出多個值,則可能是組合,這裡有一些快速技巧可以幫助您解決Excel中的此問題。

用公式列出或生成兩個列表中的所有可能組合

使用VBA代碼列出或生成來自三個或更多列表的所有可能組合

列出或生成具有強大功能的多個列表中的所有可能組合


用公式列出或生成兩個列表中的所有可能組合

下面的長公式可以幫助您快速列出兩個列表值的所有可能組合,請執行以下操作:

1。 輸入以下公式或將其複製到空白單元格中,在這種情況下,我將其輸入到單元格D2中,然後按 Enter 獲得結果的關鍵,請參見屏幕截圖:

=IF(ROW()-ROW($D$2)+1>COUNTA($A$2:$A$5)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$5,INT((ROW()-ROW($D$2))/COUNTA($B$2:$B$4)+1))&"-"&INDEX($B$2:$B$4,MOD(ROW()-ROW($D$2),COUNTA($B$2:$B$4))+1))

備註:在以上公式中, $ A $ 2:$ A $ 5 是第一列值的範圍,並且 $ B $ 2:$ B $ 4 是您要列出所有可能組合的第二個列表值的範圍, $ D $ 2 是您輸入公式的單元格,則可以根據需要更改單元格引用。

2。 然後選擇單元格D2,並將填充手柄向下拖動到單元格,直到獲得空白單元格,並且所有可能的組合均已基於兩個列表值列出。 看截圖:


使用VBA代碼列出或生成來自三個或更多列表的所有可能組合

也許上面的公式對您來說很難應用,如果有多列數據,則修改起來會很麻煩。 在這裡,我將介紹一個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:A5")  'First column data
Set xDRg2 = Range("B2:B4")  'Second column data
Set xDRg3 = Range("C2:C4")  '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:A5, B2:B4, C2:C4 是您要使用的數據范圍, E2 是您要查找結果的輸出單元格。 如果要獲得更多列的所有組合,請根據需要更改並將其他參數添加到代碼中。

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


列出或生成具有強大功能的多個列表中的所有可能組合

如果有多個列表值需要列出可能的組合,則可能很難修改代碼。 在這裡,我可以推荐一個功能強大的工具- Excel的Kutools,它包含一個方便的功能 列出所有組合 可以根據給定的數據列表快速列出所有可能的組合。

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

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

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

2。 在 列出所有組合 對話框中,進行如下所示的演示操作:

3。 然後,所有指定的值和分隔符都已列在對話框中,請參見屏幕截圖:

4然後單擊 Ok 按鈕,然後會彈出一個提示框,提醒您選擇一個單元格以輸出結果,請參見屏幕截圖:

5。 點擊 OK,已根據工作表生成了基於給定列表的所有可能組合,如以下屏幕截圖所示:

單擊以立即下載Kutools for Excel!


更多相關文章:

  • 生成3列或多列的所有組合
  • 假設我有3列數據,現在,我想在這3列中生成或列出數據的所有組合,如下圖所示。 您有什麼好的方法可以在Excel中解決此任務?
  • 查找等於給定總和的所有組合
  • 例如,我有以下數字列表,現在,我想知道列表中的數字組合總計為480,在下面的屏幕截圖中,您可以看到有五組可能的組合加起來等於到480,例如300 + 60 + 120、300 + 60 + 40 + 80等。本文,我將討論一些方法來查找哪些單元格在Excel中求和成特定值。
  • 生成或列出所有可能的排列
  • 例如,我有三個字符XYZ,現在,我想基於這三個字符列出所有可能的排列,以獲得六個不同的結果,例如:XYZ,XZY,YXZ,YZX,ZXY和ZYX。 在Excel中,如何根據不同的字符數快速生成或列出所有排列?
  • 生成所有可能的4位數字組合的列表
  • 在某些情況下,我們可能需要生成一個由4到0的所有可能的9位數字組成的列表,這意味著要生成一個0000、0001、0002…9999的列表。 為了快速解決Excel中的列表任務,我為您介紹了一些技巧。

 


最佳辦公效率工具

Kutools for Excel 解決了你的大部分問題,並將你的生產力提高了 80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過 300 項強大的功能. 支持 Office / Excel 2007-2021 和 365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能 30 天免費試用。 60 天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部

 

按評論排序
留言 (42)
5中的5評分 · 1評級
網站主持人對此評論進行了最小化
這是一個絕妙的公式! 我不知道它是如何工作的,但我只是更改了 collum A 和 B 以匹配我的列表的長度,並將我的輸出放在 D1 中。
網站主持人對此評論進行了最小化
示例:我的值列表是 1,2,3,4,5,6......80 ,我希望 kutool 顯示一組 2 個數字的所有組合,例如:1-2 , 1-3 , 1-4 , 1-5 , ....................79-80 。 使用 KUTOOL 可以做到這一點嗎?
網站主持人對此評論進行了最小化
有沒有辦法讓這個公式起作用,它產生的各種組合仍然分開在 2 個單獨的列中,但彼此相鄰?
網站主持人對此評論進行了最小化
你有沒有運氣弄清楚如何做到這一點? 我正在嘗試做完全相同的事情,並將我的所有概率都放在 excel 的 2 個單獨的列中。
網站主持人對此評論進行了最小化
你有這方面的運氣嗎? 我正在嘗試做同樣的事情,並將所有可能的組合放在一個 2 個單獨的列中。
網站主持人對此評論進行了最小化
我可以得到我嘗試但無法得到的 5*5 矩陣(5 行和 5 列)的所有組合的公式,請幫助我......
網站主持人對此評論進行了最小化
非常有幫助。我現在能夠非常輕鬆地生成組合。
網站主持人對此評論進行了最小化
如果你想要 Month 標題的所有排列,你怎麼能使用 KuTools,甚至是一個公式。 一月,一月和二月,一月和三月,一月和三月,一月和二月和三月等
網站主持人對此評論進行了最小化
任何人都知道如何修改它以反映 6 列數據,而不僅僅是兩列?
網站主持人對此評論進行了最小化
尋找同樣的東西。 有人知道嗎?
網站主持人對此評論進行了最小化
有人知道如何修改它以反映 6 列數據嗎?
網站主持人對此評論進行了最小化
更多欄目:
公式的第一部分需要修改以增加所有可能性,例如這將是 6 列
COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9)
所以你要添加
*COUNTA(你的範圍在這裡)
對於每一列
公式的第二部分需要針對每一列進行修改,如下所示:
INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1)
所以你要添加
&INDEX(YourRangeHere,MOD(ROW()-ROW($I$2),COUNTA(YourRangeHere))+1)

所以把它們放在一起,你會得到這個包含 6 列的示例:

=IF(ROW()-ROW($I$2)+1>COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9),"",INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1))

如果您將此公式粘貼到 $I$2 中,它將查看 $A$2:$A$9 $B$2:$B$9 $C$2:$C$9 ... 最高 $F$2:$F$9
網站主持人對此評論進行了最小化
這不起作用:(比這更複雜
網站主持人對此評論進行了最小化
更多欄目:
公式的第一部分需要修改以增加所有可能性,例如這將是 6 列:

COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9)
所以你要添加
*COUNTA(你的範圍在這裡)
對於每一列

公式的第二部分需要針對每一列進行修改,如下所示:

INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1)

所以你要添加

&INDEX(YourRangeHere,MOD(ROW()-ROW($I$2),COUNTA(YourRangeHere))+1)

所以把它們放在一起,你會得到這個包含 6 列的示例:

=IF(ROW()-ROW($I$2)+1>COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9),"",INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1))

如果您將此公式粘貼到 $I$2 中,它將查看 $A$2:$A$9 $B$2:$B$9 $C$2:$C$9 ... 最高 $F$2:$F$9
網站主持人對此評論進行了最小化
這不起作用:(比這更複雜
網站主持人對此評論進行了最小化
知道如何在兩者之間添加空格嗎?
網站主持人對此評論進行了最小化
你好,伊恩,
本文中的公式無法幫助您在單元格值之間添加空格,但是,您可以應用 Kutools for Excel,使用它,您可以鍵入任何想要分隔組合結果的分隔符,見截圖:
查看附件 (1 / 5)
網站主持人對此評論進行了最小化
您可以通過在索引列 B 之前添加一個空格來添加一個空格,就在現有的 & 之後,就像這樣.....

=IF(ROW()-ROW($D$1)+1>COUNTA($A$1:$A$4)*COUNTA($B$1:$B$3),"",INDEX($A$1:$A$4,INT((ROW()-ROW($D$1))/COUNTA($B$1:$B$3)+1))&" "&INDEX($B$1:$B$3,MOD(ROW()-ROW($D$1),COUNTA($B$1:$B$3))+1))


....或您喜歡的任何其他分隔符!
網站主持人對此評論進行了最小化
KuTools 是否能夠以“簡單組合”的方式連續對單元格進行排序?
我的意思是,如果我有這些數據:
_________________________
約翰·傑克·保羅·梅西

馬克·拉里

傑里·保羅·瑪麗

山姆·傑夫·彼得·盧卡斯
_________________________



我想像這樣輸出它們:
___________
約翰傑克

約翰·保羅

約翰·梅西

傑克保羅

傑克·梅西

保羅·梅西

馬克·拉里

傑里·保羅

傑瑞結婚

保羅瑪麗

山姆·傑夫

山姆·彼得

山姆·盧卡斯

傑夫·彼得

傑夫·盧卡斯

彼得·盧卡斯
____________


我怎樣才能做到這一點? KuTools可以這樣做嗎?

謝謝!
網站主持人對此評論進行了最小化
嗨,亞瑟,
抱歉,Kutools 無法幫助您解決您所說的這項工作。
感謝您的評論。
網站主持人對此評論進行了最小化
有沒有人想出一個公式來處理 5 組數據? 到目前為止,我一直對此感到困惑。
網站主持人對此評論進行了最小化
你好,馬可,
Kutools 可以幫助您快速解決問題,請查看以下屏幕截圖:
試試看,希望對你有幫助,謝謝!
查看附件 (1 / 5)
網站主持人對此評論進行了最小化
嗨,
我想知道如果我想生成超過 1,4 萬個組合併且超出 Excel 行數限制該怎麼辦?
有沒有辦法讓 Kudos 在下一欄繼續迭代?
網站主持人對此評論進行了最小化
為什麼我的公式無法在 E 列中創建 25 個組合(來自 Col A 的 5 個元素 * 來自 Col B 的 5 個元素)? 而當我將本文中的公式拖動到 25 個單元格時,它在 D 列中有效? 截屏 - https://prnt.sc/ihwr18
網站主持人對此評論進行了最小化
Hola me arroja una referencia circula la 公式
=SI(FILA()-FILA($D$1)+1>(CONTARA($A$1:$A$4)*CONTARA($B$1:$B$3));"";INDICE($A$1:$A$4;RESIDUO((FILA()-FILA($D$1));COUNTA($B$1:$B$3)+1))
&INDICE($B$1:$B$3;RESIDUO(FILA()-FILA($D$1);CONTARA($B$1:$B$3))+1))
網站主持人對此評論進行了最小化
您好,如果這些組合文本是數字,我該如何計算結果???? 我嘗試添加“+”,但 excel 不知道那是 simbol .. 你如何解決這個問題?
網站主持人對此評論進行了最小化
我使用了extions並且效果很好,但是現在我遇到了一個問題,我需要計算總和,但是結果是一個文本,我輸入了一個分隔符,即simbol +,但最後我有一個文本並且是無法在操作中轉換。
網站主持人對此評論進行了最小化
幾乎非常方便。 如果它實際使用單元格中的任何內容($A$1)並且不將單元格轉換為文本,那就太好了。 然後我可以在 A1 中粘貼我需要的內容並再次運行它而無需更改任何內容。
網站主持人對此評論進行了最小化
有沒有辦法將其輸出為 txt 文件? 當 D 列中有數百萬個結果時,拖動填充手柄並不完全可行。
這裡還沒有評論
載入更多
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點

關注我們

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