如何在 Excel 中計算加權平均
要在 Excel 中計算加權平均,請將每個值乘以其權重,把乘積加總,再除以權重的總和:=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)。一般的 AVERAGE 會把每個值視為同等重要,所以當某些值比較重要時,結果就不正確。本指南說明公式、檢查方法與常見錯誤。
1. 何時需要加權平均
當各個項目的重要性不同時,就使用加權平均。例如考試佔成績 50% 的課程、某產品售出數量遠多於其他產品的平均價格,或某個評分標準最重要的分數。請把數值放在同一欄,權重放在下一欄,每個項目佔一列。
2. 公式
SUMPRODUCT 會逐列將兩個範圍的儲存格相乘,再把乘積加總,因此不需要輔助欄。再除以權重的 SUM 就得到平均值。權重可以是百分比、分數或數量,因為除法會自動調整比例。
範例:三個部分組成的課程成績
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 項目 | 分數 | 權重 | 加權平均 | |
| 2 | 考試 | 80 | 50 | 81 | |
| 3 | 專題 | 90 | 30 | ||
| 4 | 小考 | 70 | 20 |
80 乘以 50,加上 90 乘以 30,再加上 70 乘以 20,等於 8,100。除以權重總和 100,結果是 81。
3. 與一般平均值比較
同樣三個分數的一般 AVERAGE 結果是 80,因為它把小考和考試視為同等重要。加權結果 81 較高,是因為分數高的專題比分數低的小考更重要。如果兩個結果完全相同,請檢查權重是否全部相等。
範例:同一組分數的一般平均值
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 項目 | 分數 | 一般平均 | ||
| 2 | 考試 | 80 | 80 | ||
| 3 | 專題 | 90 | |||
| 4 | 小考 | 70 |
240 除以 3 等於 80,這個計算忽略了權重。
4. 權重為百分比
如果你的權重是加總為 100% 的百分比,可以省略除法:=SUMPRODUCT(B2:B4,C2:C4)。但若不確定,仍建議保留除法,因為即使權重加總不是 100%,它也能得到正確答案,而且新增列時仍然正確。
5. 權重為數量
計算平均價格時,權重是售出數量。請把價格放在一欄,數量放在下一欄,並使用同樣的公式。它會得到每單位的實際平均價格,這與價格的一般平均值不同。
常見錯誤
範圍大小不同。 B2:B4 與 C2:C5 的 SUMPRODUCT 會傳回 #VALUE!。
修正: 讓兩個範圍的列數相同。
忘記除法。 =SUMPRODUCT(B2:B4,C2:C4) 是加權總和,而不是平均值,除非權重加總為 1。
修正: 除以權重的 SUM。
權重儲存格中有文字。 SUMPRODUCT 會把文字當成零,因此該項目會悄悄從結果中消失。
修正: 確認每個權重都是數字,並用 COUNT 核對權重的數量。
常見問題
Excel 有內建的加權平均函數嗎?
沒有。SUMPRODUCT 除以 SUM 是標準的做法。
權重的總和一定要是 100 嗎?
不一定。當你除以權重的總和時,任何比例都可以:5、3 和 2 的結果,與 50、30 和 20 相同。
可以忽略空白儲存格嗎?
在 SUMPRODUCT 中,空白的分數會當成 0。若要排除它,請先篩選列,或使用輔助欄,在分數為空白時留空。
相關函數
- AVERAGEAVERAGE 會傳回數字或範圍的算術平均值,並略過文字與空白儲存格。包含語法、範例、結果的捨入方式與常見錯誤。.
- SUMSUM 能在一個公式中加總數字、儲存格與整個範圍。內容包括語法、範圍與累計總和的範例,以及 SUM 為何有時會傳回 0。.
- AVERAGEIFAVERAGEIF 會傳回符合單一條件的值的平均值,例如某個地區的平均訂單金額。包含語法、範例與常見錯誤。.
- SUMIFSUMIF 會加總符合單一條件的值,例如某個地區的所有銷售額,或所有大於 100 的金額。包含語法、範例與常見錯誤。.
更多關於 加權平均
本指南提供 SUMPRODUCT 公式與檢查方法。本文補充如何建立提供公式所需的表格、如何結合條件使用加權平均,以及如何以手算測試結果。
建立表格,讓公式保持簡單
將數值與權重並排放在兩欄中,每個項目一列,最上方有標題。如果權重本來就是百分比,請以百分比格式設定。內部不要有空白列,因為兩個範圍的形狀必須相同。之後新增項目時,請在範圍內部插入列,讓公式跟著擴大。在最後一列下方插入列不會擴展範圍,因此每次變更後都要檢查公式。Excel 表格可以解決這個問題,因為在最下方新增列時,它的範圍會自動擴展。
單一群組的加權平均
若只想平均某一類別的列,請在 SUMPRODUCT 中乘上一個條件。比較運算會傳回 TRUE 或 FALSE,相乘後會變成 1 和 0,因此其他列就視為零。
=SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7)/SUMPRODUCT((A2:A7="North")*C2:C7)以手算檢查結果
加權平均必須介於最小值與最大值之間。如果不是,代表權重有誤。用兩列來測試:數值 10 與 20,權重 1 與 3,得到 (10 + 60) / 4 = 17.5,較接近 20,因為這個值計算了三次。將結果與一般的 AVERAGE 比較:兩者的差距顯示權重的影響有多大。
請練習 [SUMPRODUCT 課程](/learn/sumproduct)與[統計基礎](/learn/statistics-basics)。