【Excel VBA】AutoFilterで抽出|複数条件や別シートにコピーなど豊富な実践例で解説

  • 2022-04-23
  • 2022-05-13
  • Excel

抽出はデータ処理の過程で欠かせない作業ですね。
この記事ではたくさんの実践例を紹介しているので
「こんな処理をしたいとき、どんなコードを書けばいいんだろう?」
の答えがきっと見つかります。

オートフィルターで抽出する|AutoFilterメソッド基本構造

ドシンプルな使い方がこちら↓

セル範囲.AutoFilter Field:=何列目,Criteria1:=”抽出文字”

セル範囲は連続するデータの左上角のセルを指定すればOK!
Fieldでフィルターをかける列を指定して、Criteria1の後に抽出条件を記載します。
Criteria「クライテリア」と読みます。

 使用例  メス猫だけを抽出します。

Sub メス猫抽出()
 Range(“B2″).AutoFilter Field:=4,Criteria1:=”メス”
End Sub

複数条件で抽出する

引数「Operator」を使用して抽出条件の指定方法を設定します。

セル範囲.AutoFilter Field:=何列目,Criteria1:=”抽出文字”,Operator:=xlOr,Criteria2:=”抽出文字”

OperatorxlOrを使うと抽出条件1または2に当てはまるセルを抽出、xlAndをつかうと抽出条件1かつ2に当てはまるセルを抽出します。

 使用例  群馬か千葉で生まれた猫だけを抽出します。

Sub 群馬か千葉出身の猫抽出()
 Range(“B2″).AutoFilter Field:=6,Criteria1:=”群馬”,Operator:=xlOr,Criteria2:=”千葉”
End Sub

複数列に個別の条件でオートフィルターをつかう

各列個別の条件を指定して抽出することも可能です。

セル範囲.AutoFilter Field:=何列目,Criteria1:=”抽出文字”
セル範囲.AutoFilter Field:=何列目,Criteria1:=”抽出文字”

1列につき1行書きます。同じ要領で3列にフィルターをかけることもできます。

 使用例  マンチカン種のオス猫だけを抽出します。

Sub マンチカン種のオス猫抽出()
 Range(“B2″).AutoFilter Field:=3,Criteria1:=”マンチカン”

 Range(“B2″).AutoFilter Field:=4,Criteria1:=”オス”
End Sub

抽出条件に範囲を設定する

抽出文字に比較演算子を付帯します。

セル範囲.AutoFilter Field:=何列目,Criteria1:=”比較演算子と抽出文字”

 使用例  4歳より年上の猫を抽出します。

Sub 四歳より年上の猫抽出()
 Range(“B2″).AutoFilter Field:=5,Criteria1:=”>4″
End Sub

その他の範囲設定例

「Criteria1:=」のあとの比較演算子の使い方でいろいろな範囲設定ができます。

✔ ”=4″ なら「4歳の猫」      ✔ ”<=4″ なら「4歳以下の猫」
✔ ”<>4″ なら「4歳以外の猫」    ✔ ”>=4″ なら「4歳以上の猫」

ある文字列を含むセルを抽出する

抽出文字にワイルドカード比較演算子を付帯します。

 使用例  種類に「ン」が含まれる猫を抽出する。

Sub ンが付く種類の猫抽出()
 Range(“B2″).AutoFilter Field:=3,Criteria1:=”*ン*”
End Sub

その他の文字列設定例

「Criteria1:=」のあとの比較演算子やワイルドカード使い方を紹介します。

✔ ”マン*” なら「マンから始まる猫」     ✔ ”” なら「空欄のセル」
✔ ”<>*マン*” なら「マンを含まない猫」    ✔ ”<>” なら「空欄以外のセル」
✔ ”<>マンチカン” なら「マンチカン以外の猫」

1列に対して3つ以上の条件を設定する

AutoFilterメソッドでは、Criteriaは1列に対して2つまでしか設定できません。
3つ以上の条件を指定したい場合は「Array」を使えば解決します。
※長いので「 _」で改行しています。

セル範囲.AutoFilter Field:=何列目,Criteria1:=Array(“条件1″,”条件2″,”条件3”) _
,Operator:=xlFilterValues

 使用例  「ミックス」と「マンチカン」と「ラグドール」を抽出する。

Sub 三種類の猫抽出()
 Range(“B2”).AutoFilter Field:=3,Criteria1:=Array(“ミックス”,”マンチカン”,”ラグドール”), _

