Excel在生物統(tǒng)計(jì)中應(yīng)用

1、金衛(wèi)斌主編Excel在生物統(tǒng)計(jì)中應(yīng)用一、目的要求使學(xué)生掌握Excel在生物統(tǒng)計(jì)中的基本應(yīng)用方法。二、材料用具練習(xí)紙、鉛筆、裝有Excel軟件的計(jì)算機(jī)及輸出設(shè)備或計(jì)算器。三、方法步驟1啟動(dòng)Excel2加載“分析工具庫(kù)”在默認(rèn)的情況下，Excel是沒有加載“分析工具庫(kù)”，無(wú)法進(jìn)行統(tǒng)計(jì)分析。先查看計(jì)算機(jī)是否加載“分析工具庫(kù)”。首先啟動(dòng)Excel，檢查“工具”菜單中是否有“數(shù)據(jù)分析”命令。如果沒有發(fā)現(xiàn)“數(shù)據(jù)分析”命令，就表示未加載“分析工具庫(kù)”?？砂凑障铝胁襟E來(lái)加載“分析工具庫(kù)”。（1）選取“工具”菜單中的“加載宏”命令，彈出“加載宏”的對(duì)話框。圖1 “加載宏”對(duì)話框示意圖（2）選中“分析工具庫(kù)”復(fù)選

2、框，單擊“確定”按鈕，按提示完成加載過(guò)程。此時(shí)再查看“工具”菜單，就可發(fā)現(xiàn)“數(shù)據(jù)分析”命令，表示安裝完成，單擊“工具”菜單中“數(shù)據(jù)分析”命令，得圖2，即可進(jìn)行相關(guān)的統(tǒng)計(jì)分析。圖2 “數(shù)據(jù)分析”對(duì)話框示意圖3分析工具庫(kù)提供的統(tǒng)計(jì)分析方法分析工具庫(kù)提供的常用統(tǒng)計(jì)分析方法如下：1 單向分類資料的方差分析*雙向分類交叉分組無(wú)重復(fù)資料的方差分析*雙向分類交叉分組有重復(fù)資料的方差分析*描述性統(tǒng)計(jì)量計(jì)算*二總體方差的同質(zhì)性檢驗(yàn)編制頻數(shù)分布表及繪制直方圖排位與百分比排位二樣本均數(shù)差數(shù)的u檢驗(yàn)配對(duì)資料的t檢驗(yàn)*等方差非配對(duì)資料的t檢驗(yàn)*異方差非配對(duì)資料的t檢驗(yàn)一元線性回歸與多元線性回歸分析*計(jì)算多個(gè)變量?jī)蓛芍g

3、的相關(guān)系數(shù)及協(xié)方差*進(jìn)行隨機(jī)和順序抽樣4統(tǒng)計(jì)分析的粘貼函數(shù)Excel提供的統(tǒng)計(jì)分析粘貼函數(shù)如下：AVERAGEBINOMDISTCHIDISTCHIINVCHITESTCONFIDENCECORRELCOVARFDISTFINVFTESTGEOMEANHARMEANINTERCEPTMAX MEDIANMINMODENORMDISTNORMINVNORMSDISTNORMSINVPOISSONSLOPESTDEVSTDEVPTDISTTINVTTESTVARVARPZTEST計(jì)算算術(shù)平均值*計(jì)算二項(xiàng)式分布的概率值計(jì)算特定2分布的單尾概率值計(jì)算一定單尾概率值時(shí)的2臨界值計(jì)算獨(dú)立性檢驗(yàn)的2值計(jì)算總

