Excel在统计分析中的应用教学课件

1、Excel在统计分析中的应用（基本篇）第1页，共46页。Excel统计分析工具库建立Excel 提供了一组强大的数据分析工具分析工具库 。“分析工具库”的安装： 1.在Excel “工具”菜单中，单击“加载宏”命令。 2. 由“加载宏”对话框中找到“分析工具库”选项，将其选中，然后单击“确定”按钮，即可完成安装。 3. 安装完成后在Excel “工具”菜单下就会新增加“数据分析”命令。如果在步骤2“加载宏”对话框中没有找到“分析工具库”选项，就必须运行“Microsoft office安装”程序来加载“分析工具库”。第2页，共46页。第3页，共46页。第4页，共46页。1 描述统计 Descr

2、iptive Statistics 描述统计的任务就是描述随机变量的统计规律性。常用的描述统计量有数学期望、方差、协方差、相关系数、矩等。 数学期望、方差是最常用的两个统计量。 描述统计分析简单。第5页，共46页。描述统计分析统计量计算第6页，共46页。2 假设检验 Hypothesis Testing 假设检验就是根据样本的信息来判断总体分布是否具有假设的特征。例如，已知样本来自正态总体，那么是否能说明它是来自均值等于 的正态分布呢？又如，已知两个相对独立的样本分别来自两个正态分布，那么是否能说这两个总体的均值相同，或方差相同？这些都属于假设检验问题。第7页，共46页。 有时为了比较两种产品

3、、两种仪器、两种方法等等的差异性，常在相同的条件下作对比试验，得到一批成对的观察值，然后分析观察数据，从而作出推断。这种方法称为逐对比较法。在假设检验中利用t统计量进行检验，因而称为基于成对数据的t检验或成对观测值的t检验。 成对数据资料中的成对数据不是相互独立的，而是存在某种联系，因而进行成对数据平均数显著性检验时，应从成对数据入手。2.1 成对数据的假设检验第8页，共46页。例题：为了分析某种新型减肥药剂是否对人具有显著减肥作用，现随机选取12位自愿者进行试验，服药后，间隔1个疗程，分别测其12位自愿者的体重见表2。成对数据资料的均值检验 自愿者编号123456789101112服药前体重

4、x1331451001561271221421101361661381101个疗程后体重y120122105133108110135105122145128108假设服药前后，除服用此减肥药剂外，其余的生活方式、生活条件均未变化。试问根据此试验结果，能否判断这种新型减肥药对人具有显著减肥作用。 本例为典型的成对观测值t检验问题。第9页，共46页。（1）利用Excel提供的统计函数TTEST来检验 有两种检验方法：录入试验数据；插入统计函数TTEST，单击确定按钮，弹出TTEST对话窗口 本例t检验所计算的概率值p0.000604，远远小于0.01，那么否定H0，接受HA，即服药前后自愿者的体重

5、有极限著变化，表明减肥药剂有极限著减肥效果 。第10页，共46页。（2）利用Excel2000提供的“t-检验：平均值的成对二样本分析”工具进行分析 先将观察数据输入工作表中；选择工具菜单中的数据分析命令，弹出数据分析对话框； 在分析工具列表中，选择t-检验：平均值的成对二样本分析工具，单击确定按钮，弹出t-检验：平均值的成对二样本分析对话窗口 。第11页，共46页。对检验结果分析，可以看出，样本的t统计量等于4.74566402，大于t0.05/2的双边临界值2.20098627，也大于t0.01/2的双边临界值3.105815（可查也可计算）。即所以拒绝原假设H0，在置信度为0.01的情况

6、下，服用新型减肥药剂的自愿者体重有极显著差异。因此，可得出结论，这种新型减肥药剂对人具有减肥作用，效果极显著。也可以由概率值P的大小来直接判断。第12页，共46页。现在来讨论有关两个正态总体均值和方差的假设检验问题。假设有两个相互独立的样本，分别来自于正态总体 和 均未知，试从样本统计量去推断总体的均值、方差是否相等，即2.2 双样本假设检验 现有两种茶多糖提取工艺，分别从两种工艺中各取1个随机样本来测定其粗提物中的茶多糖含量，结果见表4。问两种工艺的粗提物中茶多糖含量有无显著差异？ 这是一个典型的双样本假设检验问题，下面就方差检验和均值检验分别给予讨论。表4 两种工艺粗提物中茶多糖含量测定结

7、果第13页，共46页。目的是检验两个样本的方差是否相等 。2.2.1 双样本方差的检验F检验（1）利用统计函数进行检验FTEST有两种方法：本例计算的概率值P0.9368529120.05，表明双样本的方差无显著差异,也就是说两种工艺的粗提物中茶多糖含量的方差相等。第14页，共46页。（2）利用Excel 2000提供的“F检验：双样本方差”工具进行检验。第15页，共46页。对检验结果分析，可知，Excel 2000只提供了F检验的单尾临界值，而本例属于双边假设检验问题，因此需要查找双尾临界值。查F分布表得（ =0.05，n1=5，n2=6）：由F检验结果可得，F1.0443040.05,表明

