はじめてのユーザー定義関数 〜 色のついたセルを合計

このページでは、ワークシートでユーザー定義関数を利用する手順をご紹介します。

今、ワークシートに以下のようなデータがあり、目印としてセルに赤い色がつけてあります。この色のついたセルだけを合計するにはどうすれば良いのでしょうか？

実は、ワークシート関数の中にはセルの色を取得できる関数はありません。しかし、マクロを使えば可能です。そこで、今回は VBA でユーザー定義関数を作り、色を取得するようにしてみましょう。
（色の認識には個人差があるため、色に重要な意味を持たせることは、一般的には避けるようにしてください）


Visual Basic Editorを起動します。


アクティブブックのプロジェクトに標準モジュールを挿入します。ユーザー定義ワークシート関数は標準モジュールに作成する必要があります。


標準モジュール Module1 が挿入され、コードのウィンドウが開きます。



このコードのウィンドウにユーザー定義関数を作成します。
ブラウザ上で、以下のマクロをマウスで範囲選択してコピーし、コードのウィンドウに貼り付けてください。このユーザー定義関数は指定されたセルの色番号を返します。

Function CellColor(セル)
     CellColor = セル.Interior.ColorIndex
End Function

これで標準モジュール Module1 に CellColor というユーザー定義関数が作成されました。



それでは、このユーザー定義関数を使ってセルの色を調べてみましょう。
エクセルのウィンドウに戻り、B1 セルに =CellColor(A1) と入力し、下のセルにもコピーします。関数の結果として数値が表示されますが、-4142 は塗りつぶしなし、3 は赤の色番号です。



セルの色がわかれば、SUMIF関数を使って色番号が3の行だけを合計することができます。=SUMIF(B1:B5,3,A1:A5) または =SUMIF(B1:B5,"=3",A1:A5) のような式を入力します。



このようにすれば色別の集計ができます。もし、合計ではなく件数をカウントしたければ =COUNTIF(B1:B5,"=3") でカウントできます。

ただし、色などの書式を使って計算するときには、大きな注意点が一つあります。それは、書式を変更しても自動再計算されないということです。セルの値を変更すれば自動的に再計算されますが、セルの色を変えても再計算はされないのです。

例えば、A5 の色を消して A4 に色をつけても、それだけでは計算結果には何も変化がありません。



書式の変化を計算にも反映させるためには、何らかの方法で再計算をする必要があります。確実な方法としては、数式を再入力したり、すべての数式を強制再計算する方法があります。強制再計算をするには Ctrl+Alt+F9キーを押します。これで計算結果が正しく更新されます。



NOW関数などの揮発性の関数（常に再計算される関数）を式に含める方法もあります。例えば =CellColor(A1) という式を =CellColor(A1)+NOW()*0 と書き換えます。NOW()*0 は 0 になるので計算結果には変化はありません。このようにすると、どこかのセルの値を変更したり、F9キーを押すと再計算されるようになります。多用するとエクセルの応答が遅くなるので注意してください。書式を変更しただけでは再計算されないことは同じです。

このユーザー定義関数は基本的には作成したブックの中で有効です。（他のブックでも使用できますが、複数のブックで利用する場合は、「アドイン」を作成してそこに入れておく方が便利です）

ユーザー定義関数を削除するには、Visual Basic Editor でモジュールの中のコードを削除します。計算結果を残しておくには、削除する前に、セル範囲をコピー、形式を選択して貼り付けで[値]を選択して貼り付けます。

マクロをすべて削除するには Visual Basic Editor の [プロジェクト] ウィンドウで削除したいモジュールを選択し、[ファイル]-[(モジュール)の解放]（Excel 97 の場合は [(モジュール)の削除]）を実行します。エクスポートの問い合わせがあるので、モジュールを別ファイルに保存しておきたい場合は [はい]、特に必要がなければ [いいえ] を選択します。（シートや ThisWorkbook のモジュールの場合は、モジュールは削除できないので、中のコードをすべて削除します）

文字の色の取得は以下のユーザー定義関数でできます。

Function FontColor(セル)
     FontColor = セル.Font.ColorIndex
End Function

※ VBAの他に、Excel 4.0 マクロの情報関数である GET.CELL 関数を使う方法もあります。マクロシートや名前定義の中で使用します。例えば上の表で、B1を選択して、[挿入]-[名前]-[定義]、[名前]のボックスに CellCol 、[参照範囲]に =GET.CELL(38,!A1) と入れて [追加]、B1からB4のセルに =CellCol と入力するとA列のセルの前景色の番号が表示されます。背景色は =GET.CELL(39,!A1) 文字の色は =GET.CELL(24,!A1) で取得できます。
（セルの色（背景色）は GET.CELL(63)、パターンの色（前景色）は GET.CELL(64) でも取得できます。網掛けパターンが設定されていない場合、63 と 64 はセルの色を背景色として扱いますが、38 と 39 は前景色として扱うという違いがあります）

※ Excel 2007 ではセルやフォントの色が自由に設定できるため、色番号では色の区別ができない場合があります。そのようなときは、RGB値（色を赤、緑、青の各成分量で表したもの）を使って色を区別します。RGB値を取得するには ColorIndex プロパティの代わりに Color プロパティを使います。関数の例です。

Function CellRGBColor(セル)
     CellRGBColor = セル.Interior.Color
End Function

Function FontRGBColor(セル)
     FontRGBColor = セル.Font.Color
End Function

戻る