Excel在中学基础统计教学方面的应用

金哲植+李兆玲

摘 要 随着信息技术的发展，对中学数学教育引入信息技术的应用尤其重要。在中学数学概率统计方面，概率的理解需要从随机的角度考虑，通过Excel产生大量随机数来代替重复进行随机试验得到的结果，使学生更容易接受频率与概率的联系。并且Excel能够计算事件发生的概率，比常规的计算更简单。另外Excel还可以对数据进行描述性统计分析，抽样以及回归拟合等，加上其强大的作图功能使Excel在中学数学概率统计方面有着极广泛的应用。

关键词 Excel 概率 描述统计 抽样 数据拟合

中图分类号：G424 文献标识码：A

The Application of Excel in Probability and

Statistics of Middle School Mathematics

JIN Zhezhi， LI Zhaoling

（Department of Mathematics， College of Science， Yanbian University， Yanji， Jilin 133002）

Abstract With the development of information technology， the introduction of secondary mathematics education is particularly important application of information technology.In the study of probability and statistics of middle school mathematics， the understanding of probability starts from the angle of random. Random numbers generated by Excel can replace the results of repeated random experiences， making the relationship between the frequency and probability more easily to be understood. And Excel also can calculate probability events， it is much simpler than the conventional calculation. Excel also can carry out descriptive statistical analysis， sampling and regression fitting operation on data. Coupled with its practical mapping function， Excel has wide application space in the field of mathematics statistics and probability of middle school.

Key words Excel； probability； descriptive； statistics； sampling； regression

0 引言

在高等教育概率统计的教学中，我们使用较多的专业型的统计分析软件，比如说SAS、R、SPSS等。而在中学数学的教育中，关于概率统计的学习，我们采用较为普遍的软件Excel来进行。①电子表格 Excel对于我们来说并不陌生，这里着重针对中学数学教材必修三概率统计知识说明Excel在概率统计教学中的应用。

1 概率和频率的关系

新课标中明确指出，在概率的学习中，重点是让学生理解什么是随机试验什么是概率以及试验频率是如何稳定与实际概率的。②如果通过人为地打量重复地进行随机试验，既费时又费力，这里通过计算机模拟出大量的随机试验，来理解概率的意义。这里，我们选用经典款——投硬币的试验进行说明。

试验具体步骤如下：

（1）启动Excel（这里使用的是2010版Excel）

（2）在A1单元格里输入“随机投掷10000次”，在A2单元格里输入“=int（rand（）*2）”。

说明：① int（）是取整函数。表示取小于该数字且最接近该数字的整数。比如int（2.1）=2，int（-2.1）=-3。

②rand（）表示产生（0，1）之间的随机数。如果产生的随机数小于0.5，乘以2则小于1，取整后为0；如果产生的随机数大于等于0.5，乘以2后大于等于1，取整后为1。我们假设投掷硬币正面朝上为1，反面朝上为0。

（3）通过Excel的自动填充选项，将A2单元格的公式复制到A3-A10001单元格。该过程相当于投掷硬币10000次。

（4）在B1-B5单元格分别输入“投掷次数”、“正面朝上的次数”、“反面朝上的次数”、“正面朝上的频率”、“反面朝上的频率”。

说明：这里分别选取投掷次数为100、1000、4000、7000、10000（C1-C5）以便进行比较。

（5）在C2单元格输入“=countif（A2：A101，1）”，C3单元格输入“=countif（A2：A101，0）”，C4单元格输入“=C2/C1”，C5单元格输入“=C3/C1”。同样，对D2-D5，E2-E5，F2-F5，G2-G5单元格进行相应的公式输入。得到结果如图1所示：

图1

从图1中数据可以看出，随着投掷次数的增加，正面朝上的频率和反面朝上的频率越来越接近于0.5。endprint

一个事件发生的频率是波动的，但是多次试验的频率接近于频率。因此可以通过多次试验用频率估计概率。

2 一些常见概率的计算

关于概率计算，这里介绍服从二项分布的计算。

二项分布的试验背景是重贝努里试验（试验由次基本试验构成），服从二项分布的随机变量，其概率 （ = ） = 。其中，表示试验次数，为一次试验中某种结果发生的概率。

案例：甲乙两人比赛，每次比赛中甲胜出的概率为0.4，求5次比赛中甲胜出2次的概率。