8、两个样本的均数无显著差异性，即两种工艺的粗提物中茶多糖含量无显著差异。第18页，共46页。（2） 利用Excel 2000提供的“t检验：双样本等方差假设”工具进行检验。第19页，共46页。对检验结果分析，可知所以接受原假设H0，即两种工艺的粗提物中茶多糖含量无显著差异。第20页，共46页。在以下的情况中，可以利用Z 检验分析工具来检验两个总体均值之间是否存在差异。 总体方差及标准差已知，不论样本数的大小为多少，我们都可以使用Z检验来进行总体平均差（12）的假设检验。 当总体方差及标准差未知的情况，但所抽取的样本数大于30时，我们也可以使用Z检验来进行总体平均差的假设检验。2.3 Z 检验：双

9、样本均值分析一般而言，在两总体方差为已知，总体平均差（12）的统计量，可以用下面的公式求得：第21页，共46页。若两总体方差为未知，总体平均差（12 ）的统计量，可以用下面的公式求得： 当两总体的样本数量小于30时，或者是无法得知两总体的方差和标准差值时，可以使用t检验。 在两总体方差为未知的情况下，则小样本总体平均差（12 ）的检验统计量为：也就是说，当两总体方差为已知时，我们使用Z分布来进行检验，若总体方差未知时，我们使用t分布来检验。第22页，共46页。方差分析 Analysis of Variance（ANOVA）3.1 单因素试验方差分析科学试验中仅仅考察一个因素的试验，称为单因素试

10、验。单因素试验是最简单的科学试验，也是最常用的预备试验设计方法。 例如，以淀粉为原料生产葡萄糖过程中，残留的许多糖蜜可用于酱色生产。在生产酱色之前应尽可能彻底除杂，以保证酱色质量。为此对除杂方法进行选择。今选用5种除杂方法，每种方法做4次试验，试验结果见表5，试分析不同除杂方法的除杂效果有无差异？设各总体服从正态分布，且方差相等。第23页，共46页。除杂方法（Ai）除杂量（xij）A125.624.425.025.9A227.827.027.028.0A327.027.727.525.9A429.027.327.529.9A520.621.222.021.2本研究的试验指标是除杂量，除杂方法为

11、试验因素，不同的5种除杂方法就是试验因素的五个不同水平。假定除了除杂方法这一试验因素外，其余的一切试验条件都相同。这就是单因素试验。试验的目的是要分析5种不同除杂方法的除杂效果有无显著的差异，以确定最佳除杂方法。这是典型的单因素试验方差分析问题 。第24页，共46页。借助于Excel单因素方差分析工具进行分析 第25页，共46页。对统计结果进行分析，可以看出，单元格区域A13:G17中的数据与单因素试验方差分析表中的各个统计量相对应，其中组间为因素A，组内为误差e，总计为总和，差异源为方差来源，SS为平方和，df为自由度，MS为均方（方差），F为F值，P-value为接受原假设H0的概率（此P

12、值越接近0，说明接受原假设的可能性就越小，反之亦然），F crit为拒绝域的临界值方差分析结果可见F=49.55323F0.01（d4，15）4.89F0.05（4，15） =3.055568，所以，在显著性水平0.01下拒绝原假设H0，认为5种不同除杂方法的除杂效果有极显著差异。但哪几个方法差异显著，哪几个方法差异不显著，尚需进一步进行多重比较分析，但Excel分析工具尚不能自动完成。由平均数大小可以初步判断A4方法的除杂效果理想。 第26页，共46页。3.2 双因素试验方差分析如果在试验中只有两个因素在改变，而其他因素保持不变，则称为双因素试验。双因素试验的方差分析就是观察两个因素的不同水

13、平对研究对象的影响是否有显著性差异。根据是否考虑两个因素的交互作用，又将双因素方差分析分为双因素有重复试验的方差分析和双因素无重复试验的方差分析。3.2.1 双因素有重复试验的方差分析 例如，在生产某种金属材料时，使用了四种原料、三种热处理温度。对于每种原料与每种热处理温度的组合各生产两次，产品强度的测定结果如图19所示。问原料、处理温度以及这两者的交互作用对产品强度是否有显著的影响（取显著性水平=0.05）？第27页，共46页。第28页，共46页。利用Excel 2000提供的方差分析：可重复双因素分析工具分析 第29页，共46页。样本为因素A，列为因素B，交互为因素A与B的交互作用，内部为

