エクセルを使って毎日の業務や家計簿の管理をしていると、重要な項目を目立たせるためにセルに色を付ける場面がよくあります。視覚的にわかりやすくなるので、ついつい「確認済み」や「要対応」といった意味を持たせて色分けしてしまうものです。しかし、いざ集計しようとしたときに「この色がついたセルだけを合計したい」と思っても、普段使っているCOUNTIF関数やSUMIF関数ではうまくいかずに困ってしまった経験はないでしょうか。実はエクセルの標準機能としての関数には、色を直接認識して計算するものは存在しません。そのため、少し工夫した代替案を知っておく必要があります。
この記事を読むことで、以下のメリットが得られます。
・ エクセルの標準関数では色付きセルの集計ができない理由を正しく理解できる
・ フィルター機能とSUBTOTAL関数を組み合わせて色付きセルを集計する具体的な手順がわかる
・ 4.0マクロ関数やユーザー定義関数といった少し高度な裏ワザ的な手法の存在に気付ける
・ 色に頼らずにデータを管理することで、より効率的な集計作業を行うためのヒントが得られる
エクセルで色付きセルを合計したいときにCOUNTIFは使える？
ここではエクセルで色付きセルを合計したいときにCOUNTIFは使えるのか、その基本的な仕組みと現状の制約について説明していきます。多くの人が直感的に「できるはず」と考えがちな操作ですが、実際にはエクセルの仕様上の壁が存在します。まずは基礎知識として、なぜ標準の関数では色が扱えないのか、そしてどのような誤解が多いのかを順に見ていきましょう。
・ エクセルで色のついたセルを合計する関数はある？
・ エクセルで色付きセルを合計するSUMIFの噂
・ セルに色がついていたらIFで判定できるか
・ エクセルで赤文字だけカウントする関数の現状
・ 条件付き書式で色を付けたセルを数える関数の壁
・ エクセルの色付きセルを合計する簡単な考え方
エクセルで色のついたセルを合計する関数はある？
エクセルを使い慣れてくると、様々なデータ処理に関数を用いるのが当たり前になります。そのため、セルの背景色や文字色を基準にして「エクセルで色のついたセルを合計する関数」も当然あるだろうと期待してしまうのは無理もありません。しかし、残念ながら現在のエクセルの標準機能として、セルの「色」という書式情報を直接参照して合計やカウントを行う関数は用意されていないのです。これを知らずに、関数リストの中を必死に探して時間を浪費してしまうケースは非常に多く見られます。
本来、エクセルの関数はセルに入力された「数値」や「文字列」といったデータそのものを計算対象としています。一方で、セルの色というのはあくまで「書式」という見た目の情報であり、データそのものではないという扱いになっています。このため、SUM関数やCOUNT関数などは、セルが黄色であっても赤色であっても、その中身の数値だけを見て計算を行います。視覚的にわかりやすくするための色が、データ処理の観点からは計算の対象外になってしまうというのは、多くのユーザーにとって盲点となりやすい部分です。まずは「標準関数に色を判別する能力はない」という事実を認識することが、解決への第一歩となります。
エクセルで色付きセルを合計するSUMIFの噂
インターネット上で検索をしていると、「エクセルで色付きセルを合計するならSUMIFを使えばいい」といった断片的な情報や噂を目にすることがあるかもしれません。しかし、これは多くの場合、情報の読み違えか、あるいは非常に限定的な状況を指している可能性が高いです。SUMIF関数は、指定した範囲の中で「特定の条件」に一致するセルの値を合計するための非常に便利な関数ですが、この「条件」に指定できるのは、あくまでセルの「値」に限られます。例えば「数値が100以上のセル」や「商品名がAのセル」といった条件は設定できますが、「背景色が黄色のセル」という条件は設定できません。
もしSUMIF関数を使って色付きセルのような集計ができている例があるとしたら、それはおそらく「作業列」を使っているケースでしょう。色の意味に対応する数値や文字を隣の列に入力しておき、その作業列をSUMIFの条件として使っていると考えられます。つまり、直接的に色を見ているわけではなく、間接的に色に対応するデータを集計しているに過ぎません。このように、SUMIF関数自体には色を識別する機能は備わっていないため、関数単体で色付きセルの合計を出そうと試行錯誤しても、望む結果は得られないことを理解しておく必要があります。
セルに色がついていたらIFで判定できるか
条件分岐を行う関数として最も有名なのがIF関数です。「もし〇〇ならば××する」という論理式を組み立てることができるため、「セルに色がついていたらIFで判定して、数値を返す」というような処理ができれば便利だと考えるのは自然な発想です。しかし、このIF関数においても、判定基準となる論理式で参照できるのはセルの「値」だけです。セルの背景色やフォントの色が何色であるかという情報を、IF関数の条件式の中に直接記述する方法は標準では用意されていません。
例えば、A1セルが赤色だった場合に「赤」と表示させたいと思っても、=IF(A1="赤色", "赤", "") のように書くことはできません。エクセルにとってA1セルにあるのは数値や文字データだけであり、そこに塗られた色は見えていないのと同じだからです。もちろん、VBA（マクロ）を使えば色の情報を取得してIF文のような処理を自作することは可能ですが、通常のワークシート関数としてのIF関数では、色の有無や種類を条件分岐のトリガーにすることは不可能です。この限界を知らずに数式を複雑にして悩んでしまうことがないよう、IF関数はあくまで「値」を判定するものだと割り切って考えることが大切です。
エクセルで赤文字だけカウントする関数の現状
背景色だけでなく、重要な数値を強調するために文字色を赤くすることもあるでしょう。そして「エクセルで赤文字だけカウントする関数」を使って、赤字になったデータの個数や合計を知りたいというニーズも頻繁に発生します。しかし、これまで説明してきた背景色と同様に、文字の色（フォントカラー）についても、エクセルの標準関数では直接参照することができません。黒い文字も赤い文字も、関数から見れば全く同じデータとして扱われます。
特に経理関係の書類などで、マイナスの数値を赤文字で表現することはよくあります。もし、その赤文字が「セルの書式設定」によって「負の数は赤にする」というルールで表示されているのであれば、それは「0より小さい」という数値的な条件でCOUNTIFなどが使えます。しかし、手動でフォントの色を赤に変えた場合や、特定の条件付き書式以外のルールで色を変えた場合には、その「赤色である」という状態を関数で拾うことはできません。見た目上は明確に区別されていても、データとしては区別されていない状態です。赤文字のデータを集計したい場合は、色がつく原因となった「条件」の方に着目し、その条件を元に関数を組み立てるのが本来のアプローチとなります。
条件付き書式で色を付けたセルを数える関数の壁
手動で色を塗るのではなく、一定のルールに基づいて自動で色を変える「条件付き書式」を利用している方も多いでしょう。これならルールが明確なのだから、「条件付き書式で色を付けたセルを数える関数」があっても良さそうに思えます。しかし、ここにも壁があります。条件付き書式はあくまで「表示上の装飾」を変える機能であり、セルの値そのものを変更しているわけではありません。そのため、条件付き書式で色が赤くなっていても、COUNTIF関数などがその色を検知することはできないのです。
ただし、条件付き書式の場合は、手動で色を塗った場合とは少し状況が異なります。なぜなら、色がつくための「条件」が論理的に存在しているからです。例えば「80点以上なら黄色にする」という条件付き書式を設定しているなら、色を数える代わりに「80点以上のセル」をCOUNTIFで数えれば、結果的に黄色いセルの数と一致します。つまり、条件付き書式で色がついたセルを数えたい場合は、色の結果そのものを数えようとするのではなく、色を付けた「元の条件」を数式で再現することで、間接的に目的を達成することが可能です。これが最もスマートで正確な解決策となることが多いです。
エクセルの色付きセルを合計する簡単な考え方
ここまで、標準の関数では色を直接扱えないことを説明してきました。では、諦めるしかないのかというと、そうではありません。関数に頼らずに視点を変えることで、「エクセルの色付きセルを合計する簡単な」方法はいくつか存在します。最も基本的で、かつ特別な知識がなくてもすぐに実行できるのが、エクセルの「並べ替え」や「フィルター」機能を活用する方法です。関数で自動的に計算することにこだわらなければ、操作自体は非常にシンプルです。
例えば、色ごとにデータを並べ替えてしまえば、同じ色のセルが固まりますから、その範囲をマウスで選択するだけで、ステータスバーに合計や個数が表示されます。これなら数式を組む必要すらありません。また、フィルター機能を使えば「色フィルター」で特定の色だけを表示させることができます。この「抽出された状態」に対して集計を行えばよいのです。関数だけで完結させようとすると非常に高度な技術が必要になりますが、エクセルの標準機能を組み合わせるという柔軟な考え方を持てば、色付きセルの集計は決して難しい作業ではありません。次章では、これらの代替案についてさらに詳しく解説していきます。
エクセルで色付きセルを合計するCOUNTIFの代替案
ここでは、標準関数では難しい色集計をどうやって実現するか、エクセルで色付きセルを合計するCOUNTIFの代替案について具体的に説明していきます。関数だけで悩むよりも、エクセルが本来持っている機能をうまく組み合わせる方が、遥かに簡単で確実な場合があります。実務ですぐに使えるテクニックから、少しマニアックな方法まで、幅広く可能性を探っていきましょう。順に見ていきましょう。
・ エクセルで色付きセルを合計するSUBTOTAL活用
・ エクセルで色付きセルをカウント！フィルターなし技
・ 名前定義とGET.CELL関数の活用術
・ VBAを使ったユーザー定義関数の可能性
・ 色ではなくデータで管理する重要性への気づき
・ エクセルで色付きセルを合計してCOUNTIFするまとめ
エクセルで色付きセルを合計するSUBTOTAL活用
最も現実的で、多くのユーザーにおすすめできる方法が、フィルター機能とSUBTOTAL関数を組み合わせる方法です。「エクセルで色付きセルを合計するSUBTOTAL活用」は、非常に強力なテクニックです。通常のSUM関数は、フィルターで非表示になっている行も含めてすべてのセルを合計してしまいますが、SUBTOTAL関数を使えば「フィルターで表示されているセルだけ」を計算対象にすることができます。これを利用するのです。
具体的な手順としては、まず表全体にフィルターを設定します。次に、集計結果を表示したいセルにSUBTOTAL関数を入力します（例：=SUBTOTAL(9, 集計範囲)）。この「9」はSUM（合計）を意味する番号です。準備ができたら、フィルターのメニューから「色フィルター」を選択し、集計したい色を選びます。すると、画面上にはその色の行だけが表示され、SUBTOTAL関数は自動的にその表示されているデータだけの合計を計算してくれます。これなら、難しいプログラミングや複雑な数式を組むことなく、マウス操作だけで色ごとの集計が可能です。カウントしたい場合は番号を「3」（COUNTA）などに変えるだけで対応でき、応用範囲も広いのが特徴です。
エクセルで色付きセルをカウント！フィルターなし技
フィルターを使えば集計できることはわかりましたが、いちいちフィルターを切り替えるのが面倒だという場合もあるでしょう。そこで「エクセルで色付きセルをカウント！フィルターなし」で行う方法がないか模索することになります。関数だけでこれを実現するには、どうしても「作業列」の助けが必要になります。フィルターなしで常に集計結果を表示させておきたい場合は、色情報を何らかの形で「値」として取り出す必要があるからです。
一つのアナログですが確実な方法は、隣の列に「1」などのフラグを立てることです。色を塗るタイミングで、隣のセルに「1」と入力するルールにします。そうすれば、その「1」が入っている列をSUMIFで合計すれば、フィルター操作なしで常に合計値が更新されます。「それが面倒だから色だけで判別したい」という気持ちはもっともですが、エクセルの仕様上、フィルターを使わずに自動計算させるには、この「色と同時にデータも入力する」という運用ルールの変更が、結果的に最も近道であり、ミスも少ない方法となります。もし完全に自動化したいなら、後述するマクロ関数やVBAの領域に踏み込む必要があります。
名前定義とGET.CELL関数の活用術
ここからは少し裏ワザ的な領域に入りますが、昔のエクセル（Excel4.0）で使われていたマクロ関数を利用する方法があります。それが「名前定義とGET.CELL関数」を活用するテクニックです。このGET.CELL関数は、セルの色番号などの情報を取得できる古い関数ですが、現在のワークシート上に直接入力しても動作しません。しかし、「名前の定義」の中で使用することで、現在でも擬似的に利用することが可能です。
具体的には、「数式」タブの「名前の管理」から新しい名前を定義し（例：ColorGet）、参照範囲に =GET.CELL(63, 参照セル) と記述します。引数の63は背景色を取得するコードです。そしてシート上で =ColorGet と入力すると、そのセルの色番号（数値）が表示されます。この色番号が表示されれば、あとはその数値をもとにしてCOUNTIFやSUMIFで集計が可能になります。ただし、この方法はファイル形式を「マクロ有効ブック(.xlsm)」にする必要がある場合があり、また将来的に互換性がなくなるリスクもゼロではありません。あくまで自己責任での利用となりますが、色情報を数値化できる強力な方法の一つです。
VBAを使ったユーザー定義関数の可能性
さらに自由度高く、かつ確実に色付きセルを集計したいのであれば、VBA（VisualBasicforApplications）を使って自作の関数を作るのが最終手段となります。「VBAを使ったユーザー定義関数の可能性」は無限大です。VBAを使えば、標準にはない「CountColor」や「SumColor」といった関数を自分でプログラミングして作り出すことができます。これを作ってしまえば、通常のSUM関数と同じような感覚で、=SumColor(範囲, 色見本のセル) のように使うことができるようになります。
コード自体はインターネット上で検索すれば多くのサンプルが見つかりますし、数行程度の記述で済むシンプルなものです。一度設定してしまえば、フィルター操作も作業列も不要で、リアルタイムに近い感覚で色集計が可能になります。ただし、VBAを含むファイルはセキュリティの警告が出たり、共有設定によっては他人が使えなかったりといった運用上のハードルがあります。また、色の変更は「値の変更」ではないため、色を変えた瞬間に再計算が走らない（F9キーで再計算が必要）という弱点もあります。メリットとデメリットをよく比較して導入を検討すべき手法です。
色ではなくデータで管理する重要性への気づき
様々な代替案を紹介してきましたが、ここで一度立ち止まって考えてみたいのが「色ではなくデータで管理する重要性」です。そもそも、なぜ色で情報を管理したくなるのでしょうか。それは直感的でわかりやすいからです。しかし、コンピュータであるエクセルにとっては、色はあくまで「飾り」であり、情報としての信頼性は「値」よりも劣ります。色が塗られているかどうかは、入力者の主観に左右されやすく、集計ミスや漏れの原因になりやすいのです。
もし業務で「色付きセルの集計」が頻繁に発生するのであれば、それはデータ管理の方法を見直すチャンスかもしれません。色を塗る代わりに、「ステータス」や「担当」といった列を一つ追加し、そこに「完了」「保留」「重要」といった文字や数値を入力するようにする。そして、その文字条件に基づいて条件付き書式で色を付ける。この順序に変えるだけで、集計はSUMIFやCOUNTIFで簡単にできるようになり、見た目のわかりやすさも維持できます。「色は結果としてつくもの」という設計にすることで、エクセル業務の効率と正確性は格段に向上します。
エクセルで色付きセルを合計しCOUNTIFするまとめ
今回はエクセルで色付きセルを合計したい場合のCOUNTIFの代替案についてお伝えしました。以下に、本記事の内容を要約します。
・ エクセルの標準関数には色を識別して集計する機能は存在しない
・ SUMIFやCOUNTIFはセルの値のみを対象とし書式は無視する
・ IF関数でもセルの色を条件分岐の判定基準にはできない
・ 赤文字などのフォントカラーも標準関数では判別不能である
・ 条件付き書式の色を数えるなら元の条件を数えるのが正解である
・ 最も簡単な代替案は並べ替え機能を使って手動で集計することである
・ フィルター機能の「色フィルター」を使えば特定の色だけ抽出できる
・ 抽出した状態でSUBTOTAL関数を使えば表示セルのみ合計できる
・ SUBTOTAL関数は関数番号を変えることで個数カウントも可能である
・ フィルターなしで自動化するには作業列を作るのが確実である
・ 4.0マクロ関数のGET.CELLを使えば色番号を取得可能である
・ VBAを使えば独自の色の集計関数を自作することができる
・ VBAによる色の集計は色変更時に自動再計算されない点に注意が必要
・ 色による管理は直感的だがデータ処理の観点からは非効率になりやすい
・ ステータス列を作って値で管理し条件付き書式で色を付けるのが理想である
エクセルでの色集計は、一見簡単そうで実は奥が深いテーマです。
まずはフィルターとSUBTOTAL関数という基本技をマスターし、必要に応じてデータ管理のルール自体を見直してみることをおすすめします。
色の見た目とデータの扱いやすさを両立させて、より快適なエクセルライフを送ってください。
これはCTAサンプルです。
内容を編集するか削除してください。
