附 Excel在统计分析中的应用

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Excel,在统计分析中的应用,（基本篇）,Excel,统计分析工具库建立,Excel,提供了一组强大的数据分析工具分析工具库 。,“分析工具库”的安装：,1.,在,Excel “,工具”菜单中，单击“加载宏”命令。,2.,由“加载宏”对话框中找到“分析工具库”选项，将其选中，然后单击“确定”按钮，即可完成安装。,3.,安装完成后在,Excel “,工具”菜单下就会新增加“数据分析”命令。,如果在步骤,2“,加载宏”对话框中没有找到“分析工具库”选项，就必须运行“,Microsoft office,安装”程序来加载“分析工具库”。,1,描述统计,Descriptive Statistics,描述统计的任务就是描述随机变量的统计规律性。常用的描述统计量有数学期望、方差、协方差、相关系数、矩等。,数学期望、方差,是最常用的两个统计量。,描述统计分析简单。,描述统计分析统计量计算,2,假设检验,Hypothesis Testing,假设检验,就是根据样本的信息来判断总体分布是否具有假设的特征。例如，已知样本来自正态总体，那么是否能说明它是来自均值等于 的正态分布呢？又如，已知两个相对独立的样本分别来自两个正态分布，那么是否能说这两个总体的均值相同，或方差相同？,这些都属于假设检验问题。,有时为了比较两种产品、两种仪器、两种方法等等的差异性，常在相同的条件下作对比试验，得到一批成对的观察值，然后分析观察数据，从而作出推断。这种方法称为逐对比较法。在假设检验中利用,t,统计量进行检验，因而称为基于成对数据的,t,检验或成对观测值的,t,检验。,成对数据资料中的成对数据,不是相互独立的,，,而是存在某种联系,，因而进行成对数据平均数显著性检验时，应从成对数据入手。,2.1,成对数据的假设检验,例题：为了分析某种新型减肥药剂是否对人具有显著减肥作用，现随机选取,12,位自愿者进行试验，服药后，间隔,1,个疗程，分别测其,12,位自愿者的体重见表,2,。,成对数据资料的均值检验,自愿者编号,1,2,3,4,5,6,7,8,9,10,11,12,服药前体重,x,133,145,100,156,127,122,142,110,136,166,138,110,1,个疗程后体重,y,120,122,105,133,108,110,135,105,122,145,128,108,假设服药前后，除服用此减肥药剂外，其余的生活方式、生活条件均未变化。试问根据此试验结果，能否判断这种新型减肥药对人具有显著减肥作用。,本例为典型的,成对观测值,t,检验问题,。,（,1,）利用,Excel,提供的统计函数,TTEST,来检验,有两种检验方法：,录入试验数据；插入统计函数,TTEST,，,单击确定按钮，弹出,TTEST,对话窗口,本例,t,检验所计算的概率值,p,0.000604,，,远远小于,0.01,，那么否定,H,0,，,接受,H,A,，,即服药前后自愿者的体重有极限著变化，表明减肥药剂有极限著减肥效果 。,（,2,）利用,Excel2000,提供的“,t-,检验：平均值的成对二样本分析”工具进行分析,先将观察数据输入工作表中；选择,工具,菜单中的数据分析命令，弹出数,据分析,对话框；,在分析工具列表中，选择,t-,检验：平均值的成对二样本分析,工具，单击确定按钮，弹出,t-,检验：平均值的成对二样本分析对话窗口 。,对检验结果分析，可以看出，样本的,t,统计量等于,4.74566402,，大于,t,0.05/2,的双边临界值,2.20098627,，也大于,t,0.01/2,的双边临界值,3.105815,（可查也可计算）。即,所以拒绝原假设,H,0,，,在置信度为,0.01,的情况下，服用新型减肥药剂的自愿者体重有极显著差异。因此，可得出结论，这种新型减肥药剂对人具有减肥作用，效果极显著。也可以由概率值,P,的大小来直接判断。,现在来讨论有关两个正态总体均值和方差的假设检验问题。假设有两个相互独立的样本，分别来自于正态总体,和,均未知，试从样本统计量去推断总体的均值、方差是否相等，即,2.2,双样本假设检验,现有两种茶多糖提取工艺，分别从两种工艺中各取,1,个随机样本来测定其粗提物中的茶多糖含量，结果见表,4,。问两种工艺的粗提物中茶多糖含量有无显著差异？,这是一个典型的双样本假设检验问题，下面就方差检验和均值检验分别给予讨论。,表,4,两种工艺粗提物中茶多糖含量测定结果,目的是检验两个样本的方差是否相等 。,2.2.1,双样本方差的检验,F,检验,（,1,）利用统计函数进行检验,FTEST,有两种方法：,本例计算的概率值,P,0.9368529120.05,，,表明双样本的方差无显著差异,也就是说两种工艺的粗提物中茶多糖含量的方差相等。,（,2,）利用,Excel 2000,提供的“,F,检验：双样本方差”工具进行检验。,对检验结果分析，可知，,Excel 2000,只提供了,F,检验的单尾临界值，而本例属于双边假设检验问题，因此需要查找双尾临界值。查,F,分布表得（,=0.05,，,n,1,=5,，,n,2,=6,）：,由,F,检验结果可得，,F,1.044304,0.05,表明两个样本的均数无显著差异性，即两种工艺的粗提物中茶多糖含量无显著差异。,（,2,） 利用,Excel 2000,提供的,“,t,检验：双样本等方差假设”,工具进行检验。,对检验结果分析，可知,所以接受原假设,H,0,，,即两种工艺的粗提物中茶多糖含量无显著差异,。,在以下的情况中，可以利用,Z,检验分析工具来检验两个总体均值之间是否存在差异。,总体方差及标准差已知，不论样本数的大小为多少，我们都可以使用,Z,检验来进行总体平均差（,1,2,）,的假设检验。,当总体方差及标准差未知的情况，但所抽取的样本数大于,30,时，我们也可以使用,Z,检验来进行总体平均差的假设检验。,2.3 Z,检验：双样本均值分析,一般而言，在两总体方差为已知，总体平均差（,1,2,）,的统计量，可以用下面的公式求得：,若两总体方差为未知，总体平均差（,1,2,）,的统计量，可以用下面的公式求得：,当两总体的样本数量小于,30,时，或者是无法得知两总体的方差和标准差值时，可以使用,t,检验。,在两总体方差为未知的情况下，则小样本总体平均差（,1,2,）,的检验统计量为：,也就是说，当两总体方差为已知时，我们使用,Z,分布来进行检验，若总体方差未知时，我们使用,t,分布来检验。,方差分析,Analysis of Variance,（,ANOVA,）,3.1,单因素试验方差分析,科学试验中仅仅考察一个因素的试验，称为单因素试验。单因素试验是最简单的科学试验，也是最常用的预备试验设计方法。,例如，以淀粉为原料生产葡萄糖过程中，残留的许多糖蜜可用于酱色生产。在生产酱色之前应尽可能彻底除杂，以保证酱色质量。为此对除杂方法进行选择。今选用,5,种除杂方法，每种方法做,4,次试验，试验结果见表,5,，试分析不同除杂方法的除杂效果有无差异？设各总体服从正态分布，且方差相等。,除杂方法（,A,i,）,除杂量（,x,ij,）,A,1,25.6,24.4,25.0,25.9,A,2,27.8,27.0,27.0,28.0,A,3,27.0,27.7,27.5,25.9,A,4,29.0,27.3,27.5,29.9,A,5,20.6,21.2,22.0,21.2,本研究的试验指标是除杂量，除杂方法为试验因素，不同的,5,种除杂方法就是试验因素的五个不同水平。假定除了除杂方法这一试验因素外，其余的一切试验条件都相同。这就是单因素试验。试验的目的是要分析,5,种不同除杂方法的除杂效果有无显著的差异，以确定最佳除杂方法。这是典型的单因素试验方差分析问题 。,借助于,Excel,单因素方差分析工具进行分析,对统计结果进行分析，可以看出，单元格区域,A13:G17,中的数据与单因素试验方差分析表中的各个统计量相对应，其中组间为因素,A,，,组内为误差,e,，,总计为总和，差异源为方差来源，,SS,为平方和，,df,为自由度，,MS,为均方（方差），,F,为,F,值，,P-value,为接受原假设,H,0,的概率（此,P,值越接近,0,，说明接受原假设的可能性就越小，反之亦然），,F,crit,为拒绝域的临界值,方差分析结果可见,F,=49.55323,F,0.01,（,d4,，,15,）,4.89,F,0.05,（,4,，,15,）,=3.055568,，,所以，在显著性水平,0.01,下拒绝原假设,H,0,，,认为,5,种不同除杂方法的除杂效果有极显著差异。但哪几个方法差异显著，哪几个方法差异不显著，尚需进一步进行多重比较分析，但,Excel,分析工具尚不能自动完成。由平均数大小可以初步判断,A,4,方法的除杂效果理想。,3.2,双因素试验方差分析,如果在试验中只有两个因素在改变，而其他因素保持不变，则称为双因素试验。双因素试验的方差分析就是观察两个因素的不同水平对研究对象的影响是否有显著性差异。根据是否考虑两个因素的交互作用，又将双因素方差分析分为双因素有重复试验的方差分析和双因素无重复试验的方差分析。,3.2.1,双因素有重复试验的方差分析,例如，在生产某种金属材料时，使用了四种原料、三种热处理温度。对于每种原料与每种热处理温度的组合各生产两次，产品强度的测定结果如图,19,所示。问原料、处理温度以及这两者的交互作用对产品强度是否有显著的影响（取显著性水平,=0.05,）？,利用,Excel 2000,提供的,方差分析：可重复双因素分析,工具分析,样本,为因素,A,，,列,为因素,B,，,交互,为因素,A,与,B,的交互作用，,内部,为误差，,总计,为总和，,差异源,为方差来源，,SS,为平方和，,df,为自由度，,MS,为均方，,F,为,F,值，,P-value,为接受原假设,H0,的概率，,F,crit,为拒绝域的临界值,F,a,（,u,，,v,）。,在显著性水平,0.05,下，原料和处理温度这两个因素对产品强度的影响都是显著的，且,F,I,=14.9288,远大于,=2.9961,，故两者的交互作用效应是高度显著的。,3.2.2,双因素无重复试验的方差分析,某厂现有化验员,3,人，担任该厂牛奶酸度（,T,）,的检验。每天从牛奶中抽样一次进行检验，连续,10,天的检验分析结果见表,10,。试分析,3,名化验员的化验技术有无差异，以及每天的原料牛奶酸度有无差异。,化验员,B,1,B,2,B,3,B,4,B,5,B,6,B,7,B,8,B,9,B,10,A,1,11.71,10.81,12.39,12.56,10.64,13.26,13.34,12.67,11.27,12.68,A,2,11.78,10.7,12.5,12.35,10.32,12.93,13.81,12.48,11.6,12.65,A,3,11.61,10.75,12.4,12.41,10.72,13.1,13.58,12.88,11.46,12.94,利用,Excel2000,提供的,“方差分析：无重复双因素分析”,工具分析,4,回归分析,Regression Analysis,设所研究的对象,受多个因素,的影响，假定影响因素与,的关系是线性的，则可建立多元线性回归数学模型：,式中,代表影响因素，通常是可以控制或预先给定的，故称为解释变量或自变量；,代表各种随机因素对,的影响的总和，称为随机误差项，根据中心极限定理，可以认为它服从正态分布，即,为所研究的对象，称为被解释变量或因变量,例如，在改革中，某食品企业重视科技人才，提供了足够的科研经费，获得了良好的经济效益。表,11,是该食品企业,1987,1998,年的经济效益、科研人员、科研经费的统计数据。根据这些统计数据，试建立企业经济效益与科研人员、科研经费之间的回归方程。,回归分析举例,年份,经济效益（万元）,科研人员（名）,科研经费（万元）,年份,经济效益（万元）,科研人员（名）,科研经费（万元）,1987,406,19,8.5,1993,632,38,13.7,1988,484,24,9.7,1994,685,47,14.4,1989,504,26,10.4,1995,750,49,16.2,1990,520,28,11.3,1996,794,50,18.5,1991,560,31,12.2,1997,866,51,20.3,1992,591,33,12.8,1998,989,53,25,本实例有两个自变量，科研人员（,x1,）,与科研经费（,x2,），,因变量为经济效益（,y,）,相关性分析,选择工具菜单中的数据分析命令，弹出数据分析对话框。 在分析工具列表框中，选相关系数工具，这时将出现相关系数对话框。,科研人员、科研经费和经济效益都有较强的相关性。因此，需要利用回归分析工具进一步建立关系式。,选择工具菜单中的数据分析命令，弹出数据分析对话框。,在分析工具列表框中，选回归工具，这时，将弹出回归对话框，,回归模型的建立操作步骤如下：,在,回归统计,区域中，给出的,为,0.997644,，调整后的,为,0.997121,，均很接近,1,，说明,与,的关系很密切。,检验：,检验：,在,方差分析,区域，给出的,检验值为,1905.786,远远大于,=4.26,，说明,与,之间的回归效果非常显著。,检验：回归系数的检验,科研人员与科研经费对该企业的经济效益有显著影响。优化回归方程为,5,正交试验结果分析,在实际工作中，常常需要同时考察,3,个或,3,个以上的试验因素，若进行全面试验，则试验的规模很大，往往因试验条件的限制而难于实施。对于多因素多水平试验，一般采用部分实施方法来设计试验的，如正交试验设计、均匀试验设计等。正交试验设计就是安排多因素试验、寻求最优水平组合的一种高效率试验设计方法。它从试验的全部水平组合中，挑选部分有代表性的水平组合进行试验，通过对这部分试验结果的分析了解全面试验的情况，找出最优的水平组合。,5.1,极差分析,试验结果极差分析,计算,K,值,计算,k,值,计算极差,R,绘制因素指标趋势图,优水平,因素主次顺序,优组合,结 论,试验号,因,素,A,B,C,D,液化率,%,1,1,（,10,）,1,（,1,）,1,（,20,）,1,（,1.5,）,0,2,1,2,（,4,）,2,（,35,）,2(2.5),17,3,1,3,（,7,）,3,（,50,）,3(3.5),24,4,2,（,50,）,1,2,3,12,5,2,2,3,1,47,6,2,3,1,2,28,7,3,（,90,）,1,3,2,1,8,3,2,1,3,18,9,3,3,2,1,42,K,1,41,13,46,89,K,2,87,82,71,46,K,3,61,94,72,54,k,1,13.7,4.3,15.3,29.7,k,2,29,27.3,23.7,15.3,k,3,20.3,31.3,24,18,优水平,A,2,B,3,C,3,D,1,R,j,15.3,27,8.7,14.4,主次顺序,B A D C,根据极差,R,j,的大小，可以判断各因素对试验指标的影响主次。比较各,R,值大小，可见,R,B,R,A,R,D,R,C,，,所以因素对试验指标影响的主次顺序是,BADC,。,即加酶量影响最大，其次是加水量和酶解时间，而酶解温度的影响较小。,试验结果方差分析,计算各列偏差平方和、自由度,列方差分析表，进行,F,检验,分析检验结果，写出结论,5.2,方差分析,实例：自溶酵母提取物是一种多用途食品配料。为探讨啤酒酵母的最适自溶条件，安排三因素三水平正交试验。试验指标为自溶液中蛋白质含量（）。,试验方案及结果分析见表。试对试验结果进行方差分析。,处理号,A,B,C,空列,试验结果,yi,1,1,（,50,）,1,（,6.5,）,1,（,2.0,）,1,6.25,2,1,2,（,7.0,）,2,（,2.4,）,2,4.97,3,1,3,（,7.5,）,3,（,2.8,）,3,4.54,4,2,（,55,）,1,2,3,7.53,5,2,2,3,1,5.54,6,2,3,1,2,5.5,7,3,（,58,）,1,3,2,11.4,8,3,2,1,3,10.9,9,3,3,2,1,8.95,K,1j,15.76,25.18,22.65,20.74,K,2j,18.57,21.41,21.45,21.87,K,3j,31.25,18.99,21.48,22.97,K,1j,2,248.38,634.03,513.02,430.15,K,2j,2,344.84,458.39,460.1,478.3,K,3j,2,976.56,360.62,461.39,527.62,总偏差平方和：,试验总次数为,n,，,每个因素水平数为,m,个，每个水平作,r,次重复,r,n/m,。,列偏差平方和：,变异来源,平方和,自由度,均方,F,值,Fa,显著性,A,45.4,2,22.7,79.6,F,0.05(2,4),=6.94,F,0.01(2,4),=18.0,*,B,6.49,2,3.24,11.4,*,C,0.31,2,0.16,误差,e,0.83,2,0.41,误差,e,1.14,4,0.285,总和,53.03,方差分析,表,由方差分析可见，因素,A,对试验指标的影响高度显著，因素,B,影响显著，因素,C,影响不显著。因素影响主次顺序,A-B-C,。,优化条件的确定：本试验指标为越大越好。对因素,A,、,B,分析，由,K,确定优水平为,A,3,、,B,1,；,因素,C,的水平改变对试验结果几乎无影响，从经济角度考虑，选,C,1,。,优水平组合为,A,3,B,1,C,1,。,即温度为,58,，,pH,值为,6.5,，加酶量为,2.0%,时，为酵母自溶最适条件。,