ecel在統(tǒng)計中的應用

1、Excel在統(tǒng)計中的應用MicrosoftExcel是美國微軟公司開發(fā)的Office辦公軟件中最重要的組件之一，它是目前應用最為廣泛的表格處理軟件之一。隨著Excel版本的不斷提高，統(tǒng)計分析功能也逐漸強大,目前通過加載宏添加的數(shù)據(jù)分析工具使復雜的統(tǒng)計分析變得快捷和易于計算完成。與標準統(tǒng)計學軟件SASSPS游相比較，Excel的最大優(yōu)勢在于普及率高、容易得到：正規(guī)渠道購買的電腦內大多數(shù)預裝有Office辦公軟件，且為中文界面，使用它進行統(tǒng)計分析物版權問題。強大的數(shù)據(jù)填充功能：這使在Excel工作表中輸入數(shù)據(jù)變得相當簡單使用簡單，不用記許多特殊指令“數(shù)據(jù)分析”工具可完成大多數(shù)常用的統(tǒng)計分析：對于較

2、復雜的統(tǒng)計學方法可簡單利用內置函數(shù)自編程序完成其缺點：自動化程度不高，功能不夠強大，有些統(tǒng)計計算不能直接完成。一利用分析工具進行描述性統(tǒng)計二樣本直方圖三假設檢驗四方差分析五相關與回歸一、利用分析工具進行描述性統(tǒng)計此分析工具用于對輸入?yún)^(qū)域中數(shù)據(jù)的單變量分析，并提供數(shù)據(jù)趨中性和易變性等有關信息。例1.某班20名學生考試成績單如下表，試用分析工具中的描述統(tǒng)計對班級成績進行分析匯總，并給出相關的統(tǒng)計指標。操作步驟：1.新建一個工作表表1?2.選擇“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇“描述統(tǒng)計”選項，單擊確定出現(xiàn)描述性統(tǒng)計對話筐；3.出現(xiàn)“描述統(tǒng)計”對話框，對話框內各選項的含義如下：輸入?yún)^(qū)域：在此輸入

3、待分析數(shù)據(jù)區(qū)域的單元格范圍。一般情況下Excel會自動根據(jù)當前單元格確定待分析數(shù)據(jù)區(qū)域。分組方式：如果需要指出輸入?yún)^(qū)域中的數(shù)據(jù)是按行還是按列排列，則單擊“行”或“列”，“描述統(tǒng)計”工具可以同時對多列或多行數(shù)據(jù)進行統(tǒng)計分析。標志位于第一行/列：如果輸入?yún)^(qū)域的第一行中包含標志項（變量名），則選中“標志位于第一行”復選框；如果輸入?yún)^(qū)域的第一列中包含標志項，則選中“標志位于第一列”復選框；如果輸入?yún)^(qū)域沒有標志項，則不需要選擇該復選框，Excel將在輸出表中生成適宜數(shù)據(jù)標志。輸出區(qū)域：填寫輸出結果表左上角單元格地址，用于控制輸出結果的存放位置。整個輸出結果分為兩歹0,左邊一歹0包含統(tǒng)計標志項，右邊一歹0

4、包含統(tǒng)計值。根據(jù)所選擇的“分組方式”選項的不同，Excel將為輸入表中的每一行或每一列生成一個兩列的統(tǒng)計表。新工作表：單擊此選項，可在當前工作簿中插入新工作表，并由新工作表的A1單元格開始存放計算結果。如果需要給新工作表命名，則在右側編輯框中鍵入名稱。新工作簿：單擊此選項，可創(chuàng)建一新工作簿，并在新工作簿的新工作表中存放計算結果。匯總統(tǒng)計：指定輸出表中生成以下統(tǒng)計結果，則選中此復選框。這些統(tǒng)計結果有：平均值、標準誤差、中值、眾數(shù)、標準偏差、方差、峰值、偏斜度、極差全距最小值、最大值、總和、樣本個數(shù)。均值置信度：假設需要輸出由樣本均值推斷總體均值的置信區(qū)問，則選中此復選框，然后在右側的編輯框中，輸

