EXCEL在描述統(tǒng)計相關系數(shù)與回歸分析中的應用.ppt

用EXCEL進行 相關與回歸分析,水利部灌溉試驗總站,用Excel計算描述統(tǒng)計量 用Excel進行相關分析 用Excel進行回歸分析,用Excel進行相關與回歸分析,用Excel計算描述統(tǒng)計量,用函數(shù)計算描述統(tǒng)計量 描述統(tǒng)計菜單項的使用,常用的描述統(tǒng)計量有眾數(shù)、中位數(shù)、算術平均數(shù)、調(diào)和平均數(shù)、幾何平均數(shù)、極差、四分位差、標準差、方差、標準差系數(shù)等。下面介紹如何用函數(shù)來計算描述統(tǒng)計量。,一、用函數(shù)計算描述統(tǒng)計量,用函數(shù)運算有兩種方法：一是手工輸入函數(shù)名稱及參數(shù)。這種輸入形式比較簡單、快捷。但需要非常熟悉函數(shù)名稱及其參數(shù)的輸入形式。所以，只有比較簡單的函數(shù)才用這種方法輸入；二是函數(shù)導入法。這是一種最為常用的辦法，它適合于所有函數(shù)的使用，而且在導入過程中有向?qū)崾?，因而非常方便。函?shù)的一般導入過程為：點菜單“插入”；找“函數(shù)”，此時出現(xiàn)一個“插入函數(shù)”對話框；在對話框的“選擇類別”中確定函數(shù)的類別（如常用函數(shù)或統(tǒng)計）；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱，如SUM、MODE等；點“確定”后即可出現(xiàn)該函數(shù)運算的對話框向?qū)?，再按向?qū)У奶崾就逻\行即可。,1眾數(shù),例：為了分析小麥的分蘗情況，進行了10株小麥的調(diào)查，如下圖所示：,（1）手工輸入函數(shù)名稱及參數(shù),單擊任一單元格，輸入“=MODE（B2：B11）”， 回車后即可得眾數(shù)為14。,（2）函數(shù)導入法,點菜單“插入”；找“函數(shù)”，此時出現(xiàn)一個“插入函數(shù)”對話框；在對話框的“選擇類別”中確定函數(shù)的類別“統(tǒng)計”；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱“MODE”，,點“確定”后即可出現(xiàn)該函數(shù)運算的對話框向?qū)?，在Number1處輸入B2：B11或選擇Excel中的B2：B11區(qū)域。按“確定”，在Excel中即得到眾數(shù)14。,2中位數(shù),單擊任一空白單元格， 輸入“=MEDIAN（B2:B11）”， 回車后得中位數(shù)為14。,3算術平均數(shù),單擊任一空白單元格， 輸入“=AVERAGE（B2:B11）”， 回車后得算術平均數(shù)為14.1。,4幾何平均數(shù),單擊任一空白單元格， 輸入“=GEOMEAN（B2:B11）”， 回車后得幾何平均數(shù)為14.02。,5調(diào)和平均數(shù),單擊任一空白單元格， 輸入“=HARMEAN（B2:B11）”， 回車后得調(diào)和平均數(shù)為1.422。,6截尾平均數(shù),將數(shù)據(jù)按由小到大順序排列后，因數(shù)據(jù)兩端值不夠穩(wěn)定，按一定比例去掉頭尾兩端一定數(shù)量的觀察值，然后再求平均，這樣得到的平均數(shù)就是截尾平均數(shù)。如果按2/10，即從30個數(shù)據(jù)中去掉最大的一個值和最小的一個值，再求平均數(shù)。 單擊任一空白單元格，輸入“=TRIMMEAN（B2:B11，1/10）”，回車后得截尾平均數(shù)為14.1,7全距,單擊任一空白單元格， 輸入“=MAX（B2:B11）-MIN（B2:B11）”， 回車后得全距為5。,8標準差(估計樣本）,單擊任一空白單元格， 輸入“=STDEV（B2:B11）”， 回車后得標準差為1.524。,9標準差系數(shù),單擊任一空白單元格， 輸入“=STDEV（B2:B11）/AVERAGE（B2:B11）”， 回車后得標準差系數(shù)為01080,10偏度系數(shù),單擊任一空白單元格， 輸入“=SKEW（B2:B11）”， 回車后得偏度系數(shù)為-0.678。,11峰度系數(shù),單擊任一空白單元格， 輸入“=KURT（B2:B11）”， 回車后得峰度系數(shù)為0.6249。,常用的統(tǒng)計量函數(shù),由于公式執(zhí)行后顯示的是計算結(jié)果按Ctrl+、鍵(位于鍵盤左上角)可使公式在顯示公式內(nèi)容與顯示公式結(jié)果之間切換,二、描述統(tǒng)計菜單項的使用,仍使用上面的例子 我們已經(jīng)把數(shù)據(jù)輸入到B2:B11單元格， 然后按以下步驟操作：,第一步：在工具菜單中選擇數(shù)據(jù)分析選項，從其對話框中選擇描述統(tǒng)計，按確定后打開描述統(tǒng)計對話框。,第二步：在輸入?yún)^(qū)域中輸入$B$1:$B$11，在輸出區(qū)域中選擇$F$1，其他復選框可根據(jù)需要選定，選擇匯總統(tǒng)計，可給出一系列描述統(tǒng)計量；選擇平均數(shù)置信度，會給出用樣本平均數(shù)估計總體平均數(shù)的置信區(qū)間；第K大值和第K小值會給出樣本中第K個大值和第K個小值。,第三步：單擊確定，可得輸出結(jié)果。,上面的結(jié)果中，平均指樣本均值；標準誤差指樣本平均數(shù)的標準差；中值即中位數(shù)；模式指眾數(shù)；標準偏差指樣本標準差，自由度為n-1；峰值即峰度系數(shù)；偏斜度即偏度系數(shù)；區(qū)域?qū)嶋H上是極差，或全距；可以看出與我們前面用函數(shù)計算的結(jié)果完全相同。最大值為16，最小值為11，第三個最大值為15，第三個最小值為13。,1利用函數(shù)計算相關系數(shù) 2用相關系數(shù)宏計算相關系數(shù),利用EXCEL計算相關系數(shù),1利用函數(shù)計算相關系數(shù),在Excel中，提供了兩個計算兩個變量之間相關系數(shù)的方法，CORREL函數(shù)和PERSON函數(shù)，這兩個函數(shù)是等價的，這里我們介紹用CORREL函數(shù)計算相關系數(shù)。 第一步：單擊任一個空白單元格，單擊“插入”菜單，選擇“函數(shù)”，打開“插入函數(shù)”對話框，在“選擇類別”中選擇“統(tǒng)計”；在“選擇函數(shù)”中選擇“CORREL”，單擊確定后，出現(xiàn)CORREL對話框。 第二步：在array1中輸入B3：B10，在array2中輸入C3：C10，即可在對話框下方顯示出計算結(jié)果為0.77723。,2用相關系數(shù)宏計算相關系數(shù),第一步：單擊“工具”菜單，選擇“數(shù)據(jù)分析” 項，在“數(shù)據(jù)分析”項中選擇“相關系數(shù)”，彈出 “相關系數(shù)”對話框。,第二步：在“輸入?yún)^(qū)域”輸入$B$2:$C$10，“分組方式”選擇逐列，選擇“標志位于第一行”，在“輸出區(qū)域”中輸入$I$3，單擊“確定” 。,用Excel進行回歸分析,Excel進行回歸分析同樣分函數(shù)和回歸分析宏兩種形式，其提供了9個函數(shù)用于建立回歸模型和預測，這9個函數(shù)分別是： 1INTERCEPT返回線性回歸模型的截距 2SLOPE返回線性回歸模型的斜率 3RSQ返回線性回歸模型的判定系數(shù) 4FORECAST返回一元線性回歸模型的預測值 5STEYX計算估計的標準誤差 6TREND計算線性回歸線的趨勢值 7GROWTH返回指數(shù)曲線的趨勢值 8LINEST返回線性回歸模型的參數(shù) 9LOGEST返回指數(shù)曲線模型的參數(shù) 用函數(shù)進行回歸分析比較麻煩，這里介紹使用回歸分析宏進行回歸分析。,第一步：單擊“工具”菜單，選擇“數(shù)據(jù)分析”選項，出現(xiàn)“數(shù)據(jù)分析”對話框，在分析工具中選擇“回歸”。,用Excel進行回歸分析,第二步：單擊“確定”按鈕，彈出“回歸”對話框，在“Y值輸入?yún)^(qū)域”輸入$B$1：$B$11；在“X值輸入?yún)^(qū)域”輸入$C$1:$C$11，在“輸出選項”選擇“$E$1”，如下圖所示。,多元回歸分析,用Excel進行回歸分析,第三步：單擊確定按鈕，得回歸分析結(jié)果如下圖所示。,用Excel進行回歸分析,Excel回歸分析工具的輸出結(jié)果包括3個部分： (1)回歸統(tǒng)計表 回歸統(tǒng)計表包括以下幾部分內(nèi)容： Multiple R（復相關系數(shù)R）：是R2的平方根，又稱為相關系數(shù)，用來衡量變量x和y之間相關程度的大小。本例中R為0.6313，表示二者之間的關系是正相關。 R Square（復測定系數(shù)R2）：用來說明自變量解釋因變量變差的程度，以測定因變量y的擬合效果。,回歸分析工具的輸出解釋,Adjusted R Square（調(diào)整復測定系數(shù)R2）：僅用于多元回歸才有意義，它用于衡量加入獨立變量后模型的擬合程度。當有新的獨立變量加入后，即使這一變量同因變量之間不相關，未經(jīng)修正的R2也要增大，修正的R2僅用于比較含有同一個因變量的各種模型。 標準誤差：用來衡量擬合程度的大小，也用于計算與回歸相關的其他統(tǒng)計量，此值越小，說明擬合程度越好。 觀測值：用于估計回歸方程的數(shù)據(jù)的觀測值個數(shù)。,回歸分析工具的輸出解釋,（2）方差分析表 方差分析表的主要作用是通過F檢驗來判斷回歸模型的回歸效果。表中“回歸分析”行計算的是估計值同均值之差（-）的各項指標；“殘差”行是用于計算每個樣本觀察值與估計值之差（-）的各項指標；“總計”行用于計算每個值同均值之差（-）的各項指標。第二列df是自由度，第三列SS是離差的平方和，第四列MS是均方差，它是離差平方和除以自由度，第五列是F統(tǒng)計量，第六列Significance F是在顯著性水平下的F的臨界值,用Excel進行回歸分析,冪函數(shù)回歸,基本形式,JENSEN模型（水分生產(chǎn)函數(shù)）,兩邊同時取對數(shù)轉(zhuǎn)化為線性形式,用Excel進行回歸分析,用Excel進行回歸分析,用Excel進行回歸分析,用Excel進行回歸分析,指數(shù)模型