使用EXCEL进行统计分析

#-第五节假设检验假设检验是我们常用的数据分析工具，其方法是运用统计工具对设定的H0原假设做出判断,在EXCEL的数据分析工具库中，主要包括F检验、t-检验和z-检验3种，其中t-检验又根据两个样本间的方差情况分为等、异方差检验。F检验双样本方差分析为了能进行分析，先用随机数发生器在EXCEL工作表内生成两组服从正态分布的数据，注意标志为另外输入，生成的数据如图5-1所示。12345§712345§7Sg10111213141516171819'20215-1S7223210..12765'173319.9765829.矚懐9.83035729022379.7SS2079.9595959,9634519.9629769.99147294S679086579.934509.9^.9.977024431CJ.119349.73164110,10959.930箔9.3153099.922&49943.208013499.9ST30110.134269S15S410J9T22fe發锡1两5辭10.0538-9W.S10,191S9Q9915489.94T6210.067519.961&810.07576数据在数据分析工具库中选择F-检验双样本方差分析，出现如图5-2的对话框，对话框的内容及对生成的数据分析操作如下：变量1的区域：在数据表中选择第一变量的区域，本例^$A$1:$A$21o变量2的区域：在数据表中选择第二变量的区域，本例^$B$1:$B$21o从上述选择可以看出，分析对象的两个变量可以具有不同的样本量。如果在选择变量1区域和变量2区域时，包含了数据的标志，则选择标志框。显著性水平a：—般选择0.05或0.01，本例选择0.05o

输出选项：根据需要选择分析结果存储的位置。进行上述输入过程后，产生分析结果于相应得工作表位置，如图5-3,可以根据其中的P值判断，也可以根据F计算值与临界值的比较来判定，其中若P值小于0.05或F计算值大于临界值，则说明两样本的方差存在显著差异，反之则认为两样本间的方差差异不具备统计显著性。图5-2DEFF书验双样本方差分析int一fi切一npfAB平均10.028508399.973677842止差0.01573.9321£010920095观测值2020df1919卩1.441317207P［氏壬）单尾■0.216472883F单尾临界2.168251601图5-3t-检验双样本等方差或异方差检验t-检验双样本等方差或异方差检验主要检验两个样本的均值差异显著性，其中前者条件为两个样本的方差相等，后者条件为不相等，其操作流程基本相同，现仅以双样本等方差分析为例对图5-1产生的数据进行分析。在数据分析数据库中选择t-检验双样本等方差检验，出现如图5-3的对话框，对话框内容及分析的输入内容如下：

图5-3变量1、2区域的输入与F检验相同。假设平均差，若输入0则原假设为两样本均值无显著差异。如果在选择数据是包含标志，则选择标志复选框。输入显著性水平。按以上操作后输出内容如图5-4。可以根据其中的P值判断，也可以根据t计算值与临界值的比较来判定，其中若P值小于0.05或t计算值大于临界值，则说明两样本的均值存在显著差异，反之则认为两样本间的均值差异不具备统计显著性。t-检脸：双样本等方差假设IAB平均10.023508399.973677S42、~r>0.0157393210+010920095规测值2020合并方差0.0133.29708假设平均董^金门0dfWWW,osq,nvt3呂tStat1.364851202Pgt）单尾0.090163626t单尾临界1.685954461P(K-t)双尾0.180327252t双尾临界2.024394147图5-4t-检验双样本平均差检验双样本平均差检验区别于上述两种检验的特点在于，两个样本的总体方差已知，由于这点的局限性，其实际运用意义不大。其功能对话框如图5-5，对话框内容区别于t-检验就是对已

