大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、オートフィルタを設定する方法について、ご紹介します。使うVBAコードは、「.AutoFilter」です。オートフィルタでは「比較演算子」や「ワイルドカード」を使うことができます。VBAでオートフィルタを自動化して実務で使いこなしていきましょう。

はじめに

この記事では、オートフィルタを設定する方法について、ご紹介します。

オートフィルタをVBAで設定する場合は、「.AutoFilter」を使います。

オートフィルタには、「比較演算子」と「ワイルドカード」、「論理演算子」、「配列を使ったフィルタ」を使うことができます。

「論理演算子」と「配列を使ったフィルタ」については、こちらでまとめていまます。

フィルタする際のポイントは、「表示している文字」でフィルタされるということです。

オートフィルタはかなり便利なExcel機能ですので、VBAで自動化してさらに効率化していきましょう。

では、VBAでオートフィルタを設定する方法について、解説していきます。

この記事で紹介すること

  • VBAでオートフィルタを設定する方法

目次から見たい項目へ移動すると便利ですよ。

目次

VBAコードまとめ

オートフィルタを設定するVBAコードについて、まとめています。

VBAコードだけ確認したい場合に、ご活用ください。

'オートフィルタを設定
Range("B2").AutoFilter 2, "東京"

'比較演算子を使う
Range("B2").AutoFilter 2, ">300" '300より大きい
Range("B2").AutoFilter 2, "<300" '300より小さい
Range("B2").AutoFilter 2, ">=300" '300以上
Range("B2").AutoFilter 2, "<=300" '300以下
Range("B2").AutoFilter 1, "<>北海道" '北海道以外
Range("B2").AutoFilter 2, "" '空白
Range("B2").AutoFilter 2, "<>" '空白以外

'ワイルドカードを使う
Range("B2").AutoFilter 1, "*県" '県を含む
Range("B2").AutoFilter 1, "??県" '2文字の県

では、解説していきます。

オートフィルタを設定してフィルタする

オートフィルタを設定してフィルタしてみます。

オートフィルタの設定

オートフィルタの設定は、簡単です。1行で書けます。

こんな感じで記載します。

オートフィルタの設定

Range(表の左上).AutoFilter 列番号, フィルタする値

「表の左上」のセルを指定して、フィルタする「列番号」とフィルタする「値」を入力します。

「.AutoFilter」のイメージ

「.AutoFilter」のイメージ

では、「.AutoFilter」を使って、設定してみます。

フィルタしてみる

「.AutoFilter」で「2列目」を「東京」でフィルタしてみます。

VBAコードは、このようになります。

Sub TEST1()
    
    '東京でフィルタ
    Range("B2").AutoFilter 2, "東京"
    
End Sub

では、VBAコードを実行してみます。

「2列目」を「東京」でフィルタ

「2列目」を「東京」でフィルタ

「2列目」を「東京」でフィルタできました。

空白行がある場合のオートフィルタの設定

「空白行」がある場合のオートフィルタの設定をやってみます。

空白行を含んだ表の場合

空白行を含んだ表の場合は、「表全体」を指定する必要があります。

空白行を含んだ表は、こんな感じです。

空白行がある場合

空白行がある場合

基本的には、空白行は含まない方がいいです。

どうしても空白行が必要という場合は、表全体を指定してあげましょう。

こんな感じで表全体を指定します。

表全体を指定する

表全体を指定する

では、空白行がある表でフィルタしてみます。

空白行がある表でフィルタする

「Range("B2:D11")」というように表全体を指定します。

Sub TEST2()
    
    '表全体を指定してフィルタ
    Range("B2:D11").AutoFilter 2, "東京"
    
End Sub

では、VBAコードを実行してみます。

空白行がある表でフィルタした結果

「空白行」がある表でフィルタできました。

行が可変の場合

「空白行」があって「行が可変」の場合を考えてみます。

空白行があって行が可変の場合

空白行があって行が可変の場合

表なので、実務で使う際は、行数が変わってしまう場合がほとんどです。

なので、行全体を指定する場合は、「End」を使って最終行を取得しましょう。

「End」を使って行全体を指定する

「End」を使って行全体を指定する

「Range(左上, 右下)」の「右下」がわからない場合がほとんどです。

表の右下のセルをを「Cells(Rows.Count, "D").End(xlUp)」で取得して、表全体を指定しましょう。

では、「空白行」があって「行が可変」の場合でフィルタしてみます。

「空白行」があって「行が可変」の場合でフィルタ

VBAコードは、こんな感じになります。

