1、 用Excel計(jì)算描述統(tǒng)計(jì)量 用Excel進(jìn)行相關(guān)分析(fnx) 用Excel進(jìn)行回歸分析(fnx)用Excel進(jìn)行(jnxng)相關(guān)與回歸分析第1頁/共50頁第一頁，共51頁。用Excel計(jì)算(j sun)描述統(tǒng)計(jì)量用函數(shù)(hnsh)計(jì)算描述統(tǒng)計(jì)量描述統(tǒng)計(jì)菜單項(xiàng)的使用第2頁/共50頁第二頁，共51頁。 常用的描述統(tǒng)計(jì)量有眾數(shù)、中位數(shù)、算術(shù)平均數(shù)、調(diào)和(tio h)平均數(shù)、幾何平均數(shù)、極差、四分位差、標(biāo)準(zhǔn)差、方差、標(biāo)準(zhǔn)差系數(shù)等。下面介紹如何用函數(shù)來計(jì)算描述統(tǒng)計(jì)量。一、用函數(shù)(hnsh)計(jì)算描述統(tǒng)計(jì)量第3頁/共50頁第三頁，共51頁。 用函數(shù)運(yùn)算有兩種方法：一是手工輸入函數(shù)名稱及參數(shù)。這種輸入形

2、式比較簡單、快捷。但需要非常熟悉函數(shù)名稱及其參數(shù)的輸入形式。所以，只有比較簡單的函數(shù)才用這種方法輸入；二是函數(shù)導(dǎo)入法。這是一種最為常用的辦法(bnf)，它適合于所有函數(shù)的使用，而且在導(dǎo)入過程中有向?qū)崾荆蚨浅７奖?。函?shù)的一般導(dǎo)入過程為：點(diǎn)菜單“插入”；找“函數(shù)”，此時(shí)出現(xiàn)一個“插入函數(shù)”對話框；在對話框的“選擇類別”中確定函數(shù)的類別（如常用函數(shù)或統(tǒng)計(jì)）；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱，如SUM、MODE等；點(diǎn)“確定”后即可出現(xiàn)該函數(shù)運(yùn)算的對話框向?qū)?，再按向?qū)У奶崾就逻\(yùn)行即可。第4頁/共50頁第四頁，共51頁。1眾數(shù)(zhn sh) 例：為了分析小麥的分蘗情況，進(jìn)行了10株小麥的調(diào)查(

3、dio ch)，如下圖所示：第5頁/共50頁第五頁，共51頁。（1）手工輸入函數(shù)名稱(mngchng)及參數(shù)單擊任一單元格，輸入“=MODE（B2：B11）”，回車(hu ch)后即可得眾數(shù)為14。第6頁/共50頁第六頁，共51頁。（2）函數(shù)(hnsh)導(dǎo)入法點(diǎn)菜單“插入”；找“函數(shù)”，此時(shí)出現(xiàn)一個“插入函數(shù)”對話框；在對話框的“選擇類別”中確定(qudng)函數(shù)的類別“統(tǒng)計(jì)”；在“選擇函數(shù)”內(nèi)確定(qudng)欲選的函數(shù)名稱“MODE”，第7頁/共50頁第七頁，共51頁。點(diǎn)“確定”后即可出現(xiàn)該函數(shù)運(yùn)算的對話框向?qū)?xingdo)，在Number1處輸入B2：B11或選擇Excel中的B2：B

4、11區(qū)域。按“確定”，在Excel中即得到眾數(shù)14。第8頁/共50頁第八頁，共51頁。2中位數(shù)單擊任一空白單元格，輸入(shr)“=MEDIAN（B2:B11）”，回車后得中位數(shù)為14。第9頁/共50頁第九頁，共51頁。3算術(shù)(sunsh)平均數(shù)單擊任一空白單元格，輸入“=AVERAGE（B2:B11）”，回車(hu ch)后得算術(shù)平均數(shù)為14.1。第10頁/共50頁第十頁，共51頁。4幾何(j h)平均數(shù)單擊任一空白單元格，輸入“=GEOMEAN（B2:B11）”，回車(hu ch)后得幾何平均數(shù)為14.02。第11頁/共50頁第十一頁，共51頁。5調(diào)和(tio h)平均數(shù)單擊任一空白(kn

5、gbi)單元格，輸入“=HARMEAN（B2:B11）”，回車后得調(diào)和平均數(shù)為1.422。第12頁/共50頁第十二頁，共51頁。6截尾(ji wi)平均數(shù) 將數(shù)據(jù)按由小到大順序排列后，因數(shù)據(jù)兩端值不夠穩(wěn)定，按一定比例去掉頭尾兩端一定數(shù)量的觀察值，然后再求平均，這樣得到的平均數(shù)就是(jish)截尾平均數(shù)。如果按2/10，即從30個數(shù)據(jù)中去掉最大的一個值和最小的一個值，再求平均數(shù)。 單擊任一空白單元格，輸入“=TRIMMEAN（B2:B11，1/10）”，回車后得截尾平均數(shù)為14.1第13頁/共50頁第十三頁，共51頁。7全距 單擊任一空白(kngbi)單元格， 輸入“=MAX（B2:B11）-M