知两个样本方差的输入。图5-5知两个样本方差的输入。图5-5第十节回归与规划求解回归分析在试验设计数据处理有非常重要的作用，例如正交设计、均匀设计、配方设计、复合设计都需要通过回归分析来寻找因素与响应变量间的关系，而EXCEL的数据分析工具库中就提供了回归分析的工具。通过回归分析，一般会得到因素与响应变量间的拟合方程，那么怎样根据拟合方程来确定最优试验条件呢？这时就可以使用数据分析工具库中的规划求解工具。回归分析在进行回归分析前，须将试验结果数据按规定的格式输入EXCEL表格中。现假设为了解某指标Y与因素XI、X2之间的影响关系，进行了10次试验，试验数据结果输入EXCEL表中如图10-1。ABCXI■遜YABCXI■遜Y20L534.98540.2030.洱10.12549.504［攻1.14.S3560.1052.,0119.92569.206監49阪$4沁6072.鉉热21580.60S3.51瓯.52590,2094.0139.33599・60104.52$5.21610.00620,10咱应变量与试验数据1214因素标志与取值数据15图10-1试验数据输入完成后，在数据分析工具库中选择回归，出现如图10-2的对话框，对话框内容如下：图10-2Y值输入区域：选择响应变量试验结果数据所在的区域，可以包含标志。X值输入区域：选择因素取值数据所在的区域，可以包含标志。•如果选择数据时包含了标志则选择标志复选框。如果强制拟合线通过坐标系原点则选择常数为零复选框。置信度：分析置信度，一般选择95%。输出选项：根据需要选择分析结果输出的位置。残差选项：根据需要可选择分析结果中包含残差和标准残差以及残差图与线形拟合图。如果希望输出正态概率图则选择响应得复选框。为了分析图10-1中的数据，按图10-2的内容输入对话框中，分析的数据结果如图10-3.10-4,图形结果如图10-5、10-6所示。

HSmiARYOUTPUTIS19回归统计^ollultipUo.9965S521RSquare0,9^3182liljAdjusted0+9912346sq・net23标准误差6sq・net24观测冶1025………2fi方差分析27dfSS匪.F粗ificanuEF药•回归分析259昶+032392.U155卿■别转2”E烈-朋.毋.残釜741.079395.8684853U.总计卫戈竝©.更於.翼「]~.32~Coefficien-#^i^tStatP-valueLower^SWper^5%F^^5.Otjf55.0%Intercept532.5511.73672306.64171.04E-15528.4443536.6577528.4443536.6577XI10.7664615.51&70.693B630.510131-25.924747.45762-25.924747.45T62X2Ck适勿91匚酣1?即Cl4140刃0.691,239-09^774.2旺1的-09刖門丄2血图10-3b-J40RESIDUALOUTPUTwww,6PROBABILITY0UTPU741观洌值预测Y残差标准罢差百分比擁临Y421541.4155-1.21547-0.568925540.2432549.5201-0.02011-0.009411&別9.5443559.32170.7782710.364284255&0.14545^.8Z462,3755671.1UE3435553.2465575.6201N9798961.33479545578,647e5S3.7939-3.19392-1.494975B师6457592.8577-2.6&7&9-1.24E56655&0.249S601.01541595-0.fiG27E75E的飞50g60^.837161123760+052S34铳6105110617,8332Z2667781.06100796620.1H'J们*;650.CO•YEgW…八",・■L■预澜丫H'J们*;650.CO•YEgW…八",・■L■预澜丫550100帥D.M■--rhnI1!X2LineritPlq-tXILinePitPlat650.00T&0G.COa55Q』U&00.DO・**a*iaj*e解r.K00J••4*iie■：0*1.00£00乱00*4.005.DCto*10.002D.0030^0(I50.0060.P0-5XIResidualFlatX2ResidualPlotrX2讓0DJ图10-5NormalFrobabi1ityPlot&50r&00一++♦***TOC\o"1-5"\h\z：：TXTT550,**5001111020406080100www.6s(j,netSainplePezcen图10-6规划求解假设现经试验设计及数据分析后得出，某试验指标Y与变量XI、X2之间的回归拟合方程为Y=150+20X1+30X2-46X1*X2-13X12-35X22,且拟合很好，现需确定最优的试验条件。根据要求用极值法确定最优条件，可得方程组如下：20-46X2-26X1=030-46X1-70X2=0首先在EXCEL工作表中设计输入如图10-7的内容，其中C3=B3，B2=20-46*D3-26*D2,B3==30-46*D2-70*D3,D2、D3为求解值放置的单元格。其中C3=B3的意义是选择B3中ABCD1因素方程目标函数求解2XI203030的公式为目标函数，另外的函数为约束条件。图10-7选择工具菜单栏内的规划求解，出现如图10-8的对话框，对话框内容为:1.设置目标单元格，此例选择C3,并选择条件，本例为等于0。2•可变单元格：放置解得单元格。约束：编辑求解约束，单击添加，出现如图10-9的对话框，在对话框内可输入约束条件,输入后选择确定。可根据需要选择选项按钮，可对多项参数予以设定。图10-8

约束值net単元格引用位昼约束值net!i.~.碼定|图10-9根据示例数据将各项输入对话框后确定，将出现求解结果如图10-10及敏感性报告结果如图10-11。ABCD1方程目标函数2XI-1E-06.—3-1E-06-1E-060-472^图10-10AlBCD