売上から条件を指定して、データを抽出する 入門編36回(2022年度)

売上から条件を指定して、データを抽出する

マクロ講座 動画編 36回
マクロ講座

動画でExcel 必要なデータだけ抽出したい

マクロでフィルターの自動化はどうするか?

動画版「マクロ講座」です。
ワークシートでオートフィルターを使って必要なデータを抽出するということはよくあります。 それをマクロでする場合はどのようにしたらいいかと言うフィルターの自動化方法について紹介します。
ワークシートでオートフィルターを使って必要なデータを抽出するということはよくあります。それをマクロでする場合はどのようにしたらいいかと言うフィルターの自動化方法について紹介します。
(サンプルファイルは、こちらです。 売上から条件を指定して、データを抽出する、サンプル36回

売上から条件を指定して、データを抽出する方法 2022年度入門編36回

次回からVBAで請求書を発行するマクロを制作します。そのマクロ内で対象データを抽出するためにオートフィルターを使います。 今回は予備知識の復習としてオートフィルターの復習をします。

オートフィルターの使い方をしっかり学習

  1. 今回はこちらの表を使って、特定の支店のデータを抽出してみましょう。

    オートフィルター売上から条件を指定して、データを抽出する方法1
  2. フィルターを VBA で操作するにはオートフィルターというメソッドを使います。
    フィルターをオンにして抽出の基準とする列と条件を指定して実際に抽出を行うまでの処理が autofilter メソッドひとつでできてしまうのです。
    .AutoFilter メソッドの構文は次のとおりです。
    expression.AutoFilter (Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown)

  3. .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 です。

  4. expression(エクスプレッション)は対象となる抽出元のセル範囲です。
    ここは A 1を含むセル範囲という意味でレンジ A 1を指定します。
    サンプルのように、単一セルを指定した場合は、そのセルを含むアクティブセル領域が対象になります。 引数 criteria 1には埼玉と指定しました。

    オートフィルター売上から条件を指定して、データを抽出する方法2
  5. 実行すると埼玉県だけの売り上げが抽出されます。

    オートフィルター売上から条件を指定して、データを抽出する方法3
  6. 引数Criteria1、引数Criteria2で抽出条件を設定する場合、比較演算子やワイルドカードを使用できます。
    例えば”埼玉”のように書かなくても”埼*”だけでも”*玉”だけでも同じ結果になります。
    同じ文字を含む文字列を抽出することができます。

    オートフィルター売上から条件を指定して、データを抽出する方法4
  7. 次に神奈川県の神だけ指定して抽出してみましょう。

    オートフィルター売上から条件を指定して、データを抽出する方法5
    Sub オートフィルター2()
        Range("A1").AutoFilter Field:=3, _
        Criteria1:="神*"
    End Sub
    

オートフィルターで抽出された件数を数える

  1. オートフィルターで抽出されたデータの件数を数えるには、表示されているセルを数えます。
    オートフィルターが実行されているときは、対象外のセルが折り畳まれてい非表示になっているので、折りたたまれて非表示になっているセルを除いて表示されているセルだけを数えます
    これには RANGE オブジェクトのSpecialCells メソッドを使います。引数はxlCellTypeVisibleにします。
    また-1で見出し行分をマイナスしています。 .SpecialCells(xlCellTypeVisible).Count

    オートフィルター売上から条件を指定して、データを抽出する方法6
  2. オートフィルターで同じ列に対して、複数の条件を設定して抽出する場合を考えましょう。
    例えば、営業担当を2名設定するなどというケースです。
    一つの列に対して二つの条件を組み合わせる場合は、オートフィルターのメソッドの引数 criteria 1で一つ目の条件を指定し、 criteria2に二つ目の条件を設定します。
    そのとき二つの条件の関係は and と or があります。 それは引数のoperatorで指定します。
    かつの意味では Operator:=xlAndを使い、またはの意味では Operator:=xlOrを使います。

    オートフィルター売上から条件を指定して、データを抽出する方法7
    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
  3. 最後のコードを実行するとオートフィルターは解除されます。
    Range("A1").AutoFilterです。
    そしてイミディエイトウィンドーに個数が表示されました。

    オートフィルター売上から条件を指定して、データを抽出する方法8

異なる列で複数条件を設定する

  1. オートフィルターで異なる列に対して複数条件を設定する場合には、 オートフィルターメソッドを複数実行します。 たとえば、2列目では担当者を指定、4列目では金額を指定するようなケースです。

    オートフィルター売上から条件を指定して、データを抽出する方法9
    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
    
  2. 実行すると、伊藤氏の売上で20000以上が抽出されました。

    オートフィルター売上から条件を指定して、データを抽出する方法10