用Excel進(jìn)行相關(guān)性與回歸分析

1、用用進(jìn)行進(jìn)行相關(guān)與回歸分析相關(guān)與回歸分析n用用Excel計(jì)算描述統(tǒng)計(jì)量計(jì)算描述統(tǒng)計(jì)量n用用Excel進(jìn)行相關(guān)分析進(jìn)行相關(guān)分析n用用Excel進(jìn)行回歸分析進(jìn)行回歸分析用用Excel進(jìn)行相關(guān)與回歸分析進(jìn)行相關(guān)與回歸分析用用Excel計(jì)算描述統(tǒng)計(jì)量計(jì)算描述統(tǒng)計(jì)量n用函數(shù)計(jì)算描述統(tǒng)計(jì)量用函數(shù)計(jì)算描述統(tǒng)計(jì)量n描述統(tǒng)計(jì)菜單項(xiàng)的使用描述統(tǒng)計(jì)菜單項(xiàng)的使用n常用的描述統(tǒng)計(jì)量有眾數(shù)、中位數(shù)、常用的描述統(tǒng)計(jì)量有眾數(shù)、中位數(shù)、算術(shù)平均數(shù)、調(diào)和平均數(shù)、幾何平算術(shù)平均數(shù)、調(diào)和平均數(shù)、幾何平均數(shù)、極差、四分位差、標(biāo)準(zhǔn)差、均數(shù)、極差、四分位差、標(biāo)準(zhǔn)差、方差、標(biāo)準(zhǔn)差系數(shù)等。下面介紹如方差、標(biāo)準(zhǔn)差系數(shù)等。下面介紹如何用函數(shù)來計(jì)算

2、描述統(tǒng)計(jì)量。何用函數(shù)來計(jì)算描述統(tǒng)計(jì)量。一、用函數(shù)計(jì)算描述統(tǒng)計(jì)量一、用函數(shù)計(jì)算描述統(tǒng)計(jì)量n用函數(shù)運(yùn)算有兩種方法：一是用函數(shù)運(yùn)算有兩種方法：一是手工輸入函數(shù)名稱手工輸入函數(shù)名稱及及參數(shù)。這種輸入形式比較簡(jiǎn)單、快捷。但需要非常參數(shù)。這種輸入形式比較簡(jiǎn)單、快捷。但需要非常熟悉函數(shù)名稱及其參數(shù)的輸入形式。所以，只有比熟悉函數(shù)名稱及其參數(shù)的輸入形式。所以，只有比較簡(jiǎn)單的函數(shù)才用這種方法輸入；二是較簡(jiǎn)單的函數(shù)才用這種方法輸入；二是函數(shù)導(dǎo)入函數(shù)導(dǎo)入法。法。這是一種最為常用的辦法，它適合于所有函數(shù)的使這是一種最為常用的辦法，它適合于所有函數(shù)的使用，而且在導(dǎo)入過程中有向?qū)崾荆蚨浅７奖?。用，而且在?dǎo)入過程中有

3、向?qū)崾?，因而非常方便。函?shù)的一般導(dǎo)入過程為：點(diǎn)菜單函數(shù)的一般導(dǎo)入過程為：點(diǎn)菜單“插入插入”；找；找“函函數(shù)數(shù)”，此時(shí)出現(xiàn)一個(gè)，此時(shí)出現(xiàn)一個(gè)“插入函數(shù)插入函數(shù)”對(duì)話框；在對(duì)話對(duì)話框；在對(duì)話框的框的“選擇類別選擇類別”中確定函數(shù)的類別（如常用函數(shù)中確定函數(shù)的類別（如常用函數(shù)或統(tǒng)計(jì)）；在或統(tǒng)計(jì)）；在“選擇函數(shù)選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱，內(nèi)確定欲選的函數(shù)名稱，如如SUM、MODE等；點(diǎn)等；點(diǎn)“確定確定”后即可出現(xiàn)該函數(shù)后即可出現(xiàn)該函數(shù)運(yùn)算的對(duì)話框向?qū)?，再按向?qū)У奶崾就逻\(yùn)行即可。運(yùn)算的對(duì)話框向?qū)?，再按向?qū)У奶崾就逻\(yùn)行即可。1眾數(shù)眾數(shù) 例：為了分析小麥的分蘗情況，進(jìn)行了例：為了分析小麥的分蘗情況

