売上から条件を指定して、データを抽出する
動画でExcel 必要なデータだけ抽出したい
マクロでフィルターの自動化はどうするか？
動画版「マクロ講座」です。
ワークシートでオートフィルターを使って必要なデータを抽出するということはよくあります。
それをマクロでする場合はどのようにしたらいいかと言うフィルターの自動化方法について紹介します。
ワークシートでオートフィルターを使って必要なデータを抽出するということはよくあります。それをマクロでする場合はどのようにしたらいいかと言うフィルターの自動化方法について紹介します。
（サンプルファイルは、こちらです。 売上から条件を指定して、データを抽出する、サンプル36回）
売上から条件を指定して、データを抽出する方法 2022年度入門編36回
次回からVBAで請求書を発行するマクロを制作します。そのマクロ内で対象データを抽出するためにオートフィルターを使います。 今回は予備知識の復習としてオートフィルターの復習をします。
オートフィルターの使い方をしっかり学習
-
今回はこちらの表を使って、特定の支店のデータを抽出してみましょう。
-
フィルターを VBA で操作するにはオートフィルターというメソッドを使います。
フィルターをオンにして抽出の基準とする列と条件を指定して実際に抽出を行うまでの処理が autofilter メソッドひとつでできてしまうのです。
.AutoFilter メソッドの構文は次のとおりです。
expression.AutoFilter (Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown) -
expression（エクスプレッション）は対象となる抽出元のセル範囲です。
ここは A 1を含むセル範囲という意味でレンジ A 1を指定します。
サンプルのように、単一セルを指定した場合は、そのセルを含むアクティブセル領域が対象になります。 引数 criteria 1には埼玉と指定しました。
-
実行すると埼玉県だけの売り上げが抽出されます。
-
引数Criteria1、引数Criteria2で抽出条件を設定する場合、比較演算子やワイルドカードを使用できます。
例えば”埼玉”のように書かなくても”埼*”だけでも”*玉”だけでも同じ結果になります。
同じ文字を含む文字列を抽出することができます。
-
次に神奈川県の神だけ指定して抽出してみましょう。
Sub オートフィルター2() Range("A1").AutoFilter Field:=3, _ Criteria1:="神*" End Sub
.AutoFilter メソッドの引数と説明
| 名前 | 必須 | データ型 | 説明 |
|---|---|---|---|
| Field | 省略可能 | Variant | フィルターの対象となるフィールド番号を整数で指定します。フィールド番号は、リストの左側から始まります。つまり、最も左側にあるフィールドはフィールド番号 1 になります。 |
| Criteria1 | 省略可能 | Variant | 抽出条件 ("101" などの文字列)。 空白のフィールドを検索するには "="、空白以外のフィールドを検索するには "<>"、データ型の (データなし) フィールドを選択するには "><" を使用します。 この引数を省略すると、抽出条件は All になります。 演算子 が xlTop10Items の場合は、Criteria1 に項目数を指定します (たとえば "10" など)。 |
| 演算子 | 省略可能 | XlAutoFilter Operator |
フィルターの種類を XlAutoFilterOperator の定数で指定します。 |
| Criteria2 | 省略可能 | Variant | 2 番目の抽出条件となる文字列を指定します。 Criteria1 および Operator と組み合わせて使い、複合抽出条件を構築します。 日、月、または年によってフィルター処理する日付フィールドでの単一条件としても使用されます。 その後には 配列 (レベル、日付) のフィルター処理の詳細を示す配列が続きます。 レベルが 0-2 (年、月、日) の場合は、日付はフィルター処理の期間の中にある 1 つの有効な日付です。 |
| SubField | Optional | Variant | 抽出条件を適用するデータ型のフィールド (たとえば、地理学の [人口] フィールド、または株価の [量] フィールド)。 この値を省略すると、"(表示値)" が対象になります。 |
| Visible DropDown |
省略可能 | Variant | True を指定すると、フィルター処理されるフィールドのオートフィルターのドロップダウン矢印を表示します。 False を指定すると、抽出されるフィールドのオートフィルターのドロップダウン矢印を非表示にします。 既定値は True です。 |
オートフィルターで抽出された件数を数える
-
オートフィルターで抽出されたデータの件数を数えるには、表示されているセルを数えます。
オートフィルターが実行されているときは、対象外のセルが折り畳まれてい非表示になっているので、折りたたまれて非表示になっているセルを除いて表示されているセルだけを数えます。
これには RANGE オブジェクトのSpecialCells メソッドを使います。引数はxlCellTypeVisibleにします。
また-1で見出し行分をマイナスしています。 .SpecialCells(xlCellTypeVisible).Count
-
オートフィルターで同じ列に対して、複数の条件を設定して抽出する場合を考えましょう。
例えば、営業担当を2名設定するなどというケースです。
一つの列に対して二つの条件を組み合わせる場合は、オートフィルターのメソッドの引数 criteria 1で一つ目の条件を指定し、 criteria2に二つ目の条件を設定します。
そのとき二つの条件の関係は and と or があります。 それは引数のoperatorで指定します。
かつの意味では Operator:=xlAndを使い、またはの意味では Operator:=xlOrを使います。
Sub オートフィルター東京を複数担当() Dim cnt As Long Range("A1").AutoFilter Field:=3, Criteria1:="*京" Range("A1").AutoFilter Field:=2, Criteria1:="高橋 茂", _ Operator:=xlOr, Criteria2:="鈴木 実" cnt = Range("A1").CurrentRegion.Columns(1) _ .SpecialCells(xlCellTypeVisible).Count Debug.Print "高橋,鈴木の件数：" & cnt - 1 Range("A1").AutoFilter End Sub -
最後のコードを実行するとオートフィルターは解除されます。
Range("A1").AutoFilterです。
そしてイミディエイトウィンドーに個数が表示されました。
異なる列で複数条件を設定する
-
オートフィルターで異なる列に対して複数条件を設定する場合には、 オートフィルターメソッドを複数実行します。 たとえば、2列目では担当者を指定、4列目では金額を指定するようなケースです。
Sub オートフィルター伊藤をカウント() Dim cnt As Long Range("A1").AutoFilter Field:=2, Criteria1:="伊藤*" Range("A1").AutoFilter Field:=4, Criteria1:=">20000" cnt = Range("A1").CurrentRegion.Columns(1) _ .SpecialCells(xlCellTypeVisible).Count Debug.Print "伊藤の件数：" & cnt - 1 Range("A1").AutoFilter End Sub -
実行すると、伊藤氏の売上で20000以上が抽出されました。