5、入所要使用的置信度。例如，假設置信度95咐算出的總體樣本均值置信區(qū)間為10,則表示：在5祁顯著水平下總體均值的置信區(qū)問為（-10,+10。第K個最大/小值：如果需要輸出每個區(qū)域的數(shù)據(jù)的第k個最大或最小值，則選中此復選框。然后在右側的編輯框中，輸入k的數(shù)值。4.填寫完“描述統(tǒng)計”對話框之后，按“確定”按扭即可。結果說明：描述統(tǒng)計工具可生成以下統(tǒng)計指標，按從上到下的順序其中包括樣本的平均值，標準誤差()，組中值(Medium)，眾數(shù)(Mode),樣本標準差(S),樣本方差(S2),峰度值，偏度值，極差(Max-Min),最小值(Min),最大值(Max)，樣本總和，樣本個數(shù)n和一定顯著水平下總體均

6、值的置信區(qū)問。二、樣本直方圖在實際的問題中，總體的分布情況往往是不活楚的，利用樣本資料通過作出適當?shù)慕y(tǒng)計圖作直觀觀查，當總體的數(shù)量指標是連續(xù)型隨機變量時，可作樣本頻率密度直方圖，作為總體概率密度函數(shù)的進似。例2.100包顆粒劑每包稱重如下，試推斷每包顆粒劑重量的概率分布情況。操作步驟：1.新建一個工作表表2表2中填入分好組：1找出樣本數(shù)據(jù)的最大值和最小值，這里是0.78和1.06;2確定分組的組距和組數(shù)?2.選擇“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇“直方圖”選項，單擊確定出現(xiàn)直方圖對話筐；3.出現(xiàn)“直方圖”對話框，對話框內主要選項的含義如下：輸入?yún)^(qū)域：在此輸入待分析數(shù)據(jù)區(qū)域的單元格范圍。接收

7、區(qū)域可選：在此輸入接收區(qū)域的單元格范圍，該區(qū)域應包含一組可選的用來計算頻數(shù)的邊界值。這些值應當按升序排列。只要存在的話，Excel將統(tǒng)計在各個相鄰邊界直之間的數(shù)據(jù)出現(xiàn)的次數(shù)。如果省略此處的接收區(qū)域，Excel將在數(shù)據(jù)組的最小值和最大值之間創(chuàng)建一組平滑分布的接收區(qū)間O標志：如果輸入?yún)^(qū)域的第一行或第一列中包含標志項，則選中此復選框；如果輸入?yún)^(qū)域沒有標志項，則活除此該復選框，Excel將在輸出表中生成適宜的數(shù)據(jù)標志。輸出區(qū)域：在此輸入結果輸出表的左上角單元格的地址用于控制計算結果的顯示位置。如果輸出表將覆蓋已有的數(shù)據(jù)，Excel會自動確定輸出區(qū)域的大小并顯示信息。柏拉圖：選中此復選框，可以在輸出表中

