應(yīng)用Excel進(jìn)行時(shí)間序列分析

數(shù)據(jù)預(yù)測(cè)分析專題之一

——時(shí)間序列預(yù)測(cè)1整理課件數(shù)據(jù)預(yù)測(cè)分析的兩個(gè)主要方面：時(shí)間序列預(yù)測(cè)回歸分析預(yù)測(cè)2整理課件內(nèi)容簡(jiǎn)介時(shí)間序列的概念和組成時(shí)間序列預(yù)測(cè)的步驟衡量預(yù)測(cè)準(zhǔn)確性的指標(biāo)移動(dòng)平均模型和指數(shù)平滑模型趨勢(shì)預(yù)測(cè)模型季節(jié)指數(shù)模型3整理課件一、時(shí)間序列預(yù)測(cè)概述

1.時(shí)間序列時(shí)間序列就是一個(gè)變量在一定時(shí)間段內(nèi)不同時(shí)間點(diǎn)上觀測(cè)值的集合。這些觀測(cè)值是按時(shí)間順序排列的，時(shí)間點(diǎn)之間的間隔是相等的?？梢允悄?、季度、月、周、日或其它時(shí)間段。常見的時(shí)間序列有：按年、季度、月、周、日統(tǒng)計(jì)的商品銷量、銷售額或庫(kù)存量，按年統(tǒng)計(jì)的一個(gè)省市或國(guó)家的國(guó)民生產(chǎn)總值、人口出生率等。4整理課件5整理課件一、時(shí)間序列預(yù)測(cè)概述2.時(shí)間序列預(yù)測(cè)方法定性分析方法定量分析方法外推法：找出時(shí)間序列觀測(cè)值中的變化規(guī)律與趨勢(shì)，然后通過對(duì)這些規(guī)律或趨勢(shì)的外推來確定未來的預(yù)測(cè)值。包括：移動(dòng)平均和指數(shù)平滑法趨勢(shì)預(yù)測(cè)法季節(jié)指數(shù)法因果法：尋找時(shí)間序列因變量觀測(cè)值與自變量觀測(cè)值之間的函數(shù)依賴關(guān)系（因果關(guān)系/回歸分析），然后利用這種函數(shù)關(guān)系和自變量的預(yù)計(jì)值來確定因變量的預(yù)測(cè)值。6整理課件一、時(shí)間序列預(yù)測(cè)概述無趨勢(shì)線性趨勢(shì)非線性趨勢(shì)季節(jié)成分3.時(shí)間序列成分趨勢(shì)成分：顯示一個(gè)時(shí)間序列在較長(zhǎng)時(shí)期的變化趨勢(shì)季節(jié)成分：反映時(shí)間序列在一年中有規(guī)律的變化循環(huán)成分：反映時(shí)間序列在超過一年的時(shí)間內(nèi)有規(guī)律的變化不規(guī)則成分：不能歸因于上述三種成分的時(shí)間序列的變化7整理課件二、時(shí)間序列的預(yù)測(cè)步驟

第一步，確定時(shí)間序列的類型

即分析時(shí)間序列的組成成分（趨勢(shì)成分/季節(jié)成分/循環(huán)成分）。第二步，選擇合適的方法建立預(yù)測(cè)模型

如果時(shí)間序列沒有趨勢(shì)和季節(jié)成分，可選擇移動(dòng)平均或指數(shù)平滑法如果時(shí)間序列含有趨勢(shì)成分，可選擇趨勢(shì)預(yù)測(cè)法如果時(shí)間序列含有季節(jié)成分，可選擇季節(jié)指數(shù)法第三步，評(píng)價(jià)模型準(zhǔn)確性，確定最優(yōu)模型參數(shù)

第四步，按要求進(jìn)行預(yù)測(cè)8整理課件三、移動(dòng)平均模型和指數(shù)平滑模型

