第十章 Excel在试验数据处理中的应用

第十章 Excel在试验数据处理中的应用
（I）教学内容与要求
（1）掌握图表功能、相关的公式与函数关系、数据分析工具；
（2）掌握试验数据表格的建立；
（3）掌握Excel图表功能在试验数据处理中的应用；
（4）掌握Excel在方差分析中的应用；
（5）掌握Excel在回归分析中应用；
（6）掌握Ecxel在配方试验设计中应用。

（II）教学重点
Excel图表功能在试验数据处理中的应用、Excel在方差分析中的应用、掌握
Excel在回归分析中应用、Ecxel在配方试验设计中应用。

（III）教学难点
Excel在方差分析中的应用。

10.1 概述
Microsoft Excel是微软公司开发的Windows环境下的电子表格系统，它是目前应用最广泛的表格处理软件之一，它具有强有力的数据库管理功能、丰富的宏命令和函数，强有力的图表功能，随着版本的不断提高，Excel在试验设计与数据处理中的应用主要体现在一下几个方面。

10.1.1 图表功能
10.1.2 公式与函数
公式就是以等号（=）开头，由单元格名称、运算符和数据组成的字符串。

10.1.3 数据分析工具
10.1.3.1 分析工具库
（1）“分析工具库”的安装
a．在Excel【工具】菜单中，单击“加载宏”命令。

b．从【加载宏】对话框中选中“分析工具库”选项，确定即可完成安装。

C．安装完成后，在Excel【工具】菜单下就会出现新增加“数据分析”命令。

（2）“分析工具库”提供的分析工具
点击【工具】下的【数据分析】，即可显示“分析工具”列表，改列表中共有19种不同的分析工具可供选择。

10.1.3.2“规划求解”工具
“规划求解”工具可以对有多个变量的线性和非线性规划问题进行求解，省去了人工编制程序和手工计算的麻烦。

10.2试验数据表格的建立
建立试验数据表格是Excel处理试验数据的基础，主要是生成试验数据记录表和结果表示表，在这个过程中，不仅有原始数据的输入，还应对原始数据进行初步的运算，并整理出有关结果。

10.2.1 试验数据的类型及基本输入方法
建立一个新的Excel文件之后，便可以进行数据的输入操作。

数据输入的方法很简单，只需要单击需要输入数据的单元格，使之成为活动单元格，然后从键盘上输入数据，回车即
可。

Excel中数据按类型有多种，如数值型、字符型和逻辑型等，在输入数据时，需要注意不同类型数据的输入方法。

10.2.2 有规律数据的输入
10.2.2.1 自定义填充序列
可以利用Excel自定义的序列工具来完成一些常用数据序列的填充。

在实际应用过程中，可以将那些需要经常输入的数据设置成自定义填充序列，这样在每次输入这些数据的时候，只需要输入第一个数据，其余的数据就可以用填充柄复制产生。

10.2.2.2 相同数值或文本的输入
如果需要在相邻几个单元格中输入相同的数值或文本时，不必一个一个地输入，可以采用如下两种方法简便的输入。

（1）自动填充方法输入数据
（2）用数组输入方法输入数据
10.2.2.3 等差或等比数列的输入
（1）复制填充法输入
该法只能输入等差序列。

（2）使用序列对话框输入
（3）这种方法可以输入相同的数据、等差序列、等比序列等。

10.2.3 公式的输入
Excel不仅提供了完整的算术运算符，如+、-、*、/、%、^等，还提供了丰富的内置函数（公式），如SUM（求和），AVERAGE(求算术平均值)，STDEV（求标准差）等，从而可以根据数据处理需要，建立各种公式，对数据执行计算操作，生成新的数据。

10.2.3.1 运算符及其优先级
公式中的运算符包括算术运算符、比较运算符、文本运算符和引用运算符四类
10.2.3.2 公式的创建
在Excel中，凡是以“=”开头，由单元格名称、运算符或数据库组成的字符串都被认为是公式，公式的输入可以在选中的一个单元格内，也可以在公式编辑栏中进行。

10.2.3.3 单元格引用
引用的作用在于标识工作表上的单元格和单元格区域，并指明使用数据的位置。

单元格的引用包括相对引用、绝对引用、混合引用和外部引用4种。

（1）相对引用
如果希望当公式被复制到别的区域时，公式中引用的单元格也会随之相对应，这时应在公式中使用相对引用。

（2）绝对引用
如果希望当公式复制到别的区域时，公式中引用的单元格不会随之相对变动，则应在公式中使用绝对应用。

（3）混合引用
相对引用和绝对引用混用在同一公式中，就称为混合引用。

（4）外部引用
在Excel中，不但可以引用同一工作表的单元格（内部引用），还能引用同一工作簿中不同工作表中的单元格，也能引用不同工作簿中的单元格（外部引用），在引用时需注明工作簿和工作表的名称。

10.2.4 数据的复制
如果生成的数据具有相同的规律性的时候，许多数据可以通过复制生成，可以为数据的输入带来极大的方便。

