Excel VBA(マクロ)で色のついたセルの数をカウントする、数値の合計を求める方法

leaves IT

Excelの背景色の数を数える方法として、フィルターを使う方法および検索して数える方法を紹介しましたが、マクロで数えることもできます。
開発タブが表示されていること、マクロが編集できることが前提の記事となります。
作り方つらつら書いてますが、コピペしてすぐ使い方は方法②の完成品をどうぞ。
マクロの準備と編集画面の開き方

スポンサーリンク

方法①For Nextで上から下まで数えるやりかた

色が一致したらカウント

vba 色 カウント1
左の図の色がついているセルの数を数えて、色見本の右にその数を表示したいと思います。
' vba
Sub macro1()
For i = 1 To 100

Next
End Sub
B列に色のついたセルが上から下にずらっと並んでいる場合、For Next文を使い、B列を上から順にチェックしていく方法が使えます。仮に1~100行としてますが、数字は適宜調節できます。マクロ名(macro1)も好きな名前が付けられます。
vba 色 カウント2
B列は数字で言うと左から2番目になり、
vba 色 カウント4
カウントしたい3色のうち、緑色は上から2番目、左から4番目のセル(D4)にあるので、
' vba
Sub macro1()
For i = 1 To 100
If Cells(i, 2).Interior.Color = Cells(2, 4).Interior.Color Then

End If
Next
End Sub
For Next文の中にさらにIf文を置いて条件分岐をします。
Interior.Colorはセルの背景色を取得する命令です。
For Next文は処理を繰り返すたびにiの数値が1から順に1ずつ大きくなっていくので、
1回目の処理ではB1セルとD4セルの背景色を比較し、
2回目の処理ではB2セルとD4セルの背景色を比較します。
これを100回繰り返します。
' vba
Sub macro1()
For i = 1 To 100
If Cells(i, 2).Interior.Color = Cells(2, 4).Interior.Color Then
Cells(2, 5).Value = Cells(2, 5).Value + 1
End If
Next
End Sub
もし色が一致した場合は、色の右隣(上から2、左から5番目のセル=E2)の数値を+1します。空白のセルは0として扱われます。
vba 色 カウント3
というコードを実行するとこうなります。

他のセルも加算

黄色、青も判定させると、こんな感じになります。

' vba
For i = 1 To 100

'緑
If Cells(i, 2).Interior.Color = Cells(2, 4).Interior.Color Then
Cells(2, 5).Value = Cells(2, 5).Value + 1

'黄
ElseIf Cells(i, 2).Interior.Color = Cells(3, 4).Interior.Color Then
Cells(3, 5).Value = Cells(3, 5).Value + 1

'青
ElseIf Cells(i, 2).Interior.Color = Cells(4, 4).Interior.Color Then
Cells(4, 5).Value = Cells(4, 5).Value + 1

End If
Next

クリアの実装

vba 色 カウント5
とりあえずこれで初回はちゃんと動くんですが、このコードは元の数に足し算するだけのコードなので、
vba 色 カウント6
もう一回処理を実行すると、元の数にさらに加算されておかしなことになります。
vba 色 カウント7
これを防ぐために、処理を始める前に値をクリアしておきます。
値をクリアするにはClearContentsメソッドを使います。
Cellsは単体だと範囲指定ができないので、代わりにRangeを使ってRange("D2:D4").ClearContentsのように指定するか、
' vba
Sub macro1()
Range(Cells(2, 5), Cells(4, 5)).ClearContents
またはRangeの中に範囲をCellsで指定するという方法もあります。
※始点と終点を,でつないで記述しても範囲指定になります。
vba 色 カウント5
これで完成。何回繰り返しても正しい色数がカウントされます。

応用

文字色などでも数えたい

' vba
If Cells(i, 2).Font.Color = Cells(2, 4).Font.Color Then
Cells(2, 5).Value = Cells(2, 5).Value + 1
コードのInteriorFontに書き換えれば、文字色ごとのセルのカウントが可能です。
Font.Nameを使えば文字のフォントごとの数を数えられます。セルから取得できるプロパティがわかれば、一通り同様の処理が使えます。

カウントではなく、セルの数値を合計したい

