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

问题描述

已经有一个问题和答案，如何在excel的三列中列出值的所有可能组合?"该公式完全可以实现我想要的效果，但是我需要添加另外两列，但是我无法完全理解当前公式以向列表中添加另外两列.

There is a question and answer already out there, "How to list all possible combinations of the values in three columns in excel?" This formula works exactly how I want it to, but I need added two additional columns, but I am not able to fully understand the current formula to add an additional two new columns to the list.

当前公式适用于3列.它需要更新为包括5.=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),"")

Current Formula works for 3 columns. It needs to be updated to include 5. =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),"")

此外，如果有一种方法可以解释如何增加或减少一列也会成倍增加收益的列.

Also if there is a way to explain how to add for an additional or subtract a column that would be exponentially beneficial as well.

Site    Product Type    Labor Hours Machine Hours   Batch Size      
MAR UV  2   2   100     MAR UV 2
BEL SOLVENT 5   5   300     MAR UV 5
    WATER   8   8   750     MAR UV 8
        13  13  1750        MAR UV 13
        18  18  3750        MAR UV 18
                5000        MAR SOLVENT 2
                        MAR SOLVENT 5
                        MAR SOLVENT 8
                        MAR SOLVENT 13
                        MAR SOLVENT 18
                        MAR WATER 2
                        MAR WATER 5
                        MAR WATER 8
                        MAR WATER 13
                        MAR WATER 18
                        BEL UV 2
                        BEL UV 5
                        BEL UV 8
                        BEL UV 13
                        BEL UV 18
                        BEL SOLVENT 2
                        BEL SOLVENT 5
                        BEL SOLVENT 8
                        BEL SOLVENT 13
                        BEL SOLVENT 18
                        BEL WATER 2
                        BEL WATER 5
                        BEL WATER 8
                        BEL WATER 13
                        BEL WATER 18

根据当前公式，这就是我现在看到的内容.它仅包括前3列.我需要它也包括下2个.我也喜欢这个公式，因为它不在乎每列中会增加多少行，将来可能会发生巨大变化.

This is what I am seeing right now based on the current formula. It is only including the first 3 columns. I need it to include the next 2 as well. I also like this formula because it doesn't care how many additional rows will be in each column which may change dramatically in the future.

下面是原始问题，公式中只有3列 如何列出全部excel中三列的值的可能组合?

Below is the original question that has only 3 columns in the formulas How to list all possible combinations of the values in three columns in excel?

推荐答案

我只回答这部分:

一种解释如何增加或减少一列的方法

a way to explain how to add for an additional or subtract a column

根据您的前3列网站产品类型"，每个列中有"2-3-5"个项目.将原始公式分为3行:

According to your first 3 columns "Site-Product-Type" there is "2-3-5" items in each columm. separating the original formula into 3 lines :

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

因此，每个网站"项目都需要重复3 * 5次(15次-例如MAR& BEL). [第1行]

So each "Site" item need to be repeated 3 * 5 times (15 times - Eg. MAR & BEL). [line 1]

对于每个网站"项目，每个产品"项目都需要重复5次(例如UV，溶剂，水)[第2行]

And for each "Site" item, each "Product" item need to be repeated 5 times ( Eg. UV, SOLVENT, WATER ) [line 2]

对于每个产品"项目，每个类型"项目都需要重复1次(例如2,5,8,13,18)[第3行]

And for each "Product" item, each "Type" item need to be repeated 1 time ( Eg. 2,5,8,13,18 ) [line 3]

因此，输出总数= 2 * 3 * 5 = 30. [此部分由公式的iferror(... , "")执行(30行(或3 * 2 * 5)行后无输出)]

So the total number of output = 2*3*5 = 30 . [ this part was executed by iferror(... , "") of the formulae (no output after 30 ( or 3*2*5) lines) ]

在引用的公式中..通过将行号(作为计数器，使用row())，counta()(计算每列中元素的数量，mod())(以获得重复)进行关联)和index()(根据处理的行号调用每个列项目-更多信息:链接).

In the cited formula.. it was done by relating the row number (as a counter, using row() ), counta() (to count the number of elements in each column, mod() (to get the repetition), and index() (to call each column item, depending on the row number processed - more info : last formulae in this link ).

将其放在5列站点产品类型劳动时间"中:

Taking it to 5 columns, "Site-Product-Type-Labor-Hours" :

获取每列的元素/项目数. (您应该获得2-3-5-5-6)

Get the number of elements/items for each column. (You should get 2-3-5-5-6 )

因此，输出总数= 2 * 3 * 5 * 5 * 6 = 900.

So the total number of output = 2*3*5*5*6 = 900 .

每个网站"项目都需要重复3 * 5 * 5 * 6次

each "Site" item need to be repeated 3*5*5*6 times

对于每个网站"项目，每个产品"项目都需要重复5 * 5 * 6次

for each "Site" item, each "Product" item need to be repeated 5*5*6 times

对于每个产品"项目，每个类型"项目都需要重复5 * 6次

for each "Product" item, each "Type" item need to be repeated 5*6 times

对于每个类型"项目，每个劳动"项目都需要重复6次

for each "Type" item, each "Labor" item need to be repeated 6 times

对于每个劳动"项目，每个小时"项目都需要重复1次

for each "Labor" item, each "Hours" item need to be repeated 1 time

如果您删除一列..只需使用相同的模式.

If you remove a column.. just use the same pattern.

希望您能理解. (:

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