Excel在双因素等重复试验方差分析中的应用

1、Excel在双因素等重复试验方差分析中的应用摘要：本论文旨在说明如何简单地将双因素等重复试验方差分析通过Excel软件来实现，使读者了解如何将数理统计同计算机技术相结合的一种方式。关键词：方差分析、双因素等重复试验、Excel软件。引言：方差分析是数理统计中的基本方法之一，是工农业生产和科学研究中分析数据的一种重要方法。例如在化工生产过程中，众多因素会影响到产品的数量和质量，有些因素影响较大，有些较小，为了保证优质高产，就需要找出对产品数量和质量影响显著的因素，因此，就需要进行试验。方差分析就是根据试验结果进行分析、推断各相关因素对试验结果的影响是否显著的有效方法，而往往实际需要分析的数据量庞

2、大复杂，人工计算难以适应其速度、精度的要求，就需要引入计算机技术的辅助。Excel是Microsoft Office家族中的一款应用软件，是一个功能多、技术先进、使用方便的表格式数据综合管理和分析系统，函数库丰富，制图功能较好，可以进行数据处理、统计分析和决策辅助。将Excel软件应用于方差分析，将使得处理问题的数据规模和复杂性程度极大地提高，精度也更为准确，同时方便省时，结果直观了然。而双因素等重复试验方差分析在几种简单类型的方差分析中稍微复杂些，计算量更大，更加有必要用Excel来处理。原理： SE称为误差平方和，SA、SB分别称为因素A、因素B的效应平方和，SAB称为A、B交互效应平方和

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）。因此可以按如下方法计算：ST=2ijk，SA=，SB=，SAB=（）SASB，SE= STSASBSAB 。因此均方可表示为=，=，=，=。因此F比可以表示为FA=，FB=，F AB= 。例：一枚火箭使用四种燃料，三种推进器作射程试验，每种燃料与每种推进器的组合各发射火箭两次，得射程如下表所示（以海里计）：推进器（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这里的试验指标是射程。燃料和推进器是因素，它们分别有4个、3个水平。这是一个双因素的试验。试在显著性水平0.05下，检验不同燃料（因素A）、不同推进器（因素B）下的射程是否有显著性差异？交互作用是否显著？解：需检验假设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.的计算如下表: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表中括弧内的数是Tij.。现在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 。得方差分析表如下：方差来源平方和自由度均方F比因素A（燃料）261. 67500387. 2250FA=4. 42因素B（推进器）370. 980832185. 4

7、904FB=9. 39交互作用AB1768. 692506294. 7821FAB=14. 9误差236. 950001219. 7458总和2638. 2983323由于F0.05（3,12）=3.49<FA，F0.05（2,12）=3.89<FB，F0.05（6,12）=3.00<FAB，故拒绝假设H01 ，H02 ，H03，即认为燃料和推进器这两个因素及其交互作用对射程的影响都是显著的。以上为人工计算得到的结果，虽然结果准确，但可见步骤繁杂，计算量较为庞大，下面应用Excel来计算处理这道题，以说明其精简性、优越性。将数据输入到Excel中，如下图所示：打开“方差分析：

8、可重复双因素分析”，选取数据输入范围为“A1：D9”，键入“每一样本的行数”为“2”，确定=0.05，单击确定，即可得到下图的方差分析表。这里样本为B因素效应，列为A因素效应，交互为A×B效应，内部为误差，SS表示平方和，df表示自由度，MS表示均方，F表示F比，P-value表示截尾概率，F crit表示在显著性水平下的临界分位点。实验与分析：要用Excel来进行方差分析，首先就必须详细了解这项功能的详细操作步骤，然后结合方差分析的原理来对结果进行分析。以Microsoft Office 2007为例，下面以一个数据样本实例进一步说明用Excel处理分析的流程。工业产品推销员的回报

9、最近10年，工业产品销售（Industrial Distribution）一直在研究工业产品推销员的回报问题。在1997年年薪的调查中，358名回答者的结果表明，有27%的回答者在销售额超过4000万美元的公司工作，其中典型的工业产品推销员在销售额为1200万美元的公司工作，那些在中小型公司（销售额在600万2000万美元之间）工作的人比在大公司工作的人可获得较高的收益，薪水最低的雇员在销售额不足100万美元的公司工作。1996年典型的户外推销员的收入为50000美元，而典型的室内推销员只有30000美元的收入。假设在较大的旧金山地区工业产品推销员的一个分会，进行了关于会员资格的一次调查，以研

10、究雇员资历与在户外或室内场所推销的人的年薪之间是否有关系。在这个调查中，被调查者指定为三种资历水平：低（110年）、中（1120年）、高（21年及以上），所得资料如下：单 位： 美 元观察者序号年薪场所资历观察者序号年薪场所资历观察者序号年薪场所资历128938室内中4154383户外中8151863户外高227694室内中4236128户外低8256750户外中345515户外低4329122室内低8333749室内高427031室内中4453710户外高8427638室内中537283户外低4533814室内低8537675室内中632718室内低4634276室内低8626027室内高7

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

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

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

14、外高11653702户外中3740546户外低7742237户外低11758131户外中3834057室内中7837225户外低11832788室内高3941024户外低7933866室内高11928070室内中4034457室内高8052311户外高12035259户外低在显著性水平0.05下，用方差分析法检验属于场所、资历和交互作用的显著差异？解：实例中的试验目标是年薪，场所和资历是因素，它们都有2个、3个水平。这是一个双因素可重复试验方差分析。为使用Excel进行分析，首先将原始数据输入到工作表sheet2的单元格区域A1：D121。然后，根据原始数据筛选出工作场所分别是室内和户外，资历

15、为低、中、高推销员的年薪数据。操作步骤如下：1、单击单元格区域A1：D121内的任意一格。2、单击“数据”菜单，点击“筛选”。3、可以看到筛选箭头会出现在被调查者、年薪、场所、资历的右边。如图所示：单击“场所”列右边的下拉箭头，选择“室内”。单击“资历”列右边的下拉箭头，分别单击“低、中、高”。分别出现工作场所为室内的资历为低、中、高的被调查者年薪数据清单。同样的步骤选出场所为户外，资历分别为低、中、高的年薪数据。4、将筛选出的年薪数据复制到工作表sheet1的单元格区域B2:D41。如图：接下来进行方差分析，操作步骤如下：1、单击“数据”菜单，选定“数据分析”命令，出现对话框如图所示。2、在对话框中选择“方差分析：可重复双因素分析”，单击“确定”按钮，出现对话框。3、在“方差分析：可重复双因素分析”对话框中，如图所示： 单击“确定”，得到如下的分析结果： 这里样本是为场地因素效应，列为资历因素效应，交互为场地因素和资历因素的交互效应，内部为误差。从方差分析结果表中可以看到：F0.05（1,114）=3.924330231<751.3599289=F场地，F0.05（2,114）=3.075852636<65.85596424=F资历，F0.05（2,114）=3.