同じ背景色のセルの数を計算する
Excelには条件付書式という機能があり、設定した条件にあうセルの書式を設定することができる。例えば、セルの値が10以上なら背景を赤にするとか。しかし、セルの書式を利用して計算作業をすることは通常の方法ではできない。
そこで、セルの背景色や文字の色を指定して条件にあうセルの個数を数えたり合計を計算する自作関数を作ってみた。
サンプルコードその１。
特定のセル範囲に、指定したセルと同じ背景色のセルがいくつあるかを計算する関数。
Function CountColor(myColor As Range, myRange As Range)
Dim r As Long, myCell As Range, myResult As Long
r = myColor.Interior.ColorIndex
For Each myCell In myRange If myCell.Interior.ColorIndex = r Then myResult = myResult + 1 End If Next
CountColor = myResult
End Function
計算結果を表示したいセルに、
=countcolor(A13,A1:J17)
のように入力する。
例えばセルA13の背景色が赤だとする。セル範囲A1:J17に背景色が赤のセルが5つある場合、計算結果は5になる。
オブジェクト.Interior.ColorIndexでオブジェクトの背景色のコードを取得できる。
サンプルコードその２。
指定したセルと同じ背景色のセルの値の合計を計算する関数。
Function SumColor(myColor As Range, myRange As Range)
Dim r As Long, myCell As Range, myResult As Long
r = myColor.Interior.ColorIndex
For Each myCell In myRange If myCell.Interior.ColorIndex = r Then myResult = WorksheetFunction.Sum(myCell) + myResult End If Next
SumColor = myResult
End Function
計算結果を表示したいセルに、
=sumcolor(A13,A1:J17)
のように入力する。
例えばセルA13の背景色が赤だとする。セル範囲A1:J17に背景色が赤のセルが5つある場合、その5つのセルの値を合計する。5つのセルの値がそれぞれ1、5、2、4、2だとすると、1+5+2+4+2で計算結果は14になる。
WorksheetFunction.Sum()はSumというワークシート関数を利用するためのコード。セルの値の合計を取得したいのでSumを使ったが、平均値を求めたいならAvarage、最大値を求めたいならMaxが利用できる。
同じ文字の色のセルの数を計算する
「同じ背景色のセルの数を計算する」と同様の方法だが、この関数はセルに入力されている文字の色を利用するもの。
サンプルコードその１。
特定のセル範囲に、指定したセルと同じ文字の色を使っているセルがいくつあるかを計算する。
Function CountfColor(myColor As Range, myRange As Range)
Dim r As Long, myCell As Range, myResult As Long
r = myColor.Font.ColorIndex
For Each myCell In myRange If myCell.Font.ColorIndex = r Then myResult = myResult + 1 End If Next
CountfColor = myResult
End Function
前回の関数名がCountColorだったので今回は間にfontのfをいれてCountfColorにしてみた。
オブジェクト.Font.ColorIndexでフォントの色が取得できる。
サンプルコードその２。
特定のセル範囲にある、指定したセルの文字の色と同じ色をもつセルの値の合計を計算する関数。
Function SumfColor(myColor As Range, myRange As Range)
Dim r As Long, myCell As Range, myResult As Long
r = myColor.Font.ColorIndex
For Each myCell In myRange If myCell.Font.ColorIndex = r Then myResult = WorksheetFunction.Sum(myCell) + myResult End If Next
SumfColor = myResult
End Function
.Interior.ColorIndexを.Font.ColorIndexに変えただけで、あとは前回のコードと全く同じ。
文字色・背景色両方に対応できる自作関数
関数を別々に作らなくても、引数を増やすことで両方に対応させることもできる。
上の関数では条件を指定するためのセルを第一引数myColor、計算範囲を指定する部分を第二引数myRangeとしたが、第三引数を作ってCountのときは0、Sumのときは1を指定するようにすると、CountもSumも計算できることになる。
このサンプルでは、関数名は上と同じCountColorのままで第三引数を追加し、第三引数が0の時は背景色で計算、1の時はフォントの色で計算というコードに変更してみる。
Function CountColor(myColor As Range, myRange As Range, myType As Integer)
Dim r As Long, myCell As Range, myResult As Long
If myType = 0 Then r = myColor.Interior.ColorIndex For Each myCell In myRange If myCell.Interior.ColorIndex = r Then myResult = myResult + 1 End If Next
ElseIf myType = 1 Then r = myColor.Font.ColorIndex For Each myCell In myRange If myCell.Font.ColorIndex = r Then myResult = myResult + 1 End If Next End If
CountColor = myResult Application.Volatile
End Function
コード自体はIf~ElseIfで分岐しているだけなのでそんなに難しくないと思う。
コードの終わりで指定しているApplication.Volatileはユーザー定義関数を自動再計算関数にするもの。通常、ユーザー定義関数でセルを引数に持つものは、Volatileを指定しなくても引数に指定されているセルの値が変更されたとき自動で再計算される。しかし背景色やフォントの色の変更は再計算の対象とならないので、色が変更になるケースが多くかつ数式は同じセルに入力したままという場合は指定したほうがよい。ただしVolatileを多用するとメモリを消費するので、大きなシートで使う場合はほどほどに。