☰

行中每個值的 vlookups 總和

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet google-spreadsheets
行中每個值的 vlookups 總和

應該有辦法做到這一點，但我正在掙扎......

==數據==

表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 Table選擇一個大的區域以容納所有項目。假設取 5000 行。現在每次新增項目時，請再次對 A 上包含項目的 A 和 B 兩列進行排序。一旦你排序它就會表現正確，所以沒問題。

謝謝。

相關內容