EXCEL和SPSS在回歸分析、正交試驗設(shè)計和判別分析中的應(yīng)用

實(shí)驗2指導(dǎo)：EXCEL和SPSS在回歸分析、正交試驗設(shè)計和判別分析中的應(yīng)用實(shí)險目的.熟悉EXCEL和SPSS在數(shù)據(jù)分析中的操作：.使用EXCEL和SPSS進(jìn)行回歸分析、正交試驗設(shè)計和判別分析。實(shí)險內(nèi)容1.一元線性回歸分析例：近年來國家教育部決定將各高校的后勤社會化。某從事飲食業(yè)的企業(yè)家認(rèn)為這是一個很好的投資機(jī)會，他得到十組高校人數(shù)與周邊飯店的季銷售額的數(shù)據(jù)資料，并想根據(jù)高校的數(shù)據(jù)決策其投資規(guī)模，數(shù)據(jù)見data.xls的Sheet)1)選擇數(shù)據(jù)區(qū)域B2:C11,從“插入”菜單中選擇“散點(diǎn)圖”。Excel將顯示相應(yīng)散點(diǎn)圖。2)選擇圖上的點(diǎn)，右鍵菜單，選擇添加趨勢線，如下圖所示：囹1設(shè)旁^至列格式(￡).??3)在趨勢線選項，將“顯示公式”和“顯示rM方”選項打勾，如下圖:□設(shè)置截距⑤）二H顯示公式邕）0濕不R平為值國）i???????■?????????????????■???????????■??????????結(jié)果不僅顯示散點(diǎn)圖的趨勢線，還會顯示相應(yīng)公式，即一元線性回歸的回歸函數(shù)，同時顯示R平方值，R即相關(guān)系數(shù)，其絕對值越接近1,表示兩組數(shù)據(jù)的線性相關(guān)程度越高。一元線性回歸函數(shù)描述了兩組數(shù)據(jù)間存在的線性關(guān)系，在上述例子中只要知道其它高校的人數(shù)即可根據(jù)該公式預(yù)測大概的季度銷售額。而R的大小能夠用于度量這種預(yù)測的準(zhǔn)確度。另外，使用EXCEL自帶的函數(shù)也能實(shí)現(xiàn)一元線性回歸：截距函數(shù)INTERCEPT功能：利用已知的x值與y值計算回歸直線在y軸的截距。語法結(jié)構(gòu)：INTERCEPT(known_y5kno\vn_x§斜率函數(shù)SLOPE功能:返回根據(jù)known_『s和known_xzs中的數(shù)據(jù)點(diǎn)擬合的線性回歸直線的斜率。語法結(jié)構(gòu)：SLOPE(known_y's/known_x's)相關(guān)系數(shù)函數(shù)RSQ功能:返回根據(jù)known_『s和known_x，s中數(shù)據(jù)點(diǎn)計算得出的相關(guān)系數(shù)的平方。語法結(jié)構(gòu)：RSQ(known_y's/known_x's)試比較圖表法和函數(shù)法H算得出的二元線性回歸方程是否一致。.多元線性回歸分析例：一家房地產(chǎn)評估公司想對某城市的房地產(chǎn)銷售價格(y)與地產(chǎn)的評估價值Cxi)和使用面積(x2)建立一個模型，一邊對銷售價格作出合理的預(yù)測。為此收集20棟住宅的房地產(chǎn)評估數(shù)據(jù)(data.xls的Sheet2)?由于本問題有兩個自變量，因此需要使用多元線性回歸，需要借助于Excel的數(shù)據(jù)分析功能。1)點(diǎn)擊“數(shù)據(jù)分析”，跳出回歸分析對話框；2)填充應(yīng)變量y和自變量xl,x2對應(yīng)的區(qū)域和輸出區(qū)域，如下圖:

