EXCEL與數(shù)據(jù)分析.ppt

1,第六章 數(shù)據(jù)的變動 趨勢分析,授課教師：馬銀戌,2,本章主要教學內容,第一節(jié) 觀察數(shù)據(jù)的變動趨勢特征,第二節(jié) 時間序列的趨勢外推分析,第三節(jié) 時間序列的移動平均分析,第四節(jié) 時間序列的指數(shù)平滑分析,3,第一節(jié) 觀察數(shù)據(jù)的變動趨勢特征,一、從數(shù)據(jù)看未來 二、時間序列分析的一般步驟 三、時間序列的預處理 四、時間序列的描述分析,4,一、從數(shù)據(jù)看未來,如果認為事物過去的變化規(guī)律將會持續(xù)到未來，未來將是事物過去變化規(guī)律的延伸，那么，我們就可以利用目前已經掌握的那些反映事物過去發(fā)展特征的數(shù)據(jù)，預見事物未來的發(fā)展趨勢。從數(shù)據(jù)看未來的意義正在于此。 采用科學的數(shù)量分析方法，從數(shù)據(jù)出發(fā)預見事物的未來發(fā)展趨勢，是我們實現(xiàn)分析目標的有效途徑。時間序列分析方法正是一種基于事物過去的數(shù)據(jù)資料，尋找其隨著時間推移的變化規(guī)律，從而建立時間序列模型以預測事物未來發(fā)展的數(shù)值特征的有效預測方法。,5,從數(shù)據(jù)看未來的基礎是數(shù)據(jù)，而這些數(shù)據(jù)是具有時間特征的。我們將那些依時間先后順序排列起來的一系列有相同內涵的數(shù)據(jù)稱之為時間序列。 時間序列的構成要素有兩個,現(xiàn)象所屬的時間,現(xiàn)象在不同時間上的數(shù)值,時間序列的變量值是由眾多復雜因素共同作用的結果，影響時間序列的因素按其性質和作用，可歸納為以下四類：長期趨勢( T ) 、季節(jié)變動( S ) 、循環(huán)變動( C )、不規(guī)則變動( I ) 時間序列是這四部分的綜合影響結果。時間序列的分析任務就是要正確確定時間序列的性質，對構成時間序列的各種因素加以分解，再分別測定其對時間序列變動的影響。 本課程主要講述長期趨勢的測定及Excel實現(xiàn)。,6,二、時間序列分析的一般步驟,1、收集歷史資料，編制時間序列，并將數(shù)據(jù)以恰當?shù)母袷浇M織在Excel中； 2、繪制統(tǒng)計圖，初步觀察數(shù)據(jù)的基本特征； 3、對時間序列進行分析，尋找事物隨時間變化而呈現(xiàn)的特征和規(guī)律； 4、根據(jù)時間序列的特征和分析的要求，選擇恰當?shù)哪Ｐ瓦M行數(shù)據(jù)建模，并對模型進行檢驗和評價，以求獲得最合適的模型； 5、利用模型進行分析和預測。,7,時間序列的預處理通常包括數(shù)據(jù)準備、數(shù)據(jù)的圖形化觀察、數(shù)據(jù)加工等主要工作。它是時間序列分析建模的基礎。 1、數(shù)據(jù)準備 數(shù)據(jù)準備是時間序列分析的首要工作，其主要目標是要根據(jù)分析目的收集數(shù)據(jù)，并將數(shù)據(jù)以恰當?shù)母袷浇M織在Excel中，一般將數(shù)據(jù)按時間先后順序按列錄入即可。,三、時間序列的預處理,8,2、圖形化觀察數(shù)據(jù) 圖形化觀察數(shù)據(jù)的目的是對時間序列發(fā)展變化特征有一個初步的總體把握，它是今后選擇恰當模型、對數(shù)據(jù)進行深入分析的前提。 通過圖形化觀察，可以對現(xiàn)象進行基本特征的描述分析；還可以把握時間序列的諸多特征，包括：時間序列的總體趨勢是上升或下降，是否存在有規(guī)律的上下波動；時間序列的變化是否具有周期性特點；時間序列的波動幅度是否存在規(guī)律性；時間序列是否存在異常值，不同時間點上的數(shù)據(jù)是否可能存在一定關系等。,9,時間序列的圖形化觀察和分析，可以通過柱形圖和折線圖反映現(xiàn)象的基本特征和變動趨勢，并利用圖形對事物進行基本描述分析。,柱形圖的制作及時間序列的基本描述分析：,（1）點擊“插入” 點擊“圖表”，出現(xiàn)下面的復選框： (或直接在Excel表上方的工具欄中點擊圖表向導： ),10,（2）選擇“柱形圖”，單擊“下一步”，選擇系列產生在行或列； （3）在“數(shù)據(jù)區(qū)域”后面的空白框中輸入要分析的數(shù)據(jù)區(qū)域單元格地址；,（4）單擊“系列”項，在“分類（X）軸標志”空白框中指定時間所在的單元格地址；,11,（5）單擊“下一步”，出現(xiàn)下面對話框，需根據(jù)內容和需要填寫圖表標題、分類（X）軸的標題、數(shù)值（Y）軸的標題；,（6）單擊“下一步”，選擇圖表位置；然后單擊“完成”。 （7）進一步編輯圖表，以符合分析的要求，還可以添加趨勢線，并顯示趨勢方程。,12,13,添加趨勢線：,右擊需要添加趨勢線的柱形圖，選擇“添加趨勢線”，出現(xiàn)“添加趨勢線”的復選框，可根據(jù)柱形圖的基本形狀選擇所要添加的趨勢線類型，然后單擊“確定”。,還可以在“選項”中選擇“顯示公式”，即可在圖中顯示出所添加的趨勢線的方程式。,14,15,折線圖的制作及時間序列的趨勢特征觀察：,（1）點擊“插入” 點擊“圖表”，出現(xiàn)下面的復選框： (或直接在Excel表上方的工具欄中點擊圖表向導： ),（2）選擇“折線圖”，并選擇“子圖表類型”；,16,（3）單擊“下一步”，出現(xiàn)下面對話框，在“數(shù)據(jù)區(qū)域”后面的空白框中輸入要分析的數(shù)據(jù)區(qū)域單元格地址；,（4）單擊“系列”項，在“分類（X）軸標志”空白框中指定時間所在的單元格地址；,17,（5）單擊“下一步”，出現(xiàn)下面對話框，需根據(jù)內容和需要填寫圖表標題、分類（X）軸的標題、數(shù)值（Y）軸的標題；,（6）單擊“下一步”，選擇圖表位置；然后單擊“完成”。 （7）利用折線圖進行基本描述分析，或通過觀察折線圖的形狀為下一步建立模型做準備。,18,趨勢線的主要類型及判斷：,線性：線性趨勢線適合于穩(wěn)定增加或減少的數(shù)據(jù)值。如果數(shù)據(jù)點構成的圖案類似一條直線，或數(shù)據(jù)的一次增長量（逐期增長量）為常數(shù)或接近一常數(shù)，則表明數(shù)據(jù)為線性趨勢。,對數(shù)：對數(shù)趨勢線適合于初期快速增加或減少然后趨于穩(wěn)定的數(shù)據(jù)值。對于對數(shù)趨勢線，數(shù)據(jù)可以包含負數(shù)和正數(shù)。,多項式：多項式趨勢線適合于表示波動較大的數(shù)據(jù)值。當需要分析大量數(shù)據(jù)的偏差時，可以使用多項式趨勢線。,19,乘冪：乘冪趨勢線適合于顯示以特定速率增加的數(shù)據(jù)值。對于乘冪趨勢線，數(shù)據(jù)不可以包含負數(shù)和零值。 指數(shù)：指數(shù)曲線適合于顯示環(huán)比發(fā)展速度接近常數(shù)的時間序列。,移動平均：移動平均趨勢線使用彎曲趨勢線顯示數(shù)據(jù)值，同時平滑數(shù)據(jù)波動，這樣可以更清晰地顯示圖案或趨勢。,實際中還可以對多項數(shù)據(jù)的時間序列進行比較分析：,可以對多個時間序列在同一圖中作出多條折線圖，或者可以作簇型柱狀圖，然后對多個事物的發(fā)展趨勢做比較分析。,20,21,3、原始數(shù)據(jù)的再加工 原始數(shù)據(jù)的再加工是根據(jù)研究任務的要求，將所搜集的大量原始資料，進行進一步的整理和計算新的指標數(shù)據(jù)等，以得到能滿足分析要求的數(shù)據(jù)資料。 在Excel中，可通過自行編輯公式進行各種計算，也可利用有關的函數(shù)公式自動計算。,22,對于時間序列，可以利用增長量、平均增長量、增長速度、平均增長速度等描述性指標進行描述分析。,四、時間序列的描述分析,1、增長量,增長量 = 報告期水平 基期水平,在Excel中可以通過自己編寫公式計算。,2、平均增長量,23,平均增長量的計算可利用各個逐期增長量通過插入AVERAGE函數(shù)計算。,3、增長速度,發(fā)展速度和增長速度在Excel中可通過自己編寫公式計算。,24,4、平均增長速度,平均增長速度 = 平均發(fā)展速度 1,平均發(fā)展速度：,平均發(fā)展速度在EXCEL中可通過插入GEOMEAN函數(shù)計算。,對河北省裝備制造業(yè)的基本描述分析：,25,第二節(jié) 時間序列的趨勢外推分析,一、線性趨勢的外推分析 二、非線性趨勢的外推分析,26,趨勢外推是事物發(fā)展?jié)u進過程的一種主要預測方法，是時間序列分析中的傳統(tǒng)分析方法。它認為事物的內在發(fā)展規(guī)律能夠體現(xiàn)在數(shù)據(jù)當中，可從時間序列的數(shù)據(jù)本身提煉出其發(fā)展的規(guī)律性，并假定根據(jù)過去資料建立的趨勢外推模型能夠適合未來，然后通過趨勢的外推對未來進行預測。 趨勢外推分析方法是基于回歸分析原理的。它將所要研究的時間序列觀測值序列看做回歸模型的因變量，將表式時間順序的序列作為模型的自變量。自變量可以沒有實際意義，僅表示數(shù)據(jù)點的先后順序，也可以有實際意義，如表示年份或月份等，但這些并不代表絕對數(shù)量，而只是先后順序的一種表示。,27,一、線性趨勢的外推分析,由于時間序列隨時間推移可能呈現(xiàn)出線性趨勢或非線性趨勢，因此，趨勢外推分析中的模型也分為線性趨勢外推模型和非線性趨勢外推模型。,如果一個時間序列的散點圖呈現(xiàn)直線型分布或時間序列的逐期增長量（一級增長量）相對穩(wěn)定，為一常數(shù)或接近常數(shù)，可利用線性趨勢外推模型進行擬合。,線性趨勢外推模型的理論形式為：,式中，a為趨勢線在縱軸上的截距，是時間t為0時y的趨勢預測值；b為趨勢線的斜率，表示時間t變動一個單位引起的時間序列觀測值的平均變動量。,28,式中，a為趨勢線在縱軸上的截距，是時間t為0時y的趨勢預測值；b為趨勢線的斜率，表示時間t變動一個單位引起的時間序列觀測值的平均變動量。,線性趨勢外推分析在Excel中的實現(xiàn)方法：,方法一：利用圖表加趨勢線實現(xiàn),方法二：利用線性函數(shù)LINEST實現(xiàn),方法三：利用“數(shù)據(jù)分析”的“回歸”功能實現(xiàn),29,方法一：利用圖表加趨勢線實現(xiàn),（1）錄入時間序列數(shù)據(jù)； （2）點擊“插入” 點擊“圖表”； （3）選擇圖表類型，一般選折線圖或xy散點圖； （4）填寫圖表標題等，選擇圖表輸出位置，完成圖表； （5）右擊圖表，選擇“添加趨勢線”，然后在趨勢線類型中選擇“線性”，在“選項”中選擇“顯示公式”和“顯示R平方值”，然后單擊“確定”，即可得到線性模型及R方值。 （6）要預測某個時間的數(shù)值，在空白單元格中按照已得到的線性模型自行編輯公式，并將t 值代入即可。 案例：根據(jù)江南某市2009年7月2011年12月的出口額資料，采用線性模型測定其長期趨勢，并預測2012年3月份的出口額。,30,方法二：利用線性函數(shù)LINEST實現(xiàn),（1）打開已錄好的時間序列數(shù)據(jù)； （2）在E1單元格中輸入“回歸統(tǒng)計量”； （3）選擇E2:F6，單擊“插入”單擊“函數(shù)”，出現(xiàn)如下對話框：,（4）在類別中選擇“統(tǒng)計”，在“選擇函數(shù)”中選擇“LINEST”函數(shù)，單擊“確定”，出現(xiàn)下面的對話框：,31,（5）在“Known_ys”中輸入因變量y值所在的單元格地址；在“Known_xs”中輸入自變量x值（即序號t）所在的單元格地址；在“Stats”中輸入“1”； （6）按住Ctrl+Shift組合鍵，單擊“確定”，即可得到計算結果在E2：F6。,32,33,根據(jù)Excel的輸出結果，趨勢方程為：,該趨勢方程的判定系數(shù)為0.8792，即時間變量可以解釋出口額的的總變差的87.92%，因此，該趨勢模型非常顯著。利用該模型可進行預測。 預測方法： （1）在E8單元格中輸入要 “日期”，在F8單元格中輸入“出口額預測值”，在E9單元格中輸入要預測的日期“2012年3月”； （2）在F9單元格中輸入公式“=F2+E2*33” （3）按下Enter鍵，即出預測結果：11175.932,34,方法三：利用“數(shù)據(jù)分析”的“回歸”功能實現(xiàn),（1）打開已錄好的時間序列數(shù)據(jù)； （2）選擇“工具”菜單中的“數(shù)據(jù)分析”子菜單，在“分析工具”的復選框中選擇“回歸”；,（3）單擊“確定”，出現(xiàn)如下對話框：,35,（4）給出因變量Y和自變量X的數(shù)據(jù)所在的單元格區(qū)域，在“輸出選項”中選擇輸出的位置，同時選擇“線性擬合圖”；,（5）單擊“確定”，輸出回歸結果。,36,37,對輸出結果中主要數(shù)據(jù)的解釋： （結合某市出口額的案例）,輸出結果包括“SUMMARY OUTPUT”（摘要輸出）和“RESIDUAL OUTPUT”（殘差輸出）兩部分以及線性擬合圖。,（1）“Multiple R”是時間t和因變量出口額Y之間的簡單相關系數(shù) r ，等于0.9376，表明兩者間高度線性正相關； （2）“R Square”是判定系數(shù)R2 ,等于0.8792。判定系數(shù)R2是測定直線回歸模型擬合優(yōu)度的一個重要指標，其意義同相關系數(shù) r具有一致性 。計算結果表明，出口額的總誤差中有87.92%可以由出口額隨時間而變動的依存關系來解釋，因此這條回歸直線的擬合優(yōu)度很好；,38,（3）F檢驗和t檢驗的P值均大于a（0.05），線性回歸模型檢驗通過；,（4）“Coefficients”下面的兩個數(shù)是回歸方程的兩個參數(shù)值，其中，截距 a = 4893.698，回歸系數(shù)（斜率） b =190.3707；據(jù)此，可寫出樣本回歸方程：,上述趨勢方程表明該市出口額存在著明顯的上升趨勢，且時間每增加一個月，出口額平均增加190.3707萬元；據(jù)此可利用模型進行預測。 預測方法通過編輯公式計算：“=F17+F18*33” ，預測結果同樣為11175.932萬元。,39,二、非線性趨勢的外推分析,如果一個時間序列隨著時間的推移呈現(xiàn)出非線性變動趨勢，則可利用非線性趨勢外推模型進行擬合。,在趨勢外推分析的發(fā)展過程中，人們根據(jù)事物的發(fā)展規(guī)律歸納出了可概括各種時間序列發(fā)展非線性規(guī)律的曲線模型，這里主要介紹幾種常用的、可在Excel中擬合的非線性趨勢模型，包括二次曲線、對數(shù)曲線和指數(shù)曲線。 非線性趨勢的外推分析在Excel中的實現(xiàn)主要是通過插入圖表，通過折線圖或xy散點圖，然后添加趨勢線來顯示時間序列的變動規(guī)律的。,40,（一）二次曲線趨勢的外推分析,二次曲線（拋物線）是多項式曲線的一種，是一種常見的曲線形式，如果時間序列中現(xiàn)象的二級增長量為常數(shù)，可配合二次曲線趨勢方程。,二次曲線的趨勢方程為：,在Excel中的實現(xiàn)是通過插入圖表并添加趨勢線實現(xiàn)的。,案例：根據(jù)某化肥廠歷年的產量進行趨勢分析，并預測其2012年的產量。,41,（二）對數(shù)曲線趨勢的外推分析,對數(shù)曲線也是一種常見的曲線形式，如果時間序列的初期快速增加或減少然后趨于穩(wěn)定，則可配合對數(shù)曲線趨勢方程。,對數(shù)曲線的趨勢方程為：,在Excel中的實現(xiàn)是通過插入圖表并添加趨勢線實現(xiàn)的。,案例：根據(jù)某紡織廠歷年的銷售額進行趨勢分析，并預測其2012年的產量。,42,（三）指數(shù)增長曲線趨勢的外推分析,指數(shù)增長曲線適合于顯示環(huán)比發(fā)展速度接近常數(shù)的時間序列。如果時間序列中現(xiàn)象的環(huán)比發(fā)展速度為常數(shù)，可配合指數(shù)增長曲線趨勢方程。,指數(shù)增長曲線的趨勢方程為：,在Excel中的實現(xiàn)是通過插入圖表并添加趨勢線實現(xiàn)的。,案例：根據(jù)我國歷年的轎車產量進行趨勢分析，并預測其2012年的產量。,43,時間序列的趨勢外推分析方法主要是用于中長期的預測分析。應注意到，趨勢外推分析中模型的選擇是以人們對事物發(fā)展規(guī)律的充分認知為基礎的，是建立在長期積累的經驗模型基礎上的。雖然長期的實踐人們已經總結出了大量的可借用的經驗模型，但現(xiàn)實中事物的變化是紛繁復雜的。同時，由于模型選擇上存在較強的主觀性，這可能將產生因模型選擇的差異性而導致的對同樣的數(shù)據(jù)作出不同的分析結論。因此，在時間序列的分析中，選擇最優(yōu)模型是至關重要的。 模型優(yōu)劣的評價標準通常是模型對數(shù)據(jù)點的擬合程度。擬合程度越高，模型效果越好。通常采用判定系數(shù)作為擬合優(yōu)度高低的判斷工具。判定系數(shù)越大，擬合優(yōu)度越高。,44,第三節(jié) 時間序列的移動平均分析,一、移動平均分析的基本思想 二、利用Excel實現(xiàn)簡單移動平均,45,一、移動平均分析的基本思想,移動平均法是從時間序列中，取最近期的n個觀測值計算其平均數(shù)，作為下一期的預測值。在連續(xù)計算平均數(shù)時，每當?shù)玫揭粋€新的觀測值，就把它計入平均數(shù)中，并去掉最早期的觀測值，使平均數(shù)成為一個隨時間而移動的平均數(shù)。,設時間序列各期的觀測值順次為：,設時間序列的移動間隔為k，則第t+1期的預測值為第t期的平均值，計算公式為：,46,二、利用Excel實現(xiàn)簡單移動平均分析,（1）打開已錄好的時間序列數(shù)據(jù)； （2）選擇“工具”菜單中的“數(shù)據(jù)分析”子菜單，在“分析工具”的復選框中選擇“移動平均”；,（3）單擊“確定”，出現(xiàn)如下對話框：,47,（4）在“輸入區(qū)域”框中輸入時間序列觀測值所在的單元格地址，如果包含時間序列的標題行，則選擇“標志位于第一行”。 （5）在“間隔”框中輸入簡單移動平均的時間間隔 k ，如本例指定為5。,48,（6）在“輸出選項”中選擇輸出的位置，同時選擇“圖表輸出”，然后單擊“確定”完成。,*最后一期的移動平均數(shù)即可作為下一年的預測值。,*簡單移動平均法主要適合于平穩(wěn)型的時間序列。,案例：根據(jù)我國歷年的人口性別比進行移動平均分析，并預測2011年的人口性別比。,49,第四節(jié) 時間序列的指數(shù)平滑分析,一、指數(shù)平滑分析的基本思路 二、利用Excel實現(xiàn)指數(shù)平滑分析,50,一、指數(shù)平滑分析的基本思路,指數(shù)平滑法是移動平均法的改良方法，它是用時間序列過去值的加權平均數(shù)來預測未來的值，并且通過權數(shù)大小體現(xiàn)事物發(fā)展不同時期與現(xiàn)實聯(lián)系的緊密程度。同時，指數(shù)平滑法對權數(shù)的處理簡單易行，因而在實際中應用廣泛。 指數(shù)平滑分析因權數(shù)的選擇和平滑方法不同而分成多種模型形式，包括一次指數(shù)平滑模型、二次指數(shù)平滑模型、三次指數(shù)平滑模型。 利用excel實現(xiàn)的主要是一次指數(shù)平滑模型。,51,一次指數(shù)平滑預測法是用近期預測誤差的一部分調整平滑統(tǒng)計量，即用本期的預測值，加上本期預測誤差乘一個加權系數(shù)，得到本期的平滑值，作為下一期的預測值。計算公式為：,52,平滑系數(shù)和阻尼系數(shù)的取值均介于01之間，平滑系數(shù)越大，表明預測結果對實際觀測值的變動反應越敏感，但預測會變得不穩(wěn)定，平滑系數(shù)較小將導致預測值的滯后；一般平滑系數(shù)介于0.2 0.3之間，表明應將當前預測值調整20% 30%以修正以前的預測。阻尼系數(shù)反映利用本期實際值的信息的程度，一般介于0.7 0.8之間。 對于一個時間序列來說，客觀上存在一個最佳阻尼系數(shù)，使得實際值與預測值誤差最小，通?？赏ㄟ^設置多個阻尼系數(shù)進行試算，從而找到一個實際值與預測值誤差平方和最小的阻尼系數(shù)作為最佳阻尼系數(shù)。,53,對于第一期的預測值的計算方法，可以采用第一期的實際