如何使用 Excel 中的公式使用 5 列生成所有组合? [英] How to generate all combinations using 5 columns using a formula in Excel?

问题描述

我看到并使用了 3 列组合的公式.我一直在尝试将其扩展到 4 列，然后扩展到 5 列.我无法让它正常工作.我不想用桌子.这是我在 Scott Craner 发布的这个网站上找到的原始公式.我将其扩展为 5 列，但无法正常工作.

I saw and used the formula for combinations for 3 columns. I have been trying to extend it to 4 columns and then to 5 columns. I can't get it to work properly. I don't want to use a table. This was the original formula I found on this site posted by Scott Craner. I expanded on it for 5 columns but can't get it to work properly.

=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),"")

=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),"")

推荐答案

这是一个可扩展的公式，可以从最多五列的数组输入生成所有组合.

Here is an extensible formula that can generate all combinations from an array input of up to five columns.

注意事项:

• 它需要 Excel 2019 或 Excel 365
• 它不接受列作为输入 - 它接受一个数组，因此您必须调整输入数组的大小以适合您想要的列.(请参阅下面的注释)
• 虽然它不依赖于 volatile 函数，但它仍然是计算密集型的，并且运行速度可能很慢.
• 您的范围内必须至少有 2 列，最多 5 列.
• 数组中的任何列都不能完全为空.

在此示例中，我将采用 A1:E5 范围内的一组文本单元格，并将它们与分隔符;"组合在一起.".该公式使用 LET 使其更高效和可读.您可以将其复制粘贴到目标单元格中​​ - 格式设置只是为了使其可读:

In this example, I am taking an array of text cells in the range A1:E5 and I will combine them with a delimiter of " ". The formula uses LET to make it more efficient and readable. You can copy paste it into your destination cell - the formatting is only there to make it readable:

=LET( matrix, A1:E5,
      d, " ",
       cC, COLUMNS( matrix ), cSeq, SEQUENCE( 1, cC ),
       rC, ROWS( matrix ), rSeq, SEQUENCE( rC ),
       eC, rC ^ cC, eSeq, SEQUENCE( eC,,0 ),
       m, INDEX( matrix, MOD( INT( INT( SEQUENCE( eC, cC, 0 )/cC )/rC^SEQUENCE( 1, cC, cC-1, -1 ) ), rC ) + 1, cSeq ),
       n, INDEX( m, eSeq+1, cSeq ) <> 0,
       r, IFERROR(INDEX( m, eSeq  + 1, 1 ), "") &d& INDEX( m, eSeq  + 1, 2 ) &d& IFERROR(INDEX( m, eSeq  + 1, 3 ), "") &d&
           IFERROR(INDEX( m, eSeq  + 1, 4 ), "") &d& IFERROR(INDEX( m, eSeq  + 1, 5 ), ""),
       o, IFERROR( INDEX( r, eSeq+1, ), n ),
       FILTER( INDEX( o, , 1), INDEX( n, , 1 ) * INDEX( n, , 2 ) * IFERROR(INDEX( n, , 3 ), TRUE) *
               IFERROR(INDEX( n, , 4 ), TRUE) * IFERROR(INDEX( n, , 5 ), TRUE) ) )

将变量 ma​​trix 设置为您想要的范围.当前设置为 A1:E5.将 d 变量设置为您要使用的分隔符.它被设置为""或空间现在.以下是输出示例:

Set the variable matrix to the range that you want. It is currently set to A1:E5. Set the d variable to the delimiter that you want to use. It is set to " " or space right now. Here is an example of the output:

注意事项

我有另一个版本可以使用列，但它失败了，我没有时间调试它，所以我认为现在最好给你一些你可能能够适应你的需求的东西.另一个版本的计算速度也更快.等我调试好了，会贴在这里.

I have another version that will take columns, but it is failing and I won't have time to debug it, so I thought it would be better to give you something for now that you might be able to adapt to your needs. The other version is also computationally faster. When I have debugged it, I will post it here.

这是可扩展的.如果您希望它进入 6、7 或更多列，则可以很容易地对其进行修改.我选择不这样做是因为它处理的每一列(即使列中没有数据)都会因为我使用的设计而减慢计算速度.要扩展它，您需要在末尾附近编辑两行:

This is extensible. If you want it to go to 6, 7, or more columns, it can be pretty easily modified. I chose not to do that because each column that it processes (even if there are no data in the column) slows down the calculation because of the design that I used. To extend it, you need to edit two lines near the end:

1. 您必须编辑变量 r.您需要附加另一个 &d&IFERROR(INDEX(m,eSeq+1, XXX ),"") 在逗号前面，其中 XXX 是下一栏.例如 6 然后是 7 等
2. 您必须编辑以 FILTER 开头的最终结果.您需要附加另一个 *IFERROR(INDEX(n,, XXX ),TRUE) 在最后两个右括号前，其中 XXX 是下一列.例如 6 然后是 7 等

1. You have to edit the variable r. You need to append another &d&IFERROR(INDEX(m,eSeq+1, XXX ),"") in front of the comma where XXX is the next column. For example 6 and then 7, etc.
2. You have to edit the final result that starts with FILTER. You need to append another *IFERROR(INDEX(n,, XXX ),TRUE) in front of the last two close parens where XXX is the next column. For example 6 and then 7, etc.

以下是 7 列版本的示例:

Here is an example of a 7 column version:

=LET( matrix, A1:G5,
       d, " ",
       cC, COLUMNS( matrix ), cSeq, SEQUENCE( 1, cC ),
       rC, ROWS( matrix ), rSeq, SEQUENCE( rC ),
       eC, rC ^ cC, eSeq, SEQUENCE( eC,,0 ),
       m, INDEX( matrix, MOD( INT( INT( SEQUENCE( eC, cC, 0 )/cC )/rC^SEQUENCE( 1, cC, cC-1, -1 ) ), rC ) + 1, cSeq ),
       n, INDEX( m, eSeq+1, cSeq ) <> 0,
       r, IFERROR(INDEX( m, eSeq  + 1, 1 ), "") &d& INDEX( m, eSeq  + 1, 2 ) &d& IFERROR(INDEX( m, eSeq  + 1, 3 ), "") &d&
           IFERROR(INDEX( m, eSeq  + 1, 4 ), "") &d& IFERROR(INDEX( m, eSeq  + 1, 5 ), "") &d& IFERROR(INDEX( m, eSeq  + 1, 6 ), "") &d& IFERROR(INDEX( m, eSeq  + 1, 7 ), ""),
       o, IFERROR( INDEX( r, eSeq+1, ), n ),
       FILTER( INDEX( o, , 1), INDEX( n, , 1 ) * INDEX( n, , 2 ) * IFERROR(INDEX( n, , 3 ), TRUE) *
               IFERROR(INDEX( n, , 4 ), TRUE) * IFERROR(INDEX( n, , 5 ), TRUE) * IFERROR(INDEX( n, , 6 ), TRUE) * IFERROR(INDEX( n, , 7 ), TRUE) ) )

这篇关于如何使用 Excel 中的公式使用 5 列生成所有组合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！