Operator:=xlFilterValues
End Sub

Arrayの後にダブルクォーテーションで条件を囲って、カンマで区切ります。
OperatorにxlFilterValuesを指定すれば完成です。

引数Operatorの設定値

組み合わせすることで、より詳細な抽出処理が可能になります。

✔ xlTop10Items・・・ 上から数えて10番目までを表示(xlBottomなら下から)
✔ xlTop10Percent・・・ 上から数えて10%までを表示(xlBottomなら下から)
✔ xlFilterCellColor・・・ セルの色を抽出条件に指定する
✔ xlFilterFontColor・・・ フォントの色を抽出条件に指定する
✔ xlFilterValues・・・ フィルターの値を抽出条件に指定する

オートフィルターを解除する

連続した処理などで一度オートフィルターを解除したい時は、以下のように書きます。

Activsheet.AutoFilterMode=False

アクティブシート以外のオートフィルターを解除したい時はシート名を指定します。

✔ Sheet1.AutoFilterMode = False     「シート1のオートフィルター解除」
✔ Sheets(“猫”).AutoFilterMode = False  「シート名が猫のシートでオートフィルター解除」

フィルターオプション機能を使って抽出する|AdvancedFilter

AutoFilterメソッドではプロシージャ内に抽出条件を書きましたが、
AdvancedFilterメソッドワークシート上に記述した抽出条件を元にデータを抽出します。

セル範囲.AdvancedFilter(Action,CriteriaRange,CopyToRange,Unique)

引数Actionは省略不可です。

引数 Action

どちらかの設定値を記述して、抽出結果の表示場所を設定します。

✔ 抽出結果を元データと同じ場所に表示・・・ Action:=xlFilterInPlace
✔ 抽出結果をコピーして別の場所に表示・・・ Action:=xlFilterCopy

引数 CriteriaRange

抽出条件の範囲を指定します。
ワークシート上に抽出条件を記述する範囲の事です。

AdvancedFilterメソッドで抽出結果を元データと同じ場所に表示

セル範囲.AdvancedFilter Action:=xlFilterInPlace,CriteriaRange:=任意のセル範囲,Unique

 使用例  3歳のオス猫を抽出する。

Sub 三歳のオス猫抽出()
 Range(“B4”).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, _

 Criteriarange:=Range(“B1:G2”), Unique:=True
End Sub

Unique:=Trueで重複データを非表示にしています。
Unique:=False、または省略すると重複データも表示されます。

AdvancedFilterメソッドで抽出結果をコピーして別シートに転記

セル範囲.AdvancedFilter Action:=xlFilterCopy

 使用例  群馬生まれの猫を抽出して群馬のシートに転記

Sub 群馬の猫抽出()
 Worksheets(“群馬の猫”).Cells.Clear
 Range(“B4”).CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
 CriteriaRange:=Range(“B1:G2”), CopyToRange:=Worksheets(“群馬の猫”).Range(“A1”)
End Sub

1行目で転記先のシートをクリアしてから、抽出と転記を行う指示をしています。

応用編|テーブルやインプットボックスを使用して抽出

テーブルの見出しを指定して抽出する

利点:見出しを抽出条件に指定すると、列を移動しても同じVBAコードで動くので便利。

.ListObjects(“テーブル名”).Range.AutoFilter.ListCoiumuns(“見出し名”).Index,”任意の文字列”

 使用例  種類が「ペルシャ」の猫を抽出

Sub ペルシャを抽出()
 With ActiveSheet.ListObjects(“猫”)
  .Range.AutoFilter .ListColumns(“種類”).Index, “ペルシャ”
 End With
End Sub

インプットボックスを使用して抽出する

利点:抽出条件を都度選択する仕様なら、同じVBAコードで動くので便利。

 使用例  ユーザーが入力した猫を抽出

Sub 猫選択()
 Dim myCri As String
  myCri = InputBox(“猫種を入力してください” & vbCrLf & _

  ”マンチカン・メインクーン・ラグドール・ミックス・ペルシャ”)
 Worksheets(“猫”).Range(“B2”).AutoFilter Field:=3, Criteria1:=myCri
End Sub

抽出のまとめ

今回はいろいろな抽出方法を紹介しました。
ご自身の実務に合ったものがあればぜひ、コピペ&編集してご利用ください。