在excel中相關(guān)系的求法

1、實(shí)驗(yàn)三EXCEL在相關(guān)與回歸分析中的應(yīng)用一、相關(guān)圖的繪制如圖1所示。圖1表7 - 1的EXCEL工作表制作相關(guān)圖的步驟如下：選擇區(qū)域A1:B11，如圖1所示;點(diǎn)擊EXCEL圖表向?qū)?；Mi crosoft Excel文件（I）編輯（D視E 插入（X）格式）工具（I）數(shù)JE（E）窗口 （!）幫助皿D羅耐|=標(biāo)準(zhǔn)類(lèi)型|自定義類(lèi)型|圖表類(lèi)型：M條形圈室折線圈3餅圖g面粗圖噂圓環(huán)圖.酒，雷達(dá)圖由曲面圖:氣泡圈圖表向?qū)?4步驟之1 -圖表類(lèi)型子圖表類(lèi)型心:&910n.12131415散點(diǎn)圖比較成對(duì)的數(shù)值在“圖表類(lèi)型”中選擇“XY按下不放可查看示例1 ）完成|如圖2。圖2散點(diǎn)圖的制作在“子圖表類(lèi)型”中選擇第

2、一種散點(diǎn)圖，并點(diǎn)擊“下一步”即圖2散點(diǎn)圖的制作圖3散點(diǎn)圖的制作點(diǎn)擊“完成”，并對(duì)圖形進(jìn)行修飾編輯，最后得到如圖4所示廣告投入與月平均銷(xiāo)售額之間的散點(diǎn)圖。銷(xiāo)售額圖4廣告投入與月平均銷(xiāo)售額的散點(diǎn)圖二、相關(guān)系數(shù)在EXCEL中，相關(guān)系數(shù)函數(shù)和相關(guān)系數(shù)宏提供了兩種計(jì)算相關(guān) 系數(shù)的方法。（一）相關(guān)系數(shù)函數(shù)在EXCEL中，CORREL函數(shù)和PERSON函數(shù)提供了計(jì)算兩個(gè) 變量之間的相關(guān)系數(shù)的方法，這兩個(gè)函數(shù)是等價(jià)的。與相關(guān)系數(shù)有關(guān) 的函數(shù)還有RSQ （相關(guān)系數(shù)的平方，即判定系數(shù)瘁）和COVAR （協(xié) 方差函數(shù)）。在這里我們以CORREL函數(shù)和表7-1中資料為例，介紹利用函 數(shù)計(jì)算相關(guān)系數(shù)的方法。首先，點(diǎn)擊E

3、XCEL函數(shù)圖鈕“f”，選擇“統(tǒng)計(jì)”函數(shù)；x在統(tǒng)計(jì)函數(shù)點(diǎn)擊“CORREL”，進(jìn)入函數(shù)向?qū)?；在“arrayl”中輸入第一個(gè)變量“廣告投入”的數(shù)據(jù)區(qū)域A2:A11 “array2”中輸入第二個(gè)變量“月均銷(xiāo)售額”的數(shù)據(jù)區(qū)域B2:B11樣，即可在當(dāng)前光標(biāo)所在單元格顯示函數(shù)的計(jì)算結(jié)果。如圖5所示。圖5 CORREL函數(shù)計(jì)算相關(guān)系數(shù)（二）相關(guān)系數(shù)宏在EXCEL數(shù)據(jù)分析宏中，EXCEL專(zhuān)門(mén)提供了計(jì)算相關(guān)系數(shù)宏過(guò) 程。利用此宏過(guò)程，可以計(jì)算多個(gè)變量之間的相關(guān)矩陣。仍然以表7-1中資料為例，利用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù)矩陣的 過(guò)程如下：點(diǎn)擊EXCEL 工具”菜單，選擇“數(shù)據(jù)分析”過(guò)程；在“數(shù)據(jù)分析”宏過(guò)程中，選擇

