EXCEL和SPSS在回归分析、正交试验设计和判别分析中的应用

实验2指导：EXCEL和SPSS在回归分析、正交试验设计和判别分析中的应用实险目的.熟悉EXCEL和SPSS在数据分析中的操作：.使用EXCEL和SPSS进行回归分析、正交试验设计和判别分析。实险内容1.一元线性回归分析例：近年来国家教育部决定将各高校的后勤社会化。某从事饮食业的企业家认为这是一个很好的投资机会，他得到十组高校人数与周边饭店的季销售额的数据资料，并想根据高校的数据决策其投资规模，数据见data.xls的Sheet)1)选择数据区域B2:C11,从“插入”菜单中选择“散点图”。Excel将显示相应散点图。2)选择图上的点，右键菜单，选择添加趋势线，如下图所示：囹1设旁^至列格式(£).•・3)在趋势线选项，将“显示公式”和“显示rM方”选项打勾，如下图:□设置截距⑤）二H显示公式邕）0湿不R平为值国）i••••••・■•・•••••••••♦••••・■・・••••••••・■•・••••••••结果不仅显示散点图的趋势线，还会显示相应公式，即一元线性回归的回归函数，同时显示R平方值，R即相关系数，其绝对值越接近1,表示两组数据的线性相关程度越高。一元线性回归函数描述了两组数据间存在的线性关系，在上述例子中只要知道其它高校的人数即可根据该公式预测大概的季度销售额。而R的大小能够用于度量这种预测的准确度。另外，使用EXCEL自带的函数也能实现一元线性回归：截距函数INTERCEPT功能：利用已知的x值与y值计算回归直线在y轴的截距。语法结构：INTERCEPT(known_y5kno\vn_x§斜率函数SLOPE功能:返回根据known_『s和known_xzs中的数据点拟合的线性回归直线的斜率。语法结构：SLOPE(known_y's/known_x's)相关系数函数RSQ功能:返回根据known_『s和known_x，s中数据点计算得出的相关系数的平方。语法结构：RSQ(known_y's/known_x's)试比较图表法和函数法H算得出的二元线性回归方程是否一致。.多元线性回归分析例：一家房地产评估公司想对某城市的房地产销售价格(y)与地产的评估价值Cxi)和使用面积(x2)建立一个模型，一边对销售价格作出合理的预测。为此收集20栋住宅的房地产评估数据(data.xls的Sheet2)«由于本问题有两个自变量，因此需要使用多元线性回归，需要借助于Excel的数据分析功能。1)点击“数据分析”，跳出回归分析对话框；2)填充应变量y和自变量xl,x2对应的区域和输出区域，如下图:

0®回归3）点击确定后将出现多元线性回归的结果，对于结果（如下图a,b,c）的一些重要项解释如下：回归 0归统计MultipleR0.860941385RSquare0.741220068AdjustedRSquare0.71077537标淮误差1331.729603观测值20图a方差分析dfSSMSFSignificanceF回灯分析286357011.48431785OE.7424.346441.02308E-05残差1730149563.521773503.737总计19116506575图bCoefficients标准误差tStatP-valueLower95%Urper95%下限95.0%上限95.0%1751.911Intercept-285.0090434965.4493559-0.2952086940.77141-2321.9291151751.911-2321.929XVariabl1.5598002740.7829795591.9921340920.062668-0.0921421833.2117427-0.0921423.2117427XVariabl0.314487660.0905036043.4743634020.0028980.1235417490.50543360.12354170.5054336图c图b中的SignificanceF及线性回归的pvalue,该值小于0.05表示此线性回归结果显著，及回归方程可信，房地产的销售价格与地产估价和使用面积是有关

