在描述統(tǒng)計(jì)相關(guān)系數(shù)與回歸分析中的應(yīng)用詳解

在描述統(tǒng)計(jì)相關(guān)系數(shù)與回歸分析中的應(yīng)用當(dāng)前1頁(yè)，總共49頁(yè)。用Excel計(jì)算描述統(tǒng)計(jì)量用Excel進(jìn)行相關(guān)分析用Excel進(jìn)行回歸分析用Excel進(jìn)行相關(guān)與回歸分析當(dāng)前2頁(yè)，總共49頁(yè)。用Excel計(jì)算描述統(tǒng)計(jì)量用函數(shù)計(jì)算描述統(tǒng)計(jì)量描述統(tǒng)計(jì)菜單項(xiàng)的使用當(dāng)前3頁(yè)，總共49頁(yè)。常用的描述統(tǒng)計(jì)量有眾數(shù)、中位數(shù)、算術(shù)平均數(shù)、調(diào)和平均數(shù)、幾何平均數(shù)、極差、四分位差、標(biāo)準(zhǔn)差、方差、標(biāo)準(zhǔn)差系數(shù)等。下面介紹如何用函數(shù)來(lái)計(jì)算描述統(tǒng)計(jì)量。一、用函數(shù)計(jì)算描述統(tǒng)計(jì)量當(dāng)前4頁(yè)，總共49頁(yè)。用函數(shù)運(yùn)算有兩種方法：一是手工輸入函數(shù)名稱(chēng)及參數(shù)。這種輸入形式比較簡(jiǎn)單、快捷。但需要非常熟悉函數(shù)名稱(chēng)及其參數(shù)的輸入形式。所以，只有比較簡(jiǎn)單的函數(shù)才用這種方法輸入；二是函數(shù)導(dǎo)入法。這是一種最為常用的辦法，它適合于所有函數(shù)的使用，而且在導(dǎo)入過(guò)程中有向?qū)崾?，因而非常方便。函?shù)的一般導(dǎo)入過(guò)程為：點(diǎn)菜單“插入”；找“函數(shù)”，此時(shí)出現(xiàn)一個(gè)“插入函數(shù)”對(duì)話(huà)框；在對(duì)話(huà)框的“選擇類(lèi)別”中確定函數(shù)的類(lèi)別（如常用函數(shù)或統(tǒng)計(jì)）；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱(chēng)，如SUM、MODE等；點(diǎn)“確定”后即可出現(xiàn)該函數(shù)運(yùn)算的對(duì)話(huà)框向?qū)?，再按向?qū)У奶崾就逻\(yùn)行即可。當(dāng)前5頁(yè)，總共49頁(yè)。1．眾數(shù)

