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

1、實驗2指導(dǎo)：EXCEL和SPSS在回歸分析、正交試驗設(shè)計和判別分析中的應(yīng)用實驗?zāi)康?. 熟悉EXCEL和SPSS在數(shù)據(jù)分析中的操作；2. 使用EXCEL和SPSS進行回歸分析、正交試驗設(shè)計和判別分析。實驗內(nèi)容1. 一元線性回歸分析例：近年來國家教育部決定將各高校的后勤社會化。某從事飲食業(yè)的企業(yè)家認為這是一個很好的投資機會，他得到十組高校人數(shù)與周邊飯店的季銷售額的數(shù)據(jù)資料，并想根據(jù)高校的數(shù)據(jù)決策其投資規(guī)模，數(shù)據(jù)見data.xls的Sheets1）選擇數(shù)據(jù)區(qū)域B2:C11,從“插入”菜單中選擇“散點圖”。Excel將顯示相應(yīng)散點圖。2）選擇圖上的點，右鍵菜單，選擇添加趨勢線，如下圖所示：設(shè)置截距=

2、0顯示公式g)0顯乗R平方值»z結(jié)果不僅顯示散點圖的趨勢線，還會顯示相應(yīng)公式，即一元線性回歸的回歸函數(shù)，同時顯示R平方值，R即相關(guān)系數(shù)，其絕對值越接近1,表示兩組數(shù)據(jù)的線性相關(guān)程度越高。一元線性回歸函數(shù)描述了兩組數(shù)據(jù)間存在的線性關(guān)系，在上述例子中只要知道其它高校的人數(shù)即可根據(jù)該公式預(yù)測大概的季度銷售額。而R的大小能夠用于度量這種預(yù)測的準確度。另外，使用EXCEL自帶的函數(shù)也能實現(xiàn)一元線性回歸：截距函數(shù)INTERCEPT功能：利用己知的x值與y值計算回歸直線在y軸的截距。語法結(jié)構(gòu)：INTERCEPT(known_ySknown_x's)斜率函數(shù)SLOPE功能:返回根據(jù)known

3、_yt和known_x，s中的數(shù)據(jù)點擬合的線性回歸直線的斜率。語法結(jié)構(gòu)：SLOPE(known_ySknovm_x's)相關(guān)系數(shù)函數(shù)RSQ功能:返回根據(jù)known_yt和known_xt中數(shù)據(jù)點計算得出的相關(guān)系數(shù)的平方。語法結(jié)構(gòu)：RSQ(known_y's,known_x's)試比較圖表法和函數(shù)法呑算得出的二元線性回歸方程是否一致。2. 多元線性回歸分析例：一家房地產(chǎn)評估公司想對某城市的房地產(chǎn)銷售價格(y)與地產(chǎn)的評估價值(xl)和使用面積(x2)建立一個模型，一邊對銷售價格作出合理的預(yù)測。為此收集20棟住宅的房地產(chǎn)評估數(shù)據(jù)(data.xls的Sheet2)o由于本問題

4、有兩個自變量，因此需要使用多元線性回歸，需要借助于Excel的數(shù)據(jù)分析功能。1) 點擊“數(shù)據(jù)分析”，跳出回歸分析對話框；2) 填充應(yīng)變量y和自變量xl,x2對應(yīng)的區(qū)域和輸出區(qū)域，如下圖:區(qū)）回歸3）點擊確定后將出現(xiàn)多元線性回歸的結(jié)果，對于結(jié)果（如下圖a,b,c）的一些重要項解釋如下：回歸統(tǒng)卜MultipleR0.860941385RSquare0.741220068AdjustedRSquare0.71077537標準誤差1331.729603觀測值20圖a方差分析dfSSMSFSignificanceF回歸分析286357011.4843178505.7424.346441.02308E-0

5、5殘差1730149563.521773503.737總計19116506575圖bCoefficients標準誤差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.0905

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

7、中AdjustedRSquare為調(diào)整復(fù)測定系數(shù)，本例中約為0.71,它表示兩個變量xl,x2對導(dǎo)致結(jié)果y的貢獻，也就是說還有導(dǎo)致結(jié)果y的原因中有29%是由除了xl,x2以外的因素造成的。習題：在黃罠提取工藝的研究中，選擇了前煮時間、煎煮次數(shù)和加水量進行考察，實驗數(shù)據(jù)見data.xls的Sheet3,試對實驗數(shù)據(jù)進行多元線性回歸，對結(jié)果進行討論。3. logistic回歸分析質(zhì)量性狀（qualitativecharacter）是指能觀察到而不能直接測量的，只能用文字來描述其特征的性狀，如食品顏色、風味等等。這類性狀本身不能直接用數(shù)值表示，要獲得這類性狀的數(shù)據(jù)資料，須對其觀察結(jié)果作數(shù)量化處理。例