Sub TEST3()
    
    'Endを使って表全体を指定してフィルタ
    Range(Range("B2"), Cells(Rows.Count, "D").End(xlUp)).AutoFilter 2, "東京"
    
End Sub

では、VBAコードを実行してみます。

「空白行」があって「行が可変」の場合でフィルタした結果

「空白行」があって「行が可変」の場合でフィルタできました。

ただ、空白行がない方がいいです。

消せるのであれば消しましょう。

比較演算子を使ってフィルタ

オートフィルタは、「比較演算子」を使ってフィルタすることができます。

比較演算子を使う

比較演算子は、次の種類があります。

  • 「">値"」 :値より大きい
  • 「"<値"」 :値より小さい
  • 「">=値"」:値以上
  • 「"<=値"」:値以下
  • 「"<>値"」:値以外
  • 「""」  :空白
  • 「"<>"」 :空白以外

という感じです。それぞれでフィルタしてみます。

「">値"」:値より大きい

「">値"」を使ってフィルタしてみます。

「値より大きい」値をフィルタできます。

Sub TEST4()
    
    '「300より大きい」値でフィルタ
    Range("B2").AutoFilter 2, ">300"
    
End Sub

表を用意しておきます。

表を用意

表を用意します

では、VBAコードを実行してみます。

「">300"」でフィルタ

「>300」でフィルタ

「300より大きい」値でフィルタできました。

「"<値"」:値より小さい

「"<値"」を使ってフィルタしてみます。

「値より小さい」値をフィルタできます。

Sub TEST5()
    
    '「300より小さい」値でフィルタ
    Range("B2").AutoFilter 2, "<300"
    
End Sub

では、VBAコードを実行してみます。

「"<300"」でフィルタ

「<300」でフィルタ

「300より小さい」値でフィルタできました。

「">=値"」:値以上

「">=値"」を使ってフィルタしてみます。

「値以上」をフィルタできます。

Sub TEST6()
    
    '「300以上」でフィルタ
    Range("B2").AutoFilter 2, ">=300"
    
End Sub

では、VBAコードを実行してみます。

「">=300"」でフィルタ

「>=300」でフィルタ

「300以上」の値でフィルタできました。

「"<=値"」:値以下

「"<=値"」を使ってフィルタしてみます。

「値以下」をフィルタできます。

Sub TEST7()
    
    '「300以下」でフィルタ
    Range("B2").AutoFilter 2, "<=300"
    
End Sub

では、VBAコードを実行してみます。

「"<=300"」でフィルタ

「<=300」でフィルタ

「300以下」の値でフィルタできました。

「"<>値"」:値以外

「"<>値"」を使ってフィルタしてみます。

「値以外」でフィルタできます。

Sub TEST8()
    
    '「北海道以外」でフィルタ
    Range("B2").AutoFilter 1, "<>北海道"
    
End Sub

では、VBAコードを実行してみます。

「"<>北海道"」でフィルタ

「<>北海道」でフィルタ

「北海道以外」でフィルタできました。

「""」:空白

「""」を使ってフィルタしてみます。

「空白」をフィルタできます。

Sub TEST9()
    
    '「空白」でフィルタ
    Range("B2").AutoFilter 2, ""
    
End Sub

では、VBAコードを実行してみます。

「""」でフィルタ

「

「空白」をフィルタできました。

「"<>"」:空白以外

「"<>"」を使ってフィルタしてみます。

「空白以外」をフィルタできます。

Sub TEST10()
    
    '「空白以外」でフィルタ
    Range("B2").AutoFilter 2, "<>"
    
End Sub

では、VBAコードを実行してみます。

「"<>"」でフィルタ

「<>」でフィルタ

「空白以外」でフィルタできました。

ワイルドカードを使ってフィルタする

オートフィルタでは、「ワイルドカード」を使うことができます。

ワイルドカードを使う

ワイルドカードは、次の2種類があります。

  • 「*」:なんでもいい
  • 「?」:なんでもいい。ただし1文字。

という感じです。

では、ワイルドカードを使ってフィルタしてみます。

「*県」で県を含む値をフィルタ

「*県」を使って、県を含む値をフィルタしてみます。

Sub TEST11()
    
    '「県を含む」値をフィルタ
    Range("B2").AutoFilter 1, "*県"
    
End Sub

表を用意します。

表を用意

表を用意します

では、VBAコードを実行してみます。

「*県」でフィルタ

「*県」でフィルタ

県を含む値をフィルタできました。

「??県」で2文字の県をフィルタ

「??県」を使って、2文字の県をフィルタしてみます。

Sub TEST12()
    
    '「2文字の県」をフィルタ
    Range("B2").AutoFilter 1, "??県"
    
