运用EXCEL函数进行区组试验设计

摘要：区组设计是科学研究和加工工艺中经常用到的试验设计方法，本文通过对区组设计中的配对设计和完全区组设计实质的分析，提出了运用EXCEL进行配对设计和完全区组设计的方法。结果认为灵活利用Excel的随机函数如RAND、RANDOMBETWEEN等和排序按钮可以快速进行区组试验设计。

关键词：配对设计；区组设计；EXCEL函数；局部控制

中图分类号：TP391文献标识码：A文章编号：1009-3044(2008)30-0705-03

Block Experimental Design by EXCEL Function

LI Xue-bin1, YU Xiao-Ling2

(1.College of Animal Science, Henan Institute of Science and Technology, Xinxiang 453003, China;2.College of Food Science, Henan Institute of Science and Technology, Xinxiang 453003, China)

Abstract: Block experimental design is one of the recommendations design in science experiment and produce research. In this article, block experimental design including paired design with EXCEL function is introduced. Finally, we thinks that the function such as RAND, RANDOMBETWEEN etc. in Excel can be used to do block experimental design rapidly.

Key words: paired design; block experimental design; EXCEL function; local control

1 引言

在科学研究和加工工艺试验中，当试验环境或试验单位差异较大时，仅根据重复和随机化两原则进行设计不能将试验环境或试验单位差异所引起的变异从试验误差中分离出来，因而试验误差较大，试验的精确性与检验的灵敏度较低。为解决这一问题，在试验环境或试验单位差异大时，可根据局部控制的原则，将整个试验环境或加工环境分成若干个小环境或小组，在小环境或小组内使非处理因素尽量一致，这样的试验设计就是区组试验设计[1,3]。配对设计和完全区组设计就是两种最重要的区组设计。虽然在有些资料中有利用EXCEL进行配对设计试验结果的统计分析方法，然而在生物统计学教材和专业的EXCEL书籍中均没有见到关于运用EXCEL进行区组设计的介绍[4]，本文将对如何运用EXCEL快速进行配对设计和完全区组试验设计加以介绍，从而为科学研究和加工工艺的

优化提供参考。

2 配对试验设计

2.1 配对设计的实质

配对设计是区组设计中最简单的一种设计，其是将性质相同或性质相近的对象配成一对，然后将同一对内的两个试验对象完全随机的分到两个不同的处理中所进行的试验设计。其要求配成对子的两个试验单位的初始条件尽量一致，不同对子间试验单位的初始条件允许有差异。每一个对子就是试验处理的一个重复，然后将配成对子的两个试验单位随机地分配到两个处理组中[1-3]。

2.2 配对试验设计的Excel实现

配对试验设计可以借助Excel的随机抽样函数非常快速的实现对子内处理的随机化。例如，现有供试单元18个，分别将条件相同的两个试验单位（如来源相同的原材料）配成对子，共9对，编号为1-9号。试用随机方法将每个对子中的两个供试单元分到甲、乙两个处理组中。具体操作步骤如下：

方法一：

1）新建一个Excel表格，在A2单元格输入“对子编号。

2）选择B2和C2单元格，分别输入个体编号1和2。

3）按下shift键，选择B2和C2单元格，拖动C2单元格右下角的填充柄至J2 单元格，填好对子编号，这个编号也是对子中第一个试验单位的编号，第二个试验单位不编号。哪个为第一个试验单位，哪个为第二个试验单位可以任意设定。

4）在A3单元格输入“随机数字”字符。A4单元格输入“对子中第一个试验单位组别”字符。A5单元格输入“对子中第二个试验单位组别”字符。

5）选择B3到J3单元格，设定单元格格式，数值保留0位有效数字见图1，确定。

6）选择B3 单元格，输入公式“=RAND()*100”，见图2。

7）回车，拖动B3 单元格右下角的填充柄至J3单元格，见图3。

8）选择B3到J3 单元格，点击右键复制，再点击右键，选择性粘贴设为数值型。

9）将奇数定为甲处理，偶数定为乙处理。这样便将对子中编有号码的第一个试验单位随机分配了处理。

10）未编号者的第二个试验单位随机分配处理结果相反。最后配对设计结果见图4。

方法二：

1）新建一个Excel表格，在A2 单元格输入“对子编号。

2）选择B2和 C2 单元格，分别输入个体编号1和2。

3）按下shift键，选择B2和 C2 单元格，拖动C2 单元格右下角的填充柄至J2 单元格，填好个体编号。

4）选择A3 单元格，输入“随机数字”字符。A4 单元格输入“对子中第一个试验单位组别”字符。A5 单元格输入“对子中第二个试验单位组别”字符。

5）选择B3 单元格，输入公式“= INT（RAND()*10）+1”或“=RANDBETWEEN（1，2）”，本例用“=RANDBETWEEN（1，2）” 图5。

6）回车，拖动B3 单元格右下角的填充柄至J3 单元格。

7）选择B3到J3 单元格，点击右键复制，再点击右键选择性粘贴，选择性粘贴设为数值型，结果见图6。

8）选择B4到J4 单元格，点击右键，选择性粘贴设为数值型，结果见图7。

9）选择B5 单元格，输入公式“=IF(B4=2,1,2)”，回车，拖动B5 单元格右下角的填充柄至J5 单元格见图8。

10）将对子中试验单位行中的1定为甲处理，2数定为乙处理。配对设计结果见图9。

