絞り込んだ結果を集計する

  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列が"田中"である」で絞り込み、その結果をSheet2のセルA1にコピーするとします。絞り込んだ結果をコピーする考え方は「絞り込んだ結果をコピーする」をご覧ください。

Sub Macro1()
    With Range("A1")
        .AutoFilter 1, "田中"
        .CurrentRegion.Copy Sheets("Sheet2").Range("A1")
        .AutoFilter
    End With
End Sub

結果は、こうなります。

そう、実はA列に"田中"は1件もなかったんです。これでは、コピーする必要がありません。こんなときは、次のように考えたいです。

Sub Macro1()
    With Range("A1")
        .AutoFilter 1, "田中"
        If 絞り込んだ結果 > 0 Then
            .CurrentRegion.Copy Sheets("Sheet2").Range("A1")
            .AutoFilter
        End If
    End With
End Sub

絞り込んだ結果が何件あるかは、要するに「表示されているセルが何個あるか」です。だったら、次のように調べることも可能です。

Sub Macro2()
    MsgBox Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).Count
End Sub

でもこれ、めんどくさくないですか？上記のコードを、スッと迷わずに書ける人ばかりではないでしょう。実は、こんなときはワークシート関数を使うと一発で分かるんです。使うのはSUBTOTAL関数です。SUBTOTAL関数に関しては「この関数はこう使え[SUBTOTAL関数]」をご覧ください。計算の種類に指定する数値の、1番台と100番台の違いも解説しています。

Sub Macro3()
    With Range("A1")
        .AutoFilter 1, "田中"
        If WorksheetFunction.Subtotal(3, Range("A:A")) > 1 Then
            .CurrentRegion.Copy Sheets("Sheet2").Range("A1")
            .AutoFilter
        End If
    End With
End Sub

注意していただきたいのは、SUBTOTAL関数の結果が「1より大きかったら」と判定しているところです。「0」ではありません。SUBTOTAL関数に指定している範囲は「A列全体(Range("A:A"))」です。ということは、ここにはタイトル行も含まれます。だから、SUBTOTAL関数の結果が「1」だったら、それは「タイトル行だけ」つまり「"田中"はない」ということです。

基本的な考え方は以上です。

柔軟な発想をする

何となく世間では「VBAができるとExcelの上級者」みたいに思われる節があります。まったく違います。まるで違います。ぜんぜん違います。「俺、VBAできるからExcel上級者だしw ピボットテーブルとか使わないしw 俺VBAできるからw 関数？ああ知らないしw 俺VBAできるからw」なんて。もうね、へそがプーアール茶を沸かしますw これは、車の運転(ドライビング)に例えるなら、次のように言っているのと同じです。「俺、左折できるからドライビング上級者だしw 右折とかしないしw 俺左折できるからw 車庫入れ？ああ知らないしw 俺左折できるからw」みたいなw そしてこの人は、どこへ目指すにも左折だけで行こうとします。われわれは、左折の大会に出るのではありません。VBAのコンテストに出るんじゃないんです。Excelという道具を使って、実務を遂行するのが目的です。だったらVBAだけじゃなくて、機能や関数も使えなかったら仕事になりません。こんなこと、言うまでもないことです。

だから、柔軟な発想をしてください。上記のように、オートフィルタで絞り込んだ結果が何件あるかは、ワークシート関数を使えば一発です。もし、VBAしかできなくて、SUBTOTAL関数の存在を知らなかったら、さっきのマクロは作れません。

先の「A列を"田中"で絞り込んだ結果」が0件だったらコピーしないというマクロは、次のような考え方でした。

  1. A列を"田中"絞り込んでみる
  2. その結果が0件じゃなかったら
  3. コピーする

これだと、たとえA列に"田中"が存在しなくても、とりあえず1回はオートフィルタかけます。存在しないんだったら、そもそもオートフィルタ自体無駄ですね。だったら、次のように考えることもできます。

  1. もしA列に"田中"が1件以上存在していたら
  2. A列を"田中"絞り込む
  3. その結果をコピーする

