色で絞り込む


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

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

「背景色」か「文字色」か

ここでは、下図のような表で解説します。

色による絞り込みを手動でやるなら、次のように操作します。

これを見て分かるように、オートフィルタの条件に色を指定するときは、セルの「背景色」に設定されている色か、セルの「文字色」に設定されている色かの、どちらかを選ばなければなりません。両方同時にってのは指定できないです。両者は、引数Operatorに次の定数を指定することで動作を切り替えます。

まずは、A列に設定されている"背景色"で絞り込んでみましょう。A列には現在「赤」と「黄」の背景色が設定されています。最初は「赤」からいきましょうか。

Sub Macro1()
    Range("A1").AutoFilter 1, 赤色, xlFilterCellColor
End Sub

問題は「赤色」をどう書けばいいかです。

赤なんだから、何とかレッドみたく書けばいいんじゃね?って考える方もいるでしょう。でもね、色って複雑です。世の中にはいろいろな色があります(←ダジャレではありません)。何とかレッドとか、何とかイエローみたいに言える単純な色ばかりじゃありませんよね。戦隊ヒーローじゃないんですから。

色には三原色があります。

すみません、これ正確には「光の三原色」です。「色の三原色」はシアン・マゼンダ・イエローです。ということなんですけど、話を分かりやすくするために、ここでは赤・緑・青で説明します。

コンピュータは、この三原色を用いて画面を描画しています。画面上の小さいドットひとつずつに対して「赤がどれくらい、緑がどれくらい、青がどれくらい」という混合色を表示することによって、綺麗なフルカラーの写真などを表現しているんです。この「赤がどれくらい、緑がどれくらい、青がどれくらい」という混合色を、コンピュータが理解できる数値で表現したものをRGB値などと呼びます。

Sub Macro1()
    Range("A1").AutoFilter 1, 赤色, xlFilterCellColor
End Sub

の「色」のところには、このRGB値を指定します。VBAには「赤がどれくらい、緑がどれくらい、青がどれくらい」という3つの色の割合からRGB値を求めるために、専用の関数が用意されています。それがRGB関数です。

余談ですが。赤・緑・青のそれぞれに、0から255の数値を指定するので、これで表現できる色数は、256×256×256=16,777,216色です。この、約1,600万色のことをフルカラーとかトゥルーカラーなどと呼びます。

さて、引数Criteria1に「赤色」を表すRGB値を指定します。言うまでもなく「赤色」は「赤=225, 緑=0, 青=0」です。したがって、次のようになります。

Sub Macro1()
    Range("A1").AutoFilter 1, RGB(255, 0, 0), xlFilterCellColor
End Sub

では「黄色」はどうでしょう。さて問題です。「黄色」って何色と何色を混ぜるとできるんでしたっけ?自信のない方は調べましょう。背景色に黄色が設定されているセルを選択して、[塗りつぶしの色]ボタンから[その他の色]を選択します。

表示される[色の設定]ダイアログボックスの[ユーザー設定]タブを開くと、そこにRGBの割合が示されています。

Sub Macro2()
    Range("A1").AutoFilter 1, RGB(255, 255, 0), xlFilterCellColor
End Sub

「文字色」も考え方は同じです。まずは、設定されている色のRGB値を調べましょう。今度は「文字色」ですから[フォントの色]ボタンの[その他の色]を選択します。

Sub Macro3()
    Range("A1").AutoFilter 1, RGB(192, 0, 0), xlFilterFontColor
End Sub

Sub Macro4()
    Range("A1").AutoFilter 1, RGB(0, 112, 192), xlFilterFontColor
End Sub

「色なし」で絞り込む

背景色と文字色には、それぞれ「塗りつぶしなし」と「自動」があります。

両者は、それぞれ次のように指定します。まずは「塗りつぶしなし」から。

Sub Macro5()
    Range("A1").AutoFilter Field:=1, Operator:=xlFilterNoFill
End Sub

「塗りつぶしなし」で絞り込むには、引数Operatorに定数xlFilterNoFillを指定します。このとき、引数Criteria1は指定しません。したがって、引数の指定が飛び飛びになります。この場合は、名前付き引数名「○○:=」を省略できません。次に、フォントの色の「自動」です。

Sub Macro6()
    Range("A1").AutoFilter Field:=1, Operator:=xlFilterAutomaticFontColor
End Sub

引数OperatorにxlFilterAutomaticFontColorを指定します。このときも、引数の指定が飛び飛びになりますから、名前付き引数名を記述します。

条件付き書式の色で絞り込む

実務では、セルに直接色を設定するだけでなく、条件付き書式によって、自動的に背景色や文字色を変えることが多いです。では、そうした、条件付き書式によって設定されている色で絞り込むには、どうしたらいいでしょう。これ、けっこう難しいですから、参考程度に読んでくださいね。

上図の表は、B列に2つの条件付き書式を設定しています。

では、この表で「数値が80より大きい=青色の背景色」の件数と、「数値が50より小さい=黄色の背景色」の件数を、それぞれ求めてみましょう。これ、手動操作でしたら、色で絞り込みますよね。これをマクロでやっちゃおうということです。

