【エクセル】ランダムで重複しない整数を出したい。

質問

Windows 7、エクセル2007です。
ランダムで重複しない整数を任意の範囲から一部のみ入力するにはどうしたら良いでしょうか？

１．任意の範囲「１～５０」とか「１～１００」とか「１５～３００」とかから、２０個だけとか４０個だけとか。
２．ランダムなので「２，６，３，４，８，１・・・」とかのｎ、ｎ＋１、ｎ＋２ではないランダムな整数。
３．重複しないこと。

上記１～３を満たしたやり方ってありますか？
出てこない数があっても良いんです。

例えば、１～１０までの範囲でランダムに３個だけセルに入力したいとき。
Ａ１に７、Ａ２に４、Ａ３に９というふうに。
それが１～３００の範囲で５０個の数字をランダムに重複しないように入力する。
Ａ１に１５、Ａ２に１６３、Ａ３に９２、・・・Ａ５０に４３。
残りの２５０個の数字は入力されないという感じです。

乱数だと重複します。
ランク（順位）と組み合わせると、連続（ｎ、ｎ＋１、ｎ＋２）になってしまいます。
列はどこでも良いんですが、行は連続したいです。
Ａ１～Ａ５０とか、Ａ１～Ａ３００とか。

関数の組み合わせでできないでしょうか？
また、マクロは分からないので、詳しく解説して頂けると助かります！

情報が不足する点がありましたら指摘して下さい。
追加します。

回答No.10

tom04 · ベストアンサー

No.4・5・6です。
たびたびごめんなさい。

すでに当方の回答よりも良い回答は出ていますが・・・

前回の数式があまりにも長すぎたので少し簡単にしてみました。
作成する表には○から○までの整数だけを表示し、
そのかなからRAND関数の大きい順に「表示数」だけ表示するようにしています。

↓の画像の
G1セルに
=IF(H1="","",RAND())
H1セルに
=IF(C2="","",C2)
という数式を入れオートフィルでしっかり下へコピー！

A1セルに
=IF(COUNTBLANK($C$2:$E$2),"",IF($E$2<ROW(A1),"",VLOOKUP(LARGE(G:G,ROW(A1)),G:H,2,0)))
という数式を入れオートフィルで下へコピーすると
画像のような感じになります。

前回と考え方は同じですが、作業用の表に余計なものを表示しないようにしてみました。

何度も失礼しました。m（_ _）m

お礼

作業用の表がすっきりしました。
こちらはこちらで使いやすいですよ。
結果は同じでもやり方が複数ありますからね。
A列をコピーして値として貼り付ければ、もう解決です。
そこからVLOOKUPなんかを使えば表が完成。
いろいろありがとうございました。

回答No.11

tom04

NO.10です！
何度もごめんなさい。

前回の数式でH列に間違いがありました。
H1セルは前回のままでよいのですが、
H2セルに
=IF(OR(H1="",$D$2<$H$1+ROW(A1)),"",$H$1+ROW(A1))
という数式を入れ、H2セルをオートフィルで下へコピーしてください。

何度もごめんささいね。m(_ _)m

お礼

こちらの解答で締め切り（解決）とします。

tom04さんとokormazdさんでベストアンサーを迷ったんですが、タッチの差で速かったtom04さんにします。
本当はお二方をベストアンサーにしたかったんですが、すみません。
また質問するかも知れませんので、そのときもお力を貸して下さい。
今回は本当にありがとうございました。

回答No.9

okormazd

#7です。
ちょっと修正です。

RANKは1からだから、

=RANK($E5,$E$1:INDIRECT("$E$"&($B$1-$A$1+1)))+$A$1-1

としないと、10～150にはなりませんね。

訂正してください。

お礼

ご丁寧にありがとうございます。
数式自体をコピーすればすぐに使えるので助かります。

回答No.8

watabe007

こんにちは
>任意の範囲「１～５０」とか
A1～A50セルに　
=RAND()
B1セルに
=RANK(A1,$A$1:$A$50,)
B2セル以下、必要個数分-1、コピー

>「１５～３００」とかから、２０個
A1～A286セルに
=RAND()
B1セルに
=RANK(A1,$A$1:$A$286,)+14
B2～B19セルにコピー

お礼

確かに。根本的というか究極というか。
基本はその通りですよね。
範囲の中でランク付けして、必要数をコピーする。
複雑な関数なしにすぐにできますね。
時間がないときはこれ十分かも知れません。
ありがとうございました。

回答No.7

okormazd

どこかの列を補助列として、=RAND()を得たい最大の数値以上適当に入力します。～300まで欲しかったら、1行から300行以上まで入力しておきます。これがE列だとして、10～150の数値が欲しい場合、A1に10、A2に150をいれて、表示したいセルに、

