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

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

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

3、的線性回歸直線的斜率。語(yǔ)法結(jié)構(gòu)：SLOPE(known_ySknovm_xs)相關(guān)系數(shù)函數(shù)RSQ功能:返回根據(jù)known_yt和known_xt中數(shù)據(jù)點(diǎn)計(jì)算得出的相關(guān)系數(shù)的平方。語(yǔ)法結(jié)構(gòu)：RSQ(known_ys,known_xs)試比較圖表法和函數(shù)法呑算得出的二元線性回歸方程是否一致。多元線性回歸分析例：一家房地產(chǎn)評(píng)估公司想對(duì)某城市的房地產(chǎn)銷(xiāo)售價(jià)格(y)與地產(chǎn)的評(píng)估價(jià)值(xl)和使用面積(x2)建立一個(gè)模型，一邊對(duì)銷(xiāo)售價(jià)格作出合理的預(yù)測(cè)。為此收集20棟住宅的房地產(chǎn)評(píng)估數(shù)據(jù)(data.xls的Sheet2)o由于本問(wèn)題有兩個(gè)自變量，因此需要使用多元線性回歸，需要借助于Excel的數(shù)據(jù)分析功能。

4、點(diǎn)擊“數(shù)據(jù)分析”，跳出回歸分析對(duì)話框；填充應(yīng)變量y和自變量xl,x2對(duì)應(yīng)的區(qū)域和輸出區(qū)域，如下圖:區(qū)）回歸3）點(diǎn)擊確定后將出現(xiàn)多元線性回歸的結(jié)果，對(duì)于結(jié)果（如下圖a,b,c）的一些重要項(xiàng)解釋如下：回歸統(tǒng)卜MultipleR0.860941385RSquare0.741220068AdjustedRSquare0.71077537標(biāo)準(zhǔn)誤差1331.729603觀測(cè)值20圖a方差分析dfSSMSFSignificanceF回歸分析286357011.4843178505.7424.346441.02308E-05殘差1730149563.521773503.737總計(jì)19116506575圖bCo

5、efficients標(biāo)準(zhǔn)誤差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表示此線性回歸結(jié)果顯著，及回歸方程可信，房地產(chǎn)的銷(xiāo)售價(jià)格與地產(chǎn)估價(jià)和使用面積是有關(guān)系的。圖c中的Coefficients為回歸方程的系數(shù)，因此，回歸結(jié)果為y=285.0094+1.5598x1+03145x2,在使用面積不變的情況下，地產(chǎn)估價(jià)每增加1萬(wàn)元,房產(chǎn)銷(xiāo)售的平均價(jià)格就會(huì)提高1.5598萬(wàn)元；在房地產(chǎn)估價(jià)不變的條件下，使用面積每增加1平方米，房產(chǎn)銷(xiāo)售的平均價(jià)格就會(huì)提高0.3145元；圖a中AdjustedRSquare為調(diào)整復(fù)測(cè)定系數(shù)，本例中約為0.71,它表示兩個(gè)變量xl

7、,x2對(duì)導(dǎo)致結(jié)果y的貢獻(xiàn)，也就是說(shuō)還有導(dǎo)致結(jié)果y的原因中有29%是由除了xl,x2以外的因素造成的。習(xí)題：在黃罠提取工藝的研究中，選擇了前煮時(shí)間、煎煮次數(shù)和加水量進(jìn)行考察，實(shí)驗(yàn)數(shù)據(jù)見(jiàn)data.xls的Sheet3,試對(duì)實(shí)驗(yàn)數(shù)據(jù)進(jìn)行多元線性回歸，對(duì)結(jié)果進(jìn)行討論。logistic回歸分析質(zhì)量性狀（qualitativecharacter）是指能觀察到而不能直接測(cè)量的，只能用文字來(lái)描述其特征的性狀，如食品顏色、風(fēng)味等等。這類(lèi)性狀本身不能直接用數(shù)值表示，要獲得這類(lèi)性狀的數(shù)據(jù)資料，須對(duì)其觀察結(jié)果作數(shù)量化處理。例如：是否抽煙，是否患病。釆用線性回歸分析顯然不太合適，而logistic回歸能較好地解決質(zhì)量性

