excel-醫(yī)藥統(tǒng)計學(xué)-公式使用和技巧

1、創(chuàng)作人：王志波 地址：青海民族大學(xué) 12藥學(xué)Qq：1961956988,1、單因素方差分析2、二因素交叉無重復(fù)的方差分析3、二因素交叉有重復(fù)的方差分析4、描述統(tǒng)計分析5、二樣本方差的F檢驗6、編制頻數(shù)分布表及繪制直方圖7、排位與百分位比排位8、二樣本均數(shù)差數(shù)的u檢驗9、配對資料的t檢驗10、等方差非配對資料的t檢驗11、異方差非配對資料的t檢驗12、多元回歸和直線回歸分析13、計算多個變量二二之間的相關(guān)系數(shù)及

2、協(xié)方差14、進行隨機和順序抽樣,分析工具庫提供的統(tǒng)計分析方法,由“工具”菜單打開“加載宏”命令,分析工具庫的安裝,數(shù)據(jù)分析工具外觀,1、AVERAGE計算算術(shù)平均值2、BINOMDIST計算二項式分布的概率值3、CHIDIST計算特定χ2分布的單尾概率值4、CHIINV計算一定單尾概率值時的χ2臨界值5、CHITEST計算獨立性檢驗的χ2值6、CONFIDENCE計算總體平均值的置信區(qū)間7、CORRE

3、L計算兩組數(shù)據(jù)的相關(guān)系數(shù)8、COVAR計算兩組數(shù)據(jù)的協(xié)方差9、FDIST計算特定F分布的單尾概率值10、FINV計算一定概率時的臨界F值11、FTEST計算二個樣本方差之比F值的概率12、GEOMEAN計算幾何平均數(shù)13、HARMEAN計算調(diào)和平均數(shù)14、INTERCEPT計算直線回歸的截距15、MAX計算最大值16、MEDIAN計算一組給定數(shù)字的中位數(shù),EXCEL電子表格提供的粘

4、帖函數(shù)（一）,17、MIN計算最小值18、MODE計算一組數(shù)據(jù)的眾數(shù)19、NORMDIST計算正態(tài)分布的累積函數(shù)20、NORMINV計算正態(tài)分布累積函數(shù)的逆函數(shù)21、NORMSDIST計算標準正態(tài)分布的累積函數(shù)22、NORMSINV計算標準正態(tài)分布累積函數(shù)的逆函數(shù)23、POISSON計算泊松分布的概率24、SLOPE計算給定數(shù)據(jù)的直線回歸系數(shù)25、STDEV計算樣本標準差26、STDEVP

5、計算樣本總體的標準差27、TDIST計算學(xué)生氏-t分布的概率值28、TINV計算特定概率時學(xué)生氏-t分布的臨界t值29、TTEST計算t檢驗時的學(xué)生氏-t檢驗相關(guān)的概率30、VAR計算樣本的方差31、VARP計算樣本總體的方差32、ZTEST計算u-檢驗的雙尾概率值,EXCEL電子表格提供的粘帖函數(shù)（二）,#####錯誤原因：公式產(chǎn)生的結(jié)果太長，單元格容納不下。解決辦法：適當增加列的寬度。#

6、NIV/0！錯誤原因：除數(shù)為零。在公式中，除數(shù)使用了空白單元格或包含零值的單元格引用。解決辦法：修改單元格引用，或在用作除數(shù)的單元格中輸入不為零的值。#N/A錯誤原因：表示在函數(shù)和公式中沒有可用的數(shù)值可以引用。解決辦法：檢查公式中引用的元格的數(shù)據(jù)，并輸入正確數(shù)據(jù)。,使用公式和函數(shù)時出現(xiàn)的常見錯誤信息（一）,#NAME？錯誤原因：刪除了公式中使用名稱或使用了不存在的名稱以及拼寫錯誤。解決辦法：確認使用的名稱確實存在。#NU

7、LL！錯誤原因：使用了不正確的區(qū)域運算或不正確的單元格引用。解決辦法：如果要引用2個不相交的區(qū)域，請使用聯(lián)合運算符（逗號）。例如，=SUM（B2：B5，D2：D5）表示對這2個不相交區(qū)域的引用。#NUM！錯誤原因：在需要數(shù)字參數(shù)的函數(shù)中使用了不能接受的參數(shù)或公式產(chǎn)生的數(shù)字太大或太小，EXCEL不能表示。如在計算臨界t值或F值時，輸入的概率值大于1或小于0。解決辦法：檢查數(shù)字是否超出限定區(qū)域，函數(shù)內(nèi)的參數(shù)是否正確。,使用公式和函