で、たとえば、下図のようにします。D列の表記は手抜きです。要するに意味が分かればいいかと。なお、セル範囲D1:E1にはあらかじめタイトル「条件」「個数」を入力しておきます。

条件付き書式を表すFormatConditionオブジェクトについては、詳細な解説を割愛します。

Sub Macro8()
    Dim i As Long
    With Range("B2:B21").FormatConditions
        For i = 1 To .Count
            Range("A1").AutoFilter 2, .Item(i).Interior.Color, xlFilterCellColor
            Cells(i + 1, 4) = .Item(i).Formula1
            Cells(i + 1, 5) = WorksheetFunction.Subtotal(3, Range("B:B")) - 1
        Next i
    End With
    Range("A1").AutoFilter
End Sub

セルの塗りつぶし色+パターン で絞り込む

オートフィルタを、色で絞り込む件に関して、最初は上記のところまで書きました。まぁ、これくらいで十分かなって。でも、先日VBAのセミナーで、受講された木下さんから「セルの塗りつぶし色+パターンで絞り込むにはどうしたらいいんですか?」と質問されました。正直に言って、そんなこと考えたこともありませんでした。もちろん、セルの塗りつぶし(背景色)に、パターンを設定できるということは知っていました。同じ色の塗りつぶしであっても、パターンが異なることもあり得ます。考えたことがないのですから、もちろん試したこともありませんでした。こういう、私にとっても初めてというテーマに出会えるのですから、だからセミナーで受ける質問は楽しいです。「えぇ!そこ?」みたいな質問をされると、ワクワクします。さて「塗りつぶし色+パターン」での絞り込みですが、何となく(たぶん、こんな感じかな~)みたいな想像をしながら、実際に試してみました。

まずは、そもそも「塗りつぶしのパターンで絞り込みができるのか?」を確認してみます。ここでは、上図のようなデータを作成しました。この状態でA列のオートフィルタ矢印ボタンをクリックして、[色フィルター]を開いてみると、下図のようになっています。

ここから分かるように、オートフィルタでは「塗りつぶしのパターン」で絞り込むことが可能です。さて、では「赤い塗りつぶし+白いドット」の色で絞り込みをして、それをマクロ記録してみます。

Sub Macro1()
    ActiveSheet.Range("$A$1:$B$8").AutoFilter Field:=1, Criteria1:=RGB(0, 0, 0) _
        , Operator:=xlFilterCellColor
    With ActiveSheet.AutoFilter.Filters(1).Criteria1
        .Pattern = xlGray8
        .PatternColor = 16777215
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

これ、セミナーの教室で、実際に記録してみたんですけど、コード見た瞬間「あ、やばい!」って感じました。オートフィルタの沼に潜るやつやん、ビギナーに見せちゃアカンやつやん!ってw。まずは、読みにくいので、冒頭の2行あたりを整理しましょう。

Sub Macro1()
    Range("A1").AutoFilter 1, RGB(0, 0, 0), xlFilterCellColor
    With ActiveSheet.AutoFilter.Filters(1).Criteria1
        .Pattern = xlGray8
        .PatternColor = 16777215
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

1行目の「Range("A1").AutoFilter 1, RGB(0, 0, 0), xlFilterCellColor」は、「セルA1を含む表に対して、1列目を、セルの背景色が、RGB(0, 0, 0)で絞り込め」という意味です。ちなみに「RGB(0, 0, 0)」はです。ん?黒?今回の操作、塗りつぶしの色は"赤色"なんですけど。ま、いいや。先を見てみましょう。

次の「With ActiveSheet.AutoFilter.Filters(1).Criteria1」は難しいです。ここで、新しいことを書きますので、そのつもりで読んでください。今までの解説でも、たびたび「AutoFilter」という単語が登場しました。たとえば、

Range("A1").AutoFilter 2,"田中"

みたいに。よろしいですか?これから超絶にややこしい話をしますから、覚悟してくださいね。ここで使われているAutoFilterはメソッドです。「オートフィルターで絞り込め」という動作を表しています。しかし、先ほどマクロ記録で記録された

With ActiveSheet.AutoFilter.Filters(1).Criteria1

のAutoFilterはプロパティです。このAutoFilterプロパティは、AutoFilterオブジェクトを返します。AutoFilterオブジェクトというのは、いわば「オートフィルタで操作されるセル範囲全体」を表します。オートフィルタによって、どの列からどの列までが絞り込まれるか、その絞り込みで指定されている条件は何か、などなど諸々の「オートフィルタに関する情報全体」みたいなイメージです。ちなみに、1つのワークシートでは、1つのオートフィルタしか指定できませんので、だから「ActiveSheet.AutoFilter」のように、シート名を必ず指定しなければなりません。つまり、マクロ記録された「ActiveSheet.AutoFilter」というのは、アクティブシートの"オートフィルタ情報"みたいな意味です。そして、これはAutoFilterオブジェクトなのですから、そのオブジェクトにはプロパティやメソッドがあります。続けて記述されている「Filters(1)」というのは、「1つめの列」今回ではA列のことです。その「1つめの列」に、どんな条件が指定されているかを表しているのが、最後の「Criteria1」プロパティです。これを調べることで、どんな条件で指定されているかがわかります。

