2010年12月29日 星期三

用 Excel 模擬骰子拋擲結果

我想要用 Excel 的亂數函數 Rand() 來模擬骰子的拋擲結果。

依據 Rand() 的說明文件,如果想要得到 a~b 之間的亂數,就用 =Rand()*(b-a)+a 的方式計算。我想要模擬骰子拋出 1~6 的數字,因此就以公式 =Rand()*(6-1)+1 丟入 Excel 中做計算。

啊,對了,因為我祇想要整數,所以用四捨五入函數 Round() 來對產生的亂數處理一下,整個公式變成為 =Round( Rand()*5+1,0) 。

將公式輸入 Excel 果然得到 1~6 的亂數,太好了。

ㄟ,等一下,這個公式在拋擲次數少的時候看起來一切正常,但是模擬拋擲 1000 次、10000 次時,就會明顯發現 1 & 6 出現的機會祇有其他數字的一半。

呣,怎麼會這樣呢?將問題丟到網路上,沒多久,好友帆帆就來解答我的疑惑了。

我們仔細看看 =Round(Rand()*5+1,0) 這個公式,裡面的 Rand()*5 會產生 0~4.99999 的亂數。其中:

  • 0~0.49999: +1 後為 1~1.49999,四捨五入為 1
  • 0.5~1.49999: +1 後為 1.5~2.49999,四捨五入為 2
  • 1.5~2.49999: +1 後為 2.5~3.49999,四捨五入為 3
  • 2.5~3.49999: +1 後為 3.5~4.49999,四捨五入為 4
  • 3.5~4.49999: +1 後為 4.5~5.49999,四捨五入為 5
  • 4.5~4.99999: +1 後為 5.5~5.99999,四捨五入為 6

仔細看上述說明,要得到 1 或 6,原始的亂數必須介於 0~0.49999 & 4.5~4.99999 之間,大概都祇有 0.5 的區間。而要得到 2、3、4、5 這些數字,都有完整的 1 個區間可以得到這些數字。因此在拋擲量大時就會顯現 1 & 6 得到的次數祇有其他數字的一半。

如果要得到正確的骰子結果,應該將公式改為:=Int(Rand()*6+1) (Int 函數為取整數值的意思)。其中 Rand()*6 會得到 0~5.999999 的值:

  • 0~0.99999:+1 為 1~1.99999,取整數值為 1
  • 1~1.99999:+1 為 2~2.99999,取整數值為 2
  • 2~2.99999:+1 為 3~3.99999,取整數值為 3
  • 3~3.99999:+1 為 4~4.99999,取整數值為 4
  • 4~4.99999:+1 為 5~5.99999,取整數值為 5
  • 5~5.99999:+1 為 6~6.99999,取整數值為 6

公平骰子擲 6000 次時各數字出現次數
圖、公平骰子擲 6000 次時各數字出現次數

利用這樣的公式,得到 1~6 的每個數字的區間都相同,因此出現 1~6 的機會就都一樣了。當我將這個公式執行 6000 次,每個數字出現的機會都趨近相同 (約略各 1000 次)。所以,要用 Excel 模擬骰子要很小心啊,一個沒注意就把出現機率弄錯了,變成一顆會作弊的骰子啊。

附註 -- 與帆帆的對話

  內容

帆帆

因為你要用 continue 的機率函式模擬 discrete 機率函式,rand()*5 才只有五單位,要用五單位的機率密度函數來模擬 1,2,3,4,5,6 六單位的不連續機率密度函數,就分的不平均,又剛好四捨五入,所以 1,6 只分到 0.5 的機率密度。

Yukie

依照 Rand() 的說明是,要求 a-b 之間的數字,就用 rand()*(b-a)+a 所以我才這樣寫。因為我想求 1-6。

帆帆

它那個說明是只針對連續型的機率密度函數才能這樣做。

rand()*(b-a)+a, 如果用0,1(正反面)來算,rand*(1-0)+0,如果是 round,剛好四捨五入就可以用 round; 但是用 int 就只會出現 0,出現 1 的機會幾乎是 0。

所以要以連續機率密度函數去 mapping 不連續機率密度函數會需要注意轉換的計算方式。

Technorati : , , , , , , ,