' vba
If Cells(i, 2).Interior.Color = Cells(2, 4).Interior.Color Then
Cells(2, 5).Value = Cells(2, 5).Value + Cells(i, 2).Value
数を加算したい場合は、色が一致した時に+1するのではなく、セルの値を足し算します。
vba 色 カウント8
実行するとこんな感じになります。

方法②関数を自作して色をカウント

関数の作成

Functionプロシージャでユーザー定義型関数を自作する方法もあります。

' vba
Function function1()

End Function
まずマクロの最初のSubをFunctionに書き換えます。頭がFunctionになると、最後も自動的にEnd Functionになります。Function=関数。
' vba
Function function1(rng As Range)

End Function
関数に使う引数を設定できるので、Range型として何か名前を付けておきます。
Excelの関数は「何か値を入れると」「それに決まった計算をして」「戻り値を返す」というものになります。ここでは入れる値をコード内で扱うための名前を付けています。
' vba
Function function1(rng As Range)
function1 = rng
End Function
プロシージャの名前が「このセル」、引数が「参照するセル」です。例としてこんな感じのコードを入れて、
function 色を数える1
適当なセルに=function1(B2)と記入すると、B2セルと値が一致することが確認できます。
Sum関数などと同じような使用感になっている、関数が作成されていることがおわかりになると思います。

色をカウント

' vba
Function function1(rng As Range)
'参照先の範囲の各セル
Dim targetRng As Range

End Function
完成イメージとしては、関数の引数で範囲を指定すると、範囲内の各セルごとに色が条件と一致するかを審査し、一致する場合は関数を入力したセルの数字を1増やす……というものになります。
この全体範囲「rng」の中の個別のセル用の変数もDimで作成しておきます。
' vba
Function function1(rng As Range)
'参照先の範囲の各セル
Dim targetRng As Range
'選択範囲内の各セルごとに判定
For Each targetRng In rng

Next
End Function
For Each文で範囲rngの中の各セルtargetRngごとに処理をする旨記載し、
' vba
For Each targetRng In rng
'色が条件と一致するか判定
If targetRng.Interior.Color = RGB(146, 208, 80) Then
function1 = function1 + 1
End If
Next

各セルごとに色がカラーコードと一致する色かを判定し、合致する場合は関数を入力したセルの値を1増やします。カラーコードの取得方法については後述。

完成品

' vba
'関数名を変更する場合、青字部分も変更すること
Function functionColorCount(rng As Range)
'参照先の範囲の各セル
Dim targetRng As Range
'選択範囲内の各セルごとに判定
For Each targetRng In rng
'色が条件と一致するか判定
'色(赤字部分)は要変更
If targetRng.Interior.Color = RGB(146, 208, 80) Then
functionColorCount = functionColorCount + 1
End If
Next
End Function
function 色を数える4
上のコードをVBEに入力して保存し、カウントした数を表示したいセルで関数名(範囲)(この例だと=functionColorCount(B2:B9))を入力すれば、
function 色を数える5
範囲内の指定した色(この例では緑)の数が戻ります。

色の指定

function 色を数える3
色の指定方法ですが、カラーコードを直接指定する場合は、カウントしたい色のセルを選んだ状態でその他の色に入り、
function 色を数える2
表示される色を参照します。
' vba
'色が条件と一致するか判定
If targetRng.Interior.Color = Range("D2").Interior.Color Then
functionColorCount = functionColorCount + 1
色が色見本と一致する場合は……としたい場合は、赤字の部分を色見本があるセル.Interior.Colorに変更してください。

数値の合計を求めたい場合

' vba
functionColorCount = functionColorCount + targetRng
数値を合計したい場合は、青字部分を差し替えればOKです。

自作関数の注意事項

便利なユーザー定義型関数ですが、いくつか注意事項があります。

  1. 計算が遅い(巨大な表にはおすすめできない。止まる)
  2. マクロ一覧に表示されない(VBEの表示プロジェクトエクスプローラーから再編集できます)
  3. マクロが有効な環境でないと使えない(「マクロの有効化」も必須。他の人も使うファイルの場合注意)

あとは、ファイルが壊れやすくなるという話も聞きます。あんま使わないので未確認ですが、自作関数は必要以上に作らないほうが無難なようです。

やりたいことから方法を探すエクセル(Excel)操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント