EXCELで色のついたセルの数を知りたいです。

質問

Excel2016ですが　セルに色々な色（セルの塗りつぶしです）がついています
赤色のセルは3個、黄色のセルは4個などと求めたいのです
難しいことはよく解りませんが
何か関数を使ってできないでしょうか？
よろしくお願いいたします。

回答No.5

Chiquilin

> 難しいことはよく解りませんが
それならシンプルに理解すべきことがひとつだけあります。
「書式を数えるワークシート関数はない」です。

書式を数えるには 以下の４つの方法しかありません。
(1)VBEでユーザー定義関数を作って それを使って集計する
(2)マクロで書式を数値化してから数える
(3)色フィルタで一色ずつ抽出してオートカルクで数える
(4)置換機能のオプションで「色」を数値に置換してから数える

「色を数えたい」という発想は 印刷用の書式に直接入力してし
まう人に多く見られます。 つまり根本的には表の作り方そのも
のに問題がある場合がほとんどです。

回答No.4

nishi6

＞やはりセルにデータが入っていないと簡単にはいかないようです、
回答内容とこのコメントとの関係がよく分かりません。質問者様の「データ」とは「数値や文字列」のことでしょうか。「セルのデータ」とは「数値や文字列」はもちろん、罫線やフォント、結合の有無、当然、塗りつぶしの色も含まれます。初期状態でもデータだらけになるわけですね。従って、何も入力しない状態でも、マクロやユーザー定義関数で塗りつぶしの状態を集計できます。「簡単には」をどう捉えるかですが。

質問の内容を少し考慮してみます。塗りつぶしセルの絞り込みや検索はフィルター機能や検索機能でもできますが、使い勝手が悪いですね。関数とかないのかという疑問は当然と思えます。

＞赤色のセルは3個、黄色のセルは4個などと求めたいのです
Excelでの色の種類数は、赤・緑・青の各0～256種を組み合わせたもので、1600万色以上あります。（添付図・上図）しかも、ユーザー単位で色の設定は自由で、「赤色」、「黄色」といっても、一意に決められません。 初期設定で「標準の色」に青が３種類あったりします。個々のパソコンごとに色の定義が異なる可能性があります。ワークシート関数が無い一因でしょう。また、「塗りつぶしなし」も塗りつぶしの一つになります。

＞難しいことはよく解りませんが
色の定義が分かってもらえたとして、ワークシート内のある範囲がどのような色で塗られているかは、そのセルに数値や文字列の入力がなくても計算できます。当然、補助の算式なども不必要です。

計算方法１：
　マクロで指定範囲の塗りつぶしを調べ、そのカラー(Interior.Color)毎に、塗りつぶし色とそのセル個数を表示します。指定した色をカウントするのではなく、実際に使われている色を抽出する方法です。それが赤や黄色かもしれません。添付図・中図です。Color毎に集計といっても1600万色以上あるので比較が少し面倒です。

計算方法２：
　先に塗りつぶしの色を定義しておき、塗りつぶしはその色のみで行い、ユーザー定義関数で指定した範囲内の定義した色をカウントします。添付図・下図です。

＞何か関数を使ってできないでしょうか？
さらなる問題です。

計算方法１の場合は、
　マクロを実行すれば再計算できます。このマクロ実行を是とするかどうかです。集計するには、いろいろ配色を変えた後にマクロを実行するわけです。これを面倒と思うかどうかです。（セル範囲を決め打ちにすることもできます）

計算方法２の場合は、
　ユーザー定義関数の自動実行を完璧にできません。「Application.Volatile（ユーザー定義関数を自動再計算関数にする）」を記述すれば再計算してくれると思えますが、「セルを塗る」ことでは再計算が行われません。これは、「Application.Volatileは、再計算が実行されたら再計算してね」というお願い事でしかないからです。セルの塗りつぶしでも再計算してくれる工夫が必要です。この回答では、セルの選択位置が変わったら、強制的に再計算させて「Application.Volatile」が有効に働くようにしてみました。当然、再計算を行いたいタイミングでAltキー+Ctrlキー+ファンクションキーF9を押す方法もあります。しかし、私はこのキーの組み合わせを別ソフトに割り当てていました。こんな問題も起きるわけです。

■対応方法１：計算方法１です。
Sheet1を使い、添付図・中図を想定します。
Alt+F11キーでVisual Basic Editorに移動し、メニューから挿入＞標準モジュールを選択します。現れた標準モジュールに下記を貼り付けます。（Sub～End Sub まで）

Sub countColor()
　Dim dicCol As Object　　　 '// ディクショナリー
　Set dicCol = CreateObject("Scripting.Dictionary")

　Dim rg As Range　　　　　　'// セル範囲内のセル
　Dim mykey As String　　　　 '// ディクショナリーのキー
　Const nulColor = 16777215　'// 無色

　With Selection
　　For Each rg In Selection
　　　mykey = rg.Interior.Color
　　　If mykey <> nulColor Then
　　　　If dicCol.Exists(mykey) Then
　　　　　dicCol.Item(mykey) = dicCol.Item(mykey) + 1
　　　　Else
　　　　　dicCol.Add mykey, 1
　　　　End If
　　　End If
　　Next