4、體平均值的置信區(qū)間計(jì)算兩組數(shù)據(jù)的相關(guān)系數(shù)*計(jì)算兩組數(shù)據(jù)的協(xié)方差計(jì)算特定F分布的單尾概率值*計(jì)算一定概率時(shí)的臨界F值*計(jì)算二個(gè)樣本方差之比F值的概率*計(jì)算幾何平均數(shù)計(jì)算調(diào)和平均數(shù)計(jì)算直線回歸的截距*計(jì)算最大值*計(jì)算一組給定數(shù)字的中位數(shù)*計(jì)算最小值*計(jì)算一組數(shù)據(jù)的眾數(shù)*計(jì)算正態(tài)分布的累積函數(shù)計(jì)算正態(tài)分布累積函數(shù)的逆函數(shù)計(jì)算標(biāo)準(zhǔn)正態(tài)分布的累積函數(shù)計(jì)算標(biāo)準(zhǔn)正態(tài)分布累積函數(shù)的逆函數(shù)計(jì)算泊松分布的概率計(jì)算給定數(shù)據(jù)的直線回歸系數(shù)*計(jì)算樣本標(biāo)準(zhǔn)差*計(jì)算樣本總體的標(biāo)準(zhǔn)差計(jì)算學(xué)生氏-t分布的概率值*計(jì)算特定概率時(shí)學(xué)生氏-t分布的臨界t值*計(jì)算t檢驗(yàn)時(shí)的學(xué)生氏-t檢驗(yàn)相關(guān)的概率計(jì)算樣本的方差*計(jì)算樣本總體的方差計(jì)算

5、Z檢驗(yàn)的雙尾概率值上述帶*者就是我們要學(xué)習(xí)和掌握的內(nèi)容5Excel的常用基本概念（1）工作薄和工作表Microsoft Excel工作簿是計(jì)算和儲(chǔ)存數(shù)據(jù)的文件，每一個(gè)工作簿都可以包含多張工作表，因此，可在單個(gè)文件中管理各種類型的相關(guān)信息。使用工作表可以對(duì)數(shù)據(jù)進(jìn)行組織和分析，可以同時(shí)在多張工作表上輸入和編輯數(shù)據(jù)，并且可以對(duì)來(lái)自不同工作表的數(shù)據(jù)進(jìn)行匯總計(jì)算。在創(chuàng)建圖表之后，既可以將其置于原始數(shù)據(jù)所在的工作表上，也可以放置在單獨(dú)的工作表上。工作表的名稱顯示于工作簿窗口底部的工作表標(biāo)簽上，單擊這個(gè)標(biāo)簽即可進(jìn)入該工作表。當(dāng)前所在的工作表稱為活動(dòng)工作表，在它的標(biāo)簽上標(biāo)有單下劃線?？梢栽谕还ぷ鞑緝?nèi)或兩個(gè)工

6、作簿之間對(duì)工作表進(jìn)行改名、添加、刪除、移動(dòng)或復(fù)制等編輯操作。在默認(rèn)情況下，每個(gè)工作薄由3個(gè)工作表組成，用戶可以根據(jù)需要添加更多的工作表。每個(gè)工作表由256列和65 536行組成。行和列相交形成單元格，它是存儲(chǔ)數(shù)據(jù)的基本單位。列用英文字母表示，開始是單個(gè)英文字母，然后是2個(gè)英文字母組合，即由AIV共256列。行用阿拉伯?dāng)?shù)字表示，由165 536。每個(gè)單元格的定位可以通過(guò)該單元格所對(duì)應(yīng)的行數(shù)和列標(biāo)來(lái)確定，如B6就表示B列第6行的單元格。在活動(dòng)工作表眾多的單元格中，其中有一單元格含有粗邊框線，此單元格為活動(dòng)單元格，在該單元格中可以輸入和編輯數(shù)據(jù)。每進(jìn)入一個(gè)工作表時(shí)，A1自動(dòng)為活動(dòng)單元格。在活動(dòng)單元格