14、误差，总计为总和，差异源为方差来源，SS为平方和，df为自由度，MS为均方，F为F值，P-value为接受原假设H0的概率，F crit为拒绝域的临界值Fa（u，v）。 在显著性水平0.05下，原料和处理温度这两个因素对产品强度的影响都是显著的，且FI =14.9288远大于=2.9961，故两者的交互作用效应是高度显著的。第30页，共46页。3.2.2 双因素无重复试验的方差分析某厂现有化验员3人，担任该厂牛奶酸度（T）的检验。每天从牛奶中抽样一次进行检验，连续10天的检验分析结果见表10。试分析3名化验员的化验技术有无差异，以及每天的原料牛奶酸度有无差异。化验员B1B2B3B4B5B6B7

15、B8B9B10A111.7110.8112.3912.5610.6413.2613.3412.6711.2712.68A211.7810.712.512.3510.3212.9313.8112.4811.612.65A311.6110.7512.412.4110.7213.113.5812.8811.4612.94利用Excel2000提供的“方差分析：无重复双因素分析”工具分析 第31页，共46页。第32页，共46页。4 回归分析 Regression Analysis设所研究的对象 受多个因素 的影响，假定影响因素与 的关系是线性的，则可建立多元线性回归数学模型： 式中 代表影响因素，通常

16、是可以控制或预先给定的，故称为解释变量或自变量； 代表各种随机因素对 的影响的总和，称为随机误差项，根据中心极限定理，可以认为它服从正态分布，即 为所研究的对象，称为被解释变量或因变量 第33页，共46页。 例如，在改革中，某食品企业重视科技人才，提供了足够的科研经费，获得了良好的经济效益。表11是该食品企业19871998年的经济效益、科研人员、科研经费的统计数据。根据这些统计数据，试建立企业经济效益与科研人员、科研经费之间的回归方程。回归分析举例年份经济效益（万元）科研人员（名）科研经费（万元）年份经济效益（万元）科研人员（名）科研经费（万元）1987406198.519936323813

17、.71988484249.719946854714.419895042610.419957504916.219905202811.319967945018.519915603112.219978665120.319925913312.819989895325本实例有两个自变量，科研人员（x1）与科研经费（x2），因变量为经济效益（y） 第34页，共46页。相关性分析选择工具菜单中的数据分析命令，弹出数据分析对话框。 在分析工具列表框中，选相关系数工具，这时将出现相关系数对话框。第35页，共46页。科研人员、科研经费和经济效益都有较强的相关性。因此，需要利用回归分析工具进一步建立关系式。 第36

18、页，共46页。 选择工具菜单中的数据分析命令，弹出数据分析对话框。 在分析工具列表框中，选回归工具，这时，将弹出回归对话框， 回归模型的建立操作步骤如下：第37页，共46页。第38页，共46页。在回归统计区域中，给出的 为0.997644，调整后的 为0.997121，均很接近1，说明 与 的关系很密切。 检验： 检验： 在方差分析区域，给出的 检验值为1905.786远远大于 =4.26，说明 与 之间的回归效果非常显著。检验：回归系数的检验 科研人员与科研经费对该企业的经济效益有显著影响。优化回归方程为第39页，共46页。5 正交试验结果分析在实际工作中，常常需要同时考察3个或3个以上的试

19、验因素，若进行全面试验，则试验的规模很大，往往因试验条件的限制而难于实施。对于多因素多水平试验，一般采用部分实施方法来设计试验的，如正交试验设计、均匀试验设计等。正交试验设计就是安排多因素试验、寻求最优水平组合的一种高效率试验设计方法。它从试验的全部水平组合中，挑选部分有代表性的水平组合进行试验，通过对这部分试验结果的分析了解全面试验的情况，找出最优的水平组合。 第40页，共46页。5.1 极差分析试验结果极差分析计算K值计算k值计算极差R绘制因素指标趋势图优水平因素主次顺序优组合结 论第41页，共46页。试验号因 素ABCD液化率 %11（10）1（1）1（20）1（1.5）0212（4）2

20、（35）2(2.5)17313（7）3（50）3(3.5)2442（50）123125223147623122873（90）132183213189332142K141134689K2 87827146K361947254k113.74.315.329.7k22927.323.715.3k320.331.32418优水平A2B3C3D1Rj15.3278.714.4主次顺序B A D C根据极差Rj的大小，可以判断各因素对试验指标的影响主次。比较各R值大小，可见RBRARDRC，所以因素对试验指标影响的主次顺序是BADC。即加酶量影响最大，其次是加水量和酶解时间，而酶解温度的影响较小。第42页，共46页。试验结果方差分析计算各列偏差平方和、自由度列方差分析表，进行F检验分析检验结果，写出结论5.2 方差分析第43页，共46页。实例：自溶酵母提取物是一种多用途食品配料。为探讨啤酒酵母的最适自溶条件，安排三因素三水平正交试验。试验指标为自溶液中蛋白质含量（）。试验方案及结果分析见表。试对试验结果