エクセルの参照結果を空欄を詰めて表示させたい

エクセルの入門書などによくある学校の成績表で、縦軸B3からB25までに氏名、横軸C3からCQまで科目名があるとします。
このJの列にある科目で100点を取った生徒のみを別シートのB28から縦に順番に表示させたいです。

基本的な関数を使って、100点を取った人のみ抽出して表示させることはできるのですが、空欄を詰めて順に表示させることができず困っております。
よろしくお願いします。

No.4 ベストアンサー 回答者： kagakusuki 回答日時： 2014/02/22 07:57

　もし、A列等に出席番号等の「同じ列の中に、同じ値が複数入力されている事があり得ないデータ」が入力されている場合には、次の様な方法もあります。

　今仮に、

>エクセルの入門書などによくある学校の成績表で、縦軸B3からB25までに氏名、横軸C3からCQまで科目名があるとします。

の成績表が存在しているシートのシート名がSheet1であり、Sheet2に「Sheet1のJの列にある科目」で100点を取った生徒のデータのみを表示させるものとします。

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

=Sheet1!$A$2

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

=IF(ROWS($28:28)>COUNTIF(Sheet1!$J:$J,"=100"),"",INDEX(INDEX(Sheet1!$A:$A,MATCH(A27,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$A:$A,ROWS(Sheet1!$A:$A)),MATCH(100,INDEX(Sheet1!$J:$J,MATCH(A27,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$J:$J,ROWS(Sheet1!$A:$A)),0)))

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

=IF($A28="","",VLOOKUP($A28,Sheet1!$A:$B,2,FALSE))

　次に、Sheet2のA28～B28の範囲をまとめてコピーして、Sheet2のA29～B51の範囲に貼り付けて下さい。

　以上です。


　因みに、もし、生徒の中に同姓同名の生徒が存在しない事が保障されている場合には、A列を表示させる事なしに、Sheet2のB28セルに次の様な関数を入力してから、Sheet2のB28セルをコピーして、Sheet2のB29～B51の範囲に貼り付けるだけで済みます。(Sheet2のB27セルに、Sheet1のB2セルと同じ値を入力しておく必要はあります)

=IF(ROWS($28:28)>COUNTIF(Sheet1!$J:$J,"=100"),"",INDEX(INDEX(Sheet1!$B:$B,MATCH(B27,Sheet1!$B:$B,0)+1):INDEX(Sheet1!$B:$B,ROWS(Sheet1!$B:$B)),MATCH(100,INDEX(Sheet1!$J:$J,MATCH(B27,Sheet1!$B:$B,0)+1):INDEX(Sheet1!$J:$J,ROWS(Sheet1!$B:$B)),0)))

この回答へのお礼

初めての質問だったのですが、こんなに早くしかもこんな分量の回答が来て大変感激しました。
助かりました。ありがとうございました。

お礼日時：2014/02/24 13:28

No.6 回答者： MackyNo1 回答日時： 2014/02/24 00:41

元データがSheet1にあり、例示のレイアウトなら以下の式を入力して下方向に適当数オートフィルコピーしてみてください。

=INDEX(Sheet1!$B:$B,SMALL(INDEX((Sheet1!J$3:J$25<>100)*100+ROW($3:$25),),ROW(1:1)))&""

この回答へのお礼

初めての質問だったのですが、こんなに早く回答が来て大変感激しました。
助かりました。ご親切にありがとうございました！

お礼日時：2014/02/24 13:30

No.5 回答者： tom04 回答日時： 2014/02/22 11:03

こんにちは！

質問ではJ列限定のようですが、科目名が変わっても対応できるようにしてみました。
元データはSheet1にあり、Sheet2に表示するようにしています。
↓の画像で左側がSheet1とし、右側がSheet2でSheet2のB1セルに検索科目名を入力すると
100点の人の名前を表示します。

Sheet2のA4セルに
=IFERROR(INDEX(Sheet1!B$1:B$50,SMALL(IF(OFFSET(Sheet1!B$1:B$50,,MATCH(B$1,Sheet1!B$2:CQ$2,0)-1,,1)=100,ROW(A$1:A$50)),ROW(A1))),"")

これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定！
この画面からコピー＆ペーストする場合は上記数式をドラッグ＆コピー　→　Sheet2のA4セルを選択　→　数式バー内に貼り付け
→　そのまま（編集可能なまま）Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

このA4セルをオートフィルで下へコピー！
これで画像のような感じになります。

※　質問では別SheetのB28セルから表示したいというコトですが、セル配置は実状に合わせてください。
※　IFERROR関数を使用していますので、Excel2007以降のバージョンで可能です。m(_ _)m

この回答へのお礼

初めての質問だったのですが、こんなに早く回答が来て大変感激しました。
しかも科目も選べるようになっていてすごいと思いました。
助かりました。ありがとうございました。

お礼日時：2014/02/24 13:30

No.3 回答者： Cupper-2 回答日時： 2014/02/22 05:07

失礼しました・・・ちょっと間違い訂正です。

×　＝ＩＮＤＥＸ（Ｂ＄３：Ｂ＄２５，ＭＡＴＣＨ（ＲＯＷ（Ａ１），ＣＲ＄３：ＣＲ＄２５））
○　＝ＩＮＤＥＸ（Ｂ＄３：Ｂ＄２５，ＭＡＴＣＨ（ＲＯＷ（Ａ１），ＣＲ＄３：ＣＲ＄２５，０））

ＭＡＴＣＨ関数に照合型の「，０」が抜けていました。
「０」を指定しないと上から数えて一番初めに出てきた数字を拾わないんです。・・・たしか。

この回答へのお礼

初めての質問だったのですが、こんなに早く回答が来て大変感激しました。
助かりました。ありがとうございました。

お礼日時：2014/02/24 13:28

No.2 回答者： Cupper-2 回答日時： 2014/02/22 05:02

自分なら点数が入力されているシートに作業列を設け、そこにマークを入れ、そのマークを頼りに表示させるようにします。

解答には参照するセルのシートを指定する”Ｓｈｅｅｔ１！”の記述を省いて記述しますので、質問者さんの環境に合わせて追加してください。
（記述すると見づらくなって、何をしているのか分かりにくくなるんですよ）
あと、自分はすべて全角文字で入力していますので、本文をコピーするときは注意してください。
（半角文字だとメールで文章が途切れるんですよ）


で、本題。

ＣＲ列が空いているとして、ＣＲ３セルへ
　　＝ＣＯＵＮＴＩＦ（Ｊ＄３：Ｊ３，”＝１００”）
と入力しＣＲ２５セルまでコピーします。
これで１００点を取った人に対応する行で数字が加算されます。

そしてこれが１から１ずつ増えている行番号をＭＡＴＣＨ関数で拾えばＯＫ。
あとはＩＮＤＥＸ関数で対応するＢ列を参照すればいいです。

　　＝ＩＮＤＥＸ（Ｂ＄３：Ｂ＄２５，ＭＡＴＣＨ（ＲＯＷ（Ａ１），ＣＲ＄３：ＣＲ＄２５））

こんな感じ。
で、あとはこれを２３行分コピーです。

なお、エラーに関して何も処置していませんので、
１００点を取った人の数が２３人より少ない時　＃Ｎ／Ａ　のエラーが返ってきます。
そこは自身で工夫してみてください。



汎用性を持たせるためにＪ列をＩＮＤＩＲＥＣＴ関数で指定するようにすると、他の列を検索することが楽になるかもしれません。
どこかのセルに”Ｊ”と入れたらＪ列・・・と言うようにね。
（結構めんどくさいことをしなければならないので割愛ですｗ）

この回答へのお礼

初めての質問だったのですが、こんなに早く回答が来て、かつこんなに親切な文面に大変感激しました。
助かりました。ありがとうございました。

お礼日時：2014/02/24 13:29

No.1 回答者： keithin 回答日時： 2014/02/22 03:58

＞基本的な関数を使って、100点を取った人のみ抽出して表示させることはできる

では同様に基本的な関数だけを使って。

別シートのＡ３に
=IF(成績表!J3=100,ROW(J3),"")
と記入、以下コピー

＃参考
上述は別に３行目から始める必要は全くありません。


Ｂ２８に
=IF(ROW(B1)>COUNT(A$3:A$25),"",INDEX(成績表!B:B,SMALL(A$3:A$25,ROW(B1))))
と記入、以下コピー。

この回答へのお礼

初めての質問だったのですが、こんなに早く回答が来て大変感激しました。
助かりました。ありがとうございました。

お礼日時：2014/02/24 13:26