7、的右下角有一小黑方塊，這個(gè)黑方塊稱為填充柄，利用此填充柄可以填充相鄰單元格區(qū)域的內(nèi)容。（2）單元格和區(qū)域引用引用是指調(diào)用工作表中的數(shù)據(jù)用于計(jì)算分析，是通過(guò)標(biāo)識(shí)工作表上的單元格或區(qū)域來(lái)實(shí)現(xiàn)的。通過(guò)引用，指明在計(jì)算時(shí)所使用的數(shù)據(jù)在工作表中的位置，可以使用工作表中不同區(qū)域的數(shù)據(jù)，或者在多個(gè)計(jì)算公式中使用同一單元格的數(shù)值。還可以引用同一工作簿不同工作表、不同工作簿、甚至其他應(yīng)用程序中的數(shù)據(jù)。引用不同工作簿中的數(shù)據(jù)稱為外部引用，引用其他程序中的數(shù)據(jù)稱為遠(yuǎn)程引用。如果要引用某一單元格的數(shù)據(jù)，可在計(jì)算公式中輸入該單元格的標(biāo)識(shí)，例如要引用在列B和行10這一單元格的數(shù)據(jù)，在計(jì)算公式中輸入B10，或者用鼠標(biāo)點(diǎn)擊該

8、單元格。如果要引用某個(gè)區(qū)域的數(shù)據(jù)，可在計(jì)算公式中輸入該區(qū)域左上角單元格的標(biāo)識(shí)，后面跟一個(gè)冒號(hào)（：），后面再寫上該區(qū)域右下角單元格的標(biāo)識(shí)。例如，要引用從單元格C5到單元格C15這一區(qū)域，可輸入C5:C15；如要引用從單元格A5到單元格B10這一區(qū)域，即可輸入A5:B10。（3）絕對(duì)引用與相對(duì)引用的區(qū)別當(dāng)生成公式時(shí)，對(duì)單元格或區(qū)域的引用通?；谒麄兣c公式單元格的相對(duì)位置。如在單元格A8中包含公式=8×B5，Microsoft Excel將在距單元格A8上面一個(gè)單元格和右面一個(gè)單元格處的單元格中查找數(shù)值，這就是相對(duì)引用。當(dāng)復(fù)制使用相對(duì)引用的公式時(shí)，被粘貼公式中的引用將被更新，并指向與當(dāng)前公

9、式位置相對(duì)應(yīng)的其他單元格。如單元格A8中的公式被復(fù)制到單元格A10中，單元格A10中的公式已經(jīng)改為=8×B7，即從單元格A8上面和右面一個(gè)單元格處的單元格中查找數(shù)值。如果在復(fù)制公式時(shí)，不希望引用發(fā)生改變，可使用絕對(duì)引用。絕對(duì)引用的方式是在相對(duì)引用列標(biāo)和行數(shù)字前加上美元符號(hào)，即B5。如在單元格A8中包含的公式=8×B5，現(xiàn)在將公式復(fù)制到另一單元格中，則公式中的引用將不發(fā)生改變。1 試驗(yàn)數(shù)據(jù)整理1）圖表向?qū)У睦糜肊xcel中的“圖表向?qū)А笨梢钥焖贉?zhǔn)確的繪制圖表。它是一連串的對(duì)話框，依照它逐步建立或修改圖表。新建圖表需四個(gè)步驟，修改圖表只需兩個(gè)步驟。步驟1：選擇圖表類型圖4 表

10、類型示意圖選擇“常用”工具欄中的“圖表向?qū)А卑磁?，如圖3圖標(biāo)或單擊菜單欄中“插入”再擊“圖表”命令，激活圖表向?qū)?duì)話框，如圖4。圖3 圖表按鈕Excel提供了兩大類的圖形：標(biāo)準(zhǔn)類型和自定義類型，用戶可根據(jù)需要選擇。步驟2：選定數(shù)據(jù)區(qū)域如果在單擊圖表向?qū)е?，就已選定數(shù)據(jù)范圍，那么圖表向?qū)У牡诙?duì)話框就會(huì)自動(dòng)設(shè)定數(shù)據(jù)區(qū)域。如果事先沒有設(shè)定，用戶可自己設(shè)定。步驟3：設(shè)定圖表選項(xiàng)完成步驟2之后，單擊“下一步”按鈕進(jìn)入步驟3。在步驟3里有很多的選項(xiàng)卡：“標(biāo)題”、“坐標(biāo)軸”、“網(wǎng)格線”、“圖例”、“數(shù)據(jù)標(biāo)志”、“數(shù)據(jù)表”。步驟4：指定圖表位置當(dāng)完成步驟3后，單擊“下一步”按鈕即可進(jìn)入步驟4?？梢赃x定“作

