抽出の関数計算が成立せず、困ってます！

質問

EXCELの関数を使用して、140人の生徒を7グループに分けようとしています。
生徒一人一人には、元々Ai～Niまでの14グループが振られてます（i=1～10)。
振り分けたいグループは7グループなので、
第1グループをA+Bグループ
第2グループをC+Dグループ
第3グループをE+Fグループ
としていき、
第7グループをM+Nグループ
と振り分けたいと思ってます。
但し条件があり、「A+B」グループに入る生徒は、Ai,Biのが振られていない生徒がランダムに20人入るようにしたいと思っており、「C+D」やその他のグループも同様で、グループ名になるアルファベットが振られていない生徒の中から任意で20人を1グループにするというものです。

使用した関数はVLOOKUP関数とLARGE関数で、その方法は、

【データ基】
　　　　　　　　A列　　　　　　　　　B列　　　　　　　　C列　　　　　　D列
10行目　　(便宜上の)順位　　(生徒の)番号　　氏　　名　　　　　乱数
11行目　　　　　1　　　　　　　　A1　　　　●●△△　　　　=RAND()
12行目　　　　　2　　　　　　　　A2　　　　■■○○　　　　=RAND()
　　　　　　　　　　・　　　　　　　 ・　　　　　　　 ・　　　　　　　　・
　　　　　　　　　　・　　　　　　　 ・　　　　　　　 ・　　　　　　　　・
　　　　　　　　　　・　　　　　　　 ・　　　　　　　 ・　　　　　　　　・
21行目　　　　 11　　　　　　　　B1　　　　▲▲□□　　　　=RAND()
　　　　　　　　　　・　　　　　　　 ・　　　　　　　 ・　　　　　　　　・
　　　　　　　　　　・　　　　　　　 ・　　　　　　　 ・　　　　　　　　・
　　　　　　　　　　・　　　　　　　 ・　　　　　　　 ・　　　　　　　　・
31行目　　　　 21　　　　　　　　C1　　　　★★▽▽　　　　=RAND()
　　　　　　　　　　・　　　　　　　 ・　　　　　　　 ・　　　　　　　　・
　　　　　　　　　　・　　　　　　　 ・　　　　　　　 ・　　　　　　　　・
　　　　　　　　　　・　　　　　　　 ・　　　　　　　 ・　　　　　　　　・
150行目　 　　140　　　　　　　　N10　　　 ※※◎◎　　　　=RAND()

としており、
計算式は次のようにしましたが、上手くいきませんでした。

【グループ分け】

★A+Bグループ
　　1人目抽出“=VLOOKUP(LARGE(D31:D150,A11),B31:D150,2,0)”
　　2人目抽出“=VLOOKUP(LARGE(D31:D150,A12),B31:D150,2,0)”

としましたが、結果は、“#N/A”となってしまいます。
また、

★C+Dグループ
　　1人目抽出“=VLOOKUP(AND(LARGE(D11:D30),LARGE(D61:D150)),A11,AND(B11:D30,B61:D150),2,0)”

としましたが、これについては計算そのものの処理ができませんでした。

どのように計算すれば上手く振り分けられるのでしょうか。
「データ基」ではランダムにしたいとの思いから乱数を使用していますが、無作為に選ぶことができるのであれば、乱数でなくても構いませんし、また、最初に記した条件さえ満たせば、計算ごとに結果が変わっても差し支えありませんし、どのような関数を用いても差し支えありませんが、プログラミングはできないので、関数のみで処理したいと考えてます。

どうかお知恵を貸してください。
よろしくお願いいたします。

回答No.3

KURUMITO · ベストアンサー

回答Ｎｏ1です。
提案の式や方法に誤りはありません。グループ分けの方法もおっしゃる通りにしています。
シートの余分な場所に式やデータが入っていませんか？
計算に使われている列には絶対に余分な式やデータが入っていてはいけません。
まったく新しいシートでもう一度試験してみてください。こちらでの試験では全く正常に表示されます。
すべてのデータは1行目は空で2行目から下方に入力することになりますね。
Ｂ列では乱数が表示されますが、Ｂ22のセルから下方に表示されるようになっており、そればＡおよびＢグループのメンバーが除かれているからです。残りのメンバーから1回目の抽出が行われＣ列にその20人が表示されるようになっています。
Ｄ列ではＤ22セルからＤ41セルまで乱数のの表示がありません。それはＣおよびＤグループのメンバーを除くためです。その状態で抽選されたメンバーがＥ列に表示されます。以下同様です。

