1、金衛斌主編Excel在生物統計中應用一、目的要求使學生掌握Excel在生物統計中的基本應用方法。二、材料用具練習紙、鉛筆、裝有Excel軟件的計算機及輸出設備或計算器。三、方法步驟1啟動Excel2加載“分析工具庫”在默認的情況下，Excel是沒有加載“分析工具庫”，無法進行統計分析。先查看計算機是否加載“分析工具庫”。首先啟動Excel，檢查“工具”菜單中是否有“數據分析”命令。如果沒有發現“數據分析”命令，就表示未加載“分析工具庫”。可按照下列步驟來加載“分析工具庫”。（1）選取“工具”菜單中的“加載宏”命令，彈出“加載宏”的對話框。圖1 “加載宏”對話框示意圖（2）選中“分析工具庫”復選

2、框，單擊“確定”按鈕，按提示完成加載過程。此時再查看“工具”菜單，就可發現“數據分析”命令，表示安裝完成，單擊“工具”菜單中“數據分析”命令，得圖2，即可進行相關的統計分析。圖2 “數據分析”對話框示意圖3分析工具庫提供的統計分析方法分析工具庫提供的常用統計分析方法如下：1 單向分類資料的方差分析*雙向分類交叉分組無重復資料的方差分析*雙向分類交叉分組有重復資料的方差分析*描述性統計量計算*二總體方差的同質性檢驗編制頻數分布表及繪制直方圖排位與百分比排位二樣本均數差數的u檢驗配對資料的t檢驗*等方差非配對資料的t檢驗*異方差非配對資料的t檢驗一元線性回歸與多元線性回歸分析*計算多個變量兩兩之間

3、的相關系數及協方差*進行隨機和順序抽樣4統計分析的粘貼函數Excel提供的統計分析粘貼函數如下：AVERAGEBINOMDISTCHIDISTCHIINVCHITESTCONFIDENCECORRELCOVARFDISTFINVFTESTGEOMEANHARMEANINTERCEPTMAX MEDIANMINMODENORMDISTNORMINVNORMSDISTNORMSINVPOISSONSLOPESTDEVSTDEVPTDISTTINVTTESTVARVARPZTEST計算算術平均值*計算二項式分布的概率值計算特定2分布的單尾概率值計算一定單尾概率值時的2臨界值計算獨立性檢驗的2值計算總

4、體平均值的置信區間計算兩組數據的相關系數*計算兩組數據的協方差計算特定F分布的單尾概率值*計算一定概率時的臨界F值*計算二個樣本方差之比F值的概率*計算幾何平均數計算調和平均數計算直線回歸的截距*計算最大值*計算一組給定數字的中位數*計算最小值*計算一組數據的眾數*計算正態分布的累積函數計算正態分布累積函數的逆函數計算標準正態分布的累積函數計算標準正態分布累積函數的逆函數計算泊松分布的概率計算給定數據的直線回歸系數*計算樣本標準差*計算樣本總體的標準差計算學生氏-t分布的概率值*計算特定概率時學生氏-t分布的臨界t值*計算t檢驗時的學生氏-t檢驗相關的概率計算樣本的方差*計算樣本總體的方差計算

5、Z檢驗的雙尾概率值上述帶*者就是我們要學習和掌握的內容5Excel的常用基本概念（1）工作薄和工作表Microsoft Excel工作簿是計算和儲存數據的文件，每一個工作簿都可以包含多張工作表，因此，可在單個文件中管理各種類型的相關信息。使用工作表可以對數據進行組織和分析，可以同時在多張工作表上輸入和編輯數據，并且可以對來自不同工作表的數據進行匯總計算。在創建圖表之后，既可以將其置于原始數據所在的工作表上，也可以放置在單獨的工作表上。工作表的名稱顯示于工作簿窗口底部的工作表標簽上，單擊這個標簽即可進入該工作表。當前所在的工作表稱為活動工作表，在它的標簽上標有單下劃線。可以在同一工作簿內或兩個工

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

7、的右下角有一小黑方塊，這個黑方塊稱為填充柄，利用此填充柄可以填充相鄰單元格區域的內容。（2）單元格和區域引用引用是指調用工作表中的數據用于計算分析，是通過標識工作表上的單元格或區域來實現的。通過引用，指明在計算時所使用的數據在工作表中的位置，可以使用工作表中不同區域的數據，或者在多個計算公式中使用同一單元格的數值。還可以引用同一工作簿不同工作表、不同工作簿、甚至其他應用程序中的數據。引用不同工作簿中的數據稱為外部引用，引用其他程序中的數據稱為遠程引用。如果要引用某一單元格的數據，可在計算公式中輸入該單元格的標識，例如要引用在列B和行10這一單元格的數據，在計算公式中輸入B10，或者用鼠標點擊該

