作業列で絞り込む

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

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

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

セルにない情報で絞り込む

下図のような表があったとします。

これを「A列が"平成"の日付である」で絞り込むには、どうしたらいいでしょう。まず、失敗する発想は「A列が"平成"という文字列で始まる」という考え方です。いわゆるワイルドカードですね。実際にやってみましょうか。

Sub Macro1()
    Range("A1").AutoFilter 1, "平成*"
End Sub

うまくいきません。なぜなら、セルに数値や日付が入力されているとワイルドカードは使えないからです。まぁ、平成か令和かでしたら、区切りとなる日付を条件にすることもできます。

Sub Macro2()
    Range("A1").AutoFilter 1, "<2019/5/1"
End Sub

日付で絞り込むやりかたは「日付で絞り込む」をご覧ください。

問題はここからです。ではこの表を「A列の日付が"土曜日"である」で絞り込むには、どうしたらいいでしょう。上に書いたように、ワイルドカードは使えないんです。

Sub Macro3()
    Range("A1").AutoFilter 1, "*土*"    ''失敗
End Sub

さあ、困りました。でも実務では、こうしたケースは日常茶飯事です。このようなとき「(自分のやろうとしていることは)オートフィルタではできない、無理」って諦める人が多いです。ったく…そんなことだから、いつまで経ってもExcelを活用できないんです。よろしいですか？「どうしたらできるか」を考えるんです。それにはまず、"ゴール"を明確にイメージして、その"ゴール"に行き着くには、どうなっていればいいかを考えます。たとえば、次のように「曜日」の列が存在していたら、土曜日で絞り込めますよね。

Sub Macro4()
    Range("A1").AutoFilter 4, "土"
End Sub

「でもぉ…D列に曜日の列はないし・・・」ナニ言ってんですか！なければ作ればいいんですよ。何のためにマクロやってるんですかと。D列に曜日の列を作るには、2つの方法が考えられます。

  1. VBAでA列の曜日を取得してD列に代入する
  2. D列にTEXT関数を代入する

1.の方法だと次のようになりますね。

Sub Macro5()
    Dim i As Long
    Range("D1") = "曜日"
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(i, 4) = Format(Cells(i, 1), "aaa")
    Next i
End Sub

2.の考え方だと、もっと簡単になります。

Sub Macro6()
    Range("D1") = "曜日"
    Range(Range("D2"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 3)) = "=TEXT(A2,""aaa"")"
End Sub

「"=TEXT(A2,""aaa"")"」の「""aaa""」に、なんでダブルコーテーションが2つあるのか分からない方は「ダブルコーテーションの表示」を、よく読んでください。

ちなみに、2.のTEXT関数を代入する方が速いです。10万件のセルに日付を入力して試してみました。1.のFor Nextが「4.86秒」だったのに対して、2.のTEXT関数は「0.172秒」でした。

今回の「A列の日付が"土曜日"である」で絞り込むマクロは、次のようになります。

Sub Macro7()
    Range("D1") = "曜日"
    Range(Range("D2"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 3)) = "=TEXT(A2,""aaa"")"
    Range("A1").AutoFilter 4, "土"
End Sub

絞り込んだ結果をコピーするなり、件数をカウントするなり、何らかの処理をしたら「Range("D1").EntireColumn.Delete」や「Range(Range("D2"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 3)) = ""」などのようにしてD列をクリアしてください。

発想しだいで何でもできる

あとは発想しだいです。絞り込むために必要なデータを、自分で作ってやればいいんです。いくつか例をお見せしましょう。

以前、コンサルタントでお邪魔した某大手企業で、既存マクロのレビューをしたとき、何やら変なことをしているマクロを見かけました。詳しくは書きませんが、要するに「自分自身のブックをDBとして開き、そこにクエリーをかける」みたいなマクロです。話を聞くと、やりたいことは要するにオートフィルタ的な作業です。必要なデータだけを抽出したいんです。でも「条件がすごく複雑なのでオートフィルタでは不可能なんです」とのこと。心の中で「んなわきゃーねーのにな」って思いましたけどね。オートフィルタを普通に使っては絞り込めない"複雑な条件"とやらをやってみましょうか。

下図のような表を、次のような条件で絞り込んでみます。

  1. A列が「"田中"ではない」かつ「"広瀬"ではない」かつ「"桜井"ではない」
  2. もしB列が"A"だったら、C列が「900より大きい」
  3. もしB列が"B"だったら、C列が「200より小さい」

う～ん、複雑ですね～w まず1.の条件で、いきなりオートフィルタの標準機能ではアウトです。ひとつの列に3つ以上の条件を指定するときは「と等しい」しか指定できないからです。さらに、B列の文字列によって、C列の条件を指定するなんて。ああ、なんと複雑な条件なんでしょうw これは、For Nextを使ってVBAだけで条件を作る方が簡単ですね。

Sub Macro8()
    Dim i As Long
    Range("D1") = "判定"
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Select Case True
        Case Cells(i, 1) <> "田中" And Cells(i, 1) <> "広瀬" And Cells(i, 1) <> "桜井"
            Select Case Cells(i, 2)
            Case "A"
                If Cells(i, 3) > 900 Then Cells(i, 4) = "○"
            Case "B"
                If Cells(i, 3) < 200 Then Cells(i, 4) = "○"
            End Select
        End Select
    Next i
    Range("A1").AutoFilter 4, "○"
End Sub

ちなみに、セルにワークシート関数を代入するには、上記の条件を判定する数式を作らなければなりません。こっちの方が難しいかも。一応…。

Sub Macro9()
    Range("D1") = "判定"
    Range(Range("D2"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 3)) = _
        "=IF(AND(A2<>""田中"",A2<>""広瀬"",A2<>""桜井""),IF(OR(AND(B2=""A"",C2>900),AND(B2=""B"",C2<200)),""○"",""""),"""")"
    Range("A1").AutoFilter 4, "○"
End Sub

もうひとつ。オートフィルタの標準機能では絶対にできない絞り込みをやってみましょう。

A列が適当な色で塗りつぶされています。このうち「A列が"赤ではない"かつ"青ではない"かつ"何かの色が塗られている"」セルだけで絞り込んでみます。

Sub Macro10()
    Dim i As Long
    Range("D1") = "判定"
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        With Cells(i, 1).Interior
            If .Color <> RGB(255, 0, 0) And .Color <> RGB(0, 0, 255) Then
                If .Color <> RGB(255, 255, 255) Then Cells(i, 4) = "○"
            End If
        End With
    Next i
    Range("A1").AutoFilter 4, "○"
End Sub

マクロの作成に限りません。数式を作るときも、ピボットテーブルなどの機能を使うときも、思うようにExcelを活用できない人は、与えられた"実データだけ"で、何とかしようと考えています。それは無理です。Excelだって万能ではありません。そうではなく、"どうしたい"や"何を作りたい"などのゴールを明確にして、そのゴールに行き着くためにはどんなデータが必要かを考えます。そして、そのデータがなければ自分で作ります。それがExcelの使い方です。