お礼

有難うございましたっ！！！
できましたっ！
相変わらず関数の内容の理解には至っていませんが、数式を右列にオートフィルドラッグして数式内で不足しているセルを加算し、参照除外範囲を変更していったら、見事に結果を出せましたっ！
昨日「9個しか結果が表示されない・・・」と申しましたのは、G列の数式内の「<=19」としなければならないところを「<=9」としていたため、結果が出なかったと言うことでした。

本当に本当に助かりました！
ご尽力いただき、有難うございました。
ところで、これはどうやってベストアンサーを付けるんでしょうか？
一度やってみます。

回答No.4

don9don9

No.1さんの方法で、うまくいくはずですが…

私の方で少しアレンジしてみます。
A2～A141にA1～N10が入っているとします。

1行目のB列以降に1列飛びに「A+B」グループ→「AB」という具合に
B1に「AB」、D1に「CD」、F1に「EF」、H1に「GH」
J1に「IJ」、L1に「KL」、N1に「MN」と入力します。

B2に
=IF(ISERROR(FIND(LEFT($A2,1),B$1)),RAND(),"")
C2に
=IF(B2="","",IF(AND(RANK(B2,B:B)<=20,COUNTIF(C$1:C1,"?*")<=19),$A2,""))
と入力します。
このB2:C2をB141:C141まで複写します。

D2に
=IF(AND(ISERROR(FIND(LEFT($A2,1),D$1)),COUNTIF($B2:C2,$A2)=0),RAND(),"")
と入力します。
E2は、C2のセルを複写します。
このD2:E2を表の一番右下、N141:O141まで複写します。

すると、C,E,G,I,K,M,Oの各列にそれぞれ20件ずつ
A列と同じ値が入ると思います。
これで
C列にA列と同じ値が入った生徒→「A+B」グループ
E列にA列と同じ値が入った生徒→「C+D」グループ
G列にA列と同じ値が入った生徒→「E+F」グループ
…
という具合に振り分けられると思いますが、如何でしょうか。

お礼

ご丁寧にご回答くださり、有難うございます！
この方法でもやってみましたが、確かにできました！
有難うございます！！
回答者No.1様の方法もこの方法もそうですが、「M+N」グループ側に行くほど乱数の発生回数が減少してきていますね。
それで、時折「M+N」グループのみが20名に満たない結果が出ることがありますが、どうしてなんでしょうか。
使用している関数の理解ができず、いじる事ができないため、事の解決には至っていませんが、「M+N」グループの抽出は余りの人員と捉えればいいため、さほど重要な数列ではないのかもしれませんね。

この問題の回答にご尽力いただき、本当に有難うございました。
回答者No.1の方へも書きましたが、まだベストアンサーのつけ方が分かっていないため、つけ方を調べて印をつけさせていただきたいと思います。

本当に助かりました。
有難うございました。

回答No.2

Nouble

１４０人を７グループに分けると１グループ２０人になると思いますが
その内２０人をランダムな人選にすると言うことは
全員をランダムな人選にすると言うことですよね？
過去のグループ分けを一切踏襲しない
と考えて良いのでしょうか？

それとも記載ミスでしょうか？

お礼

ご指摘、有難うございます。
拙い文章で申し訳ございません。

行いたいことを整理いたしますと、

●行いたいこと●
・140人の生徒を20人ずつランダムに7グループに振分ける。
・グループ名は、A+Bグループ、C+Dグループ、・・M+Nグループの計7グループ。

●事前準備●
・便宜上、生徒には「A1～A10」「B1～B10」・・・「N1～N10」の計140個の固有番号を割当てておく。

●欲しい結果●
・無作為に抽出した生徒を各グループに均等に振り分ける（各グループ20人ずつとなる）。

●振り分け条件●
・無作為に抽出した生徒が仮に「B3」であったとき、この生徒は「A+B」グループ以外のグループに属さなければならない。
つまり、生徒に割り当てられた固有番号（アルファベット）が付いていないグループに属さなければならない。
よって、例えば「A」君達10名と「B」君達10名の計20名は、必然的に「A+B」グループ以外のグループに属することになる。

と言うことになり、ランダムな人選で条件付のグループ分けを行うというニュアンスになりますが、ご理解いただけますでしょうか。

また、ご指摘のとおり、過去のグループ分けは踏襲しなくても構いません。