　　Dim j As Integer
　　Dim Keys() As Variant: Keys = dicCol.Keys
　　With .Cells(1, .Columns.Count + 2)
　　　Range(.Offset(-1, 0), .Offset(100, 1)).Clear
　　　.Offset(-1, 0) = "色"
　　　.Offset(-1, 1) = "個数"
　　　For j = 0 To dicCol.Count - 1
　　　 .Offset(j, 0).Interior.Color = Keys(j)
　　　 .Offset(j, 1) = dicCol.Item(Keys(j))
　　　Next
　　End With
　End With
End Sub

実行方法は、シートに戻り（VBE画面のExcelのアイコン）、色を集計したい範囲を選択し、開発タブ＞マクロ＞countColor を実行します。添付図・中図
（開発タブの表示は大丈夫でしょうか。ググればすぐわかるはずです）

■対応方法２：計算方法２です。
Sheet2を使い、添付図・下図を想定します。
対応方法１と同じように標準モジュールに下記を貼り付けます。（Function～End Function）

Function fnColorCount(cRng As Range, Area As Range, dmy)
　Dim rg As Range, wk As Integer

　Application.Volatile
　For Each rg In Area
　　If rg.Interior.Color = cRng.Interior.Color Then
　　　wk = wk + 1
　　End If
　Next
　fnColorCount = wk
End Function

続けて、左上の小窓の「Microsoft Excel ObjectsのSheet2」 をダブルクリックして、表示されたコードウィンドウに下記コードを貼り付けます。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　Cells.Calculate
End Sub

シートに戻り、この例では、添付図・下図のようにセル範囲B3:F12を集計対象にします。I列には礼として、使う色を並べてみました。塗りつぶしダイアログの「標準の色」10色です。J列は色のコメントを見ながら手入力しました。ユーザー定義関数の入力は、

　セルK3: =fnColorCount(I3,$B$3:$F$12)

として下にコピーします。ユーザー定義関数で、セルI3の塗りつぶし色をセル範囲B3:F12でカウントします。
２つのセルとセル範囲は自由に変更してください。普通の関数と同じような使い方です。色を付けたり、消したり変更すると、セルの移動のタイミングで集計結果が変わるはずです。

当方、Win10、Excel2010です。ご参考に。

回答No.3

imogasi

＞セルのみの、データが入っていないと難しいようです
私の説明した、テスト例では、データ（これが「セルの値」に当たるものだと思うが）は入っていませんが、ユーザー関数で、セル数は４と帰りました。
説明したことも、やってないようですね。
どこからこの結論を導いたのかな。ＶＢＡでも勉強したら。

回答No.2

imogasi

普通のエクセル関数では、セルの塗りつぶし色や文字（フォント）色を問題にすることはできません。エクセルの関数は、「「セルの値」のみを問題にする」
ということを（エクセルの一番基礎的なこと）、勉強していないと思われる。
エクセルのデータ等の仕組みが、重畳的になっていることを気付いてないのだろう．市販本には、これを指摘している本はごく少ないだろうが。
　そこでＶＢＡの利用形態の一種であるユーザー関数を作れば、利用できる。
ＶＢＥ画面（ＡＬＴキー＋Ｆ１１キー）で、右側列のＴｈｉｓｗｏｒｋｂｏｏｋで右クリックして、標準モジュールを挿入操作で、標準モジュール画面を出す。
そこへ
Function ColorCount(R1 As Range)
Dim r As Range
Application.Volatile
ColorCount = 0
For Each r In R1
If r.Interior.ColorIndex <> xlColorIndexNone Then
ColorCount = ColorCount + 1
End If
Next r
End Function
と入れて、
シート画面に戻り、問題にしているセル範囲（下記例ではＡ１：Ｃ１０）以外のセル（下記例ではＤ１）を選択して、数式バー部で
=colorcount(A1:C10)
と入力して、Ｅｎｔｅｒ。
私の例では、Ｄ１に４と出た。
上記セルの中の４セルに塗りつぶし色（４種の色。単一色だけでも同じ答えになる）を設定していたので。
ーー
上記回答は、塗りつぶしなしのセル「（何か」の色設定以外）を問題にしたが、（多色のセルある中）単色セルのみを対象にするなら、色をＶＢＡで指定する、ＣｏｌｏｒＩｎｄｅｘや色名前定数や、RGBのことを勉強しないとならないが。
その前に目の前のシートの色設定がどういう仕組みで色づけされているのか、割り出す必要があって、他人が色設定したシートなどでは、割り出しが難しい。
＝＝
上記が難しいなら、＃１回答で紹介されている、WEB記事の中の、フィルタ法（セル色で検索）でも勉強したら。上記VBAコードもこのWEB記事を修正したもの。

お礼

ありがとうございます、やはりセルにデータが入っていないと簡単にはいかないようです、
もっと勉強してまたないか不明な点がありましたよろしくお願いいたします。

回答No.1

Nobu-W

https://pasokon-labo.com/excel-count-colored-cell/
参考にして下さい　m(_ _)m

参考URL：

https://pasokon-labo.com/excel-count-colored-cell/

お礼

ありがとうございます、セルにデータが入っていないとＥｘｃｅｌでは難しいようです。
色のみの個数はできないようですね。