8、單元格。如果要引用某個區域的數據，可在計算公式中輸入該區域左上角單元格的標識，后面跟一個冒號（：），后面再寫上該區域右下角單元格的標識。例如，要引用從單元格C5到單元格C15這一區域，可輸入C5:C15；如要引用從單元格A5到單元格B10這一區域，即可輸入A5:B10。（3）絕對引用與相對引用的區別當生成公式時，對單元格或區域的引用通常基于他們與公式單元格的相對位置。如在單元格A8中包含公式=8×B5，Microsoft Excel將在距單元格A8上面一個單元格和右面一個單元格處的單元格中查找數值，這就是相對引用。當復制使用相對引用的公式時，被粘貼公式中的引用將被更新，并指向與當前公

9、式位置相對應的其他單元格。如單元格A8中的公式被復制到單元格A10中，單元格A10中的公式已經改為=8×B7，即從單元格A8上面和右面一個單元格處的單元格中查找數值。如果在復制公式時，不希望引用發生改變，可使用絕對引用。絕對引用的方式是在相對引用列標和行數字前加上美元符號，即B5。如在單元格A8中包含的公式=8×B5，現在將公式復制到另一單元格中，則公式中的引用將不發生改變。1 試驗數據整理1）圖表向導的利用用Excel中的“圖表向導”可以快速準確的繪制圖表。它是一連串的對話框，依照它逐步建立或修改圖表。新建圖表需四個步驟，修改圖表只需兩個步驟。步驟1：選擇圖表類型圖4 表

10、類型示意圖選擇“常用”工具欄中的“圖表向導”按扭，如圖3圖標或單擊菜單欄中“插入”再擊“圖表”命令，激活圖表向導對話框，如圖4。圖3 圖表按鈕Excel提供了兩大類的圖形：標準類型和自定義類型，用戶可根據需要選擇。步驟2：選定數據區域如果在單擊圖表向導之前，就已選定數據范圍，那么圖表向導的第二對話框就會自動設定數據區域。如果事先沒有設定，用戶可自己設定。步驟3：設定圖表選項完成步驟2之后，單擊“下一步”按鈕進入步驟3。在步驟3里有很多的選項卡：“標題”、“坐標軸”、“網格線”、“圖例”、“數據標志”、“數據表”。步驟4：指定圖表位置當完成步驟3后，單擊“下一步”按鈕即可進入步驟4。可以選定“作

11、為新工作表插入”或“作為其中的對象插入”。然后單擊“完成”按鈕結束圖表向導，完成了圖表的制作。2）“數據分析”工具欄運用現以水稻品種120行產量（kg）為例，說明其方法與步驟。表1 水稻品種120行產量（kg）表17721519797159245119119131152167104161214125175188192176175951991161652149515883801381511871261342061379897129143174159165136108141148168163176102194173751301491501551111581311899114215215416312

12、320515513120918397119181187131215111186150155197116254239160179151198124179184168169173181188211175122151171166143190213192231163159177147194227169124159159圖5 原始數據示意圖（1）打開Excel，輸入原始數據和各組的下限，樣式見圖5，圖中從A1單元格到L10單元格的區域為原始數據，各組的下限值位于M1到M11單元格。（2）從“工具”菜單中選定“數據分析”命令，再選定“直方圖”，確定后出現直方圖對話框。（3）在輸入區域中輸入A1:L10，在

13、接收區域中輸入M1:M11，在輸出選項中選定輸出區域如A12后，單擊確定，得到頻數分布表。若在“直方圖”對話框中選定“圖表輸出”復選框，則直方圖也同時輸出，如圖6。圖6 直方圖示意圖2. 特征數計算在生物統計分析中主要計算平均數和變異數。平均數主要有算術平均數、幾何平均數及中數，變異數主要有極差、方差、標準差、變異系數。1）算術平均數用AVERAGE函數可用來計算算術平均數，例如一組數據2，3，4，5，6的平均數可在單元格中鍵入“=AVERAGE（2，3，4，5，6）”得 4。如果這5個數據分別存放于A1到A5的字段中，則鍵入“= AVERAGE（A1:A5）”得4。通常“平均數”是指算術平均

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

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

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

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

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

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

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

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