例：為了分析小麥的分蘗情況，進(jìn)行了10株小麥的調(diào)查，如下圖所示：當(dāng)前6頁(yè)，總共49頁(yè)。（1）手工輸入函數(shù)名稱(chēng)及參數(shù)單擊任一單元格，輸入“=MODE（B2：B11）”，回車(chē)后即可得眾數(shù)為14。當(dāng)前7頁(yè)，總共49頁(yè)。（2）函數(shù)導(dǎo)入法點(diǎn)菜單“插入”；找“函數(shù)”，此時(shí)出現(xiàn)一個(gè)“插入函數(shù)”對(duì)話(huà)框；在對(duì)話(huà)框的“選擇類(lèi)別”中確定函數(shù)的類(lèi)別“統(tǒng)計(jì)”；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱(chēng)“MODE”，當(dāng)前8頁(yè)，總共49頁(yè)。點(diǎn)“確定”后即可出現(xiàn)該函數(shù)運(yùn)算的對(duì)話(huà)框向?qū)?，在Number1處輸入B2：B11或選擇Excel中的B2：B11區(qū)域。按“確定”，在Excel中即得到眾數(shù)14。當(dāng)前9頁(yè)，總共49頁(yè)。2．中位數(shù)單擊任一空白單元格，輸入“=MEDIAN（B2:B11）”，回車(chē)后得中位數(shù)為14。當(dāng)前10頁(yè)，總共49頁(yè)。3．算術(shù)平均數(shù)單擊任一空白單元格，輸入“=AVERAGE（B2:B11）”，回車(chē)后得算術(shù)平均數(shù)為14.1。當(dāng)前11頁(yè)，總共49頁(yè)。4．幾何平均數(shù)單擊任一空白單元格，輸入“=GEOMEAN（B2:B11）”，回車(chē)后得幾何平均數(shù)為14.02。當(dāng)前12頁(yè)，總共49頁(yè)。5．調(diào)和平均數(shù)單擊任一空白單元格，輸入“=HARMEAN（B2:B11）”，回車(chē)后得調(diào)和平均數(shù)為1.422。當(dāng)前13頁(yè)，總共49頁(yè)。6．截尾平均數(shù)將數(shù)據(jù)按由小到大順序排列后，因數(shù)據(jù)兩端值不夠穩(wěn)定，按一定比例去掉頭尾兩端一定數(shù)量的觀(guān)察值，然后再求平均，這樣得到的平均數(shù)就是截尾平均數(shù)。如果按2/10，即從30個(gè)數(shù)據(jù)中去掉最大的一個(gè)值和最小的一個(gè)值，再求平均數(shù)。單擊任一空白單元格，輸入“=TRIMMEAN（B2:B11，1/10）”，回車(chē)后得截尾平均數(shù)為14.1當(dāng)前14頁(yè)，總共49頁(yè)。7．全距單擊任一空白單元格，輸入“=MAX（B2:B11）-MIN（B2:B11）”，回車(chē)后得全距為5。當(dāng)前15頁(yè)，總共49頁(yè)。8．標(biāo)準(zhǔn)差(估計(jì)樣本）單擊任一空白單元格，輸入“=STDEV（B2:B11）”，回車(chē)后得標(biāo)準(zhǔn)差為1.524。當(dāng)前16頁(yè)，總共49頁(yè)。9．標(biāo)準(zhǔn)差系數(shù)單擊任一空白單元格，輸入“=STDEV（B2:B11）/AVERAGE（B2:B11）”，回車(chē)后得標(biāo)準(zhǔn)差系數(shù)為0．1080當(dāng)前17頁(yè)，總共49頁(yè)。10．偏度系數(shù)單擊任一空白單元格，輸入“=SKEW（B2:B11）”，回車(chē)后得偏度系數(shù)為-0.678。當(dāng)前18頁(yè)，總共49頁(yè)。11．峰度系數(shù)單擊任一空白單元格，輸入“=KURT（B2:B11）”，回車(chē)后得峰度系數(shù)為0.6249。當(dāng)前19頁(yè)，總共49頁(yè)。常用的統(tǒng)計(jì)量函數(shù)由于公式執(zhí)行后顯示的是計(jì)算結(jié)果．按Ctrl+、鍵(位于鍵盤(pán)左上角)．可使公式在顯示公式內(nèi)容與顯示公式結(jié)果之間切換．當(dāng)前20頁(yè)，總共49頁(yè)。二、描述統(tǒng)計(jì)菜單項(xiàng)的使用仍使用上面的例子我們已經(jīng)把數(shù)據(jù)輸入到B2:B11單元格，然后按以下步驟操作：當(dāng)前21頁(yè)，總共49頁(yè)。第一步：在工具菜單中選擇數(shù)據(jù)分析選項(xiàng)，從其對(duì)話(huà)框中選擇描述統(tǒng)計(jì)，按確定后打開(kāi)描述統(tǒng)計(jì)對(duì)話(huà)框。當(dāng)前22頁(yè)，總共49頁(yè)。第二步：在輸入?yún)^(qū)域中輸入$B$1:$B$11，在輸出區(qū)域中選擇$F$1，其他復(fù)選框可根據(jù)需要選定，選擇匯總統(tǒng)計(jì)，可給出一系列描述統(tǒng)計(jì)量；選擇平均數(shù)置信度，會(huì)給出用樣本平均數(shù)估計(jì)總體平均數(shù)的置信區(qū)間；第K大值和第K小值會(huì)給出樣本中第K個(gè)大值和第K個(gè)小值。當(dāng)前23頁(yè)，總共49頁(yè)。第三步：?jiǎn)螕舸_定，可得輸出結(jié)果。當(dāng)前24頁(yè)，總共49頁(yè)。上面的結(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。當(dāng)前25頁(yè)，總共49頁(yè)。1．利用函數(shù)計(jì)算相關(guān)系數(shù)2．用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù)利用EXCEL計(jì)算相關(guān)系數(shù)當(dāng)前26頁(yè)，總共49頁(yè)。1．利用函數(shù)計(jì)算相關(guān)系數(shù)在Excel中，提供了兩個(gè)計(jì)算兩個(gè)變量之間相關(guān)系數(shù)的方法，CORREL函數(shù)和PERSON函數(shù)，這兩個(gè)函數(shù)是等價(jià)的，這里我們介紹用CORREL函數(shù)計(jì)算相關(guān)系數(shù)。第一步：?jiǎn)螕羧我粋€(gè)空白單元格，單擊“插入”菜單，選擇“函數(shù)”，打開(kāi)“插入函數(shù)”對(duì)話(huà)框，在“選擇類(lèi)別”中選擇“統(tǒng)計(jì)”；在“選擇函數(shù)”中選擇“CORREL”，單擊確定后，出現(xiàn)CORREL對(duì)話(huà)框。第二步：在array1中輸入B3：B10，在array2中輸入C3：C10，即可在對(duì)話(huà)框下方顯示出計(jì)算結(jié)果為0.77723。當(dāng)前27頁(yè)，總共49頁(yè)。當(dāng)前28頁(yè)，總共49頁(yè)。2．用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù)第一步：?jiǎn)螕簟肮ぞ摺辈藛?，選擇“數(shù)據(jù)分析”項(xiàng)，在“數(shù)據(jù)分析”項(xiàng)中選擇“相關(guān)系數(shù)”，彈出“相關(guān)系數(shù)”對(duì)話(huà)框。當(dāng)前29頁(yè)，總共49頁(yè)。

