excelでのフィルタ

質問

excelを利用して成績をつけようと、現在フォーマットを作っているのですが、分からないところが出てきました。

次のようなものを出来るようにしたいです。

ノートが未提出であれば学籍番号・氏名を抜き出し"別シート"に保存する。

というものです。
同シート内でオートフィルタを利用し、ノート提出を"未提出"にすれば以上の表が出てきますが、これを自動化しなおかつ別シートへの表示を行いたいのです。

以上の問題が分かる方がいましたら教えてください。

よろしくお願い致します。

回答No.9

noname#33973

【具体例２】
【具体例】からの変更点
・作業列を任意のシートの任意の列(ここではsheet3のA列)に設置し、vlookupではなくoffsetとmatchを使用して参照。

・未提出者総数を予め作業セルに書き出す。
(式が短くなる／若干処理が軽くなる(?)）

・未提出者ナンバリングの際、未提出者を毎回カウントするのではなく、ナンバリング済み部分の最大値に１をプラスする。
(旧バージョンのExcelではcountifに関するバグがあり、作業列を他のブックに置いた場合不具合が出る可能性があるため。#6さんのご回答を参考にしました。）

仕様を
Sheet1：元データ
　A列：ノート提出（未／済）
　B列：氏名
　C列：学籍番号
　1行：タイトル行
　2行～999行：データ
Sheet2：フィルタ結果
　A列：未提出者通し番号
　B列：氏名
　C列：学籍番号
　1行：タイトル行
　2行～999行：データ
　A1セル：空白
Sheet3：作業シート
　A列：作業列(未提出者ナンバリング）
　B列：作業列(未提出者総数書き出し)
　1行：空白または数字に換算できない文字列
　2行～999行：データ

とすると、

Sheet3!A2セル：未提出者ナンバリング
=IF(Sheet1!A2="未",MAX(Sheet3!$A$1:A1)+1,"")
(当該行が未提出ならば、ナンバリング済番号の最大値＋１の番号を振る)

Sheet3!B2セル：未提出者総数
=MAX(A2:A999)
(未提出者ナンバーの最大値を書き出す)

Sheet2!A2セル：未提出者通し番号
=IF(A1>=Sheet3!$B$2,"",A1+1)
(未提出者総数に至るまで通し番号を振る）

Sheet2!B2セル：未提出者氏名
=IF(A2="","",OFFSET(Sheet1!B$1,MATCH($A2,Sheet3!$A$2:$A$999,0),0))
(通し番号に対応する行を作業列から検索、相対位置を取得して元データから参照）

Sheet2!C2セル：未提出者学籍番号
B2セルをコピー
(offset基準を相対参照しているのでコピー可)

として、Sheet3!B2セル以外をドラッグでコピー。

枠線処理、印刷範囲指定は以前と同様。

Excel2002で動作確認済み(記述ミスの可能性はアリ)