Office TANAKA - Excel VBA Tips[オートフィルタを使い倒す]

大量のデータを絞り込むには、オートフィルタが便利です。ここでは、VBAからオートフィルタを使い倒すテクニックをご紹介します。なお、ボリュームがありますので、以下の項目にページを分けて解説します。

オートフィルタを設定する

まずは簡単に、次のようなデータを例にします。

上図のように、A列を"田中"で絞り込む操作をマクロ記録すると、次のようなコードが記録されます。

Sub Macro1()
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$9").AutoFilter Field:=1, Criteria1:="田中"
End Sub

先頭の「Selection.AutoFilter」は、オートフィルタ矢印を表示しろという命令です。AutoFilterメソッドは、このように引数を指定しないで実行すると、オートフィルタ矢印のオン/オフを切り替えます。オートフィルタ矢印が表示されていなくても、次のAutoFilterメソッドで、いきなり絞り込みが可能ですから、最初にオートフィルタ矢印を表示する必要はありません。マクロ記録はあくまで、手動で行った操作を忠実に記録しただけです。

ActiveSheet.Range("$A$1:$B$9").AutoFilter Field:=1, Criteria1:="田中"

アクティブシートの表を対象にするのでしたら、「ActiveSheet」は省略可能です。
「Range("$A$1:$B$9")」は、表全体を表しています。ここを動的にしたいのでしたら、次のようにするといいでしょう。

Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="田中"

あるいは、オートフィルタを設定したい表内の、どれか1つのセルを指定すれば、Excelが自動的に表の大きさを認識してくれますので、次のように書いてもOKです。

Range("A1").AutoFilter Field:=1, Criteria1:="田中"

「Field:=1, Criteria1:="田中"」は、左から数えて1列目を、"田中"と等しいという条件で絞り込みなさいという意味です。引数Fieldと、引数Criteria1は、それぞれ1番目の引数と2番目の引数ですから、これは名前付き引数名を省略して、次のように書けます。なぜ引き数名を省略できるのかは、下記のページをご覧ください。

引数(オプション)名って書かなくていいの？

Range("A1").AutoFilter 1, "田中"

まぁ、オートフィルタをかけるだけなら、ここまでシンプルに書けますよ、というひとつの例です。必要であれば、可読性を高める工夫をしてください。

複数条件で絞り込む

1つのAutoFilterメソッドは、1つの列を絞り込むことしかできません。複数の列を絞り込むには、必要なだけAutoFilterメソッドを実行します。下記のコードは、A列を"田中"で絞り込み、B列を"40より大きい"で絞り込みます。

Sub Sample()
    Range("A1").AutoFilter Field:=1, Criteria1:="田中"
    Range("A1").AutoFilter Field:=2, Criteria1:=">40"
End Sub

1つの列を、複数の条件で絞り込むには、引数Operatorに、次の定数を指定します。

定数	値	意味
xlAnd	1	And(かつ)
xlOr	2	Or(または)

そして、2つの条件を、引数Criteria1と、引数Criteria2に指定します。

もちろん、And(かつ)も同じです。あまり良い例ではありませんが、たとえば次のような感じです。

このように、Excel 2003までは、1つの列を最大で2つの条件で絞り込むことしかできませんでした。それが、Excel 2007から、1つの列を3つ以上の条件で絞り込むことが可能になりました。下図のデーで、A列を"田中","鈴木","土屋"で絞り込んでみましょう。

この操作をマクロ記録すると、次のようなコードが記録されます。

Sub Macro2()
    ActiveSheet.Range("$A$1:$B$9").AutoFilter Field:=1, Criteria1:=Array("田中", _
        "土屋", "鈴木"), Operator:=xlFilterValues
End Sub

見にくいので、ちょっと整理します。

Sub Macro2()
    Range("A1").AutoFilter Field:=1, _
                           Criteria1:=Array("田中", "土屋", "鈴木"), _
                           Operator:=xlFilterValues
End Sub

「Field:=1」が「1列目」を表すのはいいですね。1つの列を3つ以上の条件で絞り込むには、上のように、引数Criteria1に、絞り込みたい条件を配列形式で指定し、さらに、引数Operatorに定数xlFilterValuesを指定します。この定数xlFilterValuesはExcel 2007で追加された定数ですので、Excel 2003以前で使用するとエラーになります。

