如何在Excel中的三列中列出值的所有可能组合? [英] How to list all possible combinations of the values in three columns in excel?

问题描述

我有三列，每列都有不同种类的主数据，如下所示:

I have three columns, each of which has different kinds of master data as shown below:

现在，我想拥有这三个单元格的所有可能组合-如

Now, I want to have all possible combinations of these three cells - like

aa kk jj
aa kk ff
aa ll jj
aa ll ff
aa mm jj
...

可以使用公式来完成此操作.我发现一个包含2列的公式，但是我无法正确地将其扩展到3列

Can this be done with a formula. I found one formula with 2 columns, but i'm not able to extend it to 3 columns correctly

具有2列的公式:

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

其中G1是放置结果值的单元格

where G1 is the cell to place the resulting value

推荐答案

您可以使用以下公式进行操作:

You can do this with a formula:

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

这篇关于如何在Excel中的三列中列出值的所有可能组合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！