エクセルで３ケタの乱数

質問

今度くじ引き大会をするのに３ケタの数字を250名ほどに配りたいのですが、それぞれの数字が重複なくかつ各桁数の数字が重複しないで乱数をエクセルで生成する方法を教えてください。（例えば、101,221,199などは除外したいです）

RAND,RANDBETWEEN関数を使ってみましたが、重複してしまうし、221などのような各桁で数字が重複してしまうものができてしまうのでうまくいきません。

よろしくお願いします。

回答No.6

keithin · ベストアンサー

＞簡単に説明をお願い

数式自体はごく単純なつくりです。回答の手順を間違いなく行っていれば、数式はあなたがふつーに使っている関数だけで構成されていると思いますが？

ＡＢＣ列に記入したのはあなたがヤリタカッタその通り、
１）（Ｄ列に表した）それぞれの数字が重複なく（最初のCOUNTIF）
２）（ＡＢＣ３列の）各桁数の数字が重複しない（２つ目のCOUNTIF)
数字が表れるまで、エクセルに繰り返し０～９の乱数を発生（INT(RAND()*10)）させ、条件をクリアして計算が止まるまで繰り返し計算（＝探索）させています。

Ｄ列は計算を簡単にするため、ＡＢＣ列の３つの数字をくっつけて一つにしているだけです。
Ｅ列は結果の確認用で、探索には使っていません。

２５０行ぐらいまでなら問題なく探索は終了する（確認用のＥ列にすべてＯＫが並ぶ）と思いますが、もっと沢山の行数を計算させようとすると、一回の探索ではＮＧが残る可能性もあります。その時はＦ９キーを押すことでもう１サイクル探索を続行し、ＮＧが無くなるまで繰り返します。
（他の方からも言われているように、全部で７２０通りの数字があります。最後の数個まで漏れなく探索しようとすると、結構時間がかかります。もしそういう網羅的な方法をしたいなら、０００から９９９までの数字をまず並べて、条件に満たない（ＮＧの）行を取り除いていった方が遥かに簡単です）

一般的にRAND関数をふつーに使うやり方では、何かの拍子で再計算が走る都度、これまでの計算結果がすべてお釈迦になってまた最初から全部やり直しになりますが、回答した手順は条件を満たした行ではそれ以上再計算させず、ＮＧの行だけが探索するように仕込んであります。
もしも新しい乱数の組み合わせを再度やり直したくなったら、２行目以下をいったん消去し、Ａ１：Ｅ１を改めてコピーして目的の行数分貼り付け直して下さい。

回答No.8

chieffish

1000個も有るはず無かった。
001とかは3桁とは言えないかも知れないので102から987までの648個
作ってみた。
http://pupld.net/14/121231/d2018id8re/index.cgi

回答No.7

chieffish

250個でも1000個でも誰かに作っていただいて、どこかにアップしてもらった方が・・・・

回答No.5

kagakusuki

回答:No.4の方法を一般化して、3桁以上の桁数の乱数を作る際にも応用が利く様にした方法です。

　まず、D1セルに次の関数を入力して下さい。

=IF(ROWS($1:1)>FACT(10)/FACT(10-COLUMNS($F:$H)),"",RAND())

　次に、F1セルに次の関数を入力して下さい。

=IF($D1="","",MID(IF(COLUMNS($F:F)=1,"0123456789",IF(COUNTIF(E1:$F1,"0"),"",0)&IF(COUNTIF(E1:$F1,"1"),"",1)&IF(COUNTIF(E1:$F1,"2"),"",2)&IF(COUNTIF(E1:$F1,"3"),"",3)&IF(COUNTIF(E1:$F1,"4"),"",4)&IF(COUNTIF(E1:$F1,"5"),"",5)&IF(COUNTIF(E1:$F1,"6"),"",6)&IF(COUNTIF(E1:$F1,"7"),"",7)&IF(COUNTIF(E1:$F1,"8"),"",8)&IF(COUNTIF(E1:$F1,"9"),"",9)),MOD(ROWS($1:1)-1,10-COLUMNS($F:F)+1)+1,1))

　次に、F1セルをコピーして、G1～H1の範囲に貼り付けて下さい。

　次に、E1セルに次の関数を入力して下さい。

=IF($D1="","",SUMPRODUCT($F1:$H1*10^(COLUMN($H1)-COLUMN($F1:$H1))))

　次に、B1セルに次の関数を入力して下さい。

=IF(ROWS($1:1)>COUNT($D:$D),"",VLOOKUP(SMALL($D:$D,ROWS($1:1)),$D:$E,2,FALSE))

　次に、B1～H1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　これで、B列に「それぞれの数字が重複なくかつ各桁数の数字が重複しない乱数」が表示されます。

回答No.4

kagakusuki

回答:No.3とは別の方法です。

　まず、D1セルに次の関数を入力して下さい。

=IF(ROWS($1:1)>10*9*8,"",RAND())

　次に、F1セルに次の関数を入力して下さい。

=IF($D1="","",MID("0123456789",MOD(INT((ROWS($1:1)-1)/9/8),10)+1,1))