具体操作如下：

（1）打开Excel，输入如图2，然后选定单元格B5。

图2

（2）在“开始”选项卡的“编辑”部分，单击自动求和右边的倒三角符号，选择“其他函数”，选择类别“统计”，选择函数“BINOM.DIST”，单击“确定”。

（3）根据已知条件填入相应的对话框，如图3。

图3

可得概率为0.3456。

3 抽样简介

当总体数据过大时，可以从总体中按一定方法抽取出一定数量的样本数据来代替总体。

案例：已知某班68名学生的平均成绩如下，请进行抽样分析。

67.12 77.44 89.33 81.05 65.14 80.74 82.97 69.23 75.82

69.08 79.23 75.72 82.59 77.77 73.26 74.23 93.36 55.69

72.62 84.62 74.59 89.33 69.74 66.14 66.74 75.85 81.05

66.14 62.46 93.36 71.56 84.54 66.14 64.87 71.56 75.72

67.12 70.62 81.03 70.62 79.23 63.62 64.87 77.44 80.74

93.36 74.23 76.49 62.46 76.87 75.54 65.65 56.79 71.62

67.12 66.05 82.59 77.77 66.62 59.15 81.05 74.59 71.56

66.05 73.15 82.59 81.03 93.36

图4

具体操作如下：

（1）将68个数据输入到Excel工作簿A2-A6。A1单元格输入“平均成绩”。然后单击“数据”选项下的“分析”区域下的“数据分析”，得到对话框。（没有“数据分析”时需要加载：文件—选项—加载项—分析工具库—转到分析工具库—确定）

（2）选择分析工具下的“抽样”。然后确定。

（3）在“抽样”对话框分别输入如图4所示。这里采用随机抽样方法，样本量为20。

（4）单击“确定”，得到结果如图5。B2-B21为抽样所得的样本。

图5

4 描述统计样本估计总体

用3中抽取的样本数据，用样本估计总体。

具体具体步骤如下：

（1）数据—分析—数据分析。

（2）在“数据分析”对话框，选择描述统计，单击确定，得到“描述统计”对话框。

（3）按图6所示输入。

图6

（4）确定后，得到结果如图7。

图7

结果给出的描述统计量较多，在中学数学中，我们只选择平均（均值），方差，中位数，众数等。

通过频率直方图直观地看出成绩分布的情况。绘制频率直方图具体步骤如下：

①将上面得到的B2-B21数据复制到新建Excel工作簿的A1-A20单元格（也可以在原工作簿上进行）。

②在C1-C4单元格分别输入“接受组距”“65”“75”“85”。这里是设置各组的上限。

③数据—分析——数据分析，在数据分析对话框中选择分析工具“直方图”。出现对话框如图8填写：

图8

④点击确定后结果如图9：

图9

另外，还可以函数求均值，中位数等。均值函数（average）、众数函数（mode）、中位数函数（median）、方差函数（var.p）。

比如说在B1单元格输入“均值”，C1单元格输入“=average（A1：A20）”回车后即得到20名学生成绩的平均成绩。结果如下：

图10

5 数据拟合回归分析③

在数据分析中，数据的拟合及回归可以很好地描述数据间的对应关系。就是将一组数据用函数的形式描述出来，发现其中的规律，得到数学模型，并能够用模型刻画已知数据和预测。④

案例：假定某一保险公司希望确定居民住宅区火灾造成的损失数额与该住户到最近的消防站的距离之间的相关关系，以便准确地定出保险金额。Excel表中A列和D列给出了15起火灾事故的损失y及火灾发生地与最近的消防站的距离x。

操作步骤：

（1）将数据输入到Excel工作表中，单击“插入”选项下的“图表”区域下的“散点图”。

（2）通过观察散点图发现，散点可以认为近似在一条直线上，所以用直线来拟合。鼠标右键单击任意图上的散点，单击“添加趋势线”，在“趋势线选项”中选择“线性”“显示公式”“显示R平方值”，得到拟合直线方程为y = 4.9193x + 10.278。R2的值越接近1，表明回归拟合的效果越好。

（3）我们可以预测估计当x的范围在5至7的时候y的值，如C列和D列（可以通过自动填充和公式来实现数值的计算）。

图11

说明：①对直线拟合的公式是利用“最小二乘法”，，具体的计算过程参考教科书。

