在你的学校生活中，你已经了解了平均值和计算它的方法。计算平均值的公式非常简单。您只需将给定数据中的所有值相加，然后将结果除以数据中值的总数。移动平均(Moving average)线也是另一种具有实际应用的平均线。它在股票市场，销售等许多领域都有应用。
移动平均线分为三种类型，简单加权和指数。在本文中，我们将解释在Excel(Excel)中计算所有三种移动平均线的方法。
阅读(Read)：如何使用 Excel 的 MIN、Max 和 AVERAGE 函数(How to use MIN, Max, and AVERAGE Functions of Excel)。
如何在Excel中计算(Excel)移动平均线(Moving Average)
我们将使用两种方法在Excel中计算移动平均值。(Excel)在一种方法中，我们将使用MS Excel(MS Excel)的内置工具，在另一种方法中，我们将使用公式。
1]使用数据分析工具包
在这种方法中，我们将使用Excel中的(Excel)数据分析工具包(Data Analysis Tool Pack)来计算移动平均线。MS Excel附带一个计算简单移动平均线的工具。数据(Data)分析工具包(Analysis Tool Pack)是一个加载项，这意味着您可能默认没有它。我们在此处列出了获取此工具的步骤。
1]点击“文件(File)”选项。
2]选择“选项(Options)”。
3]这将打开一个对话框。点击(Click)“加载项”(Add-Ins)。
4]在对话框的右侧面板上，您将在底部看到一个下拉菜单。在下拉菜单中选择(Select)“ Excel Add-ins ”，然后单击“ (Excel Add-ins)Go ”按钮。
5] 现在选择“分析工具包(Analysis ToolPack)”并单击“确定(OK)”按钮。
6] 以上步骤将在MS Excel上安装(MS Excel)数据分析工具(Data Analysis Tool)。您可以在“数据(Data)”选项卡中查看它。
阅读(Read)：如何在 Excel 中计算两个数字之间的百分比差异(How to calculate the percentage difference between two numbers in Excel)。
现在您已准备好计算简单移动平均线。为了向您展示计算方法，我们创建了一个月前 10 天不同温度的样本数据。
请(Please)按照下面列出的步骤使用数据分析工具包(Data Analysis Tool Pack)计算简单移动平均线。
1] 点击“数据(Data)”选项卡并选择“数据分析(Data Analysis)”选项，如上图所示（见步骤 6）。
2]将打开一个小对话框，您必须在其中选择“移动平均线(Moving Average)”选项，然后单击“确定”。这是简单的移动平均线。
3]您必须输入要计算简单移动平均线的数据的输入范围。在我们的样本数据中，我们有从单元格 B2 到单元格B11的数据范围。因此，我们进入了范围 B2: B11。之后，输入“间隔(Interval)”。在输出范围中，您必须输入要在其中获取输出范围的单元格的地址。我们选择了 E2。完成后，单击确定。
4] 你会得到结果。您可以在下面的屏幕截图中看到，前三行显示#N/A 错误。这是因为我们在“ Interval ”中输入了 4，表示它是 4 天SMA。
阅读(Read)：如何在 Excel 中计算单利(How to Calculate Simple Interest in Excel)。
在上面的方法 1 中，我们借助Excel中的工具计算了移动平均线。但是使用方法 1，我们只能计算简单移动平均线。在方法 2 中，我们将使用公式计算所有三种类型的移动平均线。
2]使用公式(Formula)计算(Calculation)简单移动平均线(Simple Moving Average)( SMA )
我们将在这里采用相同的样本数据。
1] 如果要计算 4 天SMA，则必须在第 5 行（第 4 天(Day 4)）的单元格中输入以下公式。输入公式后，按“ Enter ”。
=AVERAGE(B2:B5)
2]现在，将光标悬停在所选单元格的右下角。当光标变为“加(Plus)号”图标时，按住鼠标左键并将其拖动到E11单元格。这会将公式复制到其他单元格。
3] 你会得到你的结果。
阅读(Read)：如何在 Excel 中计算矩形、三角形或圆形的面积(How to calculate the area of a Rectangle, Triangle, or Circle in Excel)。
3]使用公式(Formula)计算(Calculation)加权移动平均线(Weighted Moving Average)( WMA )
让我们计算加权移动平均线。同样(Again)，我们采用相同的样本数据。要计算WMA，您应该将权重分配给特定值。加权移动平均线的计算公式为：
WMA = [(Latest value * weight) + (Previous value * weight) + ...] / (Sum of all weights)
我们在这里计算 3 点WMA，70% 的权重分配给最新值，20% 分配给它之前的值，10% 分配给第二个之前的值。根据此数据，您必须在 E4 单元格中输入以下公式。
=(0.7*B4+0.2*B3+0.1*B2)/(0.7+0.2+0.1)
前两个单元格是空的，因为我们正在计算三点WMA。现在，将单元格拖到最后一个单元格，就像我们之前在SMA计算中所做的那样。你会得到你的结果。
阅读(Read)：如何在 Excel 中计算中位数(How to calculate Median in Excel)。
4]使用公式(Formula)计算(Calculation)指数移动平均线(Exponential Moving Average)( EMA )
要计算EMA，我们应该有第一个EMA值，我们通过计算SMA和权重乘数或平滑常数 ( K ) 获得。计算EMA的基本公式如下：
EMA = Latest Value or Today's Value * K + Yesterday EMA Value * (1 - K)
1] 让我们使用计算SMA的同一张表。在本例中，我们采用K = 0.3。第一个EMA值等于第一个SMA值。我们在这里计算 4 天SMA和EMA值。
2] 现在将以下公式应用于单元格 F6。
=B6*$G$1+F5*(1-$G$1)
请注意，我们在上面的公式中锁定了单元格 G，因为我们需要所有EMA计算的相同 K 值。
3]现在，拖动(Drag)单元格以将公式复制到剩余的单元格。
就是这样，我们已经在MS Excel中计算了所有三种类型的移动平均线。我们希望您喜欢这篇文章。
您可能还喜欢(You may also like)：如何在 Excel 中计算平均绩点或 GPA(How to calculate Grade Point Average or GPA in Excel)。
Calculate Simple, Weighted, and Exponential Moving Average in Excel
In your ѕchоol life, you have learned about average and the method to calculate it. The formula to calculate the average is very simple. You just have to add all the values in the given data and divide the result bу the tоtal number of values in the data. Moving average is also another type of average that has real-life applications. It has applications in many areas like the stock market, sales, etc.
Moving average is of three types, simple weighted and exponential. In this article, we will explain the methods to calculate all three types of moving averages in Excel.
Read: How to use MIN, Max, and AVERAGE Functions of Excel.
How to calculate Moving Average in Excel
We will calculate the moving average in Excel using two methods. In one method, we will use a built-in tool of MS Excel and in the other method, we will use formulae.
1] Using Data Analysis Tool Pack
In this method, we will use the Data Analysis Tool Pack in Excel to calculate the moving average. MS Excel comes with a tool to calculate simple moving averages. The Data Analysis Tool Pack is an add-in, which means you may not have it by default. We are listing here the steps to get this tool.
1] Click on the “File” option.
2] Select “Options.”
3] This will open a dialog box. Click on “Add-Ins.”
4] On the right panel of the dialog box, you will see a drop-down menu at the bottom. Select “Excel Add-ins” in the drop-down menu and click on the “Go” button.
5] Now select “Analysis ToolPack” and click on the “OK” button.
6] The above steps will install the Data Analysis Tool on MS Excel. You can view it in the “Data” tab.
Read: How to calculate the percentage difference between two numbers in Excel.
Now you are ready to calculate the simple moving average. To show you the calculation method, we have created sample data of varying temperatures for the first 10 days in a month.
Please follow the below-listed steps to calculate the simple moving average using Data Analysis Tool Pack.
1] Click on the “Data” tab and select the “Data Analysis” option as shown in the above screenshot (see step 6).
2] A small dialog box will open, where you have to select the “Moving Average” option and click OK. It is the simple moving average.
3] You have to enter the input range of the data for which you want to calculate the simple moving average. In our sample data, we have data ranges from cell B2 to cell B11. Therefore, we entered the range B2:B11. After that, enter the “Interval.” In the output range, you have to enter the address of the cell in which you want to get the output range. We selected E2. When you are done, click OK.
4] You will get the result. You can see in the below screenshot, the first three rows are showing #N/A error. This is because we have entered 4 in the “Interval,” which indicates that it is a 4 days SMA.
Read: How to Calculate Simple Interest in Excel.
In method 1 above, we have calculated the moving average with the help of a tool in Excel. But using method 1, we can calculate only the simple moving average. In method 2, we will calculate all three types of moving averages using formulae.
2] Calculation of Simple Moving Average (SMA) using Formula
We will take the same sample data here.
1] If you want to calculate the 4 days SMA, you have to enter the following formula in the cell that lies on row 5 (Day 4). After entering the formula, press “Enter.”
=AVERAGE(B2:B5)
2] Now, hover your cursor on the bottom right corner of the selected cell. When your cursor changes to the “Plus” icon, press and hold the left click of your mouse and drag it to the E11 cell. This will copy the formula to the other cells.
3] You will get your result.
Read: How to calculate the area of a Rectangle, Triangle, or Circle in Excel.
3] Calculation of Weighted Moving Average (WMA) using Formula
Let’s calculate the weighted moving average. Again, we are taking the same sample data. To calculate the WMA, you should have the weights assigned to the particular values. The formula to calculate the weighted moving average is:
WMA = [(Latest value * weight) + (Previous value * weight) + ...] / (Sum of all weights)
We are calculating here 3 point WMA with 70% weight is assigned to the latest value, 20% to the value just before it, and 10% to the value before the second one. According to this data, you have to enter the following formula in the E4 cell.
=(0.7*B4+0.2*B3+0.1*B2)/(0.7+0.2+0.1)
The first two cells are empty because we are calculating three-point WMA. Now, drag the cell to the last cell as we have done before in SMA calculation. You will get your result.
Read: How to calculate Median in Excel.
4] Calculation of Exponential Moving Average (EMA) using Formula
To calculate EMA, we should have the first EMA value, which we get by calculating the SMA and the weight multiplier or smoothing constant (K). The basic formula to calculate the EMA is as follows:
EMA = Latest Value or Today's Value * K + Yesterday EMA Value * (1 - K)
1] Let’s take the same table in which we have calculated the SMA. In this example, we have taken K = 0.3. The first EMA value is equal to the first SMA value. We are calculating here the 4 days SMA and EMA values.
2] Now apply the following formula to the cell F6.
=B6*$G$1+F5*(1-$G$1)
Do note that, we have locked the cell G in the above formula because we need the same value of K for all EMA calculations.
3] Now, Drag the cell to copy the formula to the remaining cells.
That’s it, we have calculated all three types of moving averages in MS Excel. We hope you enjoyed the article.
You may also like: How to calculate Grade Point Average or GPA in Excel.
