利用Excel函數(shù)進行數(shù)學實驗的應用研究

季凌云

[摘? ? ? ? ? ?要]? Excel內置的函數(shù)功能非常強大，利用Excel函數(shù)可以解決許多問題。以概率教學內容中投擲硬幣實驗為例，分三步闡述了如何應用RAND（）、FIXED（）、IF（）函數(shù)和COUNTIF（）函數(shù)等函數(shù)計算概率問題。

[關? ? 鍵? ?詞]? Excel內置函數(shù);數(shù)學實驗;嵌套函數(shù)

[中圖分類號]? O211 ? ? ? ? ? ? ? ? ? ?[文獻標志碼]? A? ? ? ? ? ? ? ? ? ? ? [文章編號]? 2096-0603（2019）36-0192-02

Microsoft Excel是微軟公司推出的辦公軟件Microsoft office的重要組件之一，它可以進行各種數(shù)據處理、統(tǒng)計分析和輔助決策等操作，廣泛應用于教學、管理、統(tǒng)計、財經等各個領域。其中，Excel內置的函數(shù)功能非常強大，合理適度的調用能大大提高學習、工作效率。現(xiàn)以中職概率教學內容中的一個實例——投擲硬幣實驗為例，來說明如何使用Excel內置函數(shù)進行數(shù)學實驗研究。

研究的問題是投擲兩枚硬幣，問投擲后，出現(xiàn)一枚正面朝上、一枚反面朝上的概率是多少？兩枚正面朝上和兩枚反面朝上的概率又各是多少？這里，規(guī)定有面值的那面為正面，有國徽的那面為反面。

首先，借助Excel的內置函數(shù)RAND（）、FIXED（）和IF（）函數(shù)模擬投擲硬幣實驗，隨機生成“正”“反”面信息。

其中，RAND（）函數(shù)為隨機數(shù)生成函數(shù)，F(xiàn)IXED（）函數(shù)為按照指定方式進行四舍五入的函數(shù)，IF（）函數(shù)為條件函數(shù)。

打開Excel2010，在A1單元格中輸入“第一枚硬幣”，在B1單元格中輸入“第二枚硬幣”，如下圖所示：

RAND（）函數(shù)沒有參數(shù)，它會產生一個大于或等于0且小于1的平均分布隨機數(shù)，只要雙擊插入該函數(shù)的單元格，再按Enter鍵，就會產生一個新的隨機數(shù)。

函數(shù)FIXED（number，decimals，no_commas）有三個參數(shù)，number表示要進行四舍五入并轉換成文本字符串的數(shù)，decimals是為一數(shù)值，用以指定小數(shù)點右邊的小數(shù)位數(shù)，參數(shù)no_commas為一邏輯值，若為TRUE時，不顯示逗號，忽略或為FALSE時，顯示千位分隔逗號。

函數(shù)IF（logical_test，value_if_true，value_if_false）也有三個參數(shù)，logical_test表示計算結果為True或False的任意值或表達式，value_if_true表示logical_test為True時返回的值，參數(shù)value_if_false表示logical_test為False時返回的值。

單擊A2單元格，利用上述函數(shù)，在編輯欄中構造嵌套函數(shù)：

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

上述公式的含義是：按照從里向外順序，第一層函數(shù)RAND（）表示產生一個大于或等于0且小于1的均勻分布隨機數(shù)。第二層函數(shù)FIXED（RAND（），0）表示對由RAND（）函數(shù)產生的隨機數(shù)進行四舍五入運算，由此，剛好可以得到兩個數(shù)，一個是0，一個是1，且得到0與得到1的概率相同，這正好對應投擲硬幣實驗中隨機出現(xiàn)的正面和反面兩種情況。這里，假定1對應正面，0對應反面，那么第三層函數(shù)IF（FIXED（RAND（），0）=“1”，“正”，“反”）表示當函數(shù)FIXED（RAND（），0）的結果等于1時，輸出“正”，反之，輸出“反”。

至此，單擊Enter鍵，通過構造的嵌套函數(shù)，A2單元格中隨機產生“正”面。按住填充柄向右拖動，復制A2單元格的公式至A3中，這時，A2和A3單元格就顯示出了第一次投擲兩枚硬幣的結果。

然后，借助IF（）和AND（）函數(shù)，把每一次隨機實驗的結果在單元格中顯示出來，以便后續(xù)進行統(tǒng)計計數(shù)工作。

針對第一次隨機實驗，單擊C1單元格，輸入“結果”。由于該隨機實驗包含4個基本事件：“正，正”“正，反”“反，正”和“反，反”，我們需要在C2單元格中顯示A2和B2單元格的結果，那么，應該如何操作？

單擊C2單元格，借助IF（）和AND（）函數(shù)，構造如下公式：

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

上述公式是利用三層IF（）函數(shù)，同時內嵌AND（）函數(shù)，將單元格A2、B2的結果自動顯示在C2單元格中。單擊Enter鍵，C2單元格會自動顯示出A2和B2單元格的結果。

采用框選方式選擇A2、B2和C2單元格，按住填充柄向下拖動至A101、B101和C101，共計100行。每一行相當于進行了一次實驗，100行相當于進行了100次投擲硬幣實驗。

最后，利用COUNTIF（）函數(shù)統(tǒng)計各個基本事件出現(xiàn)的次數(shù)。

分別在D1、E1、F1和G1中輸入“正，正”“正，反”“反，正”和“反，反”，如下圖所示：

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

該語句表示統(tǒng)計從C2到C101這100個單元格中，其值等于D1單元格“正，正”的單元格數(shù)量，也就是統(tǒng)計100次隨機實驗中基本事件“正，正”出現(xiàn)的次數(shù)。其中，在單元格的行號和列號前加“$”符號表示絕對引用，$C$2：$C$101即是絕對地址，在復制公式過程中，該地址不發(fā)生變化。

選擇D2單元格，按住填充柄向右拖動，另外三個基本事件的結果也會自動顯示出來。經計算，發(fā)現(xiàn)這四個數(shù)之和正好是100，然后插入餅圖。

通過上述餅圖，可直觀看出這四個基本事件出現(xiàn)的頻率。如果單擊一次“保存”命令，上述四個基本事件出現(xiàn)的次數(shù)就會更新一遍，相當于又做了一遍100次隨機實驗。從餅圖中各個事件出現(xiàn)概率的百分比可以看出，雖然單擊“保存”命令后這四個基本事件出現(xiàn)的次數(shù)會發(fā)生改變，但基本都各占四分之一左右。

通過以上三個實驗步驟，可以得出結論：通過進行多次投擲兩枚硬幣實驗，投擲后出現(xiàn)一枚正面朝上、一枚反面朝上的概率是四分之一，兩枚正面朝上和兩枚反面朝上的概率也各是四分之一。

上述內容只是提出了一種方法驗證投幣實驗中正反面出現(xiàn)的概率問題，當遇到其他數(shù)學問題時，如何利用Excel內置的函數(shù)解決，還需要教師根據教學內容自身特點，開動腦筋，恰當調用并反復實踐。

參考文獻：

[1]李廣全，李尚志.數(shù)學[M].北京：高等教育出版社，2009.

[2]劉松平，佟偉祥.計算機應用基礎[M].吉林：東北師范大學出版社，2015（1）：120-126.

◎編輯 原琳娜