こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

EXCELで乱数を使用し重複しない整数を抽出したい

EXCEL2010を使用しています。

・任意の数値より一部の数値を取り出す
・「任意の数値」は複数範囲でも可能のもの
・ランダムに取り出される整数
・抽出する個数もランダムで指定したい
・VLOOKUP関数、HLOOKUP関数と組み合わせたい
・横に並べて抽出する場合と縦に並べて抽出する場合の関数をそれぞれ知りたい

上記の条件をすべて満たす方法を模索しましたが、このような使い方をする場合の応用がわからず困っています。
理解の難しい条件だとは思いますが、策を講ずることが出来る方がいらっしゃいましたら、出来る限り早急に回答をお願いします。

http://okwave.jp/qa/q7670173.html の質問に幾つか条件を足したものだと考えていただければと思います。

投稿日時 - 2014-02-28 22:59:48

QNo.8494590

すぐに回答ほしいです

質問者が選んだベストアンサー

>・VLOOKUP関数、HLOOKUP関数と組み合わせたい

という条件の事を、質問者様は、どの様な意味で仰っておられるのか判りませんが、兎に角、前回のhttp://okwave.jp/qa/q7670173.html の質問と同様の事を、複数の範囲で行いたいという事であると考えれば宜しいのでしょうか?
 それでしたら、次の様な方法は如何でしょうか?

 今仮に、抽出する個数をSheet1のE4セルに入力し、
複数に分かれた「任意の数値」の範囲の内、
1つ目の範囲の最初の数をD6セルに入力し、最後の数をE6セルに入力し、
2つ目の範囲の最初の数をD7セルに入力し、最後の数をE7セルに入力し、
3つ目の範囲の最初の数をD8セルに入力し、最後の数をE8セルに入力すると、
Sheet2のA列~E列を作業列として使用して、
Sheet1のA2以下の1列か、或いはSheet1のB2セルから始まる右方向の1行に、抽出結果が表示される様にするものとします。

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

=IF(AND(ISNUMBER(1/Sheet1!$E$3),ISNUMBER(VLOOKUP(A$1,Sheet1!$C$6:$E$8,2,FALSE)),ISNUMBER(VLOOKUP(A$1,Sheet1!$C$6:$E$8,3,FALSE))),IF(AND(Sheet1!$E$3=INT(ABS(Sheet1!$E$3)),VLOOKUP(A$1,Sheet1!$C$6:$E$8,3,FALSE)>=VLOOKUP(A$1,Sheet1!$C$6:$E$8,2,FALSE),INT(VLOOKUP(A$1,Sheet1!$C$6:$E$8,2,FALSE))=VLOOKUP(A$1,Sheet1!$C$6:$E$8,2,FALSE),INT(VLOOKUP(A$1,Sheet1!$C$6:$E$8,3,FALSE))=VLOOKUP(A$1,Sheet1!$C$6:$E$8,3,FALSE),ROWS($2:2)<=VLOOKUP(A$1,Sheet1!$C$6:$E$8,3,FALSE)-VLOOKUP(A$1,Sheet1!$C$6:$E$8,2,FALSE)+1),VLOOKUP(A$1,Sheet1!$C$6:$E$8,2,FALSE)+ROWS($2:2)-1,""),"")

 次に、Sheet2のA2セルをコピーして、Sheet2のB2~C2の範囲に貼り付けて下さい。
 次に、Sheet2のA2~C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
 次に、Sheet2のE1セルに次の関数を入力して下さい。

=IF(ROW()>COUNT($A:$C),"",RAND())

 次に、Sheet2のE1セルをコピーして、Sheet2のE2以下に貼り付けて下さい。

 次に、Sheet1のA2セルに次の関数

=IF(ROW()-ROW($A$1)>MIN(SUM(Sheet1!$E$3),COUNT(Sheet2!$E:$E)),"",SMALL(Sheet2!$A:$C,RANK(INDEX(Sheet2!$E:$E,ROW()-ROW($A$1)),Sheet2!$E:$E)))

を入力してから、Sheet1のA2セルをコピーして、Sheet1のA3以下に貼り付けるか、
或いは、Sheet1のB1セルに次の関数

=IF(COLUMN()-COLUMN($A$1)>MIN(SUM(Sheet1!$E$3),COUNT(Sheet2!$E:$E)),"",SMALL(Sheet2!$A:$C,RANK(INDEX(Sheet2!$E:$E,COLUMN()-COLUMN($A$1)),Sheet2!$E:$E)))

を入力してから、Sheet1のB1セルをコピーして、Sheet1のC1から右方向に向かって貼り付けて下さい。
 これで、複数に分かれた数値の範囲の中から、指定した数だけランダム且つ重複無しに数値を抽出する事が出来ます。


 因みに、

>・抽出する個数もランダムで指定したい

という場合には、Sheet1のE4セルに次の様な関数を入力すると良いと思います。

=INT(RAND()*(SUMIFS($E$6:$E$8,$D$6:$D$8,"<>*",$E$6:$E$8,"<>*",$D$6:$D$8,"<>",$E$6:$E$8,"<>")-SUMIFS($D$6:$D$8,$D$6:$D$8,"<>*",$E$6:$E$8,"<>*",$D$6:$D$8,"<>",$E$6:$E$8,"<>")+ROWS($D$6:$D$8)))+1

投稿日時 - 2014-03-01 15:42:23

補足

無事成功しました。ありがとうございます。

補足欄ですが改めてお礼に使わせて頂きます。

少し短絡された式などございましたら引き続き回答をお願いしたいと思います。

投稿日時 - 2014-03-01 17:05:15

お礼

ご回答を戴きありがとうございます。
この式で挑戦したいと思います。

投稿日時 - 2014-03-01 16:38:42

このQ&Aは役に立ちましたか?

22人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

>少し短絡された式などございましたら引き続き回答をお願いしたいと思います。

 実のところ、Sheet2のA2セルの関数は、その大半が

●「抽出する個数」欄に負の数や小数値、文字列などではなく、間違いなく自然数が入力されているのか

●「以上」欄と「以下」欄の双方に、負の数や小数値、文字列などではなく、間違いなく自然数が入力されているのか

●「以下」欄に入力されている数値は、間違いなく「以上」欄に入力されている数値以上の値なのか

等という事を判定するためのものでしか御座いません。
 ですから、「抽出する個数」欄、「項目1」、「項目2」、「項目3」の各「以上」欄と「以下」欄、の合わせて7つの欄に、おかしな値を入力したり、入力漏れをしていたり、といった事が無い場合には、Sheet2のA2セルの関数を次の様に簡略化する事も出来ます。

=IF(ROWS($2:2)>VLOOKUP(A$1,Sheet1!$C$6:$E$8,3,FALSE)-VLOOKUP(A$1,Sheet1!$C$6:$E$8,2,FALSE)+1,"",VLOOKUP(A$1,Sheet1!$C$6:$E$8,2,FALSE)+ROWS($2:2)-1)

投稿日時 - 2014-03-01 18:17:43

お礼

そうだったんですね。
フールプルーフ機能は多分必要ないのでこちらで考えてみます。
ありがとうございます。

投稿日時 - 2014-03-01 20:56:47

あなたにオススメの質問