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

1、1應(yīng)用應(yīng)用Excel進(jìn)行定量分析實驗進(jìn)行定量分析實驗主要內(nèi)容主要內(nèi)容v1 1、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析一元線性回歸：工具實現(xiàn)和函數(shù)實現(xiàn)一元線性回歸：工具實現(xiàn)和函數(shù)實現(xiàn)非線性回歸：對數(shù)函數(shù)和指數(shù)函數(shù)非線性回歸：對數(shù)函數(shù)和指數(shù)函數(shù)v2、用、用Excel進(jìn)行時間序列分析進(jìn)行時間序列分析移動平均及線性趨勢預(yù)測移動平均及線性趨勢預(yù)測指數(shù)平滑及指數(shù)平滑及線性趨勢預(yù)測線性趨勢預(yù)測時間序列綜合分解時間序列綜合分解v3、用、用Excel進(jìn)行層次分析進(jìn)行層次分析231、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析n一元線性回歸：工具實現(xiàn)一元線性回歸：工具實現(xiàn)1.1.打開打開EXCELEXCEL，在工具

2、一欄里找到，在工具一欄里找到“加載宏加載宏”，選，選中中“分析工具庫分析工具庫”，點擊，點擊“確定確定”；41、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析2.2.在在EXCELEXCEL里建立工作表；里建立工作表；3.3.選定數(shù)據(jù)區(qū)域，在選定數(shù)據(jù)區(qū)域，在“工具工具”欄里點擊欄里點擊“插入圖表插入圖表”，選擇選擇“XYXY散點圖散點圖”，點擊，點擊“完成完成”，出現(xiàn)如下圖所，出現(xiàn)如下圖所示情況；示情況；5674.4.鼠標(biāo)放到散點圖的點上，點變成黃色或綠色，鼠標(biāo)放到散點圖的點上，點變成黃色或綠色，點擊鼠標(biāo)右鍵，選擇點擊鼠標(biāo)右鍵，選擇“添加趨勢線添加趨勢線”; ;1、用、用Excel進(jìn)行回歸分析進(jìn)行回

3、歸分析81、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析5.5.在在“類型類型”里根據(jù)散點圖的形狀，選擇里根據(jù)散點圖的形狀，選擇一種類型，這里我們選擇一種類型，這里我們選擇“線性線性”；6.6.在在“選項選項”里選擇里選擇“顯示公式顯示公式”和和“顯顯示示R R2 2值值”，點，點“確定確定” ” 即可；即可；可以看到回歸方程及相關(guān)系數(shù)，把可以看到回歸方程及相關(guān)系數(shù)，把R R2 2 =0.98=0.98開方得開方得r0.9899r0.9899。9101、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析n一元線性回歸：函數(shù)實現(xiàn)一元線性回歸：函數(shù)實現(xiàn)1.1.打開打開EXCELEXCEL，在工具一欄里找到，在工

4、具一欄里找到“加載宏加載宏”，選中，選中“分析工具庫分析工具庫-VBA-VBA函數(shù)函數(shù)”，點擊，點擊“確定確定”；111、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析EXCELEXCEL回歸分析實現(xiàn)的主要函數(shù)：回歸分析實現(xiàn)的主要函數(shù)：（1 1）截距函數(shù)：）截距函數(shù)：INTERCEPT(yINTERCEPT(y，x)x)。（2 2）斜率函數(shù)：）斜率函數(shù)：SLOPE (ySLOPE (y，x)x)。 （3 3）測定系數(shù)函數(shù)）測定系數(shù)函數(shù)(R(R平方值平方值) )：RSQ(yRSQ(y，x)x)。（4 4）估計標(biāo)準(zhǔn)誤差函數(shù)：）估計標(biāo)準(zhǔn)誤差函數(shù)：STEYX(ySTEYX(y，x)x)。 121、用、用Ex

5、cel進(jìn)行回歸分析進(jìn)行回歸分析2.2.在在EXCELEXCEL里建立工作表；里建立工作表；131、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析3.3.用用ExcelExcel提供的函數(shù)進(jìn)行計算。提供的函數(shù)進(jìn)行計算。（1 1）在單元格）在單元格A12A12A15A15中分別輸入中分別輸入“截距截距”、“斜率斜率”、“測定系數(shù)測定系數(shù)”、“估計標(biāo)準(zhǔn)誤差估計標(biāo)準(zhǔn)誤差”。（2 2）在單元格）在單元格B12B12中輸入公式中輸入公式“=INTERCEPT(C2:C11,B2:B11)”“=INTERCEPT(C2:C11,B2:B11)”，回車后顯示，回車后顯示-79.42015-79.42015。（3 3