第二步：在“輸入?yún)^(qū)域”輸入$B$2:$C$10，“分組方式”選擇逐列，選擇“標(biāo)志位于第一行”，在“輸出區(qū)域”中輸入$I$3，單擊“確定”。當(dāng)前30頁(yè)，總共49頁(yè)。用Excel進(jìn)行回歸分析Excel進(jìn)行回歸分析同樣分函數(shù)和回歸分析宏兩種形式，其提供了9個(gè)函數(shù)用于建立回歸模型和預(yù)測(cè)，這9個(gè)函數(shù)分別是：1．INTERCEPT返回線(xiàn)性回歸模型的截距2．SLOPE返回線(xiàn)性回歸模型的斜率3．RSQ返回線(xiàn)性回歸模型的判定系數(shù)4．FORECAST返回一元線(xiàn)性回歸模型的預(yù)測(cè)值5．STEYX計(jì)算估計(jì)的標(biāo)準(zhǔn)誤差6．TREND計(jì)算線(xiàn)性回歸線(xiàn)的趨勢(shì)值7．GROWTH返回指數(shù)曲線(xiàn)的趨勢(shì)值8．LINEST返回線(xiàn)性回歸模型的參數(shù)9．LOGEST返回指數(shù)曲線(xiàn)模型的參數(shù)用函數(shù)進(jìn)行回歸分析比較麻煩，這里介紹使用回歸分析宏進(jìn)行回歸分析。當(dāng)前31頁(yè)，總共49頁(yè)。第一步：?jiǎn)螕簟肮ぞ摺辈藛?，選擇“數(shù)據(jù)分析”選項(xiàng)，出現(xiàn)“數(shù)據(jù)分析”對(duì)話(huà)框，在分析工具中選擇“回歸”。用Excel進(jìn)行回歸分析當(dāng)前32頁(yè)，總共49頁(yè)。第二步：?jiǎn)螕簟按_定”按鈕，彈出“回歸”對(duì)話(huà)框，在“Y值輸入?yún)^(qū)域”輸入$B$1：$B$11；在“X值輸入?yún)^(qū)域”輸入$C$1:$C$11，在“輸出選項(xiàng)”選擇“$E$1”，如下圖所示。多元回歸分析用Excel進(jìn)行回歸分析當(dāng)前33頁(yè)，總共49頁(yè)。