適用于圍繞一個(gè)穩(wěn)定水平上下波動(dòng)的時(shí)間序列。1.移動(dòng)平均模型利用平均使各個(gè)時(shí)間點(diǎn)上的觀測(cè)值中的隨機(jī)因素互相抵消掉，以獲得關(guān)于穩(wěn)定水平的預(yù)測(cè)將包括當(dāng)前時(shí)刻在內(nèi)的N個(gè)時(shí)間點(diǎn)上的觀測(cè)值的平均值作為對(duì)于下一時(shí)刻的預(yù)測(cè)值（N應(yīng)選擇得使MSE極小化）9整理課件【例1】某汽油批發(fā)商在過去12周內(nèi)汽油的銷售數(shù)量如表所示：試在Excel工作表中建立一個(gè)移動(dòng)平均預(yù)測(cè)模型來預(yù)測(cè)第13周的汽油銷量。實(shí)例：移動(dòng)平均模型10整理課件三、移動(dòng)平均模型和指數(shù)平滑模型11整理課件三、移動(dòng)平均模型和指數(shù)平滑模型2.指數(shù)平滑模型（改進(jìn)移動(dòng)平均預(yù)測(cè)模型），將計(jì)算平均值時(shí)對(duì)于不同時(shí)期觀測(cè)值的權(quán)數(shù)設(shè)置得不同：近期的權(quán)數(shù)較大，遠(yuǎn)期的權(quán)數(shù)較小12整理課件三、移動(dòng)平均模型和指數(shù)平滑模型指數(shù)平滑的疊代算法時(shí)間序列觀測(cè)值時(shí)間序列預(yù)測(cè)值時(shí)間序列觀測(cè)值時(shí)間序列預(yù)測(cè)值13整理課件【例2】利用例1的數(shù)據(jù)在Excel工作表中建立一個(gè)指數(shù)平滑預(yù)測(cè)模型來預(yù)測(cè)第13周的汽油銷量。實(shí)例：指數(shù)平滑模型14整理課件實(shí)例：

使用控件求解最優(yōu)跨度和最優(yōu)平滑指數(shù)【例4/例5】利用例1的數(shù)據(jù)在Excel工作表中建立一個(gè)利用函數(shù)和控件來控制移動(dòng)跨度、平滑指數(shù)的移動(dòng)平均模型和指數(shù)平滑預(yù)測(cè)模型來預(yù)測(cè)第13周的汽油銷量。試探索共有幾種利用MSE求最優(yōu)跨度和平滑系數(shù)的途徑？15整理課件四、趨勢(shì)預(yù)測(cè)模型

對(duì)于含有線性趨勢(shì)成分的時(shí)間序列，可以將預(yù)測(cè)變量在每一個(gè)時(shí)期的值和其對(duì)應(yīng)時(shí)期之間的線性依賴關(guān)系表示為：利用使均方誤差MSE極小的原則確定系數(shù)a與b，就可得到直線趨勢(shì)方程。以此求得每一個(gè)Xi所對(duì)應(yīng)的預(yù)測(cè)值:16整理課件四、趨勢(shì)預(yù)測(cè)模型求解a和b的三種方法：利用Excel內(nèi)建函數(shù)INTERCEPT()和SLOPE()利用數(shù)組函數(shù)LINEST()利用規(guī)劃求解工具求解預(yù)測(cè)值的四種方法：利用線性趨勢(shì)方程直接計(jì)算利用Excel內(nèi)建函數(shù)TREND()利用Excel內(nèi)建函數(shù)FORECAST()用特殊方法拖動(dòng)觀測(cè)值所在范圍17整理課件實(shí)例：趨勢(shì)預(yù)測(cè)模型【例3】針對(duì)NorthwindTraders公司月銷售額時(shí)間序列，建立趨勢(shì)預(yù)測(cè)模型，并預(yù)測(cè)該公司未來3個(gè)月的銷售額。

18整理課件五、Holt模型19整理課件實(shí)例：Holt預(yù)測(cè)模型【例6】某商場(chǎng)兩年內(nèi)各個(gè)月份的空調(diào)機(jī)銷售額數(shù)據(jù)如下表所示。假定商場(chǎng)空調(diào)機(jī)前年最后一個(gè)月的銷售額為42，前年銷售額的平均月增長(zhǎng)幅度為2.93。試建立一個(gè)Holt模型對(duì)商場(chǎng)未來的銷售額進(jìn)行預(yù)測(cè)。月份144月份757月份1379月份1996月份248月份867月份1482月份20100月份351月份972月份1580月份21100月份452月份1068月份1685月份22105月份558月份1172月份1794月份23110月份655月份1269月份1889月份24111商場(chǎng)各個(gè)月份空調(diào)銷售額20整理課件六、季節(jié)指數(shù)模型對(duì)于既含有線性趨勢(shì)成分又含有季節(jié)成分的時(shí)間序列，須對(duì)其成分進(jìn)行分解，這種分解建立在以下乘法模型的基礎(chǔ)上：其中，Tt表示趨勢(shì)成分，St表示季節(jié)成分，It表示不規(guī)則成分。由于不規(guī)則成分的不可預(yù)測(cè)，因此預(yù)測(cè)值就可表示為趨勢(shì)成分和季節(jié)成分的乘積。21整理課件六、季節(jié)指數(shù)模型建立季節(jié)指數(shù)模型的一般步驟：第一步，計(jì)算每一季（每季度，每月等等）的季節(jié)指數(shù)St

