エクセル クラス別に各教科の順位と名前を抽出

質問

こんにちは　いつもお世話になっています

参照先を見ていただくと助かるんですが、御面倒をおかけします。
http://oshiete.homes.jp/qa6551932.html
質問内容はこちらと同じですが
こちらの関数を使わせてもらおうとしたところ失敗しています。

J2セルに=IF(OFFSET(E2,0,MATCH(成績順位!C$1,C$1:E$1,FALSE))=成績順位!C$2,OFFSET(B2,0,MATCH(成績順位!C$1,C$1:E$1,FALSE))-A2/1000,"")

と入力した時点でエラーが出ました。

他の関数でも構いません。マクロでは複雑で応用が難しいと思っています。

クラス別に各教科の順位と名前を抽出する方法を教えてください。
ピボットテーブルでもできるでしょうか？

初心者用のものだと助かります。
エクセル2007

回答No.6

kagakusuki · ベストアンサー

>順位調整できるでしょうか。

　例えばある教科のαクラスにおいて同点2位が3人いて、同じ教科のβクラスに2位は1人しかいなかった場合、A列には何位を表示させるべきか決める事が出来なくなります。
　ですから、同じ点数の場合は同じ順位とする場合には、別の列に纏めて順位を表示させる事は諦めねばなりません。
　そこで、1つのセル内に

1位 A 100点

といった具合に、順位と名前と点数を、同じセルの中に纏めて表示する事にします。
　そのためには、各教科のシートのB3セルに入力する関数を次の様なものに変更して下さい。

=IF(COUNTIF(Sheet2!$1:$1,$A$1),IF(ROWS($3:3)>COUNTIF(OFFSET(Sheet1!$H:$H,,MATCH($A$1&"クラス",Sheet1!$H$1:$L$1,0)-1),B$2),"",COUNTIFS(OFFSET(Sheet1!$C:$C,,MATCH($A$1,Sheet1!$C$1:$G$1,0)-1),">"&INDEX(Sheet1!$C:$G,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$E$1,0)-1),0),MATCH($A$1,Sheet1!$C$1:$G$1,0)),OFFSET(Sheet1!$H:$H,,MATCH($A$1&"クラス",Sheet1!$H$1:$L$1,0)-1),B$2)+1&"位 "&INDEX(Sheet1!$B:$B,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$E$1,0)-1),0))&" "&INDEX(Sheet1!$C:$G,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$E$1,0)-1),0),MATCH($A$1,Sheet1!$C$1:$G$1,0))&"点"),"")

　以上です。

お礼

kagakusuki 様　ありがとうございました。お蔭様で解決しました。
早速の御返事、恐れ入ります。
順位も点数も表示され驚きました。
大切に使わせていただきます。
簡単で恐縮ですが、お礼申し上げます。

回答No.5

kagakusuki

ANo.3です。

>科目が増えた時の関数はどこを直せばよいでしょうか。
>例えば、「理科」の次に「社会」（F1セル)「英語」（G1セル)などを入れたい時です。

Sheet1のH1セルの関数は

=IF(INDEX($C:$G,ROW(),COLUMNS($H:H))="","",INDEX($C:$G,ROW(),COLUMNS($H:H))&"クラス")

Sheet2のA1セルの関数は

=INDEX(Sheet1!$C:$G,ROW(),COLUMNS($A:A))&""

Sheet2のA2セルの関数は

=IF(AND(INDEX(Sheet1!A:A,ROW())<>"",INDEX(Sheet1!B:B,ROW())<>"",ISNUMBER(INDEX(Sheet1!C:C,ROW())),INDEX(Sheet1!H:H,ROW())<>""),INDEX(Sheet1!H:H,ROW())&"☆"&COUNTIFS(Sheet1!C:C,">"&INDEX(Sheet1!C:C,ROW()),Sheet1!H:H,INDEX(Sheet1!H:H,ROW()))+COUNTIFS(Sheet1!C$1:INDEX(Sheet1!C:C,ROW()),INDEX(Sheet1!C:C,ROW()),Sheet1!H$1:INDEX(Sheet1!H:H,ROW()),INDEX(Sheet1!H:H,ROW())),"")

各教科のシートのA1セルの関数は変更なしの

=REPLACE(CELL("filename",A1),1,FIND(".xlsx]",CELL("filename",A1))+LEN(".xlsx]")-1,)

各教科のシートのB3セルの関数は