0?回歸3）點(diǎn)擊確定后將出現(xiàn)多元線性回歸的結(jié)果，對于結(jié)果（如下圖a,b,c）的一些重要項解釋如下：回歸 0歸統(tǒng)計MultipleR0.860941385RSquare0.741220068AdjustedRSquare0.71077537標(biāo)淮誤差1331.729603觀測值20圖a方差分析dfSSMSFSignificanceF回?zé)舴治?86357011.48431785OE.7424.346441.02308E-05殘差1730149563.521773503.737總計19116506575圖bCoefficients標(biāo)準(zhǔn)誤差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表示此線性回歸結(jié)果顯著，及回歸方程可信，房地產(chǎn)的銷售價格與地產(chǎn)估價和使用面積是有關(guān)

系的。圖c中的Coefficients為回歸方程的系數(shù)，因此，回歸結(jié)果為y=-285.0094+1.5598x1+03145x2,在使用面積不變的情況下，地產(chǎn)估價每增加1萬元,房產(chǎn)銷售的平均價格就會提高1.5598萬元；在房地產(chǎn)估價不變的條件下，使用面積每增加1平方米，房產(chǎn)銷售的平均價格就會提高0.3145元；圖a中AdjustedRSquare為調(diào)整復(fù)測定系數(shù)，本例中約為0.71,它表示兩個變量xl,x2對導(dǎo)致結(jié)果V的貢獻(xiàn)，也就是說還有導(dǎo)致結(jié)果V的原因中有29%是由除了xl,x2以外的因素造成的。習(xí)題：在黃苗提取工藝的研究中，選擇了前煮時間、煎煮次數(shù)和加水量進(jìn)行考察,實(shí)驗數(shù)據(jù)見data.xls的Sheet3,試對實(shí)驗數(shù)據(jù)進(jìn)行多元線性回歸，對結(jié)果進(jìn)行討論。.logistic回歸分析質(zhì)量性狀（qualitativecharacter）是指能觀察到而不能直接測量的，只能用文字來描述其特征的性狀，如食品顏色、風(fēng)味等等。這類性狀本身不能直接用數(shù)值表示，要獲得這類性狀的數(shù)據(jù)資料，須對其觀察結(jié)果作數(shù)量化處理。例如：是否抽煙，是否患病。采用線性回歸分析顯然不太合適，而logistic回歸能較好地解決質(zhì)量性狀分析的問題。例：我們有一組數(shù)據(jù)，45個觀測值，四個變量，包括：age（年齡，數(shù)值型）：vision（視力狀況，分類型，1表示好，0表示有問題）；drive（駕車教育，分類型，1表示參加過駕車教育，0表示沒有）和一個分類型輸出變量accident（去年是否出過事故，1表示出過事故，0表示沒有）。我們的目的就是要考察前三個變量與發(fā)生事故的關(guān)系。數(shù)據(jù)見data.xls的Sheets計算優(yōu)勢比優(yōu)勢（odds）是指某影響因素控制在某種水平時，事件發(fā)生率與事件不發(fā)生率的比值，即P/（l-P）。某影響因素的兩個不同水平的優(yōu)勢的比值稱為優(yōu)勢比，如某影響因素的一個水平為C1,另一個水平為co,則這兩個水平的優(yōu)勢比為：orJJQFR/QF）309/517208/517309/517208/517結(jié)果吸煙不吸煙合計食管癌患者309(a)126(b)435非食管癌患者208(c)243(d)451合計517(a+c)369(b+d)886吸煙的優(yōu)勢==1.49非吸煙的優(yōu)勢=霽="2149OR=--=2.870.52OR表示影響因素對事件發(fā)生的影響方向和影響能力大小。OQ1表示該因素取值越大，事件發(fā)生的概率越大，乂稱危險因素。。/?<1表示該因素取值越大，事件發(fā)生的概率越小，乂稱保護(hù)因素。OR=1表示該因素與事件的發(fā)生無關(guān)。試計算視力和駕車教育對發(fā)生事故的優(yōu)勢比。SPSS的安裝1）啟動虛擬機(jī)，并拷貝安裝文件至虛擬機(jī)中：2）空缺省方式安裝SPSS,最后在注冊環(huán)節(jié)點(diǎn)取消即可；3）運(yùn)行安裝文件夾下spss_19_patch_V2目錄中的SPSS19crack20100825.exe即可獲得破解過的SPSS。SPSS數(shù)據(jù)的導(dǎo)入在SPSS中導(dǎo)入Excel文件中數(shù)據(jù)方法如下:點(diǎn)擊SPSS中的文件一打開一數(shù)據(jù),文件類型選*.xls,*xlsx.選擇實(shí)驗指導(dǎo)文件夾中的data.xls,確定，然后匚作表選Sheet4即可。SPSS用于logistic回歸1）數(shù)據(jù)導(dǎo)入以后，菜單欄上依次點(diǎn)擊：分析一回歸一二元logistic回歸，打開相應(yīng)對話框?qū)υ捒?。卑事故分?sau[數(shù)據(jù)集1]-IB!SPSSStatistics數(shù)據(jù)編輯器文件⑹結(jié)輯但）視困&）收據(jù)①）轉(zhuǎn)換①分析?直銷?圖形⑼實(shí)用整序Msa（w）幫助:宅附昌圜小r發(fā)生事故年酎1117214431434155_5_175603570428r°579023100刀11□3312045130471405215055161631711310163191432011721170報告描述統(tǒng)計表⑴比較均值領(lǐng)一段或住俁型⑨廣火線性儂混合模型兇相關(guān)@回歸?對數(shù)線性模型⑨神經(jīng)網(wǎng)絡(luò)分類（D降維度最⑻駐泰數(shù)險/包）莎沔①生存函數(shù)@多重響應(yīng)Q）國坡失值分析①..

