方差分析F值频率分布的EXCEL抽样拟合.doc

方差分析F值频率分布的EXCEL抽样拟合 张庆远 张庆远 男 讲师 流行病学与医学统计学 南阳医学高等专科学校摘 要 利用EXCEL文件VBA功能，制作演示程序，假设从相同或不同的三个已知正态分布总体中各自随机抽取一个样本，组成单因素方差分析样本组，经过逐次方差分析F值计算及循环抽样，得到多个F值，由对F值大小及频数分布规律的观察，帮助学生理解来自同一总体三个样本所计算的F值往往较小，而来自不同总体（存在显著性差异总体）三个样本所计算F值往往较大，辅助于方差分析的理论教学。关键词 方差分析 EXCEL 抽样模拟 F值在医学统计学教学中，方差分析内容占有重要地位。为更好的配合课堂理论讲授，在深入研究方差分析的基本原理的基础上，结合EXCEL-VAB的应用特点，编制方差分析F值频数分布的抽样模拟文件，以便帮助学生更好更快地理解F值的分布规律，从而对方差分析的结果判断能够做到正确的理解及应用。其具体设计如下：1 设计思路以单因素方差分析为总设计思路，先假设三个已知正态分布的总体， 确定其相应总体均数和总体标准差值的大小；然后按给定的样本含量和需要保留的小数位数，分别从三个总体出各自抽出一个样本，将三个样本组成一个假定单因素分析的样本组，并对其进行单因素方差分析的相关计算，求出其F值；在此基础上，设置文件进行循环抽样，完成多次抽样得到多个F值，并对F值做出频数分布表，观察F值的频数分布，认识F值的概率分布规律。2 EXCEL抽样文件设计2.1 建立按钮控件首先建立一个EXCEL文件，命名为“方差分析F值频数分布拟合抽样文件”，打开该文件，在其中SHEET1工作薄中进行操作，点击“视图”-“工具栏”-“控件工具箱”，在其中单击“按钮”控件，向sheet1表中填加四个按钮，通过右键点击按钮，打开“属性”面板，将其“Caption”值依次命名为“建立基本框架”、“初次参数设定”、“抽取/更改样本”、“循环抽取F值”。2.2 输入按钮控件VBA代码的SUB子程序移动四个按钮控件到G17：I28区域位置，并调整其大小合适。左键双击第一个“建立基本框架”按钮，打开VBA代码编辑器，在相应SUB子程序中输入如下代码，其中下划线行不用重复输入。Private Sub CommandButton1_Click()Application.ScreenUpdating = FalseRange(A1) = 输入参数Range(A2) = 总体均数Range(A3) = 总体标准差Range(A4) = 样本含量Range(A5) = 保留小数位数Range(B1) = 总体1Range(C1) = 总体2Range(D1) = 总体3Range(E1) = 抽样次数Range(F1) = 次数记录器Range(A6) = 序号Range(B6) = 样本1Range(C6) = 样本2Range(D6) = 样本3Range(E6) = 样本均数1Range(F6) = 样本均数2Range(G6) = 样本均数3Range(H6) = 全部值均数Range(e9) = 变异来源Range(f9) = SSRange(g9) = 自由度Range(h9) = MSRange(i9) = F值Range(e10) = 总变异Range(e11) = 组间变异Range(e12) = 组内变异Range(K1) = 所抽F值中最大值Range(K2) = 所抽F值中最小值Range(K3) = 所抽F值样本均数Range(K4) = 所抽F值样本标准差Range(K5) = 所抽F值频数表组距Range(L7) = F值频数分布表Range(K8) = 组段界值Range(L8) = 分组Range(M8) = 频数Range(L20) = 合计With Worksheets(1).Range(E7).Formula = =AVERAGE(B7:B2000).Range(f7).Formula = =AVERAGE(c7:c2000).Range(g7).Formula = =AVERAGE(D7:d2000).Range(h7).Formula = =AVERAGE(B7:d2000).Range(f10).Formula = =STDEV(B7:D2000)2*(SUM(B4:D4)-1).Range(f11).Formula = =(E7-H7)2*B4+(F7-H7)2*C4+(G7-H7)2*D4.Range(f12).Formula = =f10-f11.Range(g10).Formula = =sum(b4:d4)-1.Range(g11) = 2.Range(g12).Formula = =g10-g11.Range(h10).Formula = =f10/g10.Range(h11).Formula = =f11/g11.Range(h12).Formula = =f12/g12.Range(i11).Formula = =h11/h12.Range(l1).Formula = =max(j:j).Range(l2).Formula = =min(j:j).Range(l3).Formula = =average(j:j).Range(l4).Formula = =stdev(j:j).Range(l5).Formula = =(l1-l2)/10.Range(K9).Formula = =L2Range(K10).Select ActiveCell.FormulaR1C1 = =R-1C+R5C1 Selection.AutoFill Destination:=Range(K10:K19), Type:=xlFillDefault Range(K10:K19).Select.Range(L9).Formula = =TEXT(K9,0.0000)&- Range(L9).Select Selection.AutoFill Destination:=Range(L9:L18), Type:=xlFillDefault Range(L9:L18).Select.Range(L19).Formula = =TEXT(K19,0.0000)& - &TEXT(K19+L$5,0.0000).Range(M9).Formula = =COUNTIF(J:J,=&K9)-COUNTIF(J:J,=&K10) Range(M9).Select Selection.AutoFill Destination:=Range(M9:M18), Type:=xlFillDefault Range(M9:M18).Select.Range(M19).Formula = =COUNTIF(J:J,=&K19).Range(M20).Formula = =SUM(M9:M19)End WithRange(K8:K19).Font.ColorIndex = 2Application.ScreenUpdating = TrueEnd Sub在“初次参数设定”按钮控件的SUB子程序中输入如下VBA代码：Private Sub CommandButton2_Click()Application.ScreenUpdating = FalseRange(b2:d2) = 100Range(b3:d3) = 5Range(b4:d4) = 15Range(b5:d5) = 2Range(e2) = 50 Range(B2:D5,E2).Select Range(E2).Activate With Selection.Interior .ColorIndex = 35 .Pattern = xlSolid End WithApplication.ScreenUpdating = TrueEnd Sub在“抽取/更改样本”按钮控件的SUB子程序中输入如下VBA代码：Private Sub CommandButton3_Click()Application.ScreenUpdating = FalseDim h, g, y, th = Range(b4) + 6g = Range(c4) + 6y = Range(d4) + 6Range(b7:b2000).Value = Range(c7:c2000).Value = Range(d7:d2000).Value = Range(a7:a2000).Value = With Worksheets(1).Range(Cells(7, 2), Cells(h, 2).Formula = =Round(NormInv(RAND(), $b$2, $b$3), $b$5).Range(Cells(7, 3), Cells(g, 3).Formula = =Round(NormInv(RAND(), $c$2, $c$3), $c$5).Range(Cells(7, 4), Cells(y, 4).Formula = =Round(NormInv(RAND(), $d$2, $d$3), $d$5)Range(e4).Formula = =max(b4:c4)Range(E4).Font.ColorIndex = 2End Witht = Range(e4) + 6For i = 7 To tCells(i, 1) = i - 6NextApplication.ScreenUpdating = TrueEnd Sub在“循环抽取F值”按钮控件的SUB子程序中输入如下VBA代码：Private Sub CommandButton4_Click()Dim d, h, td = Range(e2) + 1Range(J:J) = Range(J1) = 抽样F值For i = 2 To dApplication.ScreenUpdating = Falseh = i - 1Cells(i, 10) = Cells(11, 9).ValueRange(f2) = hApplication.ScreenUpdating = TrueNextEnd Sub在VBA代码输入完成后，关闭代码窗口，鼠标左键依次单击打开“工具”菜单下“宏”“安全性”，选“中等”，然后关闭文件，关闭时点“是”保存文件。2.3 文件的运行双击打开运行文件，打开时选“启用宏”，打开文件后依次点击四按钮，完成相应文本内容填充、初次总体参数设定、样本抽样/更改设定及首次循环F值抽样，为能直观观察F值频数分布，可以“F值频数分布表”中的“分组”栏为横轴，以“频数”栏数值为纵轴做直方图。通过更改浅绿色区域中相应参数的设置，可分别完成在同一总体中抽样或是在不同总体中抽样时F值的频数分布结果。完成后如下图所示。2.3 抽样效果拟合评价 为评价使用本程序所抽的样本是否符合F值分布的规律，假设某已知总体：为100，为5，样本含量分别为：10，15，20，循环抽样100次进行一次F值的模拟抽样。将所抽F值频数分布与总体F值概率分布进行拟合检验，结果如下表，所抽F样本值符合F值总体概率分布，无显著性差异（=7.049 P=0.7210.05）,说明抽样拟合完好。F值实际频数与理论频数拟合优度检验分组实际频数理论频数0.0216-4541.664590.5842-1923.463081.1468-1213.407711.7094-107.7689112.2720-34.5615082.8346-52.7122373.3972-11.6321663.9598-20.9935354.5224-10.6114535.0850-10.3802765.6476-10.672623注：=7.049 P=0.7213 讨论在使用本文件中需注意以下方面：1） 在输入VBA代码时，需要在英文输入法下进行。2） 其中浅绿色区域为相关基本参数设定，可以根据不同情况进行更改，更改后要在其它空白格子中单击，以确定数值输入；特别是在修改完成样本含量值后，一定要单击“抽取/更改样本”按钮，使相应样本抽样个数发生更新。3） 如果点按按钮时不发生程序运行动作，可以查看“工具”“宏”“安全性”中相关设置，将“安全性”设置为低，再次保存文件并打开后即可运行。本抽样文件目的在于讲明方差分析中 “如果3个总体均数相等，F的数值不会太大。相反，如果F的数值很大，“