使用EXCEL进行统计分析

1、使用EXCEL进行统计分析六西格玛管理方法给我们的组织带来了管理上新的认识，其中包含了如方差分析、假设 检验、回归分析、基本统计等大量的数理工具，但问题也随之而来，那就是大量的数据处理， 这时我们就会想到用软件来帮助完成大量的运算。现在应用于这一方面的软件很多，如 MINITAB、SPSS、SAS等，但是大多为英文软件，在这里我要介绍的就是用EXCEL的数据分析与规划求解来帮助您进行简单的数据分析。EXCEL的数据分析工具为中文界面，操作简单，在一定程度上能给予大家帮助。第一节加载宏EXCEL的数据处理除了提供了很多的函数外，另外还有两个功能就是数据分析和规划求解，但这两个工具必须加载相应的宏

2、后才能使用，操作步骤为工具加载宏，出现如图1-1的对话框，选择分析工具库和规划求解，点击确定后，在工具菜单栏内出现了这两个分析工具。厂吉用釈肖1iajJSili.1自机00 . 1图1-1第二节随机数据与基本分析 在上一节通过加载宏在工具栏内增加了两个数据分析库， 绍数据分析工具库中的随机数生成器和描述统计。操作工具数据分析，出现如图2-1的对话框，对话框包含了方差分析、相关系数、协方差分析等十几种分析工具，但一般处理对象为简单的数据。勞祈工員®数据分析与规划求解，在本节将介冲差分析；无遥豈誠因素分ff协 譎普驕本方蔓心甘；几H小 直方图二J确走 I 取消I 帮肋（KlJ图2-1在对

3、话框内选择随机数发生器，将出现如图2-2的对话框，对话框提供了相应的随机数产生及存储的条件命令，具体如下：aJi. iTtk f 怜M输y谨顶C锚出迂耳屯- 药T化杰泪ttiC融工便帝-2变量个数：需要形成几组数据。随机数个数：每组数据包含的数据个数。分布：选择随机形成的数据期望服从的概率分布类型，包括均匀分布、正态分布、二项 分布、离散分布等。4. 参数：输入不同分布的参数，如正态分布要求提供平均值及标准偏差。5. 随机数基数：输入随机数的基数。6输出选项：选择输出区域将使随机数据在同一工作表的制定位置放置，选择新工作表组 将使随机数据放置在同一工作薄的新工作表中，选择新工作薄

4、将生成一个新的工作薄来存储数据或分析结果，以上选择将出现在后续的各种分析工具对话框中，操作意义相同。2-3的对话框，对话框的内容操作工具数据分析，在对话框内选择描述统计，将出现如图 解释如下：帰如统计輸人区域p-KJ分#F式(?溥列工1 r遹订r柝南悅于le-仃(u晞出选项|$T£i£广昴工榨叢粗1广撕工忙薄00理1：! H B .7平均鞋g肓座®|1心詔監小伯嗟I-|1图2-31输入区域：输入数据所在的区域，可以包含数据的标志，注意分析的数据单个总体应包 含在一列或一栏中。2.分组方式：提供了两种选择，如果单个数据总体在同一行内，请选择逐行，如果在列中, 则选择

5、逐列。3. 在选取数据区域时如果选择了数据标志，请选择标志位于第一行。4. 输出选项：前三项内容如前所述，后四项选择内容供选择数据分析的内容。示例，用随机数发生器生成 3组正态数据，每组数据容量为20,数据的平均值为5,标准偏差为0.2,并对形成的数据作描述性统计。选择数据分析工具库的随机数发生器，在对话框中变量个数栏输入3,在随机数个数栏选择20,分布类型下拉表中选择正态分布，参数平均只栏输入5，标准偏差栏输入0.2,随机数基数输入0,结果如图2-4 ,确定后再工作表中输出结果如图2-5 ,其中的列标志为提前输入。JjlW乖 CD?-为塾=平坛宜凹壬痢士储=j .艸Is|0?£陡苇

