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

1、第十章 Excel在试验数据处理中的应用（I）教学内容与要求（1）掌握图表功能、相关的公式与函数关系、数据分析工具；（2）掌握试验数据表格的建立；（3）掌握Excel图表功能在试验数据处理中的应用；（4）掌握Excel在方差分析中的应用；（5）掌握Excel在回归分析中应用；（6）掌握Ecxel在配方试验设计中应用。（II）教学重点Excel图表功能在试验数据处理中的应用、Excel在方差分析中的应用、掌握Excel在回归分析中应用、Ecxel在配方试验设计中应用。（III）教学难点Excel在方差分析中的应用。10.1 概述Microsoft Excel是微软公司开发的Windows环境下的

2、电子表格系统，它是目前应用最广泛的表格处理软件之一，它具有强有力的数据库管理功能、丰富的宏命令和函数，强有力的图表功能，随着版本的不断提高，Excel在试验设计与数据处理中的应用主要体现在一下几个方面。10.1.1 图表功能10.1.2 公式与函数公式就是以等号（=）开头，由单元格名称、运算符和数据组成的字符串。10.1.3 数据分析工具10.1.3.1 分析工具库（1）“分析工具库”的安装a在Excel【工具】菜单中，单击“加载宏”命令。b从【加载宏】对话框中选中“分析工具库”选项，确定即可完成安装。C安装完成后，在Excel【工具】菜单下就会出现新增加“数据分析”命令。（2）“分析工具库”

3、提供的分析工具点击【工具】下的【数据分析】，即可显示“分析工具”列表，改列表中共有19种不同的分析工具可供选择。10.1.3.2“规划求解”工具“规划求解”工具可以对有多个变量的线性和非线性规划问题进行求解，省去了人工编制程序和手工计算的麻烦。10.2试验数据表格的建立建立试验数据表格是Excel处理试验数据的基础，主要是生成试验数据记录表和结果表示表，在这个过程中，不仅有原始数据的输入，还应对原始数据进行初步的运算，并整理出有关结果。10.2.1 试验数据的类型及基本输入方法建立一个新的Excel文件之后，便可以进行数据的输入操作。数据输入的方法很简单，只需要单击需要输入数据的单元格，使之成

4、为活动单元格，然后从键盘上输入数据，回车即可。Excel中数据按类型有多种，如数值型、字符型和逻辑型等，在输入数据时，需要注意不同类型数据的输入方法。10.2.2 有规律数据的输入10.2.2.1 自定义填充序列可以利用Excel自定义的序列工具来完成一些常用数据序列的填充。在实际应用过程中，可以将那些需要经常输入的数据设置成自定义填充序列，这样在每次输入这些数据的时候，只需要输入第一个数据，其余的数据就可以用填充柄复制产生。10.2.2.2 相同数值或文本的输入如果需要在相邻几个单元格中输入相同的数值或文本时，不必一个一个地输入，可以采用如下两种方法简便的输入。（1）自动填充方法输入数据（2

5、）用数组输入方法输入数据10.2.2.3 等差或等比数列的输入（1）复制填充法输入 该法只能输入等差序列。（2）使用序列对话框输入（3）这种方法可以输入相同的数据、等差序列、等比序列等。10.2.3 公式的输入Excel不仅提供了完整的算术运算符，如+、-、*、/、%、等，还提供了丰富的内置函数（公式），如SUM（求和），AVERAGE(求算术平均值)，STDEV（求标准差）等，从而可以根据数据处理需要，建立各种公式，对数据执行计算操作，生成新的数据。10.2.3.1 运算符及其优先级公式中的运算符包括算术运算符、比较运算符、文本运算符和引用运算符四类10.2.3.2 公式的创建在Excel中

6、，凡是以“=”开头，由单元格名称、运算符或数据库组成的字符串都被认为是公式，公式的输入可以在选中的一个单元格内，也可以在公式编辑栏中进行。10.2.3.3 单元格引用引用的作用在于标识工作表上的单元格和单元格区域，并指明使用数据的位置。单元格的引用包括相对引用、绝对引用、混合引用和外部引用4种。（1）相对引用如果希望当公式被复制到别的区域时，公式中引用的单元格也会随之相对应，这时应在公式中使用相对引用。（2）绝对引用 如果希望当公式复制到别的区域时，公式中引用的单元格不会随之相对变动，则应在公式中使用绝对应用。（3）混合引用 相对引用和绝对引用混用在同一公式中，就称为混合引用。（4）外部引用在

7、Excel中，不但可以引用同一工作表的单元格（内部引用），还能引用同一工作簿中不同工作表中的单元格，也能引用不同工作簿中的单元格（外部引用），在引用时需注明工作簿和工作表的名称。10.2.4 数据的复制如果生成的数据具有相同的规律性的时候，许多数据可以通过复制生成，可以为数据的输入带来极大的方便。复制数据可以在不同的单元格之间、不同工作表或不同工作簿之间进行。复制时，可以复制一个数，也可以同时复制一批数据。10.2.4.1 非公式单元格的复制10.2.4.2 公式单元格的复制（1）值复制（2）公式复制10.3 Excel图表功能在试验数据处理中的应用图表主要用于试验数据的初步整理归纳，在Exc