4、“相關(guān)系數(shù)”過(guò)程。如圖6所 示；在“輸入?yún)^(qū)域”中輸入兩個(gè)變量所在區(qū)域A2:B11，數(shù)據(jù)以列 排列，輸出區(qū)域選擇在同一工作表中的以D1:E5區(qū)域里。計(jì)算結(jié)果如 圖7所示。圖6相關(guān)系數(shù)宏圖7利用相關(guān)系數(shù)宏計(jì)算的相關(guān)系數(shù)矩陣三、回歸分析宏除了回歸分析宏外，EXCEL雖然提供了 9個(gè)函數(shù)用于建立回歸模 型和回歸預(yù)測(cè)。這9個(gè)函數(shù)列于表7-9中。但EXCEL提供的回歸 分析宏仍然具有更方便的特點(diǎn)。仍以表7-1中廣告投入與銷(xiāo)售額的 關(guān)系資料為例，利用一元線性回歸方程確定兩個(gè)變量之間的定量關(guān) 系。表7-9用于回歸分析的工作表函數(shù)函數(shù)名INTERCEP TSLOPERSQFORECASTSTEYXTREND G

5、ROWTH LINEST定 義一元線性回歸模型截距的估計(jì)值一元線性回歸模型斜率定系計(jì)值（r2）依照一元線性回歸模型的預(yù)測(cè)值 的標(biāo)準(zhǔn)誤差序依照多元線性回歸模型的預(yù)測(cè)值 依照多元指數(shù)回歸模型的預(yù)測(cè)值 A、一 皿回歸模型的未知參的標(biāo)準(zhǔn)誤差估計(jì)多元線性依照一元線性回歸模型的預(yù)測(cè)值數(shù)LOGEST估計(jì)多元指數(shù)回歸模型的未知參數(shù)回歸宏確定兩個(gè)變量之間定量關(guān)系的過(guò)程如下:圖8回歸分析宏過(guò)程在“工具欄”菜單“數(shù)據(jù)分析”過(guò)程中選擇“回歸”宏過(guò)程；在“Y值輸入?yún)^(qū)域”內(nèi)輸入B2:B11,在“X值輸入?yún)^(qū)域”輸入A2： A11，如果是多元線性回歸，則X值的輸入?yún)^(qū)就是除Y變量以 外的全部解釋變量；選擇“標(biāo)志”；置信度水平為

6、95%，輸出結(jié)果選擇在一張新的 工作表中；選擇“殘差分析”，并繪制回歸擬合圖，點(diǎn)擊“確定”即得到圖 9所示的回歸分析結(jié)果和圖10的殘差表。鬟M(jìn)icrosoft Excel-六章四文件但）編輯但）視圖）插入0）格式 工具 圖表但）窗口也）幫助冬） q a a as 霆我乳岫區(qū),宋體-12- B數(shù)值m .=ABCD1_ E1_ FGH1_ ISUOARE OUTPUTI回歸統(tǒng)計(jì)Multiple 0.994198R SquZr已 。 98扇o O5銷(xiāo)售額Adjusted 0.926924 標(biāo)準(zhǔn)誤差L 630011 觀測(cè)值W10方差分析廣告投入11df SS MS析計(jì)差計(jì)回殘總1 1815.93 18

7、15.93 683.468 4. 92E-098 21.25547 2.656934，18：對(duì).18515H171 QCM.ffi%rr標(biāo)準(zhǔn)誤差I(lǐng)ntercept 11.61492 1.280176V Ho”； pRI fl 口口/1 口口U fl fTQ.-1Qt Stat PfMue Lower 95線11如鹽 95WF限 95. 0：上限 95. 0% 9.072908 1. 75E-05 8.662824 14. 56701 8.662824 i4.56701 9P. 1Fl aQR-na ri 9f-|P.Q.-19 fl OP.90.10 fl 口1K口/1 叮 fl Of；90.-IQ圖9回歸分析結(jié)果RESIDUAL 0UTFIJT8910. （卜* 2040 6&預(yù)測(cè)22. 6761125.153 毆 3Z L4449 34. 9761625892D553246. 479851. 6121960. 6381365.項(xiàng)加無(wú)殘差-1. 47611T.2538N0. 755507 -0. 876162410831.144677SW0199 1.187805 -1