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

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

2、析”工具可完成大多數(shù)常用的統(tǒng)計(jì)分析 ：對于較復(fù)雜的統(tǒng)計(jì)學(xué)方法可簡單利用 內(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ì)此分析工具用于對輸入?yún)^(qū)域中數(shù)據(jù)的單變量分析， 并提供數(shù)據(jù) 趨中性和易變性等有關(guān)信息。 例 1. 某班 20 名學(xué)生考試成績單如下表，試用分析工具中的描述統(tǒng)計(jì)對班級(jí)成績進(jìn)行分析匯 總，并給出相關(guān)的統(tǒng)計(jì)指標(biāo)。操作步驟： 1. 新建一個(gè)工作表（表 1）?2 選擇“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇“描述統(tǒng)計(jì)”選項(xiàng) ，單擊確定出現(xiàn)描

3、述性統(tǒng)計(jì)對話筐；3出現(xiàn)“描述統(tǒng)計(jì)”對話框，對話框內(nèi)各選項(xiàng)的含義如下：輸入?yún)^(qū)域： 在此輸入待分析數(shù)據(jù)區(qū)域的單元格范圍。 一般情況下 Excel 會(huì)自動(dòng)根據(jù)當(dāng)前單元 格確定待分析數(shù)據(jù)區(qū)域。分組方式： 如果需要指出輸入?yún)^(qū)域中的數(shù)據(jù)是按行還是按列排列，則單擊“行”或“列”, “描述統(tǒng)計(jì)”工具可以同時(shí)對多列或多行數(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ū)域沒有標(biāo)志項(xiàng)， 則不需要選擇該復(fù)選框， Excel 將在輸出表中生成適宜數(shù)據(jù)標(biāo)志。 輸出區(qū)域：

4、填寫輸出結(jié)果表左上角單元格地址 , 用于控制輸出結(jié)果的存放位置。整個(gè)輸出結(jié) 果分為兩列 , 左邊一列包含統(tǒng)計(jì)標(biāo)志項(xiàng)，右邊一列包含統(tǒng)計(jì)值。根據(jù)所選擇的“分組方式” 選項(xiàng)的不同， Excel 將為輸入表中的每一行或每一列生成一個(gè)兩列的統(tǒng)計(jì)表。新工作表： 單擊此選項(xiàng)，可在當(dāng)前工作簿中插入新工作表，并由新工作表的 A1 單元格開始 存放計(jì)算結(jié)果。如果需要給新工作表命名，則在右側(cè)編輯框中鍵入名稱。新工作簿： 單擊此選項(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)偏差、方差、峰值、

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

6、中值（Medium），眾數(shù)（Mode）,樣本標(biāo)準(zhǔn)差（S），樣本方差（S2）,峰度 值，偏度值，極差（Max-Min）,最小值（Min）,最大值（Max）,樣本總和，樣本個(gè)數(shù)（n）和一定 顯著水平下總體均值的置信區(qū)間。二、樣本直方圖在實(shí)際的問題中， 總體的分布情況往往是不清楚的， 利用樣本資料通過作出適當(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ù)的最大值和最 小值，這里是

7、0.78 和 1.06 ；（ 2）確定分組的組距和組數(shù)?2 選擇“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇“直方圖”選項(xiàng) ，單擊確定出現(xiàn)直方圖對話筐；3出現(xiàn)“直方圖”對話框，對話框內(nèi)主要選項(xiàng)的含義如下： 輸入?yún)^(qū)域： 在此輸入待分析數(shù)據(jù)區(qū)域的單元格范圍。接收區(qū)域（可選） ： 在此輸入接收區(qū)域的單元格范圍，該區(qū)域應(yīng)包含一組可選的用來計(jì)算頻 數(shù)的邊界值。這些值應(yīng)當(dāng)按升序排列。只要存在的話， Excel 將統(tǒng)計(jì)在各個(gè)相鄰邊界直之間 的數(shù)據(jù)出現(xiàn)的次數(shù)。 如果省略此處的接收區(qū)域， Excel 將在數(shù)據(jù)組的最小值和最大值之間創(chuàng) 建一組平滑分布的接收區(qū)間。標(biāo)志：如果輸入?yún)^(qū)域的第一行或第一列中包含標(biāo)志項(xiàng)，則選中此復(fù)

