当前位置:首页 >> 农林牧渔 >>

应用MicrosoftExcel进行农业试验的统计分析


  东农业科学 山

Shandong Agricultural Sciences

2001 年   4 期 第

应用 Microsoft Excel 进行农业试验的统计分析
齐军山 ,李   ,李长松 ,徐作王 林 廷
( 山东省农业科学院植物保护研究所 ,山东 济南   250100)

     :Microsoft Excel 是 Microsoft Office 套件中的主要成员 ,作为功能强大的专业电子表格处理软件 ,除 摘 要 了可以进行报表编辑等常规功能之外 ,Excel 提供了十分有效的统计函数和必要的统计分析工具 ,可以进行假 设分析 、 方差分析 、 回归分析 、 假设检验 、 抽样分析 、 相关系数等运算 。本文以 Excel 97/ 2000 ( 中文版 ) 为例介绍 其在分析农业试验数据时的应用 。 关键词 : Excel ;统计分析 ; 农业试验 中图分类号 :TP31714    文献标识号 :A    文章编号 :1001 - 4942 (2001) 04 - 0041 - 03
编号
0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 70 109 154 123 94 107 90 62 115 76 72 64 100 105 94 79 78 152 136 56 94 58 77 107 62 131 44 97 100 73 24 79 34 55 156 72 50 81 80 43 68 40 68 45 61 66 58 54 69 22

   生物统计是一项十分繁琐的工作 , 要完成一个稍微

复杂一点的试验的统计分析工作 , 即使是借助计算器也 十分费时费力 。应用计算机软件进行统计分析 , 具有快 的设置 ,就可立即得到所需要的结果 , 省却了大量的中间 有 SAS Institute Inc1 开发的 The SAS System for Windows 和 绍其在农业试验中的应用 。 如果安装 Excel 时进行的是典型安装 ,那么分析工具 需要加载分析工具 。方法为 : 单击〔 工具〕 菜单中的〔 加载 宏〕 命令 ,在〔 加载宏〕 对话框的〔 当前加载宏〕 列表中 , 选 中) 。如果没有找到该文件 ,应运行 “安装” 〔 〕。 程序 1 速、 、 准确 简便的优点 。只需输入原始数据 , 并进行简单

特征 。Microsoft Excel 提供了〔 直方图〕 分析工具来完成这 项任务 。 例 1 ,以下为 100 行金大 332 大豆的产量 , 利用 Excel 作其次数分布表和次数分布图 。 ( 单位 :g) 表 1    行金大 332 大豆的产量 100
5 6 90 84 48 109 77 44 76 75 84 60 7 185 70 87 58 123 141 78 118 25 68 8 95 99 85 101 135 98 92 130 179 160 9 93 132 95 134 40 100 101 90 97 139 57 118 26 73 84 30 60 98 98 82

计算 。目前世界上流行的统计分析软件中 , 比较著名的

尚无中文版推出 , 普通用户应用起来有一定的难度 。国 内唐启义教授开发的 DPS 数据处理系统 , 具有很强的统 计功能 , 目前已有 Windows 版本推出 , 非常适合农业科技

人员使用 。但 DPS 普及率不高 , 市场上难以见到 。本文 以在普通用户中较为普及的 Excel 软件为例 ,结合实例介

1  分析工具库的安装和调用

很可能没有被加载 。单击菜单栏中的〔 工具命令〕 检查 ,

展开的下拉菜单中是否有〔 数据分析〕 命令 。如果没有则

