【Excel】エクセルで赤文字だけをカウントする関数（色付き文字を集計）

Excelでチェック漏れ、期限超過、差し戻しなどを赤文字で目立たせていると、赤文字の件数だけを集計したくなる場面があります。

ただし、通常のCOUNTIF関数は文字色を条件として判定できないため、目的に応じて名前の定義、補助列、VBAなどを使い分ける必要があります。

この記事では、Excelで赤文字だけをカウントする方法と、色付き文字を集計するときの注意点を解説します。

エクセルで赤文字をカウントする関数の設定

それではまず、赤文字を判定できるように色番号を取得し、関数で集計する準備について解説していきます。

名前の定義で文字色番号を取得する操作

Excelには、セルに設定された文字色をそのまま数える標準関数はありません。

COUNTIF関数はセルに入力された文字列や数値、比較条件を対象にする関数であり、文字の見た目であるフォント色は判定対象ではないためです。

そこで、古くから利用されているGET.CELLというマクロ関数を、名前の定義を経由して利用します。

GET.CELLはワークシートのセルへ直接入力する関数ではありませんが、名前の定義に登録すると、対象セルの書式情報を補助列へ返せます。

作業前には、赤文字を含む一覧がB列にあり、1行目は見出し、実際のデータはB2から始まるものとして考えます。

まず、リボンの「数式」タブをクリックし、「名前の管理」を選択します。

表示された名前の管理画面で「新規作成」をクリックし、名前には分かりやすく「FontColor」と入力します。

参照範囲の欄には、=GET.CELL(24,INDIRECT(“RC[-1]”,FALSE)) と入力してください。

GET.CELLの24は、対象セルのフォント色番号を返す指定です。

INDIRECT(“RC[-1]”,FALSE) は、数式を入れるセルから見て左隣のセルを参照する意味になります。

たとえばC2にこの名前を使う場合、左隣のB2の文字色番号が返されます。

補助列を色付き文字の右隣に置くと、参照先がずれにくく、数式の管理も簡単です。

名前の定義では、参照範囲の先頭にイコール記号を付けることも重要です。

入力後に「OK」をクリックして名前の管理を閉じれば、文字色番号を取り出す下準備は完了です。

なお、ブックを他の人へ渡す場合、名前の定義もファイル内に保存されるため、補助列の役割を見出しで示しておくと親切でしょう。

設定内容が分からなくなったときは、数式タブの名前の管理から「FontColor」を選択し、参照範囲を確認できます。

補助列へ色番号を表示して赤文字を見分ける方法

名前の定義を作成したら、B列の右隣であるC列に「文字色番号」という見出しを入力します。

次に、C2を選択し、数式バーへ =FontColor と入力してEnterキーを押します。

赤文字で入力されたB2の文字列なら、C2には多くの場合で3が表示されます。

標準的な赤色が必ず3になるとは限りませんが、同じ書式をコピーして使っている一覧では、同じ番号として取得されます。

黒文字のセルには1など別の番号が表示されるため、先に赤文字のセルをいくつか確認して、集計対象の番号を把握しましょう。

確認後は、C2の右下にあるフィルハンドルを下へドラッグし、データ最終行まで数式をコピーします。

データ量が多い場合は、C2を選択してフィルハンドルをダブルクリックすると、左隣のB列に連続データがある範囲まで自動入力されます。

ただし、GET.CELLを名前の定義で使う方法には注意点があります。

セルの文字色だけを変更しても、補助列がすぐ再計算されないことがあります。

色を変更した後は、数式タブの「再計算実行」をクリックするか、F9キーを押して計算を更新してください。

文字列の変更ではなく書式だけを直したときほど、再計算を意識する必要があります。

また、セル内の一部分だけを赤文字にした場合、GET.CELLはセル全体のフォント色を前提に扱うため、期待どおりに判定できません。

たとえば「至急」の文字だけを赤くし、残りを黒くしているセルでは、部分書式の色をCOUNTIFの条件として数えることは困難です。

このような運用では、状態を表す別列を作り、「至急」「通常」などの文字列で管理したほうが集計の信頼性を保てます。

色は視認性を高めるために使い、集計に必要な条件はデータとして別途入力するという考え方です。

COUNTIF関数による赤文字件数の集計

続いては、補助列に表示された色番号をCOUNTIF関数で数え、赤文字だけの件数を出す方法を確認していきます。

COUNTIF関数で色番号が一致するセルを数える式

補助列C2からC100に色番号が入力されており、赤文字の番号が3であると確認できた場合は、任意の集計セルにCOUNTIF関数を入力します。

この数式は、C2からC100までの範囲にある数値のうち、3と一致するセルの個数を返します。

結果が5なら、B2からB100の範囲には赤文字と判定されたセルが5件あるという意味です。

1行目にヘッダーがある表では、範囲をC2から指定することで、見出しセルを集計対象から外せます。

色そのものを数えているのではなく、補助列へ取得した色番号を数えている点がこの方法の仕組みです。

件数を表示するセルの近くに「赤文字件数」とラベルを付けておくと、後から見た人にも数値の意味が伝わります。

