电子表格Ecel的数据处理论文

【摘要】提供了一种十分简便的利用电子表格Excel软件进行多元回归，处理均匀设计数据的方法，并进行了实例分析。

【关键词】电子表格均匀设计多元回归

均匀设计由我国数学家王元和方开泰于1978年提出，是我国独创的一种重大的科学实验方法。该方法较正交设计能更有效地处理多水平的试验，减少试验次数。均匀设计与常用的正交设计“均匀分布，整齐可比”的特点不同，只考虑试验点在试验范围内的均匀散布，能用较少的试验点获得最多的信息，在药学研究领域是一种十分理想的实验方法［1］，但是使用难度较大等因素阻碍了均匀设计的推广使用。本研究将提供一种十分简便的均匀设计的方法以供同行参考。

1均匀设计在实验设计中的应用现状

由于均匀设计数据处理不能采用方差分析，需要使用专业统计软件进行多元回归，对于一般研究人员来说，均匀设计没有正交设计上手快，使人敬而远之。经CNKI检索，1980年～2007年“摘要”中含有“正交设计”和“均匀设计”的医药卫生期刊中的文献篇数则分别为1506和761，在中药、药学期刊中则分别为1416和548；维普中刊数据库检索，1989年～2007年“任意字段”中含有“正交设计”和“均匀设计”的医药卫生期刊中的文献篇数分别为1383和392，在药学期刊中则分别为433和164。这说明均匀设计的应用较正交设计比应用明显偏少。莫少红［2］认为由于均匀设计在数据分析、优化计算方面较难掌握，需借助计算机及优化软件才能进行，是其在推广应用中受到一定限制的原因。

2用电子表格Excel进行均匀设计数据处理的基本步骤

实际上，利用Office中的电子表格Excel就可以满足一般情况下处理均匀设计数据的需要。基本操作步骤如下：

2.1确认回归分析功能的安装

回归分析功能位于“工具——数据分析——回归”，或者利用Office光盘完成Excel中分析工具的安装。在Excel中的“工具”中选择“加载宏”，再选择“分析工具库”，然后插入Office光盘，点击“确定”，完成Excel中分析工具的安装。以后在“工具”中就可以看到“数据分析”选项了，我们需要的是其中的“回归”功能。

2.2实验数据的整理

将试验方案的各因素假设为X1，X2...Xn，试验结果为Y。利用“回归”，将试验方案的各因素水平（具体数值）选为“x值输入区”，试验结果则选为“y值输入区”，按照需要设置“置信度”、“输出选项”、“残差”等，就能得到该试验的回归分析结果。

2.3回归分析参数的矫正

对于回归结果的处理，系数相对较小的项可以舍去。比如，回归结果中某项的系数极小而其指数项系数可以接受，那么可以舍去原来的一次项而考虑该因素的指数项与指标之间的关系。

2.4优化方案，得到最佳值

得到的回归方程可以通过求极值的方法得到最佳值，与直观结果进行比较分析，重复或追加试验，优化方案。

3用电子表格Excel进行均匀设计数据处理的过程举例

中药中常含有挥发油类有效成分，为了防止其散失，保证疗效，经常在提取挥发油后用环糊精包结。β环糊精饱和溶液法包结挥发油的工艺优选中常常考虑的因素有：β环糊精与挥发油用量比、包结温度、包结时间等。表1是文献［3］报道的一个均匀设计的试验方案及其结果。表1环糊精饱和溶液法包结挥发油的均匀设计及其结果（略）

该文献得到的回归方程为y=90.7-61.6（1/x1）+0.04x2-0.08x3，F=1.03<F0.1。用Excel直接得到的方程为y=68.8-1.43x1+0.10x2-0.09x3，F=0.42（p=0.75），R2=0.30。这个方程经过对第一个变量取倒数得到y=90.9-61.8（1/x1）+0.04x2-0.08x3，F=1.10（p=0.47），R2=0.52，和文献得到的回归方程基本一致。但是，方程的回归检验显著性不能满足要求，相关系数也很小。文献到此即转入正交设计进行试验，又进行了9次正交实验才得到结果，而利用Excel可以不用再进行正交实验，利用前面已取得的数据接着进行分析即可。

