Excel 問題

以下的 macro,
1. 先抽出一個 1 - 100 的隨機數,
2. 然後將 1-20 的數 (即 20%) 壓縮為 1-10.
3. 將 21-75 的數 (即 55%) 壓縮為 11-30.
4. 將 76-100 的數 (即 25%) 壓縮為 31-50.
並重複200 次來測試是否真的在所要求的機率之內.
結果令人滿意.
真正要用時(即不是測試, 而只是 generate 一個隨機數), 只要將 for i = 1 to 200 的 200 改為 1 就可以了.

Sub rand_gen()
For i = 1 To 200
x = Rnd() * 100 + 1

If x < 21 Then
y = Int(x / 2)
GoTo res
End If
If x >= 21 And x < 76 Then
z = x - 21
y = Int(z / 2.75)
y = y + 11
GoTo res
End If
If x >= 76 Then
z = x - 76
y = Int(z / 1.25)
y = y + 31
GoTo res
End If

res:
Cells(i, 1) = y
Next i
End Sub

由于用兩條公式, 如公式需下拉或右拉會相當麻煩及借用相當多輔助格, 效果並不理想。
故建議用 CHOOSE取代IF, 可以將兩條公式合併而不需要輔助格
任何一格輸入:
=INT(CHOOSE(LOOKUP(RAND(),{0,0.2,0.55},{1,2,3}),RAND()*10+1,RAND()*20+11,RAND()*20+31))
下拉, 右拉均可以

2009-06-22 00:23:19 補充：
樓上公式有錯, 更正為(0.55應為0.75):
=INT(CHOOSE(LOOKUP(RAND(),{0,0.2,0.75},{1,2,3}),RAND()*10+1,RAND()*20+11,RAND()*20+31))

2009-06-22 00:34:07 補充：
vba代碼:
Function irand()
Application.Volatile
x = Rnd()
Select Case x
Case Is > 0.75
irand = Int(Rnd() * 20 + 31)
Case Is > 0.2
irand = Int(Rnd() * 20 + 11)
Case Else
irand = Int(Rnd() * 10 + 1)
End Select
End Function
任何一格 輸入 =irand()
可下拉, 右拉

2009-06-22 00:39:06 補充：
樓上代碼中加上 Application.Volatile, 即程式容許易變
按F9 或工作表有任何編輯, 所有數據都會自動更新, 效果與之前的公式辦法一樣。
如不加上Application.Volatile, 傳值便不會易變

由於抽中 1-50 的機會不一樣，我們需要兩條公式才能解決問題：(後面是詳解)

(公式一) 在 A1 輸入以下公式
=(INT(RAND()*100)+1)/100

(公式二) 在 B1 輸入以下公式，得出答案
=IF(A1<=20%,INT(RAND()*10)+1,IF(A1<=75%,INT(RAND()*20)+11,INT(RAND()*20)+31))


解題基本邏輯：
1. 首先，按照指定的機會率抽出範圍 1-10 (20%) 或 11-30 (55%) 或 31-50 (25%)。
2. 然後，再從範圍中隨機抽出數字，便是答案。


詳細解釋：
1. 由於抽中 1-50 的機會率不一樣，而 Excel 只能按相同機會率抽出數字，所以我們不能直接用公式得出數字，而需要先將資料轉化成可以計算的，就是利用累計機會率(Cumulative Probability)。

數字 ::::: 機會率 ::::: 累計機會率
1-10 :::::::: 20% :::::::: 20%
11-30 :::::: 55% :::::::: 75% (抽中30或以下的機會率=20%+55%)
31-50 :::::: 25% :::::::: 100% (抽中50或以下的機會率)

2. 當算出累計機會率後，便可以按以下邏輯篩選範圍：
當隨機數字是 1-20%，範圍是 1-10；(這時，Excel 會停止計算下一步)
否則，數字等於或少於75%，範圍是 11-30；
其他，即數字大於75%的，範圍便是 31-50。

利用(公式一)，可以得到 1-100% 的隨機數：
(INT(RAND()*100)+1)/100

利用(公式二)，用 if 篩選範圍，再用 rand 得出答案：
INT(RAND()*10)+1 是隨機抽出 1-10 的意思
INT(RAND()*20)+11 是隨機抽出 11-30 的意思
INT(RAND()*20)+31 就是隨機抽出 31-50 的意思

測試方法：
你可以把 A1 和 B1 的公式，向下複製至少 2000 格(但不要複製太多，太多會當機)，再統計 1-10 / 11-30 / 31-50 的出現率。你會發現大概就是 20%、55%、25%。



如有不明白可 e-mail 給我：
[email protected]