系的。图c中的Coefficients为回归方程的系数，因此，回归结果为y=-285.0094+1.5598x1+03145x2,在使用面积不变的情况下，地产估价每增加1万元,房产销售的平均价格就会提高1.5598万元；在房地产估价不变的条件下，使用面积每增加1平方米，房产销售的平均价格就会提高0.3145元；图a中AdjustedRSquare为调整复测定系数，本例中约为0.71,它表示两个变量xl,x2对导致结果V的贡献，也就是说还有导致结果V的原因中有29%是由除了xl,x2以外的因素造成的。习题：在黄苗提取工艺的研究中，选择了前煮时间、煎煮次数和加水量进行考察,实验数据见data.xls的Sheet3,试对实验数据进行多元线性回归，对结果进行讨论。.logistic回归分析质量性状（qualitativecharacter）是指能观察到而不能直接测量的，只能用文字来描述其特征的性状，如食品颜色、风味等等。这类性状本身不能直接用数值表示，要获得这类性状的数据资料，须对其观察结果作数量化处理。例如：是否抽烟，是否患病。采用线性回归分析显然不太合适，而logistic回归能较好地解决质量性状分析的问题。例：我们有一组数据，45个观测值，四个变量，包括：age（年龄，数值型）：vision（视力状况，分类型，1表示好，0表示有问题）；drive（驾车教育，分类型，1表示参加过驾车教育，0表示没有）和一个分类型输出变量accident（去年是否出过事故，1表示出过事故，0表示没有）。我们的目的就是要考察前三个变量与发生事故的关系。数据见data.xls的Sheets计算优势比优势（odds）是指某影响因素控制在某种水平时，事件发生率与事件不发生率的比值，即P/（l-P）。某影响因素的两个不同水平的优势的比值称为优势比，如某影响因素的一个水平为C1,另一个水平为co,则这两个水平的优势比为：orJJQFR/QF）309/517208/517309/517208/517结果吸烟不吸烟合计食管癌患者309(a)126(b)435非食管癌患者208(c)243(d)451合计517(a+c)369(b+d)886吸烟的优势==1.49非吸烟的优势=霁="2149OR=--=2.870.52OR表示影响因素对事件发生的影响方向和影响能力大小。OQ1表示该因素取值越大，事件发生的概率越大，乂称危险因素。。/?<1表示该因素取值越大，事件发生的概率越小，乂称保护因素。OR=1表示该因素与事件的发生无关。试计算视力和驾车教育对发生事故的优势比。SPSS的安装1）启动虚拟机，并拷贝安装文件至虚拟机中：2）空缺省方式安装SPSS,最后在注册环节点取消即可；3）运行安装文件夹下spss_19_patch_V2目录中的SPSS19crack20100825.exe即可获得破解过的SPSS。SPSS数据的导入在SPSS中导入Excel文件中数据方法如下:点击SPSS中的文件一打开一数据,文件类型选*.xls,*xlsx.选择实验指导文件夹中的data.xls,确定，然后匚作表选Sheet4即可。SPSS用于logistic回归1）数据导入以后，菜单栏上依次点击：分析一回归一二元logistic回归，打开相应对话框对话框。卑事故分析.sau[数据集1]-IB!SPSSStatistics数据编辑器文件⑹结辑但）视困&）收据①）转换①分析®直销®图形⑼实用整序Msa（w）帮助:宅附昌圜小r发生事故年酎1117214431434155_5_175603570428r°579023100刀11□3312045130471405215055161631711310163191432011721170报告描述统计表⑴比较均值领一段或住俣型⑨广火线性侬混合模型凶相关@回归®对数线性模型⑨神经网络分类（D降维度最⑻驻泰数险/包）莎沔①生存函数@多重响应Q）国坡失值分析①..

多直归因①

复杂抽弹但

