色で絞り込む
大量のデータを絞り込むには、オートフィルタが便利です。ここでは、VBAからオートフィルタを使い倒すテクニックをご紹介します。なお、ボリュームがありますので、以下の項目にページを分けて解説します。なお、ここで解説するオートフィルタは、通常のワークシートに設定されているとします。テーブルでオートフィルタを操作するときは、また別の考え方が必要です。VBAでテーブルを扱う方法に関しては「VBAでテーブルの操作」をご覧ください。
- 書き方の基本
- 文字列で絞り込む
- 数値で絞り込む
- 日付で絞り込む
- 色で絞り込む (←このページ)
- 作業列で絞り込む
- 絞り込んだ結果をコピーする
- 絞り込んだ結果を集計する
- 絞り込んだ結果の行を操作する
- オートフィルタの状況を判定する
- 数式のエラーで絞り込む
「背景色」か「文字色」か
ここでは、下図のような表で解説します。
色による絞り込みを手動でやるなら、次のように操作します。
これを見て分かるように、オートフィルタの条件に色を指定するときは、セルの「背景色」に設定されている色か、セルの「文字色」に設定されている色かの、どちらかを選ばなければなりません。両方同時にってのは指定できないです。両者は、引数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
これ、セミナーの教室で、実際に記録してみたんですけど、コード見た瞬間「あ、やばい！」って感じました。オートフィルタの沼に潜るやつやん、ビギナーに見せちゃアカンやつやん！ってｗ。まずは、読みにくいので、冒頭の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の"苦肉の策"を感じますねｗ まぁ、いずれにしても、めったにやらないでしょうけど「セルの塗りつぶし色＋パターン」で絞り込むには、このように考えてください。
1つのセルに複数の色が設定されている場合
ExcelやVBAに詳しくなりたかったら、何はともあれ「実際にやってみる」ことが大事です。あれこれと脳内で妄想して「じゃ、こういうケースはどーなるんだろ？」「もし、こうしたらどーなるんだろ？」みたいに、実際に試してみてください。オートフィルタを色で絞り込む件に関しても、たくさんのケースを想定しました。そんな中のひとつに「もし、1つのセルに複数の色が設定されていたら、どーなるんだろ？」ということも考えました。たとえば次のような状態です。
こんなことはできません。上図は合成です。「それは無理か、できねーしｗ」と、そのときは可能性を否定したのですが、先日のセミナーに参加された葛西さんから「もし、こういう状態だったら、1つのセルに複数の色が存在しますよね」と質問されました。いやはや恥ずかしい、プロとして失格です。その可能性に気づかなかったとは。確かに、1つのセルに複数の色が設定されるケースは存在します。たとえば、次のような状態です。
オートフィルタで指定する色って、たいていは「セルの塗りつぶし色」です。でも、もちろん「フォントの色」を指定することもできます。そして、フォントの色に関しては、1つのセル内で複数の色を指定することも可能です。この質問を受けたとき、このケースを想定できなかった悔しさと、これでまた新しい知識が増えるというワクワク感がありました。
まずは、この状態の「色フィルター」を確認してみましょう。
なるほど、部分的に設定されているかどうかは関係ないのですね。現在設定されている2色しかリストアップされていません。では、試しに"赤"をクリックしてみます。
もちろん、"青"をクリックしても同様です。
マクロで実行しても結果は同じでした。
Sub Macro1()
Range("A1").AutoFilter 1, 255, xlFilterFontColor
End Sub
フォントの色で絞り込むときは「その色を含む」という結果になるんですね。逆に「先頭3文字が赤」のような指定はできないと。ちなみに、先と同じようにCriteria1プロパティも調べてみましょう。
ほほぉ、これは意外でした。てっきり「文字色で絞り込み」を行うとCriteria1プロパティはFontオブジェクトを返すのかと思っていました。このへんは「塗りつぶし色での絞り込み」とは異なるんですね。
まぁ、いずれにしても、オートフィルタで「文字色で絞り込み」を行うときは「その色を含む」という結果になります。これは、手動でもマクロでも同じです。それだけは覚えておきましょう。