エクセルでデータ管理をしている際、重要な項目に色を塗って目立たせることがよくあります。しかし、後から「黄色いセルがいくつあるか数えたい」と思ったときに、標準のCOUNTIF関数などでは色を条件に指定できず、困ってしまうケースは少なくありません。
実は、エクセルで色のついたセルを数えるには、標準機能の組み合わせや、少し高度な設定、あるいはマクロ（VBA）を利用するといったいくつかの方法があります。状況に合わせて最適な手段を選ぶことで、手作業で数える手間を大幅に削減できます。
この記事では、初心者の方でも実践しやすいフィルター機能を使った方法から、自動化に役立つVBAの使い方まで、色のついたセルをカウントするテクニックを分かりやすく解説します。ご自身のスキルや用途に合わせて、最適な方法を見つけてみてください。
エクセルで色のついたセルを数える最も手軽な「フィルター機能」
特別な設定や難しい関数を使わずに、今すぐ色のついたセルを数えたい場合に最もおすすめなのが、エクセルの「フィルター機能」と「SUBTOTAL（サブトータル）関数」を組み合わせる方法です。
フィルターの「色フィルター」を活用して絞り込む
エクセルのフィルター機能には、指定した色がついているセルだけを表示させる「色フィルター」という便利な機能が備わっています。まず、データが含まれている範囲を選択し、「データ」タブから「フィルター」をクリックして、見出しにフィルターボタンを表示させましょう。
次に、カウントしたい色が含まれる列のフィルターボタンをクリックします。メニューの中から「色フィルター」を選択し、集計したい色を指定してください。すると、その色がついている行だけが画面に残ります。この状態でセルをドラッグして範囲選択すれば、画面右下のステータスバーに個数が表示されます。
ただし、この方法では視覚的に確認するだけで、セルに数値を残すことができません。数値としてセルに表示させたい場合は、次に紹介するSUBTOTAL関数を併用する必要があります。一時的な確認であればこの方法が最もスピーディーです。
SUBTOTAL関数を使って可視セルのみを計算する
フィルターで絞り込んだ結果を、そのまま計算結果としてセルに表示させるには「SUBTOTAL関数」が欠かせません。通常のCOUNT関数やCOUNTA関数では、フィルターで隠れているセルの数まで含めて計算してしまいますが、SUBTOTAL関数は「表示されているセルだけ」を計算対象にできます。
使い方は簡単です。集計結果を表示したいセルに「=SUBTOTAL(3, 範囲)」と入力します。「3」という引数は「COUNTA（データの個数）」を指しており、フィルターで表示されているデータの数だけを数えてくれます。範囲には、色がついている列全体を指定しておきましょう。
この関数をあらかじめ入力しておけば、フィルターで色を切り替えるたびに、合計個数が自動的に更新されます。複数の色を順番に集計したい場合や、頻繁にデータの増減がある表を管理している場合に非常に便利なテクニックです。
フィルターによる集計の注意点とデメリット
フィルターを使った方法は非常に簡単ですが、いくつか注意点もあります。最大のデメリットは、一度に「一種類の色」しか数えられない点です。もし「赤色のセルは5個、青色のセルは3個」と同時に別々のセルに表示させたい場合には、フィルター機能だけでは対応できません。
また、この方法はあくまで「手動でフィルターを操作する」ことが前提となります。データが大量にある場合や、リアルタイムで集計結果を常に表示させておきたい場合には、操作の手間が負担になるかもしれません。
さらに、条件付き書式によって自動で色が塗られているセルの場合、古いバージョンのエクセルでは色フィルターが正しく反応しないことがあります。自分の使っているエクセルのバージョンや、色の塗り方のルールを事前に確認しておくことが大切です。
検索機能を利用して色のついたセルの個数を確認する
関数を入力したりフィルターを設定したりするのが面倒な場合、エクセルの「検索と置換」機能を活用して、特定の色のセルがいくつあるかを瞬時に確認することも可能です。
「検索と置換」ダイアログで書式を指定する方法
エクセルには、文字だけでなく「塗りつぶしの色」などの書式を検索する機能があります。まず「Ctrl + F」キーを押して「検索と置換」ダイアログボックスを開きましょう。次に、右側にある「オプション」ボタンをクリックして詳細設定を表示させます。
「書式」ボタンの右側にある下向き矢印をクリックし、「セルから書式を選択」を選ぶと、マウスカーソルがスポイトの形に変わります。その状態で、カウントしたい色が塗られたセルを直接クリックしてください。これで、その色と同じ書式が検索条件として設定されます。
この機能を使えば、複雑な色指定を自分で行う必要がなく、見たままの色を条件にできるのがメリットです。設定が完了したら「すべて検索」ボタンをクリックしてください。ダイアログの下部に、該当するセルのリストと合計個数が表示されます。
全て検索してステータスバーで個数を見る
「すべて検索」を実行すると、ダイアログボックスの左下に「〇個のセルが見つかりました」というメッセージが表示されます。これが、そのシート内で指定した色がついているセルの総数です。非常にシンプルな手順で、正確な個数を把握することができます。
さらに、検索結果の一覧で「Ctrl + A」を押すと、見つかったすべてのセルがワークシート上で一括選択されます。そのままセルの色を一度に変更したり、別の書式を適用したりといった操作もスムーズに行えるため、データ整理の際にも役立つ手法です。
ただし、この方法はあくまで「検索」の結果を表示しているだけなので、データの値が変わっても検索結果は自動更新されません。最新の個数を知りたいときは、その都度検索をやり直す必要がある点に注意しましょう。
一時的な確認に最適な理由
検索機能を使ったカウント方法は、資料作成の途中などで「今の時点で、期限が切れている赤いセルはいくつあるかな？」と、パッと確認したいときに最適です。表の構造を作り変えたり、新しい関数を覚えたりする必要がないため、初心者の方でも失敗がありません。
また、この方法の強みは「シート全体」や「ブック全体」を対象に検索できることです。複数の表が散らばっているシートでも、一気に色をカウントできるのは他の方法にはない利点です。
マクロを使わずに「名前の定義」で色番号を取得して数える
関数を使って自動的に色のついたセルを数えたいけれど、VBA（マクロ）を書くのは抵抗があるという方におすすめなのが、エクセルの隠れた機能である「GET.CELL関数」を活用する方法です。この方法は「名前の定義」という機能を使います。
「名前の定義」でGET.CELL関数を設定する
GET.CELL関数は、セルの書式情報を取得するための特殊な関数で、直接セルに入力することはできません。まず「数式」タブの「名前の定義」をクリックし、新しい名前（例：セル色）を作成します。そして、参照範囲の部分に以下の数式を入力します。
=GET.CELL(63, !A1)
ここで使っている「63」という数字は、セルの背景色の番号を取得することを意味します。「!A1」は、数式を入力するセルの左隣などを参照するための相対参照の設定です。これにより、特定の名前をセルに入力するだけで、そのセルの色番号を呼び出せるようになります。
この設定自体は一度行えば、そのブック内であれば何度でも使い回すことができます。一見難しそうに見えますが、手順通りに進めればそれほど時間はかかりません。
セルに色番号（カラーコード）を表示させる
名前の定義が完了したら、実際に色がついているセルの隣の列に、先ほど定義した名前を入力してみましょう。例えば「=セル色」と入力すると、そのセルの塗りつぶしに応じた「色番号（1〜56などの数値）」が表示されます。
無色の場合は「0」、黄色なら「6」、赤なら「3」といった具合に、エクセルが内部で管理しているインデックス番号が返されます。色がついていないセルも含めて、すべての行にこの式をコピーすることで、色の情報を「数値データ」として可視化することが可能になります。
このように数値化してしまえば、あとはエクセルの得意分野です。色が目で見えるだけの情報から、計算可能なデータへと変換されるため、集計の自由度が格段に上がります。
COUNTIF関数で特定の色番号をカウントする
色番号が数値として列に並んだら、あとはおなじみの「COUNTIF関数」を使うだけです。例えば、黄色（色番号6）のセルがいくつあるか数えたい場合は、「=COUNTIF(色番号の列, 6)」と入力します。
この方法の素晴らしい点は、表の中に複数の色があっても、それぞれの色番号を条件に指定することで、一度にすべての色の個数を別々のセルに集計できることです。フィルターを切り替える手間もなく、常に最新の個数が一目で分かります。
GET.CELL関数は非常に便利ですが、セルの色を変えただけでは再計算が行われません。色を変えた後は「F9」キーを押すか、何らかのデータを入力して再計算を促す必要があります。
ファイル保存時の注意点（xlsm形式）
GET.CELL関数は、実はマクロ機能の一部として扱われます。そのため、この方法を使ったエクセルファイルを保存する際は、通常の「.xlsx」形式ではなく、「エクセル マクロ有効ブック（.xlsm）」形式で保存する必要があるので注意してください。
もし誤って「.xlsx」で保存してしまうと、設定した「名前の定義」が消えてしまい、次回ファイルを開いたときにエラーになってしまいます。ファイル形式の変更は、保存時の「ファイルの種類」から簡単に選択できるので、忘れずに行うようにしましょう。
また、ファイルを開く際に「マクロが無効にされました」といった警告が表示されることがありますが、自分で作成したものであれば「コンテンツの有効化」をクリックして進めて問題ありません。セキュリティ設定によっては動作しない場合もあるため、職場のPCなどで使う際は確認が必要です。
VBA（マクロ）を作成して色のついたセルを自動で数える
より高度で柔軟な集計を行いたい場合や、大量のシートを処理する必要がある場合は、VBA（Visual Basic for Applications）を使って独自の「色カウント関数」を作成するのが最も確実な方法です。プログラミングと聞くと難しく感じるかもしれませんが、コードをコピーして貼り付けるだけで誰でも使えます。
ユーザー定義関数を作成するメリット
VBAを使う最大のメリットは、エクセルに元々存在しない関数を自分で作れることです。これを「ユーザー定義関数」と呼びます。一度作成してしまえば、通常のSUM関数やAVERAGE関数と同じように、セルの中で直接使えるようになります。
例えば、「=CountColor(範囲, 指定した色)」のようなシンプルな数式で、パッと個数を出せるようになります。標準機能では手間がかかる作業も、VBAを使えば一瞬で完了し、さらに計算間違いの心配もありません。
また、複数のブックやシートで同じ作業を繰り返す場合、VBAをアドインとして登録したり、テンプレートとして保存したりすることで、作業効率を飛躍的に高めることができます。エクセル作業の自動化への第一歩としても非常に適しています。
特定の色のセルを数えるコードの書き方
それでは、実際に使用するVBAコードの例を見てみましょう。まずは「Alt + F11」キーを押してVBAエディタを開き、「挿入」メニューから「標準モジュール」を選択します。そこに以下のコードを貼り付けてください。
Function CountColor(Rng As Range, TargetCell As Range) As Long
Dim C As Range
Dim ColorIndex As Long
ColorIndex = TargetCell.Interior.Color
For Each C In Rng
If C.Interior.Color = ColorIndex Then CountColor = CountColor + 1
Next C
End Function
このコードは、「Rng（数えたい範囲）」の中から「TargetCell（見本となる色がついたセル）」と同じ色のセルを1つずつ探し出し、その合計数を返すという仕組みになっています。非常にシンプルでエラーが起きにくい構造です。
作成した関数をワークシートで使う方法
コードを貼り付けたら、VBAエディタを閉じてエクセルの画面に戻りましょう。これで、新しい関数「CountColor」が使えるようになっています。使い方は以下の通りです。
| 引数 | 内容 |
|---|---|
| 第1引数（範囲） | 集計したいセル全体の範囲を指定します（例：A1:A100） |
| 第2引数（見本） | カウントしたい色が塗られているセルを1つ指定します |
例えば、B列のデータの中で、D1セルと同じ色のものを数えたい場合は「=CountColor(B:B, D1)」と入力します。これで、D1が赤ければ赤いセルの数が、黄色ければ黄色いセルの数が表示されます。
この関数の便利な点は、見本となるセルの色を変えるだけで、集計対象の色を動的に変更できることです。わざわざコードを書き換える必要がないため、VBAの知識がない同僚などにファイルを共有する際にも親切な設計と言えます。
条件付き書式で色がついている場合のカウント方法
これまで紹介した方法は、手動でセルを塗りつぶした場合に有効な手段でした。しかし、「数値が100以上なら自動で赤くする」といった「条件付き書式」で色が塗られている場合は、少し考え方を変える必要があります。
色ではなく「条件」そのものを数える考え方
条件付き書式によって付いた色は、実はエクセルの内部的な「塗りつぶし書式」とは異なるレイヤーで管理されています。そのため、GET.CELL関数や先ほどのシンプルなVBAコードでは、条件付き書式の色を認識することができません。
しかし、難しく考える必要はありません。条件付き書式で色が塗られているということは、そこには必ず「色が塗られるためのルール（条件）」が存在します。その色を数える代わりに、その「条件」に当てはまるセルを数えれば良いのです。
例えば「80点以上のセルを青くする」というルールがあるなら、青いセルを数えるのではなく「80点以上のセルの数」を数えます。これなら、標準の関数だけで完璧に対応可能です。
COUNTIFS関数を活用したスマートな集計
条件を元に個数を数える際、最も強力な武器になるのが「COUNTIFS（カウントイフ・エス）関数」です。この関数を使えば、1つだけでなく複数の条件を組み合わせて、色のついた（＝条件を満たした）セルを特定できます。
例えば、「期限が過ぎていて（条件1）、かつ完了チェックが入っていない（条件2）」というルールでセルに色がつくように設定している場合、関数でも同じように2つの条件を指定します。これにより、画面上の色と完全に一致した個数を算出できます。
この方法の利点は、マクロを使わないためファイルの保存形式を気にする必要がなく、再計算もリアルタイムで行われることです。動作も非常に軽く、エクセルブックのパフォーマンスを落とすこともありません。
色のルールを一元管理する重要性
条件付き書式とCOUNTIFS関数をセットで使う場合は、ルールの一貫性を保つことが重要です。集計用のセルに直接「80」などの数値を入力するのではなく、どこか別のセルに「目標値：80」と入力しておき、書式のルールも関数の条件も、そのセルを参照するように設定しましょう。
このように、「なぜその色がついているのか」という根本的なロジックに注目することで、色のついたセルを数えるという課題はよりシンプルに解決できます。色に頼りすぎず、データそのものを管理する姿勢が、ミスを防ぐ秘訣です。
エクセルで色のついたセルを数える手法のまとめ
エクセルで色のついたセルを数える方法は、手軽さや用途に合わせていくつかの選択肢があります。最後にもう一度、それぞれの特徴を振り返っておきましょう。
まず、最も手軽なのは「フィルター機能」と「SUBTOTAL関数」の組み合わせです。データの絞り込みと同時に個数が確認できるため、普段の業務で最も使い勝手が良い方法です。より素早く、一時的に確認したいだけであれば、「検索機能」を使って書式検索を行うのがベストです。
次に、マクロを使わずに自動計算させたい場合は、「名前の定義（GET.CELL関数）」を使って色を数値化するテクニックが有効です。さらに自由度を高めたい、あるいは自分専用の便利な機能を作りたい場合には、「VBA」によるユーザー定義関数の作成にチャレンジしてみてください。
また、条件付き書式で色をつけている場合は、色そのものではなく「元の条件」をCOUNTIFS関数で数えるのが最もスマートでエラーの少ない解決策となります。
どの方法を選ぶにしても、大切なのは「どの程度の頻度で集計するのか」や「他の人と共有するファイルなのか」といった状況を考慮することです。この記事で紹介したテクニックを駆使して、面倒な色のカウント作業から解放され、より効率的なエクセルワークを実現してください。
