煩わしいパスワード管理から解放されよう!

【解決】エクセルで色のついたセルを数える・合計する方法

色のついたセルを数える・合計する方法

エクセルで色の付いたセルの数を数えたり、色の付いたセルに入力されている数を合計する方法を紹介します。

数が少ないと問題ないですが、データ量が多いと手で数えるのには無理がありますね。

フィルターなどの標準機能でパッと数える方法と、VBAを使用する少し難しいけれどカウント結果を残せる方法を紹介しますので、用途に合わせて使ってください。

WBSを作成したりする機会があれば、工数の合計などに使うことができます。

条件付き書式を使ってセルに色をつけたい場合は「条件付き書式を使って行全体に色を付ける方法をまとめた記事」が参考になります。

こんな感じにできます

色付きのセルを計算した結果

色のついたセルを数える方法

色付きのセルを数える方法は、フィルターを使用する方法、検索機能を使用する方法、VBAで関数を作成する方法の3通りを紹介します。

フィルターを使用して色のついたセルを数える

フィルターで、数えたい色のセルだけを表示させて、SUBTOTAL関数でセルの数を数える方法です。

まずは、数えた結果を表示したいセルに「=SUBTOTAL(3,数えたいセルの範囲)」を入力します。

SUBTOTALでセルの数える

次に、フィルターを設定する範囲を選択している状態で「データ」タブにある「フィルター」をクリックします。

色のついたセルのある列の「▼」を押して「色フィルター」から数えたいセルの色を選択します。

色でフィルターかける

すると、色フィルターによって表示されている色のセルのみがカウントされた結果が表示されます。

表示されているセルのみカウントされる

検索機能を使用して色のついたセルを数える

検索機能の「すべて検索」結果から色のついたセルを数える方法です。

まずは、色のついたセルを数える範囲を選択し、「Ctrl」+「F」で「検索と置換」ウィンドウを表示します。

検索する範囲を指定

検索と置換」ウィンドウが表示されたら「オプション」を開きます。(既にオプションが開いている場合は、そのままでOKです。)

検索と置換でオプションを開く

オプションが表示されたら、「書式」ボタンをクリックします。

オプションの書式をクリック

塗りつぶし」タブで、数えたいセルの色を選択し、「OK」ボタンをクリックします。

数える色を指定する

すべて検索」をクリックすると、検索結果がウィンドウの下部に表示されるので、色のついたセルの数を確認することができます。

すべて検索で結果が表示される

VBAで関数を作成して色のついたセルを数える

上記の方法は、数をパッと調べる時に有効でしたが、資料の一部として色のついたセルの数を表示したい場合には不十分です。

それぞれの色がついたセルの数を同時に表示するには、こちらの方法を使用しましょう。

VBAと聞くと、難しそうと感じ人も多いと思いますが、この記事の内容をコピペでOKなので簡単にできますよ。

こちらの記事を参考に、より分かりやすく解説しています。

参考 色付きセルの件数をカウントするExcel術

色のついたセルを数える関数を作成

まずは、「Alt」+「F11」を押すか、「開発」タブの「Visual Basic」からVBEを起動します。

開発タブが表示されていない場合は「開発タブを表示する方法」の方法で追加することができます。

開発タブのVIsual Basic
VBEとは
エクセルに標準で搭載されているVBAを編集するためののエディターのことです。

VBEが起動したら、「Microsoft Excel Objects」の上で右クリックし、「挿入」項目の「標準モジュール」をクリックしましょう。

標準モジュールを挿入する

新しくモジュールが作られ、右側がコードの入力ウィンドウとなるので、下記のコードをコピーして貼り付けてください。

色付きのセルを数えるコード
Function ColorCount(R1 As Range, C As Range)
  Dim r As Range
  Application.Volatile
  ColorCount = 0
  For Each r In R1
    If r.Interior.Color = C.Interior.Color Then
      ColorCount = ColorCount + 1
    End If
  Next r
End Function
色のついたセルを数える関数を入力

色のついたセルを数える関数を入力

次はシートの入力に移ります。エクセルのシート画面がVBEとは別画面で開いてあるので、クリックして表示しましょう。