8、如：是否抽煙，是否患病。釆用線性回歸分析顯然不太合適，而logistic回歸能較好地解決質(zhì)量性狀分析的問題。例：我們有一組數(shù)據(jù)，45個觀測值，四個變量，包括：age（年齡，數(shù)值型）；vision（視力狀況，分類型，1表示好，0表示有問題）；drive（駕車教育，分類型，1表示參加過駕車教育，0表示沒有）和一個分類型輸出變量accident（去年是否出過事故，1表示出過事故，0表示沒有）。我們的目的就是要考察前三個變量與發(fā)生事故的關(guān)系。數(shù)據(jù)見data.xls的Sheet4.3.1計算優(yōu)勢比優(yōu)勢（odds）是指某影響因素控制在某種水平時，事件發(fā)生率與事件不發(fā)生率的比值，即P/（1-P）o某影響因素

9、的兩個不同水平的優(yōu)勢的比值稱為優(yōu)勢比，如某影響因素的一個水平為cl,另一個水平為co,則這兩個水平的優(yōu)勢比為：結(jié)果吸煙不吸煙合計食管癌患者309(a)126(b)435非食管癌患者208(c)243(d)451合計517(a+c)369(b+d)886OR=例如：吸煙與食管癌關(guān)系的病例對照調(diào)查結(jié)果。吸煙的優(yōu)勢二型竺“49208/517非吸煙的優(yōu)勢=126/369243/369=0.52149OR=-=2.870.52 OR表示影響因素對事件發(fā)生的影響方向和影響能力大小。 0/?>1表示該因素取值越大，事件發(fā)生的概率越大，乂稱危險因素。 0/?<1表示該因素取值越大，事件發(fā)生的概率越

10、小，乂稱保護因素。 ORJ表示該因素與事件的發(fā)生無關(guān)。試計算視力和駕車教育對發(fā)生事故的優(yōu)勢比。3.2SPSS的安裝1）啟動虛擬機，并拷貝安裝文件至虛擬機中；2）蘭瞬省方式安裝SPSS,最后在注冊環(huán)節(jié)點取消即可；3）運行安裝文件夾下spss_19_patch_V2目錄中的SPSS19crack20100825.exe即可獲得破解過的SPSSo3.2 SPSS數(shù)據(jù)的導(dǎo)入在spss中導(dǎo)入Excel文件中數(shù)據(jù)方法如下：點擊SPSS中的文件一打開一數(shù)據(jù),文件類型選*.xls,*xlsx,選擇實驗扌旨導(dǎo)文件夾中的data.xls,確定，然后工作表選Sheet4即可。3.3 SPSS用于logistic回歸

11、1）數(shù)據(jù)導(dǎo)入以后，菜單欄上依次點擊：分析一回歸一二元logistic回歸，打開相應(yīng)對話框?qū)υ捒?。味事按分?saw數(shù)據(jù)集1一IB1SPSSStatistics數(shù)據(jù)編輸器文件(編輯旦視圖凹數(shù)據(jù)(D)轉(zhuǎn)換0)分析回直銷勉圖形實用程序®)W(W)幫助空為H3發(fā)生爭故112111770報吿描述統(tǒng)計表E比較均垃遁一股線性鍥型廠叉線性魁»混合模型辺'相關(guān)©回歸遲)對數(shù)線性模型(2)神經(jīng)網(wǎng)絡(luò)分類(£)降維度雖(S)非臭數(shù)檢驗刨預(yù)測生存函數(shù)多垂響應(yīng)因缺矢値分W-多重歸因復(fù)雜抽樣質(zhì)量控制SRoc曲線因也IUS自動線性建模®麗線性©0曲線估計跑咅

