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

マクロ講座 動画編 36回

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

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

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

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

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

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

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

   

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

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

   

4. 実行すると埼玉県だけの売り上げが抽出されます。

   

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

   

6. 次に神奈川県の神だけ指定して抽出してみましょう。

   

   Sub オートフィルター2()
       Range("A1").AutoFilter Field:=3, _
       Criteria1:="神*"
   End Sub
   

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

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

   

7. オートフィルターで同じ列に対して、複数の条件を設定して抽出する場合を考えましょう。
   例えば、営業担当を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

8. 最後のコードを実行するとオートフィルターは解除されます。
   Range("A1").AutoFilterです。
   そしてイミディエイトウィンドーに個数が表示されました。

   

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

9. オートフィルターで異なる列に対して複数条件を設定する場合には、 オートフィルターメソッドを複数実行します。 たとえば、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
   

10. 実行すると、伊藤氏の売上で20000以上が抽出されました。