SUMPRODUCT這個函數可以使用的範圍很廣，這篇介紹如何加總符合多條件的資料，以及加總符合多條件資料的乘積，實際操作中最多加總 3 個項目，但如果還需要判斷更多項目的話，也是可以繼續往下寫延伸下去，於Microsoft Excel 官方函數說明 中有提到，最多可以加到255個引數， Google Sheet 沒有特別說明到，但正常來講應該是不會用 Excel 計算到那樣龐大的項目😅，所以基本應用上是沒有問題的。
其他參考：
《 加總符合多個條件的資料 》的 SUMIFS 函式用法
《 帶出儲存格往右橫向的對應資料 》的 VLOOKUP 函式用法
基本說明
SUMPRODUCT函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用，且用法相同，此篇使用 Google Sheet 作為範例圖。為方便辨識，儲存格標示為 黃底 的是帶有函式資料的儲存格；儲存格標示為 綠底 的為可修改資料，且會影響公式結果的儲存格；標示為 淺灰底 的為黃底儲存格的公式文字。
函數 SUMPRODUCT 介紹
針對兩個大小相同的範圍或陣列，計算對應範圍的乘機總和。
函數公式： SUMPRODUCT( 引數1 , 引數2 , 引數3 , ... ... )
範例：
| A | B | C | |
|---|---|---|---|
| 1 | 種類 | 單價 | 數量 |
| 2 | 草莓 | 10 | 5 |
| 3 | 香蕉 | 20 | 3 |
| 4 | 櫻桃 | 30 | 1 |
| 5 | 鳳梨 | 40 | 0 |
使用方式 ①：
放入一個引數範圍，等同函數 SUM。
例如： SUMPRODUCT( B2 : B5 ) --- 加總B2 : B5的數字 = 10+20+30+40 = 100
使用方式 ②：
放入多個引數範圍，計算出對應列的乘機總和。
例如： SUMPRODUCT( B2 : B5 , C2 : C5 )
--- 列相乘再加總 = 10 × 5 + 20 × 3 + 30 × 1 + 40 × 0 = 140
使用方式 ③：
放入多個引數範圍，並判斷文字是否符合，將要判斷的引數範圍加上括號，並加上「 = 」，與要判斷的文字，文字使用「 " 」雙引號括上，函數中使用判斷會回傳 TRUE 或 FALSE，TRUE 為數字 1，FALSE 為 數字0。
例如： SUMPRODUCT( ( A2 : A5 = " 櫻桃 " ) , B2 : B5 , C2 : C5 )
--- 列相乘再加總
= (草莓) × 10 × 5 + (香蕉) × 20 × 3 + (櫻桃) × 30 × 1 + (鳳梨) × 40 × 0
= (FALSE) × 10 × 5 + (FALSE) × 20 × 3 + (TRUE) × 30 × 1 + (FALSE) × 40 × 0
= 0 × 10 × 5 + 0 × 20 × 3 + 1 × 30 × 1 + 0 × 40 × 0 = 30
使用方式 ④：
放入多個引數範圍，並判斷數值是否符合條件，將要判斷的引數範圍加上括號，並加上數學符號「 > 」或「 < 」，函數中使用判斷會回傳 TRUE 或 FALSE，TRUE 為數字 1，FALSE 為 數字0。
例如： SUMPRODUCT( ( B2 : B5 < 30 ) , B2 : B5 , C2 : C5 )
--- 計算單價小於30乘以數量的總和
= (單價10) × 10 × 5 + (單價20) × 20 × 3 + (單價30) × 30 × 1 + (單價40) × 40 × 0
= (TRUE) × 10 × 5 + (TRUE) × 20 × 3 + (FALSE) × 30 × 1 + (FALSE) × 40 × 0
= 1 × 10 × 5 + 1 × 20 × 3 + 0 × 30 × 1 + 0 × 40 × 0 = 50 + 60 = 110
函數實際操作
此篇的函數公式結果顯示如下( 黃底 內容)：
範例資料如下：
要計算出不同水果種類進口與非進口(本土)的總數量與總金額。
要計算出不同水果種類進口與非進口(本土)的總數量與總金額。
- 《 步驟 》
- 計算進口數量 (D2)：
在儲存格 D2 中，使用 Sumproduct 函式加入判斷產地是否為進口，因此引數1加入(G3:G8=B2)，引數2則計算數量，範圍為 I3:I8。
=SUMPRODUCT((G3:G8=B2),I3:I8) - 固定資料範圍：
繼續編輯 D2，在範圍的儲存格英文數字前面加上「$」，這樣在拉取套用公式的時候就不會被調整到。
=SUMPRODUCT(($G$3:$G$8=B2),$I$3:$I$8) - 計算本土資料 (D4)：
複製 D2 儲存格公式，直接貼上 D4，公式就會自動調整沒有固定的欄位，變成以下公式。
=SUMPRODUCT(($G$3:$G$8=B4),$I$3:$I$8) - 計算進口總計金額 (D3)：
引數1一樣先判斷是否為進口，加入(G3:G8=B2)，總計金額=單價×數量，因此引數2及3加入單價與數量的範圍，H3:H8、I3:I8。
=SUMPRODUCT((G3:G8=B2),H3:H8,I3:I8) - 固定資料範圍：繼續編輯 D3，在範圍的儲存格英文數字前面加上「$」，這樣在拉取套用公式的時候就不會被調整到。=SUMPRODUCT(($G$3:$G$8=B2),$H$3:$H$8,$I$3:$I$8)
- 計算本土總計金額 (D5)：
複製 D3 儲存格公式，直接貼上 D5，公式就會自動調整沒有固定的欄位，變成以下公式。
=SUMPRODUCT(($G$3:$G$8=B4),$H$3:$H$8,$I$3:$I$8) - 計算總金額 (D6)：
計算金額只需要單價與數量的乘積就好，因此只要加入單價與數量的範圍。
=SUMPRODUCT(H3:H8,I3:I8) - 完成~~~~~
EXCEL公式小工具
使用方式
-
選擇是否要判斷，若需要判斷，繼續輸入判斷範圍與條件，若不需要則直接輸入儲存格範圍。
(範圍格式 ex：G3:G8；範圍條件可輸入文字，但需加上「"」雙引號) - 引數2繼續輸入儲存格範圍 (範圍須與引數1大小相同)
- 若有需要可再繼續增加引數
- 按下 Enter 按鈕
- 點擊輸出的函數公式就可以直接複製
- 將它貼在想要顯示的儲存格內即可
引數1
引數2Copied!
.jpg)
.jpg)
0 Comments:
張貼留言