质量控制（2）匕ROC曲线图⑦..JJ2）将因变量和自变量放入格子的列表里，如图所示，上面的是因变量，下面的是自变量，我们看到这里有三个自变量：年龄、视力、驾车训练。3)点击确定，SPSS将计算得到分析结果，主要观察结果中最后一张表，如图：方程中的变量BS.E.WalsdtSig.Exp(B)EXP(B)&)95%C.l.下限上限步溪产年龄.007.01812917191.007.9711.043视力-1.710,70$5.8721.015.181.045.721驾车训练-1.494.7064.4961,034.224.066.893常量16211.0572.0711.1504.579a.在步膘1中输入的变量:年龄,视力.驾车训练.其中，Sig.值是回归系数的显著性检验p值，可以看出年龄系数对应的p值大ro.o5,不显著因此不将其纳入回归方程方程,而视力和驾车系数显著，常数不用进行显著性检验。因此根据Logistic回归方法可以得出估算发生事故的概率p的公式如下：Log—^―=1.521-1.710X1-1.494X21-P假设有个人，视力没问题但没有受过驾车教育(Xl=l,X2=0),代入方程，有log(p/(l-p))=-0.189,Mp/(l-p)=exp(-0.189)=0.8278,p=0.4529,即我们说这人发生事故的概率为0.4529：乂另一个，视力有问题但没受过驾车教育(Xl=0,X2=l),p=0.5068,因此视力更重要：如果视力乂有问题乂没受过驾车教育(Xl=0,X2=0),p=0.8207,说这人发生事故的概率为0.8207,非常容易发生事故。习题：data.xls的Sheets是40名肺癌病人的生存资料。其中XI：生活行动能力评分（1・100）;X2：病人年龄；X3：由诊断到进入研究时间（月）；X4：肿瘤类型（"0"表示鳞癌、“1”表示小型细胞癌、〃2"表示腺癌、“3〃表示大型细胞癌）；X5：两种化疗方法（"1”表示常规、“0”表示实验新法）；Y：病人的生存时间（〃0j表示生存时间短，即生存时间小于200天；“1”：表示生存时间长，即生存时间大于或等于200天。）根据上述分析流程对数据进行分析。4.使用EXCEL正交设计与分析请解决以下题目的要求：.某产品的产量取决于3个因素力・4・根据经验，因素力的变化范围为60~80,因素”的变化范围为1.2-1.5,因素n3的变化范圉为62〜0.3・还要考虑因素力与会之间的交互作用.试验指标y为产fib越高越好.选用正交我J。，）进行一次回归正交试脸，试聆结果（产量/kg）依次为：66.72.71.76.70.74.62.69.试确定因素上次和优方案.正交表1_8（2。请见data.xls文件的Sheet9.EXCEL进行正交设计分析以及表头设计的资料如下：6.2.5Excel在直观分析中应用正交武验设计的比观分析关键是计算K，A和氏.这些都可利用Excel的公式和函数功能进行计算，下面通过例6-8来说明。例6・8某工厂为了提高某产品的收率，根据经脸和分析，认为反应温度、罐用量和催化剂种类可能会对产品的收率造成较大的影响，对这3个因素各取3种水平，列于表6-27中。将因京A.B.C依次安排在正交表【.（33的1.2・3列.不考虑因素间的交互作用.9个试验结果y（收率/%）依次为：51.71,58,82,69・59.77.85,84。试用直观分析法葡定因素主次和优方案，并画出趋势图. 『我667例64因索水平表♦平（B）・用盘/kg（C）催化剂牌类18085甲28548乙39055内解：①依据题意.在Excel中列出正交表和试验结果.②K值的计算.这里先引入一个条件求和函数SUMIF・它的作用是对满足条件的单元格求和，其的语法为：SUMIF（range>criteriatsum-range）式中range—用于条件判断的单元格区域，criteria—确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本•例如，条件可以表示为32、"32,->32"或-apples"।sumrange 需要求和的实际单元格范围•K值表示的是同一水平下对应减验结果之和，以A因素列的Kz计算为例，收的计算公式为=SUM1F（B$2:B$10,2,$F$2«$F$10）（如图68）.其中HB$2«B$10w友示用于条件刊断的单元格区域，“2”表示在B$2,B$10范围内等于2的单元格，“$F$2：$F$10"表示求和实际单元格范用.选中该公式,然后水平拖动填充柄,就可计算图68正交试验设计直观分析出后三列的Kz.为了保证在填充柄水平拖动的过程中求和的实际范围不变，就要求行和列都加上绝对引用符号$，即$F$2l$F$10；注意条件判断单元格区域B$2：B$10・行号2和10最好绝对应用，这样往下拖动填充柄,就可将该公式笈制到下一行的单元格，而行号范围不变，对复制到下一行的公式中的“2”改成“3”,然后再水平填充，就可计算所有的K了.③力的计算.A就是K的算术平均值，在本例中£=£,由于在B14,E16范围内.每个单元格的公式都一样.故可采用数组公式法.首先选中单元格区域B14：E16,在该区域的左上角第一个单元格中输入：=B11：E13/3,再同时按“ShifIGrl+Enter"，即可在BM,E16范围内显示图6-8所示结果.④极差R的计算。图6-8中.在B17单元格中输入，-MAX（B14«B16）-M1N（B14：B16）.回车后得到22.然后选中该单元格,向右框动境充柄，就可计算出后三列的极基°住本例中是按A来计算极差R的，也可以按K计算R,这时应在B17单元格中输入：=MAX（BU：B13）-M1N（BU，B13）e对于同一张正交表，上述计算K,上和极差R的公式不会随试验指标y的取值而发生变化，所以当你下次用到时％（34）,只需将图中F列的y值换掉，新的K.A和极差R会同时计算出来,极大地减少了工作城，起到了一劳永逸的效果.⑤绘制趋势图.心势图的纵坐标表示试验指标，横坐标则是因素的水平，一般将不同因素的趋势图画在一张图中,以便于比较.利用Excel画趋势图.首先是建立工作表（如图69所示），第一列表示因素的不同水平，第二列表示对应的上需婺注意的是，在不同因索之间至少应留有一行的间隔.以免趋势图中所有的数据点相连，第一列中的数字要求为文本数字.输入时要在数字前加单图6-9趋势图工作表引号-（英文输入模式下九B因素的三个水平是按实际大小顺序排列的，与水平编号顺序不一致.选中图6-9所示的T作表，进入“图表向导”，选择折线图的第4种子类型，即数据点折线图。依据“图表向导”进入“图表选项”对话框（如图6T03选择“标题”标签，在分类（X）轴下输入“温度/C碱用量/kg催化剂种类”，在数值＜Y）轴下输入“收率/%”，单击“完成”后可成折线图。图6T0折线图"图表选项”对话框注意生成折线图后,应当对横轴（分类轴）按图6T1进行设置，这样才能使分类轴刻度线与各因素水平对应.为r让分类轴标题与坐标轴的数字对齐，可选中分类轴（横轴）标题，在三因素名称之间输入或删除空格.5.判别分析例：为研究舒张期血压与血浆胆固醇对冠心病的作用，调查了50-59岁的女冠心病人15名和正常人16名。他们的舒张期血压（xl）与血浆胆固砰（X2）数据列在data.xls的SheetG中。试用判别分析法建立判别冠心病人与正常人的判别函数。1）数据导入以后，菜单栏上依次点击：分析一分类一判别，打开相应对话框对话框。

ifcdata.sav[数据集1]-IB!SPSSStatistics数据编馒寤文件⑹编辑®视困⑦敌据(Q)转报①分析®直销画图形⑨实用程序Q)窗口⑩)帮助is届的圜^T报告 ►描述统计 ►।S3।।表⑴ ►CaseJJurn舒张压 胆比较均值⑭ ►变量 变量I变11 9.86一短族性模型⑨ ►2_ 2 13.33广义线性模型 ►3_r 3 14.66混合模型的 ►44 9.33相关。 ►5二 5 12.80叵b三(R) ►66 10.66对诂线性模型’0) >77 10.66柚纥网络 》8二| 8 13.33照两步聚类①…99 13.33旅姑 k1010 1333I*活FfiFS/Q\ k阿K•均值聚类(K)…1111 12.00度里(S)殖系统聚类回…12_ 12 14,66非参数检验型) ►曷树®…1313 13.33预测0 ►A