8、el中，可以方便地将试验数据整理成合理的表格。通过图表可以很直观地观察变量之间的相互关系，还可以为以后进一步整理数据奠定基础。10.3.1 图表的生成Excel生成图表的过程非常简单，只要按照【图表向导】的有关说明，一步一步地进行操作，即可完成图表的制作。10.3.2 图表的编辑和修改通过图表向导生成的图形可能不尽如人意，如图表尺寸比例不合适、坐标刻度不合理、漏掉了数据点或系列等，这时就需要对以生成的图表进行修改和格式化。（1）图表类型的修改若选择的图表类型不合理，应先选中待修改的图表，再由【图表】菜单，进入【图表类型】此时就可以根据需要选择新的图表类型了。（2）数据源的修改如果发现作图所用的

9、数据不是所希望的，或者需要添加新的数据，这时可以由【图表】菜单或快捷方式进入【数据源】，重新输入数据区域或者添加新的数据系列。（3）图表格式的修改对图表格式进行修改通常可以直接用鼠标右键单击需要修改的部分，在打开的有关菜单中，进行有关设置和修改。（4）图表选项的修改【图表选项】可由“图表向导”、【图表】菜单或快捷键进入。（5）图表大小的修改可以单击图表区域，拖动单击出现的操作柄即可改变图表大小。10.4 Excel在方差分析中的应用10.4.1 单因素试验的方差分析例10-6 对于P31例3-1中试验数据，如图10-47所示，试用Excel中“分析数据库”的“单因素方差分析”工具来判断温度对产

10、品得率是否有显著影响？温度得率（）6090 92 88 6597 93 92 7096 96 93 7584 83 88 8084 86 82 图10-47 单因素方差分析数据方差分析：单因素方差分析SUMMARY组观测数求和平均方差603270904653282947703285953753255857803252844方差分析差异源SSdfMSFP-valueF crit组间303.6475.915.180.3.组内50105总计353.614图10-49 单因素方差分析结果10.4.2 无重复试验的双因素方差分析例10-7对于P37例3-3 试验数据（如图10-50所示），试利用Exce

11、l中“分析数据库”的“无重复双因素方差分析”工具来判断pH值和硫酸铜溶液浓度对化验结果是否有显著性影响？图10-50 无重复双因素方差分析数据方差分析：无重复双因素分析SUMMARY观测数求和平均方差行 137.82.60.63行 236.52.0.行 334.71.0.行 432.50.0.列 149.52.3750.8025列 246.61.650.43列 345.41.350.方差分析差异源SSdfMSFP-valueF crit行5.31.40.0.00024.7571列2.21.25.80.00115.1433误差0.60.总计7.11图10-52 无重复双因素方差分析结果10.4.

12、3 可重复试验的双因素方差分析例10-8对于P42例3-4试验数据（如图10-53所示），试利用Excel中“分析数据库”的“可重复双因素方差分析”工具来判断两个因素及两者的交互作用对试验结果是否有显著性影响？图10-53 可重复双因素方差分析数据方差分析：可重复双因素分析SUMMARY10243852总计0.02观测数22228求和2422222290平均1211111111.25方差80822.78570.04观测数22228求和1618181668平均89988.5方差22883.14290.06观测数22228求和1627252492平均813.512.51211.5方差180.50.

13、588.8571总计观测数6666求和56676562平均9.11.10.8333310.333方差9.4.5.7.0667方差分析差异源SSdfMSFP-valueF crit样本44.222.166674.09230.04423.8853列11.533.0.70770.56573.4903交互2764.50.83080.56842.9961内部65125.总计147.8333323图10-55 可重复双因素方差分析结果10.4.4 Excel内置函数在方差分析中的应用Excel提供了多种可用于方差分析的内置函数，如FDIST、FINV、FTEST、COVAR等函数。（1）FDIST函数FD

14、IST函数的功能是返回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 在

15、送到某工厂的原料中，含有两种组分A、B，为了研究这两种组分含量之间的关系，取不同时间和地区进行化学分析。得到如图10-64所示的结果，试确定两种组分含量之间的关系。SUMMARY OUTPUT回归统计Multiple R0.R Square0.Adjusted R Square0.标准误差1.观测值10F0.05(1,8)=11.26F0.05(1,8)=5.32方差分析dfSSMSF Significance F 回归分析1130.758130.75844.24580.残差823.64212.95526总计9154.4Coefficients标准误差t StatP-valueLower 95

16、%Upper 95%下限 95.0%上限 95.0%Intercept5.1.878892.893530.020091.9.1.9.A/%0.0.036046.651750.000160.0.0.0.RESIDUAL OUTPUT观测值预测 B/%残差121.499672.50033218.-3.383322.0.3016420.78043-1.7804514.786761.21324610.0.28894719.0.65805815.0.25425916.0.535011013.-0.588图10-68 例10-10回归分析结果10.5.1 图表法图表法只能解决一元回归问题，不能解决多元回归

17、问题。10.5.2 分析工具库在回归分析中的应用Excel“分析工具库”提供了“回归分析”分析工具，此工具通过对一组数据使用”最小二乘法”直线拟合，进行一元和多元线性回归分析。10.5.3 Excel内置函数在回归分析中应用（1）SLOPE函数SLOPE函数的功能是计算根据一组know ys和know xs例如数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。SLOPE函数语法为：SLOPE(know ys，know xs )（2）INTERCEPT函数INTERCEPT函数的功能是利用已知的x值与y值计算直线与y轴的截距。函数语法为：INTERCEPT（know ys，know