エクセルを用いて、重みづけ・順位づけをした抽選を行いたいです。

Question

重みづけ、順位付けの抽選を行いたいです。

例として以下の事を挙げます。
当選確率が5倍、4倍、3倍、2倍、1倍の人が各1人ずついます。
その5人の中から、順位をつけて1～3位まで抽選で選出します。
どのような数式を用いて抽選を行えばよいのでしょうか？

ちなみに、現在試したことは、
各人に倍率に応じた区切り値を与え、その範囲内で乱数を発生させ、当選者を選出。
しかし、この方法だと乱数の重複や当選者の重複が出てしまいます。
発生させた乱数を固定し、rank関数によって順位付けを行うという方法もあるようですが、後述の理由によりそれ以外の方法で抽選を行いたいです。

よって、ポイントとしては、1度に当選者を選出できることと、発生させた乱数が重複しないこと、当選者が重複しないことかな思います。

ただし、倍率や人数は例として挙げただけなので、数がもっと増えても適用できるような方法でお願いします。
また、F9による更新で毎回変化させられるようにしたいです。（人が見ている中で抽選を行うので、演出としてF9長押しで抽選を行いたいと考えています）

条件が多いですが、よろしくお願いいたします。

Comments

[ttyp03]

順位付けの仕様は？何をもってして1位になるのでしょうか。

[yuuskeccho]

各倍率が1人ずつで5パターンの倍率があるとしたら、5倍の人は100%当選させないとまずいですよね？
あと、1～3位の当選、つまり3人しか当選できないとしたら、5倍の人は最低でも3位には入っているということですかね？

[momon-ga]

> 5倍の人は100%当選
確率とは？
もし、そのような仕様だったら、単純に倍率の高い順に３名選べばいいのでは？

[minamikaze3]

順位付けは全くの抽選で行う予定です。

また、当選確率についてですが、持ち票数というか応募票数というか、その様にとらえていただければと思います。
なので、5倍の確率の人は、5/(5+4+3+2+1)の確率で当選する、ということです。

[Zuishin]

それならそのように計算したらいいと思います。

0 から 5+4+3+2+1 までの乱数を発生し、その乱数が 1 未満なら 1 倍の人が当選
2+1 未満 1 以上なら 2 倍の人が当選
3+2+1 未満 2+1 以上なら 3 倍の人が当選

[minamikaze3]

質問の意図が分かりにくく、回答を困らせてしまい申し訳ありません。
皆様のおかげで無事に解決できました！
ありがとうございます！

Answer 1

こんな感じでいけそうです。
当選倍率を積み上げてRANDと比較で当選者決定、当選者だけ除いて同じことを繰り返します。
(RANDよりRANDBETWEENの方が綺麗だったか…)

---

すいません1つ訂正です。
「=IF(AND(C2<D$2,C3>D$2),TRUE,FALSE)」と書きましたが、
「=IF(AND(C2<=D$2,C3>D$2),TRUE,FALSE)」とすべきでした。
不等号に両方等号が入っていないと、ごく稀に乱数が境目の値に等しい場合に誰も当選しなくなります。

なおRANDBETWEENを使ったということで、それならこのような微妙な問題は起こりませんので大丈夫です。

Comments

[minamikaze3]

なるほど！
これで当選者を取り除いていけるのですね。

[minamikaze3]

randbetweenでやりましたが、当選者を0にして次々に計算していくという方法を式付き示していただいたで、ベストに選ばせていただきました！
ありがとうございます！

[minamikaze3]

ご丁寧にありがとうございます！
ちゃんと等号付き不等号使いましたよ～

Answer 2

抽選がよくわらかんけど・・・

5倍の確率の人は、5/(5+4+3+2+1)の確率で当選する

の部分だけ。

RANDBETWEEN 関数というのがあります。
指定した範囲の乱数を生成します。
以下のように設定すれば、１から倍率（？）の合計の範囲をランダムに出します。
これで、

発生させた乱数が重複しないこと、当選者が重複しないこと

を満たせると思います。
また、当選確率を100倍にしても値を変更するだけです。

当選回数ですが、

COUNTIFS関数というのがあり数値範囲を指定してカウントを取れます。

