EXCEL應(yīng)用時(shí)間數(shù)列分析與預(yù)測(cè)

會(huì)計(jì)學(xué)1EXCEL應(yīng)用時(shí)間數(shù)列分析與預(yù)測(cè)9.1時(shí)間數(shù)列的基本特征9.1.1時(shí)間數(shù)列的概念與特點(diǎn)9.1.2時(shí)間數(shù)列的構(gòu)成與分解返回首頁(yè)第1頁(yè)/共50頁(yè)9.1.1時(shí)間數(shù)列的概念與特點(diǎn)時(shí)間數(shù)列具有以下特點(diǎn)：（1）時(shí)間數(shù)列按時(shí)間先后順序排列。（2）時(shí)間數(shù)列是按一定方式搜集的一系列數(shù)據(jù)。（3）時(shí)間數(shù)列中的觀察值具有差異。（4）時(shí)間數(shù)列中的數(shù)據(jù)不許遺漏。返回本節(jié)第2頁(yè)/共50頁(yè)9.1.2時(shí)間數(shù)列的構(gòu)成與分解影響時(shí)間數(shù)列變動(dòng)的因素主要有4種：（1）長(zhǎng)期趨勢(shì)（T）。（2）季節(jié)變動(dòng)（S）。（3）循環(huán)變動(dòng)（C）。（4）不規(guī)則變動(dòng)（I）。返回本節(jié)第3頁(yè)/共50頁(yè)9.2移動(dòng)平均法分析與預(yù)測(cè)9.2.1移動(dòng)平均法的概念及特點(diǎn)9.2.2趨勢(shì)圖直接預(yù)測(cè)法9.2.3利用Excel創(chuàng)建公式預(yù)測(cè)9.2.4利用移動(dòng)平均分析工具預(yù)測(cè)返回首頁(yè)第4頁(yè)/共50頁(yè)9.2.1移動(dòng)平均法的概念及特點(diǎn)移動(dòng)平均法是測(cè)定時(shí)間數(shù)列趨勢(shì)的一種方法。它按一定的間隔長(zhǎng)度逐期移動(dòng)，計(jì)算一系列的移動(dòng)平均數(shù)，來(lái)修勻原時(shí)間數(shù)列的波動(dòng)，呈現(xiàn)出現(xiàn)象發(fā)展的變動(dòng)趨勢(shì)。采取移動(dòng)平均法時(shí)，移動(dòng)平均間隔的長(zhǎng)度應(yīng)長(zhǎng)短適中。移動(dòng)平均法是在算術(shù)平均法的基礎(chǔ)上發(fā)展起來(lái)的預(yù)測(cè)方法，它利用過去若干期實(shí)際值的均值來(lái)預(yù)測(cè)現(xiàn)象的發(fā)展趨勢(shì)。簡(jiǎn)單移動(dòng)平均公式如下：返回本節(jié)第5頁(yè)/共50頁(yè)9.2.2趨勢(shì)圖直接預(yù)測(cè)法例9-1某電視機(jī)廠三年的銷售額（萬(wàn)元）資料如圖9-1（“移動(dòng)平均.xls”工作表）所示，試對(duì)第四年的銷售額進(jìn)行預(yù)測(cè)。（1）產(chǎn)生“年季”變量。1）打開“移動(dòng)平均”工作表。2）在C列選定任一個(gè)單元格，選擇“插入”菜單中的“列”選項(xiàng)，則原來(lái)C列的內(nèi)容被移到D列。3）在C1單元格中輸入標(biāo)志“年季”，在C2單元格中輸入公式“=B2&CHAR(13)&A2”，再把單元格C2中的公式復(fù)制到C3:C13。結(jié)果如圖9-2所示。第6頁(yè)/共50頁(yè)圖9-1“移動(dòng)平均”工作表第7頁(yè)/共50頁(yè)圖9-2產(chǎn)生“年季”變量第8頁(yè)/共50頁(yè)（2）繪制銷售額趨勢(shì)圖。1）打開“圖表向?qū)А睂?duì)話框，在“圖表類型”列表中選擇“折線圖”項(xiàng)；在“子圖表類型”列表中選擇“數(shù)據(jù)點(diǎn)折線圖”。2）在“圖表選項(xiàng)”中，選擇“標(biāo)題”頁(yè)面，在“圖表標(biāo)題”、“分類（X）軸”、“數(shù)值（Y）軸”中分別填入“銷售額趨勢(shì)圖”、“季度”和“銷售額”。第9頁(yè)/共50頁(yè)圖9-3“圖表源數(shù)據(jù)”對(duì)話框第10頁(yè)/共50頁(yè)圖9-4“圖表選項(xiàng)”對(duì)話框第11頁(yè)/共50頁(yè)（3）在圖表中插入趨勢(shì)線進(jìn)行預(yù)測(cè)。1）單擊圖表以激活它，選取垂直軸，雙擊或單擊鼠標(biāo)右鍵并從快捷菜單中選擇“坐標(biāo)軸格式”選項(xiàng)，彈出“坐標(biāo)軸格式”對(duì)話框。2）選取水平軸，雙擊或單擊鼠標(biāo)右鍵并從快捷菜單中選擇“坐標(biāo)軸格式”選項(xiàng)，打開“對(duì)齊”頁(yè)面，取消自動(dòng)設(shè)置；打開“字體”頁(yè)面，設(shè)置字號(hào)為8。單擊“確定”按鈕。3）選取圖中的折線，單擊鼠標(biāo)右鍵并從快捷菜單中選擇“添加趨勢(shì)線”選項(xiàng)，打開“添加趨勢(shì)線”對(duì)話框。選擇“類型”頁(yè)面，在“趨勢(shì)預(yù)測(cè)/回歸分析類型”框中選擇“移動(dòng)平均”，設(shè)置“周期”為4。單擊“確定”按鈕產(chǎn)生趨勢(shì)圖，如圖9-6所示。第12頁(yè)/共50頁(yè)圖9-5“坐標(biāo)軸格式”對(duì)話框第13頁(yè)/共50頁(yè)圖9-6銷售額趨勢(shì)圖返回本節(jié)第14頁(yè)/共50頁(yè)9.2.3利用Excel創(chuàng)建公式預(yù)測(cè)可以利用Excel提供的均值函數(shù)進(jìn)行移動(dòng)平均計(jì)算。（1）打開“移動(dòng)平均”工作表。（2）在單元格E1中輸入“公式預(yù)測(cè)值”。（3）在單元格E6中輸入公式“=AVERAGE(D2:D5)”，此處需要相對(duì)引用以便復(fù)制。（4）把單元格E6中的公式復(fù)制到E7:E14各單元格中，結(jié)果如圖9-7所示?？梢钥闯?，2001年第一季度的預(yù)測(cè)值為203萬(wàn)元。第15頁(yè)/共50頁(yè)圖9-7移動(dòng)平均預(yù)測(cè)結(jié)果返回本節(jié)第16頁(yè)/共50頁(yè)9.2.4利用移動(dòng)平均分析工具預(yù)測(cè)例9-2某地區(qū)過去15年商品零售額資料如圖9-8（“移動(dòng)分析”工作表）所示，用移動(dòng)分析工具進(jìn)行預(yù)測(cè)。（1）在“工具”菜單中選擇“數(shù)據(jù)分析”選項(xiàng)，在彈出的“數(shù)據(jù)分析”對(duì)話框中選中“移動(dòng)平均”選項(xiàng)，并單擊“確定”按鈕，此時(shí)將出現(xiàn)“移動(dòng)平均”對(duì)話框，如圖9-9所示。（2）在輸入?yún)^(qū)域中輸入B3:B17，間隔設(shè)為3，在輸出區(qū)域中輸入C3，即輸出區(qū)域的左上角的絕對(duì)引用。選擇“圖表輸出”和“標(biāo)準(zhǔn)誤差”。單擊“確定”按鈕，所得結(jié)果如圖9-10所示。第17頁(yè)/共50頁(yè)圖9-8零售額資料第18頁(yè)/共50頁(yè)圖9-9“移動(dòng)平均”對(duì)話框第19頁(yè)/共50頁(yè)圖9-10移動(dòng)平均分析結(jié)果返回本節(jié)第20頁(yè)/共50頁(yè)9.3回歸法分析與預(yù)測(cè)9.3.1時(shí)間數(shù)列預(yù)測(cè)工作表函數(shù)9.3.2使用直線函數(shù)和趨勢(shì)函數(shù)進(jìn)行線性預(yù)測(cè)9.3.3使用指數(shù)函數(shù)和增長(zhǎng)函數(shù)進(jìn)行非線性預(yù)測(cè)返回首頁(yè)第21頁(yè)/共50頁(yè)9.3.1時(shí)間數(shù)列預(yù)測(cè)工作表函數(shù)Excel提供的回歸分析函數(shù)主要有：1．FORECAST預(yù)測(cè)函數(shù)2．TREND趨勢(shì)函數(shù)3．GROWTH增長(zhǎng)函數(shù)4．LINEST線性擬合函數(shù)5．LOGEST函數(shù)第22頁(yè)/共50頁(yè)1．FORECAST預(yù)測(cè)函數(shù)該函數(shù)根據(jù)已有的數(shù)值計(jì)算或預(yù)測(cè)未來(lái)值。此預(yù)測(cè)值為基于給定的x值推導(dǎo)出的y值。已知的數(shù)值為已有的x值和y值，再利用線性回歸對(duì)新值進(jìn)行預(yù)測(cè)?？梢允褂迷摵瘮?shù)對(duì)未來(lái)銷售額、庫(kù)存需求或消費(fèi)趨勢(shì)進(jìn)行預(yù)測(cè)。語(yǔ)法：FORECAST(x,known_y's,known_x's)第23頁(yè)/共50頁(yè)2．TREND趨勢(shì)函數(shù)該函數(shù)返回一條線性回歸擬合線的值。即找到適合已知數(shù)組known_y's和known_x's的直線（用最小二乘法），并返回指定數(shù)組new_x's在直線上對(duì)應(yīng)的y值。語(yǔ)法：TREND(known_y's,known_x's,new_x's,const)第24頁(yè)/共50頁(yè)3．GROWTH增長(zhǎng)函數(shù)該函數(shù)根據(jù)現(xiàn)有的數(shù)據(jù)預(yù)測(cè)指數(shù)增長(zhǎng)值。根據(jù)現(xiàn)有的x值和y值，GROWTH函數(shù)返回一組新的x值對(duì)應(yīng)的y值?？梢允褂肎ROWTH工作表函數(shù)來(lái)擬合滿足現(xiàn)有x值和y值的指數(shù)曲線。語(yǔ)法：GROWTH(known_y's,known_x's,new_x's,const)第25頁(yè)/共50頁(yè)4．LINEST線性擬合函數(shù)該函數(shù)使用最小二乘法對(duì)已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返回描述此直線的數(shù)組。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。直線的公式為：或 （如果有多個(gè)區(qū)域的x值）。語(yǔ)法：LINEST(known_y's,known_x's,const,stats)第26頁(yè)/共50頁(yè)5．LOGEST函數(shù)該函數(shù)在回歸分析中，計(jì)算最符合數(shù)據(jù)的指數(shù)回歸擬合曲線，并返回描述該曲線的數(shù)值數(shù)組。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組，故必須以數(shù)組公式的形式輸入。此曲線的公式為：或 （如果有多個(gè)x值）。語(yǔ)法：LOGEST(known_y's,known_x's,const,stats)返回本節(jié)第27頁(yè)/共50頁(yè)9.3.2使用直線函數(shù)和趨勢(shì)函數(shù)進(jìn)行線性預(yù)測(cè)1．使用LINEST函數(shù)計(jì)算回歸統(tǒng)計(jì)值2．使用趨勢(shì)函數(shù)TREND求預(yù)測(cè)值3．趨勢(shì)預(yù)測(cè)第28頁(yè)/共50頁(yè)圖9-11銷售額資料第29頁(yè)/共50頁(yè)圖9-12計(jì)算結(jié)果第30頁(yè)/共50頁(yè)

CD2參數(shù)b的估計(jì)值參數(shù)a的估計(jì)值3參數(shù)b的標(biāo)準(zhǔn)誤差參數(shù)a的標(biāo)準(zhǔn)誤差4判定系數(shù)R2y值估計(jì)標(biāo)準(zhǔn)誤差5F統(tǒng)計(jì)值自由度6回歸平方和殘差平方和表9-1LINEST函數(shù)輸出結(jié)果對(duì)應(yīng)的統(tǒng)計(jì)量第31頁(yè)/共50頁(yè)圖9-13趨勢(shì)擬合值返回本節(jié)第32頁(yè)/共50頁(yè)9.3.3使用指數(shù)函數(shù)和增長(zhǎng)函數(shù)進(jìn)行非線性預(yù)測(cè)例9-4某地區(qū)1991~2000年汽車銷售量（萬(wàn)輛）資料如圖9-14（“汽車銷量”工作表）所示。試對(duì)其擬合指數(shù)曲線，并預(yù)測(cè)2001~2003年的銷量。1．使用LOGEST函數(shù)計(jì)算回歸統(tǒng)計(jì)量2．使用增長(zhǎng)函數(shù)GROWTH計(jì)算預(yù)測(cè)值第33頁(yè)/共50頁(yè)圖9-14汽車銷量數(shù)據(jù)第34頁(yè)/共50頁(yè)圖9-15LOGEST函數(shù)計(jì)算及預(yù)測(cè)結(jié)果返回本節(jié)第35頁(yè)/共50頁(yè)9.4指數(shù)平滑法分析與預(yù)測(cè)9.4.1指數(shù)平滑法的基本內(nèi)容與要求9.4.2指數(shù)平滑分析工具預(yù)測(cè)9.4.3最佳平滑常數(shù)的確定返回首頁(yè)第36頁(yè)/共50頁(yè)9.4.1指數(shù)平滑法的基本內(nèi)容與要求指數(shù)平滑法是在移動(dòng)平均法基礎(chǔ)上發(fā)展起來(lái)的一種時(shí)間數(shù)列分析預(yù)測(cè)法，它通過計(jì)算指數(shù)平滑值，配合一定的時(shí)間數(shù)列預(yù)測(cè)模型對(duì)現(xiàn)象的未來(lái)進(jìn)行預(yù)測(cè)。指數(shù)平滑法通過對(duì)歷史時(shí)間數(shù)列進(jìn)行逐層平滑計(jì)算，從而消除隨機(jī)因素的影響，識(shí)別經(jīng)濟(jì)現(xiàn)象基本變化趨勢(shì)。根據(jù)平滑次數(shù)不同，指數(shù)平滑法分為：一次指數(shù)平滑法、二次指數(shù)平滑法和三次指數(shù)平滑法等。它們的基本思想都是：預(yù)測(cè)值是以前觀測(cè)值的加權(quán)和，且對(duì)不同的數(shù)據(jù)給予不同的權(quán)，新數(shù)據(jù)給較大的權(quán)，舊數(shù)據(jù)給較小的權(quán)。第37頁(yè)/共50頁(yè)1．一次指數(shù)平滑法2．二次指數(shù)平滑法3．三次指數(shù)平滑法返回本節(jié)第38頁(yè)/共50頁(yè)9.4.2指數(shù)平滑分析工具預(yù)測(cè)例9-5某企業(yè)1991年～2000年的產(chǎn)值（萬(wàn)元）資料如圖9-16（“平滑分析”工作表）所示，下面利用指數(shù)平滑工具進(jìn)行預(yù)測(cè)，具體步驟如下：（1）選擇“工具”菜單中的“數(shù)據(jù)分析”命令，彈出“數(shù)據(jù)分析”對(duì)話框。（2）在“輸入?yún)^(qū)域”指定數(shù)據(jù)所在的單元格區(qū)域B1:B11；因指定的輸入?yún)^(qū)域包含標(biāo)志行，所以選中“標(biāo)志”復(fù)選框；在“阻尼系數(shù)”中指定系數(shù)0.3。如圖9-17所示。（3）單擊“確定”按鈕。這時(shí)，Excel給出一次指數(shù)平滑值，如圖9-18所示。第39頁(yè)/共50頁(yè)圖9-16“平滑分析”工作表數(shù)據(jù)第40頁(yè)/共50頁(yè)圖9-17“指數(shù)平滑”對(duì)話框第41頁(yè)/共50頁(yè)圖9-18指數(shù)平滑分析結(jié)果返回本節(jié)第42頁(yè)/共50頁(yè)9.4.3最佳平滑常數(shù)的確定在指數(shù)平滑法中，預(yù)測(cè)成功的關(guān)鍵是的選擇。的大小規(guī)定了在新預(yù)測(cè)值中新數(shù)據(jù)和原預(yù)測(cè)值所占的比例。值愈大，新數(shù)據(jù)所占的比重就愈大，原預(yù)測(cè)值所占比重就愈小，反之亦然。若把一次指數(shù)平滑法的預(yù)測(cè)公式改寫為：第43頁(yè)/共50頁(yè)最佳的平滑常數(shù)應(yīng)使實(shí)際值和預(yù)測(cè)值之間的差最小，通常使預(yù)測(cè)誤差的平方和的平方根（RMSPE）最小。計(jì)算誤差的公式為：返回本節(jié)第44頁(yè)/共50頁(yè)9.5季節(jié)變動(dòng)的測(cè)定與分析季節(jié)變動(dòng)指現(xiàn)象受季節(jié)的影響而發(fā)生的變動(dòng)，即現(xiàn)象在一年內(nèi)或更短的時(shí)間內(nèi)隨著時(shí)序的更換，呈現(xiàn)周期重復(fù)的變化。季節(jié)變動(dòng)的原因，既有自然因素又有社會(huì)因素。季節(jié)變動(dòng)分析是根據(jù)以月、季為單位的時(shí)間數(shù)列資料，測(cè)定以年為周期的、隨著季節(jié)轉(zhuǎn)變而發(fā)生的周期性變動(dòng)的規(guī)律性。季節(jié)變動(dòng)分析為了消除偶然性因素的影響，至少需要三年以上的數(shù)據(jù)資料，年數(shù)愈多，偶然性因素消除得愈徹底。返回首頁(yè)第45頁(yè)/共50頁(yè)進(jìn)行季節(jié)變動(dòng)分析常采用長(zhǎng)期趨勢(shì)剔除法。長(zhǎng)期趨勢(shì)剔除法是指先配合趨勢(shì)模型，確定各月（季）的趨勢(shì)值加以剔除，再分析季節(jié)變動(dòng)的方法。具體有乘法型時(shí)間數(shù)列季節(jié)變動(dòng)分析和加法型時(shí)間數(shù)列季節(jié)變動(dòng)分析。乘法型時(shí)間數(shù)列季節(jié)變動(dòng)分析是將長(zhǎng)期趨勢(shì)值去除相應(yīng)的原時(shí)間數(shù)列的數(shù)據(jù)，剔除長(zhǎng)期趨勢(shì)影響，再用月（季）平均計(jì)算季節(jié)指數(shù)分析季節(jié)變動(dòng)。首先，計(jì)算Y/T剔除長(zhǎng)期趨勢(shì)影響；其次，根據(jù)消除長(zhǎng)期趨勢(shì)后的比率計(jì)