复制数据可以在不同的单元格之间、不同工作表或不同工作簿之间进行。

复制时，可以复制一个数，也可以同时复制一批数据。

10.2.4.1 非公式单元格的复制
10.2.4.2 公式单元格的复制
（1）值复制
（2）公式复制
10.3 Excel图表功能在试验数据处理中的应用
图表主要用于试验数据的初步整理归纳，在Excel中，可以方便地将试验数据整理成合理的表格。

通过图表可以很直观地观察变量之间的相互关系，还可以为以后进一步整理数据奠定基础。

10.3.1 图表的生成
Excel生成图表的过程非常简单，只要按照【图表向导】的有关说明，一步一步地进行操作，即可完成图表的制作。

10.3.2 图表的编辑和修改
通过图表向导生成的图形可能不尽如人意，如图表尺寸比例不合适、坐标刻度不合理、漏掉了数据点或系列等，这时就需要对以生成的图表进行修改和格式化。

（1）图表类型的修改
若选择的图表类型不合理，应先选中待修改的图表，再由【图表】菜单，进入【图表类型】此时就可以根据需要选择新的图表类型了。

（2）数据源的修改
如果发现作图所用的数据不是所希望的，或者需要添加新的数据，这时可以由【图表】菜单或快捷方式进入【数据源】，重新输入数据区域或者添加新的数据系列。

（3）图表格式的修改
对图表格式进行修改通常可以直接用鼠标右键单击需要修改的部分，在打开的有关菜单中，进行有关设置和修改。

（4）图表选项的修改
【图表选项】可由“图表向导”、【图表】菜单或快捷键进入。

（5）图表大小的修改
可以单击图表区域，拖动单击出现的操作柄即可改变图表大小。

10.4 Excel在方差分析中的应用
10.4.1 单因素试验的方差分析
例10-6 对于P31例3-1中试验数据，如图10-47所示，试用Excel中“分析数据库”
10.4.2 无重复试验的双因素方差分析
例10-7对于P37例3-3 试验数据（如图10-50所示），试利用Excel中“分析数据库”的“无重复双因素方差分析”工具来判断pH值和硫酸铜溶液浓度对化验结果是否有显著性影响？
图10-50 无重复双因素方差分析数据
10.4.3 可重复试验的双因素方差分析
例10-8对于P42例3-4试验数据（如图10-53所示），试利用Excel中“分析数据库”的“可重复双因素方差分析”工具来判断两个因素及两者的交互作用对试验结果是否有显著性影响？
图10-53 可重复双因素方差分析数据
10.4.4 Excel内置函数在方差分析中的应用
Excel提供了多种可用于方差分析的内置函数，如FDIST、FINV、FTEST、COVAR等函数。

（1）FDIST函数
FDIST函数的功能是返回F概率分布，即F检测时的显著性水平α，根据该值的大小就可以判断某因素对试验结果的影响的显著性程度。

函数语法为：
FDIST（x,degrees freedom1,degrees freedom2）
(2) FINV函数
FINV函数功能是返回F概率分布的逆函数值，如果p=FDIST（X,…），则FINV(p，…)=x。

FINV函数语法为：
FINV（probability，degrees freedom1，degrees freedom2）
10.5 Excel在回归分析中应用
Excel提供了众多的回归分析手段，如分析工具库、规划求解、图表功能和内置函数都能用于回归分析。

例10-10 在送到某工厂的原料中，含有两种组分A、B，为了研究这两种组分含量之间的关系，取不同时间和地区进行化学分析。

得到如图10-64所示的结果，试确定两种组分含
10.5.1 图表法
图表法只能解决一元回归问题，不能解决多元回归问题。

10.5.2 分析工具库在回归分析中的应用
Excel“分析工具库”提供了“回归分析”分析工具，此工具通过对一组数据使用”最小二乘法”直线拟合，进行一元和多元线性回归分析。

10.5.3 Excel内置函数在回归分析中应用
（1）SLOPE函数
SLOPE函数的功能是计算根据一组know y’s和know x’s例如数据点拟合的线性回归直线的斜率。

斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。

SLOPE函数语法为：
SLOPE(know y’s，know x’s )
（2）INTERCEPT函数
INTERCEPT函数的功能是利用已知的x值与y值计算直线与y轴的截距。

函数语法为：INTERCEPT（know y’s，know x’s）
（3）CORREL函数
CORREL函数能计算出单元格区域array1和array2之间的相关系数，根据相关系数就可以确定两变量之间的线性相关程度。

CORREL函数语法为：
CORREL（array1，array2）
10.5.4“规划求解”在回归分析中应用
回归分析是试验数据处理中最有效的方法之一，许多试验设计、试验结果都要通过回归分析确定试验指标与因素之间的回归方程，为了确定最佳工艺参数，就会遇到最优化问题，即规划问题。

Excel提供的“规划求解”工具就可以解决这一问题。

在使用“规划求解”工具之前，应建立最优化求解问题的数学模型。