加權平均值與平均值

A weighted average is one that takes into account the importance, or weight, of each value. This article will show you how to use Excel’s SUMPRODUCT and SUM functions individually and how to combine the two to calculate a weighted average.

加權平均值是考慮到每個值的重要性或權重的平均值。 本文將向您展示如何分別使用Excel的SUMPRODUCT和SUM函數，以及如何將兩者結合起來以計算加權平均值。

什么是加權平均值？ (What is a Weighted Average?)

A weighted average is an average that takes into account the importance, or weight, of each value.? A good example would be calculating a student’s final grade based on their performance on a variety of different assignments and tests. Individual assignments usually don’t count as much towards a final grade as the final exam—things like quizzes, tests, and final exams will all have different weights. The weighted average is calculated as the sum of all of the values multiplied by their weights divided by the sum of all of the weights.

加權平均值是考慮每個值的重要性或權重的平均值。 一個很好的例子是根據學生在各種不同的作業和測驗中的表現來計算他們的最終成績。 通常，個人作業對期末成績的重視程度不及期末考試的重要程度，例如測驗，測試和期末考試的權重都不同。 加權平均值計算為所有值的總和乘以其權重再除以所有權重的總和。

The following example will demonstrate how to use Excel’s SUMPRODUCT and SUM functions to calculate a weighted average.

下面的示例將演示如何使用Excel的SUMPRODUCT和SUM函數來計算加權平均值。

讓我們看一個例子 (Let’s Look at an Example)

For our example, let’s look at a student’s quiz and exam scores. There are six quizzes each worth 5% of the total grade, two exams each worth 20% of the total grade, and one final exam worth 30% of the total grade.? The student’s final grade will be a weighted average, and we will use the SUMPRODUCT and SUM functions to calculate it.

對于我們的示例，讓我們看一下學生的測驗和考試成績。 有6個測驗，每個測驗占總成績的5％；兩個測驗，每個測驗占總成績的20％；以及一個期末測驗，占測驗總值的30％。 學生的最終成績將是加權平均值，我們將使用SUMPRODUCT和SUM函數進行計算。

As you can see in our table below, we’ve already assigned the relative weights to each quiz and exam in the D column.

如您在下表中所見，我們已經在D列中為每個測驗和考試分配了相對權重。

第一步：計算SUMPRODUCT (Step One: Calculate the SUMPRODUCT)

First, let’s look at how the SUMPRODUCT function works. Start by selecting the cell where you want the result to appear (in our example, that’s cell D13). Next, navigate to the “Formulas” menu, select the “Math & Trig” drop-down, scroll to the bottom, and click on the “SUMPRODUCT” function.

首先，讓我們看一下SUMPRODUCT函數的工作方式。 首先選擇要在其中顯示結果的單元格(在我們的示例中為D13單元格)。 接下來，導航到“公式”菜單，選擇“數學和觸發”下拉菜單，滾動到底部，然后單擊“ SUMPRODUCT”功能。

The “Function Arguments” window will appear.

出現“功能參數”窗口。

For the “Array1” box, select the student’s scores. Here, we’re selecting all the cells with actual scores in the C column.

對于“ Array1”框，選擇學生的分數。 在這里，我們選擇C列中所有具有實際分數的單元格。

Next, use the “Array2” box to select the weights of the quizzes and exams. For us, those are in the D column.

接下來，使用“ Array2”框選擇測驗和檢查的權重。 對我們來說，這些在D列中。

Click “OK” when you’re done.

完成后，單擊“確定”。

The SUMPRODUCT function will multiply each score by its corresponding weight and then return the sum of all of those products.

SUMPRODUCT函數將每個分數乘以其相應的權重，然后返回所有這些乘積的總和。

第二步：計算總和 (Step Two: Calculate the SUM)

Now let’s look at how the SUM function works. Select the cell where you want the results to appear?(in our example, that’s cell D14). Next, navigate to the “Formulas” menu, select the “Math & Trig” drop-down, scroll to the bottom, and click on the “SUM” function.

現在讓我們看一下SUM函數的工作方式。 選擇要在其中顯示結果的單元格(在我們的示例中，為單元格D14)。 接下來，導航到“公式”菜單，選擇“數學和觸發”下拉菜單，滾動到底部，然后單擊“求和”功能。

The “Function Arguments” window will appear.

出現“功能參數”窗口。

For the “Number1” box, select all of the weights.

對于“ Number1”框，選擇所有權重。

Click “OK.”

點擊“確定”。

The SUM function will add all of the values together.

SUM函數會將所有值加在一起。

第三步：結合SUMPRODUCT和SUM來計算加權平均值 (Step Three: Combine the SUMPRODUCT and SUM to Calculate the Weighted Average)

Now we can combine the two functions to determine the student’s final grade based on their scores and the weights of each score. Select the cell where the weighted average should go (for us that’s cell D15) and then type the following formula into the function bar.

現在，我們可以結合這兩個功能，根據學生的分數和每個分數的權重來確定其最終成績。 選擇加權平均值應到達的單元格(對于我們來說是D15單元格)，然后在功能欄中鍵入以下公式。

=SUMPRODUCT(C3:C11,D3:D11)/SUM(D3:D11)

Press “Enter” after typing the formula to view the weighted average.

輸入公式后，按“ Enter”以查看加權平均值。

And there you have it. It’s a fairly simple example, but it’s a good one for showing how weighted averages work.

那里有。 這是一個非常簡單的示例，但是它是展示加權平均值的工作原理的一個很好的例子。

翻譯自: https://www.howtogeek.com/401826/how-to-calculate-a-weighted-average-in-excel/

加權平均值與平均值

總結

以上是生活随笔為你收集整理的加权平均值与平均值_如何在Excel中计算加权平均值的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。