1、用用進行進行相關與回歸分析相關與回歸分析n用用Excel計算描述統計量計算描述統計量n用用Excel進行相關分析進行相關分析n用用Excel進行回歸分析進行回歸分析用用Excel進行相關與回歸分析進行相關與回歸分析用用Excel計算描述統計量計算描述統計量n用函數計算描述統計量用函數計算描述統計量n描述統計菜單項的使用描述統計菜單項的使用n常用的描述統計量有眾數、中位數、常用的描述統計量有眾數、中位數、算術平均數、調和平均數、幾何平算術平均數、調和平均數、幾何平均數、極差、四分位差、標準差、均數、極差、四分位差、標準差、方差、標準差系數等。下面介紹如方差、標準差系數等。下面介紹如何用函數來計算

2、描述統計量。何用函數來計算描述統計量。一、用函數計算描述統計量一、用函數計算描述統計量n用函數運算有兩種方法：一是用函數運算有兩種方法：一是手工輸入函數名稱手工輸入函數名稱及及參數。這種輸入形式比較簡單、快捷。但需要非常參數。這種輸入形式比較簡單、快捷。但需要非常熟悉函數名稱及其參數的輸入形式。所以，只有比熟悉函數名稱及其參數的輸入形式。所以，只有比較簡單的函數才用這種方法輸入；二是較簡單的函數才用這種方法輸入；二是函數導入函數導入法。法。這是一種最為常用的辦法，它適合于所有函數的使這是一種最為常用的辦法，它適合于所有函數的使用，而且在導入過程中有向導提示，因而非常方便。用，而且在導入過程中有

3、向導提示，因而非常方便。函數的一般導入過程為：點菜單函數的一般導入過程為：點菜單“插入插入”；找；找“函函數數”，此時出現一個，此時出現一個“插入函數插入函數”對話框；在對話對話框；在對話框的框的“選擇類別選擇類別”中確定函數的類別（如常用函數中確定函數的類別（如常用函數或統計）；在或統計）；在“選擇函數選擇函數”內確定欲選的函數名稱，內確定欲選的函數名稱，如如SUM、MODE等；點等；點“確定確定”后即可出現該函數后即可出現該函數運算的對話框向導，再按向導的提示往下運行即可。運算的對話框向導，再按向導的提示往下運行即可。1眾數眾數 例：為了分析小麥的分蘗情況，進行了例：為了分析小麥的分蘗情況

4、，進行了10株株小麥的調查，如下圖所示：小麥的調查，如下圖所示：（1）手工輸入函數名稱及參數）手工輸入函數名稱及參數單擊任一單元格，輸入單擊任一單元格，輸入“=MODE（B2：B11）”，回車后即可得眾數為回車后即可得眾數為14。（2）函數導入法）函數導入法點菜單點菜單“插入插入”；找；找“函數函數”，此時出現一個，此時出現一個“插入函數插入函數”對話框；在對話框的對話框；在對話框的“選擇類別選擇類別”中確定函數的類別中確定函數的類別“統計統計”；在；在“選擇函數選擇函數”內內確定欲選的函數名稱確定欲選的函數名稱“MODE”，點點“確定確定”后即可出現該函數運算的對話框向導，在后即可出現該函數

5、運算的對話框向導，在Number1處輸入處輸入B2：B11或選擇或選擇Excel中的中的B2：B11區域。按區域。按“確定確定”，在在Excel中即得到眾數中即得到眾數14。2中位數中位數單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=MEDIAN（B2:B11）”，回車后得中位數為回車后得中位數為14。3算術平均數算術平均數單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=AVERAGE（B2:B11）”，回車后得算術平均數為回車后得算術平均數為14.1。4幾何平均數單擊任一空白單元格，單擊任一空白單元格，輸入輸入“=GEOMEAN（B2:B11）”，回車后得幾何平均數為回車后得幾何平

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

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

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

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

