葉帆++王文慶

摘 要：針對獨立院校概率統(tǒng)計課程的教學(xué)需要，將Excel引入到課程教學(xué)的數(shù)學(xué)實驗中，從隨機模擬實驗、隨機變量的二項，泊松，均勻，指數(shù)，正態(tài)等分布的概率計算、參數(shù)估計和假設(shè)檢驗等方面對Excel的應(yīng)用進行了初步的探討，不僅淡化理論分析與嚴(yán)謹(jǐn)?shù)淖C明，豐富教學(xué)內(nèi)容，增強知識應(yīng)用，更能提高教學(xué)效率，培養(yǎng)學(xué)生應(yīng)用軟件和解決問題的能力。

關(guān)鍵詞：Excel 概率統(tǒng)計 獨立院校

中圖分類號：G71 文獻(xiàn)標(biāo)識碼：A 文章編號：1672-3791（2017）06（c）-0155-05

為了使獨立院校學(xué)生畢業(yè)步入職場后更快適應(yīng)現(xiàn)代化經(jīng)濟管理工作，并形成個人獨到的工作能力和職業(yè)素質(zhì)，獨立院校的教學(xué)面臨著新的挑戰(zhàn)。尤其對于應(yīng)用型為主的獨立院校學(xué)生，基礎(chǔ)課教學(xué)要對今后就業(yè)中所需要的知識和技能要加強要求，而在理論分析與嚴(yán)謹(jǐn)?shù)淖C明上可適當(dāng)?shù)?。在概率統(tǒng)計的教學(xué)中，傳統(tǒng)的教材對內(nèi)容進行諸多的數(shù)學(xué)推導(dǎo)和演算，學(xué)生因為基礎(chǔ)有限望而生畏，并且這種概念化的教學(xué)方法使許多學(xué)生難于牢固掌握所學(xué)內(nèi)容，同時教師也因為辛苦演算后學(xué)生茫然而感到倦怠，讓原本情趣盎然的課程變得乏味，嚴(yán)重影響了教與學(xué)的融合。改變這種被動局面的根本出路在于改革傳統(tǒng)的課堂教學(xué)方法，充分利用現(xiàn)代化的教學(xué)手段，利用計算機實驗來配合課堂理論講授，使學(xué)生掌握牢固而靈活的知識。這樣，既加強了理論的直觀性，改善了教學(xué)效果，又訓(xùn)練了學(xué)生運用理論于實踐的能力。隨著計算機應(yīng)用的日益普及，這種雙管齊下、相輔相成的教學(xué)設(shè)想已經(jīng)成為可能[1-5]。

凡帶有統(tǒng)計功能的軟件都可作為概率論與數(shù)理統(tǒng)計數(shù)學(xué)實驗的軟件，如：Matlab，SAS，SPS等，這些軟件功能強大，能快速輸出結(jié)果，節(jié)省實驗的時間，但這些軟件對用戶要求較高。對于獨立院校的學(xué)生而言，概率統(tǒng)計課程往往課時較少，要求學(xué)生在短時間內(nèi)掌握上述專業(yè)統(tǒng)計軟件有很大難度。而Excel一直被認(rèn)為是“為Windows編寫的最好的應(yīng)用程序”[6]，是最普及的應(yīng)用軟件之一，并且功能強大，直觀簡便的數(shù)據(jù)輸入界面也使操作者應(yīng)用自如，附帶的分析工具可以為數(shù)理統(tǒng)計計算帶來極大便利，提高了計算效率。所以，Excel軟件是很多統(tǒng)計學(xué)專家推崇的普及型應(yīng)用軟件，將它作為概率統(tǒng)計教學(xué)的輔助工具是一個較好的選擇，可調(diào)動學(xué)生的學(xué)習(xí)熱情，活躍基礎(chǔ)課教學(xué)，提高教學(xué)質(zhì)量。

1 Excel在模擬隨機實驗中的應(yīng)用

