arrays - 在 EXCEL 中使用多个表的条件求和

Question

我有一个表，我试图根据两个引用表的值来填充该表。

我有各种不同的项目“类型 1”、“类型 2”等，每个项目运行 4 个月，并且根据其处于生命周期的时间而花费不同的金额。这些成本计算如引用表 1 所示。

引用表1

Month    |  a  |  b  |  c  |  d
---------------------------------
Type 1   |  1  |  2  |  3  |  4
Type 2   |  10 | 20  |  30 |  40
Type 3   | 100 | 200 | 300 | 400

引用表 2 显示了我 future 3 个月的项目时间表。从 1 月开始，将推出 2 个新产品，一个是 Type 1，另一个是 Type 2。 2 月份，我将有 4 个项目，前两个项目进入第二个月，两个新项目开始，但这次是类型 1 和类型 3。

引用表2

Date    | Jan | Feb | Mar
--------------------------
Type 1  |  a  |  b  |  c
Type 1  |     |  a  |  b
Type 2  |  a  |  b  |  c
Type 2  |     |     |  a
Type 3  |     |  a  |  b

我想创建一个表来计算每个项目类型每月花费的总成本。示例结果显示在下面的结果表中。

结果

Date    | Jan | Feb | Mar
-------------------------------
Type 1  |  1  |  3  |  5
Type 2  |  10 |  20 |  40
Type 3  |  0  | 100 | 200

我尝试使用数组公式来实现:

Res!b2 = {sum(if((Res!A2 = Ref2!A2:A6) * (Res!A2 = Ref1!A2:A4) * (Ref2!B2:D6 = Ref1!B1:D1), Ref!B2:E4))}

但是它不起作用，我相信这是因为第三个条件试图将向量与另一个向量而不是单个值进行比较。

有人知道我该怎么做吗？乐于使用数组、索引、匹配、向量、查找，但不喜欢使用 VBA。

谢谢

Answer 1

假设结果表标题中的月份与引用表 2 的顺序相同(根据您的示例)，然后在 Res!B2 中尝试此公式

=SUM(SUMIF(Ref1!$B$1:$E$1,IF(Ref2!$A$2:$A$6=Res!$A2,Ref2!B$2:B$6),INDEX(Ref1 !$B$2:$E$4,MATCH(Res!$A2,Ref1!$A$2:$A$4,0),0)))

使用 CTRL+SHIFT+ENTER 确认并向下和横向复制

这给我的结果与你在结果表中得到的结果相同

如果月份的顺序可能不同，那么您也可以添加一些内容来检查 - 我假设结果表行标签中的类型可能与引用表 1 的顺序不同，但如果它们始终相同也排序(根据您的示例)，那么最后的 INDEX/MATCH 部分可以简化为单个范围