excelでのフィルタ

質問

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

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

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

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

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

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

回答No.10

noname#33973 · ベストアンサー

補足

・フィルタ結果シートで、#6さんや#7さんのようにrowを使って自セル位置を取得すれば、未提出者通し番号は不要です。

・今回は項目がシンプルでしたが、項目が変動したり、元データとフィルタ結果で項目の順序が異なるような場合は、#7さんのようにoffsetでなくindexを使った方が潰しが効くかもしれません。

・#6さんの例ですが、?の部分に適切な数字を入れて、セル参照についている不要な(?)括弧を外せばちゃんと動きます。(2002で動作確認。バージョンによって括弧があっても動く、或いは必要なのかは不明)

もっと親切な人もいるかもしれませんが、これ以上のことは“質問と回答”というより“作業依頼と作業”になると考えますので、私としてはこれを最終回答にしたいと思います。

以下は“アドバイス”です。

数式の“例”が示されても、関数やアルゴリズムの意味が判っていないと実際のデータに合わせた応用や変更ができるとは限りません。

一方、例として提示された数式に間違いがあって上手く動かなくても、或いは、アルゴリズムのみで具体的な数式が提示されていなくても、関数やアルゴリズムの意味が理解できていれば、自分で補足・修正して使用することができます。

提示された数式やアルゴリズムをみてもピンと来ない場合は、課題が自分の現在の技術レベルを超えているということであり、質問するよりも自分で調べたり勉強したり練習したりした方が良いと思います。

では。

お礼

>提示された数式やアルゴリズムをみてもピンと来ない場合は、課題が自分の現在の技術レベルを超えているということであり、質問するよりも自分で調べたり勉強したり練習したりした方が良いと思います。

確かにそうですね。
勉強不足だと感じます・・・
今回は有難うございました。

回答No.11

134

Ａ２セルに
=if(iserror(vlookup(row()-1,sheet1!($A$1:$D$?),3,0)),"",vlookup(row()-1,sheet1!($A$1:$D$?),3,0))

ごめんなさい。　カッコが多いですね。kater_kurz様、ご指摘ありがとうございます。

=if(iserror(vlookup(row()-1,sheet1!$A$1:$D$?,3,0),"",vlookup(row()-1,sheet1!$A$1:$D$?,3,0))

Ｂ２セルに
=if(A2="","",vlookup(row()-1,sheet1!($A$1:$D$?),4,0))

も

Ｂ２セルに
=if(A2="","",vlookup(row()-1,sheet1!$A$1:$D$?,4,0))

ですね。
大変失礼しました。

回答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で動作確認済み(記述ミスの可能性はアリ)

回答No.8

SevenStar777

No.1です。
申し訳ありません。
誤って修正前を貼り付けてしまったようです。
今さらですが訂正させて下さい。

成績表の書式を以下のものと仮定して、
データが入力されてるシート名を"Sheet1"、
抽出先のシート名を"Sheet2"とします。

　　　　Ａ　　　　　　Ｂ　　　　　Ｃ
１　ノート提出　学籍番号　氏　名
２　　　未　　　　　0001　　　Ａさん
３　　　済　　　　　0002　　　Ｂさん

Sub Macro1()

　Dim i as Integer
　Dim RowNo1, RowNo2 as Integer

　Sheets("Sheet2").Range("A2:C?").ClearContents
　RowNo1 = 2: RowNo2 = 2
　Do Until Sheets("Sheet1").Cells(RowNo1, 1).Value = ""
　　If Sheets("Sheet1").Cells(RowNo1, 1).Value = "未" Then
　　　For i = 1 to 3
　　　　Sheets("Sheet2").Cells(RowNo2, i).Value = Sheets("Sheet1").Cells(RowNo1, i).Value
　　　Next i
　　　RowNo2 = RowNo2 + 1
　　End If
　　RowNo1 = RowNo1 + 1
　Loop

End Sub

更に、今さらですが、
Ａ列の空白セルまで読み取るようにしています。

マクロを繰り返し実行する場合は抽出先シート("Sheet2")の入力内容を消去する必要があるかと思い１行追加しました。
Sheets("Sheet2").Range("A2:C?").ClearContents
上記の?は最終行の数値に置き換えてください。

回答No.7

imogasi

ＶＢＡを使えば簡単です。ＶＢＡはどうもと言う声があるので、関数でやって見ます。
未提出の場合はＣ列に「未」と入れるとします。
（データ）Ｓｈｅｅｔ２のＡ１：Ｃ４と結果のＤ１：Ｄ４
1 山田 未 1
3 下田 1
4 上田 未 2
6 神田 2
Ｄ１に=COUNTIF($C$1:C1,"未")と入れて最下行まで複写する。結果は上記Ｄ列の通り。Ｄ列は必要悪の作業列なので、普通では見えない列にするか、非表示にする手もある。
そしてＳｈｅｅｔ３のＡ１に
=INDEX(Sheet2!$A$1:$C$10,MATCH(ROW(A1),Sheet2!$D$1:$D$10,0),COLUMN(A1))
といれ、Ａ１で＋ハンドルを出して、Ｃ１まで複写する。
＄Ｄ＄１０の１０は適当に大きく。
Ａ１：Ｃ１を範囲指定し、Ｃ１で＋}ハンドルを出して第２行目（Ｓｈｅｅｔ２のＤ列の最終行の数の２の数だけ）まで複写する。
（結果）
1 山田 未
4 上田 未
なお上の式はそれだけでもはじめての人には難しいので、
Ｓｈｅｅｔ２のＤ列の未入力行のエラー防止やＳｈｅｅｔ３の第３行目以下に式を設定したときのエラー防止は省略します。考えて見てください。
なおＳｈｅｅｔ２にしたのは意味はありません。