8、狀分析的問(wèn)題。例：我們有一組數(shù)據(jù)，45個(gè)觀測(cè)值，四個(gè)變量，包括：age（年齡，數(shù)值型）；vision（視力狀況，分類(lèi)型，1表示好，0表示有問(wèn)題）；drive（駕車(chē)教育，分類(lèi)型，1表示參加過(guò)駕車(chē)教育，0表示沒(méi)有）和一個(gè)分類(lèi)型輸出變量accident（去年是否出過(guò)事故，1表示出過(guò)事故，0表示沒(méi)有）。我們的目的就是要考察前三個(gè)變量與發(fā)生事故的關(guān)系。數(shù)據(jù)見(jiàn)data.xls的Sheet4.3.1計(jì)算優(yōu)勢(shì)比優(yōu)勢(shì)（odds）是指某影響因素控制在某種水平時(shí)，事件發(fā)生率與事件不發(fā)生率的比值，即P/（1-P）o某影響因素的兩個(gè)不同水平的優(yōu)勢(shì)的比值稱(chēng)為優(yōu)勢(shì)比，如某影響因素的一個(gè)水平為cl,另一個(gè)水平為co,則這兩個(gè)

9、水平的優(yōu)勢(shì)比為：結(jié)果吸煙不吸煙合計(jì)食管癌患者309(a)126(b)435非食管癌患者208(c)243(d)451合計(jì)517(a+c)369(b+d)886OR=例如：吸煙與食管癌關(guān)系的病例對(duì)照調(diào)查結(jié)果。吸煙的優(yōu)勢(shì)二型竺“49208/517非吸煙的優(yōu)勢(shì)=126/369243/369=0.52149OR=-=2.870.52OR表示影響因素對(duì)事件發(fā)生的影響方向和影響能力大小。0/?1表示該因素取值越大，事件發(fā)生的概率越大，乂稱(chēng)危險(xiǎn)因素。0/?1表示該因素取值越大，事件發(fā)生的概率越小，乂稱(chēng)保護(hù)因素。ORJ表示該因素與事件的發(fā)生無(wú)關(guān)。試計(jì)算視力和駕車(chē)教育對(duì)發(fā)生事故的優(yōu)勢(shì)比。3.2SPSS的安裝1）

10、啟動(dòng)虛擬機(jī)，并拷貝安裝文件至虛擬機(jī)中；2）蘭瞬省方式安裝SPSS,最后在注冊(cè)環(huán)節(jié)點(diǎn)取消即可；3）運(yùn)行安裝文件夾下spss_19_patch_V2目錄中的SPSS19crack20100825.exe即可獲得破解過(guò)的SPSSoSPSS數(shù)據(jù)的導(dǎo)入在spss中導(dǎo)入Excel文件中數(shù)據(jù)方法如下：點(diǎn)擊SPSS中的文件一打開(kāi)一數(shù)據(jù),文件類(lèi)型選*.xls,*xlsx,選擇實(shí)驗(yàn)扌旨導(dǎo)文件夾中的data.xls,確定，然后工作表選Sheet4即可。SPSS用于logistic回歸1）數(shù)據(jù)導(dǎo)入以后，菜單欄上依次點(diǎn)擊：分析一回歸一二元logistic回歸，打開(kāi)相應(yīng)對(duì)話框?qū)υ捒颉Ｎ妒掳捶治?saw數(shù)據(jù)集1一IB1SP

11、SSStatistics數(shù)據(jù)編輸器文件(編輯旦視圖凹數(shù)據(jù)(D)轉(zhuǎn)換0)分析回直銷(xiāo)勉圖形實(shí)用程序)W(W)幫助空為H3發(fā)生爭(zhēng)故112111770報(bào)吿描述統(tǒng)計(jì)表E比較均垃遁一股線性鍥型廠叉線性魁混合模型辺相關(guān)回歸遲)對(duì)數(shù)線性模型(2)神經(jīng)網(wǎng)絡(luò)分類(lèi)()降維度雖(S)非臭數(shù)檢驗(yàn)刨預(yù)測(cè)生存函數(shù)多垂響應(yīng)因缺矢?jìng)幏諻-多重歸因復(fù)雜抽樣質(zhì)量控制SRoc曲線因也IUS自動(dòng)線性建模麗線性0曲線估計(jì)跑咅附啟小平方回二元Logistic.多項(xiàng)Logistic.斕有序HProbit斕非線性刮碣杖車(chē)估計(jì)鹵翩斶小二乘法最佳尺度(CATREG)2)將因變量和自變量放入格子的列表里，如圖所示，上面的是因變量，下面的是自變量，我

12、們看到這里有三個(gè)自變量：年齡、視力、駕車(chē)訓(xùn)練。點(diǎn)擊確定，SPSS將計(jì)算得到分析結(jié)果，主要觀察結(jié)果中最后一張表，如圖：方稈中的孌量BS.E,WaisdfSig.Exp(B)EXP(B)的95號(hào)C,L下限上限步騾“年齡.007.1291.7191.007.9711.043視力-1.7107恥5.8721.015.181.045.721駕車(chē)訓(xùn)練-1.494.7054.4961.224.056.8931.5211.0572.0711.1504.579a在步驟1中輸入的變雖年齡，視力駕車(chē)訓(xùn)練.其中，Sig.值是回歸系數(shù)的顯著性檢驗(yàn)p值，可以看出年齡系數(shù)對(duì)應(yīng)的p值大于0.05,不顯著因此不將其納入回歸方程