8、選框；如果輸入?yún)^(qū)域沒有 標(biāo)志項(xiàng)，則清除此該復(fù)選框， Excel 將在輸出表中生成適宜的數(shù)據(jù)標(biāo)志。輸出區(qū)域： 在此輸入結(jié)果輸出表的左上角單元格的地址用于控制計(jì)算結(jié)果的顯示位置。 如果 輸出表將覆蓋已有的數(shù)據(jù)， Excel 會(huì)自動(dòng)確定輸出區(qū)域的大小并顯示信息。柏拉圖： 選中此復(fù)選框，可以在輸出表中同時(shí)顯示按降序排列頻率數(shù)據(jù)。如果此復(fù)選框被清 除， Excel 將只按升序來排列數(shù)據(jù)。累積百分比： 選中此復(fù)選框，可以在輸出結(jié)果中添加一列累積百分比數(shù)值，并同時(shí)在直方圖 表中添加累積百分比折線。如果清除此選項(xiàng)，則會(huì)省略以上結(jié)果。圖表輸出： 選中此復(fù)選框，可以在輸出表中同時(shí)生成一個(gè)嵌入式直方圖表。4 按需要

9、填寫完“直方圖”對話框之后，按“確定”按扭即可。結(jié)果說明：完整的結(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)：HO 1 = c 2 , HA 1工6 2 H0 仁 c 2 , HA1Vc 2 HO 仁（T 2 , HA : c 1 >c 2（一）兩正態(tài)總體方差的假設(shè)檢驗(yàn)對于來自兩個(gè)正態(tài)總體的樣本， 其總體方差分別為c 1 2和c 22，從兩個(gè)總體中獨(dú)立地抽取 容量為n1和n2的樣本，對應(yīng)的樣本方差分別為 S12

10、和S22 ,若需要檢驗(yàn)c 12 = c 22 ,則可 利用分析工具的 F 檢驗(yàn)。例 3. 合成車間某中間體生產(chǎn)的工藝條件改革后， 收率似有提高， 但工人師傅反映新工藝的條 件不易控制，收率波動(dòng)較大，為此，對新老工藝分別抽取若干批，結(jié)果如表所示。試檢驗(yàn)推斷老師傅反應(yīng)的問題是否屬實(shí)。單擊“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇 “F- 檢驗(yàn)雙樣本方差”結(jié)論：由統(tǒng)計(jì)數(shù)據(jù)知道樣本統(tǒng)計(jì)量 F= 2.990066994,其對應(yīng)的的概率 P=0.06884784>0.05, 說明抽樣的結(jié)果不是小概率事件，所以接受H0: c i2=c 22拒絕HA c 1 2> c 22 說 明工人師傅反映的問題顯

11、著有誤的。(二) 兩正態(tài)總體均數(shù)的假設(shè)檢驗(yàn)1 、配對比較例 4、某中醫(yī)師用中藥青木香治療高血壓患者，治療前后的情況下表所示。 問該中藥治療高血壓是否有效？操作步驟： 1. 新建一個(gè)工作表(表 4)2單擊“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇 “t- 檢驗(yàn)平均值的成對二樣本分析”選項(xiàng) ，單擊確定出現(xiàn) t- 檢驗(yàn)平均值的成對 二樣本分析對話筐； 3. 在對話框中， 按要求填入相應(yīng)選項(xiàng)， 單擊確定按鈕， 輸出分析結(jié)果。5.結(jié)論：由統(tǒng)計(jì)數(shù)據(jù)知道樣本統(tǒng)計(jì)量t = - 5.81435,其對應(yīng)的的概率P= 0.000117 V 0.01 , 說明抽樣的結(jié)果是小概率事件，所以拒絕H0卩1 - 1 2 = 0,

