こんにちは いつもお世話になっています
参照先を見ていただくと助かるんですが、御面倒をおかけします。
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ベストアンサー
- 回答日時:
>順位調整できるでしょうか。
例えばある教科のαクラスにおいて同点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
- 回答日時:
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))),"")
になります。
教科の数を増やす際には、例えば最初は3教科だった処を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
- 回答日時:
ものの順序としてJ~L列に、科目別通算順位から、
J2に、
=RANK(C2,C$2:C$999)
これを、横にK、L列までコピー
次、M~O列に、科目別クラス別順位、(何の意味があるのか??)
M2に、
=SUMPRODUCT((F$2:F$999=F2)*(C$2:C$999>C2))+1
これまた、横にN、O列までコピー
J2~O2が揃ったら、まとめて下に必要な数コピー
「科目別クラス別順位」といわれてもこのままでは何のこっちゃ?、読めないですね...
フィルタかませて読む??
No.3
- 回答日時:
今仮に、元データの表において、「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
- 回答日時:
どのようなエラーが出るかも書かれていないほどの関数の知識がないのでしょう。
単純な関数とオートフィルターを組み合わせればよいと思います。
1. オートフィルターをかけたときに表示されないものを0とする
I2セル =SUBTOTAL(9,C2)
右へ下へオートフィル
2. それぞれの順位
=RANK(I2,I$2:I$10)
右へ下へオートフィル
3. それぞれのクラスをオートフィルターで抽出します
数式やマクロを単純にコピペしたってダメです。理解できないなら何にもなりませんよ。
CoalTar様 ありがとうございました。お陰さまで解決しました。
画像まで付けていただき助かりました。
大切に使わせていただきます。
簡単で恐縮ですがお礼申し上げます。
No.1
- 回答日時:
数式を駆使してももちろん出来ますが、ピボットテーブルの方がはるかに簡単に出来るので、そちらをご紹介します。
手順:
レポートフィルタに「国語クラス」
行ラベルに「名前」
Σ値に「合計/国」
を配置
集計内のセル(サンプルではAの100点のセル)を右クリックして「並べ替え」で「降順」をセット
「名前 ▼」を右クリックして「フィルタ」から「トップテン」で「上位3項目」をセット
あとはページフィルタで国語のクラスを選択すれば出来上がり。
科目ごとに作成してください。
keithin様 ありがとうございました。お陰さまで解決しました。
丁寧に教えていただき助かりました。
大切に使わせていただきます。
簡単で恐縮ですがお礼申し上げます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 高校受験 中学や高校で、成績上位者の名前を廊下に張り出していましたか? 5 2022/09/25 23:21
- Excel(エクセル) RANK.EQとCOUNTIFSの組み合わせで同ポイントの場合、違う条件を加えて順位を付けたい。 1 2022/08/30 19:49
- Excel(エクセル) 並べ替え、ソートの構文がわからない。 お世話になります。VBA超初心者です。 エクセルでワークシート 2 2023/06/28 21:00
- Visual Basic(VBA) エクセルマクロでアニメを作る方法を教えてください。 1 2023/02/07 14:27
- Excel(エクセル) エクセルでエラーを無視して一番左側のセルの値を返したい 2 2023/07/27 13:06
- 大学・短大 (工学部)成績について 3 2022/09/07 14:16
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- MySQL うまくいきません教えてくださいお願いしますSQLです。クエリ構文です。 1 2023/07/07 12:39
- 学校 小中高は出席番号がアイウエオ順ですが、専門学校や大学は成績順ですか? 1番の人がテストの点数が1番高 3 2023/03/28 19:47
- MySQL SQLです。こんな感じですか?あってますか? うまくいきません教えてくださいお願いします 1 2023/07/08 15:27
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelはなんで先頭の0を消すん...
-
Excel元に戻す方法を教えてくだ...
-
Excelが固まってしまった。
-
エクセルで特定の範囲内から小...
-
Excel 2019 のピボットテーブル...
-
テレビを購入してYouTubeのボタ...
-
Excel2013のF6キー操作について
-
西暦や和暦の表示をyyyymmdd表...
-
【関数】スペースがいくつ入っ...
-
【Microsoft Office Excel Comp...
-
Excelのオートフィル
-
別シートからの文字を変更
-
Excelのセルを飛ばして入力する
-
MOS365 Excel Expert / Excel R...
-
エクセルで指定した日付、店舗...
-
4つのパターンを表示するEXACT...
-
スマートな関数を教えて下さい。
-
【Excel】セル内の時間帯が特定...
-
Excel初心者です。 詳しい方、...
-
Excelで全角を半角にしたいので...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報