【ExcelVBA】オートフィルターでデータを抽出する

オートフィルターで一番基本となる「データの抽出」を、コピペして使えるユーティリティ関数にしたものを紹介します。

オートフィルターはとても便利な機能で、しかも高速なので、使いこなせば読みやすくて速いマクロを作ることができます。

しかし、VBAで使おうとすると、意外と落とし穴の多い機能であることがわかります。
それに毎回気を配るのは大変なので、汎用関数を作って、メインコードをスッキリさせましょう。

お忙しい方は、ソースコードをコピーして、使い方のように呼び出すだけでOKです。
コードをしっかり読みたい方は、解説をどうぞ。

ソースコード
使い方
コードの解説

ソースコード

' フィルター設置
Sub フィルターを設置する(設置エリア As Range)

    ' 既に設置されているフィルターを解除してから設置
    設置エリア.Parent.AutoFilterMode = False
    設置エリア.AutoFilter
    
End Sub

' フィルター抽出
Sub フィルターで抽出する(ByVal 抽出キー As Variant, 抽出列 As Long, 対象シート As Worksheet)
    With 対象シート.AutoFilter.Range
    
        ' 数値が書式の影響を受けるのを回避
        If WorksheetFunction.IsNumber(抽出キー) Then
            .AutoFilter field:=抽出列 - .Column + 1, Criteria1:=">=" & 抽出キー _
                                         , Operator:=xlAnd, Criteria2:="<=" & 抽出キー
        ' メインの処理
        Else
            .AutoFilter field:=抽出列 - .Column + 1, Criteria1:=抽出キー
        End If
    
    End With
End Sub

使い方

オートフィルターのサンプル表

' 絞り込むマクロ
Sub みかんを2個以上買った人に絞る()

    Call フィルターを設置する(Range("B2:E2"))
    Call フィルターで抽出する("みかん", くだもの売上表の列.品物, ActiveSheet)
    Call フィルターで抽出する(">=2", くだもの売上表の列.個数, ActiveSheet)

End Sub

まずは設置する関数を呼び、そのあとで抽出する関数を呼んでいく。
すでにフィルターが設置してある場合は、設置の関数は使わなくてよい。
抽出する列の指定に、列番号をそのまま渡せる。
※ 元の仕様である「フィルター範囲で何列目」から変更している
条件で抽出するときもコードが同じなので、「みかん」と「2個以上」は↑のように同じ関数で処理できる。

※ 「くだもの売上表の列」は列番号を定数にしたものです。(B列なら2などの数値です)
↑のサンプルは2,3と列番号を直接入れても動きますが、定数にすることで読みやすく、変更に強くなります。詳しくはこちらへ：定数を使って、列の挿入などの仕様変更に強いマクロを作る

コードの解説

オートフィルターはすでにかかっているものを最優先してしまう

オートフィルターを扱うとき、とにかく厄介なのが実行前の状態で処理が変わってしまうことです。

例えば「.AutoFilter」という一番簡単なメソッドでさえ、「オートフィルターのON/OFF切り替え」なので、今がONなのかOFFなのかで挙動が変わってしまいます。

その仕様の中でもっとも深刻なのが、

Range("A1:C10").AutoFilter field:=1 , Criteria1:="みかん"

という、よく見る基本のサンプルコードに潜む罠。

これが見たまま「1」番目の列である「A列」を、「みかん」で抽出していればいいのですが、残念ながら違います。

このシートの右側に別の表があり、そこにオートフィルターがすでにかかっているとします。
その状態で↑のコードを実行すると、なんと既にあるオートフィルターの1番目をみかんで抽出してしまいます。

' G1:H10に別の表があり、そこにオートフィルターがかかっているとする
Range("A1:C10").AutoFilter 1 , "みかん" ' ← ！！！G列を絞るコード！！！
Range("A1:C10").AutoFilter                 ' ← ここでG列のフィルターが外れる
Range("A1:C10").AutoFilter 1 , "みかん" ' ← A列を絞るコード

これをみると、問題の深刻さが伝わると思います。

こうならないために、ユーティリティ関数は「設置用」と「抽出用」を分けています。

抽出の関数ではフィルター範囲を指定せず、「対象シート.AutoFilter.Range」を使って、シート上に設置されているオートフィルターのセル範囲を使用しています。

まずは意図する場所にオートフィルターを設置し、その後に抽出を行うことで、いつも同じ動きをしてくれる、安心安全なマクロになります。

Call フィルターを設置する(Range("A1:C10"))
Call フィルターで抽出する("みかん", CNoくだもの売上表.品物, ActiveSheet)
Call フィルターで抽出する(">=2", CNoくだもの売上表.個数, ActiveSheet)

ついでに、抽出する関数に範囲を指定する必要がなくなっているので、
Range("A1:C10")が連発されず、コードが見やすくなっていますね。

オートフィルターは値ではなくセルの表示テキストで抽出する

意外と知られていませんが、オートフィルターは値で抽出するのではなく、セルの表示テキストで抽出しています。

E列の「売上」を「1200」で抽出するとしましょう。

Range("B2:E8").AutoFilter 4 , 1200