=RANK($E1,$E$1:INDIRECT("$E$"&($B$1-$A$1)))+$A$1

といれて、下に必要な個数分フィルドラッグします。

なお、RAND()はそのままではどこかに入力があると再計算されますから、取った数値を動かしたくなければ、コピーしてそのまま数値として貼り付けるなどします。

なお、たいていこのような方法で、重複しないランダムな数値を取りますが、理論的には正しいわけではありません。EXCELの乱数が本当に乱数なのかは別にして、
EXCELは15桁の乱数を発生しますが、100個や1000個では同じ15桁の乱数が確率的に出ないだろうとしているだけです。したがって、もしかすると同じ数が出る可能性も無いわけでは・・・・ということです。

お礼

INDIRECTという関数があるんですね！
マスターすれば便利そうですが、上級者向けですか？
数式自体がシンプルなので他にも応用できそうです。
ありがとうございました。

回答No.6

tom04

No.4・5です！
A列の数式の説明を！というコトなので・・・

投稿後に思ったのですが、G列とH列を入れ替えればVLOOKUP関数が使えるような気がします。
ただ前回の数式で説明させていただきます。

単純に決まった数値から表示するのであればRANK関数等でもっと簡単な数式にできると思いますが、
今回の質問では「ある数値～ある数値」という範囲がありましたので、
あのような数式にしてしまいました。

=IF(COUNTBLANK($C$2:$E$2),"",IF($E$2<ROW(A1),"",INDEX(OFFSET($G$1,$C$2,,$D$2-$C$2+1),MATCH(SMALL(OFFSET($H$1,$C$2,,$D$2-$C$2+1),ROW(A1)),OFFSET($H$1,$C$2,,$D$2-$C$2+1),0))))
部分の始めの方
＞IF(COUNTBLANK($C$2:$E$2),"",IF($E$2<ROW(A1),"",
に関しては問題ないと思いますが、単にエラー表示させないための数式で
C2～E2に空白があれば何も表示しない！
オートフィルした行がE2セルの数値よりも大きい場合は空白に！
という意味です。

次に
INDEX(INDEX(OFFSET($G$1,$C$2,,$D$2-$C$2+1),MATCH(SMALL(OFFSET($H$1,$C$2,,$D$2-$C$2+1),ROW(A1)),OFFSET($H$1,$C$2,,$D$2-$C$2+1),0))))
MATCH(SMALL(OFFSET($H$1,$C$2,,$D$2-$C$2+1),ROW(A1)),OFFSET($H$1,$C$2,,$D$2-$C$2+1),0))))
は
INDEX(配列,行番号,)
ですので、
今回は行番号を出すためにMATCH関数を使用しています。
結局　INDEX(配列,MATCH(検索値,検索範囲,照合の型))となります。

（1）配列　→　ここをC2・D2の値によって範囲を指定する必要がある。
（2）行番号　→　これもC2・D2によって範囲を指定する必要がある。

