こんにちは いつもお世話になっています
参照先を見ていただくと助かるんですが、御面倒をおかけします。
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で質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの警告について
-
タイムスタンプとテキストから...
-
シートの情報を別のシートへま...
-
マクロの処理が遅くなった
-
Excelでの文字色
-
ワークシートに出現したこの画...
-
EXCELの散布図で日付が1900年に...
-
OFFSET関数を使用したいのです...
-
エクセルでファイルの最終更新...
-
エクセルの文字が途中から消える
-
エクセルデーターから必要な項...
-
Excel 大小比較演算子による「...
-
SUBTOTALは、参照された数字で...
-
エクセルの数式バーのフォント...
-
エクセルの「条件付き書式」を...
-
Excelについて教えてください。...
-
エクセルVBA 月の中で、月~土...
-
Excelの数字の前に入っている空...
-
Excelの関数について このよう...
-
セルの数を求めたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報