利用Excel函數進行數學實驗的應用研究

季凌云

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

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

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

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

◎編輯 原琳娜