オートフィルタの状況を判定する

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

大量のデータを絞り込むには、オートフィルタが便利です。ここでは、VBAからオートフィルタを使い倒すテクニックをご紹介します。なお、ボリュームがありますので、以下の項目にページを分けて解説します。なお、ここで解説するオートフィルタは、通常のワークシートに設定されているとします。テーブルでオートフィルタを操作するときは、また別の考え方が必要です。VBAでテーブルを扱う方法に関しては「VBAでテーブルの操作」をご覧ください。

  1. 書き方の基本
  2. 文字列で絞り込む
  3. 数値で絞り込む
  4. 日付で絞り込む
  5. 色で絞り込む
  6. 作業列で絞り込む
  7. 絞り込んだ結果をコピーする
  8. 絞り込んだ結果を集計する
  9. 絞り込んだ結果の行を操作する
  10. オートフィルタの状況を判定する (←このページ)
  11. 数式のエラーで絞り込む

オートフィルタが設定されているかどうか

表のタイトルに、オートフィルタ矢印ボタン(▼ボタン)が表示されているかどうかを判定するには、ワークシートのAutoFilterModeプロパティを調べます。オートフィルタが設定されていれば(▼ボタンが表示されていれば)、Trueを返します。設定されていないときはFalseです。なお、オートフィルタは、1つのワークシートに1つしか設定できません。

Sub Macro1()
    If ActiveSheet.AutoFilterMode = True Then
        MsgBox "設定されています"
    Else
        MsgBox "設定されていません"
    End If
End Sub

絞り込まれているかどうか

オートフィルタが設定されているとき、実際に何らかの条件を指定して絞り込まれているかどうかを判定するには、ワークシートのFilterModeプロパティを調べます。オートフィルタが設定されていて(▼ボタンが表示されていて)、なおかつ何らかの条件でどっかの列が絞り込まれているときは、FilterModeプロパティがTrueを返します。絞り込まれていないときはFalseを返します。

Sub Macro2()
    If ActiveSheet.FilterMode = True Then
        MsgBox "絞り込まれています"
    Else
        MsgBox "絞り込まれていません"
    End If
End Sub

そもそもオートフィルタが設定されていない(▼ボタンが表示されていない)とき、FilterModeプロパティはFalseを返します。

どの列が絞り込まれているか

オートフィルタ全体に対して、どの列に条件が指定されているか(絞り込まれているか)を一発で調べる方法はありません。調べるには、オートフィルタ内の各列をひとつずつ確認します。オートフィルタの各列はFilterオブジェクトで表されます。すべての列はFilersコレクションです。何らかの条件が指定されているFilterオブジェクトは、OnプロパティがTrueを返します。

下図は「1列目が"田中"である」という条件と「3列目が50より小さい」という条件で絞り込んでいます。

Sub Macro3()
    Dim i As Long, msg As String
    With ActiveSheet
        If .AutoFilterMode = True Then
            For i = 1 To .AutoFilter.Filters.Count
                If .AutoFilter.Filters(i).On = True Then
                    msg = msg & i & "列目" & vbCrLf
                End If
            Next i
        End If
    End With
    MsgBox msg
End Sub

上記のように、絞り込まれている列の位置を調べるのは簡単ですが、その列のタイトルを取得するのは面倒くさいです。たいていの場合は、何列目という位置が分かれば十分ですが、もしタイトルを調べたいなら、次のようにします。

Sub Macro4()
    Dim i As Long, msg As String
    With ActiveSheet
        If .AutoFilterMode = True Then
            For i = 1 To .AutoFilter.Filters.Count
                If .AutoFilter.Filters(i).On = True Then
                    msg = msg & .AutoFilter.Range(i) & vbCrLf
                End If
            Next i
        End If
    End With
    MsgBox msg
End Sub

何という条件で絞り込まれているか

設定されている条件を調べるのは大変です。なぜなら、オートフィルタにはさまざまな絞り込み方法が用意されているからです。単純に「"田中"と等しい」や「50より小さい」だけでなく、複数の条件をAndやOrで指定できたり、トップテンや色による絞り込みなども可能です。それらの、すべての判定方法を解説すると煩雑になるので、ここでは一般的によく設定される条件をいくつかご紹介します。

なお、設定されている条件を調べられないケースもあります。それは、本稿の最後に記載します。

1つの条件が設定されているとき

「1列目が"田中"と等しい」とか「3列目が50より小さい」など、1列に1つの条件が設定されているケースです。

そもそもオートフィルタを設定するときは、

のように指定します。引数Fieldは、条件を設定する列の位置です。そして、1つめの条件を引数Criteria1に指定します。実はFilterオブジェクトには、この引数Criteria1と同じ名称のCriteria1プロパティがあります。このプロパティを調べることで、引数Criteria1に指定した条件が分かります。下図は「1列目が"田中"と等しい」で絞り込んでいます。

