抽出はデータ処理の過程で欠かせない作業の1つです。
この記事ではたくさんの実践例を紹介しているので
「こんな処理をしたいとき、どんなコードを書けばいいんだろう？」
の答えがきっと見つかります。
オートフィルターで抽出する｜AutoFilterメソッド基本構造
ドシンプルな使い方がこちら↓
セル範囲.AutoFilter Field:=何列目,Criteria1:=”抽出文字”
セル範囲は連続するデータの左上角のセルを指定すればOK！
Fieldでフィルターをかける列を指定して、Criteria1の後に抽出条件を記載します。
Criteriaは「クライテリア」と読みます。
使用例 メス猫だけを抽出します。
Sub メス猫抽出()
Range(“B2″).AutoFilter Field:=4,Criteria1:=”メス”
End Sub
複数条件で抽出する
引数「Operator」を使用して抽出条件の指定方法を設定します。
セル範囲.AutoFilter Field:=何列目,Criteria1:=”抽出文字”,Operator:=xlOr,Criteria2:=”抽出文字”
OperatorにxlOrを使うと抽出条件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メソッドの違い
- 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行目で転記先のシートをクリアしてから、抽出と転記を行う指示をしています。
応用編｜テーブルやインプットボックスを使用して抽出
AutoFilterメソッドは、Excelでおなじみの機能である「テーブル」や「インプットボックス」と組み合わせて使用することも可能です。
テーブルの見出しを指定して抽出する
利点：見出しを抽出条件に指定すると、列を移動しても同じ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