②若散点不近似在一条直线上，可以通过在“趋势线选项”中选择其他近似的线性，通过多重比较R2的值可得到更好的模型。

6 结论

本文针对中学数学中概率统计的内容，演示了Excel产生大量随机数模拟试验，并且能够对已知数据进行描述性统计分析，抽样以及回归拟合等。通过得到一系列相应的计算结果和操作方便的作图分析功能，大大地方便了老师对繁杂过程的教学演示，使学生更容易理解频率与概率的联系，加深对概念的理解和在实际问题中的应用。在经过熟练的操作练习后，可以方便地得到其他相关问题的结果及分析。

注释

① 王惠春.EXCEL的共享功能在《概率和统计》教学中的应用两例[J].赤峰学院学报，2006.8.

② 唐国强.EXCEL在概率计算中的应用[J].安阳大学学报，2003.3.

③ 何晓群，刘文卿.应用回归分析（第三版）.北京：中国人民大学出版社，2011.

④ 徐稼红.信息技术与数学课程整合[J]——Excel篇（3）.中学数学月刊，2009（5）.endprint

一个事件发生的频率是波动的，但是多次试验的频率接近于频率。因此可以通过多次试验用频率估计概率。

2 一些常见概率的计算

关于概率计算，这里介绍服从二项分布的计算。

二项分布的试验背景是重贝努里试验（试验由次基本试验构成），服从二项分布的随机变量，其概率 （ = ） = 。其中，表示试验次数，为一次试验中某种结果发生的概率。

案例：甲乙两人比赛，每次比赛中甲胜出的概率为0.4，求5次比赛中甲胜出2次的概率。

具体操作如下：

（1）打开Excel，输入如图2，然后选定单元格B5。

图2

（2）在“开始”选项卡的“编辑”部分，单击自动求和右边的倒三角符号，选择“其他函数”，选择类别“统计”，选择函数“BINOM.DIST”，单击“确定”。

（3）根据已知条件填入相应的对话框，如图3。

图3

可得概率为0.3456。

3 抽样简介

当总体数据过大时，可以从总体中按一定方法抽取出一定数量的样本数据来代替总体。

案例：已知某班68名学生的平均成绩如下，请进行抽样分析。

67.12 77.44 89.33 81.05 65.14 80.74 82.97 69.23 75.82

69.08 79.23 75.72 82.59 77.77 73.26 74.23 93.36 55.69

72.62 84.62 74.59 89.33 69.74 66.14 66.74 75.85 81.05

66.14 62.46 93.36 71.56 84.54 66.14 64.87 71.56 75.72

67.12 70.62 81.03 70.62 79.23 63.62 64.87 77.44 80.74

93.36 74.23 76.49 62.46 76.87 75.54 65.65 56.79 71.62

67.12 66.05 82.59 77.77 66.62 59.15 81.05 74.59 71.56

66.05 73.15 82.59 81.03 93.36

图4

具体操作如下：

（1）将68个数据输入到Excel工作簿A2-A6。A1单元格输入“平均成绩”。然后单击“数据”选项下的“分析”区域下的“数据分析”，得到对话框。（没有“数据分析”时需要加载：文件—选项—加载项—分析工具库—转到分析工具库—确定）

（2）选择分析工具下的“抽样”。然后确定。

（3）在“抽样”对话框分别输入如图4所示。这里采用随机抽样方法，样本量为20。

（4）单击“确定”，得到结果如图5。B2-B21为抽样所得的样本。

图5

4 描述统计样本估计总体

用3中抽取的样本数据，用样本估计总体。

具体具体步骤如下：

（1）数据—分析—数据分析。

（2）在“数据分析”对话框，选择描述统计，单击确定，得到“描述统计”对话框。

（3）按图6所示输入。

图6

（4）确定后，得到结果如图7。

图7

结果给出的描述统计量较多，在中学数学中，我们只选择平均（均值），方差，中位数，众数等。

通过频率直方图直观地看出成绩分布的情况。绘制频率直方图具体步骤如下：

①将上面得到的B2-B21数据复制到新建Excel工作簿的A1-A20单元格（也可以在原工作簿上进行）。

②在C1-C4单元格分别输入“接受组距”“65”“75”“85”。这里是设置各组的上限。

③数据—分析——数据分析，在数据分析对话框中选择分析工具“直方图”。出现对话框如图8填写：

