1、-. z實驗一 E*cel在生物統(tǒng)計中應用一、目的要求使學生掌握E*cel在生物統(tǒng)計中的應用方法。二、材料用具練習紙、鉛筆、裝有E*cel軟件的計算機及輸出設備或計算器。三、方法步驟1啟動E*cel2加載分析工具庫在默認的情況下，E*cel是沒有加載分析工具庫，無法進展統(tǒng)計分析。先查看計算機是否加載分析工具庫。首先啟動E*cel，檢查工具菜單中是否有數(shù)據(jù)分析命令。如果沒有發(fā)現(xiàn)數(shù)據(jù)分析命令，就表示未加載分析工具庫?？砂凑找韵虏襟E來加載分析工具庫。1選取工具菜單中的加載宏命令，彈出加載宏的對話框。圖1 加載宏對話框示意圖2選中分析工具庫復選框，單擊確定按鈕，按提示完成加載過程。此時再查看工具菜單，

2、就可發(fā)現(xiàn)數(shù)據(jù)分析命令，表示安裝完成，單擊工具菜單中數(shù)據(jù)分析命令，得圖2，即可進展相關(guān)的統(tǒng)計分析。圖2 數(shù)據(jù)分析對話框示意圖3分析工具庫提供的統(tǒng)計分析方法分析工具庫提供的常用統(tǒng)計分析方法如下：單向分類資料的方差分析雙向分類穿插分組無重復資料的方差分析雙向分類穿插分組有重復資料的方差分析描述性統(tǒng)計量計算二總體方差的同質(zhì)性檢驗編制頻數(shù)分布表及繪制直方圖排位與百分比排位二樣本均數(shù)差數(shù)的u檢驗配對資料的t檢驗等方差非配對資料的t檢驗 eq oac(,11)異方差非配對資料的t檢驗 eq oac(,12)一元線性回歸與多元線性回歸分析 eq oac(,13)計算多個變量兩兩之間的相關(guān)系數(shù)及協(xié)方差 eq o

3、ac(,14)進展隨機和順序抽樣4統(tǒng)計分析的粘貼函數(shù)E*cel提供的統(tǒng)計分析粘貼函數(shù)如下：AVERAGEBINOMDISTCHIDISTCHIINVCHITESTCONFIDENCECORRELCOVARFDISTFINVFTESTGEOMEANHARMEANINTERCEPTMA* MEDIANMINMODENORMDISTNORMINVNORMSDISTNORMSINVPOISSONSLOPESTDEVSTDEVPTDISTTINVTTESTVARVARPZTEST計算算術(shù)平均值計算二項式分布的概率值計算特定2分布的單尾概率值計算一定單尾概率值時的2臨界值計算獨立性檢驗的2值計算總體平均值

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

5、根本概念1工作薄和工作表Microsoft E*cel工作簿是計算和儲存數(shù)據(jù)的文件，每一個工作簿都可以包含多工作表，因此，可在單個文件中管理各種類型的相關(guān)信息。使用工作表可以對數(shù)據(jù)進展組織和分析，可以同時在多工作表上輸入和編輯數(shù)據(jù)，并且可以對來自不同工作表的數(shù)據(jù)進展匯總計算。在創(chuàng)立圖表之后，既可以將其置于原始數(shù)據(jù)所在的工作表上，也可以放置在單獨的工作表上。工作表的名稱顯示于工作簿窗口底部的工作表標簽上，單擊這個標簽即可進入該工作表。當前所在的工作表稱為活開工作表，在它的標簽上標有單下劃線?？梢栽谕还ぷ鞑净騼蓚€工作簿之間對工作表進展改名、添加、刪除、移動或復制等編輯操作。在默認情況下，每個工作

6、薄由3個工作表組成，用戶可以根據(jù)需要添加更多的工作表。每個工作表由256列和65 536行組成。行和列相交形成單元格，它是存儲數(shù)據(jù)的根本單位。列用英文字母表示，開場是單個英文字母，然后是2個英文字母組合，即由AIV共256列。行用阿拉伯數(shù)字表示，由165 536。每個單元格的定位可以通過該單元格所對應的行數(shù)和列標來確定，如B6就表示B列第6行的單元格。在活開工作表眾多的單元格中，其中有一單元格含有粗邊框線，此單元格為活動單元格，在該單元格中可以輸入和編輯數(shù)據(jù)。每進入一個工作表時，A1自動為活動單元格。在活動單元格的右下角有一小黑方塊，這個黑方塊稱為填充柄，利用此填充柄可以填充相鄰單元格區(qū)域的容

