【Excel】エクセルで色付きセル・赤文字をカウント・合計する方法（関数・抽出・検索）

Excelで色付きセルや赤文字のデータを数えたい場面は、進捗管理表、エラー一覧、予算超過の確認表などでよくあります。

ところが、通常のCOUNTIF関数やSUMIF関数は、セルの塗りつぶし色やフォントの色そのものを条件として直接判定できません。

色を付けた理由と、色を付ける前の値や文字列を分けて考えることが、正確な集計への近道です。

この記事では、関数、フィルター、検索と選択、そしてVBAを使う場合の考え方まで、色付きセルと赤文字をカウント・合計する方法を順番に解説します。

色付きセルを集計する主な方法は、条件に対応する関数を使う方法、色で抽出してステータスバーを確認する方法、検索と選択で件数を確認する方法です。

塗りつぶし色だけを直接条件にしたい場合は、VBAのユーザー定義関数や補助列の活用も候補になります。

サンプルでは1行目を見出し行とし、A列に担当者、B列に区分、C列に金額、D列に判定を入力しているものとして進めます。

色付きセル・赤文字を数える基本方法【フィルターとステータスバー】

担当者	区分	金額	判定
田中	要確認	12000	差戻し
佐藤	完了	8500	承認済み
鈴木	要確認	15000	差戻し

それではまず、色付きセルや赤文字を画面上で素早く数える基本操作について解説していきます。

急ぎで件数を確認したいだけなら、数式を作成する前にフィルターとステータスバーを使う方法が便利です。

フィルターで対象色だけを表示し、表示されたセル範囲を選択すると、Excelの画面下部で個数や合計を確認できます。

塗りつぶしの色による絞り込み

塗りつぶし色が付いたセルを数えるときは、表内の任意のセルを選択してから、データタブのフィルターを設定します。

B1からD4のように見出しを含む表範囲を選択し、データタブにあるフィルターをクリックしましょう。

見出しセルに表示された下向きの矢印をクリックすると、フィルターのメニューが開きます。

メニューの色でフィルターから、セルの色でフィルターを選択します。

色付きセル・赤文字を数える基本方法【フィルターとステータスバー】 - 塗りつぶしの色による絞り込み

表示された色の一覧から黄色などの対象色を選べば、その色が設定された行だけが残ります。

この方法は、条件付き書式で見た目が変わっているセルにも使える場合がありますが、設定内容によっては期待通りに抽出されないこともあります。

実際に残った行を確認し、対象となる色だけが表示されているか確認することが大切です。

表示セルの個数と合計の確認

フィルターで対象行を表示した後、金額が入力されているC2からC4などの範囲をドラッグして選択します。

Excelウィンドウの右下にあるステータスバーには、合計、平均、データの個数などが表示されます。

数値セルを選択した場合は合計が表示されるため、黄色の行にある金額だけをすぐ確認できます。

色付きセル・赤文字を数える基本方法【フィルターとステータスバー】 - 表示セルの個数と合計の確認

文字列を含むセルを選択した場合は、数値の個数とデータの個数の違いにも注意が必要です。

数値の個数は数字が入ったセルだけを数え、データの個数は文字列や数値が入った空白ではないセルを数えます。

ステータスバーに目的の項目が表示されないときは、ステータスバーを右クリックして、合計やデータの個数にチェックを入れます。

赤文字を色で抽出する操作

赤文字をカウントしたい場合も、基本的には塗りつぶし色と同じ手順で進められます。

赤文字が入力されたD列のフィルター矢印をクリックし、色でフィルターからフォントの色を選択します。

赤色を選ぶと、赤いフォントで表示されたセルを含む行だけが一覧に残ります。

文字色で抽出するときは、セルの背景色ではなくフォントの色を選ぶことが操作上のポイントです。

抽出後にD列のデータ範囲を選択すれば、赤文字のセル数をステータスバーで確認できます。

ただし、同じ行に空欄がある場合や結合セルがある場合は、意図した件数と異なることがあるため、対象列を限定して選択しましょう。

【操作のポイント】フィルターは色で行を抽出する機能です。

色付きセルだけの合計を確認するときは、抽出後に金額列だけを選択すると判断しやすくなります。

色の意味を条件にした関数集計【COUNTIF・SUMIF】

区分	金額	判定	補助列
要確認	12000	差戻し	要確認
完了	8500	承認済み	完了
要確認	15000	差戻し	要確認

続いては、色が表している文字や判定を条件にして、関数で自動集計する方法を確認していきます。

Excelの標準関数はセルの色そのものを判定できませんが、色を付けた根拠となる文字列や数値があれば、安定してカウントと合計を行えます。

黄色のセルが要確認を表すなら、色ではなく要確認という文字列を条件にします。

赤文字が差戻しを表すなら、差戻しという文字列をCOUNTIF関数で数えます。

COUNTIF関数による件数の集計

たとえばB列に要確認と完了が入力され、要確認のセルだけを黄色で塗りつぶしているとします。