回答No.6

134

#1さんの書式にＡ列挿入させて頂きます。
sheet1に、

　　　Ａ　　　Ｂ　　　　　　Ｃ　　　　　Ｄ
1　　　　　　提出有無　　学籍No.　　　氏名
2
3

という感じです。

とりあえず、Ｂ列には、データ→入力規則→リストで「○,×」とします。

Ｃ，Ｄ列は、手入力してください。

で、Ａ２セルに
=if(b2="×",1,"")
と入力してください。
ついで、Ａ３セルに
=if(countif(B3:$B$2,"×")=max(A2:$A$2),"",max(A2:$A$2)+1)
と入力します。
Ａ３セルの関数を最下データまでコピーしてください。

sheet2を設定します。（ノート未提出者を抽出するシート）

　　　　Ａ　　　　　Ｂ　　　
1　　学籍No.　　　氏名
2
3

という感じでしょうか。

Ａ２セルに
=if(iserror(vlookup(row()-1,sheet1!($A$1:$D$?),3,0)),"",vlookup(row()-1,sheet1!($A$1:$D$?),3,0))

ただし、?　は、　最下データの行番号を入れてください。

Ｂ２セルに
=if(A2="","",vlookup(row()-1,sheet1!($A$1:$D$?),4,0))

ただし、?　は、　最下データの行番号を入れてください。

と入力して、関数を下にコピーしてください。

最後に、シート１のＡ列を「表示しない」にすると、勘弁に未提出者を抽出できると思います。

補足

同じようにやろうとするとエラーが起きてしまうのですが・・・

回答No.5

noname#33973

#3です。
先の回答はちょっと大雑把に過ぎたかと思い始めたので、蛇足かもしれませんが、具体例と補足。

