Excel在生物统计中应用.docx

Excel在生物统计中应用.docx

《Excel在生物统计中应用.docx》由会员分享，可在线阅读，更多相关《Excel在生物统计中应用.docx（21页珍藏版）》请在冰豆网上搜索。

Excel在生物统计中应用

生物统计实验指导书

金卫斌主编

Excel在生物统计中应用

一、目的要求

使学生掌握Excel在生物统计中的基本应用方法。

二、材料用具

练习纸、铅笔、装有Excel软件的计算机及输出设备或计算器。

三、方法步骤

1．启动Excel

2．加载“分析工具库”

在默认的情况下，Excel是没有加载“分析工具库”，无法进行统计分析。

先查看计算机是否加载“分析工具库”。

首先启动Excel，检查“工具”菜单中是否有“数据分析”命令。

如果没有发现“数据分析”命令，就表示未加载“分析工具库”。

可按照下列步骤来加载“分析工具库”。

（1）选取“工具”菜单中的“加载宏”命令，弹出“加载宏”的对话框。

（2）选中“分析工具库”复选框，单击“确定”按钮，按提示完成加载过程。

此时再查看“工具”菜单，就可发现“数据分析”命令，表示安装完成，单击“工具”菜单中“数据分析”命令，得图2，即可进行相关的统计分析。

3．分析工具库提供的统计分析方法

分析工具库提供的常用统计分析方法如下：

1单向分类资料的方差分析*

②双向分类交叉分组无重复资料的方差分析*

③双向分类交叉分组有重复资料的方差分析*

④描述性统计量计算*

⑤二总体方差的同质性检验

⑥编制频数分布表及绘制直方图

⑦排位与百分比排位

⑧二样本均数差数的u检验

⑨配对资料的t检验*

⑩等方差非配对资料的t检验*

异方差非配对资料的t检验

一元线性回归与多元线性回归分析*

计算多个变量两两之间的相关系数及协方差*

进行随机和顺序抽样

4．统计分析的粘贴函数

Excel提供的统计分析粘贴函数如下：

AVERAGE

BINOMDIST

CHIDIST

CHIINV

CHITEST

CONFIDENCE

CORREL

COVAR

FDIST

FINV

FTEST

GEOMEAN

HARMEAN

INTERCEPT

MAX

MEDIAN

MIN

MODE

NORMDIST

NORMINV

NORMSDIST

NORMSINV

POISSON

SLOPE

STDEV

STDEVP

TDIST

TINV

TTEST

VAR

VARP

ZTEST

计算算术平均值*

计算二项式分布的概率值

计算特定χ2分布的单尾概率值

计算一定单尾概率值时的χ2临界值

计算独立性检验的χ2值

计算总体平均值的置信区间

计算两组数据的相关系数*

计算两组数据的协方差

计算特定F分布的单尾概率值*

计算一定概率时的临界F值*

计算二个样本方差之比F值的概率*

计算几何平均数

计算调和平均数

计算直线回归的截距*

计算最大值*

计算一组给定数字的中位数*

计算最小值*

计算一组数据的众数*

计算正态分布的累积函数

计算正态分布累积函数的逆函数

计算标准正态分布的累积函数

计算标准正态分布累积函数的逆函数

计算泊松分布的概率

计算给定数据的直线回归系数*

计算样本标准差*

计算样本总体的标准差

计算学生氏-t分布的概率值*

计算特定概率时学生氏-t分布的临界t值*

计算t检验时的学生氏-t检验相关的概率

计算样本的方差*

计算样本总体的方差

计算Z检验的双尾概率值

上述带*者就是我们要学习和掌握的内容

5．Excel的常用基本概念

（1）工作薄和工作表

MicrosoftExcel工作簿是计算和储存数据的文件，每一个工作簿都可以包含多张工作表，因此，可在单个文件中管理各种类型的相关信息。

使用工作表可以对数据进行组织和分析，可以同时在多张工作表上输入和编辑数据，并且可以对来自不同工作表的数据进行汇总计算。

在创建图表之后，既可以将其置于原始数据所在的工作表上，也可以放置在单独的工作表上。

工作表的名称显示于工作簿窗口底部的工作表标签上，单击这个标签即可进入该工作表。

当前所在的工作表称为活动工作表，在它的标签上标有单下划线。

可以在同一工作簿内或两个工作簿之间对工作表进行改名、添加、删除、移动或复制等编辑操作。

