使用 SUMPRODUCT 計算加權平均值

Excel 使計算多個單元格的平均值變得非常簡單——只需使用該函數 平均 (平均)。 但是,如果某些價值觀比其他價值觀更重要怎麼辦? 例如,在許多課程中,測試比作業更重要。 對於這種情況,有必要計算 加權平均.

Excel 沒有計算加權平均值的函數,但有一個函數可以為您完成大部分工作: SUMPRODUCT (總和產品)。 即使您以前從未使用過此功能,在本文結束時,您也會像專業人士一樣使用它。 我們使用的方法適用於任何版本的 Excel 以及其他電子表格,例如 Google 表格。

我們準備桌子

如果要計算加權平均值,則至少需要兩列。 第一列(在我們的示例中為 B 列)包含每個作業或測試的分數。 第二列(C 列)包含權重。 更大的權重意味著任務或測試對最終成績的影響更大。

要了解什麼是重量,您可以將其視為最終成績的百分比。 事實上,情況並非如此,因為在這種情況下,權重加起來應該是 100%。 我們將在本課中分析的公式將正確計算所有內容,並且不依賴於權重加起來的數量。

我們輸入公式

現在我們的表格已經準備好了,我們將公式添加到單元格中 B10 (任何空單元格都可以)。 與 Excel 中的任何其他公式一樣,我們以等號 (=) 開頭。

我們公式的第一部分是函數 SUMPRODUCT (總和產品)。 參數必須用括號括起來,所以我們打開它們:

=СУММПРОИЗВ(

=SUMPRODUCT(

接下來,添加函數參數。 SUMPRODUCT (SUMPRODUCT) 可以有多個參數,但通常使用兩個。 在我們的示例中,第一個參數將是一個單元格範圍。 B2:B9包含分數的 A。

=СУММПРОИЗВ(B2:B9

=SUMPRODUCT(B2:B9

第二個參數將是一系列單元格 C2:C9,其中包含權重。 這些參數必須用分號(逗號)分隔。 一切準備就緒後,關閉括號:

=СУММПРОИЗВ(B2:B9;C2:C9)

=SUMPRODUCT(B2:B9,C2:C9)

現在讓我們添加公式的第二部分,將函數計算的結果相除 SUMPRODUCT (SUMPRODUCT) 通過權重的總和。 我們稍後會討論為什麼這很重要。

為了執行除法運算,我們用符號繼續已經輸入的公式 / (直斜杠),然後編寫函數 (和):

=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(

=SUMPRODUCT(B2:B9, C2:C9)/SUM(

對於功能 (SUM) 我們將只指定一個參數——一系列單元格 C2:C9. 輸入參數後不要忘記關閉括號:

=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)

=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

準備好! 按鍵後 Enter, Excel 會計算加權平均值。 在我們的示例中,最終結果將是 83,6.

如何運作

讓我們分解公式的每個部分,從函數開始 SUMPRODUCT (SUMPRODUCT) 來了解它是如何工作的。 功能 SUMPRODUCT (SUMPRODUCT) 計算每個項目的分數及其權重的乘積,然後將所有結果乘積相加。 換句話說,該函數找到產品的總和,因此得名。 因此對於 作業 1 將 85 乘以 5,然後為 考試 將 83 乘以 25。

如果你想知道為什麼我們需要將第一部分的值相乘,想像一下任務的權重越大,我們就越需要考慮它的等級。 例如, 任務2 數了 5 次,然後 期末考試 – 45 次。 這就是為什麼 期末考試 對最終成績影響較大。

為了比較,在計算通常的算術平均值時,每個值只考慮一次,即所有值具有相同的權重。

如果你能深入了解一個函數 SUMPRODUCT (SUMPRODUCT),我們看到事實上她相信這一點:

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)

幸運的是,我們不需要寫這麼長的公式,因為 SUMPRODUCT (SUMPRODUCT) 自動完成所有這些。

本身就是一個功能 SUMPRODUCT (SUMPRODUCT)返回給我們一個巨大的數字 - 10450. 此時,公式的第二部分開始發揮作用: /總和(C2:C9) or /總和(C2:C9),將結果返回到分數的正常範圍,給出答案 83,6.

公式的第二部分非常重要,因為它允許您自動更正計算。 還記得權重之和不一定是 100% 嗎? 這一切都要歸功於公式的第二部分。 例如,如果我們增加一個或多個權重值,公式的第二部分將簡單地除以較大的值,再次得出正確答案。 或者我們可以使權重更小,例如通過指定值 0,5, 2,5, 3 or 4,5,並且公式仍然可以正常工作。 太棒了,對吧?

發表評論