8、同時顯示按降序排列頻率數(shù)據(jù)。如果此復選框被活除，Excel將只按升序來排列數(shù)據(jù)。累積白分比：選中此復選框，可以在輸出結果中添加一列累積白分比數(shù)值，并同時在直方圖表中添加累積白分比折線。如果活除此選項，則會省略以上結果。圖表輸出：選中此復選框，可以在輸出表中同時生成一個嵌入式直方圖表。4.按需要填寫完“直方圖”對話框之后，按“確定”按扭即可。結果說明：完整的結果包括三列數(shù)據(jù)和一個頻率分布圖，第一列是數(shù)值的區(qū)間范圍，第二列是數(shù)值分布的頻數(shù)，第三列是頻數(shù)分布的累積白分比。三假設檢驗假設檢驗是統(tǒng)計推斷中的重要內容。假設檢驗主要分為兩種類型：雙側尾檢驗和單側尾檢驗。當需要檢驗：H0:(t1=(t2,HA

9、:。1丈(T2H0:(t1=(t2,HA:1<2H0:1=b2,HA:b1>b2一兩正態(tài)總體方差的假設檢驗對于來自兩個正態(tài)總體的樣本，其總體方差分別為b12和b22,從兩個總體中獨立地抽取容量為n1和n2的樣本，對應的樣本方差分別為S12和S22，假設需要檢驗b12=b22，則可利用分析工具的F檢驗。例3.合成車間某中問體生產的工藝條件改革后，收率似有提高，但工人師傅反映新工藝的條件不易控制，收率波動較大，為此，對新老工藝分別抽取假設十批，結果如表所示。試檢驗推斷老師傅反應的問題是否屆實。單擊“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇“F-檢驗一一雙樣本方差”結論：由統(tǒng)計數(shù)據(jù)知道樣本統(tǒng)

10、計量F=2.990066994，其對應的的概率P=0.06884784>0.05,說明抽樣的結果不是小概率事件，所以接受H0:bi2=b22拒絕HA(T12>b22說工工人師傅反映的問題顯著有誤的。二兩正態(tài)總體均數(shù)的假設檢驗1、配比照較例4、某中醫(yī)師用中藥宵木香治療高血壓患者，治療前后的情況下表所示。問該中藥治療高血壓是否有效？操作步驟：1.新建一個工作表表42.單擊“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇“t-檢驗一一平均值的成對二樣本分析”選項，單擊確定出現(xiàn)t-檢驗一一平均值的成對二樣本分析對話筐；3.在對話框中，按要求填入相應選項，單擊確定按鈕，輸出分析結果。5.結論：由統(tǒng)計數(shù)

11、據(jù)知道樣本統(tǒng)計量t=-5.81435,其對應的的概率P=0.000117<0.01,說明抽樣的結果是小概率事件，所以拒絕H011-以2=0,接受HA11-12丈0.說明該中藥對質量高血壓有極顯著效果。2、成組比較1已知b12=b22例5.例3中合成車間某中問體生產的工藝條件改革后，收率似有提高，為此，對新老工藝分別抽取假設十批，結果如表3所示。試推斷檢驗新老工藝的收率是否有差異。操作步驟：單擊“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇“t-檢驗一一雙樣本等方差假設”選項,5.結論：由統(tǒng)計數(shù)據(jù)知道樣本統(tǒng)計量t=-5.04748,其對應的的概率P=0.00009929.92E-05<0.0

12、1，說明抽樣的結果是小概率事件，所以拒絕H0:1-2=0，接受HA從1-從2豐0.說明新老工藝的收率有極顯著差異。2已知b12丈b22單擊“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇“t-檢驗一一雙樣本等方差假設”選項三單個正態(tài)總體的假設檢驗例6、根據(jù)大量調查，已知健康成年男子脈搏均數(shù)為72次/分。某醫(yī)生在某山區(qū)抽查了25名成年男子，其脈搏均數(shù)為74.2次/分，標準差為6.5次/分，能否據(jù)此認為該山區(qū)健康成年男子脈搏高于一般水平？操作步驟：1.在工作表中鍵入已知數(shù)據(jù)表52.計算t統(tǒng)計量及P值等：3. ?在G4中鍵入"=ABS(C4-C7)/D7*B7A0.5”；在G5中鍵入“=B7-1”求

13、自由度；在G6中鍵入"=TDIST(G4,G5,1),求單側P值；在G7中鍵入"=TDIST(G4,G5,2),求雙側P值；在G8中鍵入"=C7-C4'',求X-0;結果分析：根據(jù)專業(yè)知識，已知山區(qū)不會低于一般水平，故作單側檢驗，t值=1.692308,P值單側=0.052，按0.05水平不拒絕H0,可認為該山區(qū)健康成年男子脈搏與一般人群健康成年男子脈搏的差異無統(tǒng)計學意義。四方差分析單因素方差分析可用于檢驗兩個或兩個以上的總體均值相等的假設是否成立。該檢驗假定總體是服從正太分布的，總體方差是相等的，并且隨機樣本是獨立的。這種工具適用于完全隨機化試驗

14、的結果分析。例8.某公司研制出A,B,C,D4種新型壓片機，在單位時間內每臺壓片機進行了6次壓片操作，統(tǒng)計它們壓片數(shù)量如下表，試在a=0.01的顯著水平下檢驗這4種設備在相同時間生產的數(shù)量是否存在顯著差異。結果分析：按照如上的操作步驟即可得到計算結果。其中表格的第二部分則是方差分析的結果。SS列分別給出了四個分組的組問方差、組內方差以及總方差，DF列分別給出了對應方差的自由度，MS列是平均值方差，由SS除于DF得到，它是總體方差的兩個估計值。F列是F統(tǒng)計量的計算結果，如果四個總體均值相等的假設成立的化，它應該服從F分布，即近似為1,它是最終的計算結果，通過將它與一定置信水平下的F臨界值Fcri

15、t比較，可以判斷均值相等的假設是否成立，在本例中，1.67761<2.94668,所以不能拒絕四個總體均值相等的假設。P-value列，是單尾概率值，說明如果四個總體均值相等的假設成立的化，得到如上樣本結果的概率是19.442%，即得到以上樣本并不是小概率事件，同樣也得到不能拒絕四個總體均值相等的假設的結論。按相似方法可進行無重復雙因素方差分析，有重復雙因素方差分析。五相關與回歸相關分析按照討論的相關變量多少可分為簡單相關和多元相關乂稱復相關，簡單相關是指一個因變量與一個自變量的相關關系，而多元相關則是一個因變量與兩個或兩個以上自變得相關關系。這里著重討論如何應用EXCEI確定和度量變量

16、間的簡單線性相關與回歸分析。一散點圖對于兩變量相關關系確實定，可以采用散點圖的方法，通過在EXCEI重繪出兩變量的散點圖，根據(jù)散點分布確定兩變量的相關關系，當然散點圖僅能定性地確定出相關關系，不能給出定量的度量。例9用雙波長薄層掃描儀對紫草含量進行測定，得氣濃度c與測得積分值h的數(shù)據(jù)如下表。試作出濃度c與測得積分值h的散點圖。操作步驟：1.新建一個工作表表102.單擊“插入”菜單的“圖表”子菜單，選擇“XY-散點圖”選項，在“子圖表類型”選定最上段類型，單擊“下一步”3.出現(xiàn)“源數(shù)據(jù)”-選定“數(shù)據(jù)區(qū)域”-“列”-“下一步”4.在“圖表向導4步驟3”對話筐中，單擊“網格線”-刪去“主要網格線”-

17、單擊“下一步”輸出結果。二相關系數(shù)使用“相關系數(shù)”分析工具來確定兩個區(qū)域中數(shù)據(jù)的變化是否相關，即，一個集合的較大數(shù)據(jù)是否與另一個集合的較大數(shù)據(jù)相對應正相關；或者一個集合的較小數(shù)據(jù)是否與另一個集合的較小數(shù)據(jù)相對應負相關；還是兩個集合中的數(shù)據(jù)互不相關相關性為零。操作步驟：1.新建一個工作表表92.單擊“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇“相關系數(shù)”選項，3.填寫完“相關系數(shù)”對話框，單擊“確定”按扭即可得到各個變量的相關系數(shù)矩陣。三回歸方程線性回歸分析通過使用“最小二乘法”對樣本數(shù)據(jù)進行直線擬合，用于分析單個因變量是如何受一個或幾個自變量影響的。操作步驟：1.新建一個工作表表92.單擊“工具”菜

18、單的“數(shù)據(jù)分析”子菜單，選擇“回歸”選項，彈出回歸分析對話框。3.對話框主要選項的含義如下：Y值輸入?yún)^(qū)域：輸入因變量數(shù)據(jù)區(qū)域，該區(qū)域必須由單列數(shù)據(jù)組成；X值輸入?yún)^(qū)域：輸入對自變量數(shù)據(jù)區(qū)域，自變量的個數(shù)最多為16;置信度：如果需要在匯總輸出表中包含附加的置信度信息，則選中此復選框，然后在右側的編輯框中，輸入所要使用的置信度，95利默認值；常數(shù)為零：如果要強制回歸線通過原點，則選中此復選框；輸出區(qū)域：在此輸入輸出表左上角單元格的地址，用于控制計算結果的輸出位置。匯總輸出表至少需要有七列的寬度，包含的內容有anova表、系數(shù)、y估計值的標準誤差、r2值、觀察值個數(shù)，以及系數(shù)的標準誤差；新工作表：單擊此選項，可在當前工作簿中插入新工作表，并由新工作表的A1單元格開始顯示計算結果，如果需要給新工作表命名，則在右側的編輯框中鍵入名稱；新工作簿：單擊此選項，