實驗一：移動平均

實驗一：移動平均法一、實驗目的PartA:一次移動平均法SEQListNum\r1\*mergeformat1熟悉數據文件的建立；SEQListNum\*mergeformat2熟悉時間序列數據散點圖的繪制及其一次移動平均法適用條件的判斷；SEQListNum\*mergeformat3熟悉應用一次移動平均法進行相應預測；SEQListNum\*mergeformat4熟悉一次移動平均法預測精度的分析及其最優(yōu)移動步長N確實定；PartB:二次移動平均法SEQListNum\r1\*mergeformat1根據時間序列數據散點圖，熟悉二次移動平均法適用條件的判斷；SEQListNum\*mergeformat2熟悉應用二次移動平均法進行相應預測；SEQListNum\*mergeformat3熟悉二次移動平均法預測精度的分析及其最優(yōu)移動步長N確實定；二、實驗內容及實驗過程PartA問題描述某汽油批發(fā)商在過去12周的汽油銷售量統(tǒng)計數據如表1所示。表1：汽油銷售量數據單位：千加侖周123456789101112銷售量172119231820221822201722建立Excel數據文件，試分析統(tǒng)計數據，選擇適宜的模型來估計下周汽油銷售量。取移動步長N=3，采用一次移動平均法進行預測，并分析其預測精度。如何選擇適宜的移動步長，使預測精度較高？實驗過程Excel的數據處理除了提供很多的函數外，另外還有兩個重要的功能：數據分析和規(guī)劃求解。但是這兩個工具必須加載相應的宏后才能夠使用。具體步驟為點擊Excel【工具】下的子菜單【加載宏】，出現如圖的對話框，選擇分析工具庫和規(guī)劃求解，點擊確定后，在工具菜單欄下面就有了這兩個分析工具了。步驟1：新建Excel文檔，輸入數據。啟動Excel，翻開Excel的一個工作簿，用鼠標選中某個單元格，就可以輸入數據了，本實驗數據如圖。步驟2：繪制散點圖。點擊Excel【插入】菜單下的【圖表】子菜單。在【標準類】選項卡“圖表類型〞欄選擇“XY散點圖〞，點擊[下一步]按鈕〔圖3-1〕?！皵祿^(qū)域〞選擇數據所在的單元格區(qū)域“A2:B13”，產生系列選擇“列〞，如圖。點擊[完成]按鈕，得到銷售量與時間的散點圖，如圖步驟3：選擇預測模型。從該汽油批發(fā)商在過去12周汽油銷售量散點圖可以看出，汽油銷售量根本在20千加侖上下波動，無長期變化趨勢，無季節(jié)影響，因而可以使用一次移動平均法進行相應預測。步驟4：計算一次移動平均值和預測值。方法1：公式法1〕選擇步長為3，在單元格“C4”中輸入“=〔B2+B3+B4〕/3”或者插入函數AVERAGE，即“=AVERAGE〔B2:B42〕回車，并將單元格“C4”的內容復制到單元格區(qū)域“C5:C13”〔選中單元格“C4”，按下Ctrl鍵，拖動鼠標至C13〕，并在單元格區(qū)域C4:C13的內容復制到D5:D14區(qū)域〔在單元格D5，輸入“=C4”，并將其內容復制到區(qū)域D6:D14〕，方法2：一次移動平均預測模塊法Excel軟件的數據分析工具提供了簡單方便的一次移動平均預測模塊。1〕點擊Excel【工具】菜單下面的子菜單【數據分析】，翻開“數據分析〞對話框，從“分析工具〞列表中選擇“移動平均〞，如圖，點擊[確定]按鈕。2〕在“移動平均〞對話框“輸入區(qū)域〞選擇原始數據所在的單元格區(qū)域“B2:B13”，“間隔〞中輸入“3”〔表示步長為3〕，“輸出區(qū)域〞選擇單元格“C2”，如圖同公式法，將單元格區(qū)域C4:C13的內容復制到D5:D14，即得一次移動平均值的預測值，如圖。步驟5：計算預測誤差、誤差平方。在單元格“D5〞和“E5〞分別輸入“=B5－C5”、“=D5^2”，并將單元格區(qū)域“D5:E5”的內容復制到單元格區(qū)域“D6:E步驟6：計算平均誤差平方和。在單元格E14輸入“MSE=〞，單元格F14輸入或插入AVERAGE函數，即“=AVERAGE(E5:E13)〞，如圖。可見當步長為3時，預測誤差平方和為5.6296296，下一期的預測值為19.6666667。步驟7：最優(yōu)步長確實定。首先輸入原始數據，周次和銷售量。單元格C1、D1、E1分別輸入“預測值〞、“預測誤差〞、“誤差平方〞。將單元格H1設定為移動步長，輸入任一值。在單元格G2輸入“MSE=〞，單元格H2那么為預測的誤差均方，即H1中設定的步長所對應于MSE的值，輸入“=AVERAGE(E($H$1+1):E13)。在單元格C3中輸入=IF(A3<$H$1,"",AVERAGE(OFFSET(C4,-$H$1,-1,$H$1,1)))在單元格D3中輸入=IF(C3="","",B3-C3)在單元格E3中輸入=IF(C3="","",D3^2)將單元格區(qū)域C3:E3的內容復制到單元格C13:E13。其中IF()函數判斷，例如單元格C3，表示A3(周次)是否小于H1(移動步長)，如果是，那么C3為空白，即這一周沒有預測值，如果是否，就是該周對應的預測值，用AVERAGE函數表示，其參數范圍由OFFSET函數確定。OFFSET(reference,rows,cols,height,width)函數是以指定的范圍為參照系，通過給定偏移量得到新的范圍。各參數定義為：Reference是偏移量參照系的引用區(qū)域；Rows是相對于偏移量參照系的向上〔用負數表示〕或向下〔用正數表示〕偏移的行數；Cols相對于偏移量參照系的向左〔用負數表示〕或向右〔用正數表示〕偏移的列數；Height高度，即所要返回的引用區(qū)域的行數；Width寬度，即所要返回的引用區(qū)域的列數?？勺円苿硬介L的設置。將單元格H1設置為可變的移動步長微調項。點擊Excel菜單【視圖——【工具欄】——【窗體】，再點擊“微調項〞用鼠標拖到控制區(qū)H1，出現微調項，在微調項上點擊鼠標右鍵，在彈出的快捷菜單上選擇“設置控件格式〞，如圖。進行參數設計，本實現移動步長確定為1-6，在最小值欄輸入“1〞,在最大值欄輸入“6〞,在步長欄輸入“1〞,單元格鏈接欄選擇為步長所在位置“$H$1改變步長，即可得到對應的預測值、預測誤差、誤差平方和誤差均方的值，例如當步長等于6時的結果如圖。此時MSE為4.9398148。變動步長，可得不同的MSE，選擇最小MSE對應的步長那么為最優(yōu)步長。本實驗為當N=5時，MSE最小。當然，也可以采用規(guī)劃求解的方法計算最優(yōu)步長。規(guī)劃求解方法后續(xù)實驗中將分析，在此不再闡述。PartB問題描述某企業(yè)在過去1-12月份，其彩電銷售量統(tǒng)計數據如表2所示。表2：彩電量數據單位：臺月份123456789101112銷售量468478486495488512523508513528536550〔1〕試分析統(tǒng)計數據，選擇適宜的模型來估計下月該產品的銷售量?！?〕取移動步長N=3，采用二次移動平均法進行預測，并分析其預測精度。實驗過程步驟1：輸入數據，繪制過去1-12月彩電銷售量的“XY散點圖〞，如圖。從散點圖可以看出，彩電銷售量根本呈現線性增長趨勢，因此可以使用二次指數平滑法進行預測。步驟2：設置步長N=3，應用公式法計算各期的一次移動平均值和二次移動平均值，如圖。步驟3：計算線性趨勢的截距和斜率at和bt，在單元格G1和H1分別輸入“A〞、“B〞。單元格G2輸入截距公式，即“=2*C13－D13”，單元格H2輸入斜率計算公式，即“=2*(C13－D13)/〔3－1〕〞，結果如圖步驟4：計算預測值。在單元格E2輸入“=$G