如何在Excel中計算加權平均值？

假設您有一個如下面截圖所示的購物清單。雖然Excel的AVERAGE函數會給出不考慮重量或數量的平均價格，但在此類情況下，更準確的方法是計算加權平均值。這更好地反映了單位成本，因為具有更高重量或頻率的項目對最終結果有更大的影響力。

要計算加權平均價格，請如下使用SUMPRODUCT和SUM函數的組合：

選擇一個空白單元格，例如F2，輸入以下公式：

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

然後按Enter鍵獲取結果。

注意：在此公式中，C2:C18指重量列，D2:D18指價格列。根據您的數據佈局調整這些範圍。SUMPRODUCT函數將每個重量乘以其對應的價格並將結果相加，而SUM則總計重量——得出正確的加權平均值。確保使用的範圍長度相等，並且數據中沒有不匹配或空單元格，因為這可能會導致計算錯誤。

如果計算出的加權平均值顯示的小數位數過多或過少，您可以選擇該單元格，然後點擊 增加小數位數 按鈕 或 減少小數位數 按鈕 在 Home 選項卡上調整顯示的小數位數。

如果您遇到#VALUE!之類的錯誤，請仔細檢查每個引用的單元格是否包含數值，並確認範圍一致。此外，避免在計算範圍內包含任何標題行，以確保結果準確。當處理較大的數據集時，考慮使用命名範圍以提高清晰度和易維護性。

前面的公式計算所有項目的加權平均價格。在實際分析中，您可能希望計算特定類別的加權平均值，例如僅計算蘋果的加權平均價格。在這種情況下，您可以增強公式以根據您的條件包含一個條件。

為此，選擇一個空白單元格，例如F8，並輸入以下公式：

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

然後按下Enter鍵，計算符合您特定條件的加權平均值。該公式僅在項目符合條件（在這種情況下為“蘋果”）時，將每個重量和價格配對相乘，求和，並除以該項目的重量總和。

注意：這裡，B2:B18是水果列，C2:C18是重量，D2:D18是價格。根據需要將“蘋果”替換為另一個項目。這種方法適用於按一個條件進行篩選；如果您需要按多個條件篩選（例如，水果類型和供應商），則可能需要使用輔助列或更複雜的公式。

應用公式後，您可能希望調整小數以提高清晰度。選擇結果單元格並使用 增加小數位數 或 減少小數位數 按鈕在 Home 選項卡上更改顯示的小數位數。

如果公式返回意外的結果，請確認目標範圍內存在符合條件的項目，並留意意圖作為數值的列中是否有空白單元格或文本條目。

在某些情況下，您可能經常需要在大小變化的範圍內計算加權平均值，這些範圍可能包含缺失值或需要靈活過濾，例如同時應用多個條件。與其手動更新公式或範圍，不如使用VBA宏自動化計算，這樣可以節省時間並減少錯誤的機會——特別是在處理大型或定期更新的數據集時尤其有用。

以下是創建和使用VBA宏進行加權平均值計算的步驟：

1. 點擊開發工具 > Visual Basic（或按Alt + F11）打開Microsoft Visual Basic for Applications編輯器窗口。接下來，點擊插入 > 模塊，然後將以下代碼粘貼到新的模塊窗口中：

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2。按 F5 （或點擊 執行按鈕）來執行。
系統將提示您逐步選擇範圍（條件範圍——如果不需要可以跳過，重量範圍和值範圍）。您還可以輸入特定條件來過濾計算，或者留空以考慮所有數據。該宏支持動態數據範圍，因此如果您的表格經常增長或變化，非常實用。

最後，您將收到一個消息框，列出加權平均值結果。

提示：

• 此方法自動化重複的加權平均值分析，並且可以進一步擴展以處理額外的過濾或輸出選項。
• 確保選擇的範圍長度相等，且數據類型一致。
• 包含基本的錯誤處理（如示例所示）（例如，在未找到有效重量或重量總和為零的情況下）。
• 如果您只想應用於已過濾/可見的行，可以進一步增強代碼，實現特殊單元格枚舉。

如果您遇到權限或宏安全問題，請確保在運行代碼之前已在Excel設置中啟用了宏。

在Excel中四捨五入的平均值範圍

在Excel中平均變化率

在Excel中計算平均值/複合年增長率

移動/滾動平均值 Excel

累計總計/平均值 在Excel中