概率統(tǒng)計的整個理論體系建立在幾條基本的公理基礎(chǔ)之上，具有嚴(yán)密的邏輯思維系統(tǒng)和豐富多彩的理論，所以要求學(xué)生掌握純數(shù)學(xué)的邏輯思考方法，同時概率具有統(tǒng)計定義，還可以讓學(xué)生通過實驗和觀察的思維方式對概率有更刻的理解。尤其當(dāng)遇到理論性很強的定理或者求解比較復(fù)雜繁瑣的概率題時，若通過設(shè)計計算機實驗來演示此類過程，可在短時間內(nèi)多次快速重復(fù)，便可讓學(xué)生對試驗結(jié)果的隨機性和規(guī)律性獲得直觀而生動的理解效果。Excel軟件集成了VBA語言開發(fā)環(huán)境，編寫簡單，可直接調(diào)用Excel本身具有的各種內(nèi)置函數(shù)，表格本身可以直接作為輸入、輸出界面，能與Excel無縫集成[6-9]?；诖耍衫肊xcel中的VBA語言編寫程序來模擬隨機試驗，從而得出結(jié)論。

例1 箱子內(nèi)有40張獎券，其中有20張獎券能中獎，另外20張獎券空白，甲乙兩人輪流在箱子中抽取獎券，每次只能抽一張且不放回，問甲先中獎的概率大還是乙先中獎的概率大？

解：P（甲先中獎）=20/40+（20/40）*（19/39）*（20/38）+ （20/40）*（19/39）*

（18/38）*（17/37）*（20/36）+（20/40）*（19/39）*（18/38）*（17/37）*（16/36）*（15/35）*（20/34）+……+（20/40）*（19/39）*（18/38）*（17/37）*（16/36）*（15/35）*（14/34）*（13/33）*（12/32）*（11/31）*（10/30）*（9/29）*（8/28）*（7/27）*（6/26）*（5/25）*（4/24）*（3/23）*（2/22）*（1/21）

此題可利用概率樹將甲乙先中獎所有情況羅列清楚，情況多而繁瑣，尤其是當(dāng)獎券數(shù)增多時該計算方法不具有推廣性，若不借助計算機，要計算甲乙分別先中獎的概率相當(dāng)有難度。所以我們利用Excel中的VBA語言編寫程序模擬上述隨機試驗：

Public Function prowin（） As String

Dim n， m， k， i， j， y， p As Integer

Dim jia， yi As Double

n = 100000

m = 0

k = 0

For i = 1 To n

j = 0

y = 0

p = 0

Do While j = 0 And y = 0

If Round（Rnd（） * （40 - p）， 0） < 20 Then

j = j + 1

ElseIf Round（Rnd（） * （39 - p）， 0） < 20 Then

y = y + 1

Else

p = p + 2

End If

m = m + j

k = k + y

Loop

Next i

jia = m / n

yi = k / n

prowin = "甲" & jia & " 乙" & yi

End Function

顯示結(jié)果：甲0.66173 乙0.33827

例2 參加一個游戲，有三扇門，一門后有一輛車，另兩門后有羊，主持人讓你隨意挑選。當(dāng)你選擇了一扇門后，主持人隨后打開一扇后面有羊的門。此時問是否換到剩下的一扇門？為什么？概率是多少？假設(shè)主持人知道汽車在哪扇門后，他是故意打開羊的門給你看。

這是一個曾經(jīng)在美國《檢閱》雜志的“瑪麗蓮”專欄上介紹過的有趣的概率問題，當(dāng)時在美國引起了轟動。從二年級的小學(xué)生到大學(xué)的博士都爭相參加這個題目的討論。有趣的是，在給該專欄主持人瑪麗蓮小姐的10000多封來信中，大約有1000封是具有博士頭銜的讀者寫的，他們認(rèn)為瑪麗蓮小姐的答案是錯的，而事實上，錯的恰恰是博士們。通常的想法是，主持人既然把沒有車的那扇門打開了，剩下的兩扇門后面是車是羊的可能性各占一半，堅持原來的選擇也好，換選也好，選中車的機會都是二分之一。 瑪麗蓮小姐公布的答案是：“應(yīng)該換選另一扇門。”可以這樣考慮，第一次選擇選中車的情況下（可能性1/3），換門得不到車，故這個可能性為1/3；第一次選中羊的情況下（可能性2/3），換門一定會得到車，故此時可能性為2/3。由此可見，最佳策略就是換門，得到車的可能是堅持最初選擇門的兩倍。