在默认情况下，每个工作薄由3个工作表组成，用户可以根据需要添加更多的工作表。

每个工作表由256列和65536行组成。

行和列相交形成单元格，它是存储数据的基本单位。

列用英文字母表示，开始是单个英文字母，然后是2个英文字母组合，即由A～IV共256列。

行用阿拉伯数字表示，由1～65536。

每个单元格的定位可以通过该单元格所对应的行数和列标来确定，如B6就表示B列第6行的单元格。

在活动工作表众多的单元格中，其中有一单元格含有粗边框线，此单元格为活动单元格，在该单元格中可以输入和编辑数据。

每进入一个工作表时，A1自动为活动单元格。

在活动单元格的右下角有一小黑方块，这个黑方块称为填充柄，利用此填充柄可以填充相邻单元格区域的内容。

（2）单元格和区域引用

引用是指调用工作表中的数据用于计算分析，是通过标识工作表上的单元格或区域来实现的。

通过引用，指明在计算时所使用的数据在工作表中的位置，可以使用工作表中不同区域的数据，或者在多个计算公式中使用同一单元格的数值。

还可以引用同一工作簿不同工作表、不同工作簿、甚至其他应用程序中的数据。

引用不同工作簿中的数据称为外部引用，引用其他程序中的数据称为远程引用。

如果要引用某一单元格的数据，可在计算公式中输入该单元格的标识，例如要引用在列B和行10这一单元格的数据，在计算公式中输入B10，或者用鼠标点击该单元格。

如果要引用某个区域的数据，可在计算公式中输入该区域左上角单元格的标识，后面跟一个冒号（：

），后面再写上该区域右下角单元格的标识。

例如，要引用从单元格C5到单元格C15这一区域，可输入C5:

C15；如要引用从单元格A5到单元格B10这一区域，即可输入A5:

B10。

（3）绝对引用与相对引用的区别

当生成公式时，对单元格或区域的引用通常基于他们与公式单元格的相对位置。

如在单元格A8中包含公式=8×B5，MicrosoftExcel将在距单元格A8上面一个单元格和右面一个单元格处的单元格中查找数值，这就是相对引用。

当复制使用相对引用的公式时，被粘贴公式中的引用将被更新，并指向与当前公式位置相对应的其他单元格。

如单元格A8中的公式被复制到单元格A10中，单元格A10中的公式已经改为=8×B7，即从单元格A8上面和右面一个单元格处的单元格中查找数值。

如果在复制公式时，不希望引用发生改变，可使用绝对引用。

绝对引用的方式是在相对引用列标和行数字前加上美元符号，即＄B＄5。

如在单元格A8中包含的公式=8×＄B＄5，现在将公式复制到另一单元格中，则公式中的引用将不发生改变。

1试验数据整理

1）．图表向导的利用

用Excel中的“图表向导”可以快速准确的绘制图表。

它是一连串的对话框，依照它逐步建立或修改图表。

新建图表需四个步骤，修改图表只需两个步骤。

步骤1：

选择图表类型

选择“常用”工具栏中的“图表向导”按扭，如图3图标或单击菜单栏中“插入”再击“图表”命令，激活图表向导对话框，如图4。

Excel提供了两大类的图形：

标准类型和自定义类型，用户可根据需要选择。

步骤2：

选定数据区域

如果在单击图表向导之前，就已选定数据范围，那么图表向导的第二对话框就会自动设定数据区域。

如果事先没有设定，用户可自己设定。

步骤3：

设定图表选项

完成步骤2之后，单击“下一步”按钮进入步骤3。

在步骤3里有很多的选项卡：

“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”、“数据表”。

步骤4：

指定图表位置

当完成步骤3后，单击“下一步”按钮即可进入步骤4。

可以选定“作为新工作表插入”或“作为其中的对象插入”。

然后单击“完成”按钮结束图表向导，完成了图表的制作。

2）．“数据分析”工具栏运用

现以水稻品种120行产量（kg）为例，说明其方法与步骤。

表1水稻品种120行产量（kg）表

177

215

197

97

159

245

119

119

131

152

167

104

161

214

125

175

188

192

176

175

95

199

116

165

214

95

158

83

80

138

151

187

126

134

206

137

98

97

129

143

174

159

165

136

108

141

148

168

163

176

102

194

173

75

130

149

150

155

111

158

131

189

91

142

152

154

163

123

205

155

131

209

183

97

119

181

187

131

215