6、）在單元格）在單元格B13B13中輸入公式中輸入公式“=SLOPE(C2:C11,B2:B11)”“=SLOPE(C2:C11,B2:B11)”，回車后顯示回車后顯示0.80418250.8041825。（4 4）在單元格）在單元格B14B14中輸入公式中輸入公式“=RSQ(C2:C11,B2:B11)”“=RSQ(C2:C11,B2:B11)”，回，回車后顯示車后顯示0.68170180.6817018。（5 5）在單元格）在單元格B15B15中輸入公式中輸入公式“=STEYX(C2:C11,B2:B11)”“=STEYX(C2:C11,B2:B11)”，回車后顯示回車后顯示2.818073

7、82.8180738。計算結(jié)果如圖所示。計算結(jié)果如圖所示。141516171、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析非線性回歸分析：對數(shù)函數(shù)非線性回歸分析：對數(shù)函數(shù) y=a+b x1.1.打開打開EXCELEXCEL，建立工作表；，建立工作表；2.2.插入圖表插入圖表“散點圖散點圖”；3.3.在添加趨勢線中選擇對數(shù)并在選項中選擇顯示公式和在添加趨勢線中選擇對數(shù)并在選項中選擇顯示公式和R R值；值；181、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析4.4.進(jìn)行回歸分析：工具進(jìn)行回歸分析：工具- -數(shù)據(jù)分析數(shù)據(jù)分析- -回歸回歸 Y Y與與LnLn（X X）呈線性相關(guān)）呈線性相關(guān)191、用、用Ex

8、cel進(jìn)行回歸分析進(jìn)行回歸分析非線性回歸分析：指數(shù)函數(shù)非線性回歸分析：指數(shù)函數(shù) lny = lna + b x1.1.打開打開EXCELEXCEL，建立工作表；，建立工作表；2.2.插入圖表插入圖表“散點圖散點圖”；3.3.在添加趨勢線中選擇指數(shù)并在選項中選擇顯示公式和在添加趨勢線中選擇指數(shù)并在選項中選擇顯示公式和R R值；值；xey201、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析4.4.進(jìn)行回歸分析：工具進(jìn)行回歸分析：工具- -數(shù)據(jù)分析數(shù)據(jù)分析- -回歸回歸 LnLn（Y Y）與）與X X呈線性相關(guān)呈線性相關(guān)21有問題請舉手！有問題請舉手！22年份年份 銷售額銷售額 年份年份 銷售額銷售額

9、1998100.002006200.001999120.00 2007190.002000140.00 2008210.002001150.00 2009230.002002170.00 2010230.002003180.00 2011240.002004190.00 2012250.00 2005210.00 2013 ？移動平均及線性趨勢預(yù)測移動平均及線性趨勢預(yù)測E已知某商場已知某商場19982012年的年銷售額如下表所示，年的年銷售額如下表所示，試預(yù)測試預(yù)測2013年該商場的年銷售額（年該商場的年銷售額（n=3；n=4；n=5）。）。2. 用用EXCEL進(jìn)行時間序列分析進(jìn)行時間序列分析

10、232. 用用EXCEL進(jìn)行時間序列分析進(jìn)行時間序列分析移動平均及線性趨勢預(yù)測移動平均及線性趨勢預(yù)測1.1.打開打開EXCELEXCEL建立工作表；建立工作表；2.2.生成散點，分析和判斷時間序列有無趨勢生成散點，分析和判斷時間序列有無趨勢T T；3.3.由圖可知：商場的年銷售額有明顯的線性增長趨勢由圖可知：商場的年銷售額有明顯的線性增長趨勢T T。242. 用用EXCEL進(jìn)行時間序列分析進(jìn)行時間序列分析4.4.利用利用Excel Excel 進(jìn)行一次和二次移動平均：工具進(jìn)行一次和二次移動平均：工具- -數(shù)據(jù)分析數(shù)據(jù)分析- -移動平均。移動平均。5.5.建立移動平均線性預(yù)測模型。建立移動平均線

