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

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

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

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

年份

銷售額

年份

銷售額

1998100.002006200.001999120.002007190.002000140.002008210.002001150.002009230.002002170.002010230.002003180.002011240.002004190.002012250.002005210.00

2013

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

St

It計(jì)算季節(jié)指數(shù)St進(jìn)行移動(dòng)平均或指數(shù)平滑It進(jìn)行線性趨勢(shì)預(yù)測(cè)Tt進(jìn)行綜合預(yù)測(cè)39原始時(shí)間序列40散點(diǎn)圖：時(shí)間序列因素判斷（S、T、I）具有明顯的季節(jié)因素S；具有較為明顯的趨勢(shì)因素T；具有不太明顯的隨機(jī)因素I。41求季節(jié)指數(shù)4243消除季節(jié)影響后的數(shù)據(jù)44（1）建立趨勢(shì)T：用消除季節(jié)后的銷量進(jìn)行回歸分析用趨勢(shì)得到1到16期估計(jì)值45季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測(cè)結(jié)果46季節(jié)指數(shù)調(diào)整后得到綜合預(yù)測(cè)結(jié)果47綜合預(yù)測(cè)效果圖48（2）建立趨勢(shì)T：用移動(dòng)平動(dòng)建立線性趨勢(shì)49趨勢(shì)值T和綜合預(yù)測(cè)值50綜合預(yù)測(cè)效果圖51有問(wèn)題請(qǐng)舉手！523.用EXCEL進(jìn)行層次分析判斷矩陣計(jì)算—求特征向量—求最大特征根—一致性檢驗(yàn)53判斷矩陣計(jì)算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，