文字列で絞り込む

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

大量のデータを絞り込むには、オートフィルタが便利です。ここでは、VBAからオートフィルタを使い倒すテクニックをご紹介します。なお、ボリュームがありますので、以下の項目にページを分けて解説します。なお、ここで解説するオートフィルタは、通常のワークシートに設定されているとします。テーブルでオートフィルタを操作するときは、また別の考え方が必要です。VBAでテーブルを扱う方法に関しては「VBAでテーブルの操作」をご覧ください。

  1. 書き方の基本
  2. 文字列で絞り込む (←このページ)
  3. 数値で絞り込む
  4. 日付で絞り込む
  5. 色で絞り込む
  6. 作業列で絞り込む
  7. 絞り込んだ結果をコピーする
  8. 絞り込んだ結果を集計する
  9. 絞り込んだ結果の行を操作する
  10. オートフィルタの状況を判定する
  11. 数式のエラーで絞り込む

文字列で絞り込む

下図のような表を例にします。いかにも、文字列で絞り込みそうな表です。

1列目を「"田中"と等しい」で絞り込むには、次のようにします。

Sub Macro1()
    Range("A1").AutoFilter 1, "田中"
End Sub

2番目の引数Criteria1には、絞り込みの条件を文字列形式で指定します。本項のテーマは「文字列で絞り込む」ですから、必然的に条件も文字列形式になりますが、条件に数値や日付を指定するときは、この「文字列形式で指定する」が重要なポイントになってきますので、しっかり認識してください。

条件に指定した「"田中"」というのは、本当は「"=田中"」という意味です。オートフィルタでは、条件に指定する文字列の先頭に「=」「>」「<」などの記号を含めなければなりません。しかし、どの記号もついていないときは「=」と見なされます。「"田中"」が本当は「"=田中"」だということを理解していると「"田中"ではない」という条件をどう書けばいいかも分かりますね。

Sub Macro2()
    Range("A1").AutoFilter 1, "<>田中"
End Sub

実務でよくある「空欄のセル」で絞り込むには、条件に「""」を指定します。空欄ですから""です。もちろん、このときも本来は先頭に「=」「>」「<」などの記号を含めなければなりません。何もつけないと「=」という意味なのですから「空欄のセル」で絞り込むときは「""」だけでなく「"="」でもOKです。

Sub Macro3()
    Range("A1").AutoFilter 2, "="   ''「""」でもOK
End Sub

ここまで理解できれば「空欄ではないセル」も分かりますね。

Sub Macro4()
    Range("A1").AutoFilter 2, "<>"
End Sub

セルに入力されている値が文字列だったときは、条件にワイルドカードを使えます。セルの値が数値や日付だったとき、条件にワイルドカードを指定してもエラーにはなりませんが、もちろん正しく動作しません。

Sub Macro5()
    Range("A1").AutoFilter 3, "東京*"   ''"東京"で始まる
End Sub


Sub Macro6()
    Range("A1").AutoFilter 3, "*北区"   ''"北区"で終わる
End Sub


Sub Macro7()
    Range("A1").AutoFilter 3, "*中*"   ''"中"を含む
End Sub

特殊な記号で絞り込む

これ、セミナーで質問されたので書いておきます。もし、セルに入力されている値が「*」や「?」などワイルドカードで使われる文字列や、「=」「>」「<」などオートフィルタの条件として使われる記号だったケースです。順番にご紹介します。

【ワイルドカード】

「*」と「?」のワイルドカードを文字列として条件に指定するときは、「*」と「?」の前に半角のチルダ(~)をつけます。これは、ワークシート内で「*」と「?」を検索するときと同じです。

Sub Macro8()
    Range("A1").AutoFilter 1, "~*"
End Sub


Sub Macro9()
    Range("A1").AutoFilter 1, "~?"
End Sub

【記号】

分かりにくいのが「=」「>」「<」などの記号です。これらの記号を文字列として条件に指定するときは、記号の前に、さらに「=」をつけます。たとえば「=」という文字列を条件に指定するときは「==」とします。

Sub Macro10()
    Range("A1").AutoFilter 1, "=="
End Sub

「>」と「<」も同様です。先頭に「=」をつけます。

Sub Macro11()
    Range("A1").AutoFilter 1, "=>"
End Sub


Sub Macro12()
    Range("A1").AutoFilter 1, "=<"
End Sub

2つの条件を指定する

条件は、引数Criteria1と引数Criteria2に2つまで指定できます。もし、2つの引数を指定するときは、引数OperatorにxlAndやxlOrなどを指定します。

よく見る間違いですが、AutoFilterrメソッドには、条件を2つしか指定できません。

Range("A1").AutoFilter 1, "田中", xlOr, "広瀬", xlOr, "桜井"    ''エラー

上記のように書くとエラーです。なぜ2つしか指定できないのかというと、Excel 2003までのオートフィルタは、ひとつの列に2つしか条件を指定できなかったからです。Excel 2007になってオートフィルタの仕様が拡張され、ひとつの列に3つ以上の条件を指定できるようになりました。しかし、VBAのAutoFilterメソッドは、Excel 2003以前に作られたメソッドです。ですから、当時は2つで十分だったんです。ひとつの列に3つ以上の条件を指定する書き方は、次項をご覧ください。