このとき、要確認の件数を数える数式は、集計セルに入力します。

=COUNTIF(B2:B100,”要確認”)

COUNTIF関数は、最初の引数で指定した範囲の中から、2番目の引数と一致するセルを数える関数です。

見た目の黄色ではなく、要確認という元データを数えるため、色を変更しても集計ルールがぶれにくい仕組みです。

赤文字の差戻しを数えるなら、D列を対象にして同様の数式を使えます。

=COUNTIF(D2:D100,”差戻し”)

文字列は必ず二重引用符で囲み、見出し行である1行目を範囲に含めないようにすることが基本です。

SUMIF関数による金額の合計

黄色の要確認行に対応する金額を合計したいときは、SUMIF関数を使います。

B列の区分を判定条件にし、C列の金額を合計する場合の数式は次の形です。

=SUMIF(B2:B100,”要確認”,C2:C100)

最初のB2:B100は条件を探す範囲であり、要確認が入力されているかを判定する場所です。

次の要確認は条件であり、最後のC2:C100は合計する数値範囲です。

条件範囲と合計範囲は、必ず同じ行数・同じ開始行にそろえることが重要です。

たとえば条件範囲をB2:B100、合計範囲をC3:C101のようにずらすと、別の行の金額を足してしまうおそれがあります。

補助列を使った集計条件の整備

実務では、黄色、赤、青などの色だけが入力ルールとして使われ、元となる判定文字がない表もあります。

その場合は、E列などの空いている列に状態という見出しを作り、色の意味を文字列として入力する補助列を設ける方法が有効です。

黄色なら要確認、赤文字なら差戻し、緑色なら完了というように、各行へ明確な状態を入力します。

その後は補助列をCOUNTIF関数やSUMIF関数の条件範囲にできるため、集計表やピボットテーブルにも展開しやすくなります。

色は注意を引く表示、補助列は集計のためのデータとして役割を分けると、後から表を修正するときも安全です。

【操作のポイント】色を直接数えられないときは、色の意味を文字列として補助列に持たせます。

関数による自動更新を重視するなら、色よりも条件文字列を集計軸にする方法が適しています。

検索と選択による赤文字セルの確認【書式検索】

セル	表示内容	文字色	確認結果
D2	差戻し	赤	対象
D3	承認済み	黒	対象外
D4	差戻し	赤	対象

続いては、書式を指定して赤文字や色付きセルを探し、該当箇所を確認する検索と選択の操作を解説していきます。

この方法は、表の入力規則が整っていない場合や、どのセルに赤文字があるかを一覧で確認したい場合に役立ちます。

検索と選択の書式検索では、文字列が異なっていても、同じフォント色や塗りつぶし色のセルを見つけられます。

検索ダイアログでの書式指定

ホームタブの検索と選択から検索をクリックするか、CtrlキーとFキーを同時に押します。

検索と置換のダイアログが開いたら、オプションをクリックして詳細項目を表示します。

書式の右にある書式ボタンをクリックすると、検索書式の画面を開けます。

フォントタブで赤色を選択すれば、赤文字だけを検索条件にできます。

塗りつぶしの色を対象にしたい場合は、塗りつぶしタブで黄色などの色を指定しましょう。

検索する文字列を空欄にして書式だけを指定すると、指定書式のセルを探せます。

Book1 – Excel −　□　×

ファイル　ホーム　挿入　ページレイアウト　数式　データ　校閲　表示

貼り付け
太字 B
罫線
検索と選択

	A	B	C	D
1	担当者	区分	金額	判定
2	田中	要確認	12000	差戻し
3	佐藤	完了	8500	承認済み

赤文字のセルを検索
➤ 書式を指定

書式の指定後にすべて検索をクリックすると、該当するセルの一覧がダイアログ下部に表示されます。

検索結果からの件数把握

すべて検索を実行すると、検索ダイアログの下部にセル、値、数式などの情報が行ごとに表示されます。

表示された一覧の件数を確認すれば、赤文字セルが何個あるかを把握できます。

検索結果はセル単位で表示されるため、色付きの行数ではなく色付きセル数を確認したい場合に向いています。

結果一覧の先頭をクリックしてからCtrlキーとAキーを押すと、該当セルをまとめて選択できます。

選択されたセルはシート上でも強調表示されるので、意図しない赤文字が混ざっていないかの目視確認にも使えます。

検索条件を変更する場合は、書式のクリアを選んでから新しい書式を指定しましょう。

条件付き書式との違いの確認

赤文字が手入力の書式ではなく、条件付き書式によって表示されているケースもあります。

条件付き書式は、セルの値が一定条件を満たしたときに自動で文字色や背景色を変える機能です。

たとえば金額が10000円を超えたら赤文字にする設定では、赤という見た目は計算結果によって変化します。

この場合は赤い書式を検索するよりも、元となる条件である金額が10000より大きいデータをCOUNTIF関数で数えるほうが確実です。

=COUNTIF(C2:C100,”>10000″)

