エクセルのSUMIF関数で色付きセルを合計するには
Excelでデータを扱う際、セルに色を付けることで情報の整理や集計がしやすくなります。
セルに目印として色を付けたセルに入っている数値だけを合計したい場面もあるでしょう。そんな時、どのような方法を使えば良いのでしょうか。
Excelのワークシート関数のうち、さまざまな条件を指定してセルの合計を行うSUMIF関数で、色付きセルを条件に合計すればいいと考えるかもしれません。
しかし、実はSUMIF関数ではセルの色を条件に指定することができません。そのため、SUMIF単体では色付きセルの合計を出すことができず、集計方法を工夫する必要があります。
この記事では、Excelで色付きセルの合計をSUMIFで取得したい方のために、フィルターやGET.CELL関数と組み合わせて実現する方法を解説します。また、他の方法としてSUBTOTAL関数で行う方法も解説します。
色付きセルを合計するメリットとは
Excelでセルに色を付けると、重要なデータを視覚的に強調することができ、より見やすい資料を作成することができます。
例えば、売上データを月別にまとめた表で、目標売上を達成した売上額だけに色を付ければ、どの月が目標に達しているか一目ですぐに確認できます。
データの集計に役立つだけでなく、社内で共有する資料やレポートを作成する際、色付きのセルはデータの意味合いをより単純にし、分かりやすく伝えるためにも役に立ちます。
ただし、色そのものはデータとしての意味を持たないため、その色が何を意味するのかの補足説明が必要です。また、色は人によって見え方が違う場合があることにも注意しましょう。
SUMIF関数の基礎知識
まず、SUMIF関数の基礎的な知識について説明します。基本的な構文や使用例を確認しましょう。また、SUMIF関数の機能で色付きセルを合計できるのかについても解説します。
SUMIF関数の構文
SUMIF関数は、Excelで指定した条件に一致するデータのみを合計する関数です。
数値、文字列、日付など、特定の条件に基づいてデータを合計する際に便利なため、売上管理や営業成績などのデータ集計を行う際によく使用されます。
例えば、売上管理表で特定の商品や店舗ごとの売上を合計したい場合、SUMIF関数を使えば簡単に行うことができます。SUMIF関数の構文と引数は以下の通りです。
■SUMIF関数の構文
=SUMIF(範囲, 検索条件, 合計範囲)
「範囲」は条件判定の対象の値があるセル範囲、「検索条件」は合計の対象とするデータの条件、「合計範囲」は合計する値があるセルの範囲を指定します。
なお、「合計範囲」は省略可能で、省略時には「範囲」のセルが合計されます。
SUMIF関数の使用例
SUMIF関数の具体的な使用例を見てみましょう。例えば売上表から「東京」にある店舗の売上を合計します。
セル範囲「A2:A11」から「東京」に一致する店舗を探し、その売上データを「C2:C11」から抽出して合計するには、「=SUMIF(A2:A11, "東京", C2:C11)」の式を使います。
このように、SUMIFを使うと「範囲」で条件に該当するデータを抽出し、「合計範囲」の中の対応する数値を集計することができます。
検索条件を文字列で指定する場合は、ダブルクォーテーション(")で前後を囲みます。囲まないと、合計結果が「0」となり正しく集計できませんので気を付けましょう。
また、式を記入する時に「範囲」と「合計範囲」を指定する高さを一致させる必要があります。範囲がずれていると合計が正しく出ないことがありますがエラーは表示されず、間違えに気付きにくいためです。
SUMIF関数で直接色付きセルを取得できない
SUMIFはセルの値を判定の条件に使って合計を出す関数で、セルの背景色を取得して集計することはできません。
一見、「セルに色がついていたら条件に合うと判断する」と設定できそうですが、SUMIF関数だけでなくIF関数やCOUNTIF関数など、他のワークシート関数でもセルに付けた色を直接認識できないため、セルの色を条件に処理や集計を行うことはできません。
そのため、セルに付けた色を条件に集計したい場合、SUMIF関数と他の方法を組み合わせて工夫する必要があります。
例えば、Excelで使用できるプログラミング言語であるVBAを使えば、色付きのセルに入っている値を取得して集計することができます。
しかし、VBAを使う場合はVBAのコードを記述する必要があるため、少し難易度が高くなります。そのため、今回はVBAを使わない方法を次項から紹介していきます。
SUMIF関数とフィルターで色付きセルを合計する方法
ここでは、SUMIF関数とフィルターを組み合わせて、色付きセルのデータを合計する方法を解説します。
SUMIF関数単独ではセルの色を条件に集計することはできませんが、フィルターで色付きセルを抽出し、フラグを立てて合計を計算します。
色付きセルをフィルターで抽出してフラグを付ける
例えば、ある会社の渋谷店で「商品A」の販売キャンペーンを行ったところ、予想を超える売れ行きで在庫が足りなくなってしまったため、他の店舗から在庫を集めたいとします。
担当者は各店舗に連絡して在庫を確認し、在庫を貸しても良いと回答された店舗の在庫数のセルを、黄色で色付けしました。この黄色のセルを合計して、全部で何個の在庫を確保できるか知りたいケースを考えてみましょう。
まず、集計するデータ範囲をすべて選択します。次に、Excelの「データ」メニューから「フィルター」をクリックしてフィルタリングを有効にします。すると、各列の上にフィルターコントロール（「▼」ボタン）が表示されます。
対象となる列のフィルターコントロールボタンをクリックし、「色フィルター」を選択します。次に、フィルターしたい特定の色を指定すると、その色が付いたセルだけが表示されます。
フィルターで色付きセルだけが表示されている状態で、その隣の空いている列に「1」など任意の数値や文字を入力し、これをフラグとしてSUMIFでの合計に使用します。
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関数を組み合わせて、色付きセルの個数をカウントする方法もあわせて解説します。
GET.CELL関数を使って色番号を取得する
色番号やフォント情報など、セルに関する情報を取得する方法として、Excel 4.0マクロ関数の1つであるGET.CELL関数があります。このGET.CELL関数とSUMIFを組み合わせると、色付きセルの合計を出すことができます。
Excel 4.0マクロ関数は通常のワークシートでは直接使えないため、「名前の定義」を使用して設定します。例えば、集計表の中から黄色で色付けしたセルの合計を求める場合を考えてみましょう。
まず、Excelの「数式」タブから「名前の定義」を選び、任意の名前を設定します。ここでは「colorCode」としておきます。次に「参照範囲」に「=GET.CELL(63, !B2)」の数式を入力します。
「63」は、セルの背景色の色番号を取得するための引数です。これで指定したセルの色番号を取得できるようになります。黄色の場合、セルの色番号は「6」です。
「B2」は、色付きかどうかを判定する対象の列の一番上のセルを指定します。ここでは、「在庫数」列の1番上にある「B2」を入力しています。
色番号をセルに適用し、SUMIF関数を使って黄色のセルを合計する
設定した名前である「colorCode」を使って、色番号をセルに表示します。
色番号を表示したいセルに「=colorCode」と入力して、オートフィル機能で他のセルにもコピーします。
すると、対象のセルに色がついていない場合「0」、黄色で色付けされている場合にはその色番号を取得して「6」が表示されます。これがSUMIFで合計するためのフラグの役割を果たします。
ここまでの準備ができたら、SUMIFを使って「=SUMIF(C2:C16, 6, B2:B16)」の式を入力します。これで、色番号が「6」に該当する、つまり黄色で色が付いているセルの数値を合計できます。
なお、GET.CELL関数はマクロ関数であるため、GET.CELL関数を使用したExcelシートを保存する場合には、マクロ有効ブック（.xlsmファイル）として保存する必要があります。
GET.CELL関数を使って色付きセルをカウントすることもできる
SUMIF関数のように合計ではなく、特定の色のセルの個数がいくつあるかを知りたいときには、GET.CELL関数とCOUNTIF関数を組み合わせれば、色付きセルの数をカウントすることもできます。
GET.CELL関数で取得した色番号を基に、COUNTIF関数を使って特定の色のセルをカウントします。
例えば、黄色で色付けしたセルをカウントする場合、「=COUNTIF(B1:B13, 6)」のようにCOUNTIF関数を使用して、黄色の色番号である「6」が入力されているセルをカウントします。
COUNFIF関数で指定する色番号を変えることで、色別にカウントすることもできます。
GET.CELL関数を使えば、色付きセルを合計する場合でも、色別にカウントする場合でも、VBAやフィルターなしで行うことができます。
フィルターとSUBTOTAL関数で色付きセルを合計する方法
色付きセルの合計を出す他の方法として、フィルターとSUBTOTAL関数を使う方法を解説します。フィルターで色付きセルを抽出し、SUBTOTAL関数でその抽出結果を合計します。
SUBTOTAL関数の基本と仕組み
SUBTOTAL関数は、フィルターで非表示にされたデータを無視し、表示されたデータだけに対して合計、カウント、平均などの集計を行う関数です。
SUMIS関数では、「合計範囲」に指定したセルは、フィルターで非表示になっていてもすべて合計の対象になります。
つまり、SUMIFではフィルターをかけて色付きセルだけを表示した状態でも、非表示の色付きでないセルも合計されます。そのため、色付きセルだけを合計したい場合は、前述した通りフラグの役割をする列が必要になります。
しかし、SUBTOTAL関数を使用すればフィルター適用後に表示されたデータだけを合計することができるため、別でフラグを用意しなくてもそのまま合計することができます。
SUBTOTAL関数の構文
SUBTOTAL関数の構文は以下の通りです。「範囲」で指定した範囲に対し、「関数番号」で指定した集計を行います。
■SUBTOTAL関数の構文
=SUBTOTAL(関数番号, 範囲)
関数番号は、集計の種類を指定する番号で、指定する番号に応じた集計方法が決まっています。例えば、「101」を指定すると、フィルターで非表示にされたデータを無視して平均（AVERAGE）を計算することができます。
同様に、「102」はカウント（COUNT）、「104」は最大値（MAX）、「105」は最小値（MIN）、「109」は合計（SUM）を意味します。
今回はフィルターで非表示にしたデータを除いた値を合計したいので、「109」を使います。
色付きセルをフィルターで抽出する
SUBTOTAL関数を使う準備として、まずは色付きセルをフィルターで抽出します。この手順は、前述したフィルターとSUMIFを組み合わせる場合と同じです。
集計したいデータ範囲全体を選択して、Excelの「データ」タブにある「フィルター」ボタンをクリックします。すると、各列の上にフィルターコントロール（「▼」ボタン）が表示されます。
次に、色付きセルが含まれる列のフィルターコントロールボタンをクリックし、表示されるメニューで「色フィルター」にマウスカーソルを当て、フィルターしたい特定の色を選択すると、その色が付いたセルだけが表示されます。
SUBTOTAL関数で色付きセルを合計する方法
フィルターで色付きセルを抽出した後、表示されたデータのみをSUBTOTAL関数で合計できます。例として、下図のように黄色いセルの合計を計算するには、「=SUBTOTAL(109, B2:B16)」の式を使います。
引数の「109」は前述のとおり合計（SUM）を意味する関数番号で、A1:A10は集計したいセルの範囲です。これにより、フィルターで表示された色付きセルだけが合計されます。
なお、フィルターを解除すると、SUBTOTAL関数は全てのデータを集計するようになるため、また色付きセルだけの合計を取得したい場合は、再度フィルターをかける必要があります。
エクセルのSUMIF関数で色付きセルを分かりやすく集計しよう
ここまで、エクセルでSUMIF関数を使って色付きセルを集計する方法について解説してきました。
SUMIF関数で直接セルの色を条件にして合計することはできませんが、フィルターやGET.CELL関数と組み合わせたり、SUBTOTAL関数を使ったりすることで、色付きセルの合計を取得できることが分かりました。
色付きセルを使ってデータを視覚的に分かりやすく管理し、さらに色付きセルに基づいた集計を行いたい場合は、これらの方法を活用して作業を効率化してみてください。
マイナビエージェントに無料登録して
転職サポートを受ける
その他のエクセル関連記事
その他、エクセルに関連する内容は、下記の記事でもご紹介しています。ぜひ参考にご覧ください。
編集部オススメコンテンツ
アンドエンジニアへの取材依頼、情報提供などはこちらから
