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

科学管理合理组织，不断深化“双基”建设，狠抓安全技术教育培训工作，全面落实“手指口述安全确认操作、岗位描述”安全管理规范，提高全员安全责任意识和整体操作技能。第十章Excel在试验数据处理中的应用（I）教学内容与要求（1）掌握图表功能、相关的公式与函数关系、数据分析工具；（2）掌握试验数据表格的建立；（3）掌握Excel图表功能在试验数据处理中的应用；（4）掌握Excel在方差分析中的应用；（5）掌握Excel在回归分析中应用；（6）掌握Ecxel在配方试验设计中应用。（II）教学重点Excel图表功能在试验数据处理中的应用、Excel在方差分析中的应用、掌握Excel在回归分析中应用、Ecxel在配方试验设计中应用。（III）教学难点Excel在方差分析中的应用。10.1概述MicrosoftExcel是微软公司开发的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.3Excel图表功能在试验数据处理中的应用图表主要用于试验数据的初步整理归纳，在Excel中，可以方便地将试验数据整理成合理的表格。通过图表可以很直观地观察变量之间的相互关系，还可以为以后进一步整理数据奠定基础。10.3.1图表的生成Excel生成图表的过程非常简单，只要按照【图表向导】的有关说明，一步一步地进行操作，即可完成图表的制作。10.3.2图表的编辑和修改通过图表向导生成的图形可能不尽如人意，如图表尺寸比例不合适、坐标刻度不合理、漏掉了数据点或系列等，这时就需要对以生成的图表进行修改和格式化。（1）图表类型的修改若选择的图表类型不合理，应先选中待修改的图表，再由【图表】菜单，进入【图表类型】此时就可以根据需要选择新的图表类型了。（2）数据源的修改如果发现作图所用的数据不是所希望的，或者需要添加新的数据，这时可以由【图表】菜单或快捷方式进入【数据源】，重新输入数据区域或者添加新的数据系列。（3）图表格式的修改对图表格式进行修改通常可以直接用鼠标右键单击需要修改的部分，在打开的有关菜单中，进行有关设置和修改。（4）图表选项的修改【图表选项】可由“图表向导”、【图表】菜单或快捷键进入。（5）图表大小的修改可以单击图表区域，拖动单击出现的操作柄即可改变图表大小。10.4Excel在方差分析中的应用10.4.1单因素试验的方差分析例10-6对于P31例3-1中试验数据，如图10-47所示，试用Excel中“分析数据库”的“单因素方差分析”工具来判断温度对产品得率是否有显著影响？温度得率（％）60℃90928865℃97939270℃96969375℃84838880℃848682图10-47单因素方差分析数据科学管理合理组织，不断深化“双基”建设，狠抓安全技术教育培训工作，全面落实“手指口述安全确认操作、岗位描述”安全管理规范，提高全员安全责任意识和整体操作技能。方差分析：单因素方差分析SUMMARY组观测数求和平均方差60℃327090465℃328294770℃328595375℃325585780℃3252844方差分析差异源SSdfMSFP-valueFcrit组间303.6475.915.180.3.组内50105总计353.614图10-49单因素方差分析结果10.4.2无重复试验的双因素方差分析例10-7对于P37例3-3试验数据（如图10-50所示），试利用Excel中“分析数据库”的“无重复双因素方差分析”工具来判断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-valueFcrit行5.31.40.0.00024.7571列2.21.25.80.00115.1433误差0.60.总计7.11图10-52无重复双因素方差分析结果10.4.3可重复试验的双因素方差分析例10-8对于P42例3-4试验数据（如图10-53所示），试利用Excel中“分析数据库”的“可重复双因素方差分析”工具来判断两个因素及两者的交互作用对试验结果是否有显著性影响？科学管理合理组织，不断深化“双基”建设，狠抓安全技术教育培训工作，全面落实“手指口述安全确认操作、岗位描述”安全管理规范，提高全员安全责任意识和整体操作技能。图10-53可重复双因素方差分析数据方差分析：可重复双因素分析SUMMARY10℃24℃38℃52℃总计0.02观测数22228求和2422222290平均1211111111.25方差80822.78570.04观测数22228求和1618181668平均89988.5方差22883.14290.06观测数22228求和1627252492平均813.512.51211.5方差180.50.588.8571总计观测数6666求和56676562平均9.11.10.8333310.333方差9.4.5.7.0667方差分析差异源SSdfMSFP-valueFcrit样本44.222.166674.09230.04423.8853列11.533.0.70770.56573.4903交互2764.50.83080.56842.9961内部65125.总计147.8333323图10-55可重复双因素方差分析结果科学管理合理组织，不断深化“双基”建设，狠抓安全技术教育培训工作，全面落实“手指口述安全确认操作、岗位描述”安全管理规范，提高全员安全责任意识和整体操作技能。10.4.4Excel内置函数在方差分析中的应用Excel提供了多种可用于方差分析的内置函数，如FDIST、FINV、FTEST、COVAR等函数。（1）FDIST函数FDIST函数的功能是返回F概率分布，即F检测时的显著性水平α，根据该值的大小就可以判断某因素对试验结果的影响的显著性程度。函数语法为：FDIST（x,degreesfreedom1,degreesfreedom2）(2)FINV函数FINV函数功能是返回F概率分布的逆函数值，如果p=FDIST（X,…），则FINV(p，…)=x。FINV函数语法为：FINV（probability，degreesfreedom1，degreesfreedom2）10.5Excel在回归分析中应用Excel提供了众多的回归分析手段，如分析工具库、规划求解、图表功能和内置函数都能用于回归分析。例10-10在送到某工厂的原料中，含有两种组分A、B，为了研究这两种组分含量之间的关系，取不同时间和地区进行化学分析。得到如图10-64所示的结果，试确定两种组分含量之间的关系。SUMMARYOUTPUT回归统计MultipleR0.RSquare0.AdjustedRSquare0.标准误差1.观测值10F0.05(1,8)=11.26F0.05(1,8)=5.32方差分析dfSSMSFSignificanceF回归分析1130.758130.75844.24580.残差823.64212.95526科学管理合理组织，不断深化“双基”建设，狠抓安全技术教育培训工作，全面落实“手指口述安全确认操作