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

1、实验2指导：EXCEL和SPSS在回归分析、正交试验设计和判别分析中的应用实验目的熟悉EXCEL和SPSS在数据分析中的操作；使用EXCEL和SPSS进行回归分析、正交试验设计和判别分析。实验内容一元线性回归分析例：近年來国家教育部决定将各高校的后勤社会化。某从事饮食业的企业家认为这是一个很好的投资机会，他得到十组高校人数与周边饭店的季销售额的数据资料，并想根据高校的数据决策其投资规模，数据见data.xls的Sheets1）选择数据区域B2:C11,从“插入”菜单中选择“散点图”。Excel将显示相应散点图。2）选择图上的点，右键菜单，选择添加趋势线，如下图所示：设置截距=0显示公式g)0显

2、乗R平方值z结果不仅显示散点图的趋势线，还会显示相应公式，即一元线性回归的回归函数，同时显示R平方值，R即相关系数，其绝对值越接近1,表示两组数据的线性相关程度越高。一元线性回归函数描述了两组数据间存在的线性关系，在上述例子中只要知道其它高校的人数即可根据该公式预测大概的季度销售额。而R的大小能够用于度量这种预测的准确度。另外，使用EXCEL自带的函数也能实现一元线性回归：截距函数INTERCEPT功能：利用己知的x值与y值计算回归直线在y轴的截距。语法结构：INTERCEPT(known_ySknown_xs)斜率函数SLOPE功能:返回根据known_yt和known_x，s中的数据点拟合

3、的线性回归直线的斜率。语法结构：SLOPE(known_ySknovm_xs)相关系数函数RSQ功能:返回根据known_yt和known_xt中数据点计算得出的相关系数的平方。语法结构：RSQ(known_ys,known_xs)试比较图表法和函数法呑算得出的二元线性回归方程是否一致。多元线性回归分析例：一家房地产评估公司想对某城市的房地产销售价格(y)与地产的评估价值(xl)和使用面积(x2)建立一个模型，一边对销售价格作出合理的预测。为此收集20栋住宅的房地产评估数据(data.xls的Sheet2)o由于本问题有两个自变量，因此需要使用多元线性回归，需要借助于Excel的数据分析功能。

4、点击“数据分析”，跳出回归分析对话框；填充应变量y和自变量xl,x2对应的区域和输出区域，如下图:区）回归3）点击确定后将出现多元线性回归的结果，对于结果（如下图a,b,c）的一些重要项解释如下：回归统卜MultipleR0.860941385RSquare0.741220068AdjustedRSquare0.71077537标准误差1331.729603观测值20图a方差分析dfSSMSFSignificanceF回归分析286357011.4843178505.7424.346441.02308E-05残差1730149563.521773503.737总计19116506575图bCo

5、efficients标准误差tStatP-valueLower95%Upper95%下限95.0%上限95.0%Intercept-285.0090434965.4493559-0.2952086940.77141-2321.9291151751.911-2321.9291751.911XVariabl1.5598002740.7829795591.9921340920.062668-0.0921421833.2117427-0.0921423.2117427XVariabl0.314487660.0905036043.4748634020.0028980.1235417490.5054336

6、0.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

7、,x2对导致结果y的贡献，也就是说还有导致结果y的原因中有29%是由除了xl,x2以外的因素造成的。习题：在黄罠提取工艺的研究中，选择了前煮时间、煎煮次数和加水量进行考察，实验数据见data.xls的Sheet3,试对实验数据进行多元线性回归，对结果进行讨论。logistic回归分析质量性状（qualitativecharacter）是指能观察到而不能直接测量的，只能用文字來描述其特征的性状，如食品颜色、风味等等。这类性状本身不能直接用数值表示，要获得这类性状的数据资料，须对其观察结果作数量化处理。例如：是否抽烟，是否患病。釆用线性回归分析显然不太合适，而logistic回归能较好地解决质量性

8、状分析的问题。例：我们有一组数据，45个观测值，四个变量，包括：age（年龄，数值型）；vision（视力状况，分类型，1表示好，0表示有问题）；drive（驾车教育，分类型，1表示参加过驾车教育，0表示没有）和一个分类型输出变量accident（去年是否出过事故，1表示出过事故，0表示没有）。我们的目的就是要考察前三个变量与发生事故的关系。数据见data.xls的Sheet4.3.1计算优势比优势（odds）是指某影响因素控制在某种水平时，事件发生率与事件不发生率的比值，即P/（1-P）o某影响因素的两个不同水平的优势的比值称为优势比，如某影响因素的一个水平为cl,另一个水平为co,则这两个

