應(yīng)用Excel進行定量分析實驗

應(yīng)用Excel進行定量分析實驗1主要內(nèi)容1、用Excel進行回歸分析一元線性回歸：工具實現(xiàn)和函數(shù)實現(xiàn)非線性回歸：對數(shù)函數(shù)和指數(shù)函數(shù)2、用Excel進行時間序列分析移動平均及線性趨勢預(yù)測指數(shù)平滑及線性趨勢預(yù)測時間序列綜合分解3、用Excel進行層次分析21、用Excel進行回歸分析一元線性回歸：工具實現(xiàn)1.打開EXCEL，在工具一欄里找到“加載宏”，選中“分析工具庫”，點擊“確定”；31、用Excel進行回歸分析2.在EXCEL里建立工作表；3.選定數(shù)據(jù)區(qū)域，在“工具”欄里點擊“插入圖表”，選擇“XY散點圖”，點擊“完成”，出現(xiàn)如下圖所示情況；4564.鼠標(biāo)放到散點圖的點上，點變成黃色或綠色，點擊鼠標(biāo)右鍵，選擇“添加趨勢線”;1、用Excel進行回歸分析71、用Excel進行回歸分析5.在“類型”里根據(jù)散點圖的形狀，選擇一種類型，這里我們選擇“線性”；6.在“選項”里選擇“顯示公式”和“顯示R2值”，點“確定”即可；可以看到回歸方程及相關(guān)系數(shù)，把R2=0.98開方得r≈0.9899。891、用Excel進行回歸分析一元線性回歸：函數(shù)實現(xiàn)1.打開EXCEL，在工具一欄里找到“加載宏”，選中“分析工具庫-VBA函數(shù)”，點擊“確定”；101、用Excel進行回歸分析EXCEL回歸分析實現(xiàn)的主要函數(shù)：（1）截距函數(shù)：INTERCEPT(y，x)。（2）斜率函數(shù)：SLOPE(y，x)。（3）測定系數(shù)函數(shù)(R平方值)：RSQ(y，x)。（4）估計標(biāo)準誤差函數(shù)：STEYX(y，x)。111、用Excel進行回歸分析2.在EXCEL里建立工作表；121、用Excel進行回歸分析3.用Excel提供的函數(shù)進行計算。（1）在單元格A12～A15中分別輸入“截距”、“斜率”、“測定系數(shù)”、“估計標(biāo)準誤差”。（2）在單元格B12中輸入公式“=INTERCEPT(C2:C11,B2:B11)”，回車后顯示-79.42015。（3）在單元格B13中輸入公式“=SLOPE(C2:C11,B2:B11)”，回車后顯示0.8041825。（4）在單元格B14中輸入公式“=RSQ(C2:C11,B2:B11)”，回車后顯示0.6817018。（5）在單元格B15中輸入公式“=STEYX(C2:C11,B2:B11)”，回車后顯示2.8180738。計算結(jié)果如圖所示。131415161、用Excel進行回歸分析非線性回歸分析：對數(shù)函數(shù)y=a+b·㏒x1.打開EXCEL，建立工作表；2.插入圖表“散點圖”；3.在添加趨勢線中選擇對數(shù)并在選項中選擇顯示公式和R值；171、用Excel進行回歸分析4.進行回歸分析：工具-數(shù)據(jù)分析-回歸

Y與Ln（X）呈線性相關(guān)181、用Excel進行回歸分析非線性回歸分析：指數(shù)函數(shù)lny=lna+bx1.打開EXCEL，建立工作表；2.插入圖表“散點圖”；3.在添加趨勢線中選擇指數(shù)并在選項中選擇顯示公式和R值；191、用Excel進行回歸分析4.進行回歸分析：工具-數(shù)據(jù)分析-回歸

Ln（Y）與X呈線性相關(guān)20有問題請舉手！21

年份

銷售額

年份

銷售額

1998100.002006200.001999120.002007190.002000140.002008210.002001150.002009230.002002170.002010230.002003180.002011240.002004190.002012250.002005210.00

2013

？移動平均及線性趨勢預(yù)測已知某商場1998～2012年的年銷售額如下表所示，試預(yù)測2013年該商場的年銷售額（n=3；n=4；n=5）。2.用EXCEL進行時間序列分析222.用EXCEL進行時間序列分析移動平均及線性趨勢預(yù)測1.打開EXCEL建立工作表；2.生成散點，分析和判斷時間序列有無趨勢T；3.由圖可知：商場的年銷售額有明顯的線性增長趨勢T。232.用EXCEL進行時間序列分析4.利用Excel進行一次和二次移動平均：工具-數(shù)據(jù)分析-移動平均。5.建立移動平均線性預(yù)測模型。利用公式求at和bt的值，得出線性模型。242.用EXCEL進行時間序列分析252627282930指數(shù)平滑及線性趨勢預(yù)測：α=0.3；α=0.5；α=0.71.打開EXCEL建立工作表；2.生成散點，分析和判斷時間序列有無趨勢T；3.由圖可知：商場的年銷售額有明顯的線性增長趨勢T。2.用EXCEL進行時間序列分析312.用EXCEL進行時間序列分析4.利用Excel進行一次和二次指數(shù)平滑：工具-數(shù)據(jù)分析-移指數(shù)平滑（阻尼系數(shù)）。5.建立指數(shù)平滑線性預(yù)測模型。利用公式求at和bt的值，得出線性模型。32333435363738時間序列綜合分解及預(yù)測時間序列綜合分解及預(yù)測假設(shè)時間序列存在季節(jié)、趨勢和不規(guī)則因素yt=Tt

St

It計算季節(jié)指數(shù)St進行移動平均或指數(shù)平滑It進行線性趨勢預(yù)測Tt進行綜合預(yù)測39原始時間序列40散點圖：時間序列因素判斷（S、T、I）具有明顯的季節(jié)因素S；具有較為明顯的趨勢因素T；具有不太明顯的隨機因素I。41求季節(jié)指數(shù)4243消除季節(jié)影響后的數(shù)據(jù)44（1）建立趨勢T：用消除季節(jié)后的銷量進行回歸分析用趨勢得到1到16期估計值45季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測結(jié)果46季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測結(jié)果47綜合預(yù)測效果圖48（2）建立趨勢T：用移動平動建立線性趨勢49趨勢值T和綜合預(yù)測值50綜合預(yù)測效果圖51有問題請舉手！523.用EXCEL進行層次分析判斷矩陣計算—求特征向量—求最大特征根—一致性檢驗53判斷矩陣計算54（1）列歸一化（1）列歸一化依次用公式：=B2/SUM(B$2:B$6)55（2）行歸一化用求和函數(shù)：依次用公式=sum(B8:F8)=H8/SUM(H$8:H$12)（3）得到特征向量W＝（0.255，0.460，0.053，