☰

Excel 计算总和范围以及其他计算

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 计算总和范围以及其他计算

好吧，我有一个 Excel 表，用于存储某些数字数据。基本上，我有一个列标题和日期作为行标题。数据是介于两者之间的数字：

         column 1 | column 2 | column 3 | SUM

date1    | 1      | 5        | 3        | column1_reference * multiplier + column2_reference * multiplier...
date2    | 2      | 3        | 6        | 
date3    | 1      | 1        | 5        |

现在我有一个参考列表，其中的列名引用另一组数字，如下所示：

第 1 列：40

第 2 列：243

第 3 列：321

所以基本上我需要做的是计算每天的总数。从参考列表中获取数字并将其与数据上的数字相乘很容易（使用命名范围 * 乘数进行 OFFSET），但这仅适用于一个单元格。我不知道如何将其应用于范围。

因此，如果使用之前设置的示例，SUM 列将如下所示：

日期 1 总计 = (40 * 1) + (243 * 5) + (321 * 3)

date2 总计 = (40 * 2) + (243 * 3) + (321 * 6)

日期 3 总计 = (40 * 1) + (243 * 1) + (321 * 5)

我当然可以手动完成此操作，但是我的列表相当大，而且它不会非常动态。

答案1

VLOOKUPs如果我正确理解了你的问题，可以使用由与每个列名相关联的参考列表中的值组成的辅助行来解决这个问题。

SUMPRODUCTs然后，使用辅助行中的值和每个日期输入的金额来获取每个日期的总和。

当列的名称或其顺序发生变化时，这种方法会自动调整。

在下面显示的示例表中，B2是辅助行的第一个单元格；它包含公式

=VLOOKUP(B1,$A$7:$B$10,2,0)

它被复制到数据列中。

第一个单元格和列是E3；其公式是

=SUMPRODUCT($B$2:$D$2,B3:D3)

将其复制到数据的底部。

虽然我将辅助行显示为表格的第二行，但实际上它可以位于工作表上的任何位置。

在此处输入图片描述

答案2

如果第一行有数字B2:D2，而你想要计算总和，E2请尝试复制此公式

=SUM(MMULT(B2:D2,B$9:B$11))

假设 40, 243, 321 出现在B9:B11

参见下面的截图：

在此处输入图片描述

编辑：

如果 B1:D1 和 A9:A11 的顺序可能不同，那么 chuff 的解决方案就很不错。如果想避免多出一行，则需要将 VLOOKUP 部分合并到 SUMPRODUCT 中 - 您不能使用 VLOOKUP 本身，因为它不会返回数组，而 LOOKUP 是可行的，因为它做返回一个数组但有问题，因为 A9:A11 需要按升序排序（即使这样，如果某些列标题不匹配，您也可能会遇到问题）。

解决这个问题的方法是使用 SUMIF 作为“准查找”，即使用以下公式

=SUMPRODUCT(B2:D2,SUMIF(A$9:A$11,B$1:D$1,B$9:B$11))

如果第二个表位于另一张表上或由 OFFSET 定义，则该方法仍然有效。

[注：目前没有实际意义，但在我的原始公式中，SUM 函数不是必需的 - MMULT 可以自行工作]

相关内容