超級促銷週Claude Skills — 20% 折扣
Tips

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

Powerdrill Bloom·
如何在 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 的運作方式:

  1. SUMPRODUCT 將 85 乘以 20%、78 乘以 30%,以及 92 乘以 50%。
  2. 它將這三個乘積相加:17 + 23.4 + 46 = 86.4。
  3. SUM 將權重相加:20% + 30% + 50% = 100%。
  4. 公式將 86.4 除以 100%,得出 86.4。

當權重總和為 100% 時,除法不會改變任何結果。但仍建議保留除法。如果日後有人修改了權重,使總和變成 90%,公式依然能給出正確的結果。

Microsoft 列出了兩條與此處相關的 SUMPRODUCT 規則。範圍「必須具有相同的維度」,否則函數會傳回 #VALUE! 錯誤。此外,SUMPRODUCT 會「將非數值陣列項目視為零」。空白或文字格式的權重會被默默視為零,因此請務必檢查您的資料。

Microsoft 也警告不要使用整欄參照。它指出,為了獲得最佳效能,SUMPRODUCT「不應與整欄參照一起使用」。請使用 B2:B500,而非 B:B。

方法 2:輔助欄

有些人喜歡看到每個步驟。輔助欄可以顯示每個乘積,這使得加權平均值更容易檢查和解釋。

  1. 在 D 欄中,輸入一個標題,例如 數值 × 權重。
  2. 在 D2 中,輸入 =B2*C2 並向下複製。
  3. 在最後一列下方,使用 =SUM(D2:D4) 計算 D 欄的總和。
  4. 使用 =SUM(C2:C4) 計算權重的總和。
  5. 將第一個總和除以第二個總和。

以成績範例來說,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。這是存貨的真實平均成本,也是用於計算利潤或規劃重新訂購時應使用的數據。

請注意,這裡的權重是數量,而不是百分比。這沒關係。除以權重之和可以處理任何類型的權重。

樞紐分析表中的加權平均值

樞紐分析表可以快速彙總大型表格,但其內建選項不包含加權平均值。樞紐分析表中的「平均值」選項只是一般的平均值。不過,有一個簡單的解決方法。

  1. 在您的來源表格中,新增一個將數值乘以權重的輔助欄,例如 =B2*C2。將其命名為 Product。
  2. 重新整理樞紐分析表,使其偵測到新欄位。
  3. 選取樞紐分析表,然後選取 [樞紐分析表分析] > [欄位、項目和集] > [計算欄位]。
  4. 為欄位命名(例如 Weighted price),並輸入公式 =Product/Units。
  5. 將新欄位新增至 [值] 區域。

這之所以有效,是因為計算欄位除以的是總計,而不是單一列。對於每個群組,它會將乘積之和除以權重之和。這正是正確的數學計算方式。

將新欄位格式化為您需要的數值格式,例如小數點後兩位。現在,當您將任何區域、產品或月份拖曳到列時,樞紐分析表都會顯示正確的數據。

使用百分比計算加權平均值

許多讀者詢問如何處理以百分比表示的權重。這分為兩種情況。

權重總和為 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 個檔案到一個資料集中」。

上傳 Excel 檔案以使用 Powerdrill Bloom 計算加權平均值

步驟 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 函數。