4、，進(jìn)行了10株株小麥的調(diào)查，如下圖所示：小麥的調(diào)查，如下圖所示：（1）手工輸入函數(shù)名稱及參數(shù)）手工輸入函數(shù)名稱及參數(shù)單擊任一單元格，輸入單擊任一單元格，輸入“=MODE（B2：B11）”，回車后即可得眾數(shù)為回車后即可得眾數(shù)為14。（2）函數(shù)導(dǎo)入法）函數(shù)導(dǎo)入法點(diǎn)菜單點(diǎn)菜單“插入插入”；找；找“函數(shù)函數(shù)”，此時(shí)出現(xiàn)一個(gè)，此時(shí)出現(xiàn)一個(gè)“插入函數(shù)插入函數(shù)”對(duì)話框；在對(duì)話框的對(duì)話框；在對(duì)話框的“選擇類別選擇類別”中確定函數(shù)的類別中確定函數(shù)的類別“統(tǒng)計(jì)統(tǒng)計(jì)”；在；在“選擇函數(shù)選擇函數(shù)”內(nèi)內(nèi)確定欲選的函數(shù)名稱確定欲選的函數(shù)名稱“MODE”，點(diǎn)點(diǎn)“確定確定”后即可出現(xiàn)該函數(shù)運(yùn)算的對(duì)話框向?qū)?，在后即可出現(xiàn)該函數(shù)

5、運(yùn)算的對(duì)話框向?qū)В贜umber1處輸入處輸入B2：B11或選擇或選擇Excel中的中的B2：B11區(qū)域。按區(qū)域。按“確定確定”，在在Excel中即得到眾數(shù)中即得到眾數(shù)14。2中位數(shù)中位數(shù)單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=MEDIAN（B2:B11）”，回車后得中位數(shù)為回車后得中位數(shù)為14。3算術(shù)平均數(shù)算術(shù)平均數(shù)單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=AVERAGE（B2:B11）”，回車后得算術(shù)平均數(shù)為回車后得算術(shù)平均數(shù)為14.1。4幾何平均數(shù)單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=GEOMEAN（B2:B11）”，回車后得幾何平均數(shù)為回車后得幾何平

6、均數(shù)為14.02。5調(diào)和平均數(shù)單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=HARMEAN（B2:B11）”，回車后得調(diào)和平均數(shù)為回車后得調(diào)和平均數(shù)為1.422。6截尾平均數(shù)n將數(shù)據(jù)按由小到大順序排列后，因數(shù)據(jù)兩端將數(shù)據(jù)按由小到大順序排列后，因數(shù)據(jù)兩端值不夠穩(wěn)定，按一定比例去掉頭尾兩端一定值不夠穩(wěn)定，按一定比例去掉頭尾兩端一定數(shù)量的觀察值，然后再求平均，這樣得到的數(shù)量的觀察值，然后再求平均，這樣得到的平均數(shù)就是截尾平均數(shù)。如果按平均數(shù)就是截尾平均數(shù)。如果按2/10，即，即從從30個(gè)數(shù)據(jù)中去掉最大的一個(gè)值和最小的一個(gè)數(shù)據(jù)中去掉最大的一個(gè)值和最小的一個(gè)值，再求平均數(shù)。個(gè)值，再求平均數(shù)。n單擊