6、赧呈較血： 声砒卯忙ft施戒心r酣"ntp绢旺' 厂If工扑超也|0图2-4AC I jBtokTWWWWSW'3 宦 2552W4 d. 56328255 ,： 4. 7G25539 丄.j 4” 630617S7b76413S3 : 5. 0263706 9 ； L W519 W 4. 9627635 nZ 1731346 12 :5.3322912 n : 5. 18043 ILi乩创刃11 IE , 5* 15152234. 74446345. 23967 1.返3輕4. 895924. S0447414.戏&虹54. 92G90L4 氏 25S52E

7、3 4. B9735K5E. 47BL3094 67752055. 貂3丁只$1 乩 L3502774. 71LL62716 :4. 5955858 4. 9274246 LT乩UU拓2翼4. GE1S035 : 5, 2旳534 溯耳1冋利右 口 ：反 119H34 224. y3b4b6y4. S52704G4, H弭5. n药附刼Q. 725635 043S5155. 右曬師4.希蚯§4.呂越屈戏4. 9打凶054.3450134. 93294316. 3944424. S&901S75.1肿玮的4.汨d肿管! 92373524. 8305525.,t戈玺辿12

8、63;巴丄3（亡_上兰_工5. 45KyuO34.4S44S3S4,貓92昭听.14紇1S4. 7763E23图2-5选择数据分析工具库中的描述统计对上述形成的数据进行分析，在对话框中输入区域选择随机数据所在的表格区域，分组方式按要求为逐列，因为包含标志所以选择标志位于第一行， 选择输出位置及汇总统计，均值置信度为95%及最大和最小值。 选择如图2-6所示，单击确#N/A。定，输出如图2-7的分析结果，注意其中的众数由于不存在而显示为1倔1:$九1iE列口 宀逐厅dPllEtE:vJI确宦 I_HI申科助迪I I海恤tJEl域址J小新二也菲®/<<</, nvp兀后

9、临十5碎瀾5依虞W 坊童K丈値Q *勢E小值血.图2-6E.FGHIJ随机数据1随机数据2随机数据3平均4. %54?9 平均4. 962208 平均4 990251标准误差0,阴鉴私标推误羣” U.O51653标進谋差J a 053632中脸数4. 9041%中位数4.91213中位数4. 95S772厶数#N/A 4数#N/A 介数#N/A标淮差0. 245843标淮差0,231001标准差 ，0.23984S万差0. 060432 気差0. 053361 方差0.0575271峰度-1.14695 g度-0. 09332 14Jt0. 13119S偏度-0.29599 偏度0. 8E94

10、73 偏度a 151346区域0'. 769009 区域0.79761 E 域k 954416最木值4.563232最小值4. 67752最水值4, 4844S4最大值乩332291最夫值,5. 475131最大值5. 4389；求和99.3095S99. 24416 求和99. S0501观测数20獅擞20 wm胆1最去乩032291最大5釘5131 麻(1)5.43S9最小Q)_ '4. 563282 最小(1)4. 67752 最小(1)_4.484484ilWjS(9E0.11505藝置信度WE 0.10S112置信度恣0,1122521卩们口仁f图2-7第三节方差分析

11、 在数据分析工具库中提供了 3种基本类型的方差分析：单因素方差分析、双因素无重复试验 和可重复试验的方差分析，本节将分别介绍这三种方差分析的应用： 单因素方差分析在进行单因素方差分析之前，须先将试验所得的数据按一定的格式输入到工作表中，其中每种水平的试验数据可以放在一行或一列内，具体的格式如图3-1，图中每个水平的试验数据结果放在同一行内。E2LEVEL：5, 5634LEVEL：5. 42FLEVEL4匚&4£辽VELF V、p. 1'C.33ABK39图3-1_ L a 了冰匸数据输入完成以后，操作工具数据分析，选择数据分析工具对话框内的单因素方差分析, 出现如图

12、3-2的对话框，对话框的内容如下：1. 输入区域：选择分析数据所在区域，可以选择水平标志，针对图 选取（绿色）和黄色区域。2. 分组方式：提供列与行的选择，当同一水平的数据位于同一行时选择行，位于同一列时 选择列，本例选择行。3. 如果在选取数据时包含了水平标志，则选择标志位于第一行，本例选取。4. a :显著性水平，一般输入 0.05，即95%的置信度。3-1的数据进行分析时3-3所示。1213绢觇测数求和平均nLEVEL】42265. 55& 0. 0003&715LEVEL242匸5g5.S35C. 0003(>7BLEVELS421. &2C.40E；C.

13、0003G717lEVELd422. EG. 0003G7卯lEVKLS426.462” 34ni图3-25.输出选项：按需求选择适当的分析结果存储位置。 按图3-2输入选项后，对图3-1的数据分析的结果如图 百方分析：单因素旌分析U132.)2122232425216ESafK£FP -valueF crit妲间5.6E1SS4 9129? 2. 036344 0,140eSl御内6. T24T1 5 0. 44E31 器a计110* 37&38id方苣分析丨图3-4图3-7双因素无重复试验方差分析如图3-4所示:与单因素方差分析类似，在分析前需将试验数据按一定的格式输入工

14、作表中,图 数据输入完成以后，出现如图3-5的对话框，对话框的内容如下：1. 输入区域：选择数据所在区域，可以包含因素水平标志。2. 果数据输入时选择了因素水平标志，请选择标志按钮。3. 显著性水平a:根据实际情况输入，一般选择4. 输出选项：按需要选择分析结果存储的位置。3-4操作工具数据分析，选择数据分析工具库中的双因素无重复方差分析,0.05。A ucb-/rIPliiiFZF.4FF/Al15S 12LF3.别156.2L5'j. r.rITT.SB0iS.1S2. 3：址山药ISS. E4L码一陡ITC. 3S4曲丘気on151 J：nv.r2kiO.l：ir 12Flee.

15、 4F曲EJLb&. 231%. 3S£152. hCI1££L 34h 1 話.23ITC. 227&图3-5分析图3-4中的数据，对话框如图3-5输入，分析结果输出如图3-6:求和平均 方芽AL5£02. 9Q1&0.EB2 9<r. hJ893A卫&S虬6160= 93VL冗丽前b£03.161.752ei.T21l2AdEeod. 14160.£23S.5I2E2A5&EM* 511W.?D2S£. IIIZSFlETF-I.IJO.tQPtaE2fTSO, 11156.

16、022< &汨旣1357?2.615S- 530, 326S5fQ5EOO.OGIW.0D4Q. W1335ES5.0(8177.0160. 1366SSLTMARf 聊測翹=2L-卑nw. Eg nrt.典3：32,怒SE ,驻0曲4 l*T?6.19b le.4415S1792. lecSSP-valy4 L.-ifcV-iD6. J 4呂羽 O.Zf 药閉 3. WbJrlT4 艶立洞肝 432,11£2 4.4?£-ie S. W691716 1.0(2724双因素可重复方差分析双因素可重复方差分析与双因素无重复方差分析数据输入的区别在于对重复试验数据

17、的处 理，如图3-7所示，就是将重复试验的数据叠加起来。AB1 c1D1 E1F1E-1EQE，52Al153.12153, 64158. 921E9. 65177.583153. 16153. G615S. 36159. 5517&. 94153. IE153. 6S15E 68159. 40173.345A2152. 34154. 36158. 641E9.176. 5S6152. 56154. OO15S.&21E3. 6S17S. 107152. 69154. 5215S. 39153. 69176. 208A3153. 08159, 32159.12160.12177

18、.12915久 02159. 33156.3E158. 3917S. 5呂Fo1 灵* 8S15乩 35157.58159. eo175.3611A4152, 89156, 45157.&9160. 23176. SS12152* 9815h 2315氐 54160, 00175,3513153* 21156. 55158. 6315& 99175. 5514AS152. 6E156. 34IBS. 23160. 34176. 921515N3515 氏 6415乳 3515也 56175, 50161 152. 56156. 54158. 68158. 99174.90171

19、一M M讥仇口化19图3-7数据输入完成以后，操作工具数据分析，选择数据分析工具库中的双因素可重复方差分析， 出现如图3-8的对话框，对话框的内容基本与双因素无重复方差分析相同，区别在于每一样本的行数选项，在此输入重复试验的次数即可。x|鶴入区域：imi.IFJie毎一禅本的药皴迅h|3|0. 05拗出选顶目.亠右-r输出区竦匹：15J宵新工作表爼(£):1r新工作薄I确定取消帮助堪)I3-8若须对图3-7所示数据进行方差分析时，按图3-8的显示输入即可，在输入区域选择数据所在区域及因素水平标志，在每一样本的行数处输入3,即每种组合重复 3次试验，显著性水平选择0.05。在输出选项中

20、可以按照需求选择分析结果储存的位置。选择确定以后分析结果如图3-9所示。iF1A. 1BCDE iF iG22方差分析,可重复双因素分析h23 bsq. zkv24SUMMARYB1B2B3B4B5总计25Al理筑测数333331527:求和459. 46460. 93476. 4647S.&532. S3240S. 3323平均153.1533153.6615&畝159. 5333ITT. 6267160. 5&S729方差0. 0009330. 00040. 01560.0158330.47773335. 320333031A2测和均差 观求平方 2;、.4 5厂厂

21、3 3 3 3-333 457. 59 152.53 0. 03133462. S3154. 29330. 0709333476.15 158. 716? 0. 0226333477. 05 159. 016? 0. 3300333520. S3176. 29330. 064133152402. 55 160.1?76. 42609303941數测和均差-观求平方3458. 93 152. 9933 0.010&3333473473.05159. 3333 157. 63330. 000233 1.9262333478. 61 159. 536? 0.3812333 529. 56 1

22、76.52 0. 399615241S. 2161.213369.10304图4-142cp图3333315459, 03469, 234?2, 864?9, 22529, 232409, 6?153. 0267156.41157.62159.74176. 4267160. 64470. 0272330. 02631. 095?0. 43510. 57663372. 09554數馳和均差4-5 6 tg 4 4- g相关分析与协万差分析第四节EXCEL数据分析工具库中提供了多因素数据的两两对应得相关分析与协方差分析，分析结 果分别为相关系数矩阵和协方差矩阵。相关系数分析在进行分析前先将数据按图

23、 4-1的格式输入EXCEL工作表中，并对输入的数据进行相关分 析和协方差分析。1 A 1B ；CD . E1汽1- J-rw71530,42050, 642570.31. /总金jl530101.163511匚5、740131. 34516L71试验数据1501TL910551913匸了了Ft116020£ 6操作工具数据分析，在出现的数据分析工具栏中选择相关系数，将出现如图4-2的对话框,输入区域：选取图4-1数据表中的绿色和黄色区域，表示标志与数据。 分组方式：根据数据输入的方式选择逐行或逐列，此例选择逐列。 由于数据选择时包含了标志，所以要勾选标志位于第一行。根据需要选择输出

24、的位置。对话框内容如下：.谥AJZKQ：1吕鲍方弍.厳标志位于第一行输出选顶忖输出区城辺：r蔚工作表爼（£）新工件薄电）$口口1 “图4-24-3所示。将信息按图4-2输入对话框后，选择确定，输出结果如图协方差分析数据输入依图4-1例，在数据分析内选择协方差，出现的对话框及输入方法与相关系数分析 相同，输入完成确定后，分析结果如图4-4所示。15ABc16A2CI&. 25irB30. 2531.4918C9-73?5S19、203122图4-4第五节假设检验假设检验是我们常用的数据分析工具，其方法是运用统计工具对设定的H0原假设做出判断，在EXCEL的数据分析

25、工具库中，主要包括F检验、t-检验和Z-检验3种，其中t-检验又根据两个样本间的方差情况分为等、异方差检验。F检验双样本方差分析为了能进行分析，先用随机数发生器在EXCEL工作表内生成两组服从正态分布的数据，注意标志为另外输入，生成的数据如图5-1所示。1_2丄A7S91011IS131415UIS .1921_ A&g. 969977 ICl. 02443 10,119849. 781M110.10959. 93098 5.S1S5039. 922&49 9.94530310. 01349 g.9G7301 ICi. 13425 g. 9813S410. 2375?10.1&

26、amp;&1510. 0538910.191S99 947&2* 9&1S6S5-1B9. 87223210.1276510.173319. 9765829* 391339. 8309579. $022379. 7882079 9595959. 9634519. 929769. 9914729. g 鸵 ejgLO. 086579. 934502扁診于9. 99154810. 0675110, 0?5?6在数据分析工具库中选择 F-检验双样本方差分析， 出现如图 对生成的数据分析操作如下：5-2的对话框，对话框的内容及1.变量1的区域：在数据表中选择第一变量的区域，本例

27、为$A$1:$A$21。2.变量2的区域：在数据表中选择第二变量的区域，本例为 以看出，分析对象的两个变量可以具有不同的样本量。$B$1:$B$21。从上述选择可3.如果在选择变量1区域和变量2区域时，包含了数据的标志，则选择标志框。4.显著性水平a: 般选择0.05或0.01，本例选择0.05。5.输出选项：根据需要选择分析结果存储的位置。进行上述输入过程后，产生分析结果于相应得工作表位置，如图5-3,可以根据其中的 P值判断，也可以根据 F计算值与临界值的比较来判定，其中若 P值小于0.05或F计算值大于 临界值，则说明两样本的方差存在显著差异，反之则认为两样本间的方差差异不具备统计显著性

28、。图5-2D !EF1F-检验双样本方差分析關hSL. HCiAB平均方羞W1dfFP (?<=£)单尾 卩单尾临界rA娈蜃1的区1(1):iJ娈量£的区域):17掠老gj眉屮口 3；0卫5*i輛出选顶a输出区囲理1：|$D$ 仝aJ10. 0235083919, 9736778420. bl5732i0. 01092009520| 2019119L 44131Y2070. 216473!2.158251601图5-3t-检验双样本等方差或异方差检验t-检验双样本等方差或异方差检验主要检验两个样本的均值差异显著性，其中前者条件为两 个样本的方差相等，后者条件为不相等，

29、其操作流程基本相同， 现仅以双样本等方差分析为例对图5-1产生的数据进行分析。在数据分析数据库中选择t-检验双样本等方差检验，出现如图5-3的对话框，对话框内容及分析的输入内容如下：fo3J费量1的区域（1）. 穽a £的区疫:假设平韵差；7标志©a（A）： foVOE输出选顶金输出区域（ill： r新工住衷坦.r新工作薄5-31.变量1、2区域的输入与F检验相同。2.3.如果在选择数据是包含标志，则选择标志复选框。4.输入显著性水平。假设平均差，若输入 0则原假设为两样本均值无显著差异。按以上操作后输出内容如图5-4。可以根据其中的P值判断，也可以根据t计算值与临界值的比

30、较来判定，其中若 P值小于0.05或t计算值大于临界值，则说明两样本的均值存在显 著差异，反之则认为两样本间的均值差异不具备统计显著性。t-检验：双样本警方差假设A1'B10. 02350839"9. 9736778420. 015739321Ct 010920095觇测值20 li20合并方差a 013329708!啓平均羞附严尸L 3；t StatL 3543512021!P(T<=t)单尾0. 69016融kt单尾临界_1.635954361P gt)双尾0.1303272521；t双尾临界2. 024394147图5-4t-检验双样本平均差检验两个样本的总体方差

31、已知，由于这点的5-5,对话框内容区别于t-检验就是对已双样本平均差检验区别于上述两种检验的特点在于， 局限性，其实际运用意义不大。其功能对话框如图燮量1的匮域.)3d娈量£的匡域:1玉11限设平幽差G):1吏量1的方垂U知)电).1变量£的方差e知)匾H r Siiajl» H H 1 1a®：H n輸出选顼忖输出区域:|W133d1II45eAXI0. 53G. ?S1. G12. 012. 49BX24.朋10. 12H,瑟19. 22&. 641213 nz152.983,514.ri14.524.qIT?0,r7L74g95.£

32、;5Cl.11)CY540. 20549. 50560* 10569. 20沁&0580- 60590. 20599- 60610. 00620,10呼UP I.知两个样本方差的输入。2Sl'盂-fefe=嚴#*辛沟差檯誉Er斷工性恚追倉): 广新工件薄)图5-5 第十节回归与规划求解回归分析在试验设计数据处理有非常重要的作用，例如正交设计、均匀设计、配方设计、复合设计都需要通过回归分析来寻找因素与响应变量间的关系，而EXCEL的数据分析工具库中就提供了回归分析的工具。通过回归分析，一般会得到因素与响应变量间的拟合方程，那么怎样根据拟合方程来确定最优试验条件呢？这时就可以使用数

33、据分析工具库中的规划求 解工具。回归分析在进行回归分析前，须将试验结果数据按规定的格式输入EXCEL表格中。现假设为了解某指标丫与因素XI、X2之间的影响关系，进行了10次试验，试验数据结果输入EXCEL表中如图10-1。图 10-1试验数据输入完成后，在数据分析工具库中选择回归，出现如图 容如下：10-2的对话框，对话框内Y值输入区域;K值输入区域:输出选项席輸出区域（W'r新工作震怕迟：新工作薄迪炸JI |1| 残S'（7残差葩R标碓炭丢正态分布啟正态很率图迸）JWLT0-2Y值输入区域：选择响应变量试验结果数据所在的区域，可以包含标志。X值输入区域：选择因素

34、取值数据所在的区域，可以包含标志。如果选择数据时包含了标志则选择标志复选框。4.5.置信度：分析置信度，一般选择95%。6.输出选项：根据需要选择分析结果输出的位置。如果强制拟合线通过坐标系原点则选择常数为零复选框。7.残差选项：根据需要可选择分析结果中包含残差和标准残差以及残差图与线形拟合图。如果希望输出正态概率图则选择响应得复选框。为了分析图10-1中的数据，按图10-2的内容输入对话框中，分析的数据结果如图10-3>10-4, 图形结果如图10-5、10-6所示。17 SUMIARY OUTPir诃ig '2C212223S42L_方差if斫27 -2S回归井忻总廿Mult

35、iple R Square A<iji4Stt4 标淮溟差 观测值0. goe号药Q9931820+ g如 234Z 4224910ssMSF血fican辱 F33Intcrcepi532, 551L 73672 306, &417 1弧T5 52&拥43 536. &577 5器 4制3 536. I&ET7创XIIQ畀66q&15.51W 0. 693S63 0, 5101315. 3247 47.,W62 吃乩 9247 47. 4S7623EX20,634191L 531727 0. 414037 0. 691239-2. 9377 4.

36、2丽 149 -2- 93777 4. 256149图 10-33940RESIDUAL DUTPUT肛啊仁 /-xf/. neiPROBABILITYOLfTPUT41观测值預测¥戟菱标淮窸百分比甫诧Y421541.4155-1.21547 -0. 55S92 &540.243q Z_i549.52010.02011 -0. 00941155*3. 5前3E59.321?0.773271 0.36428425560,14566.2462.375367 1,1110343客569. 2.165575.6201Z 9'Y9S96 1.39179545578. 6U7&#

37、163;583.7939-3. 19302 -k 43497E&ESO. 6487592.3677-2.66769 -1. 24866655?0. 249SAAl. Prl A-1”41酌9 T.虽列a?只即久A50960-9； 33710.112硏6 0.05関34E56105110 617. 3332£ 26&77S 1.06100735620.1CgFFixi亡IT 标准供差 t Set P-vftl吐亡 Lwer q茨Upper 951t卜限弼* QlhfR 95, Ot图 10-4XI l?csic±ual PjDtX2 Kcsidua ?lot3U31322 59E4. C13 2932. 015 509* 345 2. 62SH337 4L 07939 5. S&34859 6025.1 曲番0 -0. I：cs2, m j”p *4一 fe 百;G& 矗詁0图 10-5曹（J ,*_±_4 二，_«!