时间戳

首页 > 解决方案 > 如何在excel的五列中列出所有可能的值组合？

excel - 如何在excel的五列中列出所有可能的值组合？

问题描述

已经有一个问题和答案，“如何在excel中列出三列中所有可能的值组合？” 这个公式完全按照我的意愿工作，但我需要添加两个额外的列，但我无法完全理解当前的公式以向列表中添加额外的两个新列。

当前公式适用于 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),"")

此外，如果有一种方法可以解释如何增加或减少一列，这也将是指数级有益的。

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。我也喜欢这个公式，因为它不关心每列中有多少额外的行，这在未来可能会发生巨大变化。

以下是公式中只有 3 列的原始问题 如何在 excel 中列出三列中值的所有可能组合？

标签： excelexcel-formula

解决方案

我只回答这部分：

一种解释如何添加或减去列的方法

根据您的前 3 列“站点产品类型”，每列中有“2-3-5”项。将原始公式分成 3 行：

=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 行]

对于每个“站点”项目，每个“产品”项目需要重复 5 次（例如 UV、溶剂、水）[第 2 行]

对于每个“产品”项，每个“类型”项需要重复 1 次（例如 2,5,8,13,18 ）[第 3 行]

所以输出的总数 = 2*3*5 = 30 。[这部分由iferror(... , "")公式执行（30（或3 * 2 * 5）行后没有输出）]

在引用的公式中..它是通过关联行号（作为计数器，使用row()），counta()（计算每列中的元素数，mod()（获取重复）和index()（调用每个列项，取决于处理的行号 - 更多信息：此链接中的最后一个公式）。

将其分为 5 列，“站点-产品-类型-工时”：

获取每列的元素/项目数。（你应该得到 2-3-5-5-6 ）

所以输出的总数 = 2*3*5*5*6 = 900 。

每个“站点”项需要重复 3*5*5*6 次

对于每个“站点”项，每个“产品”项需要重复 5*5*6 次

对于每个“产品”项，每个“类型”项需要重复 5*6 次

对于每个“类型”项目，每个“劳动力”项目需要重复 6 次

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

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

我希望你明白这个逻辑。( :

推荐阅读