Excelで群比較に関する質問です。

質問

下記のような例題について、Excelで処理する方法があれば教えてください。
マクロでも構いませんが、あまり知識が無いのでコメントを入れていただけるとありがたいです。

例題：

列Ａ1～Ａ40に人名。(ex, 相原、井上、、、渡辺)
列Ｂ1～Ｂ40にテストの点数（国語）
列Ｃ1～Ｃ40にテストの点数（数学）
（中略）
列Ｆ1～Ｆ40にテストの点数（英語）

上記のようなデータがあるとする。
ここから英語の上位20名と数学の上位20名の中で、どの人名が重複しているか。
また、英語、国語、数学の上位20名の中で同じ人名はどれか。

このような例題です。
手作業でやってやれないことはないのですが、
実際の処理が膨大になるので、二郡(あるいは複数郡)の比較方法で、
適当な方法があれば教えてください。よろしくお願いします。

回答No.2

keithin · ベストアンサー

sub macro1()
’せめて１行目はタイトル行にするのが当たり前
　range("1:1").insert
　range("A1:H1") = array("氏名","国語","数学","","","英語","英数","英国数")

’Ｇ列に英数上位者をマーク
　range("G2:G41").formula = "=(F2>=LARGE(F:F,20))+(C2>=LARGE(C:C,20))"
’Ｈ列に英国数上位者をマーク
　range("H2:H41").formula = "=G2+(B2>=LARGE(B:B,20))"

’英数ともに優秀者を抽出、コピー
　range("G:G").autofilter field:=1, criteria1:=2
　range("A:A").copy range("J1")
　range("J1") = "英数優秀者"
　activesheet.autofiltermode = false

’英国数ともに優秀者を抽出、コピー
　range("H:H").autofilter field:=1, criteria1:=3
　range("A:A").copy range("K1")
　range("K1") = "英国数優秀者"
　activesheet.autofiltermode = false
end sub

マクロにやらせてるその通りに手で行えば、マクロなんて使わなくても簡単に結果を出せます。

お礼

系統ごとに優秀な人を数値でマークして、
そのマークを基に条件に適合する人を探すという方法ですね。
確かにこれなら手作業でも手間なくできそうです。

回答ありがとうございました。

回答No.4

KURUMITO

作業列を作って対応するのがよいでしょう。
A1セルからF1セルにかけては氏名、国語、数学、・・と科目名が2文字で入力されているとしてその下方にそれぞれのデータが入力されているとします。
そこで上位20名の点数のランクをそれぞれの教科について調べ、それをN列からR列までに表示させます。
N1セルからR1セルにはB1セルからF1セルまでの教科名をコピーして貼り付けます。
N2セルには次の式を入力してR2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(B2="","",IF(RANK(B2,B:B)<=20,RANK(B2,B:B),""))

これで最上位から20番目の方のランクの番号が表示されます.。同じランク（点数）の方が多くいる場合には20人よりも多くの方に番号が付くこともあります。
このような作業列はその方のランクが直視できることで有意義であると思います。
この作業列のデータを使ってお求めの該当の方の氏名を表示させるわけですが例えばH1セルには英語と国語でランクが20位以内の方を表示させるためには　英語1国語　のように入力します。ここで間の1の数値は半角英数文字にしてください。
H2セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(INDEX($N:$R,ROW(),MATCH(LEFT(H$1,2),$N$1:$R$1,0))<>"",INDEX($N:$R,ROW(),MATCH(MID(H$1,FIND("1",H$1)+1,2),$N$1:$R$1,0))<>""),$A2,"")

H1セルに書かれた教科についてともに20位以下になっている方が表示されます。
次に3つの教科について20位以下の方についてI列に表示させます。
I1セルにはH1セルにならって　英語1国語2数学　のように入力します。
I2セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(INDEX($N:$R,ROW(),MATCH(LEFT(I$1,2),$N$1:$R$1,0))<>"",INDEX($N:$R,ROW(),MATCH(MID(I$1,FIND("1",I$1)+1,2),$N$1:$R$1,0))<>"",INDEX($N:$R,ROW(),MATCH(MID(I$1,FIND("2",I$1)+1,2),$N$1:$R$1,0))<>""),$A2,"")

H列やI列ではとびとびになって該当者が表示されますね。それらの氏名をまとめて表示させるためにK列とL列を使用します。
H1セルとI1セルの科目名をK1からL1セルに貼り付けます。
K２セルには次の式を入力し式を確定する段階でCtrlキーとShiftキーを同時に押しながらEnterキーを押します。

=INDEX(H:H,SMALL(IF(H$2:H$50<>"",ROW(H$2:H$50),1000),ROW(A1)))&""

その後にK2セルの式をL2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

K列とL列にはお望みの教科についてともに1位から20位以下となる方の氏名がまとめて表示されることになります。
なお、式をできるだけ簡単にするために教科名は必ず2文字で入力することとしています。
教科名を変えた場合でも即座に対応した表が得られます。

お礼

作業台と式の組み合わせだけでできるんですね。
ＭＡＴＣＨ関数は使ったことがなかったので、機会を見て使ってみようと思います。

回答ありがとうございました。

回答No.3

MarcoRossiItaly

フィルタ（古いバージョンではオートフィルタ）を表に取り付けて、表示されるボタンを押すと、どこかに「トップテン」というものがあります。それで上位または下位の 20 位以内に絞り込んでください。

同時に複数の列について、それぞれの 20 位以内のレコードに絞り込めます。つまり英語、国語、数学の列で同時に 20 位以内になるようにしたときに、残っている名前が答えです。絞り込んだ状態で表をコピーして、別シートに貼り付けて保存しておいたりしてもいいですね。

エクセル2010基本講座：数値フィルターのトップテン　http://www4.synapse.ne.jp/yone/excel2010/excel2010_filter3.html

なお各人の順位を求めたい場合、基本技としては、RANK 関数というものあたりを使って計算します。

Excel(エクセル)基本講座：順位の関数　http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/rank.htm

お礼

そういえばこういう機能もあったですね。今回の場合だとこの機能が一番簡単かもしれません。
Excelは便利なんですが、バージョンごとに機能とその場所が変わって困り物です(笑)

回答ありがとうございました。

回答No.1

MackyNo1

添付画像のようなレイアウトなら、例えばH列に英語と数学がいずれも上位20位以内の人の行に○を記載するように以下の数式をH2セルに入力して下方向にオートフィルします。

=IF(AND(RANK(F2,$F$2:$F$40)<=20,RANK(C2,$C$2:$C$40)<=20),"○","")

英語、数学、国語の3科目の場合は上記の条件式にもう1つB列を判定するる数式をANDの中に追加してください。

お礼

なるほど。ifとrankの組み合わせでできるんですね。
両方とも知っていた関数だけに目からうろこです。

どうもありがとうございました。