画像の例だと、
１ならC列、２，３ならD列、４，５，６ならE列が当選という感じで
E列なら、１＋２より大きくて、１＋２＋３以内という条件なので、４，５，６が当選
（範囲指定に直前のSUMより大きくて、自分の列までの合計以内という条件でヒット）

※ちなみに、１とか０とか各抽選結果は単純に上記の条件をAND関数でつなぐ感じ。
最初は、１，０表示してSUMしてたけどCOUNTIFS使うと、なくてよかった。

抽選増やすのは行のコピー、挿入でいけるけど、ここが微妙な気もする

こんな感じにすると、当選確率は５倍でも必ず１位になるわけじゃないようになります。

追記

箱に1から5の数字のかかれた紙が数字の枚数分入っていて

3つの数字がそろうまで箱から引く
早く引けた順がランクってイメージで

１度当選したら、ゼロにしてあとは上記の繰り返し。

Comments

[minamikaze3]

なるほどです。
ただ、回数を重ねると確率が乗算されていき、確率の低い人が当選することはほとんどなくなってしまうのではないかと。。。。
ですので、一度の抽選（選出）で3人を抽出できないでしょうか？？

[momon-ga]

ランク付けのイメージが、わからないのですが
今回のケースだと、箱に1から5の数字のかかれた紙が数字の枚数分入っていて
3つの数字がそろうまで箱から引く
早く引けた順がランクってイメージですか？

[momon-ga]

そもそも順位づけは仕様になく、ただ三人選ぶだけでよい？

[minamikaze3]

そうですね。どちらかというと、追記に書いていただいたような、箱から紙を引くイメージが合っています。

[minamikaze3]

おかげさまで解決できました！
ありがとうございます！
やはり、randよりrandbetweenの方がキレイですね。

Answer 3

当選確率2倍というのが2回参加しているのと同じということであれば、こうなるでしょう。

=MAX(RAND(),RAND())

参照で倍率を変えられるようにすることは、泥臭いですがこのようにして可能です(B列に倍率が入っているという想定です)

=MAX(RAND(),IF(B2>1,RAND(),0),IF(B2>2,RAND(),0),IF(B2>3,RAND(),0),IF(B2>4,RAND(),0))

これにRANK()関数を使えばいいです。そうそう被ることはないと思いますが、気になるようであれば COUNTIF() を使って重複しないようにできるようです。
https://blog-tips.sekenkodqx.jp/2019/10/15/excel-same-rank/

-- 追記
255倍までは増やせました。MAX 関数の制限により引数が最大255個になっているためこれ以上増やせませんが、関数を重ねることで増やせると思います。と言っても今度は文字数制限があるので400ちょっとまでです。