此題中很多學(xué)生錯誤地認(rèn)為主持人去掉一個門，等于讓觀眾在剩下的兩個門里選一個，二選一，自然換與不換的概率都是1/2，要說明這類觀點的錯誤之處很不容易，學(xué)生也不易理解。所以我們利用Excel中的VBA語言編寫程序模擬上述隨機試驗：

Public Function gameresult（） As String

Dim n， m， k， i As Integer

Dim change， unchange As Double

n = 10000

m = 0

k = 0

For i = 1 To n

If Int（3* Rnd（）） =0 Then

m=m + 1

ElseIf Int（3* Rnd（））=1 Then

k = k + 1

Else

k = k +1

End If

Next i

change = k / n

unchange = m / n

gameresult = "換" & change & " 不換" & unchange

End Function

顯示結(jié)果：換0.6668 不換0.3332

2 Excel在利用隨機變量分布計算概率中的應(yīng)用

在概率論的教學(xué)中，對于離散型隨機變量累積概率的計算和連續(xù)型隨機變量概率的積分運算，教師無法面面俱到地進行演示，學(xué)生往往半途而廢沒有計算出概率的最后結(jié)果或者計算出錯誤的結(jié)果，甚至少數(shù)學(xué)生用積分求出概率是負(fù)數(shù)或大于1的數(shù)而全然不知。所以我們將黑板演算與借助Excel里的統(tǒng)計函數(shù)相結(jié)合的方式提高了教師教學(xué)的信息量，同時也訓(xùn)練了學(xué)生借助軟件提高計算結(jié)果的正確性。

例3 大學(xué)英語六級考試（舊）是為全面檢驗大學(xué)生英語水平而設(shè)置的一種考試，具有一定的難度。除英文寫作占15分外，其余85道多種答案選擇每題1分，即每一道題附有A，B，C，D四個選擇答案，要求考生從中選擇最佳答案。這種考試方式使有的學(xué)生產(chǎn)生想碰運氣的僥幸心理，那么靠碰運氣能通過英語六級考試嗎？假設(shè)英文寫作恰好得9分。

解 按及格計算，85道選擇題必須答對51道題以上。如果瞎猜測的話，則每道題答對的概率為1/4，答錯的概率是3/4。顯然，各道題的解答互不影響，因此，可以將解答85道選擇題看成85重貝努利試驗。設(shè)答對的題數(shù)為隨機變量X，則靠碰運氣能通過的概率為：

此題屬于離散型隨機變量的二項分布，若用手工計算不現(xiàn)實，用一般計算器計算在概率累加上比較繁瑣，學(xué)生往往就擱置過程沒有結(jié)果，對碰運氣通過的概率大小沒有清楚的認(rèn)識，其中多數(shù)學(xué)生的想法和最終的概率結(jié)果具有很大的偏差。我們利用Excel中的統(tǒng)計函數(shù)BINOMDIST函數(shù)（二項分布），格式為：BINOMDIST（Number_s，Trials，

Probability_s，Cumulative），其中的參數(shù)對應(yīng)分別為實驗的成功次數(shù)，實驗的總次數(shù)，一次實驗成功的概率，最后一個參數(shù)是否累計：若填寫“true”，則實現(xiàn)概率的累加，從隨機變量能取到的最小值的概率累加到實驗成功次數(shù)的概率；若填寫“false”，則單求實驗成功次數(shù)為當(dāng)次的概率。上述例題中計算公式為：=1-BINOMDIST（50，85，0.25，True），計算結(jié)果為：0.00000000000832，詳細(xì)見圖1。