第三步：?jiǎn)螕舸_定按鈕，得回歸分析結(jié)果如下圖所示。用Excel進(jìn)行回歸分析當(dāng)前34頁(yè)，總共49頁(yè)。Excel回歸分析工具的輸出結(jié)果包括3個(gè)部分：(1)回歸統(tǒng)計(jì)表回歸統(tǒng)計(jì)表包括以下幾部分內(nèi)容：①M(fèi)ultipleR（復(fù)相關(guān)系數(shù)R）：是R2的平方根，又稱(chēng)為相關(guān)系數(shù)，用來(lái)衡量變量x和y之間相關(guān)程度的大小。本例中R為0.6313，表示二者之間的關(guān)系是正相關(guān)。②RSquare（復(fù)測(cè)定系數(shù)R2）：用來(lái)說(shuō)明自變量解釋因變量變差的程度，以測(cè)定因變量y的擬合效果?；貧w分析工具的輸出解釋當(dāng)前35頁(yè)，總共49頁(yè)。③AdjustedRSquare（調(diào)整復(fù)測(cè)定系數(shù)R2）：僅用于多元回歸才有意義，它用于衡量加入獨(dú)立變量后模型的擬合程度。當(dāng)有新的獨(dú)立變量加入后，即使這一變量同因變量之間不相關(guān)，未經(jīng)修正的R2也要增大，修正的R2僅用于比較含有同一個(gè)因變量的各種模型。④標(biāo)準(zhǔn)誤差：用來(lái)衡量擬合程度的大小，也用于計(jì)算與回歸相關(guān)的其他統(tǒng)計(jì)量，此值越小，說(shuō)明擬合程度越好。⑤觀(guān)測(cè)值：用于估計(jì)回歸方程的數(shù)據(jù)的觀(guān)測(cè)值個(gè)數(shù)?；貧w分析工具的輸出解釋當(dāng)前36頁(yè)，總共49頁(yè)。（2）方差分析表方差分析表的主要作用是通過(guò)F檢驗(yàn)來(lái)判斷回歸模型的回歸效果。表中“回歸分析”行計(jì)算的是估計(jì)值同均值之差（-）的各項(xiàng)指標(biāo)；“殘差”行是用于計(jì)算每個(gè)樣本觀(guān)察值與估計(jì)值之差（-）的各項(xiàng)指標(biāo)；“總計(jì)”行用于計(jì)算每個(gè)值同均值之差（-）的各項(xiàng)指標(biāo)。第二列df是自由度，第三列SS是離差的平方和，第四列MS是均方差，它是離差平方和除以自由度，第五列是F統(tǒng)計(jì)量，第六列SignificanceF是在顯著性水平下的Fα的臨界值當(dāng)前37頁(yè)，總共49頁(yè)。用Excel進(jìn)行回歸分析冪函數(shù)回歸基本形式JENSEN模型（水分生產(chǎn)函數(shù)）兩邊同時(shí)取對(duì)數(shù)轉(zhuǎn)化為線(xiàn)性形式當(dāng)前38頁(yè)，總共49頁(yè)。用Excel進(jìn)行回歸分析當(dāng)前39頁(yè)，總共49頁(yè)。用Excel進(jìn)行回歸分析當(dāng)前40頁(yè)，總共49頁(yè)。用Excel進(jìn)行回歸分析當(dāng)前41頁(yè)，總共49頁(yè)。用Excel進(jìn)行回歸分析指數(shù)模型基本形式兩邊取對(duì)數(shù)對(duì)因變量取對(duì)數(shù)對(duì)截距反對(duì)數(shù)當(dāng)前42頁(yè)，總共49頁(yè)。用Excel進(jìn)行回歸分析當(dāng)