《Excel 软件在生物统计实验教学中的综合开发应用.pdf》由会员分享，可在线阅读，更多相关《Excel 软件在生物统计实验教学中的综合开发应用.pdf（5页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、收稿日期:2002-08-27作者简介:范 平(1954),女,高级实验师 1Excel软件在生物统计实验教学中的综合开发应用范 平,崔党群,詹克慧,台国琴,孙建英(河南农业大学农学院,河南郑州 450002)摘 要:本文介绍了将Excel软件运用于生物统计实验教学中的一些技巧和方法及教学效果。并对其在实验教学中的作用和意义进行了讨论。关键词:Excel软件;生物统计学;函数;方差分析;相关和回归中图分类号:TP311.52 文献标识码:B 文章编号:1002-4956(2003)02-0065-05生物统计学是高等学院农学和生物学专业的一门重要专业基础课,由于该学科的分析方法和数学模型大都建
2、立在比较抽象的科学假设上,计算公式较多而且分析过程复杂,计算起来较繁锁,既难学又难教。随着计算机走进大学课堂,使很多数据多、过程复杂的运算得到了较大的缓解。目前适用于生物统计本科实验教学的软件并不多,我们选用功能较强大的Excel软件用于实验教学。在实践中发现该软件是由各个独立程序组合而成,而统计分析中计算公式多且相互关联,如果单独使用某一个程序就不能体现计算机的便利,甚至有些统计分析不能得到较为完整的结果。通过我校近几年实验教学,实践证明将Excel软件中的几种程序结合起来应用,不仅充分利用计算机现代技术的方便灵活,计算效率高充实了生物统计实验教学内容,使学生能较快、较好地掌握统计分析方法和
3、运算技巧,更激发了学生上实验课和学习生物统计学的兴趣。本文介绍了将Excel软件运用于生物统计实验教学中的一些技巧和方法及教学效果。1 函数程序和工作簿功能相结合的应用Excel软件提供了300多个工作表函数,与生物统计学有关的有4050个(属数学与三角函数和统计函数范畴)。如果只是利用函数程序进行运算,还不能体现计算机运算功能的强大,我们在实践中发现在运用其程序的同时与工作薄功能及功能键结合一起使用,就更体现Excel软件便利之处,使一贯称数据多、公式多、计算复杂,繁锁的统计分析现在变得极为简单,运用起来得心应手。不仅提高了教学效果,学生们也掌握了利用工作表函数解决复杂问题的能力,更大大提高
4、了他们对上实验课和学习生物统计学的兴趣。例如:对一有n个品种各有m个样本的百分数资料,求其平均数和标准差。具体操作:(1)运用SQRT函数对资料的全部数据同时开方运算:首先选种与数据相等的地址为输出结果的地56中国科技论文统计源期刊 实 验 技 术 与 管 理 Vol.20No.22003址;进入此函数,在对话框number后输入数据地址,不选对话框中的“确定”,运用功能键“shift+ctrl+enter”,结果输出。(2)运用Asin函数将全部平方根值同时取反正弦值(即弧度值):操作同(1)。(3)把弧度值转换为角度值;可借助运用编辑公式运算:在弧度值数据旁第一行、第一列单元格内输入“=n
5、1m1(即第一个品种,第一个样本数据所在的单元格)3180PI”回车;再可配合运用填充柄向下拉m-1格,将n1品种其它样本数据转换;其剩余所有数据转换可再配合运用填充命令(编辑菜单中),先选中剩余要转换数据的地址,向右填充;这些数据可全部转换。(4)运用AVERAGE和STDEA函数计算各品种的平均数和标准差。运算时都可先计算出n1m品种的x或S,再配合填充柄,其它(n-1)m个品种的结果一起算出。实践证明这样的实验课,大大提高了学生在实验过程中的兴趣,由过去不愿上实验课,到现在下课不愿离去;在实验课中学生不仅学到了统计分析的知识,也能在毕业实习中运用计算机来分析毕业论文中的数据。2 方差分析
6、程序与函数程序和工作薄功能结合的运用方差分析是生物统计学的重要内容,在实验教学中也占有较大的比重。Excel软件提供了3种有关方差分析的程序:单因素方差分析、无重复双因素方差分析、有重复双因素方差分析。但遇到有重复多因素随机区组试验资料的分析,其结果就不那么完整。在实践中我们将函数程序和工作薄功能与其程序结合在一起使用就能够解决这些问题。例1有一早稻二因素试验,A因素为品种,分A1(早熟),A2(中熟),A3(迟熟)3个水平;B因素为密度,分B1、B2、B33个水平,共ab=9个处理,重复3次,小区计产。其小区产量(kg)见表1,试作分析。表1 早稻两因素品比试验产量结果(kg)A1A2A3B
7、144153152470441531524684432263B2315315422263154153152321888888933420B334524215315415213341521这个试验资料属两因素有重复的方差分析。其步骤:(1)进入数据分析,选择有重复双因素方差分析程序,在弹出的对话框中输入资料数据所在的地址,每一样本的行数(3行),输入输出结果的地址,确定。从得出结果得知内部(差异源项)包括区组与机误,即内部需再分解。(2)分解内部,在差异源项的样本上方可借助“插入”菜单,插入一行:准备写计算出的区组的各变异量。在内部下插入一行:准备写计算出的机误的各变异量。(3)利用表1进行运算
8、。配合运用“”工具求出第一行的和(为求各区组的和作基础),再运用66实 验 技 术 与 管 理填充柄求出其它8行的和;表2 方差分析差异源SSdfMSFF crit区组01722222220136111112197142843163样本0138888888920119444441163163品种115555555562017777778613999999273163交互51555555556411388888911142857133101内部2166666666718011481481机误1194444446716011215278总计1011666666726配合运用编辑公式“=E5+E8+
9、E11”回车,求出各区组的和,再运用填充柄求出其它各区组和;配合运用DEVSQ函数求出区组的离均差平方和,再除以9(因这3个和是由9个数据相加得到)得出区组SS(其运算结果见表1右侧),继而求出区组的其它各变异量写入样本上方的插入行内(见表2)。(4)运用扣除法计算出的机误的各变异量,写入内部下方的插入行内(见表2)。从表2中可看出品种F值614 F0105=3163和交互(品 密)F值11143 F0105=3101差异十分显著。通过这样的实验使过去一直让枯燥公式使学生反感、又难教难学的实验课,变得轻松而容易接受,进一步提高了效果和效率。学生基本掌握了利用Excel软件对方差分析的基本操作,
10、同时对理论教学方差分析基本原理(总变异为各个因素的相应变异)有了进一步的认识。3 相关和回归程序与函数程序相结合的运用相关和回归分析是研究两个以上变数的统计分析(包括直线、多元、曲线),无论在实验教学或理论教学中都占极其重要的地位。Excel软件提供的相关和回归程序分析直线完全能胜任,但遇到多元和曲线中逐步回归、解矩阵、求偏相关系数及数据转换等问题时,单独使用其程序就变的繁琐,在实践中我们发现将一些函数程序与其结合使用,这些问题就迎刃而解,不但减少一系列反反复复的运算过程,又可快速计算出结果,再结合作图,就更直观,更贴近实际。例2测定了“丰产3号”小麦的单株穗数X1,每穗结实小穗数X2(主茎)
11、、百粒重X3(克),株高X4(厘米)和单株籽粒产量Y(克),测得数据列于表3,试选Y依Xj的最优线性回归方程。表3“丰产3号”小麦单株穗数x1、每穗结实数x2、百粒重x3、株高x4和单株籽粒产yX1X2X3(g)X4(cm)Y(g)102331611315179203161061415102231711117151321317109221576Excel软件在生物统计实验教学中的综合开发应用续表X1X2X3(g)X4(cm)Y(g)1022316110151510233151031619823313100816102431411417102031410413171021314110131410
12、233191042013821315109101262331211471482131711311169223161051213具体方法步骤:(1)进入多元回归分析程序,在弹出的对话框中输入自变量和依变量的地址,选默认置信区间,输入输出的地址,确定。从方差分析表中得知,整个资料的自变量对依变量有真实的回归关系;但X2、X4对Y的概率都 5%,需进行逐步回归。(2)进行逐步回归:可配合“删除键”将原始数据中概率值为最大的自变量X4那一列先删除,再进行回归分析,结果见表4。表4的无效假设正确概率P=2144508E-06 1%,得知各自变量对Y有极显著的回归关系。表4中b1、b2、b3的概率均 5%
13、得知:三元线性回归方程的3个自变量偏回归系数都达显著,可建最优方程:y=4619664+2101314X1+0167464X2+718302X3。(3)计算偏相关系数:先运用相关程序计算出各变数之间的直线相关系数;再将其系数按矩阵排列(见表5)。(4)计算Cij值:配合运用MINVERSE(矩阵的逆矩阵)函数:先选定与表5矩阵大小相等单元格,做Cij值结果的地址,再进入其函数,确定时选用“Shift+Ctrl+Enter”3键,结果输出(见表6)。表4 方差分析dfSSMSFSignificance F回归分析32201811473160381421438732144508E-06残差1119
14、1077911734355总计1423918893Coefficients标准误差t StatP-valueLower95%Upper95%Intercept-461966410119262-416078801000755-69140016706-2415326X Variable 12101313901263144716503419197E-061143396333521592315X Variable 20167464401291832131176801041170103232955511316958X Variable 3718302272126313314599101005334218
15、491089651218113586实 验 技 术 与 管 理表5 例2各变量间简单相关系数X1X2X3YX11-0113574015007301897314X2-01135741-011488901046192X30150073-011488910168898Y018973140104619201688981表6C ij值表814791551197196521574994-9147367119719651152733201966509-2150593215749940196650921812113-4129271-9147367-2150593-41292711215742(5)利用rij=
16、cijciicjj公式求出各自变量的偏相关系数。这种实验在本科实验教学中是非常重要的,但在过去一直是望而却步,现在通过运用结合起来的程序,这种实验教学得到了实现,而且学生较容易掌握。4 效果及意义经近几年在实验教学中的不断应用和尝试,老师和学生反映效果很好。具体表现在以下几个方面:(1)以新的实验手段改革了过去旧的冗繁的统计计算,不仅提高了学生的学习兴趣更提高了实验教学质量和学生的综合素质的培养;(2)改革了实验教学内容,运用新的程序,减少一些不必要的繁琐的数学运算,可突出实验教学重点,拓展教学内容。如过去计算较复杂且非常有用的逐步回归如今作为本科生教学内容,学生可以很容易掌握。(3)提高了实
17、验结果的精确度,容易校正输错得数据,可以杜绝计算的错误,有一些资料可在局域网内设定共享,不仅提高实验教学速度也杜绝了学生相互抄袭的坏习惯(运算过程和结果要存盘),提高了实验教学效果,更培养了严谨的科学态度和作风。(4)能使理论教学同实践紧密结合,在掌握统计分析理论同时,熟练掌握统计分析的方法步骤,并能对统计分析的结果有更进一步地认识,达到实验教学真正目的。并提高了学生运用现代科学技术和手段,提高对实际问题的动脑分析能力。目前实验教学的改革与提高已成为高校教育教学改革的主弦律,在新世纪培养综合素质高的人才已成为各高校的共同目标。我们要在今后的实验教学过程中不断完善补充和提高利用计算机进行统计分析
18、实验教学水平,为进一步推进实验教学的改革做出一些有益的尝试。参考文献1程 锦 1Excel2000中文版函数图书馆M1 北京:清华大学出版社,200012南京农业大学 1 田间试验和统计方法M1 北京:农业出版社,199013范 濂 1 农业试验统计方法M1 河南:河南科技出版社,198414许敖敖 1 处理好理论教学与实验教学关系,提高学生素质,培养学生创新能力J 1 实验技术与管理,2001,18(6):1-315高 红,等 1 开设自主性实验,培养学生的创新意识和创新能力J 1 实验技术与管理,2001,18(6):60-6216边振林,等 1 计算机网络技术在物理实验教学中的应用J 1 实验技术与管理,2002,19(2):47-49196Excel软件在生物统计实验教学中的综合开发应用