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

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

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