8、數(shù)時出現(xiàn)的常見錯誤信息（二）,#REF！錯誤原因：刪除了由其它公式引用的單元格或?qū)⒁苿訂卧裾程接善渌玫膯卧裰?。解決辦法：檢查引用單元格是否被刪除，或者啟動相應(yīng)的應(yīng)用程序。#VALUE！錯誤原因：需要數(shù)字或邏輯值時輸入了文本。解決辦法：確認公式或函數(shù)所需的運算符或參數(shù)正確，并且公式引用的單元格中包含有效的數(shù)值。,使用公式和函數(shù)時出現(xiàn)的常見錯誤信息（三）,二項分布,平均數(shù)、方差和標準差,二項分布的概率函數(shù)為,二項分布概率

9、的計算實例,已知某種豬病的死亡率為30%，現(xiàn)有10頭病豬，如不給予治療，問死亡4頭及死亡4頭和4頭以下的概率為多少？,死亡4頭的概率計算公式為：,死亡4頭和4頭以下概率的計算公式為：,用粘帖函數(shù)BINOMDIST計算,死亡4頭的概率計算,本計算在編緝欄中為BINOMDIST（4，10，0.30，F(xiàn)ALSE）,死亡4頭和4頭以下概率的計算,本計算在編緝欄中為BINOMDIST（4，10，0.30，TRUE）,普哇松分布,普哇松分布的概率函

10、數(shù),?為常數(shù)，它等于平均數(shù)等于方差,,?=?=?2,普哇松分布概率的計算實例,已知某一地區(qū)，出現(xiàn)怪胎的事件服從普哇松分布P（2），請計算該地區(qū)出現(xiàn)3次怪胎的概率，及出現(xiàn)3次和3次以下怪胎的概率為多少？,出現(xiàn)3次怪胎概率的公式為：,出現(xiàn)3次和3次以下怪胎的概率計算公式為：,,,用POISSON粘帖函數(shù)計算,出現(xiàn)3次怪胎概率的計算,本計算在編緝欄中顯示POISSON(3，2，F(xiàn)ALSE),出現(xiàn)3次和3次以下怪胎的概率計算,本計算編緝欄中顯示

11、POISSON(3，2，TRUE),正態(tài)分布,用符號N（?，?2） 表示,正態(tài)分布的概率函數(shù)為：,,,NORMDIST粘帖函數(shù)：計算累積函數(shù) NORMINV粘帖函數(shù)：計算逆函數(shù),已知某品種成年豬體重的總體平均數(shù)?=100kg，總體標準差?=20kg。試計算成年豬體重在70kg以下的概率。計算公式如下：,,用正態(tài)分布粘帖函數(shù)的計算,用NORMDIST粘帖函數(shù)計算概率,本計算在編緝欄中的形式為NORMDIST(70，100，20，TRUE

12、),用NORMDIST粘帖函數(shù)計算函數(shù)值,,本計算在編緝欄中的形式為NORMDIST(70，100，20，F(xiàn)ALSE),已知某品種成年豬體重的總體平均數(shù)?=100kg，總體標準差?=20kg。如果我們希望淘汰30%體重最輕的豬，問體重在多少kg以下的豬應(yīng)給予淘汰。計算公式如下：,,用NORMINV粘貼函數(shù)計算,本計算在編緝欄中的形式為NORMINV(0.30，100，20),標準正態(tài)分布,用符號N（0，1） 表示,標準正態(tài)分布的概率函數(shù)

13、為：,,標準化的公式為：,,利用標準正態(tài)分布函數(shù)計算概率,NORMSDIST粘貼函數(shù)：計算累積函數(shù) NORMSINV粘貼函數(shù) ：計算逆函數(shù),已知某品種成年豬體重的總體平均數(shù)?=100kg，總體標準差?=20kg。試計算成年豬體重在70kg以下的概率。首先對70kg進行標準化，得u值等于-1.5，計算公式如下：,,用NORMSDIST粘帖函數(shù)計算概率,本例在編緝欄中的形式是NORMSDIST(-1.5),,當給定一尾概率值時，求其臨界值

14、就得利用NORMSINV函數(shù)。假定現(xiàn)要計算當一尾概率為0.025時，計算公式如下：,,用NORMSINV粘貼函數(shù)計算,本例在編緝欄中的形式是NORMSINV(0.025),學(xué)生氏t分布,t分布的概率密度函數(shù)為,,,學(xué)生氏t分布的計算實例,TDIST粘貼函數(shù)：單尾或雙尾概率 TINV粘貼函數(shù)：臨界t值 （二尾概率 ）,,,用TDIST粘貼函數(shù)計算二尾概率,本計算在編緝欄中輸入TDIST（2，60，2）,例如現(xiàn)要計算自由度等于60，t值與