条件付き書式で表示された赤文字は、色ではなく条件式を集計する考え方が基本です。

ホームタブの条件付き書式からルールの管理を開けば、どの条件で色が付いているかを確認できます。

【操作のポイント】検索の書式指定は、入力済みの色を探す作業に便利です。

条件付き書式の場合は、表示色ではなくルールに使われている値や数式を集計条件にしましょう。

SUBTOTAL関数による抽出後の合計【フィルター連動】

区分	金額	フィルター後
要確認	12000	表示
完了	8500	非表示
要確認	15000	表示

続いては、色でフィルターをかけた結果に合わせて、表示中の数値だけを合計するSUBTOTAL関数を確認していきます。

フィルターで黄色の行だけを残したとき、通常のSUM関数では非表示の行も含めて合計されます。

そこで、抽出した行だけを計算したい場合にSUBTOTAL関数を使います。

合計用のSUBTOTAL関数

C2からC100に金額が入力されている場合、表示中の金額だけを合計する数式は次のとおりです。

=SUBTOTAL(109,C2:C100)

109は、手動で非表示にした行とフィルターで非表示になった行の両方を除外して合計する指定です。

フィルターだけを対象にして合計する指定には9もありますが、実務では109を使うと非表示行を誤って含めにくくなります。

色で抽出した後の合計をセルに表示し続けたいときは、SUM関数よりSUBTOTAL関数が適しています。

フィルターの色を変更すると、合計結果も自動で切り替わります。

表示行の件数を数える数式

抽出後に表示されているデータの件数を数える場合は、COUNTAの機能番号を使います。

たとえばA列の担当者が必ず入力されているなら、次の数式で表示行数を求められます。

=SUBTOTAL(103,A2:A100)

103は、空白ではないセルを数え、フィルターや手動で非表示にした行を除外する指定です。

金額列に空欄がある可能性がある場合は、担当者や管理番号など、必ず値が入る列を対象にするのが安全です。

見出し行を範囲に含めると1件多く数えるため、必ず2行目以降を指定しましょう。

テーブル機能を使う集計表

データ範囲をテーブルとして設定すると、フィルターと集計行を管理しやすくなります。

表内のセルを選択してCtrlキーとTキーを押し、先頭行をテーブルの見出しとして使用する設定を確認します。

テーブルデザインタブで集計行にチェックを入れると、表の最下部に集計用の行が追加されます。

金額列の集計行では、下向き矢印から合計を選択できます。

テーブルの集計行はフィルターと連動するため、色で絞り込んだ結果を確認する作業と相性がよい機能です。

【操作のポイント】フィルターで見えている数値だけを計算するならSUBTOTAL関数を使います。

件数確認には103、金額合計には109を使うと、非表示行を除外した集計ができます。

VBAによるセル色のカウントと合計【ユーザー定義関数】

対象範囲	基準セル	用途
B2:B100	F2 黄色	黄色セルの件数
C2:C100	F2 黄色	黄色行の金額合計

続いては、塗りつぶし色そのものを条件にして、セル数や金額を集計するVBAの考え方を解説していきます。

VBAを使うと、セルのInterior.ColorやFont.Colorを参照できるため、標準関数だけでは難しい色による直接判定が可能になります。

ただし、ファイルはマクロ有効ブックとして保存する必要があり、組織のセキュリティルールにも注意が必要です。

色付きセルを数えるユーザー定義関数

AltキーとF11キーを押してVBAエディターを開き、挿入から標準モジュールを選択します。

標準モジュールに次のコードを貼り付けると、基準セルと同じ背景色のセルを数える関数を作成できます。

Function CountByColor(TargetRange As Range, ColorCell As Range) As Long
    Dim cell As Range
    For Each cell In TargetRange
        If cell.Interior.Color = ColorCell.Interior.Color Then
            CountByColor = CountByColor + 1
        End If
    Next cell
End Function

Function CountByColor(TargetRange As Range, ColorCell As Range) As Long

Dim cell As Range

For Each cell In TargetRange

If cell.Interior.Color = ColorCell.Interior.Color Then

CountByColor = CountByColor + 1

End If

Next cell

End Function

たとえばF2を黄色に塗り、B2:B100の黄色セルを数える場合は、ワークシートに次の数式を入力します。

=CountByColor(B2:B100,F2)

基準色を持つセルを別に用意しておくと、色コードを直接指定せずに数式を書けます。

この関数はセルの背景色を比較するため、色の意味が明確な帳票で使うと便利です。

色付き行の金額を合計する関数

色付きの区分セルに対応する金額だけを合計したい場合は、条件範囲と合計範囲を分けた関数を作成します。

Function SumByColor(ColorRange As Range, SumRange As Range, ColorCell As Range) As Double
    Dim i As Long
    For i = 1 To ColorRange.Count
        If ColorRange.Cells(i).Interior.Color = ColorCell.Interior.Color Then
            SumByColor = SumByColor + SumRange.Cells(i).Value
        End If
    Next i
End Function