。第二步，用時(shí)間序列的每一個(gè)觀測(cè)值除以適當(dāng)?shù)募竟?jié)指數(shù)，消除季節(jié)影響。第三步，為消除了季節(jié)影響的時(shí)間序列建立適當(dāng)?shù)内厔?shì)模型并用這個(gè)模型進(jìn)行預(yù)測(cè)。第四步，用預(yù)測(cè)值乘以季節(jié)指數(shù)，計(jì)算出最終的帶季節(jié)影響的預(yù)測(cè)值。

22整理課件實(shí)例：季節(jié)指數(shù)模型【例7】某工廠過去4年的空調(diào)機(jī)銷量如下表所示，這些數(shù)據(jù)有明顯的季節(jié)性波動(dòng)，試建立一個(gè)季節(jié)指數(shù)模型來預(yù)測(cè)第5年每個(gè)季度的空調(diào)機(jī)銷量。

四年內(nèi)每季度的電視機(jī)銷量表

23整理課件實(shí)例：季節(jié)指數(shù)模型24整理課件實(shí)例：季節(jié)指數(shù)模型【例8】某工廠過去四個(gè)5年的納稅情況如右表所示，這些數(shù)據(jù)有明顯的季節(jié)性波動(dòng)，試建立一個(gè)季節(jié)指數(shù)模型來預(yù)測(cè)下一個(gè)5年的納稅情況。周期年納稅額(萬元)119864.819874.119885.61989619906.5219915.819925.219936.419946.819957.431996619975.619987.119997.520007.8420016.320025.920037.52004820058.425整理課件本章小結(jié)本章重點(diǎn)是時(shí)間序列的四種EXCEL工作表預(yù)測(cè)模型移動(dòng)平均模型指數(shù)平滑模型趨勢(shì)預(yù)測(cè)模型季節(jié)指數(shù)模型主要函數(shù)和EXCEL技術(shù)OFFSET()、SUMXMY2()、INDEX()、MATCH()、INTERCEPT()、SLOPE()、LINEST()、TREND()、FORECAST()

“規(guī)劃求解”工具、“數(shù)據(jù)分析”工具、可調(diào)圖形的制作

26整理課件應(yīng)用Excel進(jìn)行時(shí)間序列分析27整理課件重點(diǎn)1、Excel進(jìn)行移動(dòng)平均分析的操作步驟2、Excel進(jìn)行指數(shù)平滑分析的操作步驟3、Excel進(jìn)行趨勢(shì)外推預(yù)測(cè)法的操作步驟4、Excel進(jìn)行時(shí)間序列分解法的操作步驟28整理課件

移動(dòng)平均法是一種簡(jiǎn)單平滑預(yù)測(cè)技術(shù)，它的基本思想是：根據(jù)時(shí)間序列資料、逐項(xiàng)推移，依次計(jì)算包含一定項(xiàng)數(shù)的序時(shí)平均值，以反映長(zhǎng)期趨勢(shì)的方法。因此，當(dāng)時(shí)間序列的數(shù)值由于受周期變動(dòng)和隨機(jī)波動(dòng)的影響，起伏較大，不易顯示出事件的發(fā)展趨勢(shì)時(shí)，使用移動(dòng)平均法可以消除這些因素的影響，顯示出事件的發(fā)展方向與趨勢(shì)（即趨勢(shì)線），然后依趨勢(shì)線分析預(yù)測(cè)序列的長(zhǎng)期趨勢(shì)。29整理課件§1

Excel進(jìn)行移動(dòng)平均分析的操作步驟簡(jiǎn)單移動(dòng)平均法