このコードでやろうとしても、E列の表示形式が「\1,200」や、「1200 円」になっていると、「1200」では抽出されなくなります。
※ 逆に「\」や「円」での部分一致には引っかかるようになります。

数値の完全一致フィルター失敗例

よって、「数値の完全一致を抽出する場合は、別の方法で抽出する」必要があります。

それが↓この部分ですが、

' 数値が書式の影響を受けるのを回避
If WorksheetFunction.IsNumber(抽出キー) Then
    .AutoFilter field:=抽出列 - .Column + 1, Criteria1:=">=" & 抽出キー _
                                 , Operator:=xlAnd, Criteria2:="<=" & 抽出キー

意味は単純で、「1200以上」かつ「1200以下」として抽出しています。
1200より大きくて1200より小さい数字は、1200だけですね。

大小判定の条件式は、表示テキストではなく値を見てくれますので、それを利用しているわけです。

まあ書式のあるような数値で完全一致ってめったにやりませんが、だからこそ発生したときには、原因が特定できずに困り果てるかもしれません。

かといって、こういうレアケースのためにメインマクロにIf文を5行くらい書くと、どんどんコードが汚く見づらくなっていきます。

こういうものこそ、関数に隠しちゃいましょう。
臭いものには蓋です。

Call フィルターで抽出する(1200, くだもの売上表の列.売上, ActiveSheet)

裏であーだこーだ面倒なことをしても、メインマクロはたった1行、奇麗なままです。

※ もう一つの方法として、「オートフィルターが表示テキストで絞る」なら、「抽出キーも表示形式に合わせてテキスト化」しようという方法もあります。こっちの方が理屈はわかりやすいですね。しかし「列の表示形式が統一されていないと使えない」という弱点があるため、少しトリッキーですが、↑の以上And以下法を採用しています。

AutoFilterの引数fieldは、列番号をそのまま渡せない

もう一つ地味に厄介な仕様が、AutoFilterの引数「field」には、列番号ではなくオートフィルターの範囲で何列目なのかを指定しなければいけない点です。

さっきE列の売上を絞ったときの、

Range("B2:E8").AutoFilter 4 , 1200

この「4」ですね。「5」列目ですが、B,C,D,E で「4」です。

E列とわかっているのに、どこからフィルターが始まっているか気にしなければいけないのは面倒です。

なので、関数には列番号を渡し、関数の中で「列番号 - フィルター第1列 + 1」と、フィルタエリアで何列目かに変換してあげることで、表向きの引数は列番号で指定できるようにしています。

定数と相性が良くなるので、使用例のようにコードがとてもスッキリします。

前セクションの「余計なIf文を隠す」に続き、こういった細かい計算を関数の中に閉じ込めて、メインコードを読みやすく保てることも、関数化の強みです。

オートフィルターに限らず、「何番目？」や「何行？」を計算するときは、
「おわり－はじめ＋１」が良く出てきますが、
この「１」がすごい鬱陶しいと思ったことありませんか？

こういうのを無くしたいときは、短い関数でも躊躇せずに、積極的に関数にしていきましょう。

発生するとありがたいエラーはちゃんと発生させる

抽出の関数は、対象のワークシートの「AutoFilter.Range」を速攻でWithしているので、対象のシートにオートフィルターが設置されていないとエラーが出て止まってしまいます。

実はこれ、狙ってそうしています。

プログラムのバグというのは、

たまにエラーで止まるが、エラーなく終了できたときは正しい結果になる。
エラーは出ずに常に終了するが、たまに間違った結果になる。

大きくこの2種類に分けられます。
どっちが嫌なバグですか？

考えるまでもないですね。２が嫌です。

１はエラーとその場で格闘すれば済みますが、２はもはや地雷です。
もし今回踏まなかったら、むしろそっちの方が怖いです。

おかしいときはおかしいと、そのときその場所で言ってくれた方がありがたいのです。

今回の題材であるオートフィルターは、Excelの動きや、ユーザーの操作で、意図しない位置にかかってしまうことが多い機能です。

↓こんなのよく見ますよね。
オートフィルターの設置ミス1
オートフィルターの設置ミス2

「Range("B2:E8").AutoFilter 1 , "みかん"」という書き方は、
「すでにフィルターがあればそれを使う、なければB2:E8に設置して使う」
という、とてもあいまいな指示です。

↑のどっちの画像でも、この書き方では正しく動きません。
（１つめは謎のA列にみかんはなく、すべて非表示になる。２つめは見出しも消える）
しかしエラーが出てくれないので、この後の処理が実行されてしまいます。

もしこのあと、「非表示の行を削除するマクロ」が続いていたら、見事に地雷が炸裂することでしょう。

エラーというのは、いわばプログラムからのホウレンソウです。

エラーが出るのは嫌なもので、できれば見たくないと思ってしまいますが、
「俺によくない知らせを持ってくるな」なんてダメな上司にはならず、
「いつでもすぐに報告してくれ」という広い心でプログラミングをしていきましょう。

和風スパゲティのレシピ

日本語でコーディングするExcelVBA

オートフィルターでデータを抽出する