10、值會給出樣本中第小值會給出樣本中第K個大值和第個大值和第K個小值。個小值。第三步：單擊確定，可得輸出結果。第三步：單擊確定，可得輸出結果。上面的結果中，平均指樣本均值；標準誤差指樣本平均數的標準差；中值即中位數；模式指眾數；標準偏差指樣本標準差，自由度為n-1；峰值即峰度系數；偏斜度即偏度系數；區域實際上是極差，或全距；可以看出與我們前面用函數計算的結果完全相同。最大值為16，最小值為11，第三個最大值為15，第三個最小值為13。n1利用函數計算相關系數利用函數計算相關系數n2用相關系數宏計算相關系數用相關系數宏計算相關系數利用利用EXCEL計算相關系數計算相關系數1利用函數計算相關系數利用函

11、數計算相關系數n在在Excel中，提供了兩個中，提供了兩個計算兩個變量之間相關系計算兩個變量之間相關系數的方法，數的方法，CORREL函數和函數和PERSON函數函數，這兩個，這兩個函數是等價的，這里我們介紹用函數是等價的，這里我們介紹用CORREL函數計算函數計算相關系數。相關系數。n第一步：單擊任一個空白單元格，單擊第一步：單擊任一個空白單元格，單擊“插入插入”菜菜單，選擇單，選擇“函數函數”，打開，打開“插入函數插入函數”對話框，在對話框，在“選擇類別選擇類別”中選擇中選擇“統計統計”；在；在“選擇函數選擇函數”中中選擇選擇“CORREL”，單擊確定后，出現，單擊確定后，出現CORREL

12、對對話框。話框。n第二步：在第二步：在array1中輸入中輸入B3：B10，在，在array2中中輸入輸入C3：C10，即可在對話框下方顯示出計算結果，即可在對話框下方顯示出計算結果為為0.77723。2用相關系數宏計算相關系數用相關系數宏計算相關系數第一步：單擊第一步：單擊“工具工具”菜單，選擇菜單，選擇“數據分析數據分析”項，在項，在“數據分析數據分析”項中選擇項中選擇“相關系數相關系數”，彈，彈出出“相關系數相關系數”對話框。對話框。 第二步：在第二步：在“輸入區域輸入區域”輸入輸入$B$2:$C$10，“分分組方式組方式”選擇逐列，選擇選擇逐列，選擇“標志位于第一行標志位于第一行”，在

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

14、TREND計算計算線性回歸線線性回歸線的的趨勢值趨勢值n7GROWTH返回返回指數曲線指數曲線的的趨勢值趨勢值n8LINEST返回返回線性回歸線性回歸模型的模型的參數參數n9LOGEST返回返回指數曲線指數曲線模型的模型的參數參數 用函數進行回歸分析比較麻煩，這里介紹使用回歸用函數進行回歸分析比較麻煩，這里介紹使用回歸分析宏進行回歸分析。分析宏進行回歸分析。第一步：單擊第一步：單擊“工具工具”菜單，選擇菜單，選擇“數據分析數據分析”選項，出現選項，出現“數據分析數據分析”對話框，在分析工具中選擇對話框，在分析工具中選擇“回歸回歸”。第二步：單擊第二步：單擊“確定確定”按鈕，彈出按鈕，彈出“回歸

15、回歸”對話框，在對話框，在“Y值輸入值輸入區域區域”輸入輸入$B$1：$B$11；在；在“X值輸入區域值輸入區域”輸入輸入$C$1:$C$11，在，在“輸出選項輸出選項”選擇選擇“$E$1”，如下圖所示。，如下圖所示。 第三步：單擊確定按鈕，得回歸分析結果如下圖所示。第三步：單擊確定按鈕，得回歸分析結果如下圖所示。ExcelExcel回歸分析工具的輸出結果包括回歸分析工具的輸出結果包括3 3個部分：個部分：n(1)回歸統計表n回歸統計表包括以下幾部分內容：回歸統計表包括以下幾部分內容：nMultiple RMultiple R（復相關系數（復相關系數R R）：是）：是R R2 2的平方根，的平

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

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