7、任一空白單元格，輸入單擊任一空白單元格，輸入“=TRIMMEAN（B2:B11，1/10）”，回車后得截尾平均數(shù)為回車后得截尾平均數(shù)為14.17全距n單擊任一空白單元格，單擊任一空白單元格，n輸入輸入“=MAX（B2:B11）-MIN（B2:B11）”，n回車后得全距為回車后得全距為5。8標(biāo)準(zhǔn)差(估計(jì)樣本）單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=STDEV（B2:B11）”，回車后得標(biāo)準(zhǔn)差為回車后得標(biāo)準(zhǔn)差為1.524。9標(biāo)準(zhǔn)差系數(shù)單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=STDEV（B2:B11）/AVERAGE（B2:B11）”，回車后得標(biāo)準(zhǔn)差系數(shù)為回車后得標(biāo)準(zhǔn)差系數(shù)為

8、0108010偏度系數(shù)單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=SKEW（B2:B11）”，回車后得偏度系數(shù)為回車后得偏度系數(shù)為-0.678。11峰度系數(shù)單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=KURT（B2:B11）”，回車后得峰度系數(shù)為回車后得峰度系數(shù)為0.6249。常用的統(tǒng)計(jì)量函數(shù)常用的統(tǒng)計(jì)量函數(shù)由于公式執(zhí)行后顯示的是計(jì)算結(jié)果按Ctrl+、鍵(位于鍵盤左上角)可使公式在顯示公式內(nèi)容與顯示公式結(jié)果之間切換 二、描述統(tǒng)計(jì)菜單項(xiàng)的使用二、描述統(tǒng)計(jì)菜單項(xiàng)的使用仍使用上面的例子仍使用上面的例子我們已經(jīng)把數(shù)據(jù)輸入到我們已經(jīng)把數(shù)據(jù)輸入到B2:B11單元格，單元格，然后按以下步驟操

9、作：然后按以下步驟操作：第一步：在工具菜單中選擇數(shù)據(jù)分析選項(xiàng)，從其對(duì)話框中第一步：在工具菜單中選擇數(shù)據(jù)分析選項(xiàng)，從其對(duì)話框中選擇描述統(tǒng)計(jì)，按確定后打開描述統(tǒng)計(jì)對(duì)話框選擇描述統(tǒng)計(jì)，按確定后打開描述統(tǒng)計(jì)對(duì)話框。第二步：在輸入?yún)^(qū)域中輸入第二步：在輸入?yún)^(qū)域中輸入$B$1:$B$11，在，在輸出區(qū)域中選擇輸出區(qū)域中選擇$F$1，其他復(fù)選框可根據(jù)需，其他復(fù)選框可根據(jù)需要選定，選擇匯總統(tǒng)計(jì)，可給出一系列描述統(tǒng)要選定，選擇匯總統(tǒng)計(jì)，可給出一系列描述統(tǒng)計(jì)量；選擇平均數(shù)置信度，會(huì)給出用樣本平均計(jì)量；選擇平均數(shù)置信度，會(huì)給出用樣本平均數(shù)估計(jì)總體平均數(shù)的置信區(qū)間；第數(shù)估計(jì)總體平均數(shù)的置信區(qū)間；第K大值和第大值和第K小

10、值會(huì)給出樣本中第小值會(huì)給出樣本中第K個(gè)大值和第個(gè)大值和第K個(gè)小值。個(gè)小值。第三步：?jiǎn)螕舸_定，可得輸出結(jié)果。第三步：?jiǎn)螕舸_定，可得輸出結(jié)果。上面的結(jié)果中，平均指樣本均值；標(biāo)準(zhǔn)誤差指樣本平均數(shù)的標(biāo)準(zhǔn)差；中值即中位數(shù)；模式指眾數(shù)；標(biāo)準(zhǔn)偏差指樣本標(biāo)準(zhǔn)差，自由度為n-1；峰值即峰度系數(shù)；偏斜度即偏度系數(shù)；區(qū)域?qū)嶋H上是極差，或全距；可以看出與我們前面用函數(shù)計(jì)算的結(jié)果完全相同。最大值為16，最小值為11，第三個(gè)最大值為15，第三個(gè)最小值為13。n1利用函數(shù)計(jì)算相關(guān)系數(shù)利用函數(shù)計(jì)算相關(guān)系數(shù)n2用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù)用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù)利用利用EXCEL計(jì)算相關(guān)系數(shù)計(jì)算相關(guān)系數(shù)1利用函數(shù)計(jì)算相關(guān)系數(shù)利用函