引数Criteria1には、絞り込み条件を配列で指定します。配列あれば、別にArray関数でなくてもかまいません。たとえば、次のようにしても同じ結果になります。

Sub Macro2()
    Dim Target(2) As String
    Target(0) = "田中"
    Target(1) = "土屋"
    Target(2) = "鈴木"
    Range("A1").AutoFilter Field:=1, _
                           Criteria1:=Target, _
                           Operator:=xlFilterValues
End Sub

では、1つの列を最大で2つの条件でしか絞り込めないExcel 2003で、1つの列を3つ以上の条件で絞り込むには、どうしたらいいでしょう。オートフィルタの機能では、3つ以上の条件を指定できませんので、これはもう、何とか工夫するしかありません。たとえば、次のような考え方です。

Sub Sample()
    Dim i As Long
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 1) = "田中" Or _
           Cells(i, 1) = "土屋" Or _
           Cells(i, 1) = "鈴木" Then
           Cells(i, 3) = "○"
        End If
    Next i
    Range("A1").AutoFilter Field:=3, Criteria1:="○"
End Sub

3つの条件に一致する行は、作業列(ここではC列)に"○"を代入します。オートフィルタでは、このC列が"○"であるという条件で絞り込みます。絞り込んだ結果を何かに使うなど、C列がジャマになるときは、絞り込んだ結果を一度別のワークシートにコピーしてから、C列だけを削除するといいでしょう。いずれにしても、何とか工夫するしか手はないです。

いろいろな条件指定

マクロ記録で調べれば分かるのですが、いくつかご紹介します。

・"田中"と等しい

Criteria1:="田中"

・"田中"ではない

Criteria1:="<>田中"

・部分一致

Criteria1:="東京*"	''東京で始まる
Criteria1:="*横浜*"	''横浜を含む
Criteria1:="*区"	''区で終わる

・空欄である

Criteria1:=""
''または
Criteria1:="="

・空欄ではない

Criteria1:="<>"

・数値の比較

Criteria1:="100"	''100と等しい
Criteria1:=">100"	''100より大きい
Criteria1:="<=100"	''100以下

「以上」や「以下」を指定するときは、不等号を左にします。イコールを左にすると、正しい絞り込み結果になりません。

正 >=(以上)、<=(以下)
誤 =>(以上)、=<(以下)

・日付の絞り込み

Criteria1:=">2011/3/31"

日付も、基本的には数値と同じように、比較演算子で比較できます。上記の「Criteria1:=">2011/3/31"」は、「3月31日より大きい」つまり「3月31日より後の日付」という意味です。
Excelは、日付をシリアル値という特別な数値で管理しています。2011年3月31日を表すシリアル値は 40633 です。したがって、こういうやり方は好ましくありませんが、次のようにしても「3月31日より後の日付」で絞り込むことも可能です。

Criteria1:=">40633"

このように、日付を絞り込みの条件に指定するときは、Excelが、シリアル値と認識してくれる形式で、日付を指定します。もちろん、先の「2011/3/31」はシリアル値と認識してくれます。
Excelが、それをシリアル値と認識してくれるかどうかは、セルに入力してみると分かります。たとえば、セルに「3月1日」と入力すると、このデータはシリアル値として認識されます。

しかし「3月1日(火)」のように曜日まで入力すると、Excelはこれを、シリアル値とは認識できず、単なる文字列と認識されてしまいます。

これは、オートフィルタの条件に指定するときも同じです。

Criteria1:=">3月31日"

は、正しく絞り込みができますが、

Criteria1:=">3月31日(木)"

では、望む結果になりません。
もちろん、この条件には、シリアル値を返す関数などを使うことも可能です。

Criteria1:=">" & DateSerial(2011, 3, 31)

【重要】
と、ここまでは、オートフィルタで日付を絞り込む基本的なことを解説しましたが、実はこれが一筋縄ではいきません。Excelのバージョンや、セルに設定している表示形式によって、正しく絞り込めるかどうかが変わってきます。そのへんの話は、とても長くなるので、下記のページをご覧ください。

オートフィルタで日付を絞り込む

上記ページでは、Excel 2007以降で強化された、日付に関する絞り込みも詳解しています。

オートフィルタの結果を集計する >> 次ページ