エクセルで色付きセルをSUMIF関数で合計するには？方法を解説

エクセルのSUMIF関数で色付きセルを合計するには

Excelでデータを扱う際、セルに色を付けることで情報の整理や集計がしやすくなります。

セルに目印として色を付けたセルに入っている数値だけを合計したい場面もあるでしょう。そんな時、どのような方法を使えば良いのでしょうか。

Excelのワークシート関数のうち、さまざまな条件を指定してセルの合計を行うSUMIF関数で、色付きセルを条件に合計すればいいと考えるかもしれません。

しかし、実はSUMIF関数ではセルの色を条件に指定することができません。そのため、SUMIF単体では色付きセルの合計を出すことができず、集計方法を工夫する必要があります。

この記事では、Excelで色付きセルの合計をSUMIFで取得したい方のために、フィルターやGET.CELL関数と組み合わせて実現する方法を解説します。また、他の方法としてSUBTOTAL関数で行う方法も解説します。

Excelでセルに色を付けると、重要なデータを視覚的に強調することができ、より見やすい資料を作成することができます。

例えば、売上データを月別にまとめた表で、目標売上を達成した売上額だけに色を付ければ、どの月が目標に達しているか一目ですぐに確認できます。

データの集計に役立つだけでなく、社内で共有する資料やレポートを作成する際、色付きのセルはデータの意味合いをより単純にし、分かりやすく伝えるためにも役に立ちます。

ただし、色そのものはデータとしての意味を持たないため、その色が何を意味するのかの補足説明が必要です。また、色は人によって見え方が違う場合があることにも注意しましょう。

SUMIF関数の基礎知識

まず、SUMIF関数の基礎的な知識について説明します。基本的な構文や使用例を確認しましょう。また、SUMIF関数の機能で色付きセルを合計できるのかについても解説します。

SUMIF関数は、Excelで指定した条件に一致するデータのみを合計する関数です。

数値、文字列、日付など、特定の条件に基づいてデータを合計する際に便利なため、売上管理や営業成績などのデータ集計を行う際によく使用されます。

例えば、売上管理表で特定の商品や店舗ごとの売上を合計したい場合、SUMIF関数を使えば簡単に行うことができます。SUMIF関数の構文と引数は以下の通りです。

■SUMIF関数の構文

=SUMIF(範囲, 検索条件, 合計範囲)

「範囲」は条件判定の対象の値があるセル範囲、「検索条件」は合計の対象とするデータの条件、「合計範囲」は合計する値があるセルの範囲を指定します。

なお、「合計範囲」は省略可能で、省略時には「範囲」のセルが合計されます。

SUMIF関数の具体的な使用例を見てみましょう。例えば売上表から「東京」にある店舗の売上を合計します。

セル範囲「A2:A11」から「東京」に一致する店舗を探し、その売上データを「C2:C11」から抽出して合計するには、「=SUMIF(A2:A11, "東京", C2:C11)」の式を使います。

このように、SUMIFを使うと「範囲」で条件に該当するデータを抽出し、「合計範囲」の中の対応する数値を集計することができます。