=IF(COUNTIF(Sheet2!$1:$1,$A$1),IF(ROWS($3:3)>COUNTIF(OFFSET(Sheet1!$H:$H,,MATCH($A$1&"クラス",Sheet1!$H$1:$L$1,0)-1),B$2),"",INDEX(Sheet1!$B:$B,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$E$1,0)-1),0))),"")

になります。

　教科の数を増やす際には、例えば最初は３教科だった処を5教科に場合には、Sheet1のC列～D列の間と、Sheet1のF列～H列の間、それとSheet2のA列～C列の間に、それぞれ2列ずつ列を挿入（例えばE列の前に2列だけ挿入する際には、E1セルの上にある「E」と表示されているマス目とその右隣の「F」と表示されているマス目をまとめて選択→選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック→現れた選択肢の中にある[挿入]をクリック）してから、Sheet1のH1セル、Sheet2のA1セル、Sheet2のA2セル、各教科のシートのB3セル等の関数を入力したセル（各教科のシートのA1セル以外）をコピーし直せば良い訳です。

お礼

kagakusuki　様　ありがとうございました。
お陰さまで成功しました。
更に重ねてお尋ねするのは気が引けるのですが
同点のケースがあるのですが、順位調整できるでしょうか。
順位表記の方が面倒であれば、順位表記なしで、順位ごとに名前と一緒に点数をつけていただけると助かるんですが。もちろん、順位表記の調整できれば点数は不要です。
勝手なお願いですみません。気長にお待ちしております。

回答No.4

K Kazz（@JazzCorp）

ものの順序としてＪ～Ｌ列に、科目別通算順位から、
Ｊ２に、
=RANK(C2,C$2:C$999)
これを、横にＫ、Ｌ列までコピー

次、Ｍ～Ｏ列に、科目別クラス別順位、（何の意味があるのか？？）
Ｍ２に、
=SUMPRODUCT((F$2:F$999=F2)*(C$2:C$999>C2))+1
これまた、横にＮ、Ｏ列までコピー

Ｊ２～Ｏ２が揃ったら、まとめて下に必要な数コピー

「科目別クラス別順位」といわれてもこのままでは何のこっちゃ？、読めないですね...
フィルタかませて読む？？

回答No.3

kagakusuki

今仮に、元データの表において、「NO.」という項目名が入力されているセルが、Sheet1のA1セルであり、Sheet2のA列～C列を作業列として使用して、各教科毎のシートに、クラス別の成績順に名前を表示させるものとします。

　まず、Sheet1の表において、C1～E1に入力する教科名を、「国」、「算」、「理」の様な省略形ではなく、「国語」、「算数」、「理科」の様に省略しない形で入力して下さい。
　次に、Sheet1のF1セルに次の関数を入力して下さい。

=IF(INDEX($C:$E,ROW(),COLUMNS($F:F))="","",INDEX($C:$E,ROW(),COLUMNS($F:F))&"クラス")

　次に、Sheet1のF1セルをコピーして、Sheet1のG1～H1の範囲に貼り付けて下さい。

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

=INDEX(Sheet1!$C:$E,ROW(),COLUMNS($A:A))&""

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

=IF(AND(INDEX(Sheet1!A:A,ROW())<>"",INDEX(Sheet1!B:B,ROW())<>"",ISNUMBER(INDEX(Sheet1!C:C,ROW())),INDEX(Sheet1!F:F,ROW())<>""),INDEX(Sheet1!F:F,ROW())&"☆"&COUNTIFS(Sheet1!C:C,">"&INDEX(Sheet1!C:C,ROW()),Sheet1!F:F,INDEX(Sheet1!F:F,ROW()))+COUNTIFS(Sheet1!C$1:INDEX(Sheet1!C:C,ROW()),INDEX(Sheet1!C:C,ROW()),Sheet1!F$1:INDEX(Sheet1!F:F,ROW()),INDEX(Sheet1!F:F,ROW())),"")

　次に、Sheet2のA1～A2の範囲をコピーして、Sheet2のA1～C2の範囲に貼り付けて下さい。
　次に、Sheet2のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、適当な教科のシート（ここでは仮に「国語」というシート名のシートとします）のB2から始めて右に向かって、各クラスの名称を入力して下さい。
　次に、国語シートの
A3セルに　　1位
A4セルに　　2位
A5セルに　　3位
A6セルに　　4位
　　・　　　　　　・
　　・　　　　　　・
　　・　　　　　　・
と言う具合に、順位を示す項目名を入力して下さい。
　次に、国語シート（各教科のシート名は、Sheet1の表においてC1～E1に入力した文字列と同じ文字列として下さい）のA1セルに次の関数を入力して下さい。