公式表明當(dāng)t向前移動(dòng)一個(gè)時(shí)期，就增加一個(gè)新近數(shù)據(jù)，去掉一個(gè)遠(yuǎn)期數(shù)據(jù)，得到一個(gè)新的平均數(shù)。由于它不斷地“吐故納新”，逐期向前移動(dòng)，所以稱為移動(dòng)平均法。

由于移動(dòng)平均可以平滑數(shù)據(jù)，消除周期變動(dòng)和不規(guī)則變動(dòng)的影響，使得長(zhǎng)期趨勢(shì)顯示出來，因而可以用于預(yù)測(cè)。其預(yù)測(cè)公式為：

即以第t周期的一次移動(dòng)平均數(shù)作為第t+1周期的預(yù)測(cè)值。

30整理課件趨勢(shì)移動(dòng)平均法(線性二次移動(dòng)平均法)

當(dāng)時(shí)間序列沒有明顯的趨勢(shì)變動(dòng)時(shí)，使用一次移動(dòng)平均就能夠準(zhǔn)確地反映實(shí)際情況，直接用第t周期的一次移動(dòng)平均數(shù)就可預(yù)測(cè)第t+1周期之值。但當(dāng)時(shí)間序列出現(xiàn)線性變動(dòng)趨勢(shì)時(shí)，用一次移動(dòng)平均數(shù)來預(yù)測(cè)就會(huì)出現(xiàn)滯后偏差。因此，需要進(jìn)行修正，修正的方法是在一次移動(dòng)平均的基礎(chǔ)上再做二次移動(dòng)平均，利用移動(dòng)平均滯后偏差的規(guī)律找出曲線的發(fā)展方向和發(fā)展趨勢(shì)，然后才建立直線趨勢(shì)的預(yù)測(cè)模型。故稱為趨勢(shì)移動(dòng)平均法。

31整理課件

應(yīng)用舉例

已知某商場(chǎng)1978～1998年的年銷售額如下表所示，試預(yù)測(cè)1999年該商場(chǎng)的年銷售額。

年份

銷售額

年份

銷售額

1978

32

1989

76

1979

41

1990

73

1980

48

1991

79

1981

53

1992

84

1982

51

1993

86

1983

58

1994

87

1984

57

1995

92

1985

64

1996

95

1986

69

1997

101

1987

67

1998

107

1988

69

32整理課件下面使用移動(dòng)平均工具進(jìn)行預(yù)測(cè)，具體操作步驟如下：

1.選擇工具菜單中的數(shù)據(jù)分析命令，此時(shí)彈出數(shù)據(jù)分析對(duì)話框。

在分析工具列表框中，選擇移動(dòng)平均工具。

下面使用移動(dòng)平均工具進(jìn)行預(yù)測(cè)，具體操作步驟如下：

選擇工具菜單中的數(shù)據(jù)分析命令，此時(shí)彈出數(shù)據(jù)分析對(duì)話框。

在分析工具列表框中，選擇移動(dòng)平均工具。

33整理課件34整理課件35整理課件從圖可以看出，該商場(chǎng)的年銷售額具有明顯的線性增長(zhǎng)趨勢(shì)。因此要進(jìn)行預(yù)測(cè)，還必須先作二次移動(dòng)平均，再建立直線趨勢(shì)的預(yù)測(cè)模型。而利用Excel2000提供的移動(dòng)平均工具只能作一次移動(dòng)平均，所以在一次移動(dòng)平均的基礎(chǔ)上再進(jìn)行移動(dòng)平均即可。

二次移動(dòng)平均的方法同上，求出的二次移動(dòng)平均值及實(shí)際值與二次移動(dòng)平均值的擬合曲線，如下圖所示。

再利用前面所講的截距和斜率計(jì)算公式可得：

36整理課件于是可得t=21時(shí)的直線趨勢(shì)預(yù)測(cè)模型為：

預(yù)測(cè)1999年該商場(chǎng)的年銷售額為：

37整理課件§2

Excel進(jìn)行指數(shù)平滑分析的操作步驟

移動(dòng)平均法的預(yù)測(cè)值實(shí)質(zhì)上是以前觀測(cè)值的加權(quán)和，且對(duì)不同時(shí)期的數(shù)據(jù)給予相同的加權(quán)。這往往不符合實(shí)際情況。指數(shù)平滑法則對(duì)移動(dòng)平均法進(jìn)行了改進(jìn)和發(fā)展，其應(yīng)用較為廣泛。

