色で絞り込む


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

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

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

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

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

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