7、。2單元格和區(qū)域引用引用是指調(diào)用工作表中的數(shù)據(jù)用于計算分析，是通過標識工作表上的單元格或區(qū)域來實現(xiàn)的。通過引用，指明在計算時所使用的數(shù)據(jù)在工作表中的位置，可以使用工作表中不同區(qū)域的數(shù)據(jù)，或者在多個計算公式中使用同一單元格的數(shù)值。還可以引用同一工作簿不同工作表、不同工作簿、甚至其他應用程序中的數(shù)據(jù)。引用不同工作簿中的數(shù)據(jù)稱為外部引用，引用其他程序中的數(shù)據(jù)稱為遠程引用。如果要引用*一單元格的數(shù)據(jù)，可在計算公式中輸入該單元格的標識，例如要引用在列B和行10這一單元格的數(shù)據(jù)，在計算公式中輸入B10，或者用鼠標點擊該單元格。如果要引用*個區(qū)域的數(shù)據(jù)，可在計算公式中輸入該區(qū)域左上角單元格的標識，后面跟一個

8、冒號：，后面再寫上該區(qū)域右下角單元格的標識。例如，要引用從單元格C5到單元格C15這一區(qū)域，可輸入C5:C15；如要引用從單元格A5到單元格B10這一區(qū)域，即可輸入A5:B10。3絕對引用與相對引用的區(qū)別當生成公式時，對單元格或區(qū)域的引用通常基于他們與公式單元格的相對位置。如在單元格A8中包含公式=8B5，Microsoft E*cel將在距單元格A8上面一個單元格和右面一個單元格處的單元格中查找數(shù)值，這就是相對引用。當復制使用相對引用的公式時，被粘貼公式中的引用將被更新，并指向與當前公式位置相對應的其他單元格。如單元格A8中的公式被復制到單元格A10中，單元格A10中的公式已經(jīng)改為=8B7，

9、即從單元格A8上面和右面一個單元格處的單元格中查找數(shù)值。如果在復制公式時，不希望引用發(fā)生改變，可使用絕對引用。絕對引用的方式是在相對引用列標和行數(shù)字前加上美元符號，即B5。如在單元格A8中包含的公式=8B5，現(xiàn)在將公式復制到另一單元格中，則公式中的引用將不發(fā)生改變。四、作業(yè)同學們可以根據(jù)教材中實例進展驗證性練習。實驗二試驗數(shù)據(jù)整理一、目的要求了解資料搜集整理的意義，更好地掌握次數(shù)分布表、圖的制作方法，學會利用E*cel進展數(shù)據(jù)整理與圖表制作。二、材料用具裝有E*cel軟件的計算機及輸出設備。三、方法步驟1圖表向?qū)У睦糜肊*cel中的圖表向?qū)Э梢钥焖贉蚀_的繪制圖表。它是一連串的對話框，依照它逐

10、步建立或修改圖表。新建圖表需四個步驟，修改圖表只需兩個步驟。步驟1：選擇圖表類型選擇常用工具欄中的圖表向?qū)О磁ぃ瑘D標或單擊菜單欄中插入再擊圖表命令，激活圖表向?qū)υ捒颉*cel提供了兩大類的圖形：標準類型和自定義類型，用戶可根據(jù)需要選擇。步驟2：選定數(shù)據(jù)區(qū)域如果在單擊圖表向?qū)е?，就已選定數(shù)據(jù)圍，則圖表向?qū)У牡诙υ捒蚓蜁詣釉O定數(shù)據(jù)區(qū)域。如果事先沒有設定，用戶可自己設定。步驟3：設定圖表選項完成步驟2之后，單擊下一步按鈕進入步驟3。在步驟3里有很多的選項卡：標題、坐標軸、網(wǎng)格線、圖例、數(shù)據(jù)標志、數(shù)據(jù)表。步驟4：指定圖表位置當完成步驟3后，單擊下一步按鈕即可進入步驟4?？梢赃x定作為新工作表插

