第41卷第3期2020年9月
淮北师范大学学报（自然科学版）
Journal of Huaibei Normal University(Natural Sciences)
Vol.41No.3
Sep.2020卡方分布的Excel构建与模拟
查岭生，李俊，丁建华
（淮北师范大学生命科学学院，安徽淮北235000）
摘要：卡方分布是一种常见的概率分布，在生物统计学的教学中占有重要位置.为帮助学生深入地理解卡方分布的内涵，利用Excel软件中的函数与VBA编程功能对该分布曲线进行构建和模拟.该过程可以动态地展示卡方分布曲线随自由度变化而变化的基本规律，有助于激发学生的学习兴趣，有效提高课堂教学效果.
关键词：卡方分布；Excel；构建；模拟
中图分类号：O212.1文献标识码：C文章编号：2095-0691（2020）03-0092-05
0引言
卡方分布（Chi-square distribution，χ2）是一种常见的概率分布，在生物统计学的教学中占有重要位置.它的定义为：假设从标准正态总体中随机抽取k个独立变量，就会得到u1,u2,⋯,u k，把这k个随机变量先平方、再求和，这个和被定义为χ2，即
χ2=∑i=1k u2i
每进行一次随机抽样便可获得一个χ2值，于是，所有可能的χ2值所构成的分布被称作χ2分布［1］.该分布属于连续型变量的分布，自由度df=k-1，每个不同的自由度都有一个相应的分布曲线［1-2］.χ2分布具有以下特征：其取值区间为［0，+∞）；当df=1时，分布曲线以纵轴为渐近线；当df逐渐增大时，分布曲线渐趋于左右对称；当df≥30时，χ2分布已接近于正态分布［1］.
在具体的教学活动中，如果对χ2分布曲线的特征仅仅是进行简单的叙述，一方面难以引起学生的学习兴趣、激发他们的学习热情，另一方面也不利于他们对χ2分布内涵的深入理解.作为一种非常普及的办公软件，Excel具有强大的函数运算能力，在统计学教学过程中得到广泛应用［3-6］.人们利用Excel对抽样分布［7］、中心极限定理［8］、大数定律［9］等内容进行模拟，以帮助学生对这些概念的深入理解.近年来，笔者与同事们在教学过程中，利用Excel对生物统计学当中的一些内容进行构建或模拟，也取得不错的教学效果［10-14］.本文继续以Excel2010为例，采用2种方法对χ2分布曲线分别进行构建和模拟，
以供参考. 1利用函数进行构建
Excel2010中的CHISQ.DIST（χ2，df，FALSE）函数可以用来计算某χ2值与df条件下的概率密度.利用该函数对χ2分布曲线进行构建时，其数据输入格式如图1所示.在A列中自A3单元格以下输入≥0的数字以表示χ2值，本文中以0.5为间隔，即0.0、0.5、1.0、…；在第2行中自B列开始输入正整数以表示自由度，本文中以1、3、5、…为例；在B3单元格中输入“=CHISQ.DIST（$A3，B$2，FALSE）”，并将此函数公式向收稿日期：2020-03-07
基金项目：安徽省新农科研究与改革实践项目；安徽省质量工程项目（2019jyxm0211，2018jyxm0516，2017mooc323）；淮北师范大学质量工程项目（2017kfkc152，JY18036）
作者简介：查岭生（1977—），男，安徽怀宁人，副教授，研究方向为昆虫学.通信作者：丁建华（1979—），男，江西瑞昌人，博士，副教授，研究方向为昆虫分类学.
visual basic还有人用
第3期查岭生等：卡方分布的Excel构建与模拟
右、向下复制填充，一张特定χ2值与df条件下的概率密度表格便生成了；然后对所得概率密度与A列当中的χ2值进行绘图，便可得到相应的χ2分布曲线（图2）.该文件生成后，只需要任意修改表格第2行中的自由度取值，便可以在课堂上动态地展示χ2分布曲线随df变化而变化的规律特点，从而达到激发学生
学习兴趣、提高教学效果之目的.需要注意的是，当χ2=0、df=1时，Excel将返回错误值“#NUM！”，这是因为此时曲线以纵轴为渐近线的缘故
.
图1Excel函数输入示例图2不同自由度的χ2分布曲线
2利用VBA编程进行模拟
2.1模拟步骤
Excel中的NormInv（Rnd（），0，1）函数可以生成一系列服从标准正态分布N（0，1）的随机数字，正好可以用来模拟χ2分布的抽样过程.
第1步：打开Excel2010，新建一个电子表格，在当前工作表Sheet1的A1至A3单元格中分别输入“自由度df=”“抽样次数=”“组距=”；在B1至B3单元格中分别输入相应的数字，本文中以自由度df=1、抽样次数=10000、组距=0.5为例；在C1至G1单元格汇总分别输入“u”“u2”“χ2”“组限”以及“频率”（图3）.
第2步：按Alt+F11进入Visual Basic编写界面，双击左侧的Sheet1图标，进入代码编写界面，把本文附录中的VBA代码拷贝进去（图4）.然后关闭代码编写界面进入工作表界面，将文件另存为启用宏的文件类型（*.xlsm）即可.
2.2模拟结果
在B1单元格中输入具体的数字（本文中以df=1为例），便可触发该模拟程序自动运行.首先是在C2、C3单元格中生成2个服从N（0，1）的随机变量，此步骤用来模拟从标准正态总体当中随机抽取k（= df+1）个独立变量的过程；然后对这2个随机变量求平方，其平方值相应地存放在D2、D3单元格中；再对D2、D3单元格中的平方值进行求和，得到一个χ2值，将其存放在E2单元格中.若将上述抽样过程重复10000次（B2单元格中的数字），便可以得到10000个χ2值，依次被存放在E2至E10001单元格中（图3）
.
图3模拟步骤第1步的输入结果图4Excel中VBA代码的输入
93
淮北师范大学学报（自然科学版）2020年
在VBA 代码中，采用ROUNDUP （MAX （））和ROUNDDOWN （MIN （））这两个复合函数对E 列中的χ2值的最大值与最小值分别向上向下取整数，以统计χ2值的分布范围，并根据B3单元格中的组距大小设置组限，数据存放在F 列中；再采用FREQUENCY 函数进行频次统计，所得的各组的频率存放在G 列中；最后对G 列中的结果绘制柱状图.绘图时可采用动态引用函数OFFSET 以达到动态展示的效果，其具体步骤如下：
首先，按Ctrl+F3进入“名称管理器”后点击“新建（N ）…”或者从“菜单栏→公式→定义名称”进入定义名称窗口（图5）.在名称对话框中填入“频率”，在“引用位置（R ）：”对话框中输入“=OFFSET （$G $2，0，0，COUNT （$G $2：$G $65536），1）”后确定.用同样的方法再定义一个“组限”，其引用位置为“=OFFSET （$F $2，0，0，COUNT （$F $2：$F $65536），1）”，确定后关闭对话框.然后点击一下这个数据表之外的空白单元格，再点击菜单栏“插入→柱形图→二维柱形图”，在新出现的空白框中点击右键→选择数据（E ）…，进入选择数据源窗口（图6），点击“图例项（系列）（S ）”下的“添加（A ）”，在“系列值（V ）：”对话框中输入“=Sheet1！频率”后确定；再点击“水平（分类）轴标签（C ）”下的“编辑（T ）”，在“轴标签区域（A ）：”对话框中输
入“=sheet1！组限”后确定
.图5定义名称对话框图6选择数据源对话框
通过上述操作，一幅可以随F 列、G 列中数据变化而变化的动态柱状图便生成了，稍加修饰，其效果如图7所示.任意修改B1单元格中的自由度取值，该柱状图便会相应地发生改变，从而可以动态地展示χ2分布随自由度变化而变化的基本规律.从图7中可以看出，χ2值的频率分布变化趋势与图2中的概率密度曲线的变化趋势是基本一致的
.
图7不同自由度的χ2频率分布
3讨论
频率与概率之间的关系，实际上就是样本统计数与总体参数的关系.根据大数定律，当样本容量n
充分大时，事件A 发生的频率W （A ）就可以代替概率P （A ）［1］.上述的第1种方法，即利用CHISQ.DIST 函数对χ2分布曲线进行构建，恰恰就是对χ2分布总体的描述.其优点是分布曲线圆滑美观，运算速度快，不影响正常的教学节奏，便于课堂上展示；其不足之处在于未能体现抽样过程，也就不便于学生深刻理解χ2分布的由来.利用VBA 编程对χ2分布进行模拟则可以很好地弥补这一不足之处.在VBA 模拟中，学生可以根据运算过程去梳理χ2分布的由来，从而加深对该分布的理解.理论上，当图3中B2单元格中的
94
95第3期查岭生等：卡方分布的Excel构建与模拟
抽样次数无限大、B3单元格中的组距无限接近于0时，图7与图2相一致.在实际应用时，一方面Excel无法满足抽样次数无限大的要求，另一方面，抽样次数过大也会使得运算过程耗时过长.本文的VBA代码中，最大抽样次数设置为65535，已然可以基本满足模拟的需要.建议：在课堂上采用第1种方法辅助教
学，将第2种方法留给学生课下自行模拟，通过修改B1至B3单元格中的模拟参数，以查看不同自由度、不同抽样次数以及不同组距下的χ2频率分布.这种课上课下相结合的教学方式，必能激发学生的学习兴趣，提高教学效果.
致谢：特别感谢福建江夏学院会计学院的薛捷波先生在VBA编程方面给予的热情帮助.
参考文献：
［1］李春喜，姜丽娜，邵云，等.生物统计学［M］.5版.北京：科学出版社，2013：51-52.
［2］杜荣骞.生物统计学［M］.北京：高等教育出版社，1999：64-65.
［3］BELL P C.Teaching business statistics with Microsoft Excel［J］.INFORMS Transactions on Education，2000，1（1）：18–
26.
［4］WARNER C B，MEEHAN A M.Microsoft Excel TM as a tool for teaching basic statistics［J］.Teaching of Psychology，2001，28（4）：295-298.
［5］NASH J C.Teaching statistics with Excel2007and other spreadsheet［J］.Computational Statistics and Data Analysis，2008，52（10）：4602-4606.
［6］盛晓兰.正态总体均值的假设检验及其在Excel中的实现［J］.廊坊师范学院学报（自然科学版），2007，9（6）：33-34.［7］MARASINGHE M G，MEEKER W Q，COOK D，et al.Using graphics and simulation to teach statistical concepts［J］.The American Statistician，1996，50（4）：342-351.
［8］WEST R W，OGDEN R T.Interactive demonstrations for statistics education on the world wide web［J］.Journal of Statistics Education，1998，6（3）：1-8.
［9］NG V M，WONG K Y.Using simulation on the internet to teach statistics［J］.The Mathematics Teacher，1999，92（8）：729-733.
［10］DING J H，JIN X W，SHUAI L Y.Understanding the degrees of freedom of sample variance by using Microsoft Excel［J］.
Teaching Statistics Trust，2017，39（3）：92-95.
［11］DING J H，SHUAI L Y，XUE J B，et al.Application of Microsoft Excel in understanding the deg
rees of freedom of linear regression［J］.Journal of Statistics：Advances in Theory and Applications，2018，19（1）：17-30.
［12］丁建华，薛捷波，张铮.样本方差自由度的Excel模拟［J］.生物学杂志，2018，35（6）：124-126.
［13］李俊，丁建华，金显文，等.方差分析多重比较中q值表与SSR表的构建［J］.廊坊师范学院学报（自然科学版），2019，19（2）：64-67.
［14］丁建华，李俊.F分布的Excel构建与模拟［J］.韶关学院学报（自然科学版），2019，40（12）：9-12.
附录（VBA代码）
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address="$B$1"Then
Call sql
End If
End Sub
Sub sql()
Dim ar(1To65535,1To2),br(1To65535,1To1)
Range("c2:g65536").ClearContents
For i=1To[b2]
For j=1To[b1]+1
ar(j,1)=Application.NormInv(Rnd(),0,1)
ar(j,2)=ar(j,1)^2
s=s+ar(j,2)
Next
96
淮北师范大学学报（自然科学版）2020年br(i,1)=s
s=0
Next
[e2].Resize([b2])=br
[c2].Resize([b2],2)=ar
x=Application.Index(br,0,1)
r=Application.RoundUp(Application.Max(x),0)+[b3]
t=Application.RoundDown(Application.Min(x),0)
p=1
For k=t To r Step[b3].Value
p=p+1
Cells(p,6)=k
Next
cr=Application.Evaluate("Frequency(e2:e"&[b2]+1&","&"f2:f"&p&")/b2")
[g2].Resize(UBound(cr)-1)=cr
End Sub
The Construction and Simulation of Chi-square
Distribution by Using Excel
ZHA Lingsheng，LI Jun，DING Jianhua
（School of Life Science，Huaibei Normal University，235000，Huaibei,Anhui,China）
Abstract：Chi-square distribution is a common probability distribution，which plays an important role in the teaching of biostatistics.In order to help students understand the connotation of Chi-square distribution deep⁃ly，the functions and VBA programming of Excel software are used to construct and
simulate the distribution curves，which can dynamically display the basic law of the Chi-square distribution curves changing with the degrees of freedom，and then stimulate students′interest in learning，and improve the teaching effect efficient⁃ly.
Key words：Chi-square distribution；Excel；construction；simulation

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。