Excel在雙因素等重復(fù)試驗(yàn)方差分析中的應(yīng)用

1、Excel在雙因素等重復(fù)試驗(yàn)方差分析中的應(yīng)用摘要：本論文旨在說(shuō)明如何簡(jiǎn)單地將雙因素等重復(fù)試驗(yàn)方差分析通過(guò)Excel軟件來(lái)實(shí)現(xiàn)，使讀者了解如何將數(shù)理統(tǒng)計(jì)同計(jì)算機(jī)技術(shù)相結(jié)合的一種方式。關(guān)鍵詞：方差分析、雙因素等重復(fù)試驗(yàn)、Excel軟件。引言：方差分析是數(shù)理統(tǒng)計(jì)中的基本方法之一，是工農(nóng)業(yè)生產(chǎn)和科學(xué)研究中分析數(shù)據(jù)的一種重要方法。例如在化工生產(chǎn)過(guò)程中，眾多因素會(huì)影響到產(chǎn)品的數(shù)量和質(zhì)量，有些因素影響較大，有些較小，為了保證優(yōu)質(zhì)高產(chǎn)，就需要找出對(duì)產(chǎn)品數(shù)量和質(zhì)量影響顯著的因素，因此，就需要進(jìn)行試驗(yàn)。方差分析就是根據(jù)試驗(yàn)結(jié)果進(jìn)行分析、推斷各相關(guān)因素對(duì)試驗(yàn)結(jié)果的影響是否顯著的有效方法，而往往實(shí)際需要分析的數(shù)據(jù)量龐

2、大復(fù)雜，人工計(jì)算難以適應(yīng)其速度、精度的要求，就需要引入計(jì)算機(jī)技術(shù)的輔助。Excel是Microsoft Office家族中的一款應(yīng)用軟件，是一個(gè)功能多、技術(shù)先進(jìn)、使用方便的表格式數(shù)據(jù)綜合管理和分析系統(tǒng)，函數(shù)庫(kù)豐富，制圖功能較好，可以進(jìn)行數(shù)據(jù)處理、統(tǒng)計(jì)分析和決策輔助。將Excel軟件應(yīng)用于方差分析，將使得處理問(wèn)題的數(shù)據(jù)規(guī)模和復(fù)雜性程度極大地提高，精度也更為準(zhǔn)確，同時(shí)方便省時(shí)，結(jié)果直觀了然。而雙因素等重復(fù)試驗(yàn)方差分析在幾種簡(jiǎn)單類(lèi)型的方差分析中稍微復(fù)雜些，計(jì)算量更大，更加有必要用Excel來(lái)處理。原理： SE稱為誤差平方和，SA、SB分別稱為因素A、因素B的效應(yīng)平方和，SAB稱為A、B交互效應(yīng)平方和

3、。ST，SE，SA，SB，SAB的自由度依次為rst1，rs（t1），r1，s1，（r1）（s1）。記T.=ijk，Tij.=ijk（i=1,2，r；j=1,2，s），Ti.=ijk（i=1,2，r），T.j.=ijk （j=1,2，s）。因此可以按如下方法計(jì)算：ST=2ijk，SA=，SB=，SAB=（）SASB，SE= STSASBSAB 。因此均方可表示為=，=，=，=。因此F比可以表示為FA=，F(xiàn)B=，F(xiàn) AB= 。例：一枚火箭使用四種燃料，三種推進(jìn)器作射程試驗(yàn)，每種燃料與每種推進(jìn)器的組合各發(fā)射火箭兩次，得射程如下表所示（以海里計(jì)）：推進(jìn)器（B）B1B2B3燃料（A）A158.252.

4、656.241.265.360.8A249.142.854.150.551.648.4A360.158.370.973.239.240.7A475.871.558.251.048.741.4這里的試驗(yàn)指標(biāo)是射程。燃料和推進(jìn)器是因素，它們分別有4個(gè)、3個(gè)水平。這是一個(gè)雙因素的試驗(yàn)。試在顯著性水平0.05下，檢驗(yàn)不同燃料（因素A）、不同推進(jìn)器（因素B）下的射程是否有顯著性差異？交互作用是否顯著？解：需檢驗(yàn)假設(shè)H01：1=2=r=0，H11：1，2，r 不全為零；H02：1=2=s=0，H12：1，2，s 不全為零；H03：11=12=rs=0，H13：11，12，rs不全為零。T.，Tij.，Ti

5、.，T.j.的計(jì)算如下表:A BB1B2B3Ti.A158. 252. 6 (110. 8)56. 241. 2 (97. 4)65. 360. 8 (126. 1)334. 3A249. 142. 8 (91. 9)54. 150. 5 (104. 6)51. 648. 4 (100)296. 5A360. 158. 3 (118. 4)70. 973. 2 (144. 1)39. 240. 7 （79. 9）342. 4A475. 871. 5 （147. 3）58. 251. 0 (109. 2)48. 741. 4 (90. 1)346. 6T.j.468. 4455. 3396. 1