検索条件を文字列で指定する場合は、ダブルクォーテーション(")で前後を囲みます。囲まないと、合計結果が「0」となり正しく集計できませんので気を付けましょう。

また、式を記入する時に「範囲」と「合計範囲」を指定する高さを一致させる必要があります。範囲がずれていると合計が正しく出ないことがありますがエラーは表示されず、間違えに気付きにくいためです。

SUMIF関数とは？基本構文から使い方、応用まで詳しく解説

SUMIFSとは？基本的な使い方や具体的な活用例、注意点を解説

SUMIF関数で複数条件を指定する！集計関数の概要や使い方を図解

SUMIFはセルの値を判定の条件に使って合計を出す関数で、セルの背景色を取得して集計することはできません。

一見、「セルに色がついていたら条件に合うと判断する」と設定できそうですが、SUMIF関数だけでなくIF関数やCOUNTIF関数など、他のワークシート関数でもセルに付けた色を直接認識できないため、セルの色を条件に処理や集計を行うことはできません。

そのため、セルに付けた色を条件に集計したい場合、SUMIF関数と他の方法を組み合わせて工夫する必要があります。

例えば、Excelで使用できるプログラミング言語であるVBAを使えば、色付きのセルに入っている値を取得して集計することができます。

しかし、VBAを使う場合はVBAのコードを記述する必要があるため、少し難易度が高くなります。そのため、今回はVBAを使わない方法を次項から紹介していきます。

VBAとは？基本的な知識やできること、マクロとの違い、メリットを解説！

SUMIF関数とフィルターで色付きセルを合計する方法

ここでは、SUMIF関数とフィルターを組み合わせて、色付きセルのデータを合計する方法を解説します。

SUMIF関数単独ではセルの色を条件に集計することはできませんが、フィルターで色付きセルを抽出し、フラグを立てて合計を計算します。

例えば、ある会社の渋谷店で「商品A」の販売キャンペーンを行ったところ、予想を超える売れ行きで在庫が足りなくなってしまったため、他の店舗から在庫を集めたいとします。

担当者は各店舗に連絡して在庫を確認し、在庫を貸しても良いと回答された店舗の在庫数のセルを、黄色で色付けしました。この黄色のセルを合計して、全部で何個の在庫を確保できるか知りたいケースを考えてみましょう。

まず、集計するデータ範囲をすべて選択します。次に、Excelの「データ」メニューから「フィルター」をクリックしてフィルタリングを有効にします。すると、各列の上にフィルターコントロール（「▼」ボタン）が表示されます。

対象となる列のフィルターコントロールボタンをクリックし、「色フィルター」を選択します。次に、フィルターしたい特定の色を指定すると、その色が付いたセルだけが表示されます。

フィルターで色付きセルだけが表示されている状態で、その隣の空いている列に「1」など任意の数値や文字を入力し、これをフラグとしてSUMIFでの合計に使用します。

フィルターを解除すると、色付きセルの隣には「1」のフラグが付いている状態が残ります。このフラグを使って、SUMIF関数で色付きセルの数や合計を集計します。

例えば、フラグを立てた列がC列で、集計したいデータがB列にある場合、「=SUMIF(C2:C16, 1,B2:B16)」の数式を入力します。

すると、「C2:C16」の範囲でフラグの「1」がある行の集計範囲のセル、すなわち色付きセルだけがSUMIFで合計されます。

セルの色付けを変更して、再度色付きセルを合計したい場合は、1度フラグとして入力されている「1」を削除してから再度フィルターをかけてフラグを設定し直す必要があります。

前のフラグを残したままフィルターをかけると、前のフラグが残ったままになり、合計が正しく計算されない原因となります。

SUMIF関数とGET.CELL関数で色付きセルを合計する方法

次に、ExcelのSUMIF関数とGET.CELL関数を使って色付きセルを合計する方法について説明します。GET.CELL関数で色番号を取得し、SUMIF関数と組み合わせて色付きセルの合計を算出することができます。

また、GET.CELL関数とCOUNTIF関数を組み合わせて、色付きセルの個数をカウントする方法もあわせて解説します。

色番号やフォント情報など、セルに関する情報を取得する方法として、Excel 4.0マクロ関数の1つであるGET.CELL関数があります。このGET.CELL関数とSUMIFを組み合わせると、色付きセルの合計を出すことができます。

Excel 4.0マクロ関数は通常のワークシートでは直接使えないため、「名前の定義」を使用して設定します。例えば、集計表の中から黄色で色付けしたセルの合計を求める場合を考えてみましょう。

まず、Excelの「数式」タブから「名前の定義」を選び、任意の名前を設定します。ここでは「colorCode」としておきます。次に「参照範囲」に「=GET.CELL(63, !B2)」の数式を入力します。

「63」は、セルの背景色の色番号を取得するための引数です。これで指定したセルの色番号を取得できるようになります。黄色の場合、セルの色番号は「6」です。

「B2」は、色付きかどうかを判定する対象の列の一番上のセルを指定します。ここでは、「在庫数」列の1番上にある「B2」を入力しています。

Excelのマクロとは？初心者向けに概要からできること・作り方を解説

設定した名前である「colorCode」を使って、色番号をセルに表示します。

色番号を表示したいセルに「=colorCode」と入力して、オートフィル機能で他のセルにもコピーします。

すると、対象のセルに色がついていない場合「0」、黄色で色付けされている場合にはその色番号を取得して「6」が表示されます。これがSUMIFで合計するためのフラグの役割を果たします。

ここまでの準備ができたら、SUMIFを使って「=SUMIF(C2:C16, 6, B2:B16)」の式を入力します。これで、色番号が「6」に該当する、つまり黄色で色が付いているセルの数値を合計できます。

なお、GET.CELL関数はマクロ関数であるため、GET.CELL関数を使用したExcelシートを保存する場合には、マクロ有効ブック（.xlsmファイル）として保存する必要があります。

SUMIF関数のように合計ではなく、特定の色のセルの個数がいくつあるかを知りたいときには、GET.CELL関数とCOUNTIF関数を組み合わせれば、色付きセルの数をカウントすることもできます。

GET.CELL関数で取得した色番号を基に、COUNTIF関数を使って特定の色のセルをカウントします。

例えば、黄色で色付けしたセルをカウントする場合、「=COUNTIF(B1:B13, 6)」のようにCOUNTIF関数を使用して、黄色の色番号である「6」が入力されているセルをカウントします。

COUNFIF関数で指定する色番号を変えることで、色別にカウントすることもできます。

GET.CELL関数を使えば、色付きセルを合計する場合でも、色別にカウントする場合でも、VBAやフィルターなしで行うことができます。

フィルターとSUBTOTAL関数で色付きセルを合計する方法

色付きセルの合計を出す他の方法として、フィルターとSUBTOTAL関数を使う方法を解説します。フィルターで色付きセルを抽出し、SUBTOTAL関数でその抽出結果を合計します。

SUBTOTAL関数は、フィルターで非表示にされたデータを無視し、表示されたデータだけに対して合計、カウント、平均などの集計を行う関数です。

SUMIS関数では、「合計範囲」に指定したセルは、フィルターで非表示になっていてもすべて合計の対象になります。

つまり、SUMIFではフィルターをかけて色付きセルだけを表示した状態でも、非表示の色付きでないセルも合計されます。そのため、色付きセルだけを合計したい場合は、前述した通りフラグの役割をする列が必要になります。

しかし、SUBTOTAL関数を使用すればフィルター適用後に表示されたデータだけを合計することができるため、別でフラグを用意しなくてもそのまま合計することができます。

SUBTOTAL関数の構文は以下の通りです。「範囲」で指定した範囲に対し、「関数番号」で指定した集計を行います。

■SUBTOTAL関数の構文

=SUBTOTAL(関数番号, 範囲)

関数番号は、集計の種類を指定する番号で、指定する番号に応じた集計方法が決まっています。例えば、「101」を指定すると、フィルターで非表示にされたデータを無視して平均（AVERAGE）を計算することができます。

同様に、「102」はカウント（COUNT）、「104」は最大値（MAX）、「105」は最小値（MIN）、「109」は合計（SUM）を意味します。

今回はフィルターで非表示にしたデータを除いた値を合計したいので、「109」を使います。

SUBTOTAL関数を使う準備として、まずは色付きセルをフィルターで抽出します。この手順は、前述したフィルターとSUMIFを組み合わせる場合と同じです。

集計したいデータ範囲全体を選択して、Excelの「データ」タブにある「フィルター」ボタンをクリックします。すると、各列の上にフィルターコントロール（「▼」ボタン）が表示されます。

次に、色付きセルが含まれる列のフィルターコントロールボタンをクリックし、表示されるメニューで「色フィルター」にマウスカーソルを当て、フィルターしたい特定の色を選択すると、その色が付いたセルだけが表示されます。

フィルターで色付きセルを抽出した後、表示されたデータのみをSUBTOTAL関数で合計できます。例として、下図のように黄色いセルの合計を計算するには、「=SUBTOTAL(109, B2:B16)」の式を使います。

引数の「109」は前述のとおり合計（SUM）を意味する関数番号で、A1:A10は集計したいセルの範囲です。これにより、フィルターで表示された色付きセルだけが合計されます。

なお、フィルターを解除すると、SUBTOTAL関数は全てのデータを集計するようになるため、また色付きセルだけの合計を取得したい場合は、再度フィルターをかける必要があります。

エクセルのSUMIF関数で色付きセルを分かりやすく集計しよう

ここまで、エクセルでSUMIF関数を使って色付きセルを集計する方法について解説してきました。

SUMIF関数で直接セルの色を条件にして合計することはできませんが、フィルターやGET.CELL関数と組み合わせたり、SUBTOTAL関数を使ったりすることで、色付きセルの合計を取得できることが分かりました。

色付きセルを使ってデータを視覚的に分かりやすく管理し、さらに色付きセルに基づいた集計を行いたい場合は、これらの方法を活用して作業を効率化してみてください。

その他のExcel関連記事

Excelのチェックボックスを作るには？新機能と従来機能を解説

Excelのプルダウンを作成するには？初心者向けに3ステップで解説

Excelのセル内改行方法を解説！実務で使える活用テクニック

Excelで日付から曜日を算出するには？基本からカスタマイズまで

Excelの表の作り方！基本から応用まで表の作成方法を図解

PDFファイルを簡単にExcelに変換する方法と注意点などを解説

Excelの文字を縦書きにするには？基本操作と表示の調整を図解

Excelで重複データを削除するには？確認と削除の仕方を図解

Excelで掛け算をマスターしよう！初心者向けにやさしく解説

Excelで文字列を結合するには？文字列演算子や関数の使い方

Excelのマクロとは？初心者向けに概要からできること・作り方を解説

Excelを読み取り専用にするには？解除できない場合の対処も解説

Excelで複数条件の合計！SUMIFS関数OR条件をやさしく解説

ChatGPTをエクセルで活用？おすすめのアドインソフトを紹介

VLOOKUPでテーブルを活用！Excel作業を効率化する方法を紹介

【excelでpythonが使える】Python in Excelの使い方や活用例を解説！