12、附啟小平方回二元Logistic.多項Logistic.斕有序HProbit斕非線性刮碣杖車估計鹵翩斶小二乘法最佳尺度(CATREG)2)將因變量和自變量放入格子的列表里，如圖所示，上面的是因變量，下面的是自變量，我們看到這里有三個自變量：年齡、視力、駕車訓(xùn)練。3) 點擊確定，SPSS將計算得到分析結(jié)果，主要觀察結(jié)果中最后一張表，如圖：方稈中的孌量BS.E,WaisdfSig.Exp(B)EXP(B)的95號C,L下限上限步騾“年齡.007.1291.7191.007.9711.043視力-1.7107恥5.8721.015.181.045.721駕車訓(xùn)練-1.494.7054.4961.22

13、4.056.8931.5211.0572.0711.1504.579a在步驟1中輸入的變雖年齡，視力駕車訓(xùn)練.其中，Sig.值是回歸系數(shù)的顯著性檢驗p值，可以看出年齡系數(shù)對應(yīng)的p值大于0.05,不顯著因此不將其納入回歸方程方程，而視力和駕車系數(shù)顯著，常數(shù)不用進行顯著性檢驗。因此根據(jù)Logistic回歸方法可以得出估算發(fā)生事故的概率p的公式如下：1.521一1.710X11.494X2假設(shè)有個人，視力沒問題但沒有受過駕車教育(X"bX2=0),代入方程，有l(wèi)og(p/(l-p)=-0.189,再p/(l-p)=exp(-0.189)=0.8278,p=0.4529,即我們說這人發(fā)生事故

14、的概率為0.4529;乂另一個，視力有問題但沒受過駕車教育(Xl=0zX2=l),p=0.5068,因此視力更重要；如果視力乂有問題乂沒受過駕車教育(Xl=0,X2=0),p=0.8207,說這人發(fā)生事故的概率為0.8207,非常容易發(fā)生事故。習題：data.xls的Sheet5是40名肺癌病人的生存資料。其中XI：生活行動能力評分（KLOO）；X2：病人年齡；X3：由診斷到進入研究時間（月）；X4：腫瘤類型（“0"表示鱗癌、T表示小型細胞癌、“2表示腺癌、“3表示大型細胞癌）；X5：兩種化療方法（T表示常規(guī)、“0”表示實驗新法）；Y：病人的生存時間（“0”：表示生存時間短，即生存時

15、間小于200天；“1"：表示生存時間長，即生存時間大于或等于200天。）根據(jù)上述分析流程對數(shù)據(jù)進行分析。4.使用EXCEL正交設(shè)計與分析請解決以下題目的要求：1.某產(chǎn)品的產(chǎn)量取決F3個因索才】七片很據(jù)經(jīng)驗，因素巧的變化范圈為6080,因素工z的變化范圍為121.5因素工3的變化范圍為020.3.還要考慮因素工】與工2之間的交互作用試驗指標，為產(chǎn)鍛.越髙越好.選用正交表4（27）進行一次回歸正交試臉試驗結(jié)果（產(chǎn)量/kg依次為：66.72.71.76,70.74,62.69。試確定因素主次和優(yōu)方案.正交表1_8（2請見data.xls文件的Sheet9.EXCEL進行正交設(shè)計分析以及表頭

16、設(shè)計的資料如下：6.2.5Excel在直觀分析中應(yīng)用正交試驗設(shè)計的直觀分析關(guān)鍵是計算K,代和這些都可利用Excel的公式和函數(shù)功能進行計算.下面通過例68來說明。例68某工廠為了提髙某產(chǎn)品的收率.根據(jù)經(jīng)臉和分析，認為反應(yīng)溫度.堿用量和催化劑種類可能會對產(chǎn)品的收率造成較大的影響對這3個因索各取3種水平，列于喪627中。將因素A.B,C依次安排在正交表U（34）的1.2.3列.不考慮因素間的交互作用。9個試驗結(jié)果y（收率/%）依次為，51.71.58.82,69.59.77.85,84。試用直觀分析法確定因索主次和優(yōu)方案.并畫岀趟勢圖。«6-27例6"因素水平衰水平（A）fi&

17、#171;/r<B）MRt/kg（C）催化劑種類18085甲I28548乙39055丙解：依據(jù)題意.在Excel中列出正交表和試驗結(jié)果。K值的計算。這里先引入一個條件求和函數(shù)SUMIF,它的作用是對滿足條件的單元格求和，其的語法為；SUMIF（range,criteriatsumrange）式中range用于條件判斷的單元格區(qū)域$criteria確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達式或文本。例如.條件可以表示為32.“32”、*>32”或FpplesJsum_range需要求和的實際單元格范圍。K值表示的是同一水平下對應(yīng)試驗結(jié)果之和以A因素列的K?計算為例Kz的

18、計算公式為=SUMIF（B$2:B$10,2,$F$2：$F$10）（如圖6-8）其中“B$2：B$10力表示用于條件判斷的單元格區(qū)域“2”表示在B$2:B$10范圍內(nèi)等于2的單元格“$F$2:$F$10”表示求和實際單元格范圍.選中該公式.然后水平拖動填充柄就可計算3ruBT23122!3 _L十33二SUIIF(B$2：B$1CLZJF|?;$F$12)F3456789DC723?_L2 TT123312-JL5171588269597785r2 _J3 :Ki180210195204K2210|_225237207刖k3246201204225k】60706568場70757969k38

19、2676875極差R228147圖68正交試驗設(shè)計直觀分析1238480608570('9082'4875A67T.85一匹甲65$乙空丙68j圖6-9趨勢圖工作表出后三列的K”為了保證在填充柄水平拖動的過程中求和的實際范圍不變，就要求行和列都加上絕對引用符號$,即$F$2:$F$10；注意條件判斷單元格區(qū)域B$2；B$10行號2和10最好絕對應(yīng)用這樣往下拖動填充柄.就可將該公式復(fù)制到下一行的單元格.而行號范出不變，對復(fù)制到下一行的公式中的“2円改成“3”.然后再水平填充，就可計算所有的K3了。&的計算。怡就是K的算術(shù)平均值，在本例中怡=孚，由于在B14：E16范圍內(nèi)，

20、每個單元格的公式都-樣故町采用數(shù)組公式法。首先選中單元格區(qū)域1314：E16,在該區(qū)域的左上角第一個單元格中輸入：=B11:E13/3,再同時按"Shift+Ctrl+Enter",即可在B14；E16范圍內(nèi)顯示圖6-8所示結(jié)果。極差R的計算。圖68中.在B17單元格中輸入=MAX(B14:B16)-M1N(B14:B16),回車后得到22.然后選中該單元格.向右拖動填充柄，就可計算出后三列的極差。在本例中是按怡來計算極差R的，也町以按K計算R.這時應(yīng)在B17單元格中輸入：=MAX(B11:B13)-M1N(B11:B13)o對于同一張正交表，上述計算K,和極差R的公式不會

21、隨試驗指標y的取值而發(fā)生變化，所以當你下次用到時U(34)只需將圖中F列的y值換掉新的K.殳和極差R會同時計算出來極大地減少了工作童，起到了一勞永逸的效果。繪制趙勢圖。趨勢圖的縱坐標表示試驗指標.橫坐標則是因素的水平.一般將不同因素的趙勢圖畫在一張圖中.以便于比較。利用Exc“畫趨勢圖.首先是建立工作表(如圖69所示)，第一列表示因素的不同水平，第二列表示對應(yīng)的仁需耍注意的是在不同因素之間至少應(yīng)留有一行的間隔.以免箱勢圖中所有的數(shù)據(jù)點相連；第一列中的數(shù)字要求為文本數(shù)字.輸入時要在數(shù)字前加單引號（英文輸入模式下八B因素的三個水平是按實際大小順序排列的，與水平編號順序不一致。選中圖69所示的工作表

22、.進入44圖表向?qū)А?，選擇折線圖的第4種子類型，即數(shù)據(jù)點折線圖。依據(jù)“圖表向?qū)А边M入“圖表選項”對話框（如圖6-10）,選擇紀標題”標簽在分類（X）軸下輸入.溫度/堿用量/kg催化劑種類”，在數(shù)值（F）軸下輸入“收率/%"，單擊“完成”后可成折線圖。圖610折線圖圖表選項對話框注意生成折線圖后.應(yīng)當對橫軸（分類軸）按圖11進行設(shè)置這樣才能使分類軸刻度線與各因素水平對應(yīng)。為了讓分類軸標題與坐標軸的數(shù)字對齊，可選中分類軸（橫軸）標題,在三因素名稱之間輸入或刪除空格。5.判別分析例：為研究舒張期血壓與血漿膽固醇對冠心病的作用，調(diào)查了50-59歲的女冠心病人15名和正常人16名。他們的舒張期血壓（xl）與血漿膽固醇（x2）數(shù)據(jù)列在data.xls的SheetG中。試用判別分析法建立判別冠心病人與正常人的判別函數(shù)。1）