利用試算表模擬機率事件

Using Spreadsheet to Simulate Probability Events

10.6665/JLYIT.2016.15.37

陳武強(W. Drago Chen)

機率 ； 條件機率 ； 模擬 ； 試算表 ； 偽陽性 ； 生日悖論 ； 蒙特霍爾問題 ； probability ； conditional probability ； simulation ； spreadsheet ； false positive ； birthday paradox ； Monty Hall problem

蘭陽學報

15期（2016 / 07 / 01）

37 - 45

繁體中文

「機率」已經是現今社會上常用且常見的名詞，但不少人對於機率還是存有錯誤的觀念，尤其學生在碰到機率問題時，不容易有機會親自去體驗真實的情形，幾乎都是靠著理論的推導及單純的計算，難以將抽象概念轉化成為正確認知，因此我們藉由Excel軟體的特性及內建函數的應用來模擬實際的狀況，讓學生們乃至於社會大眾都能親眼看到隨機的變化與機率的奧祕。本文中將會介紹三個有名的機率問題，分別是蒙特霍爾問題、偽陽性及生日悖論，這三個問題同時兼具了知識性與啟發性，我們會先說明相關歷史、常見疑問或一般的直覺，接著利用機率論的定義、公式及定理探究各個問題的答案，看看兩者之間到底有多大的差異，最後再以試算表模擬的方法進行驗證，同時示範軟體的操作、函數的運用及數值的呈現。利用試算表進行模擬遠比撰寫程式碼再執行簡單許多，尤其表格式的介面以及立即性的計算，大家非常容易觀察與比較，只要模擬的次數夠多的話，可以觀察的是事件發生的變化，而比較的是機率數值的穩定，這些都不是定義、公式、定理能夠表現出來的，我們希望能夠積極推廣這套簡便的操作方式與模擬方法。

There exist a number of misconceptions about Probability though it is a commonly used term in today's society. In particular, when students are confronted with probability problems, they simply adopt theories and do mathematical calculations. They rarely have the opportunity to perform the generating process so as to learn accurate abstract probability concepts. In this paper, with Excel built-in function and simulation modeling, we are able to lead either students or the majority of people to reveal the mystery of random variation and probability. We present three well-known probability problems - Monte Hall problem, false positives and birthday paradox, all of which are informative and inspiring examples. First, we provide relevant background information about the problems. Next, we discuss general thoughts or questions related to the topic. Then, we investigate each problem and make comparisons by applying the probability theory, formulas and theorems. Finally, we perform the simulation using spreadsheets as a tool while we use the software to demonstrate its application functions and numerical presentations. The spreadsheet modeling is more convenient and easier compared to writing and running a program. The data frame which can make prompt calculations is very helpful for observation and comparison. With enough simulation modeling, we will be able to see the underlying data-generation process and compare the numerical stability. All these cannot be directly perceived through definitions, formulas or theorems. Thus, we hope to promote this simple convenient simulation method.

1. Excel Home(2015)。真正學會Excel 函數的8 堂課。碁峰資訊。
2. Gigerenzer, Gerd(2014).Risk Savvy: How to Make Good Decisions.USA:Penguin Books.
3. Ross, Sheldon(2002).A First Course in Probability.USA:Prentice Hall.
4. Xiong, Daguo(2003).The Natural Axiom System of Probability Theory: Mathematical Model of the Random Universe.USA:World Scientific Pub Co Inc..
5. 納西姆．尼可拉斯．塔雷伯(2014)。隨機騙局。臺灣:大塊文化。
6. 張振華(2008)。機率好好玩。臺灣:博雅書屋。
7. 捷爾德．蓋格瑞澤(2015)。機率陷阱。臺灣:商周出版。