15、平均數(shù)相差2以上的2尾概率,用TINV粘貼函數(shù)計算臨界值,請計算自由度為10且二尾概率為0.05時的臨界t值,本計算在編緝欄中為TINV（0.05，10）,卡方分布,卡方分布概率密度函數(shù)為：,,卡方分布的計算實例,CHIDIST粘貼函數(shù)：計算單尾概率 CHIINV粘貼函數(shù)：計算臨界值,CHIDIST函數(shù)積分公式為：,,CHIINV函數(shù)積分公式為：,,用CHIDIST粘貼函數(shù)計算概率,請計算自由度等于1及卡方值等于3.84時的一尾概率,

16、本計算在編緝欄中為CHIDIST（3.84，1）,用CHIINV粘貼函數(shù)計算臨界值,請計算自由度為10及一尾概率為0.01時的?2臨界值,本計算在編緝欄中為CHIINV（0.01，10）,F分布,F分布的概率密度函數(shù)為：,,F>0,F分布的計算實例,FDIST粘貼函數(shù)：計算單尾概率FINV粘貼函數(shù)：計算臨界值,FDIST函數(shù)積分公式為：,,FINV函數(shù)積分公式為：,,用函數(shù)FDIST計算一尾概率,請計算第一自由度等于2、第二自由

17、度等于4及F值等于18時的一尾概率,本計算在編緝欄中為FDIST（18，2，4）,用函數(shù)FINV計算臨界F值,請計算df1=3，df2=10及一尾概率為0.05時的臨界F值,本計算在編緝欄中為FINV（0.05，3，10）,EXCEL電子表格的模擬運算 功能,模擬運算表是工作表中的一個單元格區(qū)域，它可以顯示公式中某些值的變化對計算結(jié)果的影響。模擬運算表為同時求解某一運算中所有可能的變化值的組合提供了捷徑，并且還可以將所有不同的計算結(jié)果同

18、時顯示在工作表中，便于查找和比較。模擬運算表有兩種類型：單變量模擬運算表 雙變量模擬運算表,利用模擬運算計算臨界t值表,單變量模擬計算結(jié)果,利用模擬運算計算臨界F值表,雙變量模擬計算結(jié)果,次數(shù)分布表,直方圖對話框輸入格式圖示,次數(shù)分布表、柏拉圖表及其累積頻率,次數(shù)分布圖（直方圖）,離散型數(shù)據(jù),雜一代白毛黑斑豬自交后的毛色分離情況,將白、黑和花分別轉(zhuǎn)換成1、2和3，然后利用直方圖工具求出頻數(shù)分布表，再將1、2和3恢復(fù)成白、黑和花就得到

19、了這三種毛色豬的次數(shù)分布表，其結(jié)果為白、黑和花三種毛色豬的次數(shù)分別為39、17和16頭,描述統(tǒng)計分析,描述統(tǒng)計分析的基本統(tǒng)計量（一）,算術(shù)平均數(shù),中位數(shù),樣本方差,描述統(tǒng)計分析的基本統(tǒng)計量（二）,樣本標準差,標準誤,偏斜度,偏斜度=0，分布對稱，偏斜度為正值，分布正偏，即眾數(shù)位于算術(shù)平均數(shù)的左側(cè)，偏斜度為負值，分布負偏，即眾數(shù)位于算術(shù)平均數(shù)的右側(cè),描述統(tǒng)計分析的基本統(tǒng)計量（三）,峰值（峭度）,置信半徑,峰值=0，正態(tài)分布，峰值為正值，曲

20、線過于陡峭，峰值為負值，曲線過于平坦,描述統(tǒng)計分析對話框,200頭大白母豬仔豬一月窩重的描述統(tǒng)計分析結(jié)果,樣本均數(shù)與總體均數(shù)差異顯著性檢驗?t檢驗,無效假設(shè)為Ho：???o備擇假設(shè)為HA：???o計算公式如下：,根據(jù)以上公式可導(dǎo)出以下結(jié)論：,由此可知，當樣本平均數(shù)落在已知的總體均數(shù)置信概率為（1-?）的置信區(qū)間以外時，就表明在?顯著水平時差異顯著,已知約克夏母豬體重的總體平均數(shù)?o=130kg，現(xiàn)隨機抽測10頭母豬的體重，數(shù)據(jù)如下：