End Sub

では、VBAコードを実行してみます。

「??県」でフィルタ

「??県」でフィルタ

2文字の県をフィルタできました。

記号をフィルタする場合

記号をフィルタする場合は、ちょっと工夫が必要です。

「<」や「=」をフィルタする場合

「<」や「=」の比較演算子をフィルタする場合は、「"="」を前につけてフィルタします。

「<」をフィルタする

「<」をフィルタするには、「"=<"」とします。

Sub TEST13()
    
    '「<」をフィルタ
    Range("B2").AutoFilter 1, "=<"
    
End Sub

元データを用意しておきます。

元データ

では、VBAコードを実行してみます。

「<」をフィルタする

「<」をフィルタできました。

「=」をフィルタする

「=」をフィルタするには、「"=="」とします。

Sub TEST14()
    
    '「=」をフィルタ
    Range("B2").AutoFilter 1, "=="
    
End Sub

では、VBAコードを実行してみます。

「=」をフィルタする

「=」をフィルタできました。

「?」や「*」をフィルタする場合

「?」や「*」のワイルドカードをフィルタするには、前に「"˜"」を付けます。

「?」をフィルタする

「?」をフィルタするには、「"˜?"」とします。

Sub TEST15()
    
    '「?」をフィルタ
    Range("B2").AutoFilter 1, "~?"
    
End Sub

元データを用意しておきます。

元データ

では、VBAコードを実行してみます。

「?」をフィルタする

「?」をフィルタできました。

「*」をフィルタする

「*」をフィルタするには、「"˜*"」とします。

Sub TEST16()
    
    '「*」をフィルタ
    Range("B2").AutoFilter 1, "~*"
    
End Sub

では、VBAコードを実行してみます。

「*」をフィルタする

「*」をフィルタできました。

オートフィルタする際の注意

オートフィルタする際の注意は、「表示されている文字」でフィルタされる、ということです。

表示されている文字列でフィルタされる

先ほどの表の値に表示形式を設定して、表示を変更してみます。

表示形式を設定

表示形式を設定

「"1300"」でフィルタしてみます。

「"1300"」でフィルタ

Sub TEST17()
    
    '「1300」でフィルタ
    Range("B2").AutoFilter 2, "1300" '←うまくフィルタできない
    
End Sub

では、VBAコードを実行してみます。

フィルタ結果がなし

フィルタ結果がないですね。

ここが注意ポイントです。

「表示されている文字」でフィルタする必要があります。

表示形式を指定してフィルタする

表示形式まで含めて文字を設定して、フィルタしてみます。

「"1,300万人"」でフィルタ

「"1,300万人"」でフィルタしてみます。

Sub TEST18()
    
    '「1,300万人」でフィルタ
    Range("B2").AutoFilter 2, "1,300万人"
    
End Sub

では、VBAコードを実行してみます。

「1,300万人」でフィルタした結果

「"1,300万人"」でフィルタできました。

こんな感じで、表示している文字でフィルタすると正しくフィルタできます。

「Format」で表示形式を設定

表示形式は、「Format」で設定することもできます。

Sub TEST19()
    
    '表示形式を指定してフィルタ
    Range("B2").AutoFilter 2, Format(1300, "#,##0万人")
    
End Sub

では、VBAコードを実行してみます。

「Format」で表示形式を設定してフィルタした結果

「"1,300万人"」でフィルタできました。

「NumberFormatLocal」を使うと便利

表示形式を調べるのが面倒な場合は、「NumberFormatLocal」を使うと便利です。

Sub TEST20()
    
    '表示形式を指定してフィルタ
    Range("B2").AutoFilter 2, Format(1300, Range("C3").NumberFormatLocal)
    
End Sub

この場合は、「NumberFormatLocal」で「"#,##0万人"」を取得できます。

では、実行してみます。

「NumberFormatLocal」を使って表示形式を設定してフィルタした結果

「"1,300万人"」でフィルタできました。

同じ列には同じ表示形式が設定されている場合がほとんどですので、この方法が使えます。

おわりに

この記事では、オートフィルタを設定する方法について、ご紹介しました。

オートフィルタをVBAで設定する場合は、「.AutoFilter」を使います。

オートフィルタには、「比較演算子」と「ワイルドカード」、「論理演算子」、「配列を使ったフィルタ」を使うことができます。

「論理演算子」と「配列を使ったフィルタ」については、こちらでまとめていまます。

フィルタする際のポイントは、「表示している文字」でフィルタされるということです。

オートフィルタはかなり便利なExcel機能ですので、VBAで自動化してさらに効率化していきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す