【Excel VBA】文字列とセルの塗り潰しの色を同時に集計するオリジナル関数を作る

目次

このページで書くこと

文字列とセルの塗り潰しの色を同時に集計するオリジナル関数を作ります。

経緯

さっそくなのですが、職場にこのような表はありませんか？

セルの塗り潰しで状況を分けている表

予定や状況をセルの塗り潰しの色で分けている表です。

そしてその表をこのような感じで集計しなければいけないときはありませんか？

文字列とセルの塗り潰しの色を同時に集計する

たとえば、完了なら「1」、延期なら「2」、未定は「3」とフラグが立ててあれば集計はCOUNTIFS関数で簡単にできるのですが、当然ながらCOUNTIFS関数ではセルの塗り潰しの色で集計することはできません。

なら、集計しやすいように表を変えてしまえばいいのかと言うと、そうでもないのです。
フラグで状況が表されている表って、ぱっと見て全体像を把握しにくいんですよね。
色分けしてある表は、人間にとってはとても読みやすいものだと思います。

でも手作業で数えて集計なんてやってられません。
なので、色と文字を同時に集計できるオリジナル関数を作りました。

使用するデータ

上で出した表をサンプルデータとして使用します。
「【Excel VBA】Excelでツールファイルを作る」というページで制作したデータを流用しています。
arekoretips.hatenablog.jp

使用するコード

今回は「Sub」ではなく「Function」です。
引数の部分が、実際に関数を使用するときの選択セルになります。

Function CountColor(集計色範囲 As Range, 条件色セル As Range, 集計範囲 As Range, 条件セル As Range)
    Application.Volatile
    '
    CountColor = 0
    '
    Dim y, x, i
    For y = 1 To 集計色範囲.Columns.Count
        For x = 1 To 集計色範囲.Rows.Count
            If 集計色範囲.Rows(x).Columns(y).Interior.Color = 条件色セル.Interior.Color Then
                For i = 1 To 集計範囲.Columns.Count
                    If 集計範囲.Rows(x).Columns(i).Value = 条件セル.Value Then
                        CountColor = CountColor + 1
                    End If
                Next i
            End If
        Next x
    Next y
End Function

関数の使い方

引数のネーミングセンスが悪く、なにをどう使えばいいのか謎だと思いますので、使い方の解説をします。

集計色範囲	集計したい色の範囲
条件色セル	集計したい色のセル
集計範囲	集計したい文字の範囲
条件セル	集計したい文字のセル

「県別」シート

「集計」シート

なので、たとえば今回のような集計をしたい場合は、以下のような書き方になります。

CountColor(集計色範囲,条件色セル,集計範囲,条件セル)
↓つまり↓
CountColor(県別!$D$2:$D$34,県別!$F$2,県別!$B$2:$B$34,$B2)

備考

「Application.Volatile」はこのマクロを通常の関数のように動かしてくれるメソッドです（正確には「Volatile」がメソッドです）。
集計範囲内のセルの色を変更したり、セルの値を変更したときに自動で計算し直してくれるのはこのメソッドのおかげです。

次に選択した「集計色範囲」がどの範囲なのかを認識させています（For y = 1 To 集計色範囲.Columns.Count、For x = 1 To 集計色範囲.Rows.Count）。
ここの「1」はつまり、1番目に選択したセル（D2セル）ということです。

そして決められた範囲に選択したセルと同じ色のセルがあったら（If 集計色範囲.Rows(x).Columns(y).Interior.Color = 条件色セル.Interior.Color Then）、次のFor文に移ります。

「集計範囲」の部分でも、やっていることは「集計色範囲」と同じです。
ただし、「集計色範囲」と「集計範囲」の行は同じにしなければ正しく集計できないので、先ほど認識させた「集計色範囲」のRowの値を再利用します（集計範囲.Rows(x)）。

今度も先ほどと同じように、決められた範囲に選択したセルと同じ値のセルがあるかをチェックします（If 集計範囲.Rows(x).Columns(i).Value = 条件セル.Value Then）。

この条件をすべてクリアするとようやく1数えられるというわけです（CountColor = CountColor + 1）。

処理後は、拡張子の修正（xlsm→xlsx）を忘れずに！

以上です。