excel-formula - 使用 excel 总计 7 列中的 4 列的所有组合

Question

我有 7 列可供选择，我需要从中选择 4 列并为每一行生成一个总数。我还需要 4 的每个组合，这意味着我将有 35 个新列，其中每个组合的总数显示在每一行中。我需要这个代码，如果它只能使用 Excel 来完成。这是列的图像，灰色的是我正在谈论的 7 列。我对Excel的了解非常有限。如果重要的话，有超过 1,500 行。

标签： excel-formula

Answer 1

将使用一些辅助行的多步骤方法。可能有一个更优雅的公式可以做到这一点，并且在 VBA 中有很多更漂亮的选项，但这是一种仅限公式的方法。

第 1 步 - 生成列组合列表

要生成列表，需要在数据顶部插入 4 个辅助行。在标题行上方或下方。这 4 行将代表您要选择的列号。为了让我的数学更简单，我假设第一列是 1，最后一列是 7。这些数字将在以后转换为占电子表格之间的列。为了这个例子，第一个组合总和将出现在 AO 列中，第一个辅助行将是第 1 行。第一个组合将被硬编码，它将为其余列组合的模式播种。在相应的单元格中输入以下值：

AO1 = 1
AO2 = 2
AO3 = 3
AO4 = 4

在相邻列中，将放置一个公式并将其复制到右侧。它将自动将底部值增加 1，直到达到最大值，此时上面行中的值将增加 1，并且当前值将比上面的单元格多 1。这将在达到 BW 列时生成一个涵盖所有 35 种组合的模式。将下面的公式放在适当的单元格中并复制到右侧：

AP1
=IF(AO2=5,AO1+1,AO1)

AP2
=IF(AO2=5,AP1+1,IF(AO3=6,AO2+1,AO2))

AP3
=IF(AO3=6,AP2+1,IF(AO4=7,AO3+1,AO3))

AP4
=IF(AO4=7,AP3+1,AO4+1)

Step2 - 对适当的列求和

我希望使用某种数组类型的操作来阅读上面的列参考号，但我无法理解它。由于只需要担心 4 个条目，因此我只是在SUM函数中手动添加了每个引用。现在要注意的重要一点是，我们将在INDEX覆盖列范围的 13 列上使用该函数，以便将我们上面计算的索引号转换为可以每隔一行抓取一次的数字，即计算出来的值将乘以 2，然后减去 1。这意味着第一列组合的 1,2,3,4 变为 1,3,5,7。您可以在以下公式中看到这一点。将以下公式放在适当的单元格中，并根据需要向下和向右复制。

AO5
=INDEX($AB5:$AN5,AO$1*2-1)+INDEX($AB5:$AN5,AO$2*2-1)+INDEX($AB5:$AN5,AO$3*2-1)+INDEX($AB5:$AN5,AO$4*2-1)

仔细注意 $ 它将锁定行或列引用并防止它们在复制公式时发生更改。

现在您可能需要调整单元格引用以匹配您的工作表。