實驗二 用EXCEL進(jìn)行相關(guān)與回歸分析

1、實驗二 用EXCEL進(jìn)行相關(guān)與回歸分析實驗?zāi)康模河肊XCEL進(jìn)行相關(guān)與回歸分析實驗內(nèi)容：1、 相關(guān)分析2、回歸分析實驗步驟：采用下面的例子進(jìn)行相關(guān)和回歸分析。【例1】 10個學(xué)生身高和體重的情況如下表，要求對身高和體重作相關(guān)和回歸分析。學(xué)生身高（公分）體重（公斤）12345678910171167177154169175163152172160535664495566524758501. 用EXCEL進(jìn)行相關(guān)分析首先把有關(guān)數(shù)據(jù)輸入EXCEL的單元格中，如圖1-20所示。用EXCEL進(jìn)行相關(guān)分析有散點圖、計算相關(guān)系數(shù)，另一種是利用相關(guān)分析宏。圖1-20 EXCEL數(shù)據(jù)集（1）作散點圖（2）利用函

2、數(shù)計算相關(guān)系數(shù)在EXCEL中，提供了兩個計算兩個變量之間相關(guān)系數(shù)的方法，CORREL函數(shù)和PERSON函數(shù)，這兩個函數(shù)是等價的，這里介紹用CORREL函數(shù)計算相關(guān)系數(shù)：第一步：單擊任一個空白單元格，單擊插入菜單，選擇函數(shù)選項，打開粘貼函數(shù)對話框，在函數(shù)分類中選擇統(tǒng)計，在函數(shù)名中選擇CORREL，單擊確定后，出現(xiàn)CORREL對話框。第二步：在array1中輸入B2：B11，在array2中輸入C2：C11，即可在對話框下方顯示出計算結(jié)果為0.896，如圖1-21所示。圖1-21 CORREL對話框及輸入結(jié)果（3）用相關(guān)系數(shù)宏計算相關(guān)系數(shù)第一步：單擊工具菜單，選擇數(shù)據(jù)分析選項，在數(shù)據(jù)分析選項中選擇

3、相關(guān)系數(shù)，彈出相關(guān)系數(shù)對話框，如圖1-22所示：圖1-22 相關(guān)系數(shù)對話框第二步：在輸入?yún)^(qū)域輸入$B$1:$C$1，分組方式選擇逐列，選擇標(biāo)志位于第一行，在輸出區(qū)域中輸入$E$1，單擊確定，得輸出結(jié)果如圖1-23所示。圖1-23 相關(guān)分析輸出結(jié)果在上面的輸出結(jié)果中，身高和體重的自相關(guān)系數(shù)均為1，身高和體重的相關(guān)系數(shù)為0.896，和用函數(shù)計算的結(jié)果完全相同。2. 用EXCEL進(jìn)行回歸分析EXCEL進(jìn)行回歸分析同樣分函數(shù)和回歸分析宏兩種形式，其提供了9個函數(shù)用于建立回歸模型和預(yù)測。這9個函數(shù)分別是：INTERCEPT返回線性回歸模型的截距SLOPE返回線性回歸模型的斜率RSQ返回線性回歸模型的判定

4、系數(shù)FORECAST返回一元線性回歸模型的預(yù)測值STEYX計算估計的標(biāo)準(zhǔn)誤TREND計算線性回歸線的趨勢值GROWTH返回指數(shù)曲線的趨勢值LINEST返回線性回歸模型的參數(shù)LOGEST返回指數(shù)曲線模型的參數(shù)用函數(shù)進(jìn)行回歸分析比較麻煩，我們這里介紹使用回歸分析宏進(jìn)行回歸分析。第一步：單擊工具菜單，選擇數(shù)據(jù)分析選項，出現(xiàn)數(shù)據(jù)分析對話框，在分析工具中選擇回歸，如圖1-24所示。圖1-24 數(shù)據(jù)分析對話框第二步：單擊確定按鈕，彈出回歸對話框，在Y值輸入?yún)^(qū)域輸入$B$2：$B$11，在X值輸入?yún)^(qū)域輸入$C$2：$C$11，在輸出選項選擇新工作表組，如圖1-25所示。圖1-25 回歸對話框第三步：單擊確定