=MAX(RAND(),IF(B2>1,RAND(),0),IF(B2>2,RAND(),0),IF(B2>3,RAND(),0),IF(B2>4,RAND(),0),IF(B2>5,RAND(),0),IF(B2>6,RAND(),0),IF(B2>7,RAND(),0),IF(B2>8,RAND(),0),IF(B2>9,RAND(),0),IF(B2>10,RAND(),0),IF(B2>11,RAND(),0),IF(B2>12,RAND(),0),IF(B2>13,RAND(),0),IF(B2>14,RAND(),0),IF(B2>15,RAND(),0),IF(B2>16,RAND(),0),IF(B2>17,RAND(),0),IF(B2>18,RAND(),0),IF(B2>19,RAND(),0),IF(B2>20,RAND(),0),IF(B2>21,RAND(),0),IF(B2>22,RAND(),0),IF(B2>23,RAND(),0),IF(B2>24,RAND(),0),IF(B2>25,RAND(),0),IF(B2>26,RAND(),0),IF(B2>27,RAND(),0),IF(B2>28,RAND(),0),IF(B2>29,RAND(),0),IF(B2>30,RAND(),0),IF(B2>31,RAND(),0),IF(B2>32,RAND(),0),IF(B2>33,RAND(),0),IF(B2>34,RAND(),0),IF(B2>35,RAND(),0),IF(B2>36,RAND(),0),IF(B2>37,RAND(),0),IF(B2>38,RAND(),0),IF(B2>39,RAND(),0),IF(B2>40,RAND(),0),IF(B2>41,RAND(),0),IF(B2>42,RAND(),0),IF(B2>43,RAND(),0),IF(B2>44,RAND(),0),IF(B2>45,RAND(),0),IF(B2>46,RAND(),0),IF(B2>47,RAND(),0),IF(B2>48,RAND(),0),IF(B2>49,RAND(),0),IF(B2>50,RAND(),0),IF(B2>51,RAND(),0),IF(B2>52,RAND(),0),IF(B2>53,RAND(),0),IF(B2>54,RAND(),0),IF(B2>55,RAND(),0),IF(B2>56,RAND(),0),IF(B2>57,RAND(),0),IF(B2>58,RAND(),0),IF(B2>59,RAND(),0),IF(B2>60,RAND(),0),IF(B2>61,RAND(),0),IF(B2>62,RAND(),0),IF(B2>63,RAND(),0),IF(B2>64,RAND(),0),IF(B2>65,RAND(),0),IF(B2>66,RAND(),0),IF(B2>67,RAND(),0),IF(B2>68,RAND(),0),IF(B2>69,RAND(),0),IF(B2>70,RAND(),0),IF(B2>71,RAND(),0),IF(B2>72,RAND(),0),IF(B2>73,RAND(),0),IF(B2>74,RAND(),0),IF(B2>75,RAND(),0),IF(B2>76,RAND(),0),IF(B2>77,RAND(),0),IF(B2>78,RAND(),0),IF(B2>79,RAND(),0),IF(B2>80,RAND(),0),IF(B2>81,RAND(),0),IF(B2>82,RAND(),0),IF(B2>83,RAND(),0),IF(B2>84,RAND(),0),IF(B2>85,RAND(),0),IF(B2>86,RAND(),0),IF(B2>87,RAND(),0),IF(B2>88,RAND(),0),IF(B2>89,RAND(),0),IF(B2>90,RAND(),0),IF(B2>91,RAND(),0),IF(B2>92,RAND(),0),IF(B2>93,RAND(),0),IF(B2>94,RAND(),0),IF(B2>95,RAND(),0),IF(B2>96,RAND(),0),IF(B2>97,RAND(),0),IF(B2>98,RAND(),0),IF(B2>99,RAND(),0),IF(B2>100,RAND(),0),IF(B2>101,RAND(),0),IF(B2>102,RAND(),0),IF(B2>103,RAND(),0),IF(B2>104,RAND(),0),IF(B2>105,RAND(),0),IF(B2>106,RAND(),0),IF(B2>107,RAND(),0),IF(B2>108,RAND(),0),IF(B2>109,RAND(),0),IF(B2>110,RAND(),0),IF(B2>111,RAND(),0),IF(B2>112,RAND(),0),IF(B2>113,RAND(),0),IF(B2>114,RAND(),0),IF(B2>115,RAND(),0),IF(B2>116,RAND(),0),IF(B2>117,RAND(),0),IF(B2>118,RAND(),0),IF(B2>119,RAND(),0),IF(B2>120,RAND(),0),IF(B2>121,RAND(),0),IF(B2>122,RAND(),0),IF(B2>123,RAND(),0),IF(B2>124,RAND(),0),IF(B2>125,RAND(),0),IF(B2>126,RAND(),0),IF(B2>127,RAND(),0),IF(B2>128,RAND(),0),IF(B2>129,RAND(),0),IF(B2>130,RAND(),0),IF(B2>131,RAND(),0),IF(B2>132,RAND(),0),IF(B2>133,RAND(),0),IF(B2>134,RAND(),0),IF(B2>135,RAND(),0),IF(B2>136,RAND(),0),IF(B2>137,RAND(),0),IF(B2>138,RAND(),0),IF(B2>139,RAND(),0),IF(B2>140,RAND(),0),IF(B2>141,RAND(),0),IF(B2>142,RAND(),0),IF(B2>143,RAND(),0),IF(B2>144,RAND(),0),IF(B2>145,RAND(),0),IF(B2>146,RAND(),0),IF(B2>147,RAND(),0),IF(B2>148,RAND(),0),IF(B2>149,RAND(),0),IF(B2>150,RAND(),0),IF(B2>151,RAND(),0),IF(B2>152,RAND(),0),IF(B2>153,RAND(),0),IF(B2>154,RAND(),0),IF(B2>155,RAND(),0),IF(B2>156,RAND(),0),IF(B2>157,RAND(),0),IF(B2>158,RAND(),0),IF(B2>159,RAND(),0),IF(B2>160,RAND(),0),IF(B2>161,RAND(),0),IF(B2>162,RAND(),0),IF(B2>163,RAND(),0),IF(B2>164,RAND(),0),IF(B2>165,RAND(),0),IF(B2>166,RAND(),0),IF(B2>167,RAND(),0),IF(B2>168,RAND(),0),IF(B2>169,RAND(),0),IF(B2>170,RAND(),0),IF(B2>171,RAND(),0),IF(B2>172,RAND(),0),IF(B2>173,RAND(),0),IF(B2>174,RAND(),0),IF(B2>175,RAND(),0),IF(B2>176,RAND(),0),IF(B2>177,RAND(),0),IF(B2>178,RAND(),0),IF(B2>179,RAND(),0),IF(B2>180,RAND(),0),IF(B2>181,RAND(),0),IF(B2>182,RAND(),0),IF(B2>183,RAND(),0),IF(B2>184,RAND(),0),IF(B2>185,RAND(),0),IF(B2>186,RAND(),0),IF(B2>187,RAND(),0),IF(B2>188,RAND(),0),IF(B2>189,RAND(),0),IF(B2>190,RAND(),0),IF(B2>191,RAND(),0),IF(B2>192,RAND(),0),IF(B2>193,RAND(),0),IF(B2>194,RAND(),0),IF(B2>195,RAND(),0),IF(B2>196,RAND(),0),IF(B2>197,RAND(),0),IF(B2>198,RAND(),0),IF(B2>199,RAND(),0),IF(B2>200,RAND(),0),IF(B2>201,RAND(),0),IF(B2>202,RAND(),0),IF(B2>203,RAND(),0),IF(B2>204,RAND(),0),IF(B2>205,RAND(),0),IF(B2>206,RAND(),0),IF(B2>207,RAND(),0),IF(B2>208,RAND(),0),IF(B2>209,RAND(),0),IF(B2>210,RAND(),0),IF(B2>211,RAND(),0),IF(B2>212,RAND(),0),IF(B2>213,RAND(),0),IF(B2>214,RAND(),0),IF(B2>215,RAND(),0),IF(B2>216,RAND(),0),IF(B2>217,RAND(),0),IF(B2>218,RAND(),0),IF(B2>219,RAND(),0),IF(B2>220,RAND(),0),IF(B2>221,RAND(),0),IF(B2>222,RAND(),0),IF(B2>223,RAND(),0),IF(B2>224,RAND(),0),IF(B2>225,RAND(),0),IF(B2>226,RAND(),0),IF(B2>227,RAND(),0),IF(B2>228,RAND(),0),IF(B2>229,RAND(),0),IF(B2>230,RAND(),0),IF(B2>231,RAND(),0),IF(B2>232,RAND(),0),IF(B2>233,RAND(),0),IF(B2>234,RAND(),0),IF(B2>235,RAND(),0),IF(B2>236,RAND(),0),IF(B2>237,RAND(),0),IF(B2>238,RAND(),0),IF(B2>239,RAND(),0),IF(B2>240,RAND(),0),IF(B2>241,RAND(),0),IF(B2>242,RAND(),0),IF(B2>243,RAND(),0),IF(B2>244,RAND(),0),IF(B2>245,RAND(),0),IF(B2>246,RAND(),0),IF(B2>247,RAND(),0),IF(B2>248,RAND(),0),IF(B2>249,RAND(),0),IF(B2>250,RAND(),0),IF(B2>251,RAND(),0),IF(B2>252,RAND(),0),IF(B2>253,RAND(),0),IF(B2>254,RAND(),0))

Comments

[minamikaze3]

ありがとうございます。
理屈はそれで合っているのですが、例えば10倍、100倍などになったときにも対応できるようなやり方というのは無いのでしょうか？

[x_x]

1回書けばコピーできるので10倍くらいならいけそうですが、100倍はどうするのでしょうか？
わたしには思いつかないですが、Excel のプロフェッショナルがいるかもしれません。

[yuuskeccho]

もし、Office365を使っていればRANDARRAY関数で、 =Max(RANDARRAY(B2,,0,1,False) として使えると思います。
昔はこんな便利な関数無かったんですけど、Office365のサブスク向けに2019年4月にリリースされているみたいです。

[minamikaze3]

ありがとうございます。
ただ、Office2010なんです。。。