Sub Macro1()
    Range("A1").AutoFilter 1, RGB(0, 0, 0), xlFilterCellColor
    With ActiveSheet.AutoFilter.Filters(1).Criteria1
        .Pattern = xlGray8          ''パターンの種類
        .PatternColor = 16777215    ''パターンの色
        .Color = 255                ''セルの塗りつぶし色(←255は赤を表している)
        .TintAndShade = 0           ''セル色の明暗
        .PatternTintAndShade = 0    ''パターン色の明暗
    End With
End Sub

この「Criteria1」プロパティは、オートフィルタで"どんな"絞り込みを行ったのかによって、内容(というか実体)が異なります。

上図のようなリストを用意して、まずは「1列目を"田中"と等しい」で絞り込んでみましょう。ローカルウィンドウで確認してみると、次のようになっています。

Criteria1プロパティは"文字列型"になっています。ちなみに「Range("A1").AutoFilter 3, 200」のように、条件に数値を指定しても

このように、同じ"文字列型"です。このへんはセミナーで解説しているように「条件は文字列形式で指定する」という原則と一致しています。では、1つの列に対して3項目以上で絞り込んでみます。「Range("A1").AutoFilter 2, Array("A", "B", "C"), xlFilterValues」としてみました。

Criteria1プロパティの中も配列になります。では最後に、1列目を「赤い塗りつぶし」で絞り込んでみましょう。「Range("A1").AutoFilter 1, 255, xlFilterCellColor」です。色のところは面倒くさいので255としました。本来なら RGB(255, 0, 0) です。

Criteria1のところが「Object/Interior」となっていることに留意してください。このCriteria1プロパティは、Interiorオブジェクトを返しています。なるほど、マクロ記録したとき、冒頭で「Range("A1").AutoFilter 1, RGB(0, 0, 0), xlFilterCellColor」と"黒"で絞り込む、というコードが記録された理由が分かりました。別に"黒"じゃなくてもいいんです。何色であっても、とにかく色(今回は、セルの塗りつぶし色)で絞り込んでやります。いわばダミーです。その結果、Criteria1はInteriorオブジェクトになります。なってくれないと、パターンなどのプロパティを設定できないからです。なんか、Microsoftの"苦肉の策"を感じますねw まぁ、いずれにしても、めったにやらないでしょうけど「セルの塗りつぶし色+パターン」で絞り込むには、このように考えてください。

1つのセルに複数の色が設定されている場合

ExcelやVBAに詳しくなりたかったら、何はともあれ「実際にやってみる」ことが大事です。あれこれと脳内で妄想して「じゃ、こういうケースはどーなるんだろ?」「もし、こうしたらどーなるんだろ?」みたいに、実際に試してみてください。オートフィルタを色で絞り込む件に関しても、たくさんのケースを想定しました。そんな中のひとつに「もし、1つのセルに複数の色が設定されていたら、どーなるんだろ?」ということも考えました。たとえば次のような状態です。

こんなことはできません。上図は合成です。「それは無理か、できねーしw」と、そのときは可能性を否定したのですが、先日のセミナーに参加された葛西さんから「もし、こういう状態だったら、1つのセルに複数の色が存在しますよね」と質問されました。いやはや恥ずかしい、プロとして失格です。その可能性に気づかなかったとは。確かに、1つのセルに複数の色が設定されるケースは存在します。たとえば、次のような状態です。

オートフィルタで指定する色って、たいていは「セルの塗りつぶし色」です。でも、もちろん「フォントの色」を指定することもできます。そして、フォントの色に関しては、1つのセル内で複数の色を指定することも可能です。この質問を受けたとき、このケースを想定できなかった悔しさと、これでまた新しい知識が増えるというワクワク感がありました。

まずは、この状態の「色フィルター」を確認してみましょう。

なるほど、部分的に設定されているかどうかは関係ないのですね。現在設定されている2色しかリストアップされていません。では、試しに"赤"をクリックしてみます。

もちろん、"青"をクリックしても同様です。

マクロで実行しても結果は同じでした。

Sub Macro1()
    Range("A1").AutoFilter 1, 255, xlFilterFontColor
End Sub

フォントの色で絞り込むときは「その色を含む」という結果になるんですね。逆に「先頭3文字が赤」のような指定はできないと。ちなみに、先と同じようにCriteria1プロパティも調べてみましょう。

ほほぉ、これは意外でした。てっきり「文字色で絞り込み」を行うとCriteria1プロパティはFontオブジェクトを返すのかと思っていました。このへんは「塗りつぶし色での絞り込み」とは異なるんですね。

まぁ、いずれにしても、オートフィルタで「文字色で絞り込み」を行うときは「その色を含む」という結果になります。これは、手動でもマクロでも同じです。それだけは覚えておきましょう。