Excelのオートフィルターを文字列、数値、日付の複数条件で使う：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、AutoFilterメソッドを使って文字列、数値、日付で絞り込む方法や、複数条件を指定する際に使う、Operatorに指定する定数などを解説します。

» 2014年07月15日 18時00分公開

[薬師寺国安，PROJECT KySS]

連載目次

※本Tipsの環境：Windows 8.1 Enterprise（64ビット）＋Excel 2013

　今回も、「Excelのオートフィルターでデータ抽出条件を設定／解除」「Excelのオートフィルターで絞り込んだデータの件数と合計を関数で求める」に引き続きオートフィルターのTipsを紹介する。

　「文字列での絞り込み抽出」は「あいまい検索」だと思っていい。また「数値での絞り込み抽出」では「金額」の範囲を指定しての抽出が可能だ。「日付での絞り込み抽出」も範囲を指定しての抽出が可能だ。これらのTipsはどれも、実際の業務に即！役立つはずだ。ぜひ各自がアレンジして、現場で取り入れて使ってもらいたい。

文字列での絞り込み抽出

　今回も、冒頭で紹介したTips同様「入力画面」と「抽出画面」というシートを用意しておく。

　「入力画面」シートには「氏名」を入力するセルを用意し、Excelメニューの［挿入］→［図形］を選択して「角丸四角形」を配置する。テキストには「実行」としておく（図1）。

図1　「氏名」を入力するセルと「実行」ボタンを配置した

　「抽出画面」には「都道府県」「氏名」「金額」「購入日」のデータが表示され、下の方に「オートフィルターの解除」ボタンを配置している（図2）。

　前回でも書いておいたが、シートのデザインは各自が自由に行っても問題はないが、セルの位置が異なると、コードの変更が必要になるので気を付けてほしい。

図2　「都道府県」「氏名」「金額」「購入日」のデータが表示さている。下の方には「オートフィルターの解除」ボタンを配置している

　文字列での絞り込み抽出を行うには、AutoFilterメソッドに引数を指定する。

AutoFilterメソッドの書式

｛オブジェクト｝.AutoFilter Field:=｛列番号｝,Criteria1:=｛表1の形式｝

　｛オブジェクト｝には、Rangeオブジェクトを指定し、｛列番号｝には、フィルターの対象となるフィールド番号を指定する。「Criteria1」には、表1のような抽出条件となる文字列を指定する。

表1　文字列の指定方法
指定方法	説明
山田*	「山田」で始まる
*山田	「山田」で終わる
山田	「山田」を含む

　VBE（Visual Basic Editor）を起動してModule1内にリスト1のコードを記述する。

Option Explicit
Sub 文字列での絞り込み抽出()
  If Range("C2").Value = "" Then
    MsgBox "氏名を入力してください(あいまい検索)。"
    Exit Sub
  Else
    Worksheets("抽出画面").Range("A1").AutoFilter Field:=2, Criteria1:="*" & Worksheets("入力画面").Range("C2").Value & "*"
    Worksheets("抽出画面").Select
  End If
End Sub

リスト1　文字列での絞り込み抽出コード

　3行目のように氏名を入力する「入力画面」シートの「C2」のセルに何も入力されていない場合は、警告メッセージを表示して処理を抜ける。それ以外の場合は7～8行目の処理を行う。

　オートフィルターの書式にのっとって、「入力画面」シートの「A1（見出し）」にオートフィルターを適用する。条件としては7行目のように「Field」に「氏名」に該当する列「2（B列）」を指定し、「Criteria1」には表1の「*山田*」の形式で、セル「C2」に入力された「氏名」を含む氏名を抽出する。これで、「あいまい検索」になる。

　8行目では、「抽出画面」シートを選択して表示している。

　このマクロを「入力画面」シートの「実行」ボタンに関連付け、実行すると図3のようになる。

図3　「氏名」に「薬師寺」を指定して、「薬師寺」の含まれる「氏名」が全て抽出表示された

　図3の「オートフィルターの解除」には、リスト2のマクロを関連付ける。

Sub オートフィルターの解除()
  Range("A1").AutoFilter
  Worksheets("入力画面").Range("C2").Value = ""