6、IN（B2:B11）”， 回車后得全距為5。第14頁/共50頁第十四頁，共51頁。8標(biāo)準(zhǔn)差(估計(jì)(gj)樣本）單擊任一空白(kngbi)單元格，輸入“=STDEV（B2:B11）”，回車后得標(biāo)準(zhǔn)差為1.524。第15頁/共50頁第十五頁，共51頁。9標(biāo)準(zhǔn)差系數(shù)(xsh)單擊任一空白單元格，輸入(shr)“=STDEV（B2:B11）/AVERAGE（B2:B11）”，回車后得標(biāo)準(zhǔn)差系數(shù)為01080第16頁/共50頁第十六頁，共51頁。10偏度系數(shù)(xsh)單擊任一空白單元格，輸入(shr)“=SKEW（B2:B11）”，回車后得偏度系數(shù)為-0.678。第17頁/共50頁第十七頁，共51頁。11

7、峰度系數(shù)(xsh)單擊任一空白單元格，輸入“=KURT（B2:B11）”，回車(hu ch)后得峰度系數(shù)為0.6249。第18頁/共50頁第十八頁，共51頁。常用(chn yn)的統(tǒng)計(jì)量函數(shù)由于公式執(zhí)行后顯示的是計(jì)算結(jié)果按Ctrl+、鍵(位于(wiy)鍵盤左上角)可使公式在顯示公式內(nèi)容與顯示公式結(jié)果之間切換 第19頁/共50頁第十九頁，共51頁。二、描述統(tǒng)計(jì)菜單項(xiàng)的使用(shyng)仍使用上面的例子我們(w men)已經(jīng)把數(shù)據(jù)輸入到B2:B11單元格，然后按以下步驟操作：第20頁/共50頁第二十頁，共51頁。第一步：在工具(gngj)菜單中選擇數(shù)據(jù)分析選項(xiàng)，從其對話框中選擇描述統(tǒng)計(jì)，按確定后打

8、開描述統(tǒng)計(jì)對話框。第21頁/共50頁第二十一頁，共51頁。第二步：在輸入?yún)^(qū)域(qy)中輸入$B$1:$B$11，在輸出區(qū)域(qy)中選擇$F$1，其他復(fù)選框可根據(jù)需要選定，選擇匯總統(tǒng)計(jì)，可給出一系列描述統(tǒng)計(jì)量；選擇平均數(shù)置信度，會給出用樣本平均數(shù)估計(jì)總體平均數(shù)的置信區(qū)間；第K大值和第K小值會給出樣本中第K個大值和第K個小值。第22頁/共50頁第二十二頁，共51頁。第三步：單擊確定(qudng)，可得輸出結(jié)果。第23頁/共50頁第二十三頁，共51頁。上面的結(jié)果中，平均指樣本均值；標(biāo)準(zhǔn)誤差指樣本平均數(shù)的標(biāo)準(zhǔn)差；中值即中位數(shù)；模式指眾數(shù)；標(biāo)準(zhǔn)偏差指樣本標(biāo)準(zhǔn)差，自由度為n-1；峰值(fn zh)即峰度

9、系數(shù)；偏斜度即偏度系數(shù)；區(qū)域?qū)嶋H上是極差，或全距；可以看出與我們前面用函數(shù)計(jì)算的結(jié)果完全相同。最大值為16，最小值為11，第三個最大值為15，第三個最小值為13。第24頁/共50頁第二十四頁，共51頁。 1利用函數(shù)(hnsh)計(jì)算相關(guān)系數(shù) 2用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù)利用(lyng)EXCEL計(jì)算相關(guān)系數(shù)第25頁/共50頁第二十五頁，共51頁。1利用(lyng)函數(shù)計(jì)算相關(guān)系數(shù) 在Excel中，提供了兩個計(jì)算兩個變量之間相關(guān)系數(shù)的方法，CORREL函數(shù)和PERSON函數(shù)，這兩個函數(shù)是等價(jià)的，這里我們介紹用CORREL函數(shù)計(jì)算相關(guān)系數(shù)。 第一步：單擊任一個空白單元格，單擊“插入”菜單，選擇“函數(shù)”

10、，打開“插入函數(shù)”對話框，在“選擇類別”中選擇“統(tǒng)計(jì)”；在“選擇函數(shù)”中選擇“CORREL”，單擊確定(qudng)后，出現(xiàn)CORREL對話框。 第二步：在array1中輸入B3：B10，在array2中輸入C3：C10，即可在對話框下方顯示出計(jì)算結(jié)果為0.77723。第26頁/共50頁第二十六頁，共51頁。第27頁/共50頁第二十七頁，共51頁。2用相關(guān)系數(shù)宏計(jì)算(j sun)相關(guān)系數(shù)第一步：單擊“工具”菜單(ci dn)，選擇“數(shù)據(jù)分析”項(xiàng)，在“數(shù)據(jù)分析”項(xiàng)中選擇“相關(guān)系數(shù)”，彈出“相關(guān)系數(shù)”對話框。第28頁/共50頁第二十八頁，共51頁。 第二步：在“輸入?yún)^(qū)域(qy)”輸入$B$2:$C