　次に、G1セルに次の関数を入力して下さい。

=IF($D1="","",MID(SUBSTITUTE("0123456789",$F1,),MOD(INT((ROWS($1:1)-1)/8),9)+1,1))

　次に、H1セルに次の関数を入力して下さい。

=IF($D1="","",MID(SUBSTITUTE(SUBSTITUTE("0123456789",$F1,),$G1,),MOD(ROWS($1:1)-1,8)+1,1))

　次に、E1セルに次の関数を入力して下さい。

=IF($D1="","",($F1&$G1&$H1)+0)

　次に、B1セルに次の関数を入力して下さい。

=IF(ROWS($1:1)>COUNT($D:$D),"",VLOOKUP(SMALL($D:$D,ROWS($1:1)),$D:$E,2,FALSE))

　次に、B1～H1の範囲をコピーして、B2～H720の範囲に貼り付けて下さい。（各桁の数値が重複しない組み合わせは10×9×8＝720通りです）

　これで、B列に「それぞれの数字が重複なくかつ各桁数の数字が重複しない乱数」が表示されます。

回答No.3

kagakusuki

まず、D1セルに次の関数を入力して下さい。

=IF(AND(LEN(ROW()+101)=3,SUMPRODUCT((LEN(SUBSTITUTE(ROW()+101,MID(ROW()+101,{1,2,3},1),))<2)*1)=0),RAND(),"")

　次に、D1セルをコピーして、D2～D886の範囲に貼り付けて下さい。
　次に、B1セルに次の関数を入力して下さい。

=IF(ROWS($1:1)>COUNT($D:$D),"",MATCH(SMALL($D:$D,ROWS($1:1)),$D:$D,0)+101)

　次に、B1セルをコピーして、B2～B648の範囲に貼り付けて下さい。
　すると、B列に「それぞれの数字が重複なくかつ各桁数の数字が重複しない乱数」が表示されます。

　尚、下の添付画像におけるA列の数字は、単なる連番です。
　又、E列の数字は「各桁数の数字が重複しない3桁の数字」をしらみつぶしに表示したもので、F列の数字は、E列の数字の表示する順番を表したものです。
（もし、質問者様が御利用になられているサイトでは、画像が表示されていない場合には、次のURLのページを御覧下さい　http://okwave.jp/qa/q7866017.html）
　これら、A列、E列、F列の数字は、あくまで解り易くするために表示させたものであり、B列とD列さえあれば、A列、E列、F列が無くとも乱数は表示されます。

　あとそれから、RAND関数やRANDBETWEEN関数を使って乱数を発生させた場合、Excelで計算が行われるたび（ファイルを開く際や保存する際にも再計算は行われます）に、乱数のリストが変更されてしまいますので、1度表示された乱数のリスト全体をコピーして、どこか適当な列に値のみを貼り付ける事で、保存されておかれる事を御勧め致します。

回答No.2

keithin

エクセルに探索させてみます。

手順：
ご利用のエクセルのバージョンが不明ですが、エクセルのオプションで「反復計算」のチェックを入れる
Ａ１に
=IF(OR(COUNTIF($D$1:$D1,$D1)>1,COUNTIF($A1:$C1,A1)>1),INT(RAND()*10),A1)
と記入、Ｂ１、Ｃ１にコピー貼り付ける
Ｄ１に
=A1&B1&C1
と記入
Ｅ１に確認用に
=IF(AND(COUNTIF(D:D,D1)=1,COUNTIF(A1:C1,MEDIAN(A1:C1))=1),"OK","NG")
と記入

Ａ１：Ｅ１を下向けに２５０までコピー貼り付けて完成。

お礼

ありがとうございます。出来ました！！しかし、せっかくなので簡単に説明をお願いしてもよろしいでしょうか？関数の部分が少し複雑で理解できません。

回答No.1

Cupper-2

んじゃ、テーブルでも作りますか。
０００から９９９までのね。
でもって各桁の数字が重複している値を読み取る値から除外する。
除外したテーブルの値に８１０まで数字（シリアルナンバー）を振る。
RAND関数で１から８１０までの乱数を発生させる。
でもって、生成された乱数をテーブルと付き合わせて値を決める。

この方法は除外したい値を任意に決めることができるので
各桁の数値が重複していないだけでなく連番も除外したいとか、そんなわがままにも対応できます。

または、各桁毎に乱数を発生させて後で組み合わせましょう。
百の桁に対して０から９までの乱数を発生させる。
十の桁に対して０から８までの乱数を発生させる。
でもって百の桁に設定した数値以上の値が出たら１を足す。
一の桁に対して０から７までの乱数を発生させる。
でもって百の桁に設定した数値以上の値が出たら１を足す。
さらに十の桁に設定した数値以上の値が出たらもう一度１を足す。
各桁の数字を並べて最終の値とする。

いろいろなやり方がありますが、自分でやりやすいと思った方法で試してみましょう。

なお、各桁の数値が重複しない組み合わせは
１０×９×８
ですので８１０個ですね。