畜牧场育种数据统计分析

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,畜牧场育种数据统计分析(2)EXCEL应用高级,实习目的,利用EXCEL进行,单因素、多因素、重复数据方差分析,一元、多元回归分析,线性、非线性拟合,1 方差分析,单项分类资料,双向交叉分组无重复资料,双向交叉分组有重复资料,1.1 单项分类资料,有5个不同品种的若干头母猪的窝产仔数资料见表8，试检验不同品种母猪平均窝产仔数的差异显著性。,品种号,产仔数,1,8,13,12,9,9,9,2,7,8,10,7,3,13,14,10,11,12,12,4,13,9,8,8,10,5,12,11,15,14,Anova:Single Factor,SUMMARY,Groups,Count,Sum,Average,Variance,1,6,60,10,4,2,4,32,8,2,3,6,72,12,2,4,5,48,9.6,4.3,5,4,52,13,3.333333,ANOVA,Source of Variation,SS,df,MS,F,P-value,F crit,Between Groups,68.96,4,17.24,5.455696,0.003889,2.866081,Within Groups,63.2,20,3.16,Total,132.16,24,1.2 双向交叉分组无重复资料,有4个品种猪（A因素），每品种3头，分别饲用一种配合饲料（B因素），3个月的增重结果见图，试进行方差分析以研究品种与饲料对猪的增重影响。,因素,B1,B2,B3,A1,51,53,52,A2,56,57,58,A3,45,49,47,A4,42,44,43,Anova:Two-Factor Without Replication,Anova:Two-Factor Without Replication,SUMMARY,Count,Sum,Average,Variance,A1,3,156,52.00,1.00,A2,3,171,57.00,1.00,A3,3,141,47.00,4.00,A4,3,129,43.00,1.00,B1,4,194,48.50,39.00,B2,4,203,50.75,30.92,B3,4,200,50.00,42.00,ANOVA,Source of Variation,SS,df,MS,F,P-value,F crit,Rows,332.25,3,110.75,189.86,0.00,4.76,Columns,10.5,2,5.25,9.00,0.02,5.14,Error,3.5,6,0.58,Total,346.25,11,1.3双向交叉分组有重复资料,为了考察饲料中钙和磷的含量对仔猪的生长发育影响，将钙（A）和磷（B）在饲料中的含量各分为4个水平进行试验，每个水平组合3头猪，经2个月的饲养试验，得仔猪增重结果见表。试分析钙和磷及它们之间的交互作用对仔猪生长发育的影响。,表 不同钙磷用量的试验猪增重结果（单位：kg）,A,B,C,D,E,B1,B2,B3,B4,A1,22,30,32.4,30.5,26.5,27.5,26.5,27,24.4,26,27,25.1,A2,23.5,33.2,38,26.5,25.8,28.5,35.5,24,27,30.1,33,25,A3,30.5,36.5,28,20.5,26.8,34,30.5,22.5,25.5,33.5,24.6,19.5,A4,34.5,29,27.5,18.5,31.4,27.5,26.3,20,29.3,28,28.5,19,ANOVA,Source of Variation,SS,df,MS,F,P-value,F crit,Sample,44.51,3,14.836,3.220,0.035576266,2.901117568,Columns,383.73,3,127.911,27.766,4.9187E-09,2.901117568,Interaction,406.65,9,45.1842,9.808,5.11452E-07,2.188762949,Within,147.41,32,4.6066,Total,982.31,47,2 数据回归分析,一元线性回归,多元线性回归,2.1 一元线性回归,现有10只绵羊的胸围（x）和体重（y）的数据见图13，请进行回归分析。,胸围（x）,体重（y）,68,50,70,60,70,68,71,65,71,69,71,72,73,71,74,73,76,75,76,77,Multiple R,0.847488,R Square,0.718236,Adjusted R Square,0.683016,Standard Error,4.51192,Observations,10,ANOVA,df,SS,MS,F,Significance F,Regression,1,415.1406,415.1406,20.39259,0.001961,Residual,8,162.8594,20.35742,Total,9,578,Coefficients,Standard Error,t Stat,P-value,Intercept,-115.375,40.63234,-2.83949,0.021829,胸围（x）,2.546875,0.56399,4.515816,0.001961,2.2 多元线性回归,有人测定了18头猪的背膘厚(BF,cm)，眼肌面积(LA,cm2)，后腿比例(LEG,%)，肥育期日增重(GAIN,g)和胴体瘦肉率(LEAN,%)5项指标，数据如下表。,试求胴体瘦肉率(LEAN,%)对背膘厚(BF,cm)，眼肌面积(LA,cm2)，后腿比例(LEG,%)，肥育期日增重(GAIN,g)的多元回归方程。,猪号,背膘厚,眼肌面积,后腿比例,日增重,胴体瘦肉率,1,4.68,18.09,25.31,463,41.90,2,4.70,18.03,25.20,548,42.32,3,3.74,24.57,27.49,637,52.36,4,3.56,26.50,27.84,631,53.32,5,2.56,27.75,29.54,483,54.55,6,3.42,30.86,27.12,433,53.34,7,3.32,26.78,29.59,485,52.23,8,2.86,27.82,29.06,415,52.89,9,2.56,27.75,29.37,518,54.55,10,2.80,28.80,29.12,509,55.92,11,2.69,26.11,30.08,456,56.05,12,3.42,25.36,27.32,539,53.34,13,3.45,24.98,27.54,560,52.71,14,3.42,25.02,26.83,528,52.01,15,3.48,23.45,27.28,535,53.31,16,2.95,26.08,30.50,627,57.40,17,2.07,32.23,30.51,642,60.25,18,2.02,33.28,30.82,776,61.41,3、数据拟合分析,线性拟合,非线性拟合,日龄,体重,日龄,体重,1.0,1.2,111.0,72.3,11.0,3.5,121.0,86.1,21.0,4.8,131.0,96.4,31.0,6.5,141.0,107.3,41.0,11.2,151.0,116.8,51.0,15.0,161.0,119.8,61.0,20.4,171.0,125.9,71.0,28.1,181.0,130.7,81.0,37.1,191.0,134.7,91.0,50.3,201.0,136.9,101.0,59.4,211.0,139.7,线性拟合-y=a+bx,日龄,体重,日龄,体重,1.0,1.2,111.0,72.3,11.0,3.5,121.0,86.1,21.0,4.8,131.0,96.4,31.0,6.5,141.0,107.3,41.0,11.2,151.0,116.8,51.0,15.0,161.0,119.8,61.0,20.4,171.0,125.9,71.0,28.1,181.0,130.7,81.0,37.1,191.0,134.7,91.0,50.3,201.0,136.9,101.0,59.4,211.0,139.7,非线性拟合1多项式拟合,y=a+bx+cx,2,y=a+bx+cx,2,+dx,3,y=a+bx+cx,2,+dx,3,+ex,4,曲线拟合2已知曲线模型,奶牛的泌乳曲线,某奶牛不同日龄产奶量见表，试拟合该奶牛的泌乳曲线,泌乳日龄,日产奶量,20,20.4,50,24.9,85,23.6,120,21.8,155,20.9,185,19.9,220,18.6,250,18.1,280,17.5,310,15.6,Coefficients,Intercept,2.522700122,a=e,2.5227,泌乳日龄(t),-0.002859265,-c,ln(t),0.195013464,b,