お手数をおかけいたしますが、よろしくお願いいたします。

回答No.1

KURUMITO

かなり面倒な操作になりませね。次のようにしてはどうでしょう。
例えばＡ列にＡ2セルにＡ1の生徒、Ａ3にＡ2の生徒、・・・Ａ11にＡ10の生徒、Ａ12にはＢ1の生徒、Ａ21にはＢ10の生徒、A２2にはＣ1の生徒、・・・・のようにしてＡ141にはＮ10の生徒が並んでいるとします。
そこでＢ2セルには次の式を入力します。
=IF(OR(A2="",AND(ROW(A2)>=1,ROW(A2)<=21)),"",RAND())
Ｃ2セルには次の式
=IF(B2="","",IF(AND(RANK(B2,B:B)<=20,COUNTIF(C$1:C1,"?*")<=19),A2,""))
Ｄ２セルには次の式
=IF(OR(A2="",AND(ROW(A2)>=22,ROW(A2)<=41),C2<>""),"",RAND())
Ｅ2セルには次の式
=IF(D2="","",IF(AND(RANK(D2,D:D)<=20,COUNTIF(E$1:E1,"?*")<=19),A2,""))
Ｆ2セルには次の式
=IF(OR(A2="",AND(ROW(A2)>=42,ROW(A2)<=61),C2<>"",E2<>""),"",RAND())
Ｇ2セルには次の式
=IF(F2="","",IF(AND(RANK(F2,F:F)<=20,COUNTIF(G$1:G1,"?*")<=9),A2,""))
Ｈ2セルには次の式
=IF(OR(A2="",AND(ROW(A2)>=62,ROW(A2)<=81),C2<>"",E2<>"",G2<>""),"",RAND())
Ｉ2セルには次の式
=IF(H2="","",IF(AND(RANK(H2,H:H)<=20,COUNTIF(I$1:I1,"?*")<=19),A2,""))
このような式を右横のセルに入力してから、それぞれの式を行番号141セルまでオートフィルドラッグします。
ここで、Ｃ列、Ｅ列、Ｇ列、Ｉ列には4グループの名前がそれぞれ表示されることになります。右横列にさらに同様の式を追加して全体を完成させてください。ご成功を祈っています。

お礼

早速のご回答、有難うございました。
初めて質問したので、ご回答をいただけるものか心配してましたが、
本当に早速ご回答くださってとても嬉しく思います。
お手間かけていただき有難うございました。

以下、文字数の都合上、必要最小限の表現としてます。
ご理解ください。

結果について、
今の結論は、上手くいかなかったと言う結果。
また、私には関数に関する理解力があまり備わっていないことをご理解いただき、以下お読みいただけると幸いです。

結果の詳細について、
まず、入力はご回答どおり、計算式をコピー & ペーストし、ドラッグ&ドロップにて貼付。

その結果、C,E,G,I列のそれぞれにはA列の固有番号がランダムに示されるが、
「C」列＝19個
「E」列＝19個
「G」列＝9個
「I」列＝19個
の結果が表示。
また、例えば“C3”と言う生徒は、「C」「E」「G」「I」列のいずれかに(表示されない場合もあるが)、同じ“C3”として表示されてしまう。意味、伝わりますでしょうか？
行いたいことは、“C3”の生徒は「C+D」グループ以外のグループに振り分けること。

齟齬があるといけないので、拙いですが確認のため「行いたいこと」を申し上げますと

●行いたいこと●
・140人の生徒を20人ずつランダムに7グループに振分ける
・グループ名は、A+Bグループ、C+Dグループ、・・M+Nグループの計7グループ

●事前準備●
・便宜上、生徒には「A1～A10」「B1～B10」・・・「N1～N10」の計140個の固有番号を割当て

●欲しい結果●
・無作為に抽出した生徒を各グループに均等に振り分ける（各グループ20人ずつとなる）

●振り分け条件●
・無作為に抽出した生徒が仮に「B3」であったとき、この生徒は「A+B」グループ以外のグループに属さなければならない。
つまり、生徒に割り当てられた固有番号（アルファベット）が付いていないグループに属さなければならない。
よって、「A」君達10名と「B」君達10名の計20名は、必然的に「A+B」グループ以外のグループに属することになる。

と言うことなのですが、ご理解いただけますでしょうか。
せっかくご回答いただきながら大変申し訳ございませんが、お時間がございましたら再度ご回答いただけると大変助かります。
よろしくお願いいたします。