5、按鈕，得回歸分析結(jié)果如圖1-26所示。圖1-26 EXCEL回歸分析結(jié)果在上面的輸出結(jié)果中，第一部分為匯總統(tǒng)計，MultipleR指復(fù)相關(guān)系數(shù)，RSquare指判定系數(shù)，Adjusted指調(diào)整的判定系數(shù)，標(biāo)準(zhǔn)誤差指估計的標(biāo)準(zhǔn)誤，觀測值指樣本容量；第二部分為方差分析，df指自由度，SS指平方和，MS指均方，F(xiàn)指F統(tǒng)計量，Significance of F指p值；第三部分包括：Intercept指截距，Coefficient指系數(shù)，tstat指t統(tǒng)計量。實驗三 用EXCEL進(jìn)行預(yù)測實驗?zāi)康模河肊XCEL進(jìn)行預(yù)測實驗內(nèi)容：一、用移動平均法進(jìn)行預(yù)測二、用指數(shù)平滑法進(jìn)行預(yù)測三、趨勢預(yù)測法進(jìn)行預(yù)測實驗步驟

6、：【例13-13】：某煤礦某年1-11月份采煤量如下表：月份產(chǎn)量月份產(chǎn)量1234569.039.069.128.738.949.307891011129.159.369.459.309.24一、用移動平均法進(jìn)行預(yù)測具體步驟：第一步：將原始數(shù)據(jù)錄入到單元格區(qū)域A2：A12，如圖13-31所示：圖13-31EXCEL數(shù)據(jù)集第二步：選擇菜單條上的“工具”“數(shù)據(jù)分析”命令，彈出如圖13-32所示的對話框：圖13-32數(shù)據(jù)分析對話框第三步：在“分析工具”框中選擇“移動平均”，單擊“確定”按鈕，彈出移動平均對話框，相應(yīng)作如下輸入，即可得到如圖13-33所示的對話框：（1） 在“輸出區(qū)域”內(nèi)輸入：$A$2：

7、$A$12，即原始數(shù)據(jù)所在的單元格區(qū)域。（2） 在“間隔”內(nèi)輸入：3，表示使用三步移動平均法。（3） 在“輸出區(qū)域”內(nèi)輸入：B2，即將輸出區(qū)域的左上角單元格定義為B2。（4） 選擇“圖表輸出”復(fù)選框和“標(biāo)準(zhǔn)誤差”復(fù)選框。13-33移動平均對話框第四步：單擊“確定”按鈕，便可得到移動平均結(jié)果，如圖13-34所示：分析：在圖中，B4：B12對應(yīng)的數(shù)據(jù)即為三步移動平均的預(yù)測值；單元格區(qū)域C6：C12即為標(biāo)準(zhǔn)誤差。13-34移動平均分析結(jié)果二、用指數(shù)平滑法進(jìn)行預(yù)測：第一步：將原始數(shù)據(jù)輸入到單元格B2：B12；第二步：選擇菜單條上的“工具”“數(shù)據(jù)分析”命令，彈出如圖13-35所示的對話框：圖13-35數(shù)

8、據(jù)分析對話框第三步：在“分析工具”中選擇“指數(shù)平滑”，單擊“確定”按鈕，彈出一個對話框，作相應(yīng)輸入，即可得到如圖13-36所示的對話框；13-36指數(shù)平滑對話框第四步：單擊“確定”按鈕，即可得到指數(shù)平滑結(jié)果，如圖13-37所示：圖13-37指數(shù)平滑結(jié)果三、趨勢預(yù)測法進(jìn)行預(yù)測第一步：把相關(guān)數(shù)據(jù)輸入到EXCEL中，其中月份輸入A1A11單元格，月產(chǎn)量輸入B1B11單元格，如圖13-38所示：圖13-38EXCEL數(shù)據(jù)集第二步：在工作表中選擇一個空的單元格。在這里我們選擇D2單元格。第三步：選擇插入下拉菜單。第四步：選擇函數(shù)選項。第五步：當(dāng)函數(shù)對話框出現(xiàn)時：在函數(shù)類別框中選擇統(tǒng)計，在函數(shù)名字中選擇F

