利用Excel函数进行数学实验的应用研究

季凌云

[摘           要]  Excel内置的函数功能非常强大，利用Excel函数可以解决许多问题。以概率教学内容中投掷硬币实验为例，分三步阐述了如何应用RAND（）、FIXED（）、IF（）函数和COUNTIF（）函数等函数计算概率问题。

[关    键   词]  Excel内置函数;数学实验;嵌套函数

[中图分类号]  O211                    [文献标志码]  A                      [文章编号]  2096-0603（2019）36-0192-02

Microsoft Excel是微软公司推出的办公软件Microsoft office的重要组件之一，它可以进行各种数据处理、统计分析和辅助决策等操作，广泛应用于教学、管理、统计、财经等各个领域。其中，Excel内置的函数功能非常强大，合理适度的调用能大大提高学习、工作效率。现以中职概率教学内容中的一个实例——投掷硬币实验为例，来说明如何使用Excel内置函数进行数学实验研究。

研究的问题是投掷两枚硬币，问投掷后，出现一枚正面朝上、一枚反面朝上的概率是多少？两枚正面朝上和两枚反面朝上的概率又各是多少？这里，规定有面值的那面为正面，有国徽的那面为反面。

首先，借助Excel的内置函数RAND（）、FIXED（）和IF（）函数模拟投掷硬币实验，随机生成“正”“反”面信息。

其中，RAND（）函数为随机数生成函数，FIXED（）函数为按照指定方式进行四舍五入的函数，IF（）函数为条件函数。

打开Excel2010，在A1单元格中输入“第一枚硬币”，在B1单元格中输入“第二枚硬币”，如下图所示：

RAND（）函数没有参数，它会产生一个大于或等于0且小于1的平均分布随机数，只要双击插入该函数的单元格，再按Enter键，就会产生一个新的随机数。

函数FIXED（number，decimals，no_commas）有三个参数，number表示要进行四舍五入并转换成文本字符串的数，decimals是为一数值，用以指定小数点右边的小数位数，参数no_commas为一逻辑值，若为TRUE时，不显示逗号，忽略或为FALSE时，显示千位分隔逗号。

函数IF（logical_test，value_if_true，value_if_false）也有三個参数，logical_test表示计算结果为True或False的任意值或表达式，value_if_true表示logical_test为True时返回的值，参数value_if_false表示logical_test为False时返回的值。

单击A2单元格，利用上述函数，在编辑栏中构造嵌套函数：

=IF（FIXED（RAND（），0）=“1”，“正”，“反”）

上述公式的含义是：按照从里向外顺序，第一层函数RAND（）表示产生一个大于或等于0且小于1的均匀分布随机数。第二层函数FIXED（RAND（），0）表示对由RAND（）函数产生的随机数进行四舍五入运算，由此，刚好可以得到两个数，一个是0，一个是1，且得到0与得到1的概率相同，这正好对应投掷硬币实验中随机出现的正面和反面两种情况。这里，假定1对应正面，0对应反面，那么第三层函数IF（FIXED（RAND（），0）=“1”，“正”，“反”）表示当函数FIXED（RAND（），0）的结果等于1时，输出“正”，反之，输出“反”。

至此，单击Enter键，通过构造的嵌套函数，A2单元格中随机产生“正”面。按住填充柄向右拖动，复制A2单元格的公式至A3中，这时，A2和A3单元格就显示出了第一次投掷两枚硬币的结果。

然后，借助IF（）和AND（）函数，把每一次随机实验的结果在单元格中显示出来，以便后续进行统计计数工作。

针对第一次随机实验，单击C1单元格，输入“结果”。由于该随机实验包含4个基本事件：“正，正”“正，反”“反，正”和“反，反”，我们需要在C2单元格中显示A2和B2单元格的结果，那么，应该如何操作？

单击C2单元格，借助IF（）和AND（）函数，构造如下公式：

=IF（AND（A2=“正”，B2=“正”），“正，正”，IF（AND（A2=“正”，B2=“反”），“正，反”，IF（AND（A2=“反”，B2=“正”），“反，正”，“反，反”）））

上述公式是利用三层IF（）函数，同时内嵌AND（）函数，将单元格A2、B2的结果自动显示在C2单元格中。单击Enter键，C2单元格会自动显示出A2和B2单元格的结果。

采用框选方式选择A2、B2和C2单元格，按住填充柄向下拖动至A101、B101和C101，共计100行。每一行相当于进行了一次实验，100行相当于进行了100次投掷硬币实验。

最后，利用COUNTIF（）函数统计各个基本事件出现的次数。

分别在D1、E1、F1和G1中输入“正，正”“正，反”“反，正”和“反，反”，如下图所示：

在D2单元格中输入公式：“=COUNTIF（$C$2：$C$101，D1）”。

该语句表示统计从C2到C101这100个单元格中，其值等于D1单元格“正，正”的单元格数量，也就是统计100次随机实验中基本事件“正，正”出现的次数。其中，在单元格的行号和列号前加“$”符号表示绝对引用，$C$2：$C$101即是绝对地址，在复制公式过程中，该地址不发生变化。

选择D2单元格，按住填充柄向右拖动，另外三个基本事件的结果也会自动显示出来。经计算，发现这四个数之和正好是100，然后插入饼图。

通过上述饼图，可直观看出这四个基本事件出现的频率。如果单击一次“保存”命令，上述四个基本事件出现的次数就会更新一遍，相当于又做了一遍100次随机实验。从饼图中各个事件出现概率的百分比可以看出，虽然单击“保存”命令后这四个基本事件出现的次数会发生改变，但基本都各占四分之一左右。

通过以上三个实验步骤，可以得出结论：通过进行多次投掷两枚硬币实验，投掷后出现一枚正面朝上、一枚反面朝上的概率是四分之一，两枚正面朝上和两枚反面朝上的概率也各是四分之一。

上述内容只是提出了一种方法验证投币实验中正反面出现的概率问题，当遇到其他数学问题时，如何利用Excel内置的函数解决，还需要教师根据教学内容自身特点，开动脑筋，恰当调用并反复实践。

参考文献：

[1]李广全，李尚志.数学[M].北京：高等教育出版社，2009.

[2]刘松平，佟伟祥.计算机应用基础[M].吉林：东北师范大学出版社，2015（1）：120-126.

◎编辑 原琳娜