A列に"田中"が存在するかどうかなんて、そんなもん簡単に分かります。VBAだけでやってみましょうか。

Sub Macro4()
    Dim FoundCell As Range
    Set FoundCell = Range("A:A").Find(What:="田中", Lookat:=xlWhole)
    If FoundCell Is Nothing Then
        MsgBox "田中は存在しません"
        Exit Sub
    Else
        With Range("A1")
            .AutoFilter 1, "田中"
            .CurrentRegion.Copy Sheets("Sheet2").Range("A1")
            .AutoFilter
        End With
    End If
End Sub

もちろん、これでもできます。でもね、A列に"田中"が存在するかどうかなんて、Findステートメントを使うまでもなく、ワークシート関数で一発です。

Sub Macro5()
    If WorksheetFunction.CountIf(Range("A:A"), "田中") = 0 Then
        MsgBox "田中は存在しません"
        Exit Sub
    Else
        With Range("A1")
            .AutoFilter 1, "田中"
            .CurrentRegion.Copy Sheets("Sheet2").Range("A1")
            .AutoFilter
        End With
    End If
End Sub

マクロで、どこかの範囲に、何かのデータが"存在するかどうか"を判定することはよくあります。そんなとき、COUNTIF関数が最強です。For Nextで回して1件ずつ調べたら時間がかかります。Findステートメントはオブジェクト変数を宣言しなければなりませんし、何よりも「Is Nothing」という判定をしなければなりません。COUNTIF関数は存在しないと0を返します。つまり、存在しなくてもエラーにならないんです。その分コードが短く、シンプルになります。

ほかのケースで考えてみましょうか。

さきほど例にした上図の表から、次のような集計をしてみましょう。

実務ではこういうとき、元の表とは別シートに集計することが多いですが、ここでは解説を簡単にするため、同じシート上に集計するとします。まずは、オートフィルタだけでやってみます。

Sub Macro6()
    Dim i As Long
    For i = 2 To 13
        Range("A1").AutoFilter 1, Cells(i, 5)
        Range("A1").AutoFilter 2, Cells(i, 6)
        Cells(i, 7) = WorksheetFunction.Subtotal(3, Range("A:A")) - 1
    Next i
    Range("A1").AutoFilter
End Sub

まぁ、これでも十分短いコードですが。でもね、[名前]が"広瀬"で、[記号]が"A"の件数って、それCOUNTIFS関数で分かりますよね。

だから、次のコードでいけます。

Sub Macro7()
    With Range("G2:G13")
        .Value = "=COUNTIFS(A:A,E2,B:B,F2)"
        .Value = .Value
    End With
End Sub

同じ結果になります。もちろん、COUNTIFS関数じゃなくてSUMIFS関数を使えば合計も計算できます。

じゃ、今度は下図のような集計だったら。

これも考え方は一緒です。いかに、ワークシート関数のことや、絶対参照/相対参照や、複数セルへの一括入力という機能などを知っているかがポイントです。VBAじゃありません。

Sub Macro8()
    With Range("F2:H5")
        .Value = "=COUNTIFS($A:$A,$E2,$B:$B,F$1)"
        .Value = .Value
    End With
End Sub

Excelは「機能・関数・VBA」という3つの要素で構成されています。これら3つの要素をすべて使えるのが理想です。「VBAはできるけどワークシート関数は知らない」とか「ピボットテーブルは得意だけどVBAは苦手」のように、どれかしか使えないと、Excelのポテンシャルを最大限に引き出すことはできません。ぜひExcelの「機能・関数・VBA」をすべてマスターしてください。ちなみに、すべて完璧に分かっている者から言わせていただくと、VBAが一番簡単です。最も難しいのはダントツでワークシート関数です。間違いありません。