9、ORECAST(預(yù)測)，如圖13-39所示：圖13-39粘貼函數(shù)對話框第六步：單擊確定按鈕，出現(xiàn)預(yù)測對話框，在x中輸入12，在know-ys中輸入B1：B11，在know-xs中輸入A1：A11，如圖13-40所示：圖13-40FORCAST對話框第七步：單擊確定按鈕，預(yù)測結(jié)果出現(xiàn)在D2單元格中，如圖13-41所示：圖13-41趨勢預(yù)測法預(yù)測結(jié)果實驗四 用EXCEL進(jìn)行時間序列分析實驗?zāi)康模河肊XCEL進(jìn)行時間序列分析實驗步驟：一、測定增長量和平均增長量【例13-5】：根據(jù)1995-2001年河北省國內(nèi)生產(chǎn)總值，計算逐期增長量、累計增長量和平均增長量。如圖13-16圖13-16用EXCEL計算

10、增長量和平均增長量資料及結(jié)果計算步驟如下：第一步：在A列輸入年份，在B列輸入國內(nèi)生產(chǎn)總值。第二步：計算逐期增長量：在C3中輸入公式：=B3-B2，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C8區(qū)域。第三步：計算累計增長量：在D3中輸入公式：=B3-$B$2，并用鼠標(biāo)拖曳公式復(fù)制到D3：D8區(qū)域。第四步：計算平均增長量（水平法）：在C10中輸入公式：=（B8-B2）/5，按回車鍵，即可得到平均增長量。二、測定發(fā)展速度和平均發(fā)展速度【例13-6】：以1995-2001年河北省國內(nèi)生產(chǎn)總值為例，說明如何計算定基發(fā)展速度、環(huán)比發(fā)展速度和平均發(fā)展速度。如圖13-17圖13-17用EXCEL計算發(fā)展速度和平均發(fā)展速度

11、資料及結(jié)果第一步：在A列輸入年份，在B列輸入國內(nèi)生產(chǎn)總值。第二步：計算定基發(fā)展速度：在C3中輸入公式：=B3/$B$2，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C8區(qū)域。第三步：計算環(huán)比發(fā)展速度：在D3中輸入公式：=B3/B2，并用鼠標(biāo)拖曳將公式復(fù)制到D3：D8區(qū)域。第四步：計算平均發(fā)展速度（水平法）：選中C10單元格，單擊插入菜單，選擇函數(shù)選項，出現(xiàn)插入函數(shù)對話框后，選擇GEOMEAN（返回幾何平均值）函數(shù)，在數(shù)值區(qū)域中輸入D3：D8即可。三、計算長期趨勢【例13-7】：我們用某企業(yè)某年12個的總產(chǎn)值資料來說明如何用移動平均法計算長期趨勢。如圖13-18圖13-18用EXCEL計算長期趨勢資料及結(jié)果第

12、一步：在A列輸入月份，在B列輸入總產(chǎn)值。第二步：計算三項移動平均：在C3中輸入“=（B2+B3+B4）/3”，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C12區(qū)域。第三步：計算四項移動平均：在D4中輸入“=SUM（B2：B5）/4”，并用鼠標(biāo)拖曳將公式復(fù)制到D4：D12區(qū)域。第四步：計算二項移正平均數(shù)：在E4中輸入“=（D4+D5）/2”，并用公式拖曳將公式復(fù)制到E4：E11區(qū)域。四、計算季節(jié)變動【例13-8】：利用某種商品五年分季度的銷售額資料，說明如何用移動平均趨勢剔除法測定季節(jié)變動。如圖13-19圖13-19用EXCEL計算季節(jié)變動資料第一步：按圖上的格式在A列輸入年份，在B列輸入季別，在C列輸入銷

13、售收入。第二步：計算四項移動平均：在D3中輸入“=SUM（C2：C4）/4”，并用鼠標(biāo)拖曳將公式復(fù)制到D3：D19區(qū)域。第三步：計算趨勢值（即二項移動平均）T：在E4中輸入“=（D3+D4）/2”，并用鼠標(biāo)拖曳將公式復(fù)制到E4：E19區(qū)域。第四步：剔除長期趨勢，即計算Y/T：在F4中輸入“=C4/E4”，并用鼠標(biāo)拖曳將公式復(fù)制到F4：F19區(qū)域。第五步：重新排列F4：F19區(qū)域中的數(shù)字，使同季的數(shù)字位于一列，共排成四列。第六步：計算各年同季平均數(shù)：在B29單元格中輸入公式：=average(B25:B28)；在C29中輸入公式=average(C25:C28)；在D29中輸入公式=averag