21、121、127、103、132、157、133、130、139、140、136（kg），試檢驗該樣本是否來自總體均數(shù)為130kg的總體。,顯著性檢驗計算結(jié)果,均數(shù)差異的顯著性檢驗,等方差假設(shè)時的成組資料t檢驗異方差假設(shè)時的成組資料t檢驗成對資料的t檢驗,等方差假設(shè)時的成組資料t檢驗,檢驗的基本公式,現(xiàn)隨機抽測8頭大白豬與8頭哈白豬經(jīng)產(chǎn)母豬產(chǎn)仔數(shù)資料如下（單位：頭）：,異方差假設(shè)時的成組資料t檢驗,檢驗的基本公式,雙樣本等方差假設(shè)t檢

22、驗結(jié)果,現(xiàn)隨機抽測8頭大白豬與8頭哈白豬經(jīng)產(chǎn)母豬產(chǎn)仔數(shù)資料如下（單位：頭）：,雙樣本異方差假設(shè)t檢驗結(jié)果,成對資料的t檢驗,檢驗的基本公式,,現(xiàn)用國產(chǎn)與進口的膘厚測定儀，對14頭肥豬進行了測定（單位：mm） ，數(shù)據(jù)如下：,試檢驗兩種儀器測定的結(jié)果有無顯著差異？,成對資料的t檢驗結(jié)果,方差分析,單因素方差分析二因素交叉無重復(fù)方差分析二因素交叉有重復(fù)方差分析,單因素方差分析,具有k組每組n個觀察值的數(shù)據(jù)模式,SST=SSA+SSedf

23、T=dfA+dfe,單因素方差分析實例,現(xiàn)有5個不同品種的若干頭母豬的窩產(chǎn)仔數(shù)資料見下表，試檢驗不同品種母豬平均窩產(chǎn)仔數(shù)的差異顯著性。,五個不同品種母豬產(chǎn)仔數(shù)的方差分析表,基本公式,單因素方差分析表,單因素方差分析示意圖,二因素交叉無重復(fù)方差分析,二因素交叉無重復(fù)觀察值的資料模式,SST=SSA+SSB+SSedfT=dfA+dfB+dfe,平方和及自由度的簡易計算公式如下：,二因素交叉無重復(fù)觀察值的方差分析表,二因素交叉無重復(fù)觀察值

24、的方差分析實例,無重復(fù)雙因素方差分析,品種與飼料對豬增重的方差分析表,二因素交叉有重復(fù)方差分析,二因素交叉有重復(fù)觀察值的資料模式,平方和及自由度的簡易計算公式如下：,二因素交叉無重復(fù)觀察值的方差分析表,二因素交叉有重復(fù)觀察值的方差分析實例,為了考察飼料中鈣和磷的含量對幼豬生長發(fā)育的影響，將鈣（A）和磷（B）在飼料中的含量各分為4個水平進行試驗，每個水平組合3頭豬，經(jīng)2個月飼養(yǎng)試驗，得幼豬增重結(jié)果列于下表。試分析鈣和磷及它們之間的交互作用

25、對幼豬生長發(fā)育的影響。,不同鈣磷用量的試驗豬增重結(jié)果（單位：kg）,可重復(fù)雙因素分析示意圖,不同鈣磷用量試驗豬增重結(jié)果的方差分析,回歸分析基本公式,直線回歸方程的顯著性檢驗,回歸方程顯著性檢驗的方差分析表,回歸系數(shù)和回歸截距的顯著性檢驗,95%回歸系數(shù)的置信區(qū)間為：95%回歸截距的置信區(qū)間為：,現(xiàn)有10只綿羊的胸圍（x，cm）和體重（y，kg）的數(shù)據(jù)下表，請進行回歸分析。,直線回歸計算實例,復(fù)相關(guān)分析（回歸統(tǒng)計）表,上式中當方程中有截

26、距時i取值為1，否則為0,回歸方程的方差分析,回歸系數(shù)和回歸截距的顯著性檢驗表,多元回歸分析,多元回歸分析的矩陣表示,多元回歸方程的顯著性檢驗,多元回歸方程顯著性檢驗的方差分析表,偏回歸系數(shù)和回歸常數(shù)的顯著性檢驗,偏回歸系數(shù)及常數(shù)項的95%置信區(qū)間為,多元回歸計算實例,現(xiàn)有某豬場50頭肥豬5項胴體性狀資料的數(shù)據(jù)列于下表，其中瘦肉量依變量y，眼肌面積為x1、腿肉量為x2、腰肉量為x3、椎骨數(shù)為x4，擬配合估計瘦肉量的最優(yōu)回歸方程。,多元回