6、1319. 8表中括弧內(nèi)的數(shù)是Tij.?，F(xiàn)在r=4，s=3，t=2，故有ST=（58.22+52.62+41.42）=2638. 29833，SA=（334.32+296.52+342.42+346.62）=261. 67500，SB=（468.42+455.32+396.12）=370. 98083，SAB=（110.82+91.92+90.12）SASB=1768.69250，SE= STSASB SAB=236.95000 。得方差分析表如下：方差來(lái)源平方和自由度均方F比因素A（燃料）261. 67500387. 2250FA=4. 42因素B（推進(jìn)器）370. 980832185. 4

7、904FB=9. 39交互作用AB1768. 692506294. 7821FAB=14. 9誤差236. 950001219. 7458總和2638. 2983323由于F0.05（3,12）=3.49<FA，F(xiàn)0.05（2,12）=3.89<FB，F(xiàn)0.05（6,12）=3.00<FAB，故拒絕假設(shè)H01 ，H02 ，H03，即認(rèn)為燃料和推進(jìn)器這兩個(gè)因素及其交互作用對(duì)射程的影響都是顯著的。以上為人工計(jì)算得到的結(jié)果，雖然結(jié)果準(zhǔn)確，但可見(jiàn)步驟繁雜，計(jì)算量較為龐大，下面應(yīng)用Excel來(lái)計(jì)算處理這道題，以說(shuō)明其精簡(jiǎn)性、優(yōu)越性。將數(shù)據(jù)輸入到Excel中，如下圖所示：打開(kāi)“方差分析：

8、可重復(fù)雙因素分析”，選取數(shù)據(jù)輸入范圍為“A1：D9”，鍵入“每一樣本的行數(shù)”為“2”，確定=0.05，單擊確定，即可得到下圖的方差分析表。這里樣本為B因素效應(yīng)，列為A因素效應(yīng)，交互為A×B效應(yīng)，內(nèi)部為誤差，SS表示平方和，df表示自由度，MS表示均方，F(xiàn)表示F比，P-value表示截尾概率，F(xiàn) crit表示在顯著性水平下的臨界分位點(diǎn)。實(shí)驗(yàn)與分析：要用Excel來(lái)進(jìn)行方差分析，首先就必須詳細(xì)了解這項(xiàng)功能的詳細(xì)操作步驟，然后結(jié)合方差分析的原理來(lái)對(duì)結(jié)果進(jìn)行分析。以Microsoft Office 2007為例，下面以一個(gè)數(shù)據(jù)樣本實(shí)例進(jìn)一步說(shuō)明用Excel處理分析的流程。工業(yè)產(chǎn)品推銷(xiāo)員的回報(bào)

9、最近10年，工業(yè)產(chǎn)品銷(xiāo)售（Industrial Distribution）一直在研究工業(yè)產(chǎn)品推銷(xiāo)員的回報(bào)問(wèn)題。在1997年年薪的調(diào)查中，358名回答者的結(jié)果表明，有27%的回答者在銷(xiāo)售額超過(guò)4000萬(wàn)美元的公司工作，其中典型的工業(yè)產(chǎn)品推銷(xiāo)員在銷(xiāo)售額為1200萬(wàn)美元的公司工作，那些在中小型公司（銷(xiāo)售額在600萬(wàn)2000萬(wàn)美元之間）工作的人比在大公司工作的人可獲得較高的收益，薪水最低的雇員在銷(xiāo)售額不足100萬(wàn)美元的公司工作。1996年典型的戶外推銷(xiāo)員的收入為50000美元，而典型的室內(nèi)推銷(xiāo)員只有30000美元的收入。假設(shè)在較大的舊金山地區(qū)工業(yè)產(chǎn)品推銷(xiāo)員的一個(gè)分會(huì)，進(jìn)行了關(guān)于會(huì)員資格的一次調(diào)查，以研

10、究雇員資歷與在戶外或室內(nèi)場(chǎng)所推銷(xiāo)的人的年薪之間是否有關(guān)系。在這個(gè)調(diào)查中，被調(diào)查者指定為三種資歷水平：低（110年）、中（1120年）、高（21年及以上），所得資料如下：?jiǎn)?位： 美 元觀察者序號(hào)年薪場(chǎng)所資歷觀察者序號(hào)年薪場(chǎng)所資歷觀察者序號(hào)年薪場(chǎng)所資歷128938室內(nèi)中4154383戶外中8151863戶外高227694室內(nèi)中4236128戶外低8256750戶外中345515戶外低4329122室內(nèi)低8333749室內(nèi)高427031室內(nèi)中4453710戶外高8427638室內(nèi)中537283戶外低4533814室內(nèi)低8537675室內(nèi)中632718室內(nèi)低4634276室內(nèi)低8626027室內(nèi)高7

