業務効率化に役立つVBA/マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、AutoFilterメソッドを使って文字列、数値、日付で絞り込む方法や、複数条件を指定する際に使う、Operatorに指定する定数などを解説します。
今回も、「Excelのオートフィルターでデータ抽出条件を設定/解除」「Excelのオートフィルターで絞り込んだデータの件数と合計を関数で求める」に引き続きオートフィルターのTipsを紹介する。
「文字列での絞り込み抽出」は「あいまい検索」だと思っていい。また「数値での絞り込み抽出」では「金額」の範囲を指定しての抽出が可能だ。「日付での絞り込み抽出」も範囲を指定しての抽出が可能だ。これらのTipsはどれも、実際の業務に即!役立つはずだ。ぜひ各自がアレンジして、現場で取り入れて使ってもらいたい。
今回も、冒頭で紹介したTips同様「入力画面」と「抽出画面」というシートを用意しておく。
「入力画面」シートには「氏名」を入力するセルを用意し、Excelメニューの[挿入]→[図形]を選択して「角丸四角形」を配置する。テキストには「実行」としておく(図1)。
「抽出画面」には「都道府県」「氏名」「金額」「購入日」のデータが表示され、下の方に「オートフィルターの解除」ボタンを配置している(図2)。
前回でも書いておいたが、シートのデザインは各自が自由に行っても問題はないが、セルの位置が異なると、コードの変更が必要になるので気を付けてほしい。
文字列での絞り込み抽出を行うには、AutoFilterメソッドに引数を指定する。
{オブジェクト}.AutoFilter Field:={列番号},Criteria1:={表1の形式}
{オブジェクト}には、Rangeオブジェクトを指定し、{列番号}には、フィルターの対象となるフィールド番号を指定する。「Criteria1」には、表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
3行目のように氏名を入力する「入力画面」シートの「C2」のセルに何も入力されていない場合は、警告メッセージを表示して処理を抜ける。それ以外の場合は7〜8行目の処理を行う。
オートフィルターの書式にのっとって、「入力画面」シートの「A1(見出し)」にオートフィルターを適用する。条件としては7行目のように「Field」に「氏名」に該当する列「2(B列)」を指定し、「Criteria1」には表1の「*山田*」の形式で、セル「C2」に入力された「氏名」を含む氏名を抽出する。これで、「あいまい検索」になる。
8行目では、「抽出画面」シートを選択して表示している。
このマクロを「入力画面」シートの「実行」ボタンに関連付け、実行すると図3のようになる。
図3の「オートフィルターの解除」には、リスト2のマクロを関連付ける。
Sub オートフィルターの解除() Range("A1").AutoFilter Worksheets("入力画面").Range("C2").Value = "" End Sub
オートフィルターを解除し、「入力画面シート」の「C2」セルを空にする。
今度は、「金額」の下限と上限を設定し、範囲を絞って抽出してみよう。
数値での絞り込み抽出を行うには、AutoFilterメソッドに以下のように引数を指定する。
{オブジェクト}.AutoFilter Field:={列番号},Criteria1:={抽出条件1},Operator1:={Operator},Criteria2:={抽出条件2})
{オブジェクト}には、Rangeオブジェクトを指定し、{列番号}には、フィルターの対象となるフィールド番号を指定する。{抽出条件1}{抽出条件2}には、表2のように比較演算子を使用する。{Operator}は表3のように、{抽出条件1}{抽出条件2}に関する論理演算子などを指定する。
指定の仕方 | 説明 |
---|---|
Criteria1:="1000" | 1000と等しい |
Criteria1:=">1000" | 1000より大きい |
Criteria1:=">=1000" | 1000以上 |
Criteria1:="<1000" | 1000より小さい |
Criteria1:="<=1000" | 1000以下 |
定数 | 説明 |
---|---|
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に入力した値で抽出するコードはリスト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
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の「オートフィルターの解除」ボタンには、リスト3のコードを関連付ける。
Sub オートフィルターの解除2() Range("A1").AutoFilter Worksheets("入力画面").Range("C4").Value = "" Worksheets("入力画面").Range("C6").Value = "" End Sub
オートフィルターを解除し、「入力画面シート」の「金額1」と「金額2」を入力するセルを空にする。
ここでは、「購入日」の検索開始日と終了日を指定して、その範囲内のデータを抽出する方法を紹介する。日付での絞り込み抽出を行うには、「数値での絞り込み抽出」と同じ書式を用いる。
「入力画面」シートに図6のように「購入日」を入力するセルを用意しておく。「購入日1」と「購入日2」に入力された値で抽出を行う。AND検索になる。
図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
2行目のように「購入日」を入力するセルが空の場合は警告メッセージを出して処理を抜ける。それ以外の場合は6〜7行目の処理を行う。
6行目では、「抽出画面」シートの「A1(見出し)」に対してオートフィルターを提供する。フィルター条件として、「Field」に「購入日」を表示している「D列」、すなわち列番号の「4」を指定する。最初の抽出条件を指定する「Criteria1」には「入力画面シートのセルC8に入力した値に等しくそれより大きく」を指定し、Operatorには表1の「xlAnd」の論理演算子「AND」を指定し、次の抽出条件である「Criteria2」には、「入力画面シートのセルC10に入力された値に等しくそれより小さい」という条件を指定する。
つまり「C8」の「日付」から「C10」の範囲の日付が抽出表示される。
7行目では最後に「抽出画面」を選択し、抽出結果を表示する。
リスト4のマクロを「購入日」の「実行」ボタンに関連付け、実行した結果は図7のようになる。
図7の「オートフィルターの解除」ボタンには、リスト5のコードを関連付ける。
Sub オートフィルターの解除3() Range("A1").AutoFilter Worksheets("入力画面").Range("C8").Value = "" Worksheets("入力画面").Range("C10").Value = "" End Sub
オートフィルターを解除し、「入力画面シート」の「購入日1」と「購入日2」を入力するセルを空にする。
ここで注意していただきたいことがある。
それは、解説の都合上、今回は1つの「オートフィルターの解除」ボタンに、その都度異なったマクロを関連付けしているため、別のマクロを実行すると、「入力画面」シートの入力セルは空にならないということである。
今回紹介したTipsは3個あり、3個の「オートフィルターの解除」マクロを作成している。本来なら「文字列オートフィルターの解除」「金額オートフィルターの解除」「日付オートフィルターの解除」という3つのボタンを配置し、それぞれに該当するマクロを関連付けする必要がある。しかし、今回はそれを行っていないので、ご了承願いたい。各自が試す場合は個別のボタンを作成して試してみるといいだろう。
薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所
薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。
1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。
1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。
2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。
Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。
Microsoft MVP for Development Platforms - Client App Dev(Oct 2003-Sep 2012)。
Microsoft MVP for Development Platforms - Windows Phone Development(Oct 2012-Sep 2013)。
Microsoft MVP for Development Platforms - Client Development(Oct 2013-Sep 2014)。
Copyright © ITmedia, Inc. All Rights Reserved.