というコトなのでOFFSET関数を使いました。
配列の　INDEX(OFFSET($G$1,$C$2,,$D$2-$C$2+1)　部分は
G1セルを基準として、C2セル（画像では4行）下から　D2－C2+1（画像では12-4+1＝7行分）が
INDEX関数の配列の範囲となります。

同様にMATCH関数の検索範囲もC2・D2の値によって変動しますので、
H2セルを基準として、OFFSET関数で4行下から7行分がMATCH関数の検索範囲としています。

すなわちINDEX関数で範囲指定した行の隣の列（H列）がMATCH関数の検索範囲となり、
その中から小さい順に一致するG列の数値をA列の1行目からE5セルの値の数だけ表示！
といった数式になります。

SMALL(OFFSET($H$1,$C$2,,$D$2-$C$2+1),ROW(A1))
の
ROW(A1)
がオートフィルでROW(A2)=2 ROW(A3)=3・・・
と変化しますので、1行目からSMALL関数の小さい順となります。

以上、ざっくりとした説明でしたが
この程度でよろしいでしょうか？m(_ _)m

お礼

すごく丁寧な解説ありがとうございます。
エクセルのガイド本はあるんですが、関数の辞典買わないとダメですね。
複数の関数を組み合わせたり、使い方を考えるときの参考になりました。
ちょっと消化できない部分もあるんで、調べながらやってみます。

回答No.5

tom04

No.4です！
たびたびごめんなさい。
投稿後に画像の列と説明の列がずれているコトに気づきました。

＞F列は2行目から単純に連番を！
↓
＞G列は2行目から単純に連番を！

＞G2セルには・・・
↓
＞H2セルには・・・

＞F2セルには表示させたい数を入力！
↓
＞E2セルには・・・

にそれぞれ訂正してください。

A1セルの数式はそのままで大丈夫だと思います。
ごめんなさいね。m（_ _）m

お礼

ご丁寧にありがとうございます。
大丈夫ですよ。その辺は分かりましたから。
ただ、A列の数式が複雑で分かりません。
もし可能ならば、そこのところを解説して頂けると助かります（勉強の意味で）。
ちょっと最初の質問の趣旨とはずれますけれども・・・

回答No.4

tom04

こんばんは！
一例です。

↓の画像のF・G列のような表をあらかじめ作成しておきます。
F列は2行目から単純に連番を！
（これ以上はない！というくらいまで連番を表示しておきます）
G2セルには
=RAND()
という数式を入れ、フィルハンドルでダブルクリック！

そして、C2セルには表示させたい最初の数値を！
D2セルには表示させたい最後の数値を！
F2セルには表示させたい数を入力！

A1セルに
=IF(COUNTBLANK($C$2:$E$2),"",IF($E$2<ROW(A1),"",INDEX(OFFSET($G$1,$C$2,,$D$2-$C$2+1),MATCH(SMALL(OFFSET($H$1,$C$2,,$D$2-$C$2+1),ROW(A1)),OFFSET($H$1,$C$2,,$D$2-$C$2+1),0))))

という数式を入れ、オートフィルでずぃ～～～！っとしっかり下までコピー！

これで画像のような感じになります。
F9キーを押すたびにA列にC2からD2までの整数がE2セルの数だけ表示されるはずです。

参考になりますかね？m（_ _）m

お礼

入力したかったのは、Ａ１からＡ５までのような数値です。
「から」、「まで」、「表示数」を設定すれば、簡単に求められますね。
Ａ列の数式がとても複雑なのでビックリしています。
とりあえずは、思っていたような形になりました。
ありがとうございました。

回答No.3

notnot

ちょっと意図をつかみきれないところもあるのですが、
1～300の数字をでたらめに並べ替えたいと言うことなら、
A1:A300に、1～300を順に入れて、B1:B300に、=rand() を入れて、A1:B300をB列をキーにしてソートすれば、A列がでたらめに並び変わりますので、その先頭の数個を取れば良い。

お礼

確かにそうです。
重複しないし、ランダムですね。
基本すぎて気づきませんでした。
複雑な数式がないのでいざとなったら使ってみます。
ありがとうございました。

回答No.2

KURUMITO

例えば１５から２００の数値で４０個の整数を取り出したいとしたら次のようにしてはいかがでしょう。
A１セルには１５を入力し、B１セルには２００を入力します。また、C１セルには４０と入力します。
これらの条件をもとに整数を取り出すための作業列を作ります。
２行目は空白としてA３セルには次の式を入力して下方にドラッグコピーします。A１０００セルまでもドラッグします。

=IF((A$1+ROW(A1)-1)>B$1,"",A$1+ROW(A1)-1)

これで１５から２００までの整数が並びます。
B３セルには次の式を入力して下方B１０００セルまでドラッグコピーします。

=IF(A3="","",RAND())

乱数が並びます。
C３セルには次の式を入力して下方にC１０００セルまでドラッグコピーします。

=IF(A3="","",IF(RANK(B3,B$3:INDEX(B:B,MATCH(B$1,A:A,0)),1)<=C$1,RANK(B3,B$3:INDEX(B:B,MATCH(B$1,A:A,0)),1),""))

D１セルにはランダムな整数表示とでも入力し、D３セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>C$1,"",INDEX(A$3:A$1000,MATCH(ROW(A1),C$3:C$1000,0)))

これで、A１セルからC１セルに入力された条件で抽出された整数がD列に表示されます。
D列の数値はF９キーを押すことで変わりますし、セルにデータなどを入力する作業をするなどしますと変化してしまいます。
変化しないようにするためにはD列を選択してコピーしてからF１セルなどを選択し、「形式を選択して貼り付け」で「値」にチェックをして貼り付けます。F列には変化することない整数が並ぶことになります。

お礼

最初と最後と個数を入力して、IFなんたらかんたらで入力するって感じでしょうか。
こういう数式もあるんですね。
参考にさせて頂きます。
F9とコピーの解説もありがとうございます。

回答No.1

akina_line

こんにちは。

　>２．ランダムなので「２，６，３，４，８，１・・・」とかのｎ、ｎ＋１、ｎ＋２ではないランダムな整数。

　の意味が理解できませんが、乱数を求めた後で条件に照らしてNGなら再度、乱数を求めるようなロジックにしたらどうでしょう。

では。

お礼

ちょっと言葉で説明するのが下手で上手く伝わらなかったみたいですね。
他の方が回答してくれましたが、１～３００の数字の中から４０個を重複しないようにランダムに抜き出すにはどうしたらいいかなと？もちろん抜き出されない数字が出てくるのは当たり前ですが。
概ねNO.４の回答で解決かと思いますが、他の方法などありましたらよろしくお願いします。