應用Excel進行定量分析實驗

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

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

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

年份

銷售額

年份

銷售額

1998100.002006200.001999120.002007190.002000140.002008210.002001150.002009230.002002170.002010230.002003180.002011240.002004190.002012250.002005210.00

2013

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

St

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