11、入或作為其中的對象插入。然后單擊完成按鈕完畢圖表向?qū)В瓿闪藞D表的制作。2數(shù)據(jù)分析工具欄運用現(xiàn)以水稻品種120行產(chǎn)量kg為例，說明其方法與步驟。表1 水稻品種120行產(chǎn)量kg表17721519797159245119119131152167104161214125175188192176175951991161652149515883801381511871261342061379897129143174159165136108141148168163176102194173751301491501551111581311899114215215416312320515513120918397

12、119181187131215111186150155197116254239160179151198124179184168169173181188211175122151171166143190213192231163159177147194227169124159159圖5 原始數(shù)據(jù)示意圖1翻開E*cel，輸入原始數(shù)據(jù)和各組的下限，樣式見圖5，圖中從A1單元格到L10單元格的區(qū)域為原始數(shù)據(jù)，各組的組中值或下限值位于M1到M11單元格。2從工具菜單中選定數(shù)據(jù)分析命令，再選定直方圖，確定后出現(xiàn)直方圖對話框。3在輸入?yún)^(qū)域中輸入A1:L10，在接收區(qū)域中輸入M1:M11，在輸出選項中選定輸出區(qū)域

13、如A12后，單擊確定，得到頻數(shù)分布表。假設在直方圖對話框中選定圖表輸出復選框，則直方圖也同時輸出，如圖6。注意：雙擊分類軸，在數(shù)據(jù)系列選項中將分類間距設為0，才得直方圖，不然只得到條形圖。圖6 直方圖示意圖四、作業(yè)1利用E*cel將表中的數(shù)據(jù)進展排序并分組，然后編制次數(shù)分布表。2利用E*cel 將整理后的數(shù)據(jù)繪制直方圖和折線圖。實驗三特征數(shù)計算一、目的要求掌握各種特征數(shù)的計算方法，學會利用E*cel進展特征數(shù)的計算。二、材料用具練習紙、鉛筆、裝有E*cel軟件的計算機及輸出設備或計算器。三、方法步驟在生物統(tǒng)計分析中主要計算平均數(shù)和變異數(shù)。平均數(shù)主要有算術(shù)平均數(shù)、幾何平均數(shù)及中數(shù)，變異數(shù)主要有極

14、差、方差、標準差、變異系數(shù)。1算術(shù)平均數(shù)用AVERAGE函數(shù)可用來計算算術(shù)平均數(shù)，例如一組數(shù)據(jù)2，3，4，5，6的平均數(shù)可在單元格中鍵入=AVERAGE2，3，4，5，6得 4。如果這5個數(shù)據(jù)分別存放于A1到A5的字段中，則鍵入= AVERAGEA1:A5得4。通常平均數(shù)是指算術(shù)平均數(shù)。2幾何平均數(shù)用GEOMEAN函數(shù)可用來計算幾何平均數(shù)，例如6，15，35，105，260的幾何平均數(shù)可在單元格中鍵入=GEOMEAN6，15，35，105，260得38.63。3中數(shù)計算如果觀察值次數(shù)是偶數(shù)，則取中間兩個觀察值的算術(shù)平均數(shù)作為中數(shù)。用MEDIAN函數(shù)可以計算中數(shù)。如在單元格中輸入=MEDIAN2

15、，3，4，7，8得4；在單元格中輸入=MEDIAN2，3，4，7得3.5。4極差資料中最大值減去最小值是極差，可以利用MA*求得最大值，用MIN 求得最小值，兩者的差數(shù)就是極差。5方差平方和除以自由度即為方差。用VARP函數(shù)可以求總體方差；用VAR函數(shù)可以求樣本的方差。如在單元格中輸入=VARP1，2，3，4，5得2；在單元格中輸入=VAR1，2，3，4，5得2.5。6標準差就是方差開平方。用STDEVP可以計算總體標準差；用STDEV則可以計算樣本標準差。如在單元格中輸入=STDEVP1，2，3，4，5得1.414214；在單元格中輸入=STDEV1，2，3，4，5得1.581139。7變異

