こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

回答受付中の質問

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

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

投稿日時 - 2019-11-12 11:58:52

QNo.9678076

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

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

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

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

投稿日時 - 2019-11-19 09:57:26

ANo.4

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

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

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

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

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

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

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

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

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

■対応方法1:計算方法1です。
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 を実行します。添付図・中図
(開発タブの表示は大丈夫でしょうか。ググればすぐわかるはずです)

■対応方法2:計算方法2です。
Sheet2を使い、添付図・下図を想定します。
対応方法1と同じように標準モジュールに下記を貼り付けます。(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でカウントします。
2つのセルとセル範囲は自由に変更してください。普通の関数と同じような使い方です。色を付けたり、消したり変更すると、セルの移動のタイミングで集計結果が変わるはずです。

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

投稿日時 - 2019-11-13 06:34:20

ANo.3

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

投稿日時 - 2019-11-12 14:30:16

ANo.2

普通のエクセル関数では、セルの塗りつぶし色や文字(フォント)色を問題にすることはできません。エクセルの関数は、「「セルの値」のみを問題にする」
ということを(エクセルの一番基礎的なこと)、勉強していないと思われる。
エクセルのデータ等の仕組みが、重畳的になっていることを気付いてないのだろう.市販本には、これを指摘している本はごく少ないだろうが。
 そこでVBAの利用形態の一種であるユーザー関数を作れば、利用できる。
VBE画面(ALTキー+F11キー)で、右側列のThisworkbookで右クリックして、標準モジュールを挿入操作で、標準モジュール画面を出す。
そこへ
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
と入れて、
シート画面に戻り、問題にしているセル範囲(下記例ではA1:C10)以外のセル(下記例ではD1)を選択して、数式バー部で
=colorcount(A1:C10)
と入力して、Enter。
私の例では、D1に4と出た。
上記セルの中の4セルに塗りつぶし色(4種の色。単一色だけでも同じ答えになる)を設定していたので。
ーー
上記回答は、塗りつぶしなしのセル「(何か」の色設定以外)を問題にしたが、(多色のセルある中)単色セルのみを対象にするなら、色をVBAで指定する、ColorIndexや色名前定数や、RGBのことを勉強しないとならないが。
その前に目の前のシートの色設定がどういう仕組みで色づけされているのか、割り出す必要があって、他人が色設定したシートなどでは、割り出しが難しい。
==
上記が難しいなら、#1回答で紹介されている、WEB記事の中の、フィルタ法(セル色で検索)でも勉強したら。上記VBAコードもこのWEB記事を修正したもの。

投稿日時 - 2019-11-12 13:01:35

お礼

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

投稿日時 - 2019-11-12 13:56:54

ANo.1

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

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

投稿日時 - 2019-11-12 12:00:20

お礼

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

投稿日時 - 2019-11-12 13:51:09

あなたにオススメの質問