在统计学中，均方误差 ( MSE ) 是衡量统计模型中误差量的重要指标。它通常用于评估用于预测未来值的模型的准确性。
在本教程中，我们将更详细地解释什么是MSE ，然后向您展示如何在Excel中计算(Excel)MSE。
什么是均方误差？
均方误差 ( MSE ) 是一种计算方法，用于测量数据集中估计值与实际值之间的平均平方差。换句话说，它估计统计模型中的错误量。对于统计极客来说，它计算回归线与一组数据点的拟合程度。
例如，假设您有一个模型可以预测您作为一家软件公司一年内将获得的销售价值。在年底，您可以输入生成的实际销售值。然后，您可以计算MSE以了解您的模型预测结果的效果。
MSE是通过取目标变量的预测值和实际值之间的平方差的平均值来计算的。
MSE 公式如下所示：
在哪里：
- Σ表示数值之和；
- n 是样本大小或观察次数；
- Yi 是观测值，并且；
- Ŷi 是预测值。
误差值越低，表明模型的标准误差越小，对目标变量的预测能力越好。MSE广泛应用于统计回归和数据分析，有助于比较不同模型或调整参数以提高预测的准确性。
这看起来很令人困惑，但是当我们在下面的部分中对其进行分解时，就不难理解了。
如何在Microsoft Excel中计算(Microsoft Excel)均方误差(Mean Squared Error)
在Microsoft Excel中计算MSE 的(MSE)主要方法有两种：SUMSQ函数、AVERAGE 函数(AVERAGE function)和MSE公式。我们将使用下面的示例向您展示如何使用以下每个函数
计算MSE ：
在这个简化的示例中，我们将查看每个月的虚构销售值（A 列(Column A)）。B 列(Column B)表示估计值， C 列(Column C)表示实际值。
(Calculate MSE)使用SUMSQ 函数(SUMSQ Function)计算 MSE
Excel中的SUMSQ函数用于计算某个范围内数字的平方和。以下是如何使用它来计算MSE：
- 在D 列(Column D)中创建一个名为“ Difference ”的新列。这将代表预期值和实际值之间的差异。
- 使用公式“C2-B2”计算每行中数据点的差异。
- 选择一个新单元格并键入以下语法：“=SUMSQ(D2: D13/COUNT (D2: D13 )”。将公式中的单元格替换为Excel电子表格中的正确单元格。此单元格中的最终结果是您的MSE。
(Calculate MSE)使用AVERAGE函数
计算 MSE
MS Excel中的AVERAGE函数用于计算一系列数字的平均值。此方法返回与SUMSQ(SUMSQ)函数相同的结果，但每个步骤都是手动执行的。
- 执行(Perform)SUMSQ方法中描述的步骤 1 和 2 。这将返回预测值和实际数据之间的差异。
- 将 E 列命名为“差异平方”，然后使用公式“=D2^2”计算D 列(Column D)中每个值的差异平方。
- 选择一个新单元格并计算E 列(Column E.)的平均值。这将返回您之前计算的平方值的平均值。为此，请输入“=AVERAGE(E2:E12)”并按Enter。确保将这些单元格替换为您在(Make)工作表(worksheet)中使用的单元格。生成的数据值就是您的MSE。
Microsoft Excel中的统计分析
Microsoft Excel长期以来一直是最强大的数据分析工具之一。希望(Hopefully)通过本教程，您现在可以使用Excel轻松计算MSE 。而且，由于它们非常相似，您应该能够在Google Sheets中执行相同的计算。
How to Calculate Mean Squared Error (MSE) in Microsoft Excel
In statistics, the mean squared error (MSE) is аn important metric that measures the amount of errоr in a statistical model. It’s often used to assess the accuracy of a model that’s used to predict future values.
In this tutorial, we’ll explain what MSE is in more detail and then show you how to calculate MSE in Excel.
What Is Mean Squared Error?
The mean squared error (MSE) is a calculation that measures the average squared difference between estimated and actual values in a dataset. In other words, it estimates the amount of error in a statistical model. For the statistics geeks out there, it calculates how closely a regression line fits a set of data points.
For example, say you have a model that predicts the value of sales you’ll receive as a software company over a year. At the end of the year, you plug in the actual sales values that you generated. You can then calculate the MSE to see how well your model predicted the outcome.
MSE is calculated by taking the average of the squared differences between the predicted and actual values of the target variable.
The MSE formula looks like this:
Where:
- Σ means the sum of values;
- n is the sample size or number of observations;
- Yi are the observed values, and;
- Ŷi are the predicted values.
A lower error value indicates that the model has a smaller standard error and is better at predicting the target variable. MSE is widely used in statistical regression and data analysis and can be helpful in comparing different models or tuning parameters to improve the accuracy of predictions.
This looks confusing, but it isn’t too hard to follow when we break it down in the following sections.
How to Calculate Mean Squared Error in Microsoft Excel
There are two main ways you can calculate MSE in Microsoft Excel: the SUMSQ function, the AVERAGE function, and the MSE formula. We’ll use the example below to show you how to calculate MSE using each of these functions:
In this simplified example, we’ll look at fictional sales values for each month (Column A). The estimated values are represented in Column B and the actual values in Column C.
Calculate MSE Using the SUMSQ Function
The SUMSQ function in Excel is used to calculate the sum of the squares of numbers in a range. Here’s how to use it to calculate MSE:
- Create a new column in Column D called “Difference”. This will represent the difference between the expected and actual values.
- Use the formula “C2-B2” to calculate the difference for the data points in each row.
- Select a new cell and type the following syntax: “=SUMSQ(D2:D13/COUNT(D2:D13)”. Replace the cells in the formula with the correct cells from your Excel spreadsheet. The final result in this cell is your MSE.
Calculate MSE Using the AVERAGE Function
The AVERAGE function in MS Excel is used to calculate the average of a range of numbers. This method returns the same result as the SUMSQ function, but each step is performed manually.
- Perform Steps 1 and 2 described in the SUMSQ method. This will return the differences between the forecasted values and actual data.
- Name Column E “Square of Differences”, then use the formula “=D2^2” to calculate the square of the difference of each value in Column D.
- Select a new cell and calculate the average value of Column E. This will return the mean of the squared values that you previously calculated. To do so, type “=AVERAGE(E2:E12)” and press Enter. Make sure to replace the cells with the cells you used in your worksheet. The resulting data value is your MSE.
Statistical Analysis in Microsoft Excel
Microsoft Excel has long been one of the most powerful tools for data analysis. Hopefully, with this tutorial, you can now use Excel to calculate MSE easily. And, since they’re so similar, you should be able to perform the same calculations in Google Sheets.