需要说明的是，也可在上述第7步之后直接在B4单元格输入“=IF(B3=2,"乙","甲")”，向后推动填充柄至J4 单元格，在B5单元格输入“=IF(B3=2," 甲","乙")”，向后推动填充柄至J5 单元格，所得结果与图9相同，并且操作更简便。

3 完全区组试验设计

3.1 完全区组设计的实质

将整个试验环境或试验单位分成若干个小环境（如用一个生产流水线，同一台试验仪器等）或小组（如同一个操作人员或同一批加工材料），在小环境或小组内使非处理因素尽量一致。每个比较一致的小环境或小组，称为单位组（或区组）。它是根据局部控制的原则，将各单位组的试验动物随机分配到各处理组，这种设计称为随机单位组设计，也即随机区组设计。也就是说，当试验规模扩大到一定程度后，完全随机设计有时很难做到使试验条件一致，此时，可以把整个试验分成几个部分，每一部分内进行整套的试验，这每一个部分也就是区组，在同一区组内的供试对象和试验条件应尽可能一致，而区组之间则可有一定的差异，且不必控制；实际上，随机区组设计是配对设计的扩展和延伸，配对设计中的对子即相当于区组设计中的区组；但当试验的处理在两个以上时，如果存在某种对试验指标有较大影响的干扰因素（如试验单元的差别、试验空间、时间、测试仪器、操作人员等条件存在明显差异），根据配对试验可以通过局部控制降低试验误差的原理，将配对试验设计中的对子扩大为组，每组试验单元基本一致，不同组之间在该干扰因素方面有差别，组内不同的试验单元要随机实施不同的处理，这样的设计即为随机区组设计[1-3]，每个区组能安排所有处理数的区组设计为完全区组设计。

3.2 完全区组设计的Excel实现

由于在区组试验设计中，同一区组组内的试验单元应随机实施不同的处理，因此区组设计也可借助Excel的随机抽样函数快速的实现区组内处理的随机化，从而实现区组试验设计。

例如，5种食品添加剂分别以A1、A2、A3、A4、A5表示，供试单元20个有4个来源Ⅰ~Ⅳ。供试单元分别按编号为：1-5号为第Ⅰ组，6-10号为第Ⅱ组，11-15号为第Ⅲ组，16-20为第Ⅳ组。试按随机区组设计将供试单元分组。具体操作步骤如下：

2）选择B5 单元格，输入“=rand()”，见图11。

3）回车，选择B5，拖动B5单元格右下角的填充柄至U5单元格，填好随机数字。

4）选择B5到U5单元格，点击右键复制，再点击右键，选择性粘贴设为数值型，见图12。

5）将5种添加剂编号按顺序填入添加剂行对应的区组列中，见图13。

6）新建一个Excel工作表，将上述表2区组试验设计的内容复制到这个新的Excel工作表中，并删除供试单元编号行，结果见图14。

7）单击数据按钮，选择排序，见图15。

8）单击排序，出现排序对话框，单击选项按钮，出现排序选项对话框。方向选择按行排序，见图16。确定。

9）将排序对话框中主要关键字设定为行1，次要关键字设定为行2，确定，即出现图17结果。

10）将添加剂一行结果复制粘贴到图12表中，如图18。

11）删除随机数字行，并将区组编号相同的单元格合并，便得到区组试验设计结果，见图19。这说明在第一个区组内1-5供试单元应分别用A2、A4、A3、A1和A5种添加剂，余类推。

4 其他区组设计

广义的区组设计还包括拉丁方试验设计和不完全区组设计，拉丁方设计是从横行和直列两个方向进行双重局部控制，使得横行和直列两向皆成单位组，是比随机单位组设计多一个单位组的设计。完全区组设计的每个区组能安排所有处理数，可使所有处理间的比较能在同区组内进行，但有时由于实验条件的限制，区组的容量小于处理数，即区组并不能容纳全部的试验水平，这样就不能将所有的处理在同区组内比较。这样的区组就称为“不完全区组”。需要采用不完全随机区组设计[5]。这两种较为复杂的区组设计在由于篇幅所限，本文不再讨论。

5小结

1）配对设计实施处理的实质是将性质相同或性质相近的试验单位配成一对，然后将同一对内的两个试验单位完全随机的分到两个不同的处理中所进行的试验。灵活利用Excel的随机函数如RAND、RANDOMBETWEEN等就可以快速地进行科学研究中的配对设计。

2）完全区组设计实质是将整个试验环境分成若干个小环境或小组，在小环境或小组内使非处理因素尽量一致。每一独立供试单位在单位组内随机实施不同的处理，因此灵活利用Excel的随机函数如RAND、RANDOMBETWEEN等和排序按钮，可以快速地进行产品加工工艺优化中完全随机区组设计。

参考文献：

[1] 谢庄,贾青.兽医统计学[M].北京：高等教育出版社,2005:193-195

[2] 明道绪.生物统计附试验设计[M].3版.北京：中国农业出版社,2002:227-228

[3] Gerry P, Michael J. Experimental Design and Data Analysis for Biologists[M]. Cambridge: Cambridge University Press,2002:157-164.

[4] 吴权威,吕琳琳.Excel 2003函数与统计应用实务[M].北京:中国铁道出版社,2005:109-253.

[5] 杨德.试验设计与分析[M].北京:中国农业出版社,2002:95-113.