例4 到某服務(wù)單位辦事總要排隊等待。設(shè)等待時間T是服從參數(shù)為1/10的指數(shù)分布的隨機變量，某人到此處辦事，等待時間若超過15 min，他就憤然離去。設(shè)此人一個月去該處10次，求至少有2次憤然離去的概率。

此題涉及連續(xù)型隨機變量與離散型隨機變量，是指數(shù)分布和二項分布相結(jié)合的概率題。由于本獨立學(xué)院本科生合班上課，人數(shù)眾多，文理學(xué)生混合，水平參差不齊，整體數(shù)學(xué)基礎(chǔ)不佳，這種現(xiàn)狀導(dǎo)致大量教學(xué)時間花在基本運算上，而運算能力強的學(xué)生很多時間處于閑置狀態(tài)，不滿足信息量的現(xiàn)狀，這樣不利于調(diào)動學(xué)生學(xué)習(xí)的積極性與能動性， 影響了教學(xué)效果。因此教師可先黑板演示積分過程來計算概率，同時可利用Excel中的統(tǒng)計函數(shù)EXPONDIST函數(shù)（指數(shù)分布），格式為：EXPONDIST（X，Lambda，Cumulative），其中的參數(shù)對應(yīng)分別為區(qū)間點，指數(shù)分布的參數(shù)，最后一個參數(shù)是否累計：若填寫“true”，則求出分布函數(shù)值，即求出隨機變量從無窮取到區(qū)間點的累加概率，若填寫“false”，則出概率密度函數(shù)值。上述例題中計算公式為：=1- EXPONDIST（15，0.1，True），計算結(jié)果為：0.22313。再利用二項分布的統(tǒng)計函數(shù)，其中參數(shù)“一次實驗成功的概率”，引用指數(shù)分布的計算結(jié)果，得到最終結(jié)果為：0.689946。詳細(xì)見圖2和圖3。

3 Excel在參數(shù)估計和假設(shè)檢驗中的應(yīng)用

在數(shù)理統(tǒng)計教學(xué)中，課程的教學(xué)重點仍然在于數(shù)理統(tǒng)計的基本方法與原理的介紹上，所以采用板書步步演示是非常必要的。但是不管對于教師或?qū)W生，在教與學(xué)中經(jīng)常會遇到大量統(tǒng)計數(shù)據(jù)帶來的計算量問題，對于一些缺乏主動性的學(xué)生會因為各種借口，在計算過程中就擱置了進而影響后續(xù)的計算，推導(dǎo)和結(jié)論。所以我們將理論教學(xué)與Excel演示合理結(jié)合，減少不必要講授的詳細(xì)手工計算步驟，讓學(xué)生多實際操練，使其能更有效地理解統(tǒng)計分析的各種方法，并能借助于計算機完成統(tǒng)計計算，具備一定的統(tǒng)計應(yīng)用能力。

例5 某旅行社為調(diào)查當(dāng)?shù)芈糜握叩钠骄M額，隨機訪問了100名旅游者，得知平均消費額元。根據(jù)經(jīng)驗，已知旅游者消費額服從正態(tài)分布，且標(biāo)準(zhǔn)差元，求該地旅游者平均消費額的置信度為0.95的置信區(qū)間。

對于此類的區(qū)間估計題，教學(xué)重點在于樣本均值統(tǒng)計量的統(tǒng)計原理介紹上，之后只要將數(shù)據(jù)代入?yún)^(qū)間估計的公式即可。若題目中的統(tǒng)計數(shù)據(jù)構(gòu)造不恰當(dāng)，學(xué)生往往嫌麻煩就不再繼續(xù)，我們可以利用Excel中的統(tǒng)計函數(shù)NORMSINV函數(shù)求出的值，格式為：NORMSINV（probability），其中probability為正態(tài)分布的概率。上例中的計算公式為：=80-NORMSINV（0.975）*12/sqrt（100）和=80+NORMSINV（0.975）*12/sqrt（100），計算結(jié)果分別為：77.64804，82.35196，詳細(xì)見圖4和圖5。

