多组项目的Excel组合 [英] Excel combinations of multiple sets of items

问题描述

我正在寻找一种简单的方法来列出excel中三组项目的所有可能组合.

I'm searching for an easy way to list all possible combinations for three sets of items in excel.

例如，采取以下三组项目，每组项目都在其自己的单元格中:

For example, taken the following three sets of items, each item in its own cell:

(meals) pizza, pasta, lasagne    
(drinks) wine, beer, water    
(dessert) ice, fruits

现在我想列出所有可能的组合，每个组合都在其自己的行中，例如

now I want to list all possible combinations, each in its own row like

pizza, wine, ice
pasta, wine, ice
lasagne, wine, ice
pizza, wine, fruits
...
lasagne, water, fruits

排序无关紧要.

为此有任何预定义功能吗?

Is there any predefined function for that?

推荐答案

将项目放在工作表的前三列中，就像这样
A B C pizza wine ice pasta beer fruits lasagne water

Place the items to your worksheet in the first three columns like this
A B C pizza wine ice pasta beer fruits lasagne water

然后，您可以使用以下公式获得F，G，H的组合.

Then, you can get the combinations in F, G, H using the following formulae.

F1:
=IF(ROW()-ROW($F$1)+1<=COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C), INDEX(A:A,INT((ROW()-ROW($F$1))/(COUNTA(B:B)*COUNTA(C:C))+1)), "")

F1:
=IF(ROW()-ROW($F$1)+1<=COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C), INDEX(A:A,INT((ROW()-ROW($F$1))/(COUNTA(B:B)*COUNTA(C:C))+1)), "")

G1:
=IF(ROW()-ROW($F$1)+1<=COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C), INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1), "")

G1:
=IF(ROW()-ROW($F$1)+1<=COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C), INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1), "")

H1:
=IF(ROW()-ROW($F$1)+1<=COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C), INDEX(C:C,MOD(ROW()-ROW($F$1),COUNTA(C:C))+1), "")

H1:
=IF(ROW()-ROW($F$1)+1<=COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C), INDEX(C:C,MOD(ROW()-ROW($F$1),COUNTA(C:C))+1), "")

下拉单元格，直到这些值为空.然后，您可能需要将结果与=IF(F1<>"", F1 & ", " & G1& ", " & H1, "")

Pull down the cells until the values are empty. Then you may want to combine the results to column I with =IF(F1<>"", F1 & ", " & G1& ", " & H1, "")

这篇关于多组项目的Excel组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！