11、$10，“分組方式”選擇逐列，選擇“標(biāo)志位于第一行”，在“輸出區(qū)域(qy)”中輸入$I$3，單擊“確定” 。第29頁/共50頁第二十九頁，共51頁。 Excel進(jìn)行回歸分析同樣分函數(shù)和回歸分析宏兩種形式，其提供了9個函數(shù)用于建立回歸模型(mxng)和預(yù)測，這9個函數(shù)分別是： 1INTERCEPT返回線性回歸模型(mxng)的截距 2SLOPE返回線性回歸模型(mxng)的斜率 3RSQ返回線性回歸模型(mxng)的判定系數(shù) 4FORECAST返回一元線性回歸模型(mxng)的預(yù)測值 5STEYX計(jì)算估計(jì)的標(biāo)準(zhǔn)誤差 6TREND計(jì)算線性回歸線的趨勢值 7GROWTH返回指數(shù)曲線的趨勢值 8LIN

12、EST返回線性回歸模型(mxng)的參數(shù) 9LOGEST返回指數(shù)曲線模型(mxng)的參數(shù) 用函數(shù)進(jìn)行回歸分析比較麻煩，這里介紹使用回歸分析宏進(jìn)行回歸分析。第30頁/共50頁第三十頁，共51頁。第一步：單擊“工具”菜單，選擇“數(shù)據(jù)分析”選項(xiàng)，出現(xiàn)(chxin)“數(shù)據(jù)分析”對話框，在分析工具中選擇“回歸”。第31頁/共50頁第三十一頁，共51頁。第二步：單擊“確定”按鈕，彈出“回歸”對話框，在“Y值輸入?yún)^(qū)域”輸入$B$1：$B$11；在“X值輸入?yún)^(qū)域”輸入$C$1:$C$11，在“輸出選項(xiàng)”選擇(xunz)“$E$1”，如下圖所示。第32頁/共50頁第三十二頁，共51頁。 第三步：單擊確定按鈕，

13、得回歸分析(fnx)結(jié)果如下圖所示。第33頁/共50頁第三十三頁，共51頁。Excel回歸分析工具的輸出結(jié)果包括3個部分：(1)回歸統(tǒng)計(jì)表回歸統(tǒng)計(jì)表包括以下幾部分內(nèi)容：Multiple R（復(fù)相關(guān)系數(shù)R）：是R2的平方根，又稱為相關(guān)系數(shù)，用來(yn li)衡量變量x和y之間相關(guān)程度的大小。本例中R為0.6313，表示二者之間的關(guān)系是正相關(guān)。 R Square（復(fù)測定系數(shù)R2）：用來(yn li)說明自變量解釋因變量變差的程度，以測定因變量y的擬合效果。 第34頁/共50頁第三十四頁，共51頁。第35頁/共50頁第三十五頁，共51頁。 （2 2）方差分析表 方差分析表的主要作用是通過F F檢驗(yàn)來

14、判斷回歸模型的回歸效果。表中“回歸分析”行計(jì)算的是估計(jì)值同均值之差（- -）的各項(xiàng)指標(biāo)；“殘差”行是用于計(jì)算每個樣本觀察(gunch)(gunch)值與估計(jì)值之差（- -）的各項(xiàng)指標(biāo)；“總計(jì)”行用于計(jì)算每個值同均值之差（- -）的各項(xiàng)指標(biāo)。第二列dfdf是自由度，第三列SSSS是離差的平方和，第四列MSMS是均方差，它是離差平方和除以自由度，第五列是F F統(tǒng)計(jì)量，第六列Significance FSignificance F是在顯著性水平下的FF的臨界值 第36頁/共50頁第三十六頁，共51頁。inicmicimETETYY)(1dbxxy21)(ln()ln()ln(21xdxby基本(jb

15、n)形式JENSEN模型（水分生產(chǎn)函數(shù)）兩邊同時(shí)取對數(shù)轉(zhuǎn)化為線性形式第37頁/共50頁第三十七頁，共51頁。第38頁/共50頁第三十八頁，共51頁。第39頁/共50頁第三十九頁，共51頁。第40頁/共50頁第四十頁，共51頁。bxcey 基本(jbn)形式兩邊取對數(shù)bxcy)ln()ln(對因變量取對數(shù)對截距反對數(shù)第41頁/共50頁第四十一頁，共51頁。第42頁/共50頁第四十二頁，共51頁。第43頁/共50頁第四十三頁，共51頁。)ln(xbay基本(jbn)形式對自變量取對數(shù)第44頁/共50頁第四十四頁，共51頁。第45頁/共50頁第四十五頁，共51頁。產(chǎn)量(chnling)與需水量之間的關(guān)系：拋物線cbxaxY2把x2,x分別看作變量按多元回歸計(jì)算即可第46頁/共50頁第四十六頁，共51頁。第47頁/共50頁第四十七頁，共51頁。第48頁/共50頁第四十八頁，共51頁。第49頁/共50頁第四十九頁，共51頁。謝謝您的觀看(gunkn)！第50頁/共50頁