=REPLACE(CELL("filename",A1),1,FIND(".xlsx]",CELL("filename",A1))+LEN(".xlsx]")-1,)

　次に、国語シートのB3セルに次の関数を入力して下さい。

=IF(COUNTIF(Sheet2!$1:$1,$A$1),IF(ROWS($3:3)>COUNTIF(OFFSET(Sheet1!$F:$F,,MATCH($A$1&"クラス",Sheet1!$F$1:$H$1,0)-1),B$2),"",INDEX(Sheet1!$B:$B,MATCH(B$2&"☆"&ROWS($3:3),OFFSET(Sheet2!$A:$A,,MATCH($A$1,Sheet2!$A$1:$C$1,0)-1),0))),"")

　次に、国語シートのB3セルをコピーして、国語シートのC3～D3の範囲に貼り付けて下さい。
　次に、国語シートのB3～D3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　次に、国語シートのコピーシートを複数複製し、各々のシート名を各教科の名称として下さい。
　その際、シート名は必ずSheet1の表においてC1～E1に入力した文字列と同じ文字列として下さい。

　これで、各教科毎のクラス別順位表が自動的に作成されます。

お礼

kagakusuki 様　ありがとうございました。お蔭様で解決しました。
重ねて質問させていただきたいのですが、
科目が増えた時の関数はどこを直せばよいでしょうか。
例えば、「理科」の次に「社会」（F1セル)「英語」（G1セル)などを入れたい時です。

Sheet1のF1セルには
=IF(INDEX($C:$G,ROW(),COLUMNS($H:H))="","",INDEX($C:$G,ROW(),COLUMNS($H:H))&"クラス")
として、
Sheet2のA1セルには
=INDEX(Sheet1!$C:$G,ROW(),COLUMNS($A:A))&""
としました。

Sheet2のA2セルにいれる関数ではどこを変えればいいでしょうか。
=IF(AND(INDEX(Sheet1!A:A,ROW())<>"",INDEX(Sheet1!B:B,ROW())<>"",ISNUMBER(INDEX(Sheet1!C:C,ROW())),INDEX(Sheet1!F:F,ROW())<>""),INDEX(Sheet1!F:F,ROW())&"☆"&COUNTIFS(Sheet1!C:C,">"&INDEX(Sheet1!C:C,ROW()),Sheet1!F:F,INDEX(Sheet1!F:F,ROW()))+COUNTIFS(Sheet1!C$1:INDEX(Sheet1!C:C,ROW()),INDEX(Sheet1!C:C,ROW()),Sheet1!F$1:INDEX(Sheet1!F:F,ROW()),INDEX(Sheet1!F:F,ROW())),"")

この後の関数も教えてもらえないでしょうか。
初心者質問で恐縮です。よろしくお願いします。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

どのようなエラーが出るかも書かれていないほどの関数の知識がないのでしょう。
単純な関数とオートフィルターを組み合わせればよいと思います。

1. オートフィルターをかけたときに表示されないものを0とする
　 I2セル =SUBTOTAL(9,C2)
　 右へ下へオートフィル

2. それぞれの順位
　 =RANK(I2,I$2:I$10)
　 右へ下へオートフィル

3. それぞれのクラスをオートフィルターで抽出します

数式やマクロを単純にコピペしたってダメです。理解できないなら何にもなりませんよ。

お礼

CoalTar様　ありがとうございました。お陰さまで解決しました。
画像まで付けていただき助かりました。
大切に使わせていただきます。
簡単で恐縮ですがお礼申し上げます。

回答No.1

keithin

数式を駆使してももちろん出来ますが、ピボットテーブルの方がはるかに簡単に出来るので、そちらをご紹介します。

手順：
レポートフィルタに「国語クラス」
行ラベルに「名前」
Σ値に「合計／国」
を配置

集計内のセル（サンプルではＡの１００点のセル）を右クリックして「並べ替え」で「降順」をセット
「名前　▼」を右クリックして「フィルタ」から「トップテン」で「上位３項目」をセット
あとはページフィルタで国語のクラスを選択すれば出来上がり。

科目ごとに作成してください。

お礼

keithin様　ありがとうございました。お陰さまで解決しました。
丁寧に教えていただき助かりました。
大切に使わせていただきます。
簡単で恐縮ですがお礼申し上げます。