111

186

150

155

197

116

254

239

160

179

151

198

124

179

184

168

169

173

181

188

211

175

122

151

171

166

143

190

213

192

231

163

159

177

147

194

227

169

124

159

159

（1）打开Excel，输入原始数据和各组的下限，样式见图5，图中从A1单元格到L10单元格的区域为原始数据，各组的下限值位于M1到M11单元格。

（2）从“工具”菜单中选定“数据分析”命令，再选定“直方图”，确定后出现直方图对话框。

（3）在输入区域中输入＄A＄1:

＄L＄10，在接收区域中输入＄M＄1:

＄M＄11，在输出选项中选定输出区域如＄A＄12后，单击确定，得到频数分布表。

若在“直方图”对话框中选定“图表输出”复选框，则直方图也同时输出，如图6。

2.特征数计算

在生物统计分析中主要计算平均数和变异数。

平均数主要有算术平均数、几何平均数及中数，变异数主要有极差、方差、标准差、变异系数。

1）．算术平均数

用AVERAGE函数可用来计算算术平均数，例如一组数据2，3，4，5，6的平均数可在单元格中键入“=AVERAGE（2，3，4，5，6）”得4。

如果这5个数据分别存放于A1到A5的字段中，则键入“=AVERAGE（A1:

A5）”得4。

通常“平均数”是指算术平均数。

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函数可以求样本的方差。

如在单元格中输入“=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工作表中输入数据

2．选择计算命令

这是一个配对设计的两样本平均数显著性测验。

选择“工具”菜单的“数据分析”命令，在弹出的“数据分析”对话框中，双击“t-检验：

平均值的成对二样本分析”选项，则弹出“t-检验：

平均值的成对二样本分析”对话框。

3．填写区域

分别填写变量1的区域：

$B$1:

$B$11，变量2的区域：

$C$1:

$C$11，由于我们进行的是等均值的检验，填写假设平均差为0，由于数据的首行包括标志项，所以选择“标志”复选框，再填写显著水平α为0.05，如图13所示，然后点击“确定”按扭，则可以得到下图所示的结果。

4．结果分析

如图14所示，表中分别给出了两组两种处理方法病痕数的平均值、方差和样本个数。

其中，合并方差是样本方差加权之后的平均值，DF是假设检验的自由度，t统计量是两个样本差值减去假设平均差之后再除于标准误差的结果，“P（T≤t）单尾”是单尾检验的显著水平，“t单尾临界”是单尾检验t的临界值，“P（T≤t）双尾”是双尾检验的显著水平，“t双尾临界”是双尾检验t的临界值。

由图表的结果可以看出t统计量均大于两个临界值，所以，在5%显著水平下，A、B两种处理方法对纯化病毒的效应差异显著。

对于非配对数据，可选择“工具”菜单的“数据分析”命令，在弹出的“数据分析”对话框中，双击“t-检验：

平均值的成组二样本分析”选项，则弹出“t-检验：

平均值的成组二样本分析”对话框。

选择数据后得到分析结果。

5.作业题：

5.9,5.22.

4.方差分析

（一）以A、B、C、D4种药剂处理水稻种子，其中A为对照，每处理各得4个苗高观察值（cm），得结果如表2，试进行方差分析。

表2不同药剂处理的水稻苗高（cm）

药剂

A

B

C

D

观察值

19

21

20

22

23

24

18

25

21

27

19

27

13

20

15

22

1．将资料1表中的数据复制到Excel表中，其输入格式如图15。

2．在“工具”菜单选择“数据分析”命令，弹出“分析工具”对话框，选中列表中“方差分析：

单因素方差分析”选项，单击“确定”按钮，在接着弹出的对话框中输入数据范围$B$2:

$E$5，a值设为0.05或0.01。

3．单击“确定”按钮，得方差分析表，如图16：

F＞F0.05，不同药剂处理苗高差异达到显著水平。

（二）用生长素处理豌豆，共6个处理。

豌豆种子发芽后，分别在每一箱中移植4株，每组6个木箱，每箱1个处理。

试验共有4组24箱，试验时按组排于温室中，使同组各项环境一致，然后记录各箱第一朵花时4株豌豆的总节间数，结果如表3，试作方差分析。

表3不同生长素处理豌豆的试验结果

处理

区组

Ⅰ

Ⅱ

Ⅲ

Ⅳ

对照（未处理）

60

62

61

60

赤霉素

65

65

68