11、數(shù)計(jì)算相關(guān)系數(shù)n在在Excel中，提供了兩個(gè)中，提供了兩個(gè)計(jì)算兩個(gè)變量之間相關(guān)系計(jì)算兩個(gè)變量之間相關(guān)系數(shù)的方法，數(shù)的方法，CORREL函數(shù)和函數(shù)和PERSON函數(shù)函數(shù)，這兩個(gè)，這兩個(gè)函數(shù)是等價(jià)的，這里我們介紹用函數(shù)是等價(jià)的，這里我們介紹用CORREL函數(shù)計(jì)算函數(shù)計(jì)算相關(guān)系數(shù)。相關(guān)系數(shù)。n第一步：?jiǎn)螕羧我粋€(gè)空白單元格，單擊第一步：?jiǎn)螕羧我粋€(gè)空白單元格，單擊“插入插入”菜菜單，選擇單，選擇“函數(shù)函數(shù)”，打開，打開“插入函數(shù)插入函數(shù)”對(duì)話框，在對(duì)話框，在“選擇類別選擇類別”中選擇中選擇“統(tǒng)計(jì)統(tǒng)計(jì)”；在；在“選擇函數(shù)選擇函數(shù)”中中選擇選擇“CORREL”，單擊確定后，出現(xiàn)，單擊確定后，出現(xiàn)CORREL

12、對(duì)對(duì)話框。話框。n第二步：在第二步：在array1中輸入中輸入B3：B10，在，在array2中中輸入輸入C3：C10，即可在對(duì)話框下方顯示出計(jì)算結(jié)果，即可在對(duì)話框下方顯示出計(jì)算結(jié)果為為0.77723。2用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù)用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù)第一步：?jiǎn)螕舻谝徊剑簡(jiǎn)螕簟肮ぞ吖ぞ摺辈藛危x擇菜單，選擇“數(shù)據(jù)分析數(shù)據(jù)分析”項(xiàng)，在項(xiàng)，在“數(shù)據(jù)分析數(shù)據(jù)分析”項(xiàng)中選擇項(xiàng)中選擇“相關(guān)系數(shù)相關(guān)系數(shù)”，彈，彈出出“相關(guān)系數(shù)相關(guān)系數(shù)”對(duì)話框。對(duì)話框。 第二步：在第二步：在“輸入?yún)^(qū)域輸入?yún)^(qū)域”輸入輸入$B$2:$C$10，“分分組方式組方式”選擇逐列，選擇選擇逐列，選擇“標(biāo)志位于第一行標(biāo)志位于第一行”，在

13、，在“輸出區(qū)域輸出區(qū)域”中輸入中輸入$I$3，單擊，單擊“確定確定” 。nExcel進(jìn)行回歸分析同樣分函數(shù)和回歸分析宏兩種形進(jìn)行回歸分析同樣分函數(shù)和回歸分析宏兩種形式，其提供了式，其提供了9個(gè)函數(shù)用于建立回歸模型和預(yù)測(cè)，這個(gè)函數(shù)用于建立回歸模型和預(yù)測(cè)，這9個(gè)函數(shù)分別是：個(gè)函數(shù)分別是：n1INTERCEPT返回線性回歸模型的返回線性回歸模型的截距截距n2SLOPE返回線性回歸模型的返回線性回歸模型的斜率斜率n3RSQ返回線性回歸模型的返回線性回歸模型的判定系數(shù)判定系數(shù)n4FORECAST返回一元線性回歸模型的返回一元線性回歸模型的預(yù)測(cè)值預(yù)測(cè)值n5STEYX計(jì)算估計(jì)的計(jì)算估計(jì)的標(biāo)準(zhǔn)誤差標(biāo)準(zhǔn)誤差n6