11、為新工作表插入”或“作為其中的對(duì)象插入”。然后單擊“完成”按鈕結(jié)束圖表向?qū)?，完成了圖表的制作。2）“數(shù)據(jù)分析”工具欄運(yùn)用現(xiàn)以水稻品種120行產(chǎn)量（kg）為例，說(shuō)明其方法與步驟。表1 水稻品種120行產(chǎn)量（kg）表17721519797159245119119131152167104161214125175188192176175951991161652149515883801381511871261342061379897129143174159165136108141148168163176102194173751301491501551111581311899114215215416312

12、320515513120918397119181187131215111186150155197116254239160179151198124179184168169173181188211175122151171166143190213192231163159177147194227169124159159圖5 原始數(shù)據(jù)示意圖（1）打開Excel，輸入原始數(shù)據(jù)和各組的下限，樣式見圖5，圖中從A1單元格到L10單元格的區(qū)域?yàn)樵紨?shù)據(jù)，各組的下限值位于M1到M11單元格。（2）從“工具”菜單中選定“數(shù)據(jù)分析”命令，再選定“直方圖”，確定后出現(xiàn)直方圖對(duì)話框。（3）在輸入?yún)^(qū)域中輸入A1:L10，在

13、接收區(qū)域中輸入M1:M11，在輸出選項(xiàng)中選定輸出區(qū)域如A12后，單擊確定，得到頻數(shù)分布表。若在“直方圖”對(duì)話框中選定“圖表輸出”復(fù)選框，則直方圖也同時(shí)輸出，如圖6。圖6 直方圖示意圖2. 特征數(shù)計(jì)算在生物統(tǒng)計(jì)分析中主要計(jì)算平均數(shù)和變異數(shù)。平均數(shù)主要有算術(shù)平均數(shù)、幾何平均數(shù)及中數(shù)，變異數(shù)主要有極差、方差、標(biāo)準(zhǔn)差、變異系數(shù)。1）算術(shù)平均數(shù)用AVERAGE函數(shù)可用來(lái)計(jì)算算術(shù)平均數(shù)，例如一組數(shù)據(jù)2，3，4，5，6的平均數(shù)可在單元格中鍵入“=AVERAGE（2，3，4，5，6）”得 4。如果這5個(gè)數(shù)據(jù)分別存放于A1到A5的字段中，則鍵入“= AVERAGE（A1:A5）”得4。通?！捌骄鶖?shù)”是指算術(shù)平均

14、數(shù)。2）幾何平均數(shù)用GEOMEAN函數(shù)可用來(lái)計(jì)算幾何平均數(shù)，例如6，15，35，105，260的幾何平均數(shù)可在單元格中鍵入“=GEOMEAN（6，15，35，105，260）”得38.63。3）中數(shù)計(jì)算如果觀察值次數(shù)是偶數(shù)，則取中間兩個(gè)觀察值的算術(shù)平均數(shù)作為中數(shù)。用MEDIAN函數(shù)可以計(jì)算中數(shù)。如在單元格中輸入“=MEDIAN（2，3，4，7，8）”得4；在單元格中輸入“=MEDIAN（2，3，4，7）”得3.5。4）極差資料中最大值減去最小值是極差，可以利用MAX求得最大值，用MIN 求得最小值，兩者的差數(shù)就是極差。5）方差平方和除以自由度即為方差。用VARP函數(shù)可以求總體方差；用VAR函數(shù)

