☰

一行中每个值的查找总和

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet google-spreadsheets
一行中每个值的查找总和

应该有办法做到这一点，但我很挣扎......

==数据==

工作表1

        A           B
+---------------+-------+
| Fishy_Product | Price |
+---------------+-------+
| Cod           | $9.99 |
| Trout         | $8.99 |
| Haddock       | $6.99 |
| Kippers       | $3.99 |
+---------------+-------+

工作表2

       A            B         C       D        E
+------------+-------------+-----+--------+---------+
|            | Total Spend | Cod | Trout  | Haddock |
+------------+-------------+-----+--------+---------+
| Customer 1 | ?           |   1 |        |       3 |
| Customer 2 | ?           |     |      2 |         |
| Customer 3 | ?           |   2 |      2 |       2 |
+------------+-------------+-----+--------+---------+

==挑战==

对于每个客户，我需要计算总支出通过将 Sheet2 中的“购买量”乘以 Sheet1 中的“产品/价格”。例如，我可以计算总支出对于客户 1，如下所示： =(C2*vlookup(c1,'Sheet1!A:B,2,0))+(D2*vlookup(D1,'Sheet1!A:B,2,0))+(E2*vlookup(E1,'Sheet1!A:B,2,0))等等。

但新产品不断添加到 Sheet1 中。

一旦“kippers”被添加为 F 列，我就必须手动循环并更新所有公式。（请注意，每当将新产品添加到 Sheet1 时，由于使用了公式，它都会自动显示为 Sheet2 中的新列）。除了个人懒惰之外，我担心的是，我可能无法发现何时需要更新，并且根据错误的总数进行工作。

有什么方法可以计算总支出而无需在每次添加新项目/列时手动更新公式？

在此先向所有能帮助我的人表示衷心的感谢。

PS 我应该补充一下，我正在使用 Google 表格

答案1

只需用来SUMPRODUCT()进行计算：

在此处输入图片描述

公式B8：

=SUMPRODUCT((A$2:A$5=C$7:E$7)*(B$2:B$5*C8:E8))

向下拖动，但请注意，添加产品时它不会自动更新。

需要澄清的是，产品顺序无关紧要：

在此处输入图片描述

结果相同。

答案2

只需对数据方向进行少许修改即可解决这个问题：

：警告：

所示方法适用于 Google Sheet 和 Excel。

在此处输入图片描述

  • 单元格 Sheet2 H19 中的公式并向下填充：

     =IFERROR(SUMPRODUCT(VLOOKUP(Sheet1!G13,$G$19:$K$22,{3,4,5},FALSE))*Sheet1!H13,"")

注意：

如果 SUMPRODUCT 只有一个参数，它的行为与 SUM 非常相似。但是当 SUMPRODUCT 有多个参数时，它会返回其参数乘积的总和。以下是使用单个参数的情况。

{3,4,5} 是数据范围 $G$19:$K$22 中的 I、J 和 K 列，并且可以调整。

根据需要调整公式中的单元格引用。

答案3

您为 VLookup 表选择一个大区域，以便容纳所有项目。假设取 5000 行。现在，每次添加新项目时，请再次对包含项目的 A 上的 A 和 B 两列进行排序。排序后，它会正常运行，所以没有问题。

谢谢。

相关内容