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应用程序 窗口。

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列或多列数据生成所有组合

如果你有 Kutools for Excel,其功能强大 列出所有组合 功能,您可以快速轻松地列出多列的所有组合。

小贴士:申请这个 列出所有组合 功能,首先,您应该下载 Kutools for Excel,然后快速轻松地应用该功能。

安装后 Kutools for Excel,请这样做:

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?
这里还没有评论

关注我们

版权所有 © 2009 - extendoffice.com。 | 版权所有。 供电 ExtendOffice。 | 网站地图
Microsoft和Office徽标是Microsoft Corporation在美国和/或其他国家的商标或注册商标。
受Sectigo SSL保护