一時的な集計なら色フィルターとSUBTOTAL、複数の色の合計を並べるならSumColorを使います。条件付き書式で色を付けている場合は、その条件をSUMIFSで集計します。
デスクトップ版 Excel / VBAはWeb版では実行不可
色フィルターとSUBTOTALで合計する
B2:B10に金額があり、そのセル自体に色が付いている例です。表にフィルターを設定し、B列の［色フィルター］から対象の色を選んで、表の外へ次の式を入れます。
=SUBTOTAL(109,B2:B10)109は表示されている行の数値を合計し、手動で非表示にした行も除きます。横方向の非表示列を除く機能ではありません。別の列の色で判断する場合は、その列を色フィルターで絞り込んでから金額列を合計します。
SumColor関数を用意する
VBEで標準モジュールを挿入し、次のコードを貼り付けます。ブックは.xlsm形式で保存してください。
Option Explicit
Public Function SumColor(ByVal cells As Range, _
ByVal sample As Range) As Double
Application.Volatile
Dim cell As Range
Dim total As Double
For Each cell In cells.Cells
If cell.Interior.Color = sample.Cells(1, 1).Interior.Color Then
If Not IsError(cell.Value2) Then
If VarType(cell.Value2) <> vbString And _
VarType(cell.Value2) <> vbBoolean Then
If IsNumeric(cell.Value2) Then
total = total + CDbl(cell.Value2)
End If
End If
End If
End If
Next cell
SumColor = total
End Functionこのコードは、数値として保存された値だけを合計します。空白・文字列・エラー・TRUE/FALSEは合計に含めません。文字列の「100」も対象外です。エラーのある行は集計前に別途確認してください。
見本セルを指定して集計する
E2を対象色で塗り、結果セルに次の式を入力します。B2:B10の中でE2と同じ色で直接塗りつぶされたセルの数値を合計します。
=SumColor(B2:B10,E2)赤・黄・緑の見本セルを並べれば、色ごとの結果を表示できます。ただし、この関数は非表示行も含めて集計します。
業務の集計は条件列に置き換えると安定する
「支払済」を青で表しているなら、状態をC列に保存して次のように集計できます。条件付き書式もこの状態列をもとに設定すると、見た目と計算の根拠がそろいます。
=SUMIFS(B2:B10,C2:C10,"支払済")サンプルをダウンロード
この記事のサンプル素材無料ダウンロード · .bas標準モジュール用のVBAソースファイルです。マクロを組み込んだブックではありません。内容を確認してから、ガイドの手順に沿って使ってください。
本記事は旧URLの参照情報をもとに、新たに執筆した解説です。旧サイトの記事本文の復元・転載ではありません。画面や機能はExcelのバージョンによって異なります。