11、54081戶外高4750869戶外中8737881戶外低823621室內(nèi)低4832549室內(nèi)中8850791戶外高947835戶外高4951762戶外中8936680戶外低1029768室內(nèi)中5035993戶外低9034768室內(nèi)高1127282室內(nèi)中5138362室內(nèi)中9131568室內(nèi)高1230632室內(nèi)低5228231室內(nèi)高9257622戶外中1338856戶外低5337115室內(nèi)高9350326戶外高1426827室內(nèi)中5431080室內(nèi)中9432719室內(nèi)高1526948室內(nèi)低5528392室內(nèi)中9532366室內(nèi)高1631588室內(nèi)中5636299戶外低9632670室內(nèi)低1743

12、858戶外低5728894室內(nèi)高9727072室內(nèi)低1838478戶外低5857794戶外中9843569戶外高1958846戶外中5931990室內(nèi)低9956526戶外中2034253室內(nèi)高6052403戶外高10057059戶外中2128464室內(nèi)低6153936戶外高10149374戶外高2258176戶外中6229282室內(nèi)中10226119室內(nèi)低2335949室內(nèi)高6353850戶外中10355696戶外中2427833室內(nèi)低6429592室內(nèi)高10431352室內(nèi)低2547914戶外高6531461室內(nèi)低10552622戶外高2658040戶外中6649389戶外高10644142

13、戶外低2739288戶外低6724422室內(nèi)低10742603戶外高2839562室內(nèi)高6861692戶外中10835561戶外低2927644室內(nèi)高6952356戶外高10926246室內(nèi)中3030959室內(nèi)低7033055室內(nèi)中11029891室內(nèi)低3163730戶外中7152820戶外高11162090戶外中3252683戶外中7252801戶外高11230482室內(nèi)高3331339室內(nèi)高7333053室內(nèi)中11328464室內(nèi)低3446345戶外低7453169戶外中11433568室內(nèi)中3538799室內(nèi)低7543560戶外低11533080室內(nèi)高3653074戶外高7641320戶

14、外高11653702戶外中3740546戶外低7742237戶外低11758131戶外中3834057室內(nèi)中7837225戶外低11832788室內(nèi)高3941024戶外低7933866室內(nèi)高11928070室內(nèi)中4034457室內(nèi)高8052311戶外高12035259戶外低在顯著性水平0.05下，用方差分析法檢驗(yàn)屬于場(chǎng)所、資歷和交互作用的顯著差異？解：實(shí)例中的試驗(yàn)?zāi)繕?biāo)是年薪，場(chǎng)所和資歷是因素，它們都有2個(gè)、3個(gè)水平。這是一個(gè)雙因素可重復(fù)試驗(yàn)方差分析。為使用Excel進(jìn)行分析，首先將原始數(shù)據(jù)輸入到工作表sheet2的單元格區(qū)域A1：D121。然后，根據(jù)原始數(shù)據(jù)篩選出工作場(chǎng)所分別是室內(nèi)和戶外，資歷

15、為低、中、高推銷(xiāo)員的年薪數(shù)據(jù)。操作步驟如下：1、單擊單元格區(qū)域A1：D121內(nèi)的任意一格。2、單擊“數(shù)據(jù)”菜單，點(diǎn)擊“篩選”。3、可以看到篩選箭頭會(huì)出現(xiàn)在被調(diào)查者、年薪、場(chǎng)所、資歷的右邊。如圖所示：?jiǎn)螕簟皥?chǎng)所”列右邊的下拉箭頭，選擇“室內(nèi)”。單擊“資歷”列右邊的下拉箭頭，分別單擊“低、中、高”。分別出現(xiàn)工作場(chǎng)所為室內(nèi)的資歷為低、中、高的被調(diào)查者年薪數(shù)據(jù)清單。同樣的步驟選出場(chǎng)所為戶外，資歷分別為低、中、高的年薪數(shù)據(jù)。4、將篩選出的年薪數(shù)據(jù)復(fù)制到工作表sheet1的單元格區(qū)域B2:D41。如圖：接下來(lái)進(jìn)行方差分析，操作步驟如下：1、單擊“數(shù)據(jù)”菜單，選定“數(shù)據(jù)分析”命令，出現(xiàn)對(duì)話框如圖所示。2、在對(duì)話框中選擇“方差分析：可重復(fù)雙因素分析”，單擊“確定”按鈕，出現(xiàn)對(duì)話框。3、在“方差分析：可重復(fù)雙因素分析”對(duì)話框中，如圖所示： 單擊“確定”，得到如下的分析結(jié)果： 這里樣本是為場(chǎng)地因素效應(yīng)，列為資歷因素效應(yīng)，交互為場(chǎng)地因素和資歷因素的交互效應(yīng)，內(nèi)部為誤差。從方差分析結(jié)果表中可以看到：F0.05（1,114）=3.924330231<751.3599289=F場(chǎng)地，F(xiàn)0.05（2,114）=3.075852636<65.85596424=F資歷，F(xiàn)0.05（2,114）=3.