色のついたセルを数えた結果を表示したいセルにVBAで登録した関数(ColorCount)を入力します。

下記のように関数を入力しましょう。

  • =ColorCount( 色のついたセルを数えたい範囲 , 数えたい色が設定されているセル )

例の場合は、「=ColorCount($D3:$D12,F3)」です。(必要に応じて絶対参照$を付与してください。絶対参照については「VLOOKUPがうまくいかない原因」に記載していますので、確認してください。)

結果を表示したいセルに数式を入力

正しく入力すると、指定した範囲に指定した色がついたセルの数がセルに表示されます。

これで、色のついたセルを数えることができました。

参照先がズレていることがあるので、最終チェックは怠らずに!!

マクロありのブックとして保存する

最後に、「Ctrl」+「S」で保存しておきましょう。

もともと.xlsmシートで作成されていたり、保存が不要の場合は飛ばしてください。

「.xlsx」で作成されたブックの場合は、このようなメッセージが表示されるので、「いいえ」をクリックしましょう。

いいえを選択してマクロありのブックとして保存する
MEMO
このエラーは、「.xlsx」というVBAが保存できないブックにVBAの情報を保存しようとするために表示されますので、「いいえ」を選択し、VBA情報を保存できる「.xlsm」として保存しましょう。詳しくは「次の機能はマクロなしのブックに保存できませんが表示される原因と対処」にまとめてあります。

ファイルの種類」を「.xlsm」に変更して保存しましょう。

xlsmの拡張子を選択して保存する

色のついたセルの数値を合計する方法

色のついたセル自体の数ではなく、色のついたセルに入力されている数値の合計を求める方法を紹介します。

VBAで関数を作成して色のついたセルの数値を合計する

基本的な流れは、「VBAで関数を作成して色のついたセルを数える」と同じです。

コピーして貼り付けるコードと、セルに入力する関数が少し違うだけです。

色のついたセルの数値を合計する関数を作成

VBEを起動し、新しくモジュールを作成するか、End Functionの文字より下に下記のコードをコピーして貼り付けましょう。

色付きのセルの数値を合計するコード
Function ColorSum(R1 As Range, C As Range)
  Dim r As Range
  Application.Volatile
  ColorSum = 0
  For Each r In R1
    If r.Interior.Color = C.Interior.Color Then
      ColorSum = ColorSum + r.Value
    End If
  Next r
End Function
色のついたセルの数値を合計する関数

色のついたセルの数値を合計する関数を入力

色のついたセルの数値の合計を表示したいセルにVBAで登録した関数(ColorSum)を入力します。

下記のように関数を入力しましょう。

  • =ColorSum( 色のついたセルの数値を合計したい範囲 , 合計したい色が設定されているセル )

例の場合は、「=ColorSum($D3:$D12,F3)」です。

すると、このように指定した色のついたセルの数値が合計された結果が表示されます。

色のついたセルの数値の合計結果

色のついたセルの数値を条件付きで合計、カウントする方法

条件付きで色付きのセルをカウントできないか?」というお問い合わせをいただきましたので、関数を追加してみました!

同様に関数を追加してみてお試しください!

VBAで関数を作成して色のついたセルの数値を条件付きで合計する

同様に、コードを貼り付けるだけで関数の追加ができます。

色のついたセルの数値を条件付きで合計する関数を作成

VBEを起動し、新しくモジュールを作成するか、End Functionの文字より下に下記のコードをコピーして貼り付けましょう。

色付きのセルの数値を条件付きで合計するコード
Function ColorSumIf(R1 As Range, C As Range, OF As Integer, C2 As Range)
    Dim r As Range
    Application.Volatile
    ColorSumIf = 0
    For Each r In R1
        If r.Interior.Color = C.Interior.Color Then
            If r.Offset(0, OF).Value = C2.Value Then
                ColorSumIf = ColorSumIf + r.Value
            End If
        End If
    Next r
End Function
色付きのセルの値を条件付きで合計するコード

色のついたセルの数値を条件付きで合計する関数を入力

色のついたセルの数値の合計を表示したいセルにVBAで登録した関数(ColorSumIf)を入力します。

