如何使用TREND函數(shù)和LINEST函數(shù)做銷售預(yù)測(cè)或成本分析?
【例】如下圖所示A為產(chǎn)品數(shù)量,B列是對(duì)應(yīng)的單個(gè)產(chǎn)品成本。要求計(jì)算:當(dāng)生產(chǎn)50個(gè)產(chǎn)品時(shí),相對(duì)應(yīng)的成本是多少?
方法1:使用TREND函數(shù)。
公式:
=TREND(OFFSET(B1,MATCH(D2,A:A)-1,,2),OFFSET(A1,MATCH(D2,A:A)-1,,2),D2)
公式說(shuō)明:
Trend函數(shù)是做線性預(yù)測(cè)的函數(shù),但本例中的A列和B列并非線性關(guān)系(y=ax+b)。所以需要分段插值。即在A列查找到相鄰的小值和大值。如50為13~68。
MATCH(D2,A:A)-1:利用match函數(shù)的模糊查找功能,找到比樣本小且最接近的值。如比50小的是13
OFFSET(B1,match()-1,,2):用offset返回小值和大值的所在B列區(qū)域。如50對(duì)應(yīng)B列的是B5:B6,同理A列的區(qū)域A5:A6
D2:是樣本值。本例是50
Trand函數(shù)預(yù)測(cè)的結(jié)果是:76.64
方法2:利用LINEST函數(shù)
公式:
=D2*INDEX(LINEST($B$2:$B$8,$A$2:$A$8),1)+INDEX(LINEST($B$2:$B$8,$A$2:$A$8),2)
LINEST():如果我們知道A列和B列對(duì)應(yīng)的線型關(guān)系式(y=ax+b),那么我們可以直接把X值代入求值。而LINEST函數(shù)可以根據(jù)兩組數(shù)據(jù),直接取得a和b的值。如本例:LINEST($B$2:$B$8,$A$2:$A$8)可以返回{-0.05,85.97},其中-0.05是a,85.9是b。那么關(guān)系式出來(lái): y=-0.05x+85.9
INDEX(linest(),1)可以取值第一個(gè)值,即a的值。同理當(dāng)為2時(shí)可以取出b的值。
方法2預(yù)測(cè)的結(jié)果是:83.39(由于預(yù)測(cè)原理不同,結(jié)果和方法不相同是正常的)
繼續(xù)查找其他問題的答案?
-
Excel 2019如何使用復(fù)制、粘貼命令插入Word數(shù)據(jù)?(視頻講解)
2021-01-257次播放
-
Excel 2019如何制作數(shù)據(jù)透視表?(視頻講解)
2021-01-2227次播放
-
Excel 2019如何設(shè)置相對(duì)引用?(視頻講解)
2021-01-2210次播放
-
2021-01-2212次播放
-
Excel 2019如何使用命令復(fù)制公式?(視頻講解)
2021-01-226次播放
-
2021-01-223次播放
-
2021-01-222次播放
-
Excel 2019如何利用開始選項(xiàng)卡進(jìn)入篩選模式?(視頻講解)
2021-01-223次播放
-
Excel 2019如何設(shè)置指定數(shù)據(jù)的篩選?(視頻講解)
2021-01-223次播放
-
Excel 2019如何設(shè)置自定義排序?(視頻講解)
2021-01-222次播放
點(diǎn)擊加載更多評(píng)論>>