9、水平的优势比为：结果吸烟不吸烟合计食管癌患者309(a)126(b)435非食管癌患者208(c)243(d)451合计517(a+c)369(b+d)886OR=例如：吸烟与食管癌关系的病例对照调查结果。吸烟的优势二型竺“49208/517非吸烟的优势=126/369243/369=0.52149OR=-=2.870.52OR表示影响因素对事件发生的影响方向和影响能力大小。0/?1表示该因素取值越大，事件发生的概率越大，乂称危险因素。0/?1表示该因素取值越大，事件发生的概率越小，乂称保护因素。ORJ表示该因素与事件的发生无关。试计算视力和驾车教育对发生事故的优势比。3.2SPSS的安装1）

10、启动虚拟机，并拷贝安装文件至虚拟机中；2）兰瞬省方式安装SPSS,最后在注册环节点取消即可；3）运行安装文件夹下spss_19_patch_V2目录中的SPSS19crack20100825.exe即可获得破解过的SPSSoSPSS数据的导入在spss中导入Excel文件中数据方法如下：点击SPSS中的文件一打开一数据,文件类型选*.xls,*xlsx,选择实验扌旨导文件夹中的data.xls,确定，然后工作表选Sheet4即可。SPSS用于logistic回归1）数据导入以后，菜单栏上依次点击：分析一回归一二元logistic回归，打开相应对话框对话框。味事按分析.saw数据集1一IB1SP

11、SSStatistics数据编输器文件(编辑旦视图凹数据(D)转换0)分析回直销勉图形实用程序)W(W)帮助空為H3发生爭故112111770报吿描述统计表E比较均垃遁一股线性锲型厂叉线性魁混合模型辺相关回归迟)对数线性模型(2)神经网络分类()降维度虽(S)非臭数检验刨预測生存函数多垂响应因缺矢値分W-多重归因复杂抽样质量控制SRoc曲线因也IUS自动线性建模丽线性0曲线估计跑咅附启小平方回二元Logistic.多项Logistic.斓有序HProbit斓非线性刮碣杖車估计鹵翩斶小二乘法最佳尺度(CATREG)2)将因变量和自变量放入格子的列表里，如图所示，上面的是因变量，下面的是自变量，我

12、们看到这里有三个自变量：年龄、视力、驾车训练。点击确定，SPSS将计算得到分析结果，主要观察结果中最后一张表，如图：方稈中的娈量BS.E,WaisdfSig.Exp(B)EXP(B)的95號C,L下限上限步骡“年龄.007.1291.7191.007.9711.043视力-1.7107恥5.8721.015.181.045.721驾车训练-1.494.7054.4961.224.056.8931.5211.0572.0711.1504.579a在步骤1中输入的变虽年龄，视力驾车训练.其中，Sig.值是回归系数的显著性检验p值，可以看出年龄系数对应的p值大于0.05,不显著因此不将其纳入回归方程

