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

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

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

4、具一欄里找到“加載宏加載宏”，選中，選中“分析工具庫(kù)分析工具庫(kù)-VBA-VBA函數(shù)函數(shù)”，點(diǎn)擊，點(diǎn)擊“確定確定”；111、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析EXCELEXCEL回歸分析實(shí)現(xiàn)的主要函數(shù)：回歸分析實(shí)現(xiàn)的主要函數(shù)：（1 1）截距函數(shù)：）截距函數(shù)：INTERCEPT(yINTERCEPT(y，x)x)。（2 2）斜率函數(shù)：）斜率函數(shù)：SLOPE (ySLOPE (y，x)x)。 （3 3）測(cè)定系數(shù)函數(shù)）測(cè)定系數(shù)函數(shù)(R(R平方值平方值) )：RSQ(yRSQ(y，x)x)。（4 4）估計(jì)標(biāo)準(zhǔn)誤差函數(shù)：）估計(jì)標(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)行計(jì)算。提供的函數(shù)進(jìn)行計(jì)算。（1 1）在單元格）在單元格A12A12A15A15中分別輸入中分別輸入“截距截距”、“斜率斜率”、“測(cè)定系數(shù)測(cè)定系數(shù)”、“估計(jì)標(biāo)準(zhǔn)誤差估計(jì)標(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。計(jì)算結(jié)果如圖所示。計(jì)算結(jié)果如圖所示。141516171、用、用Excel進(jìn)行回歸分析進(jìn)行回歸分析非線性回歸分析：對(duì)數(shù)函數(shù)非線性回歸分析：對(duì)數(shù)函數(shù) y=a+b x1.1.打開(kāi)打開(kāi)EXCELEXCEL，建立工作表；，建立工作表；2.2.插入圖表插入圖表“散點(diǎn)圖散點(diǎn)圖”；3.3.在添加趨勢(shì)線中選擇對(duì)數(shù)并在選項(xiàng)中選擇顯示公式和在添加趨勢(shì)線中選擇對(duì)數(shù)并在選項(xiàng)中選擇顯示公式和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.打開(kāi)打開(kāi)EXCELEXCEL，建立工作表；，建立工作表；2.2.插入圖表插入圖表“散點(diǎn)圖散點(diǎn)圖”；3.3.在添加趨勢(shì)線中選擇指數(shù)并在選項(xiàng)中選擇顯示公式和在添加趨勢(shì)線中選擇指數(shù)并在選項(xiàng)中選擇顯示公式和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有問(wèn)題請(qǐng)舉手！有問(wèn)題請(qǐng)舉手！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 ？移動(dòng)平均及線性趨勢(shì)預(yù)測(cè)移動(dòng)平均及線性趨勢(shì)預(yù)測(cè)E已知某商場(chǎng)已知某商場(chǎng)19982012年的年銷售額如下表所示，年的年銷售額如下表所示，試預(yù)測(cè)試預(yù)測(cè)2013年該商場(chǎng)的年銷售額（年該商場(chǎng)的年銷售額（n=3；n=4；n=5）。）。2. 用用EXCEL進(jìn)行時(shí)間序列分析進(jìn)行時(shí)間序列分析

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

11、性預(yù)測(cè)模型。利用公式求利用公式求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)行時(shí)間序列分析進(jìn)行時(shí)間序列分析262728293

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

13、滑（阻尼系數(shù)）。5.5.建立指數(shù)平滑線性預(yù)測(cè)模型。建立指數(shù)平滑線性預(yù)測(cè)模型。利用公式求利用公式求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時(shí)間序列綜合分解及預(yù)測(cè)時(shí)間序列綜合

14、分解及預(yù)測(cè)時(shí)間序列綜合分解及預(yù)測(cè)時(shí)間序列綜合分解及預(yù)測(cè)假設(shè)時(shí)間序列存在季節(jié)、趨勢(shì)和不規(guī)則因素假設(shè)時(shí)間序列存在季節(jié)、趨勢(shì)和不規(guī)則因素yt = Tt St It計(jì)算季節(jié)指數(shù)計(jì)算季節(jié)指數(shù)St進(jìn)行移動(dòng)平均或指數(shù)平滑進(jìn)行移動(dòng)平均或指數(shù)平滑It進(jìn)行線性趨勢(shì)預(yù)測(cè)進(jìn)行線性趨勢(shì)預(yù)測(cè)Tt進(jìn)行綜合預(yù)測(cè)進(jìn)行綜合預(yù)測(cè)40原始時(shí)間序列原始時(shí)間序列41散點(diǎn)圖：時(shí)間序列因素判斷（散點(diǎn)圖：時(shí)間序列因素判斷（S、T、I）具有明顯的季節(jié)因素具有明顯的季節(jié)因素S；具有較為明顯的趨勢(shì)因素具有較為明顯的趨勢(shì)因素T；具有不太明顯的隨機(jī)因素具有不太明顯的隨機(jī)因素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）建立趨勢(shì)）建立趨勢(shì)T：用消除季節(jié)后的銷量進(jìn)行回歸分析：用消除季節(jié)后的銷量進(jìn)行回歸分析趨勢(shì)線y = 0.1471x + 5.0996R2 = 0.920801234567805101520tTt147. 0100. 5用趨勢(shì)得到用趨勢(shì)得到1到到16期估計(jì)值期估計(jì)值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ù)測(cè)結(jié)果季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測(cè)結(jié)果47季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測(cè)結(jié)果季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測(cè)結(jié)果48綜合預(yù)測(cè)效果圖綜合預(yù)測(cè)效果圖49（2）建立趨勢(shì)）建立趨勢(shì)T：用移動(dòng)平動(dòng)建立線性趨勢(shì)：用移動(dòng)平動(dòng)建立線性趨勢(shì))(12)2()1(tttMMnbTbayttTt)2()1(2tttMMa127. 0)96. 615. 7(142tbTyTt127. 034. 734. 796. 615. 72ta50趨勢(shì)值趨勢(shì)值T和綜合預(yù)測(cè)值和綜合預(yù)測(cè)值51綜合預(yù)測(cè)效果圖綜合預(yù)測(cè)效果圖52有問(wèn)題請(qǐng)舉手！有問(wèn)題請(qǐng)舉手！533. 用用EXCEL進(jìn)行層次分析進(jìn)行層次分析判斷矩陣計(jì)算判斷矩陣計(jì)算求特征向量求特征向量求最大特征根求最大特征根一致性檢驗(yàn)一致性檢驗(yàn)54判斷矩陣計(jì)算判斷矩陣計(jì)算1135131112513131211714155712334211A55（1）列歸一化）列歸一化（1）列歸一化）列歸一化依次用公式：依次用公式：=B2/SUM(B$2:B$6)56（2）行歸一化）行歸一化用求和函數(shù)：依次用公式用求和函數(shù)：依次用