Sub Macro4()
    MsgBox ActiveSheet.AutoFilter.Filters(1).Criteria1
End Sub

2つの条件が設定されているとき

下図は「3列目が"40より大きい"かつ"80より小さい"」で絞り込んでいます。

1つめの条件は、先のようにCriteria1プロパティで分かります。同様に、2つめの条件はCriteria2プロパティです。

Sub Macro5()
    With ActiveSheet.AutoFilter.Filters(3)
        MsgBox .Criteria1 & vbCrLf & .Criteria2
    End With
End Sub

さて問題は「かつ(AND)」です。ここまでの流れを理解された方なら、おそらくFilterオブジェクトには、引数Operatorと同じ名前のOperatorプロパティがあるのではないかと想像できるでしょう。その通りです。引数Operatorに指定した「かつ(AND)」や「または(OR)」などは、Operatorプロパティを調べれば分かります。ちょっと、やってみましょう。

Sub Macro6()
    With ActiveSheet.AutoFilter.Filters(3)
        MsgBox .Operator
    End With
End Sub

この「1」とは何でしょう。オートフィルタの設定をVBAでやるとき、たとえば今回のような「3列目が"40より大きい"かつ"80より小さい"」でしたら、次のように書きます。

Range("A1").AutoFilter 3, ">40", xlAnd, "<80"

このとき、引数Operatorに指定した定数xlAndの実体が「1」です。引数Operatorに指定できる定数は、次のとおりです。

定数 実体 意味
xlAnd 1 AND
xlOr 2 OR
xlTop10Items 3 トップテン
xlBottom10Items 4 トップテン
xlTop10Percent 5 トップテン
xlBottom10Percent 6 トップテン
xlFilterValues 7 値
xlFilterCellColor 8 セルの色
xlFilterFontColor 9 フォントの色
xlFilterIcon 10 アイコン
xlFilterDynamic 11 動的フィルタ
xlFilterNoFill 12 セルの色(なし)
xlFilterAutomaticFontColor 13 フォントの色(自動)
xlFilterNoIcon 14 アイコン(なし)

FilterオブジェクトのOperatorプロパティが返すのは、これら"実体"の数値です。つまり、Operatorプロパティで取得できる数値から、上記のいずれかを導かなければなりません。本項の冒頭で「設定されている条件を調べるのは大変です」と書いたのは、こういう理由です。ちなみに「1列目が"田中"と等しい」のように、1つの列に1つの条件しか指定していないとき、つまり引数Operatorを指定していないときは、Operatorプロパティが0を返します。

以上をふまえた上で、少しだけ実用的なコードをご紹介します。下図は「1列目が"田中"または"広瀬"である」と「3列目が"40より大きい"かつ"80より小さい"」で絞り込んでいます。

Sub Macro7()
    Range("A1").AutoFilter 1, "田中", xlOr, "広瀬"
    Range("A1").AutoFilter 3, ">40", xlAnd, "<80"
End Sub

これらの条件を判定するには、次のように面倒くさくなりますね。

Sub Macro9()
    Dim i As Long, msg As String
    With ActiveSheet
        If .AutoFilterMode = True Then
            With .AutoFilter
                For i = 1 To .Filters.Count
                    If .Filters(i).On = True Then
                        msg = msg & i & "列目：" & vbCrLf
                        Select Case .Filters(i).Operator
                        Case 0
                            msg = msg & .Filters(i).Criteria1 & vbCrLf
                        Case 1
                            msg = msg & .Filters(i).Criteria1 & " AND " & .Filters(i).Criteria2 & vbCrLf
                        Case 2
                            msg = msg & .Filters(i).Criteria1 & " OR " & .Filters(i).Criteria2 & vbCrLf
                        End Select
                    End If
                Next i
            End With
        End If
    End With
    MsgBox msg
End Sub

3つ以上の条件が設定されているとき

1つの列に、3つ以上の条件が設定されているケースです。下図は「1列目が"田中"または"広瀬"または"桜井"である」という条件で絞り込んでいます。

VBAでやるなら、次のような感じです。

Sub Macro10()
    Range("A1").AutoFilter 1, Array("田中", "広瀬", "桜井"), xlFilterValues
End Sub

このように、1つの列に3つ以上の条件を指定するときは、引数Criteria1に配列を指定します。すると、今まで条件を調べていたFilterオブジェクトのCriteria1プロパティも配列になります。

Sub Macro11()
    With ActiveSheet.AutoFilter.Filters(1)
        If IsArray(.Criteria1) = True Then
            MsgBox "配列です"
        Else
            MsgBox "配列ではありません"
        End If
    End With