图8

④点击确定后结果如图9：

图9

另外，还可以函数求均值，中位数等。均值函数（average）、众数函数（mode）、中位数函数（median）、方差函数（var.p）。

比如说在B1单元格输入“均值”，C1单元格输入“=average（A1：A20）”回车后即得到20名学生成绩的平均成绩。结果如下：

图10

5 数据拟合回归分析③

在数据分析中，数据的拟合及回归可以很好地描述数据间的对应关系。就是将一组数据用函数的形式描述出来，发现其中的规律，得到数学模型，并能够用模型刻画已知数据和预测。④

案例：假定某一保险公司希望确定居民住宅区火灾造成的损失数额与该住户到最近的消防站的距离之间的相关关系，以便准确地定出保险金额。Excel表中A列和D列给出了15起火灾事故的损失y及火灾发生地与最近的消防站的距离x。

操作步骤：

（1）将数据输入到Excel工作表中，单击“插入”选项下的“图表”区域下的“散点图”。

（2）通过观察散点图发现，散点可以认为近似在一条直线上，所以用直线来拟合。鼠标右键单击任意图上的散点，单击“添加趋势线”，在“趋势线选项”中选择“线性”“显示公式”“显示R平方值”，得到拟合直线方程为y = 4.9193x + 10.278。R2的值越接近1，表明回归拟合的效果越好。

（3）我们可以预测估计当x的范围在5至7的时候y的值，如C列和D列（可以通过自动填充和公式来实现数值的计算）。

图11

说明：①对直线拟合的公式是利用“最小二乘法”，，具体的计算过程参考教科书。

②若散点不近似在一条直线上，可以通过在“趋势线选项”中选择其他近似的线性，通过多重比较R2的值可得到更好的模型。

6 结论

本文针对中学数学中概率统计的内容，演示了Excel产生大量随机数模拟试验，并且能够对已知数据进行描述性统计分析，抽样以及回归拟合等。通过得到一系列相应的计算结果和操作方便的作图分析功能，大大地方便了老师对繁杂过程的教学演示，使学生更容易理解频率与概率的联系，加深对概念的理解和在实际问题中的应用。在经过熟练的操作练习后，可以方便地得到其他相关问题的结果及分析。

注释

① 王惠春.EXCEL的共享功能在《概率和统计》教学中的应用两例[J].赤峰学院学报，2006.8.

② 唐国强.EXCEL在概率计算中的应用[J].安阳大学学报，2003.3.

③ 何晓群，刘文卿.应用回归分析（第三版）.北京：中国人民大学出版社，2011.

④ 徐稼红.信息技术与数学课程整合[J]——Excel篇（3）.中学数学月刊，2009（5）.endprint

一个事件发生的频率是波动的，但是多次试验的频率接近于频率。因此可以通过多次试验用频率估计概率。

2 一些常见概率的计算

关于概率计算，这里介绍服从二项分布的计算。

二项分布的试验背景是重贝努里试验（试验由次基本试验构成），服从二项分布的随机变量，其概率 （ = ） = 。其中，表示试验次数，为一次试验中某种结果发生的概率。

案例：甲乙两人比赛，每次比赛中甲胜出的概率为0.4，求5次比赛中甲胜出2次的概率。

具体操作如下：

（1）打开Excel，输入如图2，然后选定单元格B5。

图2

（2）在“开始”选项卡的“编辑”部分，单击自动求和右边的倒三角符号，选择“其他函数”，选择类别“统计”，选择函数“BINOM.DIST”，单击“确定”。

（3）根据已知条件填入相应的对话框，如图3。

图3

可得概率为0.3456。

3 抽样简介

当总体数据过大时，可以从总体中按一定方法抽取出一定数量的样本数据来代替总体。

案例：已知某班68名学生的平均成绩如下，请进行抽样分析。

67.12 77.44 89.33 81.05 65.14 80.74 82.97 69.23 75.82

69.08 79.23 75.72 82.59 77.77 73.26 74.23 93.36 55.69

72.62 84.62 74.59 89.33 69.74 66.14 66.74 75.85 81.05

66.14 62.46 93.36 71.56 84.54 66.14 64.87 71.56 75.72

67.12 70.62 81.03 70.62 79.23 63.62 64.87 77.44 80.74

93.36 74.23 76.49 62.46 76.87 75.54 65.65 56.79 71.62