Sub Macro13()
    Range("A1").AutoFilter 1, "田中", xlOr, "広瀬"
End Sub

ここで指定した"田中"と"広瀬"は、それぞれ"=田中"と"=広瀬"という意味です。したがって、次のような指定も可能です。

Sub Macro14()
    Range("A1").AutoFilter 1, "<>田中", xlAnd, "<>広瀬"
End Sub

3つ以上の条件を指定する

ひとつの列に、3つ以上の条件を指定する操作をマクロ記録すると、おおむね次のようなコードが記録されます。

Sub Macro15()
    Range("A1").AutoFilter 1, Array("田中", "広瀬", "桜井"), xlFilterValues
End Sub

条件を指定する引数Criteria1には配列を指定します。引数Operatorには、定数xlFilterValuesを指定します。この定数は、ある意味"お約束"です。このように、Array関数を使って配列を指定してもいいですが、Array関数は、項目数を可変にすることができません。今回は3項目で、次は4項目で…のようなケースで、Array関数は使い勝手が悪いです。ひとつの列に3つ以上の条件を指定するときは、引数Criteria1に配列を指定すればいいのですから、この配列は自分で作った方がいいでしょう。

Sub Macro16()
    Dim A(2) As String
    A(0) = "田中"
    A(1) = "広瀬"
    A(2) = "桜井"
    Range("A1").AutoFilter 1, A, xlFilterValues
End Sub

もし条件に指定する項目数が可変になる場合は、動的配列を使います。動的配列に関しては「部屋数を変えられる動的配列」をご覧ください。ここでは、条件が別のセルに入力されているケースで考えてみましょう。実務では"あるある"ですね。

上図のように、条件に指定したい項目がE列に入力されていたとします。

Sub Macro17()
    Dim i As Long
    ReDim A(Cells(Rows.Count, 5).End(xlUp).Row - 1)
    For i = 0 To UBound(A)
        A(i) = Cells(i + 2, 5)
    Next i
    Range("A1").AutoFilter 1, A, xlFilterValues
End Sub

注意が必要なのは、このように、ひとつの列に3つ以上の条件を指定する場合は、必ず「と等しい」という指定しかできないことです。先の考え方で

Sub Macro16()    ''できない
    Dim A(2) As String
    A(0) = "<>田中"
    A(1) = "<>広瀬"
    A(2) = "<>桜井"
    Range("A1").AutoFilter 1, A, xlFilterValues
End Sub

という指定はできませんし、次のようにワイルドカードを使うこともできません。

Sub Macro16()    ''できない
    Dim A(2) As String
    A(0) = "田*"
    A(1) = "広?"
    A(2) = "*桜*"
    Range("A1").AutoFilter 1, A, xlFilterValues
End Sub

これは、手動操作を考えてみれば分かります。

ここで「"田中"ではない」や「"広"で始まる」を指定することはできませんよね。手動でできないことは、VBAでもできません。それは、オートフィルタの仕様だからです。どうしても、そういう条件を指定したいのでしたら「作業列で絞り込む」を参照してください。

前後のスペースは無視される

あと、これはVBAに限った話ではなく、Excelの仕様なんですけど。文字列を条件にして絞り込むとき、セルに入力された文字列の前後に、半角スペースがあっても無視されます。次の表で確認してみましょう。

セルA2には、普通に"田中"と入力しています。文字数を調べるLEN関数も2となっていますね。セルA3には" 田中"と先頭に半角スペースを入れています。セルA4は分かりにくいですが"田中 "と後ろに半角スペースがあります。この表に対して「1列目が"田中"と等しい」という条件で絞り込んでみましょう。

Sub Macro17()
    Range("A1").AutoFilter 1, "田中"
End Sub

結果は次のとおりです。

これは、手動でオートフィルタを操作しているときも同じです。A列の▼ボタンをクリックすると次のようになります。

リストには"田中"しか出てきません。それに、選択した"田中"をよく見ると、前後にスペースも存在していません。オートフィルタでは、文字列の前後にある半角スペースは無視されるということです。ですから、ちなみにですけど、次のようなコードでも"田中"で絞り込まれます。

Sub Macro17()
    Range("A1").AutoFilter 1, "  田中     "
End Sub

もちろん、無視されるのは半角のスペースだけです。次のように全角のスペースが存在した場合は、別の文字列として区別されます。

リストアップされている3つの候補が分かりにくいですから、拡大してみましょう。

このように「半角スペースが無視される」というのは、Excelの中でオートフィルタだけです。なので、半角スペースの存在に気がつかないと、次のように、他の関数や機能と結果が合わない…と慌てることになります。

上図の表は、セルA4だけ"田中 "と後ろにスペースが入っています。まずはこの表を「1列目が"田中"と等しい」で絞り込みます。これは、VBAでも手動操作でも同じことです。

オートフィルタで絞り込んだ結果をSUBTOTAL関数で計算してみます。

見えているとおりの結果になりますね。では、同じ計算をCOUNTIF関数とSUMIF関数でやってみましょう。

結果が異なります。COUNTIF関数とSUMIF関数は、半角スペースが含まれている"田中 "を、"田中"とは別の文字列だと認識します。これは、ピボットテーブルも同じです。

元データに半角のスペースが含まれているかどうかは、目に見えないだけに注意が必要です。