12、接受HA卩1 -卩2工0 . 說明該中藥對質(zhì)量高血壓有極顯著效果。2、成組比較(1) 已知c 1 2= c 22例 5. 例 3 中合成車間某中間體生產(chǎn)的工藝條件改革后， 收率似有提高， 為此，對新老工藝分 別抽取若干批，結(jié)果如(表 3)所示。試推斷檢驗(yàn)新老工藝的收率是否有差異。 操作步驟：單擊“工具”菜單的“數(shù)據(jù)分析”子菜單，選擇“t- 檢驗(yàn)雙樣本等方差假設(shè)”選項(xiàng),5.結(jié)論：由統(tǒng)計(jì)數(shù)據(jù)知道樣本統(tǒng)計(jì)量t = - 5.04748,其對應(yīng)的的概率 P=0.0000992 (9.92E-05) V0.01 ，說明抽樣的結(jié)果是小概率事件，所以拒絕H0：11 - 12 = 0，接受HA 11- 1 2工

13、0 .說明新老工藝的收率有極顯著差異。(2) 已知 c 12 Mc 22單擊“工具”菜單的“數(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ù)(表 5) 2計(jì)算t統(tǒng)計(jì)量及P值等：?在 G4中鍵入“ =ABS(C4-C7)/D7*B79.5 ” ;在G5中鍵入“ =B7-1”求自由度；在G6中鍵入“ =TDIS

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

15、某公司研制出 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è)分組的組間方差、組內(nèi)方差以及總方差，DF列分別給出了對應(yīng)方差的自由度，MS列是平均值方差，由SS除于DF得到，它是總體方差的兩個(gè)估計(jì)值。F列是F統(tǒng)計(jì)量的計(jì)算結(jié)果，如果四個(gè)總體均值相等的假設(shè)成立的化，它應(yīng)該服從F分布，即近似為1,它是最終的計(jì)算結(jié)果，通過將它與一定置信水平下的F臨界值F c

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

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

18、步”4.在“圖表向?qū)?4 步驟 3”對話筐中， 單擊“網(wǎng) 格線”- 刪去“主要網(wǎng)格 線”- 單擊“下一步”輸出結(jié)果。（二）相關(guān)系數(shù) 使用“相關(guān)系數(shù)”分析工具來確定兩個(gè)區(qū)域中數(shù)據(jù)的變化是否相關(guān)，即，一個(gè)集合的較大數(shù)據(jù)是否與另一個(gè)集合的較大數(shù)據(jù)相對應(yīng)（正相關(guān)） ；或者一個(gè)集合的較小數(shù)據(jù)是否與另 一個(gè)集合的較小數(shù)據(jù)相對應(yīng)（負(fù)相關(guān)） ；還是兩個(gè)集合中的數(shù)據(jù)互不相關(guān)（相關(guān)性為零） 。 操作步驟： 1. 新建一個(gè)工作表（表 9）2單擊“工具”菜單的“數(shù)據(jù)分析”子菜單，選 擇“相關(guān)系數(shù)”選項(xiàng) ， 3 . 填寫完“相關(guān)系數(shù)”對話框，單擊“確定”按扭即可得到各個(gè) 變量的相關(guān)系數(shù)矩陣。（三）回歸方程線性回歸分析通過使用“最小二乘法”對樣本數(shù)據(jù)進(jìn)行直線擬合， 用于分析單個(gè)因變量是 如何受一個(gè)或幾個(gè)自變量影響的。操作步驟： 1. 新建一個(gè)工作表（表 9） 2 單擊“工具”菜單的“數(shù)據(jù)分析”子菜單， 選擇“回歸”選項(xiàng) ，彈出回歸分析對話框。3 . 對話框主要選項(xiàng)的含義如下：Y 值輸入?yún)^(qū)域： 輸入因變量數(shù)據(jù)區(qū)域，該區(qū)域必須由單列數(shù)據(jù)組成；X 值輸入?yún)^(qū)域 : 輸入