【文章內(nèi)容簡介】 域”輸入“b2:b21”單元格，“阻尼系數(shù)”輸入“”（注：阻尼系數(shù)=1平滑常數(shù)），在“輸出區(qū)域”輸入“c2”單元格，單擊“確定”按鈕。二、運(yùn)用指數(shù)平滑公式進(jìn)行預(yù)測步驟1：，在單元格c2中輸入公式：“=b2”，作為第一年的預(yù)測值（F1），在單元格c3中輸入指數(shù)平滑模型預(yù)測公式“=$f$1*b2+(1$f$1)*c2”。將單元格c3往下復(fù)制。步驟2：繪制指數(shù)平滑預(yù)測圖。利用單元格a2:c22中的數(shù)據(jù)繪制公司銷售量指數(shù)平滑預(yù)測圖。三、尋找最優(yōu)的平滑常數(shù)步驟1：計(jì)算均方誤差。單元格f2中輸入公式：“=average((b2:b21c2:c21)^2)”，作為數(shù)組運(yùn)算，需要同時按Ctrl+Shift+Enter三個鍵作為輸入結(jié)束，計(jì)算均方誤差MSE。步驟2：利用模擬運(yùn)算表及查找引用函數(shù)功能，尋找最優(yōu)平滑常數(shù)。單元格e7:e24中給出不同的平滑常數(shù)（大于0小于1），在單元格f6中輸入公式：“=f2”，選定單元格e6:f24，在“數(shù)據(jù)”菜單中選擇“模擬運(yùn)算表”。 在單元格f4中輸入公式：“=index(e7:e24,match(min(f7:f24),f7:f24,0))”。然后，（將此值代入單元格f1中）。步驟3：利用規(guī)劃求解功能，尋找最優(yōu)平滑常數(shù)。在“文件”選項(xiàng)卡中選擇“選項(xiàng)”，將打開“Excel選項(xiàng)”對話框，在該對話框的左邊一欄里選擇“加載項(xiàng)”，右邊一欄的最下方單擊“轉(zhuǎn)到”按鈕，Excel將顯示“加載宏”對話框。在 “加載宏”對話框中選擇“規(guī)劃求解加載項(xiàng)”，單擊“確定”按鈕，將會在“數(shù)據(jù)”選項(xiàng)卡下方的“分析”組中出現(xiàn)“規(guī)劃求解”選項(xiàng)，選中“規(guī)劃求解”。 在設(shè)置目標(biāo)欄輸入$f$2,然后點(diǎn)擊“求解”按鈕。實(shí)驗(yàn)32 “美食佳”公司月管理費(fèi)預(yù)測實(shí)驗(yàn)?zāi)康模? 理解移動平均預(yù)測法的概念；? 掌握在excel中建立移動平均模型的方法；? 掌握尋找最優(yōu)移動平均跨度的各種方法。實(shí)驗(yàn)步驟：一、運(yùn)用“數(shù)據(jù)分析”工具進(jìn)行移動平均預(yù)測步驟1：確定時間序列的類型。繪制公司從2006年1月至2007年6月共18個月的管理費(fèi)用折線圖，步驟2：利用“數(shù)據(jù)分析”工具的移動平均功能進(jìn)行預(yù)測。在“數(shù)據(jù)”選項(xiàng)卡中選擇“數(shù)據(jù)分析”，在出現(xiàn)的“數(shù)據(jù)分析”對話框中選擇“移動平均”，在“移動平均”對話框中，在“輸入?yún)^(qū)域”輸入“c2:c19”單元格，“間隔”輸入“3”（注：移動平均跨度為3），在“輸出區(qū)域”輸入“d3”單元格，單擊“確定”按鈕。二、運(yùn)用移動平均公式進(jìn)行預(yù)測步驟1：利用average()函數(shù)計(jì)算移動平均預(yù)測值。在單元格g1中輸入移動平均跨度3，在單元格d5中輸入移動平均模型預(yù)測公式：“=average(c2:c4)”。將單元格d5往下復(fù)制。步驟2：繪制移動平均預(yù)測圖。利用單元格c2:d20中的數(shù)據(jù)繪制公司18個月的管理費(fèi)用及移動平均預(yù)測圖。三、尋找最優(yōu)的移動平均跨度步驟1：計(jì)算均方誤差。此處用到兩個函數(shù)：sumxmy2()函數(shù)和count()函數(shù)。sumxmy2()函數(shù)的功能是返回兩數(shù)組中對應(yīng)數(shù)值之差的平方和，它需要兩個參數(shù)，一個參數(shù)是第一個數(shù)組或數(shù)值區(qū)域，另一個參數(shù)是第二個數(shù)組或數(shù)值區(qū)域。count()函數(shù)的功能是計(jì)算某一范圍內(nèi)包含數(shù)值的單元格的個數(shù)。在單元格g2中輸入公式：“=sumxmy2(c2:c19,d2:d19)/count(d2:d19)”，計(jì)算均方誤差MSE。步驟2：利用offset()函數(shù)輔助進(jìn)行不同移動平均跨度下的預(yù)測。借助average()函數(shù)進(jìn)行的移動平均計(jì)算僅對跨度3有效，若跨度改為其他值，則要修改average()函數(shù)的參數(shù)。為此引入offset()函數(shù)解決average()函數(shù)的參數(shù)范圍變化問題。offset()函數(shù)的功能是以指定的范圍為參照系，通過給定偏移量得到新的范圍。返回（求出）的范圍可以為一個單元格或單元格區(qū)域，并可以指定返回的行數(shù)或列數(shù)。它需要五個參數(shù)，第一個參數(shù)是作為參照系的基準(zhǔn)位置；第二個參數(shù)是相對于這個基準(zhǔn)位置向上（用負(fù)數(shù)表示）或向下（用正數(shù)表示）偏移的行數(shù)；第三個參數(shù)是相對于這個基準(zhǔn)位置向左（用負(fù)數(shù)表示）或向右（用正數(shù)表示）偏移的列數(shù)；第四個參數(shù)是要返回?cái)?shù)據(jù)范圍的行數(shù)；第五個參數(shù)是要返回?cái)?shù)據(jù)范圍的列數(shù)。事實(shí)上前三個參數(shù)指定了要返回?cái)?shù)據(jù)范圍的起始單元格。在單元格d5中輸入公式：“=if(a5=$g$1,average(offset(d5,$g$1,1,$g$1,1)))”，拖動單元格d5的填充柄向上復(fù)制至d2，向下復(fù)制至d20，從而可在變化的移動平均跨度下計(jì)算移動平均值。將單元格g1中的移動平均跨度改為2，不必改動d列的公式，步驟3：利用模擬運(yùn)算表及查找引用函數(shù)功能，尋找最優(yōu)移動平均跨度。在單元格f7:f21給出不同的移動平均跨度，在單元格g6中給出公式：“=g2”，選定單元格f6:g21，在“數(shù)據(jù)”菜單中選擇“模擬運(yùn)算表”，在單元格g4中輸入公式：“=index(f7:f15,match(min(g7:g15),g7:g15,0))”，找到最優(yōu)移動平均跨度為5。實(shí)驗(yàn)思考1. 可否利用規(guī)劃求解功能，尋找最優(yōu)的移動平均跨度？答：在實(shí)驗(yàn)32中，無法利用規(guī)劃求解功能尋找最優(yōu)的移動平均刻度。因?yàn)榍驧SE所用的公式為“=SUMXMY2(C2:C19,D2:D19)/COUNT(D2:D19)”與移動平均刻度值所在的G1單元格無直接聯(lián)系2. excel提供的移動平均趨勢線功能也可進(jìn)行移動平均預(yù)測，但趨勢線方法與本實(shí)驗(yàn)所介紹的方法有何不同？答：Excel提供的移動平均趨勢線方法與本實(shí)驗(yàn)所介紹的方法與本實(shí)驗(yàn)所介紹方法的區(qū)別在于趨勢線的作用是對已知的一堆數(shù)據(jù)作回歸分析，以找到一個可以直接計(jì)算的方程式并對其他任意未經(jīng)測量的數(shù)值進(jìn)行計(jì)算。趨勢線方法考慮了大量可能的結(jié)果。實(shí)驗(yàn)33 “美食佳”華東分公司銷售額趨勢預(yù)測實(shí)驗(yàn)?zāi)康? 理解趨勢預(yù)測法的概念；? 掌握在excel中建立線性趨勢預(yù)測模型的方法；? 掌握尋找線性趨勢模型參數(shù)的各種方法；? 掌握線性趨勢值預(yù)測的不同方法步驟1：確定時間序列的類型。步驟2：添加線性趨勢線。在圖中選中數(shù)據(jù)系列，右鍵菜單中選擇“添加趨勢線”，出現(xiàn)“添加趨勢線”對話框。在“添加趨勢線”對話框的“類型”中選擇“線性”。在“添加趨勢線”對話框的“選項(xiàng)”中選擇“顯示公式”和“顯示r平方值”。步驟3：用趨勢線前推法大致預(yù)測線性趨勢值。選定線性趨勢線，右鍵菜單中選擇“趨勢線格式”，在“趨勢線格式”對話框中選定“選項(xiàng)”，將趨勢預(yù)測前推1周期。步驟4：用方程或函數(shù)準(zhǔn)確預(yù)測線性趨勢值。根據(jù)得到的線性趨勢方程公式y(tǒng)=+，如圖328所示，在單元格c13中輸入公式：“=*a13+”，即將x=12（2007年為第12個時間序列點(diǎn)）代入公式。利用forecast()函數(shù)，在單元格c14中輸入公式：“=forecast(a13,c2:c12,a2:a12)”。利用trend()函數(shù)，在單元格c15中輸入公式：“=trend(c2:c12,a2:a12,a13)”。步驟5：將預(yù)測結(jié)果在圖中表示。同時選中單元格b13和c13，并作為新數(shù)據(jù)點(diǎn)復(fù)制到圖形的數(shù)據(jù)線上，趨勢線前推只能在圖中看到大致預(yù)測結(jié)果1000左右，實(shí)驗(yàn)思考1．本實(shí)驗(yàn)的幾張圖中，x軸是“分類”還是“自動”？本實(shí)驗(yàn)（實(shí)驗(yàn)33）中，X軸是自動2．預(yù)測點(diǎn)數(shù)據(jù)如果作為新數(shù)據(jù)系列添加到圖形中，結(jié)果與圖329有何不同？答：實(shí)驗(yàn)33中，預(yù)測點(diǎn)數(shù)據(jù)如果作為新數(shù)據(jù)系列添加到圖形中，預(yù)測部分的值將是一條直線。3．為什么預(yù)測值一定在趨勢線的延伸線上？答：預(yù)測值一定在趨勢線上的原因是預(yù)測值是依據(jù)趨勢線作出來的。4．若要預(yù)測公司2008年的全國銷售額，可以怎么做？若要預(yù)測公司2009年、2010年、甚至更遠(yuǎn)年份的銷售額，會有什么問題？答：若要預(yù)測2008年的全國銷售額，可依據(jù)2007年的預(yù)測值來作。但若要預(yù)測更遠(yuǎn)年份的銷售額，則不能以之為基礎(chǔ)由趨勢線函數(shù)進(jìn)行預(yù)測，因?yàn)楸藭r銷售額呈線性增長，與客觀事實(shí)不符。5．除了本實(shí)驗(yàn)中介紹的添加趨勢線方法可以找到線性趨勢預(yù)測模型的參數(shù)外，還可以用哪些方法找到線性趨勢預(yù)測模型y=a+bx中的參數(shù) a和b。答：還可用回歸方法找到Y(jié)=a+bX中參數(shù)a,b的值。實(shí)驗(yàn)34 “美食佳”公司會員卡發(fā)行量趨勢預(yù)測實(shí)驗(yàn)類型：驗(yàn)證性 實(shí)驗(yàn)學(xué)時：2實(shí)驗(yàn)?zāi)康模? 理解非線性趨勢預(yù)測法的概念；? 掌握在excel中建立非線性趨勢預(yù)測模型的方法；? 掌握非線性趨勢值預(yù)測的方法。? 預(yù)測公司2007年7月會員卡的發(fā)行量。實(shí)驗(yàn)步驟：步驟1：確定時間序列的類型。步驟2：添加非線性趨勢線在圖中選中數(shù)據(jù)系列，右鍵菜單中選擇“添加趨勢線”，出現(xiàn)“添加趨勢線”對話框。在“添加趨勢線”對話框的“類型”中選擇“對數(shù)”。在“添加趨勢線”對話框的“選項(xiàng)”中選中“顯示公式”和“顯示r平方值”步驟3：趨勢線前推法大致預(yù)測非線性趨勢值。選定對數(shù)趨勢線，右鍵菜單中選擇“趨勢線格式”，在“趨勢線格式”對話框中選定“選項(xiàng)”。將趨勢預(yù)測前推1周期。步驟4：用方程或函數(shù)準(zhǔn)確預(yù)測非線性趨勢值。根據(jù)得到的方程公式y(tǒng)=(x)+，在單元格c16中輸入公式：“=*ln(a16)+”，即將x=15（2007年7月為第15個時間序列點(diǎn)）代入公式。步驟5：將預(yù)測結(jié)果在圖中表示。趨勢線前推只能在圖中看到大致預(yù)測結(jié)果，實(shí)驗(yàn)思考1．請?jiān)囈幌?，圖339可否考慮用xy散點(diǎn)圖做？ 這時用什么數(shù)據(jù)作為x軸合適？ 答：可以，用序號的對數(shù)值ln(x)代替x，作為x軸合適，y與x是一次線性函數(shù)關(guān)系。2．為什么預(yù)測值一定在趨勢線的延伸線上？ 答：預(yù)測值一定在趨勢線上的原因是預(yù)測值是依據(jù)趨勢線作出來的。3．除了本實(shí)驗(yàn)中介紹的添加趨勢線方法可以找到對數(shù)趨勢預(yù)測模型的參數(shù)外，是否可以用規(guī)劃求解法找到對數(shù)趨勢預(yù)測模型y=a+bln(x)中的參數(shù)a和b？ 答：還可用回歸方法找到Y(jié)=a+bX中參數(shù)a,b的值。實(shí)驗(yàn)35 “美食佳”火鍋連鎖店原料年度采購成本預(yù)測實(shí)驗(yàn)?zāi)康模? 理解季節(jié)指數(shù)的概念；? 掌握季節(jié)指數(shù)預(yù)測方法。實(shí)驗(yàn)步驟：步驟1：確定時間序列的類型。步驟