エクセルで特定の行だけ別シートに抽出する方法

たとえばＳｈｅｅｔ１に


　　　　　　　生徒番号　　生徒名　　　第１回模試　　到達度　　　　第２回模試　　到達度
　英数国　　１０１０１　　今川義元　　　　６２．５　　　　Ａ２　　　　　　　６０．２　　　　　Ａ２
　英数国　　１０１０２　　伊達政宗　　　　５０．６　　　　B2　　　　　　　５５．６　　　　　ｂ１
　　英　　　　１０１０１　　今川義元　　　　５６．９　　　　Ｂ１　　　　　　　５２．３　　　　　Ｂ２
　　英　　　　１０１０２　　伊達政宗　　　　５２．５　　　　Ｂ１　　　　　　　６０．２　　　　　Ａ２


という表があります。

Sheet２のＡ１セルに　生徒番号を入力すると、（１０１０１を入力した場合）

　　　　　　第１回模試　　第２回模試
英国数　　６２．５　　　　　６０．２　　

というように、該当の列の特定の列だけを表示させる方法はありますか？
フィルタをかけて抽出するのではなく、自動的に表示させたいのですが。
わかりにくい質問で申し訳ありません。なにとぞよろしくお願いします。

　　　　　　　　　　　　　

No.6 ベストアンサー 回答者： MackyNo1 回答日時： 2012/10/29 18:29

Sheet2のA2セルに入力した生徒番号の一致するSheet1のA列のデータを表示するなら、Sheet2のA4セルに以下の式を入力して下方向にオートフィルします。

=INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$B$2:$B$10<>$A$1)*10000+ROW($B$2:$B$10),),ROW(1:1)))&""

同様に第1回模試および第2回模試のデータは以下のような式になります。

B4セル
=IF(A4="","",INDEX(Sheet1!D:D,SMALL(INDEX((Sheet1!$B$2:$B$10<>$A$1)*10000+ROW($B$2:$B$10),),ROW(1:1))))

C4セル
=IF(B4="","",INDEX(Sheet1!F:F,SMALL(INDEX((Sheet1!$B$2:$B$10<>$A$1)*10000+ROW($B$2:$B$10),),ROW(1:1))))

この回答への補足

ありがとうございます。おかげで思っているような表ができました。
ところで、できあがったSheet２を使ってグラフを表示させたいのですが、これまたうまくいきません。
どうも、参照してきた数字を数字として認識できないようなのです。
セルの書式は「数値」を選んでいます。
試しに同じ数値を手入力するとグラフに反映するのですが。。。
いろいろやっかいなことを申しますが、何とぞご教示ください。

補足日時：2012/10/30 12:10

この回答へのお礼

皆様いろいろな回答をありがとうございました。
おかげさまで思っているような表ができました。

お礼日時：2012/10/31 17:57

No.5 回答者： doredora 回答日時： 2012/10/29 14:28

こんにちは

添付画像はシート2です

シート1の科目と生徒番号の間に１列挿入し、科目コードのフィールドをつくり、
英数国は１、英は２とコードを振ってあります

シート1のフィールド名を必要なセル（シート2）へコピーしたものが添付画像の２行目です
シート2のセルG3に

=DGET(Sheet1!$A$1:$H$5,D2,$B$2:$C$3)
=DGET(データ範囲,フィールド名,抽出条件)となります。

右へ試験回数分ドラッグします

シート1に１列挿入したのは、科目の英数国と英とをDGETが判別できない弱点をカバーするためです。

こんな感じでいかがでしょう？

No.4 回答者： mike_g 回答日時： 2012/10/29 14:09

[No.3]の補遺、

ステップ３の「次式」を書き漏らしていました。
=OFFSET(Sheet1!$A$1,SMALL(Sheet1!$H:$H,ROW(A1))-1,MATCH(A$2,Sheet1!$1:$1,0)-1)

No.3 回答者： mike_g 回答日時： 2012/10/29 14:05

添付図参照

１．Sheet1 のセル H2 に式 =IF(B2=Sheet4!A$1,ROW(),"") を入力して、
　　此れを下方にズズーッとドラッグ＆ペースト
２．Sheet2 のセル A3 に次の[条件付き書式]を設定
　　　数式が　　　=ISERROR(A3)
　　　フォント色　白
３．Sheet2 のセル A3 に次式を入力して此れを右方および下方にズズーッ
　　とドラッグ＆ペースト

No.2 回答者： tom04 回答日時： 2012/10/29 13:58

こんにちは！

一例です。

↓の画像のようにSheet1に作業用の列を設けます。
作業列H2セルに
=IF(OR(B2="",B2<>Sheet2!$A$1),"",ROW())
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

Sheet2のA3セルに
=IF(COUNT(Sheet1!H:H)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!H:H,ROW(A1))))

B3セルに
=IF(COUNT(Sheet1!$H:$H)<ROW(A1),"",INDEX(Sheet1!$C:$G,SMALL(Sheet1!$H:$H,ROW(A1)),MATCH(B$2,Sheet1!$C$1:$H$1,0)))
という数式を入れ隣のC3セルまでオートフィルでコピー！

最後にA3～C3セルを範囲指定　→　C3セルのフィルハンドルで下へコピーすると
画像のような感じになります。

※　作業列を使わず配列数式にする方法もありますが、データ量が極端に多い場合は
PCに負担をかけますので、あまりオススメできません。

※　セル配置は実状に合わせてください。

参考になりますかね？m（_ _）m

No.1 回答者： tsubuyuki 回答日時： 2012/10/29 13:49

例のように、

表中に生徒番号が重複して存在する場合、
・すべて抽出
・特定の行だけ抽出
後者の場合、その判断条件は何か？


生徒番号に重複が無く、抽出条件も生徒番号だけと言うのであれば
関数でも（VLOOKUP辺り）出来ると思いますが、
例の表のように重複があるとすると、その他の検索条件が必要であり、
そうなると関数だと少々捻る必要があります。
イメージだけで何も試していませんが、INDEXやMATCH辺りを駆使することになるかなぁと。

マクロで一気に必要な分だけ転記するやり方もありますが、
そこまで手間をかけるべき処理でもないような気もします。

フィルタで限定して特定列のみコピー貼り付けが一番手っ取り早いでしょう。

そんなわけで、
> 該当の列の特定の列だけを表示させる方法はありますか？
これが質問の本文だとしたら、回答は「あります」ですね。