对于多元一次方程的回归分析，考虑用（X1+X2+…+Xn）n=Y的回归形式，即多元n次线性回归，也就是考虑试验各因素之间的交互作用（即X1*X2，X2*X3，X1*X2*X3……）等，就可以利用Excel强大而灵活的计算功能自行加入各因素的指数项及交叉项，然后使用“回归”功能，将要考虑的各项一并选为“x值输入区”就可以完成多元n次线性回归了。将表1的数据处理见表2。表2多元回归设计的变量设置（略）

回归后我们得到了一个相关系数为1，含11项的回归方程。显然，这个方程过于复杂，也无此必要。因此可以舍去一些系数相对很小的项，简化得到方程：y=-15.28+9.86x1+3.03x2+0.78x12-0.48x1×x2，F=38.57，R2=0.99。这个方程已经去掉了x3，即时间因素的影响，该因素在试验范围内对挥发油包结率的贡献可以忽略，实际中可以采用方便操作的一个条件即可。同时，可以看出试验中β环糊精与挥发油用量比在起主要影响的同时还与包结温度之间存在交互作用（x1×x2）。

继续简化，去掉交互项和x2，得到方程：y=16.76+19.93x1-1.55x12，F=7.12>F0.05，R2=0.78。其含义是：在β环糊精饱和溶液法包结挥发油的工艺中，β环糊精与挥发油用量比、包结温度、包结时间等因素对包结率的影响中β环糊精与挥发油用量比占主导位置，其余因素可以根据方便试验的原则选择。该方程的极值为（6.43，80.83），即在β环糊精与挥发油用量比为6.43时，挥发油包结率可以达到80.83％（文献通过正交设计得到最佳包结条件为：挥发油（ml)与环糊精（g)量比为1：6）。

此时的回归方程仅含一个变量x1，也可以直接利用Excel的作图功能做β环糊精与挥发油用量比与包结率的散点图并添加趋势线，得到回归方程，其结果与最后得到的方程一致。做图法也可以看出在6.5附近可以得到该方程的极大值(80)。

4结语

以上实例分析结果与文献结果基本一致，而采用均匀设计经过回归分析只用了7次试验，比文献总试验次数16次减少了一半以上的工作量。高英等［4］进行了均匀设计与正交设计在金莲花提取工艺筛选研究中的比较，结果表明正交设计与均匀设计试验所得的结果相近，而试验次数分别为25次和7次。

由于均匀设计的数据分析一般要藉助于回归分析，要用到线性回归模型、二次回归模型、非线性模型，以及各种选择回归变量的方法（如前进法、后退法、逐步回归、最优回归子集等），可以使用专业统计软件进行数据处理及结果求算。但是用上文介绍的方法也可以完成相当多的分析工作，而且有利于研究者对数据分析过程的认识。加强均匀设计在药物制剂工艺、处方分析等方面的研究和应用，将对医药研究工作有所裨益。本文抛砖引玉，以资大家借鉴。对此感兴趣的读者可以参考文献［5，6］获得更详细的介绍。

【参考文献】

1方开泰．均匀设计与均匀设计表．北京：科学出版社，1994，14：49．

2莫少红．均匀设计法在中药制剂中的应用概况．时珍国医国药，2001，12(8)：734～735．

3刘长河．乳香、香橼、前胡挥发油的包结研究．中药材，2002，25（7）：497～498．

4高英，李卫民，伍淑华，等．均匀设计与正交设计在金莲花提取工艺筛选研究中的比较应用．中国实验方剂学杂志，2002，8(1):1～3．

5方开泰，马长兴．正交与均匀设计．北京：科学出版社，2001．

6香港浸会大学均匀设计主页．http：//www.math.hkbu.edu.hk/UniformDesign/