15、可以求樣本的方差。如在單元格中輸入“=VARP（1，2，3，4，5）”得2；在單元格中輸入“=VAR（1，2，3，4，5）”得2.5。6）標(biāo)準(zhǔn)差就是方差開平方。用STDEVP可以計(jì)算總體標(biāo)準(zhǔn)差；用STDEV則可以計(jì)算樣本標(biāo)準(zhǔn)差。如在單元格中輸入“=STDEVP（1，2，3，4，5）”得1.414214；在單元格中輸入“=STDEV（1，2，3，4，5）”得1.581139。7）變異系數(shù)標(biāo)準(zhǔn)差除以平均數(shù)就是變異系數(shù)。但Excel本身沒有提供函數(shù)計(jì)算變異系數(shù)，我們可以通過(guò)STDEV計(jì)算樣本標(biāo)準(zhǔn)差，用AVERAGE計(jì)算平均數(shù)，兩者相除即得變異系數(shù)。3. 統(tǒng)計(jì)假設(shè)測(cè)驗(yàn)1在Excel工作表中輸入數(shù)據(jù)圖7

16、原始數(shù)據(jù)示意圖2選擇計(jì)算命令這是一個(gè)配對(duì)設(shè)計(jì)的兩樣本平均數(shù)顯著性測(cè)驗(yàn)。選擇“工具”菜單的“數(shù)據(jù)分析”命令，在彈出的“數(shù)據(jù)分析”對(duì)話框中，雙擊“t-檢驗(yàn)：平均值的成對(duì)二樣本分析”選項(xiàng)，則彈出“t-檢驗(yàn)：平均值的成對(duì)二樣本分析”對(duì)話框。圖8 t-檢驗(yàn)：平均值成對(duì)二樣本分析對(duì)話框示意圖3填寫區(qū)域分別填寫變量1的區(qū)域：$B$1:$B$11，變量2的區(qū)域：$C$1:$C$11，由于我們進(jìn)行的是等均值的檢驗(yàn)，填寫假設(shè)平均差為0，由于數(shù)據(jù)的首行包括標(biāo)志項(xiàng)，所以選擇“標(biāo)志”復(fù)選框，再填寫顯著水平為0.05，如圖13所示，然后點(diǎn)擊“確定”按扭，則可以得到下圖所示的結(jié)果。圖9 t-檢驗(yàn)結(jié)果對(duì)話框示意圖4結(jié)果分析如

17、圖14所示，表中分別給出了兩組兩種處理方法病痕數(shù)的平均值、方差和樣本個(gè)數(shù)。其中，合并方差是樣本方差加權(quán)之后的平均值，DF是假設(shè)檢驗(yàn)的自由度，t統(tǒng)計(jì)量是兩個(gè)樣本差值減去假設(shè)平均差之后再除于標(biāo)準(zhǔn)誤差的結(jié)果，“P(Tt)單尾”是單尾檢驗(yàn)的顯著水平，“t單尾臨界”是單尾檢驗(yàn)t的臨界值，“P(Tt)雙尾”是雙尾檢驗(yàn)的顯著水平，“t雙尾臨界”是雙尾檢驗(yàn)t的臨界值。由圖表的結(jié)果可以看出t統(tǒng)計(jì)量均大于兩個(gè)臨界值，所以，在5%顯著水平下，A、B兩種處理方法對(duì)純化病毒的效應(yīng)差異顯著。對(duì)于非配對(duì)數(shù)據(jù)，可選擇“工具”菜單的“數(shù)據(jù)分析”命令，在彈出的“數(shù)據(jù)分析”對(duì)話框中，雙擊“t-檢驗(yàn)：平均值的成組二樣本分析”選項(xiàng)，則

18、彈出“t-檢驗(yàn)：平均值的成組二樣本分析”對(duì)話框。選擇數(shù)據(jù)后得到分析結(jié)果。5. 作業(yè)題：5.9,5.22.4. 方差分析（一）以A、B、C、D4種藥劑處理水稻種子，其中A為對(duì)照，每處理各得4個(gè)苗高觀察值（cm），得結(jié)果如表2，試進(jìn)行方差分析。表2 不同藥劑處理的水稻苗高（cm）藥劑ABCD觀察值192120222324182521271927132015221將資料1表中的數(shù)據(jù)復(fù)制到Excel表中，其輸入格式如圖15。圖10原始數(shù)據(jù)示意圖2在“工具”菜單選擇“數(shù)據(jù)分析”命令，彈出“分析工具”對(duì)話框，選中列表中“方差分析：?jiǎn)我蛩胤讲罘治觥边x項(xiàng)，單擊“確定”按鈕，在接著彈出的對(duì)話框中輸入數(shù)據(jù)范圍$B

