標題:
Excel 問題
發問:
若果我想在Excel隨機抽 1 個數字 ( 係 1 至 50 之中抽 ) 。 但1-10抽中既機率係20%，11-30抽中既機率係55%，31-50抽中既機率係25%。 咁請問條式應該點寫 ???
以下的 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 給我： tomson8484@yahoo.com.hk
Excel 問題
發問:
若果我想在Excel隨機抽 1 個數字 ( 係 1 至 50 之中抽 ) 。 但1-10抽中既機率係20%，11-30抽中既機率係55%，31-50抽中既機率係25%。 咁請問條式應該點寫 ???
此文章來自奇摩知識+如有不便請留言告知
最佳解答:以下的 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 給我： tomson8484@yahoo.com.hk
文章標籤
全站熱搜
留言列表