下記のように関数を入力しましょう。

  • =ColorSumIf( 色のついたセルの数値を合計したい範囲 , 合計したい色が設定されているセル , 指定範囲から何列目にあるか , 条件の設定されているセル )

例の場合は、「=ColorSumIf($D3:$D12,F3,1,$J2)」です。

すると、このように指定した色のついたセルの数値が合計された結果が表示されます。

色付きのセルの値を条件付きで合計するコードの結果

VBAで関数を作成して色のついたセルの数値を条件付きでカウントする

同様に、カウントの場合もコピペでお使いください!

色のついたセルの数値を条件付きでカウントする関数を作成

VBEを起動し、新しくモジュールを作成するか、End Functionの文字より下に下記のコードをコピーして貼り付けましょう。

色付きのセルの数値を条件付きで合計するコード
Function ColorCountIf(R1 As Range, C As Range, OF As Integer, C2 As Range)
    Dim r As Range
    Application.Volatile
    ColorCountIf = 0
    For Each r In R1
        If r.Interior.Color = C.Interior.Color Then
            If r.Offset(0, OF).Value = C2.Value Then
                ColorCountIf = ColorCountIf + 1
            End If
        End If
    Next r
End Function

色のついたセルの数値を条件付きでカウントする関数を入力

  • =ColorCountIf( 色のついたセルの数値をカウントしたい範囲 , カウントしたい色が設定されているセル , 指定範囲から何列目にあるか , 条件の設定されているセル )

例の場合は、「=ColorCountIf($D3:$D12,G3,1,$K2)」です。

色付きのセルの値を条件付きでカウントするコードの結果

最後に

以上が、エクセルで色のついたセルを数える・数値を合計する方法でした。

VBAと聞くと難しく感じるかもしれませんが、とても簡単に実装することができますので、ぜひ試してみてください。

知ってるか、知らないかでできることが全然変わってきますので、色の付いたセルを数える関数を作れることだけでも覚えておいてくださいね。

他にもご要望があれば随時受付中ですので、ご連絡くださいませ!

8 COMMENTS

(有)井上徳太郎商店 井上誠一

弊社は家内工業で、エクセルを使用して、何とかかんとか時代の波について行っています。いろいろ教えて頂き本当に助かります。誠にありがとうございます。カラーカウントの仕方を調べて、現在厳しくなっている年間出勤や、有休休暇などの計算に使用させていただきました。

返信する
おーちゃん

はじめまして。コメントありがとうございます。
少しでもお役に立てたみたいで、とても嬉しいです。
もし他にも分からないことや、このサイトに載っていないことがあれば、ぜひご連絡ください!

返信する
奇天烈

はじめまして。とても便利な方法を教えていただきありがとうございます。
一点質問させてください。
条件付き書式設定で色を塗ったセルをVBAで作った関数で数えようとしましたがカウントされませんでした。(色も確認しました)
もう一度色が塗っている箇所を塗りなおしたらカウントされるようになりました。
条件付き書式で塗った色は反映されないのでしょうか?

返信する
おーちゃん

ユーザ定義関数(VBAで自作した関数)を使って、”シート上で自動的”に条件付き書式で色のついたセルの数を表示・更新させるプロパティがないようなんです…。
なので、それをやろうとするとかなり複雑な処理になってしまいます。

別途、ボタンを押したら起動する入力フォームに範囲などを入力して実行してもらい、数値を更新するというのであればすぐにはできるんですが、たぶんやりたいことからずれているのかなと思います。

返信する
K

条件を変えてもカウントしません
再計算するとカウントします
自動計算できないですか

返信する
こうめ

 たまたま少し前にVBEについてe-ラーニングによる学習をしたところで、色付きセルの色毎の集計をしたい局面があり、これを参考にさせていただきました。
 以前ならマクロを使うというと敬遠したところなんですが、こんなに便利に使えると言うことが実践で体験できました。
 とても役に立ちました。
 ありがとうございました。

返信する
アンナ

一から学ぶ時間が取れない私には、
VBA自体知らなくても、
とても分かりやすく説明されていて感謝しかありません。
嬉しくてコメントしました。
ありがとうございます。

返信する

コメントを残す