函數(shù)公式、職場(chǎng)模板摊沉、財(cái)務(wù)應(yīng)用狐史、分析圖表、練習(xí)題说墨、軟件工具骏全、表格合并、Office 365尼斧、Power Query姜贡、表格美化、符號(hào)作用棺棵、條件格式楼咳、學(xué)會(huì)騙、一本不正經(jīng)律秃、避坑指南爬橡、數(shù)據(jù)整理、篩選技巧棒动、偷懶寶典
??專題文章??
????最新文章??
· ?正 ?· ?文 ?· ?來 ?· ?啦 ?·
財(cái)務(wù)預(yù)測(cè) ? 2020年3月怜浅,本公眾號(hào)(Excel偷懶的技術(shù))寫了一篇介紹如何使用折線圖的趨勢(shì)線來做預(yù)測(cè)疫情的發(fā)展: 后來搀暑,在此文基礎(chǔ)上,進(jìn)一步拓展跨琳,介紹如何使用TREND函數(shù)自点,根據(jù)已知數(shù)據(jù),來預(yù)測(cè)脉让、評(píng)估: 從這篇文章我們知道功炮,利用TREND函數(shù),只能得到未來數(shù)據(jù)的預(yù)測(cè)值(結(jié)果)术唬,但是薪伏,不知道這些結(jié)果是根據(jù)什么方程式計(jì)算得來的。 要想知道計(jì)算未來值的方程式(線性回歸方程)碴开,就要用到LINEST函數(shù)毅该。 本文就來介紹LINEST函數(shù)。 一潦牛、統(tǒng)計(jì)知識(shí) 佛曰:“一切諸果,皆從因起挡育,一切諸報(bào)巴碗,皆從業(yè)起〖春”事出必有因橡淆,有果必有因。從統(tǒng)計(jì)學(xué)上來講母赵,有自變量逸爵,有因變量。問題的關(guān)鍵是:自變量凹嘲、因變量的關(guān)系如何师倔,相關(guān)性有多大。用什么來度量周蹭? 比如:差旅費(fèi)趋艘、招待費(fèi)肯定與銷售規(guī)模有一定關(guān)系;我們的工資與所在的行業(yè)凶朗、城市瓷胧、學(xué)歷、工作年限有一定的關(guān)系棚愤。我們需要知道的是他們的關(guān)系到底有多大搓萧。 能不能根據(jù)已有的數(shù)據(jù),來發(fā)現(xiàn)其因果關(guān)系宛畦,并用一個(gè)公式來描述瘸洛。 比如,某商品的銷售量與當(dāng)?shù)厝丝跀?shù)量存在一定的比例關(guān)系刃永,我們就可利用已有歷史數(shù)據(jù)繪制散點(diǎn)圖货矮。如果能找到一個(gè)線條,剛好穿過所有的點(diǎn)斯够。那么生成該線條的公式囚玫,就能完美地描述銷量和人口的因果關(guān)系喧锦。=LINEST(B2:B5,C2:C5,1,0)
公式結(jié)果如下: =LINEST(E2:E12,A2:D12,TRUE,TRUE)
提醒:
請(qǐng)注意計(jì)算結(jié)果中m?、m?……mn與X?岔绸、X?……的對(duì)應(yīng)關(guān)系理逊。
所以,如果根據(jù)計(jì)算出的A15:D15的系數(shù)盒揉,手動(dòng)計(jì)算評(píng)估A23:D23數(shù)據(jù)所對(duì)應(yīng)的價(jià)值晋被,其公式為:
=D15*A23+C15*B23+B15*C23+A15*D23+E15
其結(jié)果?與TREND計(jì)算的結(jié)果完全一樣:
=TREND(E2:E12,A2:D12,A23:D23)
案例3:多項(xiàng)式回歸預(yù)測(cè)
LINEST函數(shù)還可用于多項(xiàng)式回歸預(yù)測(cè)。
我們來看一下鰱魚體長(zhǎng)與每千克尾數(shù)的關(guān)系刚盈,第一張圖是一元線性回歸羡洛。
可用公式
=LINEST(D5:D35,C5:C35)
求出其系數(shù)。
可以看出藕漱,其趨勢(shì)線與數(shù)據(jù)的擬合度并不好欲侮,R平方值為0.8996崭闲。
我們將趨勢(shì)線改為多項(xiàng)式,階數(shù)為2
趨勢(shì)線公式為:
y = 2.2836x2 - 71.701x + 590.68
趨勢(shì)線與大多數(shù)點(diǎn)基本上重合了威蕉,擬合度很高刁俭。
接下來,在B列添加一列數(shù)據(jù)韧涨,數(shù)據(jù)等于C列值的平方牍戚。
然后再用LINEST函數(shù)對(duì)B列C列和D列進(jìn)行計(jì)算
=LINEST(D5:D35,B5:C35,1,1)
得到的系數(shù)與多項(xiàng)式圖表的公式中的系數(shù)完全相符。
這說明虑粥,LINEST函數(shù)不但可以用于多元線性預(yù)測(cè)如孝,還可用于多項(xiàng)式預(yù)測(cè)。
如果每次都要去添加輔助列來構(gòu)造計(jì)算舀奶,就比較麻煩暑竟。能否不添加輔助列?
我們可以使用下面的數(shù)組公式:
=LINEST(D5:D35,C5:C35^{1,2,3},1,1)
解釋:
^表示幾次方育勺,
{1,2罗岖,3}是常量數(shù)組涧至,相關(guān)知識(shí)請(qǐng)閱讀:
大括號(hào),用處大桑包,{1,0} {1;0}中間分號(hào)逗號(hào)是干啥南蓬?
由于生成的結(jié)果是一個(gè)矩形區(qū)域,所以我們可以使用INDEX函數(shù)來引用各個(gè)系數(shù)哑了,然后右拉填充:
=INDEX($G$35:$J$39,1,COLUMN(A1))
多項(xiàng)式各系數(shù)的完整公式為:
=INDEX(LINEST($D$5:$D$35,$C$5:$C$35^{1,2,3},1,1),1,COLUMN(A1))
歡迎加入Excel偷懶的技術(shù)讀者微信群赘方,學(xué)習(xí)更多Excel技巧。
加偷懶小助手拉你入群弱左,微信號(hào)toulanxzs
Excel偷懶的技術(shù)微信公眾號(hào)的OFFICE圖書:
本文使用 文章同步助手 同步