跳到主要内容

快速生成/列出Excel中某些列表的所有可能组合

Kutools for Excel

提升 Excel 300+
强大的功能

例如,我需要列出A2:A6,B2:B8和C2:C6范围内三个列表的所有可能组合,如下图所示。 这三个列表共有175种组合,而我自己列出所有这些组合将非常耗时且容易出错。 但 Kutools 为 Exce我的 列出所有组合 实用程序可以帮助我们在短时间内在Excel中轻松生成并列出给定列表的所有可能组合。  

快速生成/列出Excel中某些列表的所有可能组合


点击 库工具 > 插页 > 列出所有组合,请参见屏幕截图:

 kutools-右箭头

快速生成/列出Excel中某些列表的所有可能组合

该实用程序将轻松在列中列出给定数据列表的所有可能组合。 请执行以下操作:

1。 点击 库工具 > 插页 > 列出所有组合.

2.在打开的列出所有组合对话框中,您需要:

(1)点击 Type 框并选择 从下拉列表中;

提示: 请选择 分隔器 如果需要组合添加分隔符,请从下拉列表中选择。

(2)点击 浏览 按键  选择要从中生成组合的数据列表,然后单击 OK 按钮。

Tips::您也可以在 文本 框直接输入,请用逗号“,”分隔每个条目。  

(3)点击 地址 按钮。

(4)重复上述步骤(1)–(3),根据您的需要添加更多数据列表和分隔符。

3.完成设置后,请单击 Ok 按钮,在弹出的对话框中,指定将输出所有组合的目标范围的第一个单元格地址,然后单击 OK 按钮。

现在,将生成给定数据列表的所有可能组合,并立即将其列出到指定范围内。

备注:将值或分隔符插入对话框后,您可以检查要列出其组合的项目,并取消选中不需要的其他项目。


演示:快速生成/列出某些列表的所有可能组合

Kutools for Excel:具有300多个方便的Excel加载项,可以在30天内免费试用,没有任何限制。 立即下载并免费试用!

生产力工具推荐
以下工具可以极大地节省您的时间和金钱,哪一种适合您?
Office Tab: 在办公室中使用方便的选项卡,作为Chrome,Firefox和New Internet Explorer的方式。
Kutools for Excel: 超过300种Excel高级功能 2021、2019、2016、2013、2010、2007 和 Office 365。

Kutools for Excel

上述功能只是 Kutools for Excel 300 个强大功能之一。

专为 Excel(Office) 2021、2019、2016、2013、2010、2007 和 Office 365 设计。免费下载和使用 30 天。

Kutools for Excel 的屏幕截图

btn了解更多      btn下载     btn购买

Comments (5)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi I'm looking to create all combinations of a given data set, without a value being used twice in one sequence.





Eg below example, think like its the lottery and you don't want numbers doubling in a combination?



When imputing multiple rows of the same 9 numbers 2, 7, 9, 11, 17, 20, 22, 24, 27, in 6 rows, for a sequence of six numbers, eg 2 -9-11-22-24-27



However as the numbers are in all columns and there is no rule in the process to stop it from using a value twice in a sequence. Eg. list after using the above method

17 17 17 17 17 17

17 17 17 17 17 17

17 17 17 17 17 17

17 17 17 17 17 9

17 17 17 17 17 9

17 17 17 17 17 9

17 17 17 17 17 27

17 17 17 17 17 27

17 17 17 17 17 27





As you can see I want to stop numbers from appearing more then once?
This comment was minimized by the moderator on the site
Hi I'm looking to create all combinations of a given data set, without a value being used twice in one sequence.


Eg below example, think like its the lottery and you don't want numbers doubling in a combination?

When imputing multiple rows of the same 9 numbers 2, 7, 9, 11, 17, 20, 22, 24, 27, in 6 rows, for a sequence of six numbers, eg 2 -9-11-22-24-27

However as the numbers are in all columns and there is no rule in the process to stop it from using a value twice in a sequence. Eg. list after using the above method
17 17 17 17 17 17

17 17 17 17 17 17

17 17 17 17 17 17

17 17 17 17 17 9

17 17 17 17 17 9

17 17 17 17 17 9

17 17 17 17 17 27

17 17 17 17 17 27

17 17 17 17 17 27


As you can see I want to stop numbers from appearing more then once?
This comment was minimized by the moderator on the site
Hi i am looking out to build truck optimization tool
If you can help with this
This comment was minimized by the moderator on the site
Hello. Thanks for the List All combinations tool. However, some combinations exceed the total number of rows excel can offer in a given column (1,048,576). Is it possible to let the list continue in another column within the same sheet? Or even have the tool in word or text which doesn't limit the rows? I appreciate your feedback!
This comment was minimized by the moderator on the site
have the same problem?!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations