Chapter 6. 函式 (一)：邏輯與運算 › 6.7 計算加權平均與加總
6.7 計算加權平均與加總
本節將介紹 SUMPRODUCT / AVERAGE.WEIGHTED 兩個函式：
- SUMPRODUCT：計算多個對應陣列的乘積加總
語法 | SUMPRODUCT (陣列或範圍_1, [陣列或範圍_2,…]) |
輸入 | 陣列或範圍_1：可以是陣列或多個儲存格的範圍，例如 {1, 2, 3, 4, 5} 或 A2:A5，每一個陣列大小需相同，不然會跳出 #VALUE!。 |
輸出 | 所有 陣列或範圍_n 相對應的乘積，例如 =SUMPRODUCT({1,2,3},{4,5,6}) 為 (1×4)+(2×5)+(3×6)=32。 |
- AVERAGE.WEIGHTED：計算加權平均
語法 | AVERAGE.WEIGHTED (值_1, 權重_1, [值_2, 權重_2,...]) |
輸入 |
|
輸出 | 所有 值_n 根據 權重_n 權重的加權平均，例如 =AVERAGE.WEIGHTED({90,70},{3,1}} 為 [(90×3)+(70×1)]÷(3+1)=85。 |
在此使用一個基本範例說明兩函式的用法，並比較異同之處，完成後結果如下。
- 計算每位學生的加權平均，其中權重為 B1:D1
公式
E3 的公式為下列任一個都可以：
- =SUMPRODUCT(B3:D3,B$1:D$1)
- =AVERAGE.WEIGHTED(B3:D3,B$1:D$1)
- =AVERAGE.WEIGHTED(B3,B$1,C3,C$1,D3,D$1)
解說
- 權重的列設為絕對參照，以便將公式套用到第 4~10 列。
- 在此使用 SUMPRODUCT 與 AVERAGE.WEIGHTED 結果相同，因為 B$1:D$1 的加總為 1 (100%)。
上述範例會覺得兩者的用法相同，但在以下兩種狀況結果可能會不同：
- 權重的顯示方式加總並非 1 (100%)：如下圖，例如要讓加總是 110%，此情況下 AVERAGE.WEIGHTED 會自動換為 100%。
- 權重並非以百分比而是用整數顯示：如下圖，此時 SUMPRODUCT 會直接乘上 100，若要避免此問題可以使用 =SUMPRODUCT(B3:D3,B$1:D$1)/SUM(B$1:D$1) 即可將權重調整為正確的加權平均。
了解兩個函式各自適合的使用時機後，會發現 SUMPRODUCT 除了可以達成所有 AVERAGE.WEIGHTED 計算加權平均之功能，還多出了計算多個陣列相乘的功能，因此建議可以優先記熟 SUMPRODUCT 的用法，而 AVERAGE.WEIGHTED 只需要知道有這個函式即可。
藉由這個範例也可以知道，在 Google Sheets 中有很多函式，功能與輸入方法都沒有很困難，最困難的是如何在有需要時馬上知道可以使用哪些函式完成計算，唯一的方式就是勤加練習讓自己熟悉各種試算表操作時可能遇到的問題，之後面對到一樣或類似的情況時可以快速的解決。