實驗一：移動平均

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