14、e(D24:27)；在E29中輸入公式=average(E24:E27)。第七步：計算調(diào)整系數(shù)：在B31中輸入公式：=4/sum(B29:E29)第八步：計算季節(jié)比率：在B30中輸入公式：=B29*$B$31，并用鼠標(biāo)拖曳將公式復(fù)制到單元格區(qū)域B30：E30，就可以得到季節(jié)比率的值，具體結(jié)果見圖13-20：圖13-20用EXCEL計算季節(jié)變動結(jié)果指數(shù)分析實驗?zāi)康模河肊XCEL進(jìn)行指數(shù)分析實驗步驟：指數(shù)分析法是研究社會經(jīng)濟(jì)現(xiàn)象數(shù)量變動情況的一種統(tǒng)計分析法。指數(shù)有總指數(shù)與平均指數(shù)之分，在這一節(jié)我們介紹如何用Excel進(jìn)行指數(shù)分析與因素分析。一、用Excel計算總指數(shù)【例13-9】：圖中是某企業(yè)甲、

15、乙、丙三種產(chǎn)品的生產(chǎn)情況，以基期價格p作為同度量因素，計算生產(chǎn)量指數(shù)。如圖13-21圖13-21用EXCEL計算總指數(shù)資料及結(jié)果計算步驟：第一步：計算各個p0q0：在G2中輸入“=C2*D2”，并用鼠標(biāo)拖曳將公式復(fù)制到G2：G4區(qū)域。第二步：計算各個p0*q1：有H2中輸入“=C2*F2”，并用鼠標(biāo)拖曳將公式復(fù)制到H2：H4區(qū)域。第三步：計算p0q0和p0q1：選定G2：G4區(qū)域，單擊工具欄上的“”按鈕，在G5出現(xiàn)該列的求和值。選定H2：H4區(qū)域，單擊工具欄上的“”按鈕，在H5出現(xiàn)該列的求和值。第四步：計算生產(chǎn)量綜合指數(shù)Iq=p0q1/p0q0：在C6中輸入“=H5/G5”便可得到生產(chǎn)量綜合指

16、數(shù)注意：在輸入公式的時候，不要忘記等號，否則就不會出現(xiàn)數(shù)值。二、用Excel計算平均指數(shù)現(xiàn)以生產(chǎn)量平均指數(shù)為例，說明加權(quán)算術(shù)平均法的計算方法?！纠?3-10】：圖中的A1：A4區(qū)域內(nèi)是某企業(yè)生產(chǎn)情況的統(tǒng)計資料，我們要以基期總成本為同度量因素，計算生產(chǎn)量平均指數(shù)。如圖13-22圖13-22用EXCEL計算平均指數(shù)資料及結(jié)果計算步驟：第一步：計算個體指數(shù) k=q1/q0：在F2中輸入“=D2/C2”。并用鼠標(biāo)拖曳將公式復(fù)制到F2：F4區(qū)域。第二步：計算k*p0q0并求和。在G2中輸入“=F2*E2”并用鼠標(biāo)拖曳將公式復(fù)制到G2：G4區(qū)域。選定G2：G4區(qū)域，單擊工具欄上的：“”按鈕，在G5列出現(xiàn)該列的求和值。第三步：計算生產(chǎn)量平均指數(shù)：在C7中輸入“=G5/E5”即得到所求的值。三、用Excel進(jìn)行因素分析 【例13-11】：我們還用上面的例子，有關(guān)資料如圖13-23圖13-23用EXCEL進(jìn)行因素分析資料及結(jié)果進(jìn)行因素分析的計算步驟如下：第一步：計算各個p0*q0和p0q0：在G2中輸入“C2*D2”，并用鼠標(biāo)拖曳將公式復(fù)制到G2：G4區(qū)域。選定G2：G4區(qū)域，單擊工具欄上的“”按鈕，在G5出現(xiàn)該列的求和值。第二步：計算各個p0*q1和p0*q