以下Excel2000で動作確認済みですが、学生の総数によっては、計算にかかる時間やファイルサイズの点で実用にならないかも(汗

【具体例】
仕様を
Sheet1：
　A列：作業列（未提出者をナンバリング）
　B列：ノート提出（未／済）
　C列：氏名
　D列：学籍番号
　1行：タイトル行
　2行～999行：データ
Sheet2：
　A列：未提出者の通し番号
　B列：氏名
　C列：学籍番号
　1行：タイトル行
　2行～999行：データ
　A1セル：空白
とすると、

Sheet1A2セル：
=IF(B2="未",COUNTIF($B$2:B2,"未"),"")
(未提出者のみ上から順にカウント）

Sheet2A2セル：
=IF(A1>=MAX(Sheet1!$A$2:$A$999),"",A1+1)
(未提出者総数に至るまで通し番号を振る）

Sheet2B2セル：
=IF(A2="","",VLOOKUP(A2,Sheet1!$A$2:$D$999,3,0))
(通し番号に対応する行を元データから検索して参照）

Sheet2C2セル：
=IF(A2="","",VLOOKUP(A2,Sheet1!$A$2:$D$999,4,0))
(通し番号に対応する行を元データから検索して参照）

と入力して以下ドラッグ。

枠線をつける場合は、A1セルのみ普通に罫線指定、それ以外は条件付書式で空白でない場合のみ外枠指定。

印刷が必要な場合は、名前の定義で
Print_Area：
=INDIRECT("A1:C"&(COUNT($A$1:$A$999)+1))
(空白でない行数をカウントして表の範囲を返す）

として印刷範囲を指定。

【補足】
作業列をシート１の表の左に設けたく場合、
vlookupの代わりにmatchとoffsetを併用して参照すれば、作業列を表の右や別シートに置くのも可能です。

補足

>vlookupの代わりにmatchとoffsetを併用して参照すれば、作業列を表の右や別シートに置くのも可能です。

の方法を教えていただけると助かります。

回答No.4

twowelk

すみません、一つ訂正します。
先ほどの式ですが、

×　=IF(Sheet1!A2="",Sheet1!B2)
○　=IF(Sheet1!A2="",Sheet1!B2,"")

が正しいです。

失礼しました。

回答No.3

noname#33973

素朴、というより原始的な方法を２つほど。

元データの入っているシートをシート１
フィルタ結果を表示するシートをシート２
とします。

・オートフィルタとユーザー設定のビューを使って。
シート１の表全体をシート２にリンク貼り付け。オートフィルタでフィルタリングした状態をビューに登録しておけば、完全ではありませんが、ほぼ自動化。

・countifとvlookupを使って
シート１の表の左側に作業列を設け、countifで未提出者をナンバリング。シート２で、vlookup等を使って順に参照すれば完全自動化。
枠線は条件付書式を利用。

お世辞にもスマートとはいえませんが、ご参考まで。

回答No.2

twowelk

マクロ無しで行うならば、このような方法もあります。
No.1さんに合わせます。

　　　　Ａ　　　　　Ｂ　　　　Ｃ
１　ノート提出　学籍番号　氏　名
２　　　　　　　　　　0001　　Ａさん
３　　　○　　　　　0002　　Ｂさん

ノート提出者には、○（何でもいいです）をつけ、未提出者は
空欄にしておきます。

Sheet2の構成は
　　　　　Ａ　　　　　Ｂ　　　　
１　　学籍番号　氏　名
２　　　　
３　　　　

例えば、このようにして、
２Aに以下の式を入れます。

=IF(Sheet1!A2="",Sheet1!B2)

解説すると、
ノート提出の爛（つまりSheet1のA2セル）に
何も入力がない（つまり未提出）場合はSheet1・B2セルをコピー(表示？)。
何か入力がある場合は、何もしない。

となります。

Sheet2の他のセルに同様に式を入れると

　　　　　Ａ　　　　　Ｂ　　　　
１　　学籍番号　氏　名
２　　　0001　　Ａさん　
３　　　　

となります。

これの欠点としては、Sheet1の並び通りにSheet2へ抜き出すので
隙間が開いてしまうことでしょうか・・・。
ただ、フィルタリングをかければ、解消できるかと思います。

回答No.1

SevenStar777

自動化で別シートへの抽出となるとマクロしか思いつかないんですが、
成績表の書式を以下のものと仮定して、
データが入力されてるシート名を"Sheet1"、
抽出先のシート名を"Sheet2"とします。

　　　　Ａ　　　　　Ｂ　　　　Ｃ
１　ノート提出　学籍番号　氏　名
２　　　未　　　　　0001　　Ａさん
３　　　済　　　　　0002　　Ｂさん

Sub Macro1()
Dim i as Integer
Dim RowNo1, RowNo2 as Integer
RowNo1 = 2: RowNo2 = 2
Do Until Sheets("Sheet1").Cells(RowNo1, 1).Value = ""
If Sheets("Sheet1").Cells(RowNo1, 1).Value = "未" Then
For i = 1 to 3
Sheets("Sheet2").Cells(RowNo, i).Value = Sheets("Sheet1").Cells(RowNo, i).Value
Next i
RowNo2 = RowNo2 + 1
End If
RowNo = RowNo + 1
Loop
End Sub

ご希望の回答になってますでしょうか？