VBAで同じ背景色のセルの数値の合計を取得する

同じ背景色のセルの合計を知りたい

Excelでデータの確認をしている際に、データの種類ごとの合計を知りたい場合があります。

そういう場合はフィルター機能を使って対象のデータを絞って、セルの数値を全選択してからステータスバーで合計値を見る、なんてことをやったりします。

それでいいんですが、データを行と列にきれいに整理しておいてフィルターを用意しておいたりする手間が必要なことや、単一種類のデータの合計しか分からないという制限があります。

作業をするにおいて、その時だけしか使わないデータってのは結構あるもので、そういうデータのためにわざわざフィルターかけたり見た目をきれいにしたりするのは時間の無駄です。

そこで、好き勝手に配置されたセルの中で、同じ背景色のセルの数値の合計を取得するマクロを紹介します。

コード

以下のマクロは選択範囲のセルの中で、同じ背景色の合計値をイミディエイトウィンドウに出力します。背景色は赤、緑、青をそれぞれ出力しています。

なお、Color値をRGB関数に変換したうえで出力しています。詳細は「Color値をRGB関数値に変換」を参照ください。

Function SumSameColor()
    Dim r           As Range            '// 単一セル
    Dim sr          As Range            '// 参照セル範囲
    Dim total       As Double           '// 合計値
    Dim dicColor    As New Dictionary   '// 選択セル範囲で使われている背景色コレクション
    Dim lColor      As Long             '// 背景色
    Dim v           As Variant          '// 各背景色の合計値
    Dim key                             '// Dictionaryのキー
    Dim iRed        As Integer          '// 赤
    Dim iGreen      As Integer          '// 緑
    Dim iBlue       As Integer          '// 青
    
    '// 選択セル範囲を取得
    Set sr = Selection
    
    '// 選択セル範囲をループして背景色の種類を取得
    For Each r In sr
        '// セルの背景色を取得
        lColor = r.Interior.Color
        
        '// セルの値が数値ではない場合
        If IsNumeric(r.Value) = False Then
            '// このセルは処理対象外のため次のセルの処理を行う
            GoTo CONTINUE
        End If
        
        '// コレクションに未登録の場合
        If dicColor.Exists(lColor) = False Then
            '// コレクションに追加
            Call dicColor.Add(lColor, r.Value)
        '// コレクションに登録されている場合
        Else
            '// コレクションに登録されている値を取得
            v = Val(dicColor.Item(lColor))
            '// コレクションの値にセルの値を加算する
            dicColor.Item(lColor) = v + r.Value
        End If
            
CONTINUE:
    Next
    
    '// コレクションループ
    For Each key In dicColor.Keys
        '// Color値をRGBに変換
        iRed = key Mod 256
        iGreen = Int(key / 256) Mod 256
        iBlue = Int(key / 256 / 256)
        
        '// 背景色ごとの合計を出力
         Debug.Print "Color[" & key & "] RGB[" & CStr(iRed) & ", " & CStr(iGreen) & ", " & CStr(iBlue) & "] 合計値; [" & dicColor.Item(key) & "]; """
    Next
End Function

Function SumSameColor()

Dim r As Range '// 単一セル

Dim sr As Range '// 参照セル範囲

Dim total As Double '// 合計値

Dim dicColor As New Dictionary '// 選択セル範囲で使われている背景色コレクション

Dim lColor As Long '// 背景色

Dim v As Variant '// 各背景色の合計値

Dim key '// Dictionaryのキー

Dim iRed As Integer '// 赤

Dim iGreen As Integer '// 緑

Dim iBlue As Integer '// 青

'// 選択セル範囲を取得

Set sr = Selection

'// 選択セル範囲をループして背景色の種類を取得

For Each r In sr

'// セルの背景色を取得

lColor = r.Interior.Color

'// セルの値が数値ではない場合

If IsNumeric(r.Value) = False Then

'// このセルは処理対象外のため次のセルの処理を行う

GoTo CONTINUE

End If

'// コレクションに未登録の場合

If dicColor.Exists(lColor) = False Then

'// コレクションに追加

Call dicColor.Add(lColor, r.Value)

'// コレクションに登録されている場合

Else

'// コレクションに登録されている値を取得

v = Val(dicColor.Item(lColor))

'// コレクションの値にセルの値を加算する

dicColor.Item(lColor) = v + r.Value

End If

CONTINUE:

'// コレクションループ

For Each key In dicColor.Keys

'// Color値をRGBに変換

iRed = key Mod 256

iGreen = Int(key / 256) Mod 256

iBlue = Int(key / 256 / 256)

'// 背景色ごとの合計を出力

Debug.Print "Color[" & key & "] RGB[" & CStr(iRed) & ", " & CStr(iGreen) & ", " & CStr(iBlue) & "] 合計値; [" & dicColor.Item(key) & "]; """

End Function

使い方

以下のように適当に並べられた数値で、背景色を付けているとします。

赤色が２か所で1と14の合計15です。グレーは１か所で14です。黄色、薄いオレンジ、緑などは複数あります。

これらのセルを範囲選択して上記マクロを実行するとイミディエイトウィンドウに以下のように出力されます。

Color[65535] RGB[255, 255, 0] 合計値; [128]; ”
Color[16777215] RGB[255, 255, 255] 合計値; [124]; ”
Color[255] RGB[255, 0, 0] 合計値; [15]; ”
Color[5296274] RGB[146, 208, 80] 合計値; [112]; ”
Color[10086143] RGB[255, 230, 153] 合計値; [132]; ”
Color[14277081] RGB[217, 217, 217] 合計値; [14]; ”

まず、６行出力されているため色の種類が６個あることが分かります。

Color[]の部分は背景色の値でRGB関数の値と同じです。

RGB[x, x, x]は赤緑青の順の色です。塗りつぶしの色の「その他の色」で表示される「色の設定」ダイアログで「ユーザー設定」タブを開くと、RGBの赤緑青の値が分かります。

そして、同じ背景色の合計値が出力されます。

同じ背景色のセルの合計を知りたい

コード

使い方

関連記事