エクセルで重複なしの範囲抽出

質問

現在，EXCELで単語テストを簡単に作れるように試行錯誤しているのですが，EXCELで1から200などの指定範囲から，20個，数字の重複なしで選びたいのですが，どのようにすればいいでしょうか？

また，1つのセルに指定範囲(20～60まで)を入れると，自動で選んでくれるようにしたいのですが，出来ますか？

教えて下さい。よろしくお願いします。

回答No.6

mshr1962

シート１
＿A＿＿B＿＿C＿＿D
1_KEY＿No_＿単語_英語
2＿＿＿_1＿＿愛＿_LOVE
3＿＿＿_2＿＿青＿_BLUE
シート２
＿A＿＿B＿＿C＿＿D
1_指定範囲を選択してください。
2_|1|～|200|

シート２のA2に開始行、C2に終了行をセットしてあるとして
シート１!A2=IF(AND(B2>=シート２!$A$2,B2<=シート２!$C$2),RAND()*20,50)
でA2をA201までコピー（これで指定範囲は0～20、指定範囲以外は50）
シート２!B4=INDIRECT("Sheet1!B"&MATCH(SMALL(Sheet1!A:A,ROW(A1)),Sheet1!A:A,0))
シート２!C4=VLOOKUP(B4,シート１!B:D,2,FALSE)
でB4:C4をB23:C23までコピー
注意１：シート2のC2はA2より必ず20以上大きい数値にしてください。
注意２：「ツール」「オプション」「計算方法」は手動にしてください。
自動で再計算されるとセルに入力される度に問題が変わります。
問題を変える場合はF9キーを押せば変わります。

回答No.5

Wendy02

こんにちは。

#2の回答者です。

うーん、もうある程度出来上がってはいるのですね。
後だし条件は、こちらとしては、かなり凹みますね。最初に、そういう条件とか出していただいたほうが、お互いに無駄が少なくていいのですが。

その数式の
　=INT(RAND()*10^2)*10^2+I2　

って、ほぼ1回から2回で、必ずというほど、同数が出ますから、もう乱数とはいえなくなってくるし、乱数を取るなら、単に、RAND()関数だけでよいと思いますね。

範囲というのは、30～80とかの範囲を出力ということですか・・・・。

それは、

選択単語番号入力
K2: 30
K3: 80　　（ただし、20問出すのに、範囲が、20行以下なら、エラーが出ます）

L2
=INDEX($A$2:$C$247,MATCH(SMALL(OFFSET($J$1,$K$2,,$K$3-$K$2),ROW(A1)),$J$2:$J$247,0),1)

=INDEX($A$2:$C$247,MATCH(SMALL(OFFSET($J$1,$K$2,,$K$3-$K$2),ROW(A1)),$J$2:$J$247,0),2)

のようにすればよいと思います。
しかし、元のデータがランダムに並んでいなければ、単に、抽出した範囲に対してランダムに並ぶだけです。まだ、出されていない情報があるようです。

乱数は、一度出たら、固定をしたほうが、後の問題で重複が出ないと思います。範囲指定は、20問出すのでしたら、20問ずつ区切ればよいのですから。それが、それほど、面倒なことだという認識はありませんね。なぜ、#2でいけないのか良く分かりません。

後は、よろしく、お考えになってみてください。

回答No.4

noname#176215

> その方法では，範囲指定ができますか?
範囲を指定するだけです。

> 30から80までで20個みたいなことをやりたいのですが・・・。
ＲＡＮＤの入った列を並び替えした後 １行目から２０行目までをコピー。
できますよね？

> そこだけを自動的に数字の範囲指定するだけでしたいのですが，
> 無理ですかね？
何か問題があったのですか？
まず手動でできることを確認してから それを自動化しましょう。
マクロの記録の仕方は分かりますか？

「無理ですかね」ってのが 何の為の確認か分かりません。

補足

回答ありがとうございます。
並び替えのところが，よく理解できないです。
マクロ記録はやったことがないので，分からないです。
質問内容が分かりにくくて申し訳ありません。

回答No.3

Wendy02

訂正
D1: 単語　-> 乱数
です。

回答No.2

Wendy02

こんにちは。

単語テストって、個人で使用するものはなく、テストとして出すものですね。

　A　　　B　　　　　　C　　　　　D
番号　　単語　　　　　順番　　　単語
　1　　absurdity　　　98　　　0.019871683
　2　　actuality　　　27　　　0.703251135
　3　　addictive　　　61　　　0.333945299
　4　　adherence　　　8　　　0.872105382
　5　　adjacent　　　45　　　0.512775279
　6　　admittance　　33　　　0.658703376
　・　　　・　　　　　・　　　　　・
　・　　　・　　　　　・　　　　　・

D2から、=RAND()
で乱数を出して、範囲を選択して、コピー－右クリック－形式を選択して貼り付け－値
で、数式を数値にしてしまいます。

C2 は、
=RANK(D2,$D$2:$D$201)

で、順位を出し、

この順番で並び替えたら、重複のない、乱数の並び替えが出ますから、後は、上から取り出せばよいです。

E1:出力数
　20

F1:～オートフィルで下にコピーします。
=IF(ROW(A1)>$E$1,"",INDEX($A$2:$B$201,ROW(A1),2))

当面は、このような感じでどうでしょうね。

2回目からは、

=INDEX($A$21:$B$201,ROW(A1),2)

$A$2 -> $A$21 と

すればよいでしょうね。そうすれば、前回のテストとの重複はなくなります。

なお、レイアウトは、適当に考えてみてください。

補足

回答ありがとうございます。単語テストは，塾でアルバイトをしており，そのときの単語テストを毎回作るのが面倒なので，EXCELを使ってできないかと考えています。

セルが
A列　単語番号
B列　単語
C列　日本語
D列～G列　選択肢1～選択肢4
H列　=RAND()
I列 =RANK(H2,$H$2:$H$247)
J列　=INT(RAND()*10^2)*10^2+I2　
K列　選択単語番号入力
L列からINDEX関数で単語，日本語，選択肢呼び出し
となっています。

現在は，K列の選択番号入力のところで手入力して，単語を出しているのですが，I列～K列をどのように変えればうまくいくでしょうか。
レイアウト等は，EXCELのデータをWORDの差込み印刷で利用するようにしてあります。

希望としては，数値の範囲指定を1度するだけで，20問取り出し出来れば，嬉しいです。授業中に覚えてきた単語範囲を聞いて，その場で印刷するという使い方を想定しているので，なるべく操作が少なくて済むようにしたいのですが，どうすればいいでしょうか。教えて下さい。よろしくお願いします。

回答No.1

noname#176215

横のセルに「=RAND()」をデータの数だけ入力
その列を基準にして並び替え
上から順に必要なだけデータをコピー

でどうですか？
自動でというなら SMALLか LARGE関数を使えばできますが 上の操作を
マクロでやった方が速いし失敗がないと思います。

補足

その方法では，範囲指定ができますか?
30から80までで20個みたいなことをやりたいのですが・・・。
今は手入力で，数字を選んでるので，そこだけを自動的に数字の範囲指定するだけでしたいのですが，無理ですかね？