データが増える予定なら、C2:C100のような固定範囲ではなく、Excelテーブルに変換する方法も便利です。

一覧の任意のセルを選び、CtrlキーとTキーを押してテーブルを作成すると、新しい行を追加したときに補助列の数式が自動で引き継がれます。

一方で、名前の定義を使うGET.CELLはテーブルの構造化参照と組み合わせると理解しづらくなる場合があります。

初めて設定する場合は通常のセル範囲で動作を確かめ、安定してからテーブル化するとよいでしょう。

赤色が複数種類ある場合は、赤いセルごとに補助列の番号を確認してください。

濃い赤、標準の赤、テーマカラーのアクセント色は別番号になることがあり、=COUNTIF(C2:C100,3) だけでは一部の赤文字が集計されないかもしれません。

対象の赤が3と10の両方なら、2つのCOUNTIFを加算します。

このように色番号を明示しておけば、どの色を集計対象にしたかを数式から確認できます。

条件付き書式で赤くなった文字を集計するときの注意点

Excelでは、セルに直接設定した文字色のほかに、条件付き書式によって文字が赤く見えるケースがあります。

たとえば期限日が今日より前なら赤文字にする、点数が基準未満なら赤文字にする、といったルールです。

この場合、GET.CELLで取得できる値は条件付き書式の見た目を正しく反映しないことがあります。

条件付き書式はセルに保存された固定のフォント色ではなく、条件を満たしたときに表示だけを変えているためです。

そのため、条件付き書式で赤文字にしている表では、色番号を数えるよりも、赤色にしている条件をそのままCOUNTIFやCOUNTIFSに書き直すほうが確実です。

たとえばB2:B100の数値が0未満のときに赤文字になるルールなら、次の数式で赤文字相当の件数を数えられます。

期限日がD列にあり、今日より前の未対応案件を赤文字にしているなら、COUNTIFS関数が役立ちます。

この式では、D列の日付が今日より前であり、かつE列の状態が未対応である行だけを数えます。

条件付き書式のルールと集計式を同じ条件にそろえると、画面上の赤文字と集計結果が一致しやすくなります。

条件付き書式の内容は、ホームタブの「条件付き書式」から「ルールの管理」を選ぶと確認できます。

表示されるルールで、適用先の範囲、比較演算子、参照セルを確認し、それと同じ条件をCOUNTIFまたはCOUNTIFSへ組み込みます。

数式ルールを使っている場合は、絶対参照と相対参照にも注意しましょう。

たとえば=$D2<TODAY()というルールなら、D列の日付を基準に各行を判定しています。

集計でもD2:D100を対象範囲とし、日付条件を同じ形で指定する必要があります。

見た目だけを追いかけるより、赤くした理由をデータ条件として数える方法は、監査や報告で根拠を説明しやすいという利点もあります。

VBAによる赤文字判定の自動化

続いては、文字色を直接調べるユーザー定義関数をVBAで作り、補助列を自動化する方法を確認していきます。

ユーザー定義関数を標準モジュールへ登録する手順

赤文字の判定を何度も使う場合や、色番号を別途確認する手間を減らしたい場合は、VBAでユーザー定義関数を作る方法があります。

この方法では、対象セルのフォント色を調べ、赤なら1、赤以外なら0を返す関数をブックに登録します。

マクロを利用するため、作業前にファイル形式をExcelマクロ有効ブックであるxlsm形式に保存してください。

通常のxlsx形式で保存すると、VBAのコードが保持されません。

AltキーとF11キーを同時に押すと、Visual Basic Editorが開きます。

上部メニューの「挿入」から「標準モジュール」を選択し、表示されたコード画面へ次の内容を貼り付けます。

Function IsRedFont(targetCell As Range) As Long
    If targetCell.Font.Color = RGB(255, 0, 0) Then
        IsRedFont = 1
    Else
        IsRedFont = 0
    End If
End Function

RGB(255,0,0) は、赤の成分を赤255、緑0、青0で指定する書き方です。

標準的な純粋な赤を対象にするため、フォント色が完全に同じ場合だけ1を返します。

赤の色調が異なるデータも含めたい場合は、実際に使われている色のRGB値を確認して条件を調整します。

コードを保存したら、Excel画面へ戻り、C2に =IsRedFont(B2) と入力します。

赤文字のB2なら1、黒文字なら0が表示されるため、C列をCOUNTIF関数で数える構成にできます。

VBAを含むファイルを受け取った場合は、信頼できる作成者のファイルであることを確認してからマクロを有効化しましょう。

VBA関数とCOUNTIF関数を組み合わせる集計方法

VBA関数で赤判定の列を作った後は、通常のCOUNTIF関数を使って件数を集計できます。

C2からC100に =IsRedFont(B2) をコピーした場合、赤文字の行には1、赤以外の行には0が表示されます。

集計用セルには次の数式を入力してください。

この式は、赤と判定された1だけを数えるため、対象範囲の赤文字件数を返します。

色番号を直接扱う方法と比べると、数式を見る人が「1は赤、0は赤以外」という意味を理解しやすい点がメリットです。

赤判定の列には、見出しとして「要確認フラグ」や「赤文字判定」などを入力しておくとよいでしょう。

