当サイトはアフィリエイト広告を利用しています。
Excelでセルに色を付けるには「セルの塗りつぶし」をおこなうだけなので簡単ですが、色付きセルをカウントするとなると、やり方がよくわからず困ってしまいますよね。
そこで今回は、Excelで色付きセルをカウントする方法をご紹介します。
Excelで色付きセルを色別にカウントする3つの方法
Excelで色付きセルを色別にカウントする方法には、以下のようなものがあります。
- 検索機能を使う
- 色フィルターとSUBTOTAL関数を使う
- ユーザー定義関数を使う
方法1：検索機能を使う
検索機能を使うと、指定した色のセルの個数をカウントできます。色付きセルの個数を確認したいだけの場合はこの方法がおすすめです。条件付き書式で色付けしている場合はカウントできませんが、セルが空欄の場合もカウントできます。
検索機能を使って色付きセルをカウントするには、まず色付きセルが存在するセル範囲を選択した状態で［Ctrl］＋［F］を押します。
「検索と置換」が表示されたら、「オプション」⇒「書式」をクリックします。
「書式の検索」が表示されたら、「塗りつぶし」タブ⇒カウントしたい色を選択⇒「OK」をクリックします。
「検索と置換」に戻ったら、「すべて検索」をクリックします。
指定した色のセルが存在する場合、「検索と置換」の最下部左側に「◯セルが見つかりました」と表示されます。この「◯」の部分が、指定した色のセルの個数です。
方法2：色フィルターとSUBTOTAL関数を使う
色フィルターとSUBTOTAL関数を使うと、指定した色のセルの個数をカウントして結果を特定のセルに表示したり、セルの値を合計して結果を特定のセルに表示したりできます。色付きセルが空欄の場合はカウントできませんが、条件付き書式で色付けしたセルもカウントできます。
SUBTOTAL関数（サブトータル関数）は、指定された方法で集計する関数です。
SUBTOTAL（集計方法, 範囲1, ［範囲2］, …）
- 集計方法（必須）
- 集計方法を1～11で指定する。
- 1：AVERAGE
- 2：COUNT
- 3：COUNTA
- 4：MAX
- 5：MIN
- 6：PRODUCT
- 7：STDEV
- 8：STDEVP
- 9：SUM
- 10：VAR
- 11：VARP
- 範囲1（必須）
- 集計する範囲を指定する。
- 範囲2（省略可）
- 集計する追加の範囲を指定する。
なお、「範囲」は最大254個まで指定できます。
色フィルターとSUBTOTAL関数を使って色付きセルをカウントするには、まず元データのセル範囲のいずれかのセルをクリックし、「データ」タブ⇒「フィルター」をクリックします。なお、元データをテーブル化している場合は、この作業は不要です。
見出しの右下に「▼」ボタンが表示されたらOKです。
次にカウントした結果を表示したいセルに以下のような数式を入力します。
=SUBTOTAL(3,色付きセルを含むセル範囲)
=SUBTOTAL(9,色付きセルを含むセル範囲)次に元データをカウントしたい色でフィルタリングします。色付きセルの見出しの「▼」ボタンをクリックし、「色フィルター」⇒カウントしたい色を選択します。
これで指定した色のセルの個数、または値の合計が表示されます。
元データの表示を元に戻すには、元データのセル範囲のいずれかのセルをクリックし、「データ」タブ⇒「クリア」をクリックします。
これで元データの表示が元に戻ります。
方法3：ユーザー定義関数を使う
ユーザー定義関数を使うと、色フィルターとSUBTOTAL関数を使う方法と同じことが、フィルターをかけずに可能です。条件付き書式で色付けしている場合はカウントできませんが、セルが空欄の場合もカウントできます。
ユーザー定義関数を使って色付きセルをカウントするには、まずVBEを起動して標準モジュールに以下のコードを貼り付けます。
Function CountColor(arg1 As Range, arg2 As Long, arg3 As Range)
Dim buf As Range
Application.Volatile
CountColor = 0
Select Case arg2
Case 1 'セルの個数をカウントする場合
For Each buf In arg1
If buf.Interior.Color = arg3.Interior.Color Then
CountColor = CountColor + 1
End If
Next buf
Case 2 'セルの数値を合計する場合
For Each buf In arg1
If buf.Interior.Color = arg3.Interior.Color Then
CountColor = CountColor + buf.Value
End If
Next buf
End Select
End FunctionVBEの起動方法、標準モジュールの挿入方法、ユーザー定義関数を使ったファイルの保存方法などがわからない場合は、以下の記事を参考にしてみてください。
コードを貼り付けたら、あとはカウントしたい色でセルを塗りつぶし、CountColor関数を使って集計するだけです。
今回のカウント対象となる元データはこちらです。
まずカウントしたい色で任意のセルを塗りつぶします。
次にカウントした結果を表示したいセルに以下のような数式を入力します。これで色付きセルの個数や、値の合計が表示されます。
=CountColor(色付きセルを含むセル範囲,1,カウントしたい色のセル)
=CountColor(色付きセルを含むセル範囲,2,合計したい色のセル)Excelで色付きセルを色の区別なくカウントする方法
Excelで色付きセルを色の区別なくカウントするには、COUNTA関数やSUM関数を使ってセルの全体数を求め、そこから色なしセルの数を引きます。
なお、色なしセルをカウントするために色フィルターとSUBTOTAL関数を使うので、空欄のセルを含む場合、この方法は使えません。
COUNTA関数（カウントエー関数）は、空白ではないセルの個数を求める関数です。
COUNTA（値1, ［値2］, …）
- 値1（必須）
- 検索する範囲を指定する。
- 値2（省略可）
- 検索する追加の範囲を指定する。
なお、「値」は最大255個まで指定できます。
SUM関数（サム関数）は、数値の合計を求める関数です。
SUM（数値1, ［数値2］, …）
- 数値1（必須）
- 合計したい数値を指定する。
- 数値2（省略可）
- 合計したい追加の数値を指定する。
なお、「数値」は最大255個まで指定できます。
今回のカウント対象となる元データはこちらです。色フィルターを使うため、フィルターをオンにしています。
まずカウントした結果を表示したいセルに以下のような数式を入力します。
=COUNTA(色付きセルを含むセル範囲)-SUBTOTAL(3,色付きセルを含むセル範囲)
=SUM(色付きセルを含むセル範囲)-SUBTOTAL(9,色付きセルを含むセル範囲)次に元データを色なしでフィルタリングします。色付きセルの見出しの「▼」ボタンをクリックし、「色フィルター」⇒「塗りつぶしなし」を選択します。
これで色付きセルの個数、または値の合計が表示されます。
色の変更がユーザー定義関数の集計結果に反映されないときの対処法
ユーザー定義関数を使う場合、集計範囲の色を変更しても、即座に集計結果には反映されません。
集計結果に反映させるには、色を変更するたびに、再計算するショートカットキーの［F9］を押す必要があります。
Excelで色付きセルのデータを別シートに抽出する方法
Excelではセルが色付きかどうか判定する方法はないため、「セルが色付きかどうか調べ、色付きなら別シートに抽出する」といったことはできません。
ただし、「条件に一致するデータを別シートに抽出する」といったことは可能なため、色付きセルに共通する条件がある場合は、このやり方で色付きセルのデータを別シートに抽出できます。
たとえば、色付けの条件が「30以下」の場合、抽出先シートに以下のような数式を入力すれば、色付きセルのデータを抽出できます。
=FILTER(色付きセルを含むセル範囲,色付けの条件)条件に合うデータを抽出する方法については以下の記事で詳しく解説しています。
まとめ
今回は、Excelで色付きセルをカウントする方法をご紹介しました。
色付きセルを色別にカウントしたい場合は、検索機能を使う方法や、色フィルターとSUBTOTAL関数を使う方法、ユーザー定義関数を使う方法などがあります。
色の区別なく色付きセルをカウントしたい場合は、COUNTA関数やSUM関数を使ってセルの全体数を求め、そこから色なしセルの数を引きましょう。
ご紹介したマクロで希望の動作にならない場合、マクロの作成を依頼できるサービスを利用するのがおすすめです。以下の記事でマクロの作成を依頼できるサービスをご紹介していますので、興味のある方はぜひあわせてご覧ください。
ガントチャートを作成する目的で色付きセルをカウントしたいと思っている場合は、以下の記事も参考になるかもしれません。こちらは実績をセルの塗りつぶしではなく矢印で表す方法になります。
Udemyでは、買い切りの動画講座で好きな分野について自分のペースで学ぶことができます。
パソコンだけでなくスマホにも対応しているので、移動時間や休憩時間にスマホで学習することも可能です。
30日間の返金保証付きなので、初めてでも安心して受講できますよ。
▼ おすすめのExcel VBA講座はこちら ▼
