EXCEL VBA AdvancedFilterメゾット・フィルターオプション・データ抽出・抽出条件・詳細条件・日付範囲抽出・抽出結果を別シート・重複削除
EXCEL VBA AdvancedFilterメゾット・フィルターオプション・データ抽出・抽出条件・詳細条件・日付範囲抽出・抽出結果を別シート・重複削除
今回説明するのは、EXCELの一覧データから抽出条件を設定して、さまざまなデータを抽出する事ができるAdvancedFilterメゾットの利用方法を説明いたします。AdvancedFilterメゾットの設定条件により、一意のデータを抽出したり、抽出結果をコピーする事で、元データを変更せずに別シートに抽出結果を転記することも出来ます。また、複雑な抽出条件も設定出来るので、AdvancedFilterメゾットを使いこなすことで、簡単に大量のデータから必要なデータを抽出できるのでとても便利な機能です。それでは、サンプルプログラムを交えて順番に説明いたします。
●【AutoFilterメゾットは、下記の参照して下さい】
●【EXCEL VBA データの並べ替えSort は、下記の参照して下さい】
● AdvancedFilterメゾットを利用するには、下記の通りに設定を行います。
| パラメータ | 必須・オプション | 引数・データ型 | 説明（内容） |
|---|---|---|---|
| Action | 必須 | xlFilterInPlace | 検索結果をそのまま同じ場所に出力する。 |
| xlFilterCopy | 検索結果をコピーする。（別の場所に転記）※CopyToRangeでコピー（転記）先を指定します。 | ||
| CriteriaRange | 省略可能 | Variant型 | 検索条件範囲を指定します。※指定しないとそのまま出力されます。 |
| CopyToRange | 省略可能 | Variant型 | Actionの引数が「xlFilterCopy」の場合、検索結果のコピー先を指定します。 |
| Unique | 省略可能 | True | 検索結果に一致するデータから重複データが除かれます。 |
| FALSE | 検索結果に一致するデータから重複データも含まれます。 |
●EXCELのフィルターオプション設定の場合（一致データ抽出）
【使用例】
●上記のEXCELのフィルターオプション設定と同じ処理をVBAプログラムを作成します。（一致データ抽出・重複データ削除）
|
1 2 3 4 5 6 7 8 9 |
' ' Sub AdvancedFilter00() 'A列・B列にあるデータから重複データを取り除きD・E列に表示します。 ActiveSheet.Columns("A:B").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=ActiveSheet.Range("D1"), Unique:=True End Sub ' |
【実行前～実行結果】 ※重複データが削除されました。（D・E列）
EXCEL VBA AdvancedFilterメゾット 指定した複数条件でのデータ抽出・フィルター処理（同一シートでの表示）
下記のサンプルプログラムは、AdvancedFilterメゾットを利用して、複数条件を指定して該当するデータ抽出を行うサンプルプログラムです。元データがセル「A8：C37」に勘定科目・部署データの一覧が登録されています。このデータを元に、セル「A１:C3」に抽出条件が記載されているので、この抽出条件で同一シートにフィルタリング処理を行い結果が表示されます。
【抽出条件】
①：部署:人事部 かつ 金額：800を超える
②：勘定科目：交通費 かつ 金額：1000未満
【プログラムの流れ】
① ワークシート：セル「A8」（青枠）からなる一覧データから、セル「A1」（黄枠）の抽出条件を元に同一シートにフィルタリング処理されます。
抽出結果がフィルタリングされるので、抽出結果に該当するセルは、表示・該当しない場合は、非表示（行）されます。
【非表示された行を再表示する場合】
下記プログラム： AdvancedFilter01_ShowAllData を実行します。
● ActiveSheet.ShowAllData ‘全てのデータを表示表示します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
' ' Sub AdvancedFilter01() '指定した複数条件でのデータ抽出（同一シートでの結果表示） ActiveSheet.Range("A8").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=ActiveSheet.Range("A1").CurrentRegion 'セルA8からなるデータ一覧にたして、セルA1からなる記載のフィルター条件により、データを抽出する。 End Sub ' ' '------------------------------------------------------------------------------- ' Sub AdvancedFilter01_ShowAllData() 'データ抽出したデータを再表示する。 If ActiveSheet.FilterMode Then 'アクティブシートがフィルターモードでならば ActiveSheet.ShowAllData '全てのデータ表示 End If End Sub ' |
（画面クリックして拡大）
EXCEL VBA AdvancedFilterメゾット 指定した複数条件でのデータ抽出（別シートに結果を表示）
下記のサンプルプログラムは、AdvancedFilterメゾットを利用して、複数条件での指定したデータ抽出を行うサンプルプログラムです。シート「Sheet1」セル「A8：C20」にパソコン一覧データが登録されています。このデータを元に、セル「A１:C3」に抽出条件を記載されています。この条件で抽出結果をシート「Sheet2」に転記します。
【抽出条件】
①：OS:Windows10 かつ 価格：210000未満
②：パソコン名：Surface かつ 価格 200000未満
【プログラムの流れ】
① 抽出結果の転記先のワークシート「Sheet２」をクリアー（消去）します。
② ワークシート「Sheet1」セル「A8」（青枠）からなるデータからセル「A1」（黄枠）の抽出条件でワークシート「Sheet2」へ抽出された結果を転記します。
【プログラム実行条件】
① ワークシート「Sheet1」・・・パソコン一覧データを登録します。（上記参照）
② ワークシート「Sheet2」・・・ワークシートのみ作成します（空白）
③ ワークシート「Sheet1」・・・抽出条件を指定します。（※抽出条件を指定）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
' ' Sub AdvancedFilter02() '指定した複数条件でのデータ抽出（別シートでの結果表示） Worksheets("Sheet2").Cells.Clear 'データ抽出の転記先の別シートをクリアー Range("A8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("A1").CurrentRegion, CopyToRange:=Worksheets("Sheet2").Range("A1"), Unique:=False 'セルA8からなるデータ一覧に対して、セルA1からなる表のフィルター条件により、データを抽出して、別シートに転記します。 End Sub ' |
（画面クリックして拡大）
EXCEL VBA AdvancedFilterメゾット 指定した複数条件でのデータ抽出（別シートでの結果表示）範囲期間の抽出
下記のサンプルプログラムは、AdvancedFilterメゾットを利用して、複数条件での指定したデータ抽出を行うサンプルプログラムです。今回は、日付に範囲指定を行い該当するデータを別シートに転記するサンプルプログラムです。データの抽出条件が「入金日」の範囲（2021年２月１日～2021年2月28日）に該当するデータが別シートに転記されます。
【抽出条件】
①：入金日：2021年２月１日以降 かつ 2021年２月28日まで
（範囲期間 2021年２月１日～2021年2月28日この範囲が抽出対象となります。）
【プログラムの流れ】
① 抽出結果の転記先のワークシート「抽出結果」をクリアー（消去）します。
② ワークシート「入金データ」セル「A４」（青枠）からなるデータからセル「B2：C３」（黄枠）に抽出条件（入金日：2021年２月１日以降 かつ 2021年２月28日まで）が登録されているので、この抽出条件でワークシート「抽出結果」を転記します。
【プログラム実行条件】
① ワークシート「入金データ」・・・入金データを登録します。（上記参照）
② ワークシート「抽出結果」・・・ワークシートのみ作成します（空白）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
' ' Sub AdvancedFilter03() '指定した複数条件でのデータ抽出（別シートでの結果表示）選択期間を転記 Dim ws01, ws02 As Worksheet Set ws01 = Worksheets("入金データ") Set ws02 = Worksheets("抽出結果") ws02.Cells.Clear 'データ抽出の転記先のシート[抽出結果]をクリアー ws01.Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=ws01.Range("B1:C2"), CopyToRange:=ws02.Range("A1"), Unique:=False 'セルA4からなるデータ一覧(入金データ)に対して、セルB1：C2からなる表のフィルター条件により、データを抽出して、別シートに転記します。 End Sub ' |
（画面クリックして拡大）
EXCEL VBA AdvancedFilterメゾット 指定した複数条件でのデータ抽出・抽出結果により、別シートに結果表示（複数シートに転記）
下記のサンプルプログラムは、AdvancedFilterメゾットを利用して、複数条件で指定したデータ抽出を行うサンプルプログラムです。今回は、人事リストに男性・女性共に一覧登録されているワークシート「人事リスト」があります。この人事リストを元に、男性のデータは、ワークシート「男性」へ、女性のデータは、ワークシート「女性」へデータを一括転記するサンプルプログラムです。
【抽出条件】
①：性別・男 ⇒ ワークシート「男性」に該当するデータを転記
②：性別・女 ⇒ ワークシート「女性」に該当するデータを転記
【プログラムの流れ】
① 抽出結果の転記先のワークシート「男性」及び「女性」をクリアー（３行目～100行目まで：消去）します。
② ワークシート「人事リスト」内に登録されている「性別」：男に該当するデータをワークシート「男性」へ転記します。
③ ワークシート「人事リスト」内に登録されている「性別」：女に該当するデータをワークシート「女性」へ転記します。
【プログラム実行条件】
① ワークシート「人事リスト」・・・人事データを登録します。「性別」・男女登録必須（上記参照）
② ワークシート「男性」・「女性」・・・ワークシートを作成して、各シートのセルA１に「性別」・セルA2に「男」を登録下記参照（女性は、「女」を登録）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
' ' Sub AdvancedFilter04() '指定した複数条件でのデータ抽出・抽出結果により、別シートに結果表示（複数シートに転記） Dim ws01, ws02, ws03 As Worksheet Set ws01 = Worksheets("人事リスト") Set ws02 = Worksheets("男性") Set ws03 = Worksheets("女性") ws02.Rows("3:100").Clear 'データ抽出の転記先のシート[男性]3行目～100行目までクリアー ws03.Rows("3:100").Clear 'データ抽出の転記先のシート[女性]3行目～100行目までクリアー ws01.Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=ws02.Range("A1:A2"), CopyToRange:=ws02.Range("A4"), Unique:=False 'セルA4からなるデータ一覧(人事リスト)に対して、性別が”男”のデータは、シート「男性」へデータが転記されます。 ws01.Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=ws03.Range("A1:A2"), CopyToRange:=ws03.Range("A4"), Unique:=False 'セルA4からなるデータ一覧(人事リスト)に対して、性別が”女”のデータは、シート「女性」へデータが転記されます。 End Sub ' |
（画面クリックして拡大）
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。