赤文字を単なる装飾ではなく、集計可能なフラグとして扱えるようになることがVBA方式の利点です。

ただし、ユーザー定義関数も書式変更だけでは自動再計算されないことがあります。

文字色を変更した後に結果が変わらないときは、F9キーで再計算を実行してください。

さらに確実に更新したい場合は、赤文字を設定するマクロの中で計算を実行する方法もありますが、運用が複雑になるため、まずは手動再計算で問題ないか確認することをおすすめします。

また、VBAはExcelのWeb版では実行できず、組織のセキュリティポリシーによってはマクロ付きブックを開けない場合もあります。

共有先にマクロ利用者が多いか、Web版中心の環境かを考えたうえで採用しましょう。

共同編集が前提なら、赤文字の理由をステータス列に入力し、その列をCOUNTIFやCOUNTIFSで集計する構成のほうが互換性に優れます。

赤文字集計で起こりやすいエラーと対処

続いては、赤文字の件数が合わない原因と、集計結果を正しく確認するための対処を解説していきます。

色番号の違いによって集計漏れが起こるケース

赤く見える文字であっても、Excel内部では同じ色として扱われていないことがあります。

たとえばホームタブのフォントの色から標準の赤を選んだセルと、テーマの色から濃い赤を選んだセルでは、補助列に異なる色番号が表示される可能性があります。

担当者ごとに書式設定の方法が違う一覧では、この違いが集計漏れにつながります。

まずはフィルターを使い、補助列の色番号で並べ替えてください。

データタブの「フィルター」をクリックし、補助列の下向き矢印から番号ごとにチェックを切り替えると、どの番号のセルがどの色になっているか確認できます。

見た目が赤いセルを数件確認し、すべて同じ色番号かを調べることが、正確な集計の第一歩です。

複数の番号を赤として集計する必要がある場合は、COUNTIFを加算するか、COUNTIFの対象になる番号一覧を別の場所に作る方法があります。

たとえばF2に3、F3に10、F4に12と赤の番号を並べたなら、SUMPRODUCT関数でまとめて数えることも可能です。

この数式は、F2からF4に入力された各番号についてC列を数え、その件数を合計します。

色の追加や変更があっても、F列の一覧を更新するだけで集計条件を調整できるため、式を何度も書き換えずに済みます。

ただし、番号の意味が分からなくならないよう、G列に「標準赤」「濃い赤」のような説明を添えると管理しやすくなります。

最も望ましいのは、入力規則やテンプレートを使って、赤文字の色を統一することです。

書式を統一すれば集計式が単純になり、担当者の交代後も運用を引き継ぎやすくなります。

再計算とデータ範囲の見直しによる確認

赤文字を変更したのに件数が変わらない場合は、数式の範囲と再計算設定を確認しましょう。

GET.CELLやVBA関数は、値ではなく書式を参照する性質があるため、通常の入力変更と同じように即時更新されない場合があります。

まずF9キーを押し、シート全体の計算を実行してください。

計算方法が手動になっていると、セルの値を変更しても計算結果が更新されないことがあります。

数式タブの「計算方法の設定」を開き、「自動」が選択されているかを確認しましょう。

書式を変えた直後に集計が合わないときは、関数の誤りと決めつけず、再計算を先に試すことが大切です。

次に、COUNTIFの範囲が最終行まで届いているかを確認します。

たとえばデータが150行目まで増えているのに、=COUNTIF(C2:C100,3) のままでは、101行目以降の赤文字は集計されません。

範囲をC2:C200へ広げるか、データ全体をテーブルとして管理してください。

空白行が途中にある場合、フィルハンドルのダブルクリックで補助列の数式が途中までしかコピーされないこともあります。

補助列の最終行を確認し、空白のまま残っているセルがないかをチェックします。

フィルターで赤色番号だけを表示した状態では、非表示の行を含めてCOUNTIFが数える点にも注意が必要です。

画面に見えている赤文字だけを数えたい場合は、SUBTOTAL関数やフィルター条件を組み合わせる必要があります。

通常の報告用では非表示行も含む総件数なのか、絞り込み後の表示件数なのかを、あらかじめ決めておくと認識違いを防げます。

まとめ　エクセルの色付き文字をカウントする関数

Excelで赤文字だけをカウントしたいときは、通常のCOUNTIF関数だけでは文字色を直接判定できないことを理解する必要があります。

セルへ直接設定した赤文字なら、GET.CELLを名前の定義へ登録して色番号を補助列に表示し、COUNTIF関数で対象番号を数える方法が基本です。

条件付き書式で赤くしている場合は、色を数えるのではなく、赤文字にする条件をCOUNTIFまたはCOUNTIFSへ再現する方法が正確です。

VBAを使える環境なら、赤文字を1、赤以外を0として返すユーザー定義関数を作り、補助列を使って集計することもできます。

見た目の色だけで管理するより、赤にした理由をステータスや期限などのデータとして残すと、集計の精度と共有のしやすさが高まります。

集計結果が合わないときは、色番号の違い、条件付き書式の有無、再計算、集計範囲を順番に見直しましょう。