指數(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)。

38整理課件

一次指數(shù)平滑法

設(shè)時(shí)間序列為，則一次指數(shù)平滑公式為：

式中為第t周期的一次指數(shù)平滑值；為加權(quán)系數(shù)，0＜＜1。

為了弄清指數(shù)平滑的實(shí)質(zhì)，將上述公式依次展開，可得：

因?yàn)榧訖?quán)系數(shù)符合指數(shù)規(guī)律，且又具有平滑數(shù)據(jù)的功能，所以稱為指數(shù)平滑。

用上述平滑值進(jìn)行預(yù)測(cè)，就是一次指數(shù)平滑法。其預(yù)測(cè)模型為：

即以第t周期的一次指數(shù)平滑值作為第t+1期的預(yù)測(cè)值。

39整理課件二次指數(shù)平滑法

當(dāng)時(shí)間序列沒有明顯的趨勢(shì)變動(dòng)時(shí)，使用第t周期一次指數(shù)平滑就能直接預(yù)測(cè)第t+1期之值。但當(dāng)時(shí)間序列的變動(dòng)出現(xiàn)直線趨勢(shì)時(shí)，用一次指數(shù)平滑法來預(yù)測(cè)仍存在著明顯的滯后偏差。因此，也需要進(jìn)行修正。修正的方法也是在一次指數(shù)平滑的基礎(chǔ)上再作二次指數(shù)平滑，利用滯后偏差的規(guī)律找出曲線的發(fā)展方向和發(fā)展趨勢(shì)，然后建立直線趨勢(shì)預(yù)測(cè)模型。故稱為二次指數(shù)平滑法。

40整理課件

應(yīng)用舉例

已知某廠1978～1998年的鋼產(chǎn)量如下表所示，試預(yù)測(cè)1999年該廠的鋼產(chǎn)量。年份

鋼產(chǎn)量

年份

鋼產(chǎn)量

1978

676

1989

2031

1979

825

1990

2234

1980

774

1991

2566

1981

716

1992

2820

1982

940

1993

3006

1983

1159

1994

3093

1984

1384

1995

3277

1985

1524

1996

3514

1986

1668

1997

3770

1987

1688

1998

4107

1988

1958

41整理課件下面利用指數(shù)平滑工具進(jìn)行預(yù)測(cè)，具體步驟如下：

選擇工具菜單中的數(shù)據(jù)分析命令，此時(shí)彈出數(shù)據(jù)分析對(duì)話框。

在分析工具列表框中，選擇指數(shù)平滑工具。

這時(shí)將出現(xiàn)指數(shù)平滑對(duì)話框，如圖所示。

42整理課件在輸入框中指定輸入?yún)?shù)。在輸入?yún)^(qū)域指定數(shù)據(jù)所在的單元格區(qū)域B1:B22；因指定的輸入?yún)^(qū)域包含標(biāo)志行，所以選中標(biāo)志復(fù)選框；在阻尼系數(shù)指定加權(quán)系數(shù)0.3。注:阻尼系數(shù)不是平滑常數(shù)(阻尼系數(shù)=1-平滑常數(shù)

)

在輸出選項(xiàng)框中指定輸出選項(xiàng)。本例選擇輸出區(qū)域，并指定輸出到當(dāng)前工作表以C2為左上角的單元格區(qū)域；選中圖表輸出復(fù)選框。單擊確定按鈕。

這時(shí)，Excel給出一次指數(shù)平滑值，如下圖所示。

43整理課件

從圖可以看出，鋼產(chǎn)量具有明顯的線性增長(zhǎng)趨勢(shì)。因此需使用二次指數(shù)平滑法，即在一次指數(shù)平滑的基礎(chǔ)上再進(jìn)行指數(shù)平滑。所得結(jié)果如下圖所示。

44整理課件45整理課件§3.Excel進(jìn)行趨勢(shì)外推預(yù)測(cè)法的操作步驟

統(tǒng)計(jì)資料表明,大量社會(huì)經(jīng)濟(jì)現(xiàn)象的發(fā)展主要是漸進(jìn)的,其發(fā)展相對(duì)于時(shí)間