19、$2:$E$5，a值設(shè)為0.05或0.01。3單擊“確定”按鈕，得方差分析表，如圖16：圖11 資料1的方差分析示意圖FF，不同藥劑處理苗高差異達(dá)到顯著水平。（二）用生長(zhǎng)素處理豌豆，共6個(gè)處理。豌豆種子發(fā)芽后，分別在每一箱中移植4株，每組6個(gè)木箱，每箱1個(gè)處理。試驗(yàn)共有4組24箱，試驗(yàn)時(shí)按組排于溫室中，使同組各項(xiàng)環(huán)境一致，然后記錄各箱第一朵花時(shí)4株豌豆的總節(jié)間數(shù)，結(jié)果如表3，試作方差分析。表3 不同生長(zhǎng)素處理豌豆的試驗(yàn)結(jié)果處理區(qū) 組對(duì)照（未處理）60626160赤霉素65656865動(dòng)力精63616160吲哚乙酸64676361硫酸腺嘌呤62656264馬來(lái)酸616262651將資料表3中數(shù)據(jù)

20、資料復(fù)制到Excel中，輸入格式如圖17。圖12 原始數(shù)據(jù)示意圖2在“工具”菜單中選擇“數(shù)據(jù)分析”命令，彈出“分析工具”對(duì)話框，選中“方差分析：無(wú)重復(fù)雙因素方差分析”選項(xiàng)，單擊“確定”按鈕，在接著彈出的對(duì)話框中輸入數(shù)據(jù)范圍$B$2:$E$7，a值設(shè)為0.05或0.01。3單擊“確定”按鈕，得方差分析表，如圖18：圖13資料2的方差分析示意圖在變異來(lái)源欄目：行代表不同處理，列代表不同區(qū)組。分析結(jié)果FF，不同處理間差異達(dá)到極顯著。（三）施用A1、A2、A33種肥料于B1、B2、B33種土壤，以小麥為指示作物，每處理組合種3盆，得產(chǎn)量結(jié)果（g）于表4。試作方差分析。表 4 種肥料施入3種土壤的小麥產(chǎn)

21、量（g）肥料種類盆土壤種類B1（油砂）B2（二合）B3（白僵）A1123A2123A31231將資料表4中數(shù)據(jù)資料復(fù)制到Excel中，輸入格式如圖19。圖14 原始數(shù)據(jù)示意圖2在“工具”菜單中選擇“數(shù)據(jù)分析”命令，在彈出“分析工具”對(duì)話框中選中“方差分析：可重復(fù)雙因素方差分析”命令，單擊“確定”按鈕，在接著彈出的對(duì)話框中輸入數(shù)據(jù)范圍$A$1:$D$10，a值設(shè)為0.05或0.01。圖15 資料3方差分析示意圖3單擊“確定”按鈕，得方差分析表，如圖20：在變異來(lái)源欄里，樣本代表肥類因素，列代表土類，交互代表肥類×土類的互作，內(nèi)部代表誤差。F測(cè)驗(yàn)結(jié)果表明：肥類間和肥類×土類的互作的效應(yīng)達(dá)到極顯著。多重比較請(qǐng)參考教材。同學(xué)們可自行進(jìn)行比較。四、作業(yè) 8.2， 9.1 ，9.12 5. 直線相關(guān)回歸某水稻研究所，進(jìn)行水稻品種生育期與產(chǎn)量試驗(yàn)，其部分品種生育期與產(chǎn)量結(jié)果列于下表，試作回歸相關(guān)分析。（生育期為x，單位：天；產(chǎn)量為y，單位：kg/667m2）。表7 水稻品種生育期與產(chǎn)量試驗(yàn)結(jié)果表品種編號(hào)12345678生育期x(天)112114116119123128129131產(chǎn)