摘要:利用linest()函數(shù)進行預(yù)測,通常會因為自變量順序與預(yù)想的順序不一致、或者設(shè)想的數(shù)據(jù)之間存在某種等比或者等差等關(guān)系導(dǎo)致預(yù)測結(jié)果的錯誤,本文對這些講授重點進行了分析,以利于相關(guān)課程的完善。
關(guān)鍵詞:linest()函數(shù) 趨勢預(yù)測 自變量 因變量筆者在講授《EXCEL在財務(wù)管理中的應(yīng)用》課程時,發(fā)現(xiàn)某些時候?qū)W生在預(yù)測成本得到的結(jié)果出乎意料,經(jīng)過仔細分析,最終解決了問題,現(xiàn)將有關(guān)事項介紹如下,以免其他的老師和同學(xué)發(fā)生相同情況,花費太多的精力和時間。
一、涉及多變量的時候預(yù)測的成本不準確
Linest()函數(shù)主要針對線性函數(shù),利用給定的歷史數(shù)據(jù),就可以求出相關(guān)的函數(shù)關(guān)系,單實際練習(xí)(或者作業(yè))中,學(xué)生通常都會出現(xiàn)一些錯誤。
(一)自變量順序有誤
利用linest()函數(shù)進行預(yù)測的時候,已知自變量的順序和數(shù)據(jù)排列的順序不一致。
假設(shè)某種產(chǎn)品的總成本為Z,主要涉及生產(chǎn)人員數(shù)量X,材料投入數(shù)量Y,假設(shè)它們之間的關(guān)系式為:Z=AX+BY+C,其成本歷史數(shù)據(jù)如下圖1,其中的A1:G4部分為已知的歷史數(shù)據(jù):圖1歷史成本數(shù)據(jù)與預(yù)測結(jié)果
首先,我們求出兩個自變量的系數(shù)A和B、常數(shù)項C,以及相關(guān)系數(shù)平方R2(其值為0.984947,接近于1,說明相關(guān)程度很好,可以利用函數(shù)進行預(yù)測)。
其次,進行預(yù)測計算。
1、假設(shè)第一自變量系數(shù)就是A,第二自變量系數(shù)為B,我們在B5單元格中輸入公式:“=$B$10*B2+$B$11*B3+$B$12”,向右填充至G5,求得第一組利用函數(shù)計算的預(yù)測結(jié)果,我們會發(fā)現(xiàn)預(yù)測結(jié)果與實際歷史數(shù)據(jù)偏離很大,不準確。
2、假設(shè)第一自變量系數(shù)為B,第二自變量系數(shù)為A,再在B6單元格中輸入公式:“=$B$11*B2+$B$10*B3+$B$12”,填充至G6,則預(yù)測結(jié)果與實際歷史數(shù)據(jù)有偏離,但偏離度很小,比較接近,說明我們隊函數(shù)的應(yīng)用時正確的。
最后,總結(jié)錯誤。這種錯誤的發(fā)生主要是在練習(xí)或者實際工作中認為數(shù)據(jù)的排序和自變量的排序是一致的,本案例中表現(xiàn)為第二、三行與第一自變量X、第二自變量Y是否是排序一致,實際表現(xiàn)為反向。
不僅是學(xué)生容易犯此種錯誤,就是某些教材上也出現(xiàn)了同樣的錯誤,這是老師上課時必須注意的一個重要知識點。
(二)學(xué)生作業(yè)中歷史數(shù)據(jù)的設(shè)置有誤
學(xué)生在完成作業(yè)或者進行練習(xí)的時候,總是希望越省事越好,因此在數(shù)據(jù)設(shè)置的時候就出現(xiàn)了等比數(shù)列和等差數(shù)列問題,從而導(dǎo)致了預(yù)測結(jié)果的錯誤。
同上例,對部分數(shù)據(jù)進行修改,如圖2,A1:G4區(qū)域的數(shù)據(jù)為歷史數(shù)據(jù),計算相關(guān)的自變量系數(shù)A和B,以及常數(shù)項C和相關(guān)系數(shù)平方R2,R2的值接近于1,說明函數(shù)可以用于預(yù)測,且預(yù)測結(jié)果相對比較準確。在B5中輸入公式:“=$B$9*B2+$B$8*B3+$B$10”,并向右填充到G5,得到預(yù)測結(jié)果,比較接近于假設(shè)數(shù)據(jù)。圖2自變量呈等差數(shù)列時的預(yù)測情況1
通過圖2,我們會發(fā)現(xiàn),生產(chǎn)人員的數(shù)量與成本沒有關(guān)系,這是不符合我們的生產(chǎn)常識的。
修改歷史成本數(shù)據(jù)分別為16500、21500、25300、29500、34000、37800,A、C、R2的值都發(fā)生了變化,但B的值仍然為0.00,說明兩個自變量的值不能同時為等差數(shù)列。
通過對照分析,可知多個自變量的歷史數(shù)據(jù)不能同時為等差數(shù)列,否則通常預(yù)測結(jié)果是錯誤的,不符合常情。
同上,如果我們將材料投入的歷史數(shù)據(jù)改為2、3、4、5、6、7,分別是人員數(shù)的1/10的時候,B的值仍然為0.00,預(yù)測結(jié)果也是不符合常情。
對于作業(yè)中或者練習(xí)中的歷史數(shù)據(jù)的假設(shè)一定要注意是否是等差數(shù)列或者等比數(shù)列,自變量數(shù)據(jù)之間有沒有特殊的數(shù)據(jù)關(guān)系,以防出現(xiàn)預(yù)測錯誤情況,這是老師講課時應(yīng)該注意的一個重要問題。
總之,對于linest()函數(shù)在成本預(yù)測中的應(yīng)用,一定要慎之又慎,對于歷史數(shù)據(jù)的處理一定要留意他們之間的關(guān)系,否則容易出現(xiàn)運算結(jié)果錯誤的情況,也不利于學(xué)生理解掌握。(作者單位:1、長春大學(xué)管理學(xué)院 2、長春大學(xué)旅游學(xué)院)
參考文獻:
[1]張瑞君.計算機財務(wù)管理[M].中國人民大學(xué)出版社.2011(10).P270-276
[2]百度百科. LINEST函數(shù).http://baike.baidu.com/view/3219705.htm
[3]劉宣杰,李志剛. 用友ERP沙盤應(yīng)收賬款存在的問題與設(shè)想[J].商場現(xiàn)代化.2007(10).