ecel在統(tǒng)計(jì)中的應(yīng)用

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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