End Sub

リスト2　オートフィルターを解除するコード

　オートフィルターを解除し、「入力画面シート」の「C2」セルを空にする。

数値での絞り込み抽出

　今度は、「金額」の下限と上限を設定し、範囲を絞って抽出してみよう。

　数値での絞り込み抽出を行うには、AutoFilterメソッドに以下のように引数を指定する。

AutoFilterメソッドの書式

｛オブジェクト｝.AutoFilter Field:=｛列番号｝,Criteria1:=｛抽出条件1｝,Operator1:=｛Operator｝,Criteria2:=｛抽出条件2｝)

　｛オブジェクト｝には、Rangeオブジェクトを指定し、｛列番号｝には、フィルターの対象となるフィールド番号を指定する。｛抽出条件1｝｛抽出条件2｝には、表2のように比較演算子を使用する。｛Operator｝は表3のように、｛抽出条件1｝｛抽出条件2｝に関する論理演算子などを指定する。

表2　条件の指定の仕方
指定の仕方	説明
Criteria1:="1000"	1000と等しい
Criteria1:=">1000"	1000より大きい
Criteria1:=">=1000"	1000以上
Criteria1：="<1000"	1000より小さい
Criteria1:="<=1000"	1000以下

表3　｛Operator｝に指定する定数（一部）
定数	説明
xlAnd	｛抽出条件1｝と｛抽出条件2｝の論理演算子「AND」
xlBottom10Items	下から数えて○番目（｛抽出条件1｝で指定される項目数）まで表示
xlBottom10Percent	下から数えて○％（｛抽出条件1｝で指定される割合）まで表示
xlFilterCellColor	セルの色
xlFilterDynamic	動的フィルター
xlFilterFontColor	フォントの色
xlFilterIcon	フィルターアイコン
xlFilterValues	フィルターの値
xlOr	｛抽出条件1｝または｛抽出条件2｝の論理演算子「OR」
xlTop10Items	上から数えて○番目（｛抽出条件1｝で指定される項目数）まで表示
xlTop10Percent	上から数えて○％（｛抽出条件1｝で指定される割合）まで表示
参考「XlAutoFilterOperator 列挙型 - MSDN」

　「入力画面」シートには、図4のように「金額」を入力するセルを用意しておく。「金額1」と「金額2」に入力された値で抽出を行う。AND検索になる。

図4　「金額」を入力するセルを用意する

　図4に入力した値で抽出するコードはリスト3になる。

Sub 指定した範囲内の金額のデータを抽出()
  If Range("C4").Value = "" And Range("C6").Value = "" Then
    MsgBox "金額が未入力です。"
    Exit Sub
  Else
    If IsNumeric(Range("C4").Value) = False And IsNumeric(Range("C6").Value) = False Then
      MsgBox "金額が不正です。"
      Exit Sub
    Else
      Worksheets("抽出画面").Range("A1").AutoFilter Field:=3, Criteria1:=">" & Worksheets("入力画面").Range("C4").Value, Operator:=xlAnd, Criteria2:="<" & Worksheets("入力画面").Range("C6").Value
      Worksheets("抽出画面").Select
    End If
  End If
End Sub

リスト3　指定した範囲内の「金額」のデータを抽出するコード

　2行目のように「金額」を入力するセルが空の場合は警告メッセージを出して処理を抜ける。それ以外の場合は6～12行目の処理を行う。

　数値かどうかの判定は6行目のようにIsNumeric関数で行っている。IsNumeric関数では、数値に変換できる値の場合はTrueを返し、それ以外はFalseを返す関数だ。

　10行目のように「抽出画面」シートの「A1（見出し）」に対してオートフィルターを適用する。フィルター条件として、「Field」に「金額」を表示している「C列」、すなわち列番号の「3」を指定する。最初の抽出条件を指定する「Criteria1」には「入力画面シートのセルC4に入力した値より大きく」を指定し、Operatorには表3の「xlAnd」の論理演算子「AND」を指定し、次の抽出条件である「Criteria2」には、「入力画面シートのセルC6に入力された値より小さい」という条件を指定する。

