色付きセルのみを合計する [SumColor]

ユーザー定義関数 ,

34-1.png

関連記事 [ 自分で関数を作ってみる
ユーザー定義関数を使って色付きセルを(指定した色のみ)合計したいと思います。
簡単に言うとSUMIFの色バージョンです。

但し、色に重要性を持たすという事は一般的に避けるべきです。なぜなら色の認識について個人差があるからです。たとえば赤くしているセルがありますが、この定義関数を知らなければ何の為に赤くしているのか理解出来ませんし、勝手に色を変えられるかもしれません。データの場合はなかなか勝手に変えたりしませんよね。
このことを踏まえた上で、自分用に使ってみる分には結構使える機能ではないでしょうか。

Interior.ColorIndex

まずは色情報を読み取らないと始まりません。色判別したいんだから。
その時に使うのがInterior.ColorIndex
Excelには色番号というものがあります。赤だと3番になります。
カラーパレットの並びと番号はバラバラですが、各色に番号というものが付いています。
Interior.ColorIndex で背景色の色番号を返します。
ほかにFont.ColorIndexなどもありますが、ここでは背景色を使います。
上の画像で Cells(“A1”).Interior.ColorIndex だと赤色の3が返ります。

SumColor

関数名SumColor( 計算範囲 , 条件色セル )
機能計算範囲の中で色の条件色セルと同じ色の値を合計する

コード

Function SumColor(計算範囲, 条件色セル)
SumColor = 0
For x = 1 To 計算範囲.Rows.Count
If 計算範囲.Rows(x).Interior.ColorIndex = 条件色セル.Interior.ColorIndex Then
SumColor = SumColor + 計算範囲.Rows(x)
End If
Next
End Function

実際の使い方

34-2.png


ちょっとアレンジを加えれば白以外のセルの合計を・・・という風にも出来ますね。

注意点

注意点が2点あります!
まず、この計算は縦1列にしか対応していません。A1:C10のように複数列の場合はエラーとなります。
次回記事でこの問題について対応しております。そちらの方法を使えば複数列でも合計出来るようになります。
もう1点は、関数を入れた後にセル色を変更した場合。たとえばA7はやっぱり赤だった、と色を変更してもC3の計算結果は更新されません。色を変更しただけではExcelは再計算行われないのです。
対処法としては、Alt+Ctrl+F9を押して強制的にSheetを更新させるのが良いでしょう。
続き [ 色付きセルの件数をカウントする [CountColor] ]
関連記事 [ 自分で関数を作ってみる

アドインダウンロード

ダウンロードページに当記事で紹介したCountColorのアドインファイルを掲載しております。

Comment

  1. やじゅ より:

    SumColor = SumColor + 計算範囲.Rows(x)
    ↓ではないですか?
    SumColor = SumColor + 1

  2. Isakat より:

    それだと件数を数えるCountになりますよね?
    これは値のSum合計させたいのでこれで問題ないです。

  3. やじゅ より:

    すみません、勘違いしてましたm(_ _)m

  4. 舞花 より:

    コードをコピペしてみたのですが、うまくできません。
    表の中の黄色のセルの合計を求めたいのですが、どうしたらいいのでしょうか。
    実行ボタンを押すとマクロのダイアログボックスが出てきます。
    回答宜しくお願い致します。

  5. やま より:

    はじめまして。

    Sumcolorで複数列に対応するにはどのようなコードを使用すればいいですか?

    • より:

      Function SumIndexColor(計算範囲, 条件色セル)
      Application.Volatile
      SumIndexColor = 0
      For y = 1 To 計算範囲.Columns.Count
      For x = 1 To 計算範囲.Rows.Count
      If 計算範囲.Rows(x).Columns(y).Interior.ColorIndex = 条件色セル.Interior.ColorIndex Then
      SumIndexColor = SumIndexColor + 計算範囲.Rows(y).Columns(x)
      End If
      Next
      Next
      End Function

      複数列に対応したSumIndexColorという関数を作りました。

    • chikaharu より:

      Function SumIndexColor(計算範囲, 条件色セル)
      Application.Volatile
      SumIndexColor = 0
      For y = 1 To 計算範囲.Columns.Count
      For x = 1 To 計算範囲.Rows.Count
      If 計算範囲.Rows(x).Columns(y).Interior.ColorIndex = 条件色セル.Interior.ColorIndex Then
      SumIndexColor = SumIndexColor + 計算範囲.Rows(y).Columns(x)
      End If
      Next
      Next
      End Function

      • chikaharu より:

        xとyが反転してました。修正しました。

        Function SumIndexColor(計算範囲, 条件色セル)
        Application.Volatile
        SumIndexColor = 0
        For y = 1 To 計算範囲.Columns.Count
        For x = 1 To 計算範囲.Rows.Count
        If 計算範囲.Rows(x).Columns(y).Interior.ColorIndex = 条件色セル.Interior.ColorIndex Then
        SumIndexColor = SumIndexColor + 計算範囲.Rows(x).Columns(y)
        End If
        Next
        Next
        End Function

コメントをどうぞ

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

  • ▼お小遣い稼ぎしませんか?▼ 日々の生活にhappyをプラスする|ハピタス

    クレジットカード・FX口座を作るだけで簡単に3万円GETです。一切支出はありません。

ワイルドカード COUNTIF コピペ SUM グラフ フロー図 印刷 ユーザー定義関数 作業効率UP INDEX 条件付き書式 TODAY 図形 置換 無料 変換 COLUMN HLOOKUP テンプレート VLOOKUP XLSTART 日付 検索 NA() SUMIF DATE SUMIFS MATCH バーコード 目標
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