65

动力精

63

61

61

60

吲哚乙酸

64

67

63

61

硫酸腺嘌呤

62

65

62

64

马来酸

61

62

62

65

1．将资料表3中数据资料复制到Excel中，输入格式如图17。

2．在“工具”菜单中选择“数据分析”命令，弹出“分析工具”对话框，选中“方差分析：

无重复双因素方差分析”选项，单击“确定”按钮，在接着弹出的对话框中输入数据范围$B$2:

$E$7，a值设为0.05或0.01。

3．单击“确定”按钮，得方差分析表，如图18：

在变异来源栏目：

行代表不同处理，列代表不同区组。

分析结果F＞F0.01，不同处理间差异达到极显著。

（三）施用A1、A2、A33种肥料于B1、B2、B33种土壤，以小麦为指示作物，每处理组合种3盆，得产量结果（g）于表4。

试作方差分析。

表4种肥料施入3种土壤的小麦产量（g）

肥料种类

盆

土壤种类

B1（油砂）

B2（二合）

B3（白僵）

A1

1

21.4

19.6

17.6

2

21.2

18.8

16.6

3

20.1

16.4

17.5

A2

1

12.0

13.0

13.3

2

14.2

13.7

14.0

3

12.1

12.0

13.9

A3

1

12.8

14.2

12.0

2

13.8

13.6

14.6

3

13.7

13.3

14.0

1．将资料表4中数据资料复制到Excel中，输入格式如图19。

2．在“工具”菜单中选择“数据分析”命令，在弹出“分析工具”对话框中选中“方差分析：

可重复双因素方差分析”命令，单击“确定”按钮，在接着弹出的对话框中输入数据范围$A$1:

$D$10，a值设为0.05或0.01。

3．单击“确定”按钮，得方差分析表，如图20：

在变异来源栏里，样本代表肥类因素，列代表土类，交互代表肥类×土类的互作，内部代表误差。

F测验结果表明：

肥类间和肥类×土类的互作的效应达到极显著。

多重比较请参考教材。

同学们可自行进行比较。

四、作业8.2，9.1，9.12

5.直线相关回归

某水稻研究所，进行水稻品种生育期与产量试验，其部分品种生育期与产量结果列于下表，试作回归相关分析。

（生育期为x，单位：

天；产量为y，单位：

kg/667m2）。

表7水稻品种生育期与产量试验结果表

品种编号

1

2

3

4

5

6

7

8

生育期x（天）

112

114

116

119

123

128

129

131

产量y（kg/667m2）

371

382

415

441

438

461

479

474

1．输入数据。

将上述资料录入某一Excel空白工作簿中。

2．数据分析计算

单击“工具”菜单中的“数据分析”命令，弹出数据分析对话框，在该对话框“分析工具”列表中选中“回归”选项，单击“确定”按钮，在随后弹出的“回归”对话框中，输入选项区中“X值输入区域”输入框中输入数据范围为$B$2:

$B$9，在“Y值输入区域”输入框中输入数据范围为$C$2:

$C$9；并在“数据区域”中选择置信度设为95%或99%；在输出选项区中选择，如在本工作表中输出结果则选择“输出区域”，如在其他工作表中输出结果则选择“新工作表组”，如在其他工作簿中输出结果则选择“新工作簿”，本例中选择在本工作表中输出结果；在“残差”选项区选定“残差”选项，然后单击“确定”按钮即弹出汇总结果。

图19回归分析结果汇总

3．相关系数显著性测验

在输出结果中可读出相关系数（r）为0.9546、决定系数（

）为0.9113。

查r值表，当v=7时，

，

，实得

>

，故P<0.01，应否定

，接受

，相关系数极显著，表明X和Y高度相关。

4．读写数据

在输出结果中可读出回归系数（b）为-208.8235294，回归截距（a）为5.27941176470588，并列回归方程

5．绘回归直线图和标定实际相关点

（1）在数据汇总结果中读出最小x（112）和最大x（131）值的两个对应的

值

=382.4706

=482.7794

（2）标定并连接（112，382.4706）（131，482.7794）两点

（3）标出各实际相关点

（4）在直线旁写上回归方程式

。

6．推断：

该品种的生育期天数与产量之间存在极显著的回归关系，可以利用上述回归方程式和回归直线图预测产量。

一般情况下，该品种水稻的生育期天数每增加一天，其产量将平均提高0.1756kg/667m2。

四、作业10.13