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

Question

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

参照先を見ていただくと助かるんですが、御面倒をおかけします。
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

kagakusuki · Accepted Answer

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

例えばある教科のαクラスにおいて同点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 · Answer

　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セル以外）をコピーし直せば良い訳です。

JazzCorp · Answer

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

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

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

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

kagakusuki · Answer

　今仮に、元データの表において、「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に入力した文字列と同じ文字列として下さい。

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

CoalTar · Answer

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

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

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

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

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

keithin · Answer

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

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

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

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

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

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

ANo.3です。

ものの順序としてＪ～Ｌ列に、科目別通算順位から、

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

どのようなエラーが出るかも書かれていないほどの関数の知識がないのでしょう。

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

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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

　ANo.3です。

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