11、性預(yù)測模型。利用公式求利用公式求a at t和和b bt t的值，得出線性模型。的值，得出線性模型。28.24722.232240215a)(12)2()1(tttMMnbTbayttTt)2()1(2tttMMa)(12)2()1(tttMMnb)2()1(2tttMMa78. 7)22.232240(13215b06.255178. 728.24778. 728.247115120122013TyyyTbayttTt)2()1(2tttMMa)(12)2()1(tttMMnbTbayttTt)2()1(2tttMMa252. 用用EXCEL進(jìn)行時間序列分析進(jìn)行時間序列分析262728293

12、031 指數(shù)平滑及線性趨勢預(yù)測：指數(shù)平滑及線性趨勢預(yù)測：=0.3；=0.5；=0.71.1.打開打開EXCELEXCEL建立工作表；建立工作表；2.2.生成散點，分析和判斷時間序列有無趨勢生成散點，分析和判斷時間序列有無趨勢T T；3.3.由圖可知：商場的年銷售額有明顯的線性增長趨勢由圖可知：商場的年銷售額有明顯的線性增長趨勢T T。2. 用用EXCEL進(jìn)行時間序列分析進(jìn)行時間序列分析322. 用用EXCEL進(jìn)行時間序列分析進(jìn)行時間序列分析4.4.利用利用Excel Excel 進(jìn)行一次和二次指數(shù)平滑：工具進(jìn)行一次和二次指數(shù)平滑：工具- -數(shù)據(jù)分析數(shù)據(jù)分析- -移指數(shù)移指數(shù)平滑（阻尼系數(shù)）。平

13、滑（阻尼系數(shù)）。5.5.建立指數(shù)平滑線性預(yù)測模型。建立指數(shù)平滑線性預(yù)測模型。利用公式求利用公式求a at t和和b bt t的值，得出線性模型。的值，得出線性模型。)(1)2()1(tttSSb)2()1(2tttSSaTbayttTt)2()1(2tttSSaTbayttTt)(1)2()1(tttSSb)2()1(2tttSSaTbayttTt14. 5)33.22432.236(3 . 013 . 015b31.24833.22432.236215a45.253114. 531.24814. 531.2481152013Tyy33343536373839時間序列綜合分解及預(yù)測時間序列綜合

14、分解及預(yù)測時間序列綜合分解及預(yù)測時間序列綜合分解及預(yù)測假設(shè)時間序列存在季節(jié)、趨勢和不規(guī)則因素假設(shè)時間序列存在季節(jié)、趨勢和不規(guī)則因素yt = Tt St It計算季節(jié)指數(shù)計算季節(jié)指數(shù)St進(jìn)行移動平均或指數(shù)平滑進(jìn)行移動平均或指數(shù)平滑It進(jìn)行線性趨勢預(yù)測進(jìn)行線性趨勢預(yù)測Tt進(jìn)行綜合預(yù)測進(jìn)行綜合預(yù)測40原始時間序列原始時間序列41散點圖：時間序列因素判斷（散點圖：時間序列因素判斷（S、T、I）具有明顯的季節(jié)因素具有明顯的季節(jié)因素S；具有較為明顯的趨勢因素具有較為明顯的趨勢因素T；具有不太明顯的隨機因素具有不太明顯的隨機因素I。42求季節(jié)指數(shù)求季節(jié)指數(shù)4314. 13141. 1156. 1133. 1

15、09. 13109. 1075. 1096. 184. 03834. 0839. 0840. 093. 03908. 0918. 0971. 0第四季度季節(jié)指數(shù)第三季度季節(jié)指數(shù)第二季度季節(jié)指數(shù)第一季度季節(jié)指數(shù)44消除季節(jié)影響后的數(shù)據(jù)消除季節(jié)影響后的數(shù)據(jù)ttSY /45（1）建立趨勢）建立趨勢T：用消除季節(jié)后的銷量進(jìn)行回歸分析：用消除季節(jié)后的銷量進(jìn)行回歸分析趨勢線y = 0.1471x + 5.0996R2 = 0.920801234567805101520tTt147. 0100. 5用趨勢得到用趨勢得到1到到16期估計值期估計值46190.914.1061.8625.809.1913.752

16、3.684.0765.7084.793.0617.7016.8913.7765.7617.717148.0101.5201918181817171720191817FFSTFSTFTTTT季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測結(jié)果季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測結(jié)果47季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測結(jié)果季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測結(jié)果48綜合預(yù)測效果圖綜合預(yù)測效果圖49（2）建立趨勢）建立趨勢T：用移動平動建立線性趨勢：用移動平動建立線性趨勢)(12)2()1(tttMMnbTbayttTt)2()1(2tttMMa127. 0)96. 615. 7(142tbTyTt127. 034. 734. 796. 615. 72ta50趨勢值趨勢值T和綜合預(yù)測值和綜合預(yù)測值51綜合預(yù)測效果圖綜合預(yù)測效果圖52有問題請舉手！有問題請舉手！533. 用用EXCEL進(jìn)行層次分析進(jìn)行層次分析判斷矩陣計算判斷矩陣計算求特征向量求特征向量求最大特征根求最大特征根一致性檢驗一致性檢驗54判斷矩陣計算判斷矩陣計算1135131112513131211714155712334211A55（1）列歸一化）列歸一化（1）列歸一化）列歸一化依次用公式：依次用公式：=B2/SUM(B$2:B$6)56（2）行歸一化）行歸一化用求和函數(shù)：依次用公式用求和函數(shù)：依次用