　つまり「C4」の値より大きく「C6」の値より小さい範囲の金額が抽出表示される。

　11行目では最後に「抽出画面」を選択し、抽出結果を表示する。実行結果は図5のようになる。

図5　「金額」が「200000」より大きく「350000」より小さいデータを抽出した

　図5の「オートフィルターの解除」ボタンには、リスト3のコードを関連付ける。

Sub オートフィルターの解除2()
  Range("A1").AutoFilter
  Worksheets("入力画面").Range("C4").Value = ""
  Worksheets("入力画面").Range("C6").Value = ""
End Sub

リスト3　オートフィルターを解除するコード

　オートフィルターを解除し、「入力画面シート」の「金額1」と「金額2」を入力するセルを空にする。

日付での絞り込み抽出

　ここでは、「購入日」の検索開始日と終了日を指定して、その範囲内のデータを抽出する方法を紹介する。日付での絞り込み抽出を行うには、「数値での絞り込み抽出」と同じ書式を用いる。

　「入力画面」シートに図6のように「購入日」を入力するセルを用意しておく。「購入日1」と「購入日2」に入力された値で抽出を行う。AND検索になる。

図6　「日付」を入力するセルを用意する

　図6に入力した値で抽出するコードはリスト4になる。

Sub 日付による絞り込み()
  If Range("C8").Value = "" And Range("C10").Value = "" Then
    MsgBox "日付が未入力です。"
    Exit Sub
  Else
   Worksheets("抽出画面").Range("A1").AutoFilter Field:=4, Criteria1:=">=" & Worksheets("入力画面").Range("C8").Value, Operator:=xlAnd, Criteria2:="<=" & Worksheets("入力画面").Range("C10").Value
   Worksheets("抽出画面").Select
  End If
End Sub

リスト4　指定した範囲内の「購入日」のデータを抽出するコード

　2行目のように「購入日」を入力するセルが空の場合は警告メッセージを出して処理を抜ける。それ以外の場合は6～7行目の処理を行う。

　6行目では、「抽出画面」シートの「A1（見出し）」に対してオートフィルターを提供する。フィルター条件として、「Field」に「購入日」を表示している「D列」、すなわち列番号の「4」を指定する。最初の抽出条件を指定する「Criteria1」には「入力画面シートのセルC8に入力した値に等しくそれより大きく」を指定し、Operatorには表1の「xlAnd」の論理演算子「AND」を指定し、次の抽出条件である「Criteria2」には、「入力画面シートのセルC10に入力された値に等しくそれより小さい」という条件を指定する。

　つまり「C8」の「日付」から「C10」の範囲の日付が抽出表示される。

　7行目では最後に「抽出画面」を選択し、抽出結果を表示する。

　リスト4のマクロを「購入日」の「実行」ボタンに関連付け、実行した結果は図7のようになる。

図7　「購入日」が「2013/5/1」から「2014/2/28」までのデータを抽出した

　図7の「オートフィルターの解除」ボタンには、リスト5のコードを関連付ける。

Sub オートフィルターの解除3()
  Range("A1").AutoFilter
  Worksheets("入力画面").Range("C8").Value = ""
  Worksheets("入力画面").Range("C10").Value = ""
End Sub

リスト5　オートフィルターを解除するコード

　オートフィルターを解除し、「入力画面シート」の「購入日1」と「購入日2」を入力するセルを空にする。

3つのTipsを使う際の注意点

　ここで注意していただきたいことがある。

　それは、解説の都合上、今回は1つの「オートフィルターの解除」ボタンに、その都度異なったマクロを関連付けしているため、別のマクロを実行すると、「入力画面」シートの入力セルは空にならないということである。

　今回紹介したTipsは3個あり、3個の「オートフィルターの解除」マクロを作成している。本来なら「文字列オートフィルターの解除」「金額オートフィルターの解除」「日付オートフィルターの解除」という3つのボタンを配置し、それぞれに該当するマクロを関連付けする必要がある。しかし、今回はそれを行っていないので、ご了承願いたい。各自が試す場合は個別のボタンを作成して試してみるといいだろう。