Excel 数据处理

Excel数据处理六西格玛管理方法给我们的组织带来了管理上新的认识，其中包含了如方差分析、假设检验、回归分析、基本统计等大量的数理工具，但问题也随之而来，那就是大量的数据处理，这时我们就会想到用软件来帮助完成大量的运算。现在应用于这一方面的软件很多，如MINITAB、SPSS、SAS等，但是大多为英文软件，在这里我要介绍的就是用EXCEL的数据分析与规划求解来帮助您进行简单的数据分析。EXCEL的数据分析工具为中文界面，操作简单，在一定程度上能给予大家帮助。6sigma品质网6Fj)fd/bb!Q4F7Ie3q#D 第一节 加载宏0T QC f(J|W EXCEL的数据处理除了提供了很多的函数外，另外还有两个功能就是数据分析和规划求解，但这两个工具必须加载相应的宏后才能使用，操作步骤为工具加载宏，出现如图1-1的对话框，选择分析工具库和规划求解，点击确定后，在工具菜单栏内出现了这两个分析工具。6sigma品质网&N:|Y.JjMX8c8QWa2:X 图 1-1第二节 随机数据与基本分析 zmU%U0Bk 1ns 在上一节通过加载宏在工具栏内增加了两个数据分析库，数据分析与规划求解，在本节将介绍数据分析工具库中的随机数生成器和描述统计。6sigma品质网l;z5IZ f F*pC/Bf6sigma品质网eOR/g3-OF ? X-Z%C操作工具数据分析，出现如图2-1的对话框，对话框包含了方差分析、相关系数、协方差分析等十几种分析工具，但一般处理对象为简单的数据。6sigma品质网&tc(Wp+f!Gt UE,J? t 图 2-16sigma品质网y6dmT6of0U在对话框内选择随机数发生器，将出现如图2-2的对话框，对话框提供了相应的随机数产生及存储的条件命令，具体如下：|lSB ba 6sigma品质网 iv4SuRa图 2-2,Q:hpm:F(/ 1变量个数：需要形成几组数据。6sigma品质网cz T$FuP2随机数个数：每组数据包含的数据个数。6sigma品质网Q*HA(o2U$x3分布：选择随机形成的数据期望服从的概率分布类型，包括均匀分布、正态分布、二项分布、离散分布等。6sigma品质网o D)t,TT4参数：输入不同分布的参数，如正态分布要求提供平均值及标准偏差。MweMy?!g 5随机数基数：输入随机数的基数。6sigma品质网 3NPg3e6输出选项：选择输出区域将使随机数据在同一工作表的制定位置放置，选择新工作表组将使随机数据放置在同一工作薄的新工作表中，选择新工作薄将生成一个新的工作薄来存储数据或分析结果，以上选择将出现在后续的各种分析工具对话框中，操作意义相同。6sigma品质网x;I4bkn6sigma品质网9n!|b2g5O&b*G,n操作工具数据分析，在对话框内选择描述统计，将出现如图2-3的对话框，对话框的内容解释如下：2mqi%&Z:Hp l1_+fZ t C B 图 2-3a7C GkDAN_ 1输入区域：输入数据所在的区域，可以包含数据的标志，注意分析的数据单个总体应包含在一列或一栏中。s cAXOc 2分组方式：提供了两种选择，如果单个数据总体在同一行内，请选择逐行，如果在列中，则选择逐列。Ap(B5X 3在选取数据区域时如果选择了数据标志，请选择标志位于第一行。6sigma品质网4UH lk:Ow4输出选项：前三项内容如前所述，后四项选择内容供选择数据分析的内容。gVGe;%S+Il|/h.H +z z7a*?M? b 示例，用随机数发生器生成3组正态数据，每组数据容量为20，数据的平均值为5，标准偏差为0.2，并对形成的数据作描述性统计。6sigma品质网5b| KVf KDE6s r Q选择数据分析工具库的随机数发生器，在对话框中变量个数栏输入3，在随机数个数栏选择20，分布类型下拉表中选择正态分布，参数平均只栏输入5，标准偏差栏输入0.2，随机数基数输入0，结果如图2-4，确定后再工作表中输出结果如图2-5，其中的列标志为提前输入。VOZ7j 8h!SX8Z$|4b 图 2-44U w7D&o H/qu 3|BL vd,mh 图 2-5-M2Hs/t!hQ 选择数据分析工具库中的描述统计对上述形成的数据进行分析，在对话框中输入区域选择随机数据所在的表格区域，分组方式按要求为逐列，因为包含标志所以选择标志位于第一行，选择输出位置及汇总统计，均值置信度为95%及最大和最小值。选择如图2-6所示，单击确定，输出如图2-7的分析结果，注意其中的众数由于不存在而显示为#N/A。oSh#t6X$QZ VvFVFZwB 图 2-6t3H t:i 6sigma品质网M#ka(cUJz ?v图 2-7第三节 方差分析6sigma品质网lc n |A!b3g在数据分析工具库中提供了3种基本类型的方差分析：单因素方差分析、双因素无重复试验和可重复试验的方差分析，本节将分别介绍这三种方差分析的应用：?Ks7hr!P 8xC8r*Or$nQ U 单因素方差分析RQ8xq 在进行单因素方差分析之前，须先将试验所得的数据按一定的格式输入到工作表中，其中每种水平的试验数据可以放在一行或一列内，具体的格式如图3-1，图中每个水平的试验数据结果放在同一行内。6sigma品质网do%c+j)q6sigma品质网!j2U W%p-Z!tXK _e图 3-1f;Ep5L 5KH 数据输入完成以后，操作工具数据分析，选择数据分析工具对话框内的单因素方差分析，出现如图3-2的对话框，对话框的内容如下：6sigma品质网0F7rXM1 输入区域：选择分析数据所在区域，可以选择水平标志，针对图3-1的数据进行分析时选取（绿色）和黄色区域。+v;Y.rCPnCw1w 2 分组方式：提供列与行的选择，当同一水平的数据位于同一行时选择行，位于同一列时选择列，本例选择行。6sigma品质网N.a pQ RD4Y c3 如果在选取数据时包含了水平标志，则选择标志位于第一行，本例选取。*W:L4l5C)kIY 4 ：显著性水平，一般输入0.05，即95%的置信度。WWa(i9.|%Q 0h!%r#hket 图 3-253A$fv!k 5 输出选项：按需求选择适当的分析结果存储位置。6sigma品质网#o1r7Q_+N#sf,x按图3-2输入选项后，对图3-1的数据分析的结果如图3-3所示。;5q0E5G y w$ra QO 图 3-36sigma品质网2QNB P p/|6sigma品质网eT;N1H q3z2g j双因素无重复试验方差分析6sigma品质网8_Qf与单因素方差分析类似，在分析前需将试验数据按一定的格式输入工作表中，如图3-4所示：6sigma品质网*u/ds Elw89od6sigma品质网 S;g/Y!p;Z;xW图 3-4VT,_5zK!za&Z o 数据输入完成以后，操作工具数据分析，选择数据分析工具库中的双因素无重复方差分析，出现如图3-5的对话框，对话框的内容如下：6sigma品质网td gM6s1 输入区域：选择数据所在区域，可以包含因素水平标志。*bhX!W$e*MMD 2 果数据输入时选择了因素水平标志，请选择标志按钮。?p$|)w!YLl6e 3 显著性水平：根据实际情况输入，一般选择0.05。7Us0oY d 4 输出选项：按需要选择分析结果存储的位置。6sigma品质网g3!Xcs8A6sigma品质网2dB)o hY图 3-56sigma品质网$r6a FrR分析图3-4中的数据，对话框如图3-5输入，分析结果输出如图3-6：6sigma品质网 il g P5ObJg, TwZ 图 3-66sigma品质网!M#hQYM F6sigma品质网or-$s|x双因素可重复方差分析?;jJ(VYE 双因素可重复方差分析与双因素无重复方差分析数据输入的区别在于对重复试验数据的处理，如图3-7所示，就是将重复试验的数据叠加起来。2f7a7DU|4O 6sigma品质网2X05V9q!v图 3-76sigma品质网;w1?M(W K G&o数据输入完成以后，操作工具数据分析，选择数据分析工具库中的双因素可重复方差分析，出现如图3-8的对话框，对话框的内容基本与双因素无重复方差分析相同，区别在于每一样本的行数选项，在此输入重复试验的次数即可。6sigma品质网 $R.i?_oi?*z U*jgtBb;v!6z 3-89z+sgFq 若须对图3-7所示数据进行方差分析时，按图3-8的显示输入即可，在输入区域选择数据所在区域及因素水平标志，在每一样本的行数处输入3，即每种组合重复3次试验，显著性水平选择0.05。在输出选项中可以按照需求选择分析结果储存的位置。选择确定以后分析结果如图3-9所示。6sigma品质网T,D8OtA H8N:s6sigma品质网Z8Er5Ir;N:q x+BU0 图 3-9第四节 相关分析与协方差分析6sigma品质网3R4A5Nk&4g6sigma品质网!W;ET5X5J!cEXCEL数据分析工具库中提供了多因素数据的两两对应得相关分析与协方差分析，分析结果分别为相关系数矩阵和协方差矩阵。EGlz1 _j!b*l3F 相关系数分析6sigma品质网+q;U1i/RXm在进行分析前先将数据按图4-1的格式输入EXCEL工作表中，并对输入的数据进行相关分析和协方差分析。XAB d M5Mf 6sigma品质网0ZH*B9G F图 4-16sigma品质网#c9D;Ph MR m操作工具数据分析，在出现的数据分析工具栏中选择相关系数，将出现如图4-2的对话框，对话框内容如下：LDV6UL 1 输入区域：选取图4-1数据表中的绿色和黄色区域，表示标志与数据。6sigma品质网Kr6!q$_ T+c!L2 分组方式：根据数据输入的方式选择逐行或逐列，此例选择逐列。_D|or*Hu 3 由于数据选择时包含了标志，所以要勾选标志位于第一行。&l2C&va8F%U*j+b 4 根据需要选择输出的位置。6sigma品质网#;Rt+(s6sigma品质网49GHkz6A图 4-26sigma品质网8a1;wEs1X/q58R将信息按图4-2输入对话框后，选择确定，输出结果如图4-3所示。x D6H;b9+k 6V E Wsx 图 4-36sigma品质网j+L v k_.PVw9g:b7j-P 协方差分析Pcp5O n C9wy 数据输入依图4-1例，在数据分析内选择协方差，出现的对话框及输入方法与相关系数分析相同，输入完成确定后，分析结果如图4-4所示。g$g#D nD+x5 6sigma品质网%|(R&og2Jn第五节 假设检验z C3ay6D:yIj O8_!sUHq f6G8n*F 假设检验是我们常用的数据分析工具，其方法是运用统计工具对设定的H0原假设做出判断，在EXCEL的数据分析工具库中，主要包括F检验、t-检验和z-检验3种，其中t-检验又根据两个样本间的方差情况分为等、异方差检验。6sigma品质网N,*K.Q8n.5FR#mzj/ F检验 双样本方差分析c(Zdxv/dy X 为了能进行分析，先用随机数发生器在EXCEL工作表内生成两组服从正态分布的数据，注意标志为另外输入，生成的数据如图5-1所示。$g3DD*Xi 6sigma品质网Y,rtr$M u$_QZn5-16sigma品质网q3KJ_n/D e在数据分析工具库中选择F-检验双样本方差分析，出现如图5-2的对话框，对话框的内容及对生成的数据分析操作如下：6sigma品质网 M1y+sis1h k$fU1 变量1的区域：在数据表中选择第一变量的区域，本例为$A$1:$A$21。2G a*mr(XP 2 变量2的区域：在数据表中选择第二变量的区域，本例为$B$1:$B$21。从上述选择可以看出，分析对象的两个变量可以具有不同的样本量。%F:b7c w;4V;vn:c 3 如果在选择变量1区域和变量2区域时，包含了数据的标志，则选择标志框。f)k.X)t3U8LB1F r*Ht 4 显著性水平：一般选择0.05或0.01，本例选择0.05。6sigma品质网 G&Du.AT6AIf-Gw5 输出选项：根据需要选择分析结果存储的位置。t0b7B%zPD+hrR 进行上述输入过程后，产生分析结果于相应得工作表位置，如图5-3，可以根据其中的P值判断，也可以根据F计算值与临界值的比较来判定，其中若P值小于0.05或F计算值大于临界值，则说明两样本的方差存在显著差异，反之则认为两样本间的方差差异不具备统计显著性。;xEcv*IO k -bNp0F4bz*J w M8o 图 5-26sigma品质网9gnJ*l5svg $l(Z? f2b:fyhY 图 5-3&izS7V+MW3T )fY4 q M&ONI t-检验 双样本等方差或异方差检验6sigma品质网X N zxZ wHt-检验双样本等方差或异方差检验主要检验两个样本的均值差异显著性，其中前者条件为两个样本的方差相等，后者条件为不相等，其操作流程基本相同，现仅以双样本等方差分析为例对图5-1产生的数据进行分析。6sigma品质网q#nPx LN在数据分析数据库中选择t-检验双样本等方差检验，出现如图5-3的对话框，对话框内容及分析的输入内容如下：6sigma品质网bD3oP)9l*NY6sigma品质网)uB!wg N#s图 5-3Szp( a f.L 1 变量1、2区域的输入与F检验相同。6sigma品质网.P&V nA5P2 假设平均差，若输入0则原假设为两样本均值无显著差异。,b%AoLd6_/ 3 如果在选择数据是包含标志，则选择标志复选框。6sigma品质网4xkylD4 输入显著性水平。M?5t*m2V&)X 按以上操作后输出内容如图5-4。可以根据其中的P值判断，也可以根据t计算值与临界值的比较来判定，其中若P值小于0.05或t计算值大于临界值，则说明两样本的均值存在显著差异，反之则认为两样本间的均值差异不具备统计显著性。V%*Bfh%l0Bcx 6sigma品质网kl7n0Y t F图 5-4S1 qgm J&D ojrAV(L t-检验 双样本平均差检验P#Qj&O 双样本平均差检验区别于上述两种检验的特点在于，两个样本的总体方差已知，由于这点的局限性，其实际运用意义不大。其功能对话框如图5-5，对话框内容区别于t-检验就是对已知两个样本方差的输入。*1rjv3b.P xsZmMvL 图 5-56sigma品质网k: EIN|6sigma品质网v&T$yrX Cm*y第十节 回归与规划求解6sigma品质网ZG|q,oX#S9u45a/;X 回归分析在试验设计数据处理有非常重要的作用，例如正交设计、均匀设计、配方设计、复合设计都需要通过回归分析来寻找因素与响应变量间的关系，而EXCEL的数据分析工具库中就提供了回归分析的工具。通过回归分析，一般会得到因素与响应变量间的拟合方程，那么怎样根据拟合方程来确定最优试验条件呢？这时就可以使用数据分析工具库中的规划求解工具。#x$W IU eEs 6sigma品质网-Vss1f9f lD/ih-m e回归分析6sigma品质网2(hrQ8qUaG2n在进行回归分析前，须将试验结果数据按规定的格式输入EXCEL表格中。现假设为了解某指标Y与因素X1、X2之间的影响关系，进行了10次试验，试验数据结果输入EXCEL表中如图10-1。6sigma品质网8x7|gf0n t!b?)Y6sigma品质网9ix m1#R CN图 10-1,VK+zj+hF QK 试验数据输入完成后，在数据分析工具库中选择回归，出现如图10-2的对话框，对话框内容如下：ylp8SWS0o3d.A g 6sigma品质网QV+dr-u?Ge3图 10-26sigma品质网fic(l0A.r1 Y值输入区域：选择响应变量试验结果数据所在的区域，可以包含标志。6sigma品质网4F.EDtXFWq2 X值输入区域：选择因素取值数据所在的区域，可以包含标志。.6sigma品质网G8k#w!H U|S53 如果选择数据时包含了标志则选择标志复选框。_+ff0P IZe 4 如果强制拟合线通过坐标系原点则选择常数为零复选框。u+IV8zv4H8A9+Mf 5 置信度：分析置信度，一般选择95%。6sigma品质网pxLj)W6 输出选项：根据需要选择分析结果输出的位置。6sigma品质网 |QEILPd7 残差选项：根据需要可选择分析结果中包含残差和标准残差以及残差图与线形拟合图。v1Z;q6lC&Di 8 如果希望输出正态概率图则选择响应得复选框。U/!G.$r 为了分析图10-1中的数据，按图10-2的内容输入对话框中，分析的数据结果如图10-3、10-4，图形结果如图10-5、10-6所示。&*X(Bb3I/N3/, 6sigma品质网)9Dsts8wk r图 10-36sigma品质网l*GOj b u6sigma品质网UVi#Uw$7pA i4W图 10-46sigma品质网;n$d9F5aSg1lq(z|*V 图 10-52c |h8 6sigma品质网Em/F*q75(h图 10-6Q-z3F$D6Qq 6sigma品质网Acq(d t规划求解NR-_ DF)rN 假设现经试验设计及数据分析后得出，某试验指标Y与变量X1、X2之间的回归拟合方程为Y=150+20X1+30X2-46X1*X2-13X12-35X22，且拟合很好，现需确定最优的试验条件。6sigma品质网2Dx%f9B#m*t根据要求用极值法确定最优条件，可得方程组如下：6sigma品质网$i2A8D)A%x 20-46X2-26X1=05Dde |k-* w*T5Bx djn:b 30-46X1-70X2=06sigma品质网3lqh#A&S首先在EXCEL工作表中设计输入如图10-7的内容，其中C3=B3，B2=20-46*D3-26*D2，B3=30-46*D2-70*D3，D2、D3为求解值放置的单元格。其中C3=B3的意义是选择B3中的公式为目标函数，另外的函数为约束条件。v4G*PR4V;g2i T/(lT+N 图 10-76sigma品质网T0zZ(n.KU%C选择工具菜单栏内的规划求解，出现如图10-8的对话框，对话框内容为：!w*X5X:d$m 1 设置目标单元格，此例选择C3，并选择条件，本例为等于0。6sigma品质网+dv M NabWN2 可变单元格：放置解得单元格。DY9Kja3SL 3 约束：编辑求解约束，单击添加，出现如图10-9的对话框，在对话框内可输入约束条件，输入后选择确定。6sigma品质网N0BHm*ww)q4A4 可根据需要选择选项按钮，可对多项参数予以设定。s%Qm(V ? 6sigma品质网2zkV图 10-8r:w:Y;M ?8h.s*i 6sigma品质网MEDNT7yT!w图 10-96sigma品质网C,Q-x:G-rI(L3?根据示例数据将各项输入对话框后确定，将出现求解结果如图10-10及敏感性报告结果如图10-11。6sigma品质网)l*wu!F.i6E K6sigma品质网Or67KXJ(V:l图 10-10%Q(v)DOi c H B/B.WL 图10-116sigma品质网I:K;vr6HF-y:xE/L w(JE9yw$zt+R$Y_r xZ