End Sub

指定されている条件は、この配列(Criteria1プロパティ)の要素を調べます。

Sub Macro12()
    Dim C As Variant, msg As String
    With ActiveSheet.AutoFilter.Filters(1)
        If IsArray(.Criteria1) = True Then
            For Each C In .Criteria1
                msg = msg & C & vbCrLf
            Next C
        End If
    End With
    MsgBox msg
End Sub

色の条件が設定されているとき

下図のような表で

「1列目の塗りつぶし色が"赤"である」という条件で絞り込むには、次のようにします。

Sub Macro13()
    Range("A1").AutoFilter 1, RGB(255, 0, 0), xlFilterCellColor
End Sub

このように、オートフィルタの条件にセルの塗りつぶし色を指定したとき、FilterオブジェクトのCriteria1プロパティは、Interiorオブジェクトを返します。意味が分からない方は、とりあえず"そういうもんだ"と思ってください。

Sub Macro14()
    With ActiveSheet.AutoFilter.Filters(1)
        MsgBox TypeName(.Criteria1)
    End With
End Sub

なので、条件に指定した色は、InteriorオブジェクトのColorプロパティで分かります。

Sub Macro15()
    With ActiveSheet.AutoFilter.Filters(1)
        MsgBox .Criteria1.Color
    End With
End Sub

もっとも、今回は「混じりっけなし、純色の"赤"」を指定したので、赤を表すRGB値「255」が返りましたが、もっと変な色だったら変なRGB値が返ります。

「9359529」って言われてもねぇ～w せめてRがいくつ、Gがいくつ、Bがいくつという数値が知りたいところです。ちなみに、手動操作で調べるのでしたら簡単です。塗りつぶし色が設定されているセルを選択して、リボンの[塗りつぶしの色]ボタンから[その他の色]を選択します。

表示される[色の設定]ダイアログボックスにRGBの値が示されます。

まぁ、こんなことは、めったにやらないでしょうけど、このRGB値を計算によって求めるには次のようにします。詳しくは「色パレットに登録されている色を調べる」をご覧ください。

Sub Macro16()
    Dim R As Long, G As Long, B As Long
    With ActiveSheet.AutoFilter.Filters(1).Criteria1
        B = Int(.Color / 65536)
        G = Int((.Color - (B * 65536)) / 256)
        R = .Color - (G * 256) - (B * 65536)
    End With
    MsgBox "R:" & R & vbCrLf & _
           "G:" & G & vbCrLf & _
           "B:" & B
End Sub

条件を調べられないケース

これ、私が実際にハマったケースです。まずは、ここまでの「こうすれば調べられる」を検証します。なお、ここでは分かりやすく、オートフィルタで絞り込むコードもお見せしますが、手動操作で絞り込んだときも同様です。

・1つの条件が設定されているとき

Sub Macro()
    Range("A1").AutoFilter 2, "田中"
End Sub

上図は、オートフィルタの状況をローカルウィンドウで調べたところです。Criteria1プロパティに、指定した条件が設定されています。

・2つの条件が設定されているとき

Sub Macro()
    Range("A1").AutoFilter 2, "田中", xlOr, "広瀬"
End Sub

・3つ以上の条件が設定されているとき

Sub Macro()
    Range("A1").AutoFilter 2, Array("田中", "広瀬", "桜井"), xlFilterValues
End Sub

・色の条件が設定されているとき

Sub Macro()
    Range("A1").AutoFilter 3, RGB(255, 0, 0), xlFilterCellColor
End Sub

と、ここまでは上記で解説した検証です。問題は日付を絞り込んだときです。

日付を普通に絞り込んだときは、Criteria1プロパティやCriteria2プロパティを調べれば分かります。

Sub Macro()
    Range("A1").AutoFilter 1, "2019/5/3"
End Sub


Sub Macro()
    Range("A1").AutoFilter 1, ">2019/5/15", xlAnd, "<2019/6/15"
End Sub


Sub Macro()
    Range("A1").AutoFilter 1, Array("2019/6/1", "2019/5/3", "2019/6/13"), xlFilterValues
End Sub

また、日付を「今月」や「(年は問わず)6月」などのような条件で絞り込んだときも分かります。このやり方に関しては「日付で絞り込む」をご覧ください。

Sub Macro()
    Range("A1").AutoFilter 1, xlFilterAllDatesInPeriodJune, xlFilterDynamic
End Sub

Criteria1プロパティに設定されている「26」というのは、ここで指定した定数xlFilterAllDatesInPeriodJuneの実体数値です。さて、問題は日付を「3つ以上の条件で絞り込んだ」ときです。これは、たとえば

のように、任意の日付を選択したり、

みたく、"年単位"や"月単位"を条件に指定するやり方です。具体的な方法は「日付で絞り込む」をご覧ください。引数Criteria2には、次のように指定します。

Sub Macro()
    Range("A1").AutoFilter _
                Field:=1, _
                Operator:=xlFilterValues, _
                Criteria2:=Array(1, "2019/6/1")
End Sub

Criteria1プロパティとCriteria2プロパティはアクセスできません。条件(値)が設定されていないんです。でも、Operatorプロパティに引数xlFilterValuesが設定されていることから、実際にA列(1列目)が絞り込まれているのは間違いありません。もちろん、VBAでCriteria1プロパティやCriteria2プロパティを調べようとするとエラーになります。

これ、たぶんできないんだろうと思います。以下は、私の個人的な想像です。

そもそもオートフィルタという機能は、"セルに表示されている文字列"で絞り込むのが原則です。おそらく、最初にオートフィルタを実装するとき、そうした使い方を想定して設計したのだと思います。でも、日付や数値を"範囲"で絞り込むことも、実務では不可欠です。なので、Excelが内部で範囲を判断する「○○から××まで」という絞り込みも、機能として追加したのでしょう。さて、Excel 2007になってオートフィルタは劇的に進化しました。改良ポイントは次の4つです。

  1. 1つの列に3つ以上の条件を指定できるようになった
  2. 色を条件として指定できるようになった
  3. 日付に「今月」や「(年は問わず)6月」などを指定できるようになった
  4. 日付の条件に"年単位"や"月単位"を指定できるようになった

1.は「AまたはBまたはC」のような条件ですが、いずれにしても"と等しい"を判定しているだけなので、従来オートフィルタが持っていた機能で応用できそうです。このとき"より大きい"などは指定できません。ドンピシャ、イコールだけです。2.の色も、指定したRGB値に対して"と等しい"を判定しているだけですし、3.も要するに「"今月"とはいつからいつまでか」の範囲をExcelが内部で計算して、それを指定しているだけです。いずれも、従来の機能を拡張すれば実現できそうです。「(年は問わず)6月」などを条件に指定すると、非連続の日付で絞り込みができますが、これも、オートフィルタの対象範囲をインメモリデータベースとして構築するときに、年月のフィールドを作ってやれば"と等しい"で判定できます。問題は4.です。Excel 2007のオートフィルタからは、日付が自動的にグループ化されます。その結果、次のような条件が可能になります。

Sub Macro()
    Range("A1").AutoFilter _
                Field:=1, _
                Operator:=xlFilterValues, _
                Criteria2:=Array(0, "2018/1/1", 1, "2019/5/1", 2, "2019/6/20")
End Sub

これは、日付が「2018年である または 2019年5月である または 2019年6月20日である」という条件です。すげぇ複雑です。今までのオートフィルタは基本的に「と等しい」か「○○から××まで」という判定しかできませんでしたが、その域をはるかに超えています。でも「日付がグループ化される」＋「日付を個別に指定できる」という機能を実現するのなら、必然的にこうした超複雑な条件にも対応できなければなりません。そこで、おそらくですけど、従来のオートフィルタとは別に、もうひとつ別の仕組みを実装したのではないかと思います。指定された複雑な条件に一致するデータを特定するための、専用の仕組みを。そもそも

Criteria2:=Array(0, "2018/1/1", 1, "2019/5/1", 2, "2019/6/20")

という書き方を知ったとき、ものすごく違和感がありました。なぜ、こんな変な指定をさせるのだろう？なぜ、Criteria1を使わないんだろう？って。3つ以上の条件で絞り込むとき、それぞれの条件を配列形式で指定します。でも、これだってCriteria1です。Criteria1を使わない、しかも配列で指定させる、というあたりで、今までのオートフィルタではない別の何かを感じます。ちなみに「Criteria2:=Array(0, "2018/1/1", 1, "2019/5/1", 2, "2019/6/20")」の書き方で、0が年、1が月…などというルールは、Microsoftのヘルプに公開されていません。いや、探せばどこかにあるのかもしれませんけど、少なくとも通常のAutoFiltrerメソッドには解説されていません。その後のExcel 2016で追加された引数SubFieldなんか、ご丁寧にサンプルまで掲載されているというのに。

ちょっと話が脱線しましたが、要するに「Criteria2:=Array(0, "2018/1/1", 1, "2019/5/1", 2, "2019/6/20")」方式で絞り込むときは、従来のAutoFilterオブジェクトだけでなく、何か別の仕組みを使っているような気がするので、Criteria1プロパティなどを調べても、条件が取得できないのではないかと。そう感じています。もし、調べる方法を見つけたら、またここに追記します。