中 分析工具库〕 〔 复选框 , 按〔 确定〕 按钮 。如果〔 加载宏〕 对话框中没有〔 分析工具库〕请单击〔 , 浏览〕 按钮 , 定位到 分析工具库加载宏文件 Analys321xll ” “ 所在的驱动器和文 件夹 ( 通 常 应 在 Microsoft Office/ Library/ Analysis 文 件 夹

2   直方图〕 用〔 工具作次数分布表和次数

分布图

布特征 ,常编制成次 ( 频 ) 数分布表 。并常常根据次数分
收稿日期 :2001 - 02 - 14

布表作成次数分布图 , 以更加形象直观地表示次数分布

SPSS Inc1 开发的 SPSS for Windows 等 。但这两种软件目前

  : 资料来源于文献〔 〕 注 2 。

   初步整理农业试验数据资料时 , 为了反映数据的分

首先在 A1 ~ A100 单元格区域内输入上述 100 个数 据 。单击〔 工具〕 菜单中的〔 数据分析〕 , 在显示的〔 项 分析 工具〕 对话框中 , 选定〔 直方图〕 选项 , 按〔 确定〕 按钮 。在 〔 直方图〕 对话框中如下设置 : 〔 输入区域〕将鼠标指针定位在〔 : 输入区域〕 的编辑 框中 ,再在工作表上选定 A1~A100 的单元格区域 ,〔 输入 区域〕 将显示 A 1∶ A 100 。 〔 接收区域〕为可选项 , 在此输入接收区域的单元格 : 引用 。该区域应包含一组用来定义接收区间的边界值 , 即组限 。这些值应按升序排列 。如果该项缺省 , 则 Excel 将创建一组平滑分布的接收区间 。 〔 标志〕 如果输入区域包含标志项 , 则选中该复选 : 框 。否则 ,清除之 。 〔 输出区域〕 新工作表组〕〔 、 〔 、新工作薄〕 根据想要 : 将运算结果输出的位置选其一 。若选中〔 输出区域〕 选 项 ,则在其后的编辑框中键入对输出表左上角单元格的 引用 。 〔 柏拉图〕如果选中该复选框 , 则在输出表中除按接 :

41

收升序排列数据外 ,同时按频率降序排列数据 , 另外在嵌 入的直方图表中横坐标按频率降序排列 。若清除该复选 框 ,则只按接收升序排列数据 , 即输出表中最右边的三列 数据缺少 ,同时在嵌入的直方图中横坐标将按接收升序 排列 。 〔 累计百分率〕如果选中此复选框 , 则在输出表中包 : 含一列累计百分比数值 , 同时在直方图表中添加累计百 分比折线 。若清除该复选框 , 则会在输出表和直方图表 中省略累计百分比 。 〔 图表输出〕选中此复选框 , 可以在输出表中同时生 : 成一个嵌入式直方图表 。 设置完毕后 , 单击〔 确定〕 按钮 , 返回编辑窗口 , 将看 到次数分布表 ( 表 2) 和次数分布图 ( 略 ) 。在表 2 中 , 第 1 列 “接收” 即组限 , 第 2 列 “频率” 为对应组限的次数 , 第 3 列 “累积 %” 即累积概率 。 表2  金大 332 产量次数分布表
接收
22 3813 5416 7019 8712

框中输入 1 ,则输出表中包含最大值和最小值 。如果都输 入 2 ,则输出表中包含次大值和次小值 ,依次数推 。 设置完毕 , 单击〔 确定〕 按钮 , 将会看到输出结果 ( 表
3) 。表 3 中 ,标准误差指样本平均数标准误差 ; 模式即众

数 ; 标准偏差即样本标准差 ; 区域即极差或全距 ( 变幅 ) ; 峰值和偏斜度描述的是数据分布的正态性 。 表3  金大 332 产量的统计特征数
列1 平均 标准误差 中值 模式 标准偏差 样本方差 峰值 偏斜度 区域 最小值 最大值 求和 计数
86125 31419149 83 94 34119149 11691058 01259236 01534567 163 22 185 8625 100

频率
1 5 9 19 21 21 8 8 3 3 2

3   “描述统计” 用 工具计算统计特征数
在统计工作中 ,除了次数分布表外 , 我们还常用样本 特征数来描述数据趋中性和易变性等信息 。在 Excel 中 , 可以用 “描述统计” 工具来完成这项工作 。 例 2 ,求表 1 中 100 行大豆产量数据的统计特征数 。 在 分析工具〕 〔 对话框中 , 选定〔 描述统计〕 选项 。在 打开的〔 描述统计〕 对话框中如下设置 : 〔 输入区域〕输入 A 1∶ A 100 。 : 〔 分组方式〕选中 : “列” 复选框 。 〔 标志位于第 1 行〕如果输入区域没有标志项 ,则该 : 复选框不被选中 。 〔 输出区域〕 输入一无数据的空白区 , 如 B 2 , 此 : 工具将为每个数据集产生两列信息 , 左列为统计标志项 , 右列为统计值 。依〔 分组方式〕 选项的不同 , Excel 将对每 行或每列生成一个两列的统计表 。 〔 汇总统计〕 选中该复选框 , 则 Excel 将在输出表中 : 生成下列结果 : 平均值 、 标准误差 、 中值 、 众数 、 方差 、 峰 值、 偏斜度 、 极差 、 最小值 、 最大值 、 总和 、 总个数 。 〔 平均数置信度〕如果选中该框 , 则在输出表中就包 : 含均值的置信度 。例如 ,在右边的编辑框中输入 95 % ,可 用来计算在显著性水平为 5 %时的均值置信度 。 〔 K 大值〕〔 K 小值〕选中它 , 则在输出表中包 第 、第 : 含第 K 个最大值和第 K 个最小值 。例如 , 在右边的编辑

42

10315 11918 13611 15214 16817

其他

累计 ( %) 1100 6100 15100 34100 55100 76100 84100 92100 95100 98100 100100

4  方差分析

分差分析是将总变异分解为各个因素的相应变异 , 从而推断各个因素在变异中所占的重要程度 。Excel 提供 了 3 个方差分析工具 ,即 “单因素方差分析” 可重复双因 “ 、 素方差分析” 无重复双因素方差分析” “ 、 。其中 “单因素 方差分析” 适用于单向分组资料 , 如单因素完全随机试 验。 “可重复双因素方差分析” 适用于两因素完全随机试 验。 “无重复双因素方差分析” 适用于两向分组资料 , 如 单因素随机区组试验 。 例 3 ,研究 6 种氮肥施肥法对小麦植株含氮量的影 响 ,每种施肥法重复 5 次 , 完全随机设计 , 结果如表 4 , 试 进行方差分析 。

1 2 3 4 5 6 1219 14 1216 1015 1416 14

表 4   6 种施肥法的小麦植株含氮量

1213 1318 1312 1018 1416 1313


1212 1318 1314 1017 1414 1317




1217 1316 13 1015 1414 1317

  : 资料来源于文献〔 〕 注 3 。

这是一个单因素完全随机设计试验的单向分组资 料 ,所以用 “方差分析 : 单因素方差分析” 工具 。在工作表 中输入上述数据 ,单击〔 工具〕 菜单中的〔 数据分析〕 , 在 项 显示的〔 分析工具〕 对话框中 , 选定〔 方差分析 : 单因素方 差分析〕 选项 ,按〔 确定〕 按钮 。在〔 方差分析 : 单因素方差 分析〕 对话框中如下设置 : 〔 输入区域〕输入待分析数据区域的单元格引用 。 : 〔 分组方式〕 根据输入区域中不同处理的数据是按 : 行还是按列排列 ,单击 “行” “列” 或 选项 。 〔 标志位于第 1 列〕 若输入区域的第 1 列包含标志 :

1215 1316 1314 1018 1414 1315

项 ,则选中该复选框 ,否则 ,清除它 。 α 编辑框 : 输入计算 F 统计临界值的置信度 ( 即显著 〔〕 性水平) ,如 0105 或 0101 。 表5  表6 
B1 50 59 45 52 58 55 60 56 62 45 A1 A2

在 输出选项〕 〔 中确定统计结果的输出位置 。按〔 确 方差分析

定〕 按钮 ,得到 SUMMARY 表 ( 略) 和方差分析表 ( 表 5) 。
差异源 SS df MS F P - value F crit 组间 441463 5 818926 16411711 9162 × - 18 31895082 10 组内 113 24 01054167 总计 451763 29

范围时 ,通过对试验数据进行整理 , 并数次执行这些方差 分析工具 ,即可解决问题 。此处不再累述 , 有兴趣的读者 4 可参阅相关著作〔 〕。 需要注意的是 , 当我们经方差分析发现处理效应显 著时 ,一般还需要对各处理平均数作两两比较 。遗憾的 是 ,Excel 没有提供该项功能 。但我们可以根据 Excel 所提 供的 SUMMARY 表和方差分析表进行人工比较 。

5  回归和相关分析
除了对单个变量进行分析外 , 农业试验研究还需要 揭示几个变量之间的相互关系 , 这就是回归与相关分析 。 对此 ,Excel 也提供了相应的分析工具 。 例 5 ,测得 22 个土样的全氮量 ( y) 与有机质含量 ( x) 列 于表 7 ,试建立全氮量 ( y) 与有机质含量 ( x) 变化的回归方 程 ,并进行检验 。 表 7   个土样的全氮量 ( y) 与有机质含量 ( x) 22
土样 号
1 2 3 4 5 6 7 8 9 10 11

   5 中 ,第 1 列为变异来源 ,组间即不同施肥方法 ,组 表

内即误差 。第 2 列 SS 为平方和 。第 3 列 df 为自由度 。第
4 列 MS 为均方 。P - value 表示自由度为 ( 5 ,24) 的 F 分布 中 ,F > 16411711 的 上 侧 概 率 为 9162 ×10 - 18 , 即 P ( F > 16411711) = 9162 ×10 - 18 。F crit 为临界 F 值 , 即 F0101 ( 5 , 24) = 31895082 。在表 5 中 ,F > F0101 ( 5 ,24) , 说明不同施肥

法对小麦植株含氮量的影响极显著 。

如果将例 3 中的试验改为随机区组设计 ,则应该采用

有机质
( %) x 11336 01761 11203 11151 11128 11112 11185 11060 11039 11142 11558

全氮量
( %) y 010965 010657 010901 010865 010817 010838 010801 010767 010731 010767 011018

土样 号
12 13 14 15 16 17 18 19 20 21 22

有机质
( %) x 11254 11120 11158 11105 11042 11173 11107 11103 11227 11270 11245

全氮量
( %) y 010899 010815 010829 010861 010728 010830 010773 010773 010838 010885 010885

〔 方差分析 : 无重复双因素方差分析〕 工具 。进行类似的 设置后 ,得到方差分析表 ( 略 ) 。注意在得到的方差分析

表中 ,差异源多了一项 。其中 “行” 即处理间 “列” , 即区组 著 。而区组间 F < F crit ,说明重复间差异不显著 。 例 4 ,某地进行玉米氮肥用量 ( 因素 B ) 及施用时期

间 。而处理间 F > F crit , 说明不同施肥方法间差异极显

(因素 A) 试验 , 其中氮肥用量取 3 个水平 , 施用时期取 2

个水平 。各处理组合重复 5 次 ,完全随机排列 。小区产量 结果如表 6 ,试作方差分析 。 玉米产量结果
B2 60 53 58 62 55 71 65 75 78 80

B3 64 66 67 71 63 55 59 58 63 60

  : 资料来源于文献〔 〕 注 4 。

  : 资料来源于文献〔 〕 注 3 。

这是一个完全随机设计的两因素试验 , 采用〔 方差分

析 : 可重复双因素方差分析〕 工具进行方差分析 。 在 可重复双因素方差分析〕 , 如果数据表的结构 〔 中 认可的形式 。

与表 6 不同 , 需首先将工作表整理为如表 6 所示的 Excel 在打开的〔 方差分析 : 可重复双因素方差分析〕 对话

框中 ,〔 输入区域〕 要包括标志项所在的行和列 。在〔 每一 ( R) 〕 ( 即每个处理组合的重复 样本的行数 编辑框中输入 5 次数) 。在所得的方差分析表 ( 略) 中 ,样本即因素 A ,列即 因素 B ,交互即 A × ,内部即误差 。 B 例 3 、 4 的试验设计正好满足 Excel 提供的方差分 例

析工具 。当试验设计不满足这 3 个方差分析工具的使用

在〔 分析工具〕 对话框中选〔 回归〕 分析工具 , 在打开 的〔 回归〕 对话框中如下设置 : 〔 值输入区域〕输入对依变量数据区域的引用 。该 Y : 区域必须由单列数据组成 。 〔 值输入区域〕输入对自变量数据区域的引用 。自 X : 变量最多为 16 个 。自左至右按升序排列 ,其顺序与输出 结果中的回归系数相对应 。 〔 标志〕若输入区域中包含标志项 ,应选中 。 : 〔 置信度〕 若需在汇总结果中包含附加的置信度信 : 息 ,则选中 。 〔 常数为零〕 选中该复选框 , 则强制回归线通过原 : 点。 〔 输出区域〕输出结果包括 3 张表 : 回归统计表 ( 略) 、 : 方差分析表 ( 表 8) 和回归系数表 ( 表 9) 。 〔 残差〕残差是回归分析时 ,计算出的每一点的 Y 的 : 估计值和实际值的平方差 。选中它可以以残差输出表的 形式查看残差 。 〔 标准残差〕 选中它则在残差输出表中包括标准残 : 差。 〔 残差图〕选中它则生成一张图表 , 绘制每个自变量 : 及残差 。 〔 线性拟合图〕选中它则为预测值和观察值生成一 :

43

  东农业科学 山

Shandong Agricultural Sciences

2001 年   4 期 第

应用 AFS2201 型双道原子荧光光谱仪联合 测定蔬菜中砷和汞
张丙春 ,孟立红 ,王   磊
( 农业部食品质量监督检验测试中心 ,山东 济南   250100)

     : 研究了应用 AFS2201 型双道原子荧光光谱仪联合测定蔬菜中砷和汞的技术 ,用硝酸 - 高氯酸 ( 4 摘 要
+ 1) 混合酸消化样品 ,待样品处理好后加入 5 %硫脲 - 5 %抗坏血酸混合改进剂 ,并以 2 %硼氢化钾 ( KHB4 ) 为

   砷和汞在历年的蔬菜卫生监督抽查检验中 , 都被列为重点监督的有害元素 。过去一般应用比 色法 、 分光光度法 、 原子吸收法等 , 由于测定原理 不同 ,其灵敏度也各不相同 。近年来虽广泛采用

原子荧光法 , 但砷 、 汞也通常分别进行测定 , 样品 前处理麻烦 、 、 。为了适应蔬菜抽查检测 费时 费力 任务大 、 品种多 、 砷汞含量相对较低的特点 , 本文 研究采用灵敏度高的双道原子荧光法联合测定蔬
收稿日期 :2001 - 05 - 11

个图表 。 〔 正态概率图〕选中它则自动绘制出正态概率图 。 : 在本例中 , 从〔 置信度〕 正态概率图〕 到〔 均不选中 。 单击〔 确定〕 按钮 ,则得到表 8 和表 9 。 表8  方差分析
df 1 SS MS F Significance F

回归分析 残差 总计

2182383 × - 9 ,这是一个小概率事件 , 说明 F > F0101 , 回归 10

分析极显著 ,接受该回归方程 。 表9  回归系数表
Coefficients 0102353 0105128 Intercept

   9 中 ,Coefficients 列为回归系数 ,其中 Intercept 为截 表 距 ,第二条为斜率 。标准误差指对应的回归系数的标准 差 。t Stat 即对应的回归系数的 t 值 。P - value 对应 t 值的 上侧概率 。

   该表为回归方程显著性检验 ,Significance F = 2182383E

- 09 ,表示自由度为 (1 ,20) 的 F 中 ,F > 10113597 的概率为
有机质 X

44

还原剂 ,在 10 %的硫酸介质中测定砷和汞 ,其砷和汞的最低检出量分别为 012ng/ ml 和 0102ng/ ml 。 关键词 : 双道原子荧光光谱仪 ; 蔬菜 ; 砷和汞 ; 联合测定 中图分类号 :O657131 ;S63    文献标识号 :A    文章编号 :1001 - 4942 (2001) 04 - 0044 - 03
20 21 01001149 01000227 01001375

菜中砷和汞 。方法原理 : 样品经酸加热消解后 ,在 酸性介质中 ,样品中砷 、 汞被硼氢化钾还原为原子 态砷 、 ,由载气 ( 氩气 ) 带入原子化器中 , 在特制 汞 砷、 汞空心阴极灯照射下 , 基态砷 、 汞原子被激发 至高能态 ,在去活化回到基态时 ,发射出特征波长 的荧光 ,其荧光强度在固定条件下与砷 、 汞含量成 正比 ,与标准系列比较定量 。 本方法一次性消化样品 ,同时测定蔬菜中砷

标准误差 01005943 01005094

1113E - 05

01001149 10113597 2182383E - 09 t Stat P - value 31959909 10106776 01000773

2182 × - 9 10

〔 〕 经辰工作室 1 Excel2000 中文版预测分析与统计分析指南 1 〔 〕 电子工业出版社 ,20001 M 1 〔 〕 马育华 1 试验设计〔 〕 农业出版社 ,19851 2   M 1 〔 〕 李永孝 1 农业应用生物统计〔 〕 山东科学技术出版社 , 3   M 1 19891 〔 〕 方   1 实用农业试验设计与统计分析指南〔 〕 中国农 4   平 M 1 业出版社 ,20001

由表 9 中 Coefficients 栏可得回归方程 : y = 010235 + 010513x ,由表 8 得出 ,该回归方程可以接受 。 例 6 ,计算例 5 中全氮量 ( y) 与有机质含量 ( x) 两个变 量之间的相关系数 。 在〔 分析工具〕 对话框中选〔 相关系数〕 分析工具 , 在 打开的对话框中 ,〔 分组方式〕 按变量是按行还是按列排 列而选择 。从得到的相关系数表 ( 略 ) 中可知 , 全氮量 ( y) 与有机质含量 ( x) 两个变量之间的相关系数为 0191389 。 除了以上介绍的功能外 , Excel 还提供了 300 多个函 数及其它分析工具 , 如 t 检验 、 检验 、 检验 、 z F 抽样 、 随机 数发生器等 。充分利用这些功能并在实际工作中加以拓 展 ,就可以完成常见的统计任务 。 参      : 考 文 献


相关文章:
Excel在统计中的应用与数据统计分析
(一)中文Excel 简介 Microsoft Excel 是美国微软公司...统计中的应用,要求学生熟练掌握运用Excel进行统计分析...这种工具适用于完全随机化试验的结果分析。 例1-10...
统计与数据分析编写提纲
统计与数据分析编写提纲_调查/报告_表格/模板_应用文书...Microsoft Excel 电子表格具有强大的统计分析功能,利用...科学、农业 试验和生产实际中数据的常见的统计分析...
Microsoft EXcel 2007中的数据分析和图表设计(图)
Microsoft EXcel 2007中的数据分析和图表设计(图)_计算机软件及应用_IT/计算机_专业资料。个人总结 :Microsoft EXcel 2007中的数据分析和图表设计(图)原始...
新疆农业大学校医院门诊抗高血压药物的应用分析
13 新疆农业大学校医院门诊抗高血压药物的 应用分析 新疆农业大学校医院门诊抗高...1.3.6 统计方法 应用Microsoft Excel对数据进行分析、汇总、排序并统计。 2 ...
Excel在方差分析中的应用
主要通过三个实例来介绍 Excel 在方差分析中的应用...最早用于生物农业 田间试验,后来在很多领域都获得了...Microsoft Excel 软件分别进行单因素方差分析、无重复...
吉林农业大学 DPS生物统计实验指导
吉林农业大学 《生物试验设计》 实验教学指导喇叭吆喝...(4 学时) 实验一 Excel、DPS 在生物统计应用 ...使用工作表可以对数据进行组织和分析,可以同时在多张...
统计学excel操作
统计学excel操作_计算机软件及应用_IT/计算机_专业资料...【试验内容】 Excel 中的统计分析功能,包括算术平均...Microsoft Excel 将在输出表中生成适宜的数据标志。 ...
《统计应用软件》教学大纲-米子
2.主要教学内容 第一节 统计学在应用数据分析与...工农业或其它领域,找出一个利用统计 软件来解决实际...第二章 Microsoft Excel20003 统计分析 1.教学目的和...
Excel 多元线性回归在数据分析中的应用
我主要讨论 Excel 在多元线性回归在数据分析中的应用,以希望对广大的使 用者有...农业试验统计[M] .上海:上海科学技术出版社,1984. 多元回归分析, 种方法,...
Excel在《生物统计学》实验教学中的应用探讨
河南农业大学园艺学院;2.河南农业大学林艺学院河南...(b)-0183-01 ExcelMicrosoft 开发的电子表格...中的应用特点,详细分析Excel 用于《生物统计学》...
更多相关标签: