使用EXCEL进行统计分析

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

2、，另外还有两个功能就是数据分析和规划求解，但这两个工具必须加载相应的宏后才能使用，操作步骤为工具加载宏，出现如图1-11-1 的对话框，选择分析工具库和规划求解，点击确定后，在工具菜单栏内出现了这两个分析工具。第二节随机数据与基本分析在上一节通过加载宏在工具栏内增加了两个数据分析库，数据分析与规划求解，在本节将介绍数据分析工具库中的随机数生成器和描述统计。操作工具数据分析，出现如图2-12-1 的对话框，对话框包含了方差分析、相关系数、协方差分析等十几种分析工具，但一般处理对象为简单的数据。数据分析狩靳工恒(A)专：圭君丄/1听tfrtfr析分分丢因因素复豈一：- -EJLrrEJLrrl-E

3、l-ErTIrTI4.-34.-34 4卑可无俾和叶分析直方图vwwt6su. nelzJ2J图 1-11-1A图 2-12-1A在对话框内选择随机数发生器，将出现如图 2-22-2 的对话框，对话框提供了相应的随机数产生及存储的条件命令，具体如下：图 2-22-21 1 变量个数：需要形成几组数据。2.2. 随机数个数：每组数据包含的数据个数。3.3. 分布：选择随机形成的数据期望服从的概率分布类型，包括均匀分布、正态分布、二项 分布、离散分布等。4.4. 参数：输入不同分布的参数，如正态分布要求提供平均值及标准偏差。5.5. 随机数基数：输入随机数的基数。6 6输出选项：选择输出区域将使随

4、机数据在同一工作表的制定位置放置，选择新工作表组 将使随机数据放置在同一工作薄的新工作表中，选择新工作薄将生成一个新的工作薄来存储数据或分析结果，以上选择将出现在后续的各种分析工具对话框中，操作意义相同。操作工具数据分析，在对话框内选择描述统计，将出现如图2-32-3 的对话框，对话框的内容解释如下：图 2-32-31 1.输入区域：输入数据所在的区域，可以包含数据的标志，注意分析的数据单个总体应包 含在一列或一栏中。A2 2分组方式：提供了两种选择，如果单个数据总体在同一行内，请选择逐行，如果在列中， 则选择逐列。3 3 在选取数据区域时如果选择了数据标志，请选择标志位于第一行。4 4输出选

5、项：前三项内容如前所述，后四项选择内容供选择数据分析的内容。示例，用随机数发生器生成 3 3 组正态数据，每组数据容量为2020,数据的平均值为 5 5,标准偏差为 0.20.2,并对形成的数据作描述性统计。选择数据分析工具库的随机数发生器，在对话框中变量个数栏输入3 3,在随机数个数栏选择2020,分布类型下拉表中选择正态分布，参数平均只栏输入5 5，标准偏差栏输入 0.20.2,随机数基数输入 0 0,结果如图 2-42-4 ,确定后再工作表中输出结果如图2-52-5 ,其中的列标志为提前输入。图 2-42-4CDHF1隨机数括1陡林数花2附幽据324. 9399536 4. 714463

6、45 048851S3艮25529475. 239675. 346626(514. 5632825 4. 95316385.210045S4. 752659? 4. 85L9S924.6S1913E64.630517S 4. 8044414,245258671576413E 4. 3S64L54.幻91刃530269705 4. 92690144. 934601934* 9253519 5. 25852834. 9829431W4. 9627&85 4. 8J735B55. 39444245.1731346 5. 47513094. 8640187V12 5.3322912 4. 67

7、752055,1077897再5.1804383 5. 3838314,$33066XU4. 895211 5. 102774. 9237352丽产生極据115 5.1513223 4. 71116274. 8305525IFM7K V -L.Ml. -一_-1- - - 蒐4. 6955858 4. 32742464.935042q riei17乩UUb卫 理4.匕41；塾|1& 1 6515035 4. S527CU4.4844835115.289534 4.旳4。4罚4. 36928?|5L1515427 5. 09334245. H49215521乩11911341.72563

8、4.TT68E2322图 2-52-5选择数据分析工具库中的描述统计对上述形成的数据进行分析，在对话框中输入区域选择随机数据所在的表格区域，分组方式按要求为逐列，因为包含标志所以选择标志位于第一行， 选择输出位置及汇总统计，均值置信度为 95%95%及最大和最小值。 选择如图 2-62-6 所示，单击确定，输出如图 2-72-7 的分析结果，注意其中的众数由于不存在而显示为#N/A#N/A。A图 2-62-6FGHIJK随机数据1随机数据2随机数据3平均4. 965479平均4. 962208平均4. 990251标推误差0.0. U54S72U54S72标蔺吴差0.0. 0516530516

9、53标推误差0.0. 053632053632中位数4. 984196中位数4. 91213中位数4. 95S772处数处数#N/A丈数#N/A标准差0, 245843标淮差0.231001标准差0.239848方差0. 060439方差Q. 053361方差0.057527峰度-1.14695-0, 093320.131198扁度-0.29599偏度Q.889473fi 度0.151346区域0. 769009区域0. 79761区域0.954416最木值 563282最小值4. 67752最小值4.484484最大值5. ?32291最大值5. 475131最大值5.4389亲和99.30

10、958隶和99. 24416耒和99.80501观测数20观测数2020阶忻结皋,最矢5 332291最矢5. 475131最大5.4389L_! r T 1最小4. 563282最小4, 67752最小(1)4. 484484晝信度3E0.1150580.108112置信度（戛0.112252www. Gsq. net-图 2-72-7第三节方差分析在数据分析工具库中提供了 3 3 种基本类型的方差分析：单因素方差分析、双因素无重复试验 和可重复试验的方差分析，本节将分别介绍这三种方差分析的应用：单因素方差分析在进行单因素方差分析之前，须先将试验所得的数据按一定的格式输入到工作表中，其中每种

11、水平的试验数据可以放在一行或一列内，具体的格式如图3-13-1，图中每个水平的试验数据结果放在同一行内。A图 3-3-3 3图 3-13-1数据输入完成以后，操作工具数据分析，选择数据分析工具对话框内的单因素方差分析，出现如图 3-23-2 的对话框，对话框的内容如下：1 1 输入区域：选择分析数据所在区域，可以选择水平标志，针对图3-13-1 的数据进行分析时选取（绿色）和黄色区域。2 2分组方式：提供列与行的选择，当同一水平的数据位于同一行时选择行，位于同一列时 选择列，本例选择行。3.3.如果在选取数据时包含了水平标志，则选择标志位于第一行，本例选取。4.4.a:显著性水平，一般输入0.

12、050.05，即 95%95%的置信度。图 3-23-25 5.输出选项：按需求选择适当的分析结果存储位置。按图 3-23-2 输入选项后，对图 3-13-1 的数据分析的结果如图3-33-3 所示。n方苣分忻：单因素方差分析1112IT绡涯测数求和平均nLEVEL!422. 26反&5o. ooos&r15LEVEL2423*585-895C.000367IS LEVEL3421. &2&. 4弱0. 00036F/17 LEVEL4422, 50. 0003&7/1气LEVELS426,4ftL 615Z 24012DHww. bsq. net212

13、223方苣分析S3, 65168df40. 91292F2. 036344P-value0.140681F crit24组内6. 724T150. 448313方2；总计10763819LEVEL!&,fcEVEL.2S+92LEVEL；5.LEVEL4S.&4LEVELFB.S83fT34A图 3-3-6 6双因素无重复试验方差分析与单因素方差分析类似，在分析前需将试验数据按一定的格式输入工作表中，如图 3-43-4 所示:图 3-43-4数据输入完成以后，操作工具数据分析，选择数据分析工具库中的双因素无重复方差分析, 出现如图 3-3-5 5的对话框，对话框的内容如下：1.

14、1.输入区域：选择数据所在区域，可以包含因素水平标志。2 2 果数据输入时选择了因素水平标志，请选择标志按钮。3 3 显著性水平a:根据实际情况输入，一般选择0.050.05。4 4输出选项：按需要选择分析结果存储的位置。图 3-53-5分析图 3-43-4 中的数据，对话框如图3-53-5 输入，分析结果输出如图3-63-6:SSd15? Paue J CTLi乩 沱9讯4&一樂也此1. .ULU-I?17ft MS4勺灶0胡T船氏1125 4.4?J-15 316&691T1电上41強16 1.0(27624丄二1?11IEir辽K严222蕊21EUMARf规刈衆Tin甲均

15、TtALA2fi3AdE23t-5803.乩1旳.U-決J加2801. 6160.3291. TO刚FEOB. T6161528：. 72112F80i.14160.82387. 512E25M 511W.$O28C.11122C7“阴152,322C. 10312STT9fl)n156.(221 899325阴目15E- 520, 3Z6B580. OE1W. fl 04f. 1 JI 93bK8. 00177.0160.1366S272S文和SLA17 9 B. 46624A双因素可重复方差分析双因素可重复方差分析与双因素无重复方差分析数据输入的区别在于对重复试验数据的处 理， 如图3-3

16、-7 7所示，就是将重复试验的数据叠加起来。图 3-73-7若须对图 3-73-7 所示数据进行方差分析时，按图3-83-8 的显示输入即可，在输入区域选择数据所在区域及因素水平标志，在每一样本的行数处输入3 3,即每种组合重复 3 3 次试验，显著性水ABCDEF1Bl&2阴2Al153.12153. 64158. 92159. 65177. 583153.16153. 66158. S6159. 5517&. 964153.18153. 68158. 681S9. 40173.345A2152. 34154. 36158. 64159. 68176. 5S152.56154

17、. 00158. 52158, 68176.10152/S9154+52158. 89遍69176. 208A3153, 08159. 32159.12160.12177.129153*0215勺*33156. 35153. 89176. 5名10152.8815135157. 58159. 60175.3611M152. 89156, 45157.69160. 23176. 8812152.9815& 2315氐54160. 00175. 8513153.2115乩55158. 63158. 991T5. 5514AS152. &8156. 34IBS.23160. 3417

18、6. 9215152. 35156. 64158.35159. 66175.5016152. 56156.54 1158, 68158; 99174.9017试tenwwnr(isa. ne重复试验1319数据输入完成以后，操作工具数据分析，选择数据分析工具库中的双因素可重复方差分析， 出现如图 3-3-8 8的对话框，对话框的内容基本与双因素无重复方差分析相同，区别在于每一样本的行数选项，在此输入重复试验的次数即可。A平选择 0.050.05。在输出选项中可以按照需求选择分析结果储存的位置。选择确定以后分析结果如图 3-93-9 所示。A图 4-4-1 1第四节相关分析与协方差分析EXCEL

19、EXCEL 数据分析工具库中提供了多因素数据的两两对应得相关分析与协方差分析，分析结 果分别为相关系数矩阵和协方差矩阵。相关系数分析在进行分析前先将数据按图 4-14-1 的格式输入 EXCELEXCEL 工作表中，并对输入的数据进行相关分 析和协方差分析。A22方差分祈t23BCD可車篦双因盍分忻www.bs(总计25262728293025262728293015459.464G0.98476.4647S, 6532.88 2408.38153.1533153. 6615& S2 159. 5333 177. 6267 160. 55町0. 0009330. 00040. 0156

20、 0. 015833 0. 477733 85. 3203833 J氏34 -3333315457.59462. S3476.15477.0552S. S32402.55152. 53 154. 2933 158. 7167 159. 0167 176. 2933160.170. 0313 0. 070933 0. 022633 0. 330033 0. 064133 76. 42609A33339观求10平41h3333315458.98478473.0547S. 61529.&2418,2152. 9933 159. 3333157. 6833 1S9. 5367176.52 16

21、1. 21330. 3995 69.1030442434243A A4 44 4 5 54 4 4 46 6 q q差方3333315匚459.459. 23472. 86479. 22529. 232409. 67153. 0261156.41157.62159.74176. 426?160. 644?0. 0272330. 02681. 09570. 43510. 57663372. 09554AB1AB153205425753D1063511740134516501710551911602031观测数35方差3637数测和均差图 3-93-9A图 4-4-4 4操作工具数据分析，在出现的

22、数据分析工具栏中选择相关系数，将出现如图 4-24-2 的对话框,对话框内容如下：1.1.输入区域：选取图 4-14-1 数据表中的绿色和黄色区域，表示标志与数据。2.2.分组方式：根据数据输入的方式选择逐行或逐列，此例选择逐列。3.3.由于数据选择时包含了标志，所以要勾选标志位于第一行。4.4.根据需要选择输出的位置。图 4-24-2将信息按图 4-24-2 输入对话框后，选择确定，输出结果如图4-34-3 所示。协方差分析数据输入依图 4-14-1 例，在数据分析内选择协方差，出现的对话框及输入方法与相关系数分析 相同，输入完成确定后，分析结果如图4-44-4 所示。1516ABCA206

23、. 2517B30. 2531, 4918C9,丁3.7580,46961920b21tQr f i-血方差矩阵|22A4 4.显著性水平a:般选择 0.050.05 或 0.010.01，本例选择 0.050.05。第五节假设检验假设检验是我们常用的数据分析工具，其方法是运用统计工具对设定的H0H0 原假设做出判断，在 EXCELEXCEL 的数据分析工具库中，主要包括F F 检验、t-t-检验和 z-z-检验 3 3 种，其中 t-t-检验又根据两个样本间的方差情况分为等、异方差检验。F F 检验双样本方差分析为了能进行分析，先用随机数发生器在EXCELEXCEL 工作表内生成两组服从正态

24、分布的数据，注意标志为另外输入，生成的数据如图5-15-1 所示。AB TI触B9. 9G9977 9. 87223210. 02443 10.12765 101,11984 10,173319* 781M1 9. 976582烂一5-15-1在数据分析工具库中选择F-F-检验双样本方差分析，出现如图 5-25-2 的对话框，对话框的内容及对生成的数据分析操作如下：1.1.变量 1 1 的区域：在数据表中选择第一变量的区域，本例为$A$1:$A$21$A$1:$A$21。2.2.变量 2 2 的区域：在数据表中选择第二变量的区域，本例为$B$1:$B$21$B$1:$B$21。 从上述选择可

25、以看出，分析对象的两个变量可以具有不同的样本量。123410,10959.930989101112131415159. 922&499, 5432081CJ. 013499.9675011CL 13426g. 98132410.1972210, 237579. 891339. 8309579. 9022379. 7882079. 9595959. 9634519. 9G29769. 9914729. 94S67910. 086579. 934509171819202110. 05389W. 191899. 947629. 9G1868就硝驚卩net9, 99154810. 067511

26、0. Q7576A3.3.如果在选择变量 1 1 区域和变量 2 2 区域时，包含了数据的标志，则选择标志框。A5.5.输出选项：根据需要选择分析结果存储的位置。进行上述输入过程后，产生分析结果于相应得工作表位置，如图5-35-3,可以根据其中的 P P 值判断，也可以根据 F F 计算值与临界值的比较来判定，其中若 P P 值小于 0.050.05 或 F F 计算值大于 临界值，则说明两样本的方差存在显著差异，反之则认为两样本间的方差差异不具备统计显著性。图 5-25-2DE_ F _ 卩卡验双样本方差分析 仁AB10. 023508399,. 973677842方差0, 01573932

27、10. 010920095观测值20201919L 441317207P(F=f)单尾0. 216472883卩单尾临束2.168251601图 5-35-3t-t-检验双样本等方差或异方差检验t-t-检验双样本等方差或异方差检验主要检验两个样本的均值差异显著性，其中前者条件为两个样本的方差相等，后者条件为不相等，其操作流程基本相同，现仅以双样本等方差分析为例对图 5-15-1 产生的数据进行分析。在数据分析数据库中选择 t-t-检验双样本等方差检验，出现如图5-35-3 的对话框，对话框内容及分析的输入内容如下：A图 10-110-1图 5-35-31.1.变量 1 1、2 2 区域的输入与

28、 F F 检验相同。2.2.假设平均差，若输入 0 0 则原假设为两样本均值无显著差异。3.3.如果在选择数据是包含标志，则选择标志复选框。4.4.输入显著性水平。按以上操作后输出内容如图5-45-4。可以根据其中的 P P 值判断，也可以根据 t t 计算值与临界值的比较来判定，其中若P P 值小于 0.050.05 或 t t 计算值大于临界值，则说明两样本的均值存在显 著差异，反之则认为两样本间的均值差异不具备统计显著性。亡-检验：双样本等方差假设t单尾临界P(T=t)双尾t双尾临界10. 023508390. 0157393211. 3648512020. 0901636261.685

29、954461Q.2. 024394147B9. 9736778426. 01092009520图 5-45-4t-t-检验双样本平均差检验双样本平均差检验区别于上述两种检验的特点在于，两个样本的总体方差已知， 由于这点的局限性，其实际运用意义不大。其功能对话框如图5-55-5,对话框内容区别于 t-t-检验就是对已差单差均)值方平atat=t=t均差测并设ststTT. 00620,10XIX20.53融9810.121* G1烛辭2. 0119.2. 49642.9830.213.5135.524.0139.034. 5245.214. 9750.If因素柝韶与取值数据 I第十节回归与规划求

30、解回归分析在试验设计数据处理有非常重要的作用，例如正交设计、均匀设计、配方设计、复合设计都需要通过回归分析来寻找因素与响应变量间的关系，而 EXCELEXCEL 的数据分析工具库中就提供了回归分析的工具。通过回归分析，一般会得到因素与响应变量间的拟合方程，那么怎样根据拟合方程来确定最优试验条件呢？这时就可以使用数据分析工具库中的规划求 解工具。 回归分析在进行回归分析前，须将试验结果数据按规定的格式输入EXCELEXCEL 表格中。现假设为了解某指标 Y Y 与因素 XIXI、X2X2 之间的影响关系，进行了1010 次试验，试验数据结果输入中如图 10-110-1。EXCELEXCEL 表知

31、两个样本方差的输入。图 5-55-5A图 10-110-11.1.Y Y 值输入区域：选择响应变量试验结果数据所在的区域，可以包含标志。2.2.X X 值输入区域：选择因素取值数据所在的区域，可以包含标志。试验数据输入完成后，在数据分析工具库中选择回归，出现如图 容如下：10-210-2 的对话框，对话框内图 10-210-2A3.3.如果选择数据时包含了标志则选择标志复选框。4.4.如果强制拟合线通过坐标系原点则选择常数为零复选框。5.5.置信度：分析置信度，一般选择95%95%。6.6.输出选项：根据需要选择分析结果输出的位置。7.7.残差选项：根据需要可选择分析结果中包含残差和标准残差以

32、及残差图与线形拟合图。& & 如果希望输出正态概率图则选择响应得复选框。为了分析图 10-110-1 中的数据，按图 10-210-2 的内容输入对话框中，分析的数据结果如图 10-310-410-310-4, 图形结果如图10-510-5、10-610-6 所示。2 2 2 2 2 2 2 2 2 2g CO 7 G 5 4 3 J O i O3 3 3 3 3 35 4 3 2 1 UH.MJI回曲泮茸MulHplpg嫖WOO5RSQUBep993182出岳宀瓷p9912爸茅陆湎隐N海圮接笛堂血$戶FJlKic 号评 f回沱力皐W59蛊sMgaz.015509L445卩6芻

33、專T41-生gwgW&84OO5施Vt96R5-10gC畐Ri巧ierrtllaMt宀即籐P4巴灯nl 窘畀glsuppe glaFa95*0thf河资0% 3t黑盘282,52rg23x64171畐15冒444330S-JT800448Sr65T-J slp7626 10J23p2超a051231沖冒務43SLa窈乌4-457S看pGHlglL53170-411370-623gIM-s7产bj5614g12gg-JT7 40-3444444 4 44g T 6 JI 4 3 2 1 OREFLJALulTTPUT两_ xOi4*Jm6W2Ea苣 T常前潇將酬154L4155丄凶 54

34、 -0 56ggzV54 产5 啓一右誇 011 占00941358&217 戶骂 8271P3S2S4 曇黑46护咼栄1.111COM45 75鬻01皆gT9oclg61.7 曙豊矗S7SJr 240066MRflLniRI兰REgsgMOQd0-1120076OO5L-JM生s 613器1S13Tg oo F62O 6* Kb1 oPRUBABILT7UUTFSO1g N:rJJ4tocno二j*h*frVI p c50a8 ”2P寻tft PQiO6h-1*|1屮w-dX X2 2TE.-M-TE.-M-邑丄MW；65S/7边；xlrg FT phr*ELinemPOTsp呂1sps