16、系數(shù)標準差除以平均數(shù)就是變異系數(shù)。但E*cel本身沒有提供函數(shù)計算變異系數(shù)，我們可以通過STDEV計算樣本標準差，用AVERAGE計算平均數(shù)，兩者相除即得變異系數(shù)。四、作業(yè)1利用E*cel 查找最大值、最小值，計算算術(shù)平均數(shù)等特征數(shù)。2利用E*cel 計算思考練習題的中的方差和標準差。實驗四概率和概率分布一、目的要求掌握概率和概率計算。了解二項分布、正態(tài)分布原理，練習主要理論分布的概率計算及幾種常用理論分布的使用方法。二、材料用具練習紙、鉛筆、裝有E*cel軟件的計算機及輸出設備或計算器。三、方法步驟1二項分布的概率計算例如：觀察施用*種農(nóng)藥后棉鈴蟲的死亡概率為0.65，現(xiàn)對10頭棉鈴蟲噴施該

17、農(nóng)藥，問死亡8頭及死亡8頭以下的概率是多少？棉鈴蟲死亡頭數(shù)服從二項分布。上述兩個概率可以用E*cel中的函數(shù)BINOMDIST來計算，操作方法如下：圖7 二項分布概率結(jié)果對話框示意圖在E*cel工作表中單擊要輸出這一計算結(jié)果的單元格，然后單擊按紐f*，在選擇類別下拉列表框中中選擇統(tǒng)計，在其下方的選擇函數(shù)列表中選擇BINOMDIST函數(shù)，單擊確定按紐，彈出BINOMDIST函數(shù)的對話框。在對話框中的Number_s框中輸入8，表示8頭死亡；在Trials框中輸入10，表示一共進展了10次獨立的試驗；在Probability_s框中輸入0.65，表示棉鈴蟲死亡率為0.65；在Cumulative框

18、中，有二項FALSE和TRUE供選擇，F(xiàn)ALSE用于計算單一概率，TRUE用于計算累積概率。如輸入FALSE，計算的是死亡8頭的概率，如輸入TRUE，計算的是死亡8頭和8頭以下的概率。按確定按鈕后，在選定的單元格中就顯示所需要的結(jié)果。如果需要改變剛剛的計算容，將光標移到該單元格上，此時在編輯欄中顯示 BINOMDIST8，10，0.65，F(xiàn)ALSE，在此可以直接改變其中的容。2正態(tài)分布概率的計算1一般正態(tài)分布概率的計算例如：有一玉米果穗長度的正態(tài)總體，其平均數(shù)=20cm，標準差=3.4cm，試計算：果穗長度*13cm的概率。短果穗中，概率占10%的臨界果穗長度是多少？E*cel提供了正態(tài)分布概

19、率計算的二個函數(shù)NORMDIST和NORMINV，NORMDIST用來計算正態(tài)分布的概率密度函數(shù)值和累積概率函數(shù)值，NORMINV用來計算正態(tài)分布累積函數(shù)的逆函數(shù)。用NORMDIST函數(shù)計算上例中概率的步驟為：翻開NORMDIST函數(shù)對話框，在*后的編輯框中輸入13，在Mean后的編輯框中輸入20，在Standard_dev后的編輯框中輸入3.4，在Cumulative后的編輯框中輸入TRUE，然后按確定按鈕，顯示累積分布函數(shù)值為0.0197。如果在Cumulative后的編輯框中輸入FALSE，這時函數(shù)NORMDIST就返回概率密度函數(shù)值，結(jié)果顯示0.0141，即正態(tài)曲線在*=13時的縱高。圖8 正態(tài)分布概率結(jié)果對話框示意圖用NORMINV函數(shù)計算上例中*的步驟為：翻開NORMINV函數(shù)對話框，在Probability后的編輯框中輸入0.1，在Mean后的編輯框中輸入20，在Standard_dev后的編輯框中輸入3.4，然后按確定按鈕，顯示區(qū)間點為15.6，即果穗長在15.6cm以下的果穗占總體的10%。圖9 正態(tài)分布概率結(jié)果對話框示意圖2標準正態(tài)分布概率計算 E*cel提供了標準正態(tài)分布概率計算的二個函數(shù)NORMSDIST和NORMSINV，圖11 標準正態(tài)分布結(jié)果對話框示意圖圖10