例6 某化學(xué)日用品有限責(zé)任公司用包裝機包裝洗衣粉，洗衣粉包裝機在正常工作時，裝包量（單位：g），每天開工后，需先檢驗包裝機工作是否正常。某天開工后，在裝好的洗衣粉中任取9袋，其重量如下：505 499 502 506 498 498 497 510 503假設(shè)總體標(biāo)準(zhǔn)差不變，即，試問這天包裝機工作是否正常（）？

對于此類假設(shè)檢驗題，為了讓學(xué)生更深刻地理解假設(shè)檢驗的原理及其步驟，采用黑板演示與利用Excel軟件相結(jié)合?？捎肊xcel中的統(tǒng)計函數(shù)ZTEST對上例進行檢驗，所謂ZTEST函數(shù)就是正態(tài)檢驗函數(shù)，其值為返回檢驗的單位概率，格式為：ZTEST（array，，sigma），其中Array為用來檢驗的數(shù)組或數(shù)據(jù)區(qū)域，為被檢驗的值，Sigma為總體的標(biāo)準(zhǔn)差（已知），則ZTEST（array，，sigma）=1- NORMSDIST

4 結(jié)語

Excel還有許多統(tǒng)計工具，在數(shù)理統(tǒng)計中的方差分析、相關(guān)分析與線性回歸中有著重要的應(yīng)用，由于課時較少，這部分內(nèi)容可作為學(xué)生的進一步提高之用，這里不再列舉?？傊瑢⒏怕式y(tǒng)計與Excel結(jié)合起來，對教師而言，有利于改進教學(xué)內(nèi)容和教學(xué)方法，能加強教學(xué)的直觀性、生動性和趣味性，教學(xué)中的重點和難點可得到更深刻的理解，而處理重復(fù)的計算可交給計算機去完成。對學(xué)生而言，有利于從復(fù)雜的概率思想和繁瑣的計算中解放出來，縮小文理科之間計算上的差異，更多地理解統(tǒng)計思想和概率意義，有利于提高概率統(tǒng)計的學(xué)習(xí)效率和質(zhì)量，同時提高計算機實際應(yīng)用能力及綜合素質(zhì)。

參考文獻(xiàn)

[1] 卞金金，徐福蔭.基于智慧課堂的學(xué)習(xí)模式設(shè)計與效果研究[J].中國電化教育，2016（2）：64-69.

[2] 李曉毅，徐兆棣.SPSS軟件在概率統(tǒng)計課程設(shè)置中的主導(dǎo)地位及應(yīng)用[J].沈陽師范大學(xué)學(xué)報（自然科學(xué)版），2010，28（1）：24-26.

[3] 劉明姬.大學(xué)數(shù)學(xué)課程實驗在教學(xué)改革中的探索——Mathematica在概率統(tǒng)計中的應(yīng)用教學(xué)研究[J].吉林廣播電視大學(xué)學(xué)報，2016（5）：82-83.

[4] 徐紅敏，陳爽，肖銜，等.MATLABGUI在數(shù)理統(tǒng)計教學(xué)中的應(yīng)用研究[J].北京石油化工學(xué)院學(xué)報，2015，23（4）：60-63.

[5] 劉東海，彭丹.基于R的概率統(tǒng)計實驗教學(xué)模式探討[J].當(dāng)代教育理論與實踐，2014，6（7）：43-45.

[6] John Walkenbach著，邱燕明，趙迎等譯. Excel2003公式與函數(shù)應(yīng)用[M].北京：電子工業(yè)出版社，2004.

[7] 劉宇. Excel高效辦公—VBA入門與實戰(zhàn)[M].北京：人民郵電出版社，2006.

[8] 韓小良，韓舒婷.Excel VBA從入門到精通[M].北京：中國鐵道出版社，2006.

[9] 張永成，王洪輝，譚桂花.基于Excel VBA的圖解力度參數(shù)計算[J].程度理工大學(xué)學(xué)報（自然科學(xué)版），2010，37（6）：650-653.