多直歸因①

復(fù)雜抽彈但

質(zhì)量控制（2）匕ROC曲線圖⑦..JJ2）將因變量和自變量放入格子的列表里，如圖所示，上面的是因變量，下面的是自變量，我們看到這里有三個自變量：年齡、視力、駕車訓(xùn)練。3)點(diǎn)擊確定，SPSS將計算得到分析結(jié)果，主要觀察結(jié)果中最后一張表，如圖：方程中的變量BS.E.WalsdtSig.Exp(B)EXP(B)&)95%C.l.下限上限步溪產(chǎn)年齡.007.01812917191.007.9711.043視力-1.710,70$5.8721.015.181.045.721駕車訓(xùn)練-1.494.7064.4961,034.224.066.893常量16211.0572.0711.1504.579a.在步膘1中輸入的變量:年齡,視力.駕車訓(xùn)練.其中，Sig.值是回歸系數(shù)的顯著性檢驗p值，可以看出年齡系數(shù)對應(yīng)的p值大ro.o5,不顯著因此不將其納入回歸方程方程,而視力和駕車系數(shù)顯著，常數(shù)不用進(jìn)行顯著性檢驗。因此根據(jù)Logistic回歸方法可以得出估算發(fā)生事故的概率p的公式如下：Log—^―=1.521-1.710X1-1.494X21-P假設(shè)有個人，視力沒問題但沒有受過駕車教育(Xl=l,X2=0),代入方程，有l(wèi)og(p/(l-p))=-0.189,Mp/(l-p)=exp(-0.189)=0.8278,p=0.4529,即我們說這人發(fā)生事故的概率為0.4529：乂另一個，視力有問題但沒受過駕車教育(Xl=0,X2=l),p=0.5068,因此視力更重要：如果視力乂有問題乂沒受過駕車教育(Xl=0,X2=0),p=0.8207,說這人發(fā)生事故的概率為0.8207,非常容易發(fā)生事故。習(xí)題：data.xls的Sheets是40名肺癌病人的生存資料。其中XI：生活行動能力評分（1?100）;X2：病人年齡；X3：由診斷到進(jìn)入研究時間（月）；X4：腫瘤類型（"0"表示鱗癌、“1”表示小型細(xì)胞癌、〃2"表示腺癌、“3〃表示大型細(xì)胞癌）；X5：兩種化療方法（"1”表示常規(guī)、“0”表示實(shí)驗新法）；Y：病人的生存時間（〃0j表示生存時間短，即生存時間小于200天；“1”：表示生存時間長，即生存時間大于或等于200天。）根據(jù)上述分析流程對數(shù)據(jù)進(jìn)行分析。4.使用EXCEL正交設(shè)計與分析請解決以下題目的要求：.某產(chǎn)品的產(chǎn)量取決于3個因素力?4?根據(jù)經(jīng)驗，因素力的變化范圍為60~80,因素”的變化范圍為1.2-1.5,因素n3的變化范圉為62?0.3?還要考慮因素力與會之間的交互作用.試驗指標(biāo)y為產(chǎn)fib越高越好.選用正交我J。，）進(jìn)行一次回歸正交試臉，試聆結(jié)果（產(chǎn)量/kg）依次為：66.72.71.76.70.74.62.69.試確定因素上次和優(yōu)方案.正交表1_8（2。請見data.xls文件的Sheet9.EXCEL進(jìn)行正交設(shè)計分析以及表頭設(shè)計的資料如下：6.2.5Excel在直觀分析中應(yīng)用正交武驗設(shè)計的比觀分析關(guān)鍵是計算K，A和氏.這些都可利用Excel的公式和函數(shù)功能進(jìn)行計算，下面通過例6-8來說明。例6?8某工廠為了提高某產(chǎn)品的收率，根據(jù)經(jīng)臉和分析，認(rèn)為反應(yīng)溫度、罐用量和催化劑種類可能會對產(chǎn)品的收率造成較大的影響，對這3個因素各取3種水平，列于表6-27中。將因京A.B.C依次安排在正交表【.（33的1.2?3列.不考慮因素間的交互作用.9個試驗結(jié)果y（收率/%）依次為：51.71,58,82,69?59.77.85,84。試用直觀分析法葡定因素主次和優(yōu)方案，并畫出趨勢圖. 『我667例64因索水平表?平（B）?用盤/kg（C）催化劑牌類18085甲28548乙39055內(nèi)解：①依據(jù)題意.在Excel中列出正交表和試驗結(jié)果.②K值的計算.這里先引入一個條件求和函數(shù)SUMIF?它的作用是對滿足條件的單元格求和，其的語法為：SUMIF（range>criteriatsum-range）式中range—用于條件判斷的單元格區(qū)域，criteria—確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本?例如，條件可以表示為32、"32,->32"或-apples"?sumrange 需要求和的實(shí)際單元格范圍?K值表示的是同一水平下對應(yīng)減驗結(jié)果之和，以A因素列的Kz計算為例，收的計算公式為=SUM1F（B$2:B$10,2,$F$2?$F$10）（如圖68）.其中HB$2?B$10w友示用于條件刊斷的單元格區(qū)域，“2”表示在B$2,B$10范圍內(nèi)等于2的單元格，“$F$2：$F$10"表示求和實(shí)際單元格范用.選中該公式,然后水平拖動填充柄,就可計算圖68正交試驗設(shè)計直觀分析出后三列的Kz.為了保證在填充柄水平拖動的過程中求和的實(shí)際范圍不變，就要求行和列都加上絕對引用符號$，即$F$2l$F$10；注意條件判斷單元格區(qū)域B$2：B$10?行號2和10最好絕對應(yīng)用，這樣往下拖動填充柄,就可將該公式笈制到下一行的單元格，而行號范圍不變，對復(fù)制到下一行的公式中的“2”改成“3”,然后再水平填充，就可計算所有的K了.③力的計算.A就是K的算術(shù)平均值，在本例中￡=￡,由于在B14,E16范圍內(nèi).每個單元格的公式都一樣.故可采用數(shù)組公式法.首先選中單元格區(qū)域B14：E16,在該區(qū)域的左上角第一個單元格中輸入：=B11：E13/3,再同時按“ShifIGrl+Enter"，即可在BM,E16范圍內(nèi)顯示圖6-8所示結(jié)果.④極差R的計算。圖6-8中.在B17單元格中輸入，-MAX（B14?B16）-M1N（B14：B16）.回車后得到22.然后選中該單元格,向右框動境充柄，就可計算出后三列的極基°住本例中是按A來計算極差R的，也可以按K計算R,這時應(yīng)在B17單元格中輸入：=MAX（BU：B13）-M1N（BU，B13）e對于同一張正交表，上述計算K,上和極差R的公式不會隨試驗指標(biāo)y的取值而發(fā)生變化，所以當(dāng)你下次用到時％（34）,只需將圖中F列的y值換掉，新的K.A和極差R會同時計算出來,極大地減少了工作城，起到了一勞永逸的效果.⑤繪制趨勢圖.心勢圖的縱坐標(biāo)表示試驗指標(biāo)，橫坐標(biāo)則是因素的水平，一般將不同因素的趨勢圖畫在一張圖中,以便于比較.利用Excel畫趨勢圖.首先是建立工作表（如圖69所示），第一列表示因素的不同水平，第二列表示對應(yīng)的上需婺注意的是，在不同因索之間至少應(yīng)留有一行的間隔.以免趨勢圖中所有的數(shù)據(jù)點(diǎn)相連，第一列中的數(shù)字要求為文本數(shù)字.輸入時要在數(shù)字前加單圖6-9趨勢圖工作表引號-（英文輸入模式下九B因素的三個水平是按實(shí)際大小順序排列的，與水平編號順序不一致.選中圖6-9所示的T作表，進(jìn)入“圖表向?qū)А保x擇折線圖的第4種子類型，即數(shù)據(jù)點(diǎn)折線圖。依據(jù)“圖表向?qū)А边M(jìn)入“圖表選項”對話框（如圖6T03選擇“標(biāo)題”標(biāo)簽，在分類（X）軸下輸入“溫度/C堿用量/kg催化劑種類”，在數(shù)值＜Y）軸下輸入“收率/%”，單擊“完成”后可成折線圖。圖6T0折線圖"圖表選項”對話框注意生成折線圖后,應(yīng)當(dāng)對橫軸（分類軸）按圖6T1進(jìn)行設(shè)置，這樣才能使分類軸刻度線與各因素水平對應(yīng).為r讓分類軸標(biāo)題與坐標(biāo)軸的數(shù)字對齊，可選中分類軸（橫軸）標(biāo)題，在三因素名稱之間輸入或刪除空格.5.判別分析例：為研究舒張期血壓與血漿膽固醇對冠心病的作用，調(diào)查了50-59歲的女冠心病人15名和正常人16名。他們的舒張期血壓（xl）與血漿膽固砰（X2）數(shù)據(jù)列在data.xls的SheetG中。試用判別分析法建立判別冠心病人與正常人的判別函數(shù)。1）數(shù)據(jù)導(dǎo)入以后，菜單欄上依次點(diǎn)擊：分析一分類一判別，打開相應(yīng)對話框?qū)υ捒颉?/p>

ifcdata.sav[數(shù)據(jù)集1]-IB!SPSSStatistics數(shù)據(jù)編饅寤文件⑹編輯?視困⑦敵據(jù)(Q)轉(zhuǎn)報①分析?直銷畫圖形⑨實(shí)用程序Q)窗口⑩)幫助is屆的圜^T報告 ?描述統(tǒng)計 ??S3??表⑴ ?CaseJJurn舒張壓 膽比較均值? ?變量 變量I變11 9.86一短族性模型⑨ ?2_ 2 13.33廣義線性模型 ?3_r 3 14.66混合模型的 ?44 9.33相關(guān)。 ?5二 5 12.80叵b三(R) ?66 10.66對詁線性模型’0) >77 10.66柚紇網(wǎng)絡(luò) 》8二| 8 13.33照兩步聚類①…99 13.33旅姑 k1010 1333I*活FfiFS/Q\ k阿K?均值聚類(K)…1111 12.00度里(S)殖系統(tǒng)聚類回…12_ 12 14,66非參數(shù)檢驗型) ?曷樹?…1313 13.33預(yù)測0 ?A