今天给大家聊一下如何用Excel预测未来！
先别砸鸡蛋，看我小眼睛，我是认真的。
摊手，其实预测未来这事吧，说起来，Excel有很多可用的方法。今天给大家聊四种函数方案；也就是用函数实现移动平均预测、线性回归预测、指数回归预测、多项式拟合等。看完之后，你会发现……虽然看不懂，但好像很涨姿势的样子( •̅_•̅ )~~就不妨先收藏一波，以便将来备用。
▎1、移动平均预测
移动平均预测是一种比较简单的预测方法。随着时间序列的推移，它依次取连续的多项数据求取平均值，每移动一个时间周期就增加一个近期的数据，去掉一个远期的数据，得到一个新的平均数。由于它逐渐向前移动，所以称为移动平均法。
移动平均可以让数据更平滑，消除周期变动和不规范变动的影响，使得长期趋势得以显示，因而可以用于一些周期变动较小的预测。
举个例子。下图是某企业近一年的销售数据，需要以三个月为计算周期预测下一个月的销售额。
在C4单元格输入以下公式，复制到C13单元格。
=AVERAGE(B2:B4)
此时C列所得的结果就是这组销售额以三个月为周期的移动平均值，其中最后一个单元格C13的移动平均值，就是下一个月的销售额预测值：
▎2、线性回归预测
下图是某生产企业近一年的产量及其能耗数据，通过绘制X/Y散点图可以发现，产品和能耗两组数据基本呈现线性关系。
假设希望依照线性关系做预测分析，计算当产量达到2000时的能耗是多少，可以使用下面的公式：
=TREND(C2:C13,B2:B13,2000)
TREND函数语法为：
TREND(known_ y's，known_ x's，new_ x's，const)该函数用于返回一条线性回归拟合线的值。即找到适合已知数组 known_y’s 和 known_x’s 的直线，并返回指定数组 new_x’s 在直线上对应的 y 值。
其中第一参数是已知的目标值序列，第二参数是已知的变量值序列，第三参数是需要预测的目标值所对应的变量值。将数据表中的数据代入就可以通过线性拟合运算得到相应的预测值。
除了TREND函数，FORECAST函数也可以进行线性回归的预测，公式如下：
=FORECAST(2000,C2:C13,B2:B13)
FORECAST函数的语法，与TREND函数相比，只是在参数的排列位置上稍有区别：
FORECAST(x, known_y's, known_x's)使用以上两条公式会返回同样的计算结果，产量达到2000时能耗为886.049。
▎3、指数回归预测
下图显示了某国家近百年来人口数的增长记录，通过绘制柱形图并添加趋势线可以发现人口增长趋势基本符合指数增长的模型。
假定希望依照指数回归预测的方法对其2020年的人口进行预测，可以使用下面的公式：
=GROWTH(B2:B11,A2:A11,2020)
公式运算结果为：22289.06
GROWTH函数可用于拟合通项公式为y=b*m^x的指数曲线，语法和TREND函数相似：
GROWTH(known_y's,known_x's,new_x's,const)▎4、多项式拟合预测
下图是某种药物测试数据，是药物浓度随着时间变化、和相应的数据分布图表。
假设需要使用多项式曲线来对这组数据进行拟合……
首先，已知多项式曲线的通项公式为：
Y=m_0+m_1 x^1+m_2 x^2+m_3 x^3+⋯m_n x^n
其中n代表了多项式的阶数，m则表示与每个x幂次相对应的系数。
然后，使用LINEST函数可以求得不同阶次的多项式方程中的系数m值，进而就可以得到多项式曲线的拟合方程。
LINEST函数语法如下：
LINEST(known_y's,known_x's,const,stats)各参数含义与LOGEST函数的参数相同。
假定以2阶多项式来对上图所示的观测数据进行拟合，使用以下公式得到2阶多项式的系数：
=LINEST(B2:B15,A2:A15^{1,2})
这个公式的运算结果是一个包含三个数据的数组，数组中的三个数据依次是多项式拟合方程中m2、m1和m0的取值。将这三个系数取值代入到多项式拟合方程中就可以得到多项式拟合方程的y值公式：
=INDEX(LINEST(B2:B15,A2:A15^{1,2}),1)*x^2+INDEX(LINEST(B2:B15,A2:A15^{1,2}),2)*x+INDEX(LINEST(B2:B15,A2:A15^{1,2}),3)上述公式可以简化为数组公式：
=SUM(LINEST(B2:B15,A2:A15^{1,2})*x^{2,1,0})
将具体的x取值代入该公式就可以得到二阶多项式拟合曲线，在C2单元格输入以下公式，并复制到C15单元格，即可得到结果。
=SUM(LINEST(B$2:B$15,A$2:A$15^{1,2})*A2^{2,1,0})
结果下图所示：
……
盖木欧瓦。挥挥手，咱们下期再见。哦，对了，如果你对相似内容的Excel数据分析感兴趣，我这里向你推荐一本书。
评论(0)