14、TREND計(jì)算計(jì)算線性回歸線線性回歸線的的趨勢(shì)值趨勢(shì)值n7GROWTH返回返回指數(shù)曲線指數(shù)曲線的的趨勢(shì)值趨勢(shì)值n8LINEST返回返回線性回歸線性回歸模型的模型的參數(shù)參數(shù)n9LOGEST返回返回指數(shù)曲線指數(shù)曲線模型的模型的參數(shù)參數(shù) 用函數(shù)進(jìn)行回歸分析比較麻煩，這里介紹使用回歸用函數(shù)進(jìn)行回歸分析比較麻煩，這里介紹使用回歸分析宏進(jìn)行回歸分析。分析宏進(jìn)行回歸分析。第一步：?jiǎn)螕舻谝徊剑簡(jiǎn)螕簟肮ぞ吖ぞ摺辈藛?，選擇菜單，選擇“數(shù)據(jù)分析數(shù)據(jù)分析”選項(xiàng)，出現(xiàn)選項(xiàng)，出現(xiàn)“數(shù)據(jù)分析數(shù)據(jù)分析”對(duì)話框，在分析工具中選擇對(duì)話框，在分析工具中選擇“回歸回歸”。第二步：?jiǎn)螕舻诙剑簡(jiǎn)螕簟按_定確定”按鈕，彈出按鈕，彈出“回歸

15、回歸”對(duì)話框，在對(duì)話框，在“Y值輸入值輸入?yún)^(qū)域區(qū)域”輸入輸入$B$1：$B$11；在；在“X值輸入?yún)^(qū)域值輸入?yún)^(qū)域”輸入輸入$C$1:$C$11，在，在“輸出選項(xiàng)輸出選項(xiàng)”選擇選擇“$E$1”，如下圖所示。，如下圖所示。 第三步：?jiǎn)螕舸_定按鈕，得回歸分析結(jié)果如下圖所示。第三步：?jiǎn)螕舸_定按鈕，得回歸分析結(jié)果如下圖所示。ExcelExcel回歸分析工具的輸出結(jié)果包括回歸分析工具的輸出結(jié)果包括3 3個(gè)部分：個(gè)部分：n(1)回歸統(tǒng)計(jì)表n回歸統(tǒng)計(jì)表包括以下幾部分內(nèi)容：回歸統(tǒng)計(jì)表包括以下幾部分內(nèi)容：nMultiple RMultiple R（復(fù)相關(guān)系數(shù)（復(fù)相關(guān)系數(shù)R R）：是）：是R R2 2的平方根，的平

16、方根，又稱為相關(guān)系數(shù)，又稱為相關(guān)系數(shù)，用來衡量變量用來衡量變量x x和和y y之間相關(guān)程之間相關(guān)程度的大小。度的大小。本例中本例中R R為為0.63130.6313，表示二者之間的，表示二者之間的關(guān)系是正相關(guān)。關(guān)系是正相關(guān)。n R SquareR Square（復(fù)測(cè)定系數(shù)（復(fù)測(cè)定系數(shù)R R2 2）：）：用來說明自變用來說明自變量解釋因變量變差的程度，量解釋因變量變差的程度，以測(cè)定因變量以測(cè)定因變量y y的擬的擬合效果。合效果。 n（2）方差分析表n方差分析表的主要作用是方差分析表的主要作用是通過通過F F檢驗(yàn)檢驗(yàn)來判斷回歸模來判斷回歸模型的回歸效果。表中型的回歸效果。表中“回歸分析回歸分析”行

17、計(jì)算的是行計(jì)算的是估估計(jì)值同均值之差（計(jì)值同均值之差（- -）的各項(xiàng)指標(biāo)）的各項(xiàng)指標(biāo)；“殘差殘差”行是行是用于計(jì)算每個(gè)樣本觀察值與估計(jì)值之差（用于計(jì)算每個(gè)樣本觀察值與估計(jì)值之差（- -）的各）的各項(xiàng)指標(biāo)；項(xiàng)指標(biāo)；“總計(jì)總計(jì)”行用于計(jì)算每個(gè)值同均值之差行用于計(jì)算每個(gè)值同均值之差（- -）的各項(xiàng)指標(biāo)。第二列）的各項(xiàng)指標(biāo)。第二列dfdf是自由度，第三列是自由度，第三列SSSS是離差的平方和，第四列是離差的平方和，第四列MSMS是均方差，它是離差是均方差，它是離差平方和除以自由度，第五列是平方和除以自由度，第五列是F F統(tǒng)計(jì)量，第六列統(tǒng)計(jì)量，第六列Significance FSignificance F是在顯著性水平下的是在顯著性水平下的F F的臨界值的臨界值 in