13、方程，而視力和駕車(chē)系數(shù)顯著，常數(shù)不用進(jìn)行顯著性檢驗(yàn)。因此根據(jù)Logistic回歸方法可以得出估算發(fā)生事故的概率p的公式如下：1.521一1.710X11.494X2假設(shè)有個(gè)人，視力沒(méi)問(wèn)題但沒(méi)有受過(guò)駕車(chē)教育(XbX2=0),代入方程，有l(wèi)og(p/(l-p)=-0.189,再p/(l-p)=exp(-0.189)=0.8278,p=0.4529,即我們說(shuō)這人發(fā)生事故的概率為0.4529;乂另一個(gè)，視力有問(wèn)題但沒(méi)受過(guò)駕車(chē)教育(Xl=0zX2=l),p=0.5068,因此視力更重要；如果視力乂有問(wèn)題乂沒(méi)受過(guò)駕車(chē)教育(Xl=0,X2=0),p=0.8207,說(shuō)這人發(fā)生事故的概率為0.8207,非常容易

14、發(fā)生事故。習(xí)題：data.xls的Sheet5是40名肺癌病人的生存資料。其中XI：生活行動(dòng)能力評(píng)分（KLOO）；X2：病人年齡；X3：由診斷到進(jìn)入研究時(shí)間（月）；X4：腫瘤類(lèi)型（“0表示鱗癌、T表示小型細(xì)胞癌、“2表示腺癌、“3表示大型細(xì)胞癌）；X5：兩種化療方法（T表示常規(guī)、“0”表示實(shí)驗(yàn)新法）；Y：病人的生存時(shí)間（“0”：表示生存時(shí)間短，即生存時(shí)間小于200天；“1：表示生存時(shí)間長(zhǎng)，即生存時(shí)間大于或等于200天。）根據(jù)上述分析流程對(duì)數(shù)據(jù)進(jìn)行分析。4.使用EXCEL正交設(shè)計(jì)與分析請(qǐng)解決以下題目的要求：1.某產(chǎn)品的產(chǎn)量取決F3個(gè)因索才】七片很據(jù)經(jīng)驗(yàn)，因素巧的變化范圈為6080,因素工z的變化

15、范圍為121.5因素工3的變化范圍為020.3.還要考慮因素工】與工2之間的交互作用試驗(yàn)指標(biāo)，為產(chǎn)鍛.越髙越好.選用正交表4（27）進(jìn)行一次回歸正交試臉試驗(yàn)結(jié)果（產(chǎn)量/kg依次為：66.72.71.76,70.74,62.69。試確定因素主次和優(yōu)方案.正交表1_8（2請(qǐng)見(jiàn)data.xls文件的Sheet9.EXCEL進(jìn)行正交設(shè)計(jì)分析以及表頭設(shè)計(jì)的資料如下：6.2.5Excel在直觀分析中應(yīng)用正交試驗(yàn)設(shè)計(jì)的直觀分析關(guān)鍵是計(jì)算K,代和這些都可利用Excel的公式和函數(shù)功能進(jìn)行計(jì)算.下面通過(guò)例68來(lái)說(shuō)明。例68某工廠為了提髙某產(chǎn)品的收率.根據(jù)經(jīng)臉和分析，認(rèn)為反應(yīng)溫度.堿用量和催化劑種類(lèi)可能會(huì)對(duì)產(chǎn)品的

16、收率造成較大的影響對(duì)這3個(gè)因索各取3種水平，列于喪627中。將因素A.B,C依次安排在正交表U（34）的1.2.3列.不考慮因素間的交互作用。9個(gè)試驗(yàn)結(jié)果y（收率/%）依次為，51.71.58.82,69.59.77.85,84。試用直觀分析法確定因索主次和優(yōu)方案.并畫(huà)岀趟勢(shì)圖。6-27例6因素水平衰水平（A）fi/r32”或FpplesJsum_range需要求和的實(shí)際單元格范圍。K值表示的是同一水平下對(duì)應(yīng)試驗(yàn)結(jié)果之和以A因素列的K?計(jì)算為例Kz的計(jì)算公式為=SUMIF（B$2:B$10,2,$F$2：$F$10）（如圖6-8）其中“B$2：B$10力表示用于條件判斷的單元格區(qū)域“2”表示在

17、B$2:B$10范圍內(nèi)等于2的單元格“$F$2:$F$10”表示求和實(shí)際單元格范圍.選中該公式.然后水平拖動(dòng)填充柄就可計(jì)算r2!_L十33二SUIIF(B$2：B$1CLZJF|?;$F$12)F3456789?_LTTuBT2312DC723123312-JL5171588269597785r_J:Ki180210195204K2210|_225237207刖k3246201204225k】60706568場(chǎng)70757969k382676875極差R228147圖68正交試驗(yàn)設(shè)計(jì)直觀分析1238480608570(90824875A67T.85一匹甲65$乙空丙68j圖6-9趨勢(shì)圖工作表出后

18、三列的K”為了保證在填充柄水平拖動(dòng)的過(guò)程中求和的實(shí)際范圍不變，就要求行和列都加上絕對(duì)引用符號(hào)$,即$F$2:$F$10；注意條件判斷單元格區(qū)域B$2；B$10行號(hào)2和10最好絕對(duì)應(yīng)用這樣往下拖動(dòng)填充柄.就可將該公式復(fù)制到下一行的單元格.而行號(hào)范出不變，對(duì)復(fù)制到下一行的公式中的“2円改成“3”.然后再水平填充，就可計(jì)算所有的K3了。&的計(jì)算。怡就是K的算術(shù)平均值，在本例中怡=孚，由于在B14：E16范圍內(nèi)，每個(gè)單元格的公式都-樣故町采用數(shù)組公式法。首先選中單元格區(qū)域1314：E16,在該區(qū)域的左上角第一個(gè)單元格中輸入：=B11:E13/3,再同時(shí)按Shift+Ctrl+Enter,即可在B14；

19、E16范圍內(nèi)顯示圖6-8所示結(jié)果。極差R的計(jì)算。圖68中.在B17單元格中輸入=MAX(B14:B16)-M1N(B14:B16),回車(chē)后得到22.然后選中該單元格.向右拖動(dòng)填充柄，就可計(jì)算出后三列的極差。在本例中是按怡來(lái)計(jì)算極差R的，也町以按K計(jì)算R.這時(shí)應(yīng)在B17單元格中輸入：=MAX(B11:B13)-M1N(B11:B13)o對(duì)于同一張正交表，上述計(jì)算K,和極差R的公式不會(huì)隨試驗(yàn)指標(biāo)y的取值而發(fā)生變化，所以當(dāng)你下次用到時(shí)U(34)只需將圖中F列的y值換掉新的K.殳和極差R會(huì)同時(shí)計(jì)算出來(lái)極大地減少了工作童，起到了一勞永逸的效果。繪制趙勢(shì)圖。趨勢(shì)圖的縱坐標(biāo)表示試驗(yàn)指標(biāo).橫坐標(biāo)則是因素的水平

20、.一般將不同因素的趙勢(shì)圖畫(huà)在一張圖中.以便于比較。利用Exc“畫(huà)趨勢(shì)圖.首先是建立工作表(如圖69所示)，第一列表示因素的不同水平，第二列表示對(duì)應(yīng)的仁需耍注意的是在不同因素之間至少應(yīng)留有一行的間隔.以免箱勢(shì)圖中所有的數(shù)據(jù)點(diǎn)相連；第一列中的數(shù)字要求為文本數(shù)字.輸入時(shí)要在數(shù)字前加單引號(hào)（英文輸入模式下八B因素的三個(gè)水平是按實(shí)際大小順序排列的，與水平編號(hào)順序不一致。選中圖69所示的工作表.進(jìn)入44圖表向?qū)А?，選擇折線圖的第4種子類(lèi)型，即數(shù)據(jù)點(diǎn)折線圖。依據(jù)“圖表向?qū)А边M(jìn)入“圖表選項(xiàng)”對(duì)話框（如圖6-10）,選擇紀(jì)標(biāo)題”標(biāo)簽在分類(lèi)（X）軸下輸入.溫度/堿用量/kg催化劑種類(lèi)”，在數(shù)值（F）軸下輸入“收率/%，單擊“完成”后可成折線圖。圖610折線圖圖表選項(xiàng)對(duì)話框注意生成折線圖后.應(yīng)當(dāng)對(duì)橫軸（分類(lèi)軸）按圖11進(jìn)行設(shè)置這樣才能使分類(lèi)軸刻度線與各因素水平對(duì)應(yīng)。為了讓分類(lèi)軸標(biāo)題與坐標(biāo)軸的數(shù)字對(duì)齊，可選中分類(lèi)軸（橫軸）標(biāo)題,在三因素名稱(chēng)之間輸入或刪除空格。5.判別分析例：為研究舒張期血壓與血漿膽固醇對(duì)冠心病的作用，調(diào)查了50-59歲的女冠心病人15名和正常人16名。他們的舒張期血壓（xl）與血漿膽固醇（x2）數(shù)據(jù)列在data.xls的SheetG中。試用判別分析法建立判別冠心病人與正常人的判別函數(shù)。1）數(shù)據(jù)導(dǎo)入以后，菜單欄上依次