☰

Excel 中的数组求和

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
Excel 中的数组求和

我有四张表。第一张表显示每道菜所用食材的数量。

菜单成分

  menu  | ingredient | amount
--------+------------+--------
  Foo   |   A        |  0.1
  Foo   |   B        |  0.2
  Bar   |   A        |  0.3
  Baz   |   C        |  0.4

然后我有显示日期和销售菜单的交易表。

菜单已售出

  date  | menu sold
--------+------------
   x    |   Foo
   x    |   Foo
   x    |   Bar
   x    |   Baz

我将日期留空，假设它们现在都一样。我想要的最终表格是显示每个月使用了多少成分。如 A、B、C 列所示。为此，我在 F、G、H 列中创建了另一个表格。

     A         B          C           F       G      H
   month | ingredient | amount      month | menu | amount
  -------+------------+--------   --------+------+--------
3    x   |     A      |   ?           x   |  Foo |   2
4    x   |     B      |   ?           x   |  Bar |   1
5    x   |     C      |   ?           x   |  Baz |   1

这是我用来计算 H 列金额的公式。

=COUNTIF(Menu_Sold!B:B,Monthly_Ingredient_Used!G4)

这是我为找到成分 A 的含量所做的事情。

  1. 获取包含 A3 中配料的菜单数组 =IF($B$3=Menu_Ingredient!B:B,Menu_Ingredient!A:A,"")
  2. 获取当月销售量（此处假设所有条目都在同一个月，因此留空）
    =IFNA(INDEX(H:H,MATCH(IF($B$3=Menu_Ingredient!B:B,Menu_Ingredient!A:A,""),G:G,0)),0)
  3. 乘以所用成分的量
    =IFNA(INDEX(H:H,MATCH(IF($B$3=Menu_Ingredient!B:B,Menu_Ingredient!A:A,""),G:G,0)),0)*Menu_Ingredient!C:C
  4. 总和
    =SUM(IFNA(INDEX(H:H,MATCH(IF($B$3=Menu_Ingredient!B:B,Menu_Ingredient!A:A,""),G:G,0)),0)*Menu_Ingredient!C:C)

这是我得到的结果图

问题：

  1. 前三个步骤给出了正确的结果。当我将数组向下展开时，我可以看到正确的数字。然而在最后一步，它没有对整个数组求和并给出第一个元素作为结果。
  2. 我也尝试在第 2 步中使用 VLOOKUP，但是不知何故它给出了错误的查找值，我不明白为什么。

如果您想尝试一下，这是文件。 https://1drv.ms/x/s!AgK3RJYPrJR8iGsfvXV0rrhtLiZa

答案1

请检查这是否适合您。

看起来您创建的表格将为您提供 A 的结果。如果您想获得 B 的结果，则必须创建另一个表格。根据我对您的问题的最佳理解，我建议采用略有不同的方法，即要求您创建一个辅助表，其列数 = 菜单项数。

这是一个例子。为了保持简单并容纳在单个屏幕截图中，所有表格都放在一个工作表中。

表 3、4 和 5 与您的工作表中的内容相同（但表号可能有所不同）

参见辅助表。这里的每一列都是每个菜单项。因此，在本例中是 Foo、Bar 和 Baz。这与表 1（最终结果表）并列。

在这个例子中，我添加了一种其他成分，比如 D。

在此处输入图片描述

现在在 D3 中输入以下公式并将其拖动到该表的行和列上。

=SUMPRODUCT(($P$3:$P$7=$B3)*($O$3:$O$7=D$2),$Q$3:$Q$7)

这给出了第 3 行中 Foo、Bar 和 Baz 中使用的 A 的数量。同样，在下一行第 4 行中，它给出了相同成分中使用的 B 的数量。

现在要获得已用金额，您只需使用 SUMPRODUCT

C3 中的公式是

=SUMPRODUCT(D3:F3,TRANSPOSE($J$3:$J$5))

由于 J3:J6 被转置以匹配 SUMPRODUCT 中的数组，因此整个公式需要是数组公式。在公式栏中按CTRL+ SHIFT+ENTER以创建数组公式。公式现在应括在花括号中以表明它是一个数组公式。将其沿列向下拖动，对于每种成分，您将获得消耗量。

请注意，这里的月份并没有像您在示例中那样被处理。如果要添加月份，公式可能会变得更加复杂。

相关内容