27、歸分析對話框示意圖,復(fù)相關(guān)分析（回歸統(tǒng)計）表,多元回歸方程的方差分析,偏回歸系數(shù)和回歸常數(shù)的顯著性檢驗表,相關(guān)系數(shù)與協(xié)方差的計算,簡單相關(guān)系數(shù)的基本公式,簡單相關(guān)系數(shù)的計算實例,用多元回歸的例題,簡單相關(guān)系數(shù)計算結(jié)果,協(xié)方差的計算,協(xié)方差的基本公式,總體協(xié)方差,樣本協(xié)方差,粘帖函數(shù)（COVAR函數(shù)）：總體協(xié)方差協(xié)方差分析工具法：樣本協(xié)方差分析,協(xié)方差的計算實例,用多元回歸的例題,協(xié)方差計算結(jié)果,偏相關(guān)系數(shù)的計算,,任意級偏相關(guān)系數(shù)的基

28、本公式如下：,上式計算所得的偏相關(guān)系數(shù)是（n-2）級的，即n個變量中有（n-2）個保持不變，其中Cij是簡單相關(guān)系數(shù)矩陣的逆矩陣中第i行第j列的元素，Cii和Cjj逆矩陣主對角線上第i和第j個元素。偏相關(guān)系數(shù)下標中圓點前的下標表示相關(guān)的2個變量，圓點后的下標表示保持不變的變量。,偏相關(guān)系數(shù)的計算實例,多元回歸分析的例題用粘帖函數(shù)MINVERSE計算出相關(guān)系數(shù)矩陣的逆矩陣,簡單相關(guān)系數(shù)矩陣的逆矩陣,,,協(xié)方差矩陣的逆矩陣,單因素一元協(xié)方

29、差分析,各組平方和的計算公式為：,乘積和計算的基本公式如下：,，，，或,協(xié)方差分析簡表,,回歸系數(shù)的計算公式,校正自由度的計算公式,總校正自由度,組內(nèi)校正自由度,組間校正自由度或,校正平方和的計算公式為,總校正平方和,組內(nèi)校正平方和,組間校正平方和,校正均方的計算公式,,組間校正均方,,組內(nèi)校正均方,,F檢驗公式為,協(xié)方差分析簡表的構(gòu)建,F5=E5?C5,寫入公式后的協(xié)方差分析簡表,協(xié)方差分析實例,在研究3種不同飼料對豬

30、的增重效果時，用24頭始重不一樣的幼豬分為3組，每組8頭進行試驗，各組的增重結(jié)果見下表。,原始數(shù)據(jù)經(jīng)計算后所得的組平均數(shù)和總和,原始數(shù)據(jù)經(jīng)計算后所得的平方和和乘積和,各項乘積和的計算,根據(jù)乘積公式進行計算；計算相關(guān)系數(shù)，然后除以二個變量平方和的幾何平均數(shù)；計算樣本協(xié)方差，然后用自由度與樣本協(xié)方差相乘；計算總體協(xié)方差，然后用樣本含量與總體協(xié)方差相乘,各項乘積和的計算，有以下幾種方法：,計算總體協(xié)方差，然后用樣本含量與總體協(xié)方差相乘,

31、校正增重的方差分析,校正組間平均數(shù)的多重比較,用LSD法時公用均數(shù)差數(shù)標準誤的計算公式為：,,采用SSR法或q法檢驗時公用均數(shù)差數(shù)標準誤的計算公式為：,,,本例采用LSD法進行檢驗，計算公用均數(shù)差數(shù)標準誤如下：,,1%和5%的最小顯著差數(shù)計算如下：5%LSD=2.086?2.70=5.631%LSD=2.845?2.70=7.68,校正增重的多重比較,進行q檢驗，公用均數(shù)差數(shù)標準誤的計算如下：,,LSR值計算表,根據(jù)dfe=20，k

32、=2，3查q值表，并計算相應(yīng)的最小顯著極差得下表,線性規(guī)劃求解畜禽飼料配方,線性規(guī)劃求解畜禽飼料配方的數(shù)學(xué)模型,豬飼料配方實例,現(xiàn)有玉米（x1）、四號粉（x2）、麩皮（x3）、菜籽餅（x4）、豆粕（x5）、進口魚粉（x6）、磷酸氫鈣（x7）、石粉（x8）和食鹽（x9）等9種原料，它們的價格分別為每公斤1.3、1.26、1.24、1.2、1.58、4.8、2.0、0.24和1.6元，現(xiàn)以60kg至90kg生長肥育豬階段的各種營養(yǎng)需要為約束