67.12 66.05 82.59 77.77 66.62 59.15 81.05 74.59 71.56

66.05 73.15 82.59 81.03 93.36

图4

具体操作如下：

（1）将68个数据输入到Excel工作簿A2-A6。A1单元格输入“平均成绩”。然后单击“数据”选项下的“分析”区域下的“数据分析”，得到对话框。（没有“数据分析”时需要加载：文件—选项—加载项—分析工具库—转到分析工具库—确定）

（2）选择分析工具下的“抽样”。然后确定。

（3）在“抽样”对话框分别输入如图4所示。这里采用随机抽样方法，样本量为20。

（4）单击“确定”，得到结果如图5。B2-B21为抽样所得的样本。

图5

4 描述统计样本估计总体

用3中抽取的样本数据，用样本估计总体。

具体具体步骤如下：

（1）数据—分析—数据分析。

（2）在“数据分析”对话框，选择描述统计，单击确定，得到“描述统计”对话框。

（3）按图6所示输入。

图6

（4）确定后，得到结果如图7。

图7

结果给出的描述统计量较多，在中学数学中，我们只选择平均（均值），方差，中位数，众数等。

通过频率直方图直观地看出成绩分布的情况。绘制频率直方图具体步骤如下：

①将上面得到的B2-B21数据复制到新建Excel工作簿的A1-A20单元格（也可以在原工作簿上进行）。

②在C1-C4单元格分别输入“接受组距”“65”“75”“85”。这里是设置各组的上限。

③数据—分析——数据分析，在数据分析对话框中选择分析工具“直方图”。出现对话框如图8填写：

图8

④点击确定后结果如图9：

图9

另外，还可以函数求均值，中位数等。均值函数（average）、众数函数（mode）、中位数函数（median）、方差函数（var.p）。

比如说在B1单元格输入“均值”，C1单元格输入“=average（A1：A20）”回车后即得到20名学生成绩的平均成绩。结果如下：

图10

5 数据拟合回归分析③

在数据分析中，数据的拟合及回归可以很好地描述数据间的对应关系。就是将一组数据用函数的形式描述出来，发现其中的规律，得到数学模型，并能够用模型刻画已知数据和预测。④

案例：假定某一保险公司希望确定居民住宅区火灾造成的损失数额与该住户到最近的消防站的距离之间的相关关系，以便准确地定出保险金额。Excel表中A列和D列给出了15起火灾事故的损失y及火灾发生地与最近的消防站的距离x。

操作步骤：

（1）将数据输入到Excel工作表中，单击“插入”选项下的“图表”区域下的“散点图”。

（2）通过观察散点图发现，散点可以认为近似在一条直线上，所以用直线来拟合。鼠标右键单击任意图上的散点，单击“添加趋势线”，在“趋势线选项”中选择“线性”“显示公式”“显示R平方值”，得到拟合直线方程为y = 4.9193x + 10.278。R2的值越接近1，表明回归拟合的效果越好。

（3）我们可以预测估计当x的范围在5至7的时候y的值，如C列和D列（可以通过自动填充和公式来实现数值的计算）。

图11

说明：①对直线拟合的公式是利用“最小二乘法”，，具体的计算过程参考教科书。

②若散点不近似在一条直线上，可以通过在“趋势线选项”中选择其他近似的线性，通过多重比较R2的值可得到更好的模型。

6 结论

本文针对中学数学中概率统计的内容，演示了Excel产生大量随机数模拟试验，并且能够对已知数据进行描述性统计分析，抽样以及回归拟合等。通过得到一系列相应的计算结果和操作方便的作图分析功能，大大地方便了老师对繁杂过程的教学演示，使学生更容易理解频率与概率的联系，加深对概念的理解和在实际问题中的应用。在经过熟练的操作练习后，可以方便地得到其他相关问题的结果及分析。

注释

① 王惠春.EXCEL的共享功能在《概率和统计》教学中的应用两例[J].赤峰学院学报，2006.8.

② 唐国强.EXCEL在概率计算中的应用[J].安阳大学学报，2003.3.

③ 何晓群，刘文卿.应用回归分析（第三版）.北京：中国人民大学出版社，2011.

④ 徐稼红.信息技术与数学课程整合[J]——Excel篇（3）.中学数学月刊，2009（5）.endprint