如何用Excel做數(shù)據(jù)預(yù)測分析？
如何用Excel做數(shù)據(jù)預(yù)測分析？
AhaExcel
建議常用Excel的職場人關(guān)注，海量教程隨學(xué)隨用，隨用隨查。 主創(chuàng)：看見星光，微軟全球最有價(jià)值專家、Excel圖書作者、培訓(xùn)師。 內(nèi)容：每日四文，一篇函數(shù)教程、一篇VBA教程、一個(gè)短視頻小技巧、一篇雜文。
HI，大家好，我是星光。
今天給大家聊一下如何用Excel預(yù)測未來！
先別砸雞蛋，看我小眼睛，我是認(rèn)真的。
攤手，其實(shí)預(yù)測未來這事吧，說起來，Excel有很多可用的 方法。今天給大家聊四種函數(shù)方案；也就是用函數(shù)實(shí)現(xiàn)移動(dòng)平均預(yù)測、線性回歸預(yù)測、指數(shù)回歸預(yù)測、 多項(xiàng)式擬合等?？赐曛?，你會(huì)發(fā)現(xiàn)……雖然看不懂，但好像很漲姿勢的樣子( ??_?? )~~就不妨先收藏一波，以便將來備用。
▎ 1， 移動(dòng)平均預(yù)測
移動(dòng)平均預(yù)測是一種比較簡單的預(yù)測方法。 隨著時(shí)間序列的推移，它依次取連續(xù)的多項(xiàng)數(shù)據(jù)求取平均值，每移動(dòng)一個(gè)時(shí)間周期就增加一個(gè)近期的數(shù)據(jù)，去掉一個(gè)遠(yuǎn)期的數(shù)據(jù)，得到一個(gè)新的平均數(shù)。由于它逐漸向前移動(dòng)，所以稱為移動(dòng)平均法。
移動(dòng)平均可以讓數(shù)據(jù)更平滑， 消除周期變動(dòng)和不規(guī)范變動(dòng)的影響 ，使得長期趨勢得以顯示，因而可以用于一些周期變動(dòng)較小的預(yù)測。
舉個(gè)例子。下圖是 某企業(yè)近一年的銷售數(shù)據(jù)，需要以三個(gè)月為計(jì)算周期預(yù)測下一個(gè)月的銷售額。
在C4單元格輸入以下公式，復(fù)制到C13單元格。
=AVERAGE(B2:B4)
此時(shí)C列所得的結(jié)果就是這組銷售額以三個(gè)月為周期的移動(dòng)平均值，其中最后一個(gè)單元格C13的移動(dòng)平均值，就是下一個(gè)月的銷售額預(yù)測值：
▎ 2，線性回歸預(yù)測
下圖是某生產(chǎn)企業(yè)近一年的產(chǎn)量及其能耗數(shù)據(jù)，通過繪制X/Y散點(diǎn)圖可以發(fā)現(xiàn)，產(chǎn)品和能耗兩組數(shù)據(jù)基本呈現(xiàn)線性關(guān)系。
假設(shè)希望依照線性關(guān)系做預(yù)測分析，計(jì)算當(dāng)產(chǎn)量達(dá)到2000時(shí)的能耗是多少，可以使用下面的公式：
=TREND(C2:C13,B2:B13,2000)
TREND函數(shù)語法為：
TREND(known_ y
’s，known_ x’
s，new_ x
’s，const)
該函數(shù)用于返回一條線性回歸擬合線的值。即找到適合已知數(shù)組 known_y’s 和 known_x’s 的直線，并返回指定數(shù)組 new_x’s 在直線上對(duì)應(yīng)的 y 值。
其中第一參數(shù)是已知的目標(biāo)值序列，第二參數(shù)是已知的變量值序列，第三參數(shù)是需要預(yù)測的目標(biāo)值所對(duì)應(yīng)的變量值。將數(shù)據(jù)表中的數(shù)據(jù)代入就可以通過線性擬合運(yùn)算得到相應(yīng)的預(yù)測值。
除了TREND函數(shù)，F(xiàn)ORECAST函數(shù)也可以進(jìn)行線性回歸的預(yù)測，公式如下：
=FORECAST(2000,C2:C13,B2:B13)
FORECAST函數(shù)的語法，與TREND函數(shù)相比，只是在參數(shù)的排列位置上稍有區(qū)別：
FORECAST(x, known_y
’s, known_x’
s)
使用以上兩條公式會(huì)返回同樣的計(jì)算結(jié)果，產(chǎn)量達(dá)到2000時(shí)能耗為886.049。
▎ 3，指數(shù)回歸預(yù)測
下圖顯示了某國家近百年來人口數(shù)的增長記錄，通過繪制柱形圖并添加趨勢線可以發(fā)現(xiàn)人口增長趨勢基本符合指數(shù)增長的模型。
假定希望依照指數(shù)回歸預(yù)測的方法對(duì)其2020年的人口進(jìn)行預(yù)測，可以使用下面的公式：
=GROWTH(B2:B11,A2:A11,2020)
公式運(yùn)算結(jié)果為：22289.06
GROWTH函數(shù)可用于擬合通項(xiàng)公式為y=b*m^x的指數(shù)曲線，語法和TREND函數(shù)相似：
GROWTH(known_y
’s,known_x’
s,new_x
’s,const)
▎ 4，多項(xiàng)式擬合預(yù)測
下圖是 某種藥物測試數(shù)據(jù)，是藥物濃度隨著時(shí)間變化、和相應(yīng)的數(shù)據(jù)分布圖表。
假設(shè)需要使用多項(xiàng)式曲線來對(duì)這組數(shù)據(jù)進(jìn)行擬合……
首先，已知多項(xiàng)式曲線的通項(xiàng)公式為：
Y=m_0+m_1 x^1+m_2 x^2+m_3 x^3+?m_n x^n
其中n代表了多項(xiàng)式的階數(shù)，m則表示與每個(gè)x冪次相對(duì)應(yīng)的系數(shù)。
然后，使用LINEST函數(shù)可以求得不同階次的多項(xiàng)式方程中的系數(shù)m值，進(jìn)而就可以得到多項(xiàng)式曲線的擬合方程。
LINEST函數(shù)語法如下：
LINEST(known_y
’s,known_x’
s,
const
,stats)
各參數(shù)含義與LOGEST函數(shù)的參數(shù)相同。
假定以2階多項(xiàng)式來對(duì)上圖所示的觀測數(shù)據(jù)進(jìn)行擬合，使用以下公式得到2階多項(xiàng)式的系數(shù)：
=LINEST(B2:B15,A2:A15^{1,2})
這個(gè)公式的運(yùn)算結(jié)果是一個(gè)包含三個(gè)數(shù)據(jù)的數(shù)組，數(shù)組中的三個(gè)數(shù)據(jù)依次是多項(xiàng)式擬合方程中m2、m1和m0的取值。將這三個(gè)系數(shù)取值代入到多項(xiàng)式擬合方程中就可以得到多項(xiàng)式擬合方程的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)
上述公式可以簡化為數(shù)組公式：
=SUM(LINEST(B2:B15,A2:A15^{1,2})*x^{2,1,0})
將具體的x取值代入該公式就可以得到二階多項(xiàng)式擬合曲線，在C2單元格輸入以下公式，并復(fù)制到C15單元格，即可得到結(jié)果。
=SUM(LINEST(B$2:B$15,A$2:A$15^{1,2})*A2^{2,1,0})
結(jié)果下圖所示：
本文由公眾號(hào)“Excel星球”首發(fā)。
點(diǎn)擊 閱讀原文 ，加入Excel會(huì)員社群！