如何在 Excel 中計算加權平均值:2026 年 3 個簡單方法

若要在 Excel 中計算加權平均值,請將每個數值乘以其權重,將結果相加,然後除以權重之和。最快速的公式是 =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4),其中 B 欄代表數值,C 欄代表權重。如果您的權重總和已經是 100%,單靠 SUMPRODUCT 就能得出答案。
本指南將說明什麼是加權平均值以及何時使用它。接著,我們將介紹在 Excel 中計算加權平均值的三種方法:SUMPRODUCT 搭配 SUM、輔助欄,以及包含條件的 SUMPRODUCT。每種方法都包含實際操作範例。函數詳細資訊源自 Microsoft 的支援頁面,擷取日期為 2026 年 11 月 17 日。
什麼是加權平均值
一般的平均值會平等對待每個數值。Microsoft 將 AVERAGE 函數描述為傳回「引數的平均值 (算術平均值)」。每個數字只計算一次。
加權平均值會賦予某些數值比其他數值更大的影響力。每個數值都有一個權重,權重較大的數值會將結果拉向它們。
公式非常簡單:
加權平均值 = (數值 1 × 權重 1 + 數值 2 × 權重 2 + …) ÷ (權重 1 + 權重 2 + …)
以下是一個簡單的範例。某門課程的成績由作業、期中考和期末考組成。期末考佔成績的一半,因此一般的平均值會得出錯誤的結果。
| 項目 | 分數 | 權重 |
|---|---|---|
| 作業 | 85 | 20% |
| 期中考 | 78 | 30% |
| 期末考 | 92 | 50% |
這三個分數的一般平均值是 85。加權後的結果是 86.4,因為較高的期末考分數佔了最大的權重。這種差異正是加權平均值至關重要的原因。
何時使用加權平均值
當某些項目的重要性高於其他項目時,請使用加權平均值。常見的應用場景包括:
- 成績。考試、專案和作業佔有不同的權重。
- 價格。不同規模訂單所支付的平均價格。
- 投資組合。不同價值持股的平均報酬率。
- 調查。根據群體大小或樣本權重進行加權的回答。
- 庫存。以不同價格購入之存貨的加權平均成本。
- 關鍵績效指標 (KPI)。由多個不同重要性的指標所構成的分數。
權重可以來自不同的地方。有時它們是由規則設定的,例如教學大綱規定期末考佔 50%。有時它們來自資料本身,例如銷售數量或每項持股的價值。在這兩種情況下,數學計算方式都是相同的。
如果每個項目的重要性都相同,使用一般的平均值即可。如果某個項目顯然應該對結果有更大的影響力,您就需要使用權重。
設定您的資料
在撰寫任何公式之前,請先將資料整理成一個簡單的表格。每一列代表一個項目,並包含以下欄位:
| 欄位 | 內容說明 |
|---|---|
| A | 項目名稱,例如「作業」 |
| B | 數值,例如「分數」 |
| C | 權重,以數字或百分比表示 |
權重可以是百分比(例如 20%),也可以是一般數字(例如銷售數量)。只要您除以權重之和,這兩種方式都適用於下方的公式。
進行兩項檢查可以避免大部分的錯誤。第一,確保每個數值在同一列中都有對應的權重。第二,保持數值和權重範圍的大小相同,否則公式會傳回錯誤。
將範圍轉換為 Excel 表格也有助於操作。選取範圍,然後選取 [常用] > [格式化為表格]。如此一來,新增的列就會自動擴展表格,且使用表格參照的公式也會自動將其納入。
方法 1:SUMPRODUCT 與 SUM
這是 Excel 中計算加權平均值的標準方法。它在同一個公式中結合了兩個函數。
Microsoft 指出,SUMPRODUCT 會「傳回多個對應範圍或陣列之乘積的和」。簡單來說,它會將每個數值乘以其權重,然後將結果相加。
語法為 =SUMPRODUCT(array1, [array2], [array3], ...)。以我們的成績範例來說,數值位於 B2:B4,權重位於 C2:C4。請輸入:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
以下是 Excel 的運作方式:
- SUMPRODUCT 將 85 乘以 20%、78 乘以 30%,以及 92 乘以 50%。
- 它將這三個乘積相加:17 + 23.4 + 46 = 86.4。
- SUM 將權重相加:20% + 30% + 50% = 100%。
- 公式將 86.4 除以 100%,得出 86.4。
當權重總和為 100% 時,除法不會改變任何結果。但仍建議保留除法。如果日後有人修改了權重,使總和變成 90%,公式依然能給出正確的結果。
Microsoft 列出了兩條與此處相關的 SUMPRODUCT 規則。範圍「必須具有相同的維度」,否則函數會傳回 #VALUE! 錯誤。此外,SUMPRODUCT 會「將非數值陣列項目視為零」。空白或文字格式的權重會被默默視為零,因此請務必檢查您的資料。
Microsoft 也警告不要使用整欄參照。它指出,為了獲得最佳效能,SUMPRODUCT「不應與整欄參照一起使用」。請使用 B2:B500,而非 B:B。
方法 2:輔助欄
有些人喜歡看到每個步驟。輔助欄可以顯示每個乘積,這使得加權平均值更容易檢查和解釋。
- 在 D 欄中,輸入一個標題,例如 數值 × 權重。
- 在 D2 中,輸入
=B2*C2並向下複製。 - 在最後一列下方,使用
=SUM(D2:D4)計算 D 欄的總和。 - 使用
=SUM(C2:C4)計算權重的總和。 - 將第一個總和除以第二個總和。
以成績範例來說,D 欄會顯示 17、23.4 和 46。它們相加為 86.4。權重相加為 100%,因此加權平均值為 86.4。
這與 SUMPRODUCT 在一個步驟中完成的數學計算完全相同。Microsoft 官方頁面指出,像 =C2*D2+C3*D3+C4*D4+C5*D5 這樣較長的公式會得出與 SUMPRODUCT 「相同的結果」。
當需要教學、稽核或在任何其他人需要理解邏輯的工作表上時,請使用輔助欄。若您希望工作表簡潔,請使用方法 1。
方法 3:包含條件的 SUMPRODUCT
有時您只需要計算部分資料。例如,您可能想知道某個區域的平均支付價格,並以銷售數量進行加權。
SUMPRODUCT 可以在公式內部測試條件。Microsoft 的範例使用 =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9) 來計算在特定區域銷售的櫻桃數量。比較結果會傳回 TRUE 或 FALSE,在相乘時會被視為 1 或 0。
將相同的概念應用於此。假設 A 欄代表區域,B 欄代表價格,C 欄代表數量。若要取得東部 (East) 區域的加權平均價格:
=SUMPRODUCT((A2:A100="East")*B2:B100*C2:C100)/SUMIF(A2:A100,"East",C2:C100)
上半部分將價格乘以數量,但僅限於東部 (East) 的列。下半部分則使用 SUMIF 僅加總東部列的數量。Microsoft 解釋,SUMIF 會加總「符合您指定之準則的範圍內的值」。
請將區域名稱放入儲存格中,而不是直接輸入 "East"。這樣一來,您只需變更該儲存格,即可查看各個區域,而無需編輯公式。
若有兩個條件(例如區域和月份),請在公式上半部分加入第二個比較條件。下半部分則使用 SUMIFS 代替 SUMIF。
實際操作範例:加權平均價格
成績是經典的範例。在工作上,價格也同樣常見。以下是一個採購範例。
某家商店以不同的價格三次購入了相同的產品。價格為單價。
| 訂單 | 數量 | 單價 | 數量 × 價格 |
|---|---|---|---|
| 訂單 1 | 10 | 4.00 | 40.00 |
| 訂單 2 | 25 | 3.20 | 80.00 |
| 訂單 3 | 15 | 5.00 | 75.00 |
| 總計 | 50 | 195.00 |
這三個價格的一般平均值是 4.07。這個數字具有誤導性,因為大部分的數量都來自最便宜的那筆訂單。
加權平均值以數量作為權重:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)
上半部分是 195。下半部分是 50。加權後的單價是 3.90。這是存貨的真實平均成本,也是用於計算利潤或規劃重新訂購時應使用的數據。
請注意,這裡的權重是數量,而不是百分比。這沒關係。除以權重之和可以處理任何類型的權重。
樞紐分析表中的加權平均值
樞紐分析表可以快速彙總大型表格,但其內建選項不包含加權平均值。樞紐分析表中的「平均值」選項只是一般的平均值。不過,有一個簡單的解決方法。
- 在您的來源表格中,新增一個將數值乘以權重的輔助欄,例如
=B2*C2。將其命名為 Product。 - 重新整理樞紐分析表,使其偵測到新欄位。
- 選取樞紐分析表,然後選取 [樞紐分析表分析] > [欄位、項目和集] > [計算欄位]。
- 為欄位命名(例如 Weighted price),並輸入公式
=Product/Units。 - 將新欄位新增至 [值] 區域。
這之所以有效,是因為計算欄位除以的是總計,而不是單一列。對於每個群組,它會將乘積之和除以權重之和。這正是正確的數學計算方式。
將新欄位格式化為您需要的數值格式,例如小數點後兩位。現在,當您將任何區域、產品或月份拖曳到列時,樞紐分析表都會顯示正確的數據。
使用百分比計算加權平均值
許多讀者詢問如何處理以百分比表示的權重。這分為兩種情況。
權重總和為 100%。您可以直接使用 =SUMPRODUCT(B2:B4,C2:C4),而無需進行除法。結果已經是正確的。儘管如此,除以 SUM(C2:C4) 並不費事,且能防止四捨五入或輸入錯誤。
權重總和不為 100%。請務必除以權重之和。否則,得出的結果會過高或過低。
將權重儲存格格式化為百分比。選取它們,然後從 [常用] 索引標籤上的數值格式清單中選擇 [百分比]。在格式化為百分比的儲存格中輸入 20,Excel 就會將其儲存為 0.2。
常見的錯誤是混合使用格式,例如在某一列輸入 20%,而在下一列輸入 30。這樣 Excel 會將第二個權重視為 3,000%。請保持所有權重使用相同的格式。
使用 AI 在 Excel 中計算加權平均值
上述公式適用於單一表格。當您的資料分散在許多列或多個檔案中時,設定範圍會非常耗時。AI 工具可以讀取檔案並為您完成計算。
以下是使用 Powerdrill Bloom 的三個步驟方法。
步驟 1:上傳您的 Excel 檔案
上傳包含您的數值和權重的工作簿或 CSV。 Excel Calculations 頁面會要求您確保資料「已正確格式化,以利進行順暢的計算」。
您可以一次上傳多個檔案。該頁面指出,您「最多可以同時上傳 10 個檔案到一個資料集中」。
步驟 2:使用自然語言要求計算加權平均值
輸入您需要的計算。例如:「計算各區域的平均單價,並以銷售數量進行加權。」
該頁面指出,此工具「將識別並套用正確的公式」。您可以要求它顯示所使用的公式,以便您在工作表中重新建立。
步驟 3:檢查結果並重複使用公式
將結果與對單一群組進行的快速手動檢查進行比較。然後將公式複製到您的工作簿中,或匯出摘要表。
該頁面指出,此工具將「根據您的文字輸入快速產生精確的結果」。請將公式保留在工作表中,以便在資料變更時自動更新結果。
應避免的常見錯誤
這些是在 Excel 中計算加權平均值時最常出現的問題。
- 使用 AVERAGE。AVERAGE 會賦予每個數值相同的權重。請改用 SUMPRODUCT 和 SUM。
- 未除以權重。除非權重總和剛好是 100%,否則請務必除以權重之和。
- 範圍不相符。SUMPRODUCT 需要大小相同的範圍,否則會傳回 #VALUE!。
- 空白權重。空白權重會被視為零,這會使該列在沒有警告的情況下被忽略。
- 混合百分比格式。請保持所有權重皆為百分比,或皆為數字。
- 整欄參照。為了提高速度,請使用有範圍限制的區域,例如 B2:B500。
- 寫死條件。將條件放入儲存格中,以便公式重複使用。
關於其他常見的計算,請參閱我們的指南:如何在 Excel 中製作樞紐分析表以及如何在 Excel 中進行 ABC 分析。
當您的數值和權重存在於大型匯出檔案中時,請嘗試使用 Powerdrill Bloom,只需一句話即可要求計算出加權平均值。
常見問題
在 Excel 中計算加權平均值的公式是什麼?
請使用 =SUMPRODUCT(values,weights)/SUM(weights)。例如,=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) 會將每個數值乘以其權重,將結果相加,然後除以總權重。
我可以在不使用 SUMPRODUCT 的情況下計算加權平均值嗎?
可以。新增一個將每個數值乘以其權重的輔助欄。然後將該欄的總和除以權重的總和。
如何在 Excel 中使用百分比計算加權平均值?
如果百分比總和為 100%,則數值和權重的 SUMPRODUCT 即可得出答案。如果不是,請除以權重之和。
如何計算包含條件的加權平均值?
在 SUMPRODUCT 內部將條件(例如 (A2:A100="East"))與數值 and 權重相乘。然後除以相同條件下權重的 SUMIF。
平均值與加權平均值有何不同?
一般的平均值會平等對待每個數值。加權平均值則會賦予每個數值一個權重,因此較重要的數值對結果會有更大的影響力。
資料來源: Microsoft 支援服務,SUMPRODUCT 函數 · Microsoft 支援服務,AVERAGE 函數 · Microsoft 支援服務,SUMIF 函數。