13、方程，而视力和驾车系数显著，常数不用进行显著性检验。因此根据Logistic回归方法可以得出估算发生事故的概率p的公式如下：1.521一1.710X11.494X2假设有个人，视力没问题但没有受过驾车教育(XbX2=0),代入方程，有log(p/(l-p)=-0.189,再p/(l-p)=exp(-0.189)=0.8278,p=0.4529,即我们说这人发生事故的概率为0.4529;乂另一个，视力有问题但没受过驾车教育(Xl=0zX2=l),p=0.5068,因此视力更重要；如果视力乂有问题乂没受过驾车教育(Xl=0,X2=0),p=0.8207,说这人发生事故的概率为0.8207,非常容易

14、发生事故。习题：data.xls的Sheet5是40名肺癌病人的生存资料。其中XI：生活行动能力评分（KLOO）；X2：病人年龄；X3：由诊断到进入研究时间（月）；X4：肿瘤类型（“0表示鳞癌、T表示小型细胞癌、“2表示腺癌、“3表示大型细胞癌）；X5：两种化疗方法（T表示常规、“0”表示实验新法）；Y：病人的生存时间（“0”：表示生存时间短，即生存时间小于200天；“1：表示生存时间长，即生存时间大于或等于200天。）根据上述分析流程对数据进行分析。4.使用EXCEL正交设计与分析请解决以下题目的要求：1.某产品的产量取决F3个因索才】七片很据经验，因素巧的变化范圈为6080,因素工z的变化

15、范围为121.5因素工3的变化范围为020.3.还要考虑因素工】与工2之间的交互作用试验指标，为产锻.越髙越好.选用正交表4（27）进行一次回归正交试脸试验结果（产量/kg依次为：66.72.71.76,70.74,62.69。试确定因素主次和优方案.正交表1_8（2请见data.xls文件的Sheet9.EXCEL进行正交设计分析以及表头设计的资料如下：6.2.5Excel在直观分析中应用正交试验设计的直观分析关键是计算K,代和这些都可利用Excel的公式和函数功能进行计算.下面通过例68来说明。例68某工厂为了提髙某产品的收率.根据经脸和分析，认为反应温度.碱用量和催化剂种类可能会对产品的

16、收率造成较大的影响对这3个因索各取3种水平，列于丧627中。将因素A.B,C依次安排在正交表U（34）的1.2.3列.不考虑因素间的交互作用。9个试验结果y（收率/%）依次为，51.71.58.82,69.59.77.85,84。试用直观分析法确定因索主次和优方案.并画岀趟势图。6-27例6因素水平衰水平（A）fi/r32”或FpplesJsum_range需要求和的实际单元格范围。K值表示的是同一水平下对应试验结果之和以A因素列的K?计算为例Kz的计算公式为=SUMIF（B$2:B$10,2,$F$2：$F$10）（如图6-8）其中“B$2：B$10力表示用于条件判断的单元格区域“2”表示在

17、B$2:B$10范围内等于2的单元格“$F$2:$F$10”表示求和实际单元格范围.选中该公式.然后水平拖动填充柄就可计算r2!_L十33二SUIIF(B$2：B$1CLZJF|?;$F$12)F3456789?_LTTuBT2312DC723123312-JL5171588269597785r_J:Ki180210195204K2210|_225237207刖k3246201204225k】60706568场70757969k382676875极差R228147图68正交试验设计直观分析1238480608570(90824875A67T.85一匹甲65$乙空丙68j图6-9趋势图工作表出后

18、三列的K”为了保证在填充柄水平拖动的过程中求和的实际范围不变，就要求行和列都加上绝对引用符号$,即$F$2:$F$10；注意条件判断单元格区域B$2；B$10行号2和10最好绝对应用这样往下拖动填充柄.就可将该公式复制到下一行的单元格.而行号范出不变，对复制到下一行的公式中的“2円改成“3”.然后再水平填充，就可计算所有的K3了。&的计算。怡就是K的算术平均值，在本例中怡=孚，由于在B14：E16范围内，每个单元格的公式都-样故町采用数组公式法。首先选中单元格区域1314：E16,在该区域的左上角第一个单元格中输入：=B11:E13/3,再同时按Shift+Ctrl+Enter,即可在B14；

19、E16范围内显示图6-8所示结果。极差R的计算。图68中.在B17单元格中输入=MAX(B14:B16)-M1N(B14:B16),回车后得到22.然后选中该单元格.向右拖动填充柄，就可计算出后三列的极差。在本例中是按怡来计算极差R的，也町以按K计算R.这时应在B17单元格中输入：=MAX(B11:B13)-M1N(B11:B13)o对于同一张正交表，上述计算K,和极差R的公式不会随试验指标y的取值而发生变化，所以当你下次用到时U(34)只需将图中F列的y值换掉新的K.殳和极差R会同时计算出来极大地减少了工作童，起到了一劳永逸的效果。绘制趙势图。趋势图的纵坐标表示试验指标.横坐标则是因素的水平

20、.一般将不同因素的趙势图画在一张图中.以便于比较。利用Exc“画趋势图.首先是建立工作表(如图69所示)，第一列表示因素的不同水平，第二列表示对应的仁需耍注意的是在不同因素之间至少应留有一行的间隔.以免箱势图中所有的数据点相连；第一列中的数字要求为文本数字.输入时要在数字前加单引号（英文输入模式下八B因素的三个水平是按实际大小顺序排列的，与水平编号顺序不一致。选中图69所示的工作表.进入44图表向导”，选择折线图的第4种子类型，即数据点折线图。依据“图表向导”进入“图表选项”对话框（如图6-10）,选择紀标题”标签在分类（X）轴下输入.温度/碱用量/kg催化剂种类”，在数值（F）轴下输入“收率/%，单击“完成”后可成折线图。图610折线图图表选项对话框注意生成折线图后.应当对横轴（分类轴）按图11进行设置这样才能使分类轴刻度线与各因素水平对应。为了让分类轴标题与坐标轴的数字对齐，可选中分类轴（横轴）标题,在三因素名称之间输入或删除空格。5.判别分析例：为研究舒张期血压与血浆胆固醇对冠心病的作用，调查了50-59岁的女冠心病人15名和正常人16名。他们的舒张期血压（xl）与血浆胆固醇（x2）数据列在data.xls的SheetG中。试用判别分析法建立判别冠心病人与正常人的判别函数。1）数据导入以后，菜单栏上依次