【Excel】エクセルで色付きセルの合計をSUMIFで求める方法
Excelで背景色が付いたセルだけを合計したい場面では、SUMIF関数を使えば簡単に処理できそうに見えます。
しかし、SUMIF関数はセルの色そのものを条件として判定できないため、色付きセルの合計には少し工夫が必要です。
実務では、色を付ける前に条件列を作る方法、フィルターで色を絞る方法、定義名とGET.CELL関数を組み合わせる方法などを使い分けます。
色付きセルの合計で押さえたいポイント
・SUMIFは文字列や数値の条件には強いものの、塗りつぶし色は直接条件にできません。
・最も安定する方法は、色の意味を別列の文字や数値として管理してSUMIFで集計することです。
・すでに色だけが設定された表では、フィルターや補助列を利用して合計します。
この記事では、1行目に見出しがある売上表を例に、色付きセルの合計を求める考え方と具体的な数式を解説します。
表示色に頼りすぎず、あとから集計条件を変更しやすい表を作るコツも確認していきましょう。
色付きセルをSUMIFで合計する基本方法
| 商品 | 区分 | 売上 | 表示 |
|---|---|---|---|
| ノート | 重点 | 1200 | 黄色 |
| ペン | 通常 | 800 | なし |
| ファイル | 重点 | 1500 | 黄色 |
それではまず、色付きセルをSUMIFで合計するための基本的な考え方について解説していきます。
結論として、色を判定する代わりに、色が表す内容を区分列へ入力する方法が最も確実です。
色ではなく区分列を条件にする数式
上のサンプルでは、B列の区分が重点の行だけを黄色で表示し、C列に売上を入力している前提です。
黄色の売上を合計したい場合は、色が付いたD列ではなく、色の根拠となるB列をSUMIFの条件範囲に指定します。
=SUMIF(B2:B4,”重点”,C2:C4)
この数式では、B2からB4のうち重点と一致するセルを探し、同じ行にあるC2からC4の数値を合計します。
結果は1200と1500を足した2700です。
条件範囲、検索条件、合計範囲という順番を意識すると、数式の意味を確認しやすくなります。
セルの色は見やすくするための表示であり、集計の条件は区分列で持たせると、並べ替えやコピーをしても集計のルールが崩れにくくなります。
【操作のポイント】黄色のセルだけを数えたい場合でも、黄色にした理由を区分列へ重点、要確認、完了などの文字で残します。
条件付き書式とSUMIFを連動させる設定
毎回手作業で塗りつぶしを設定すると、区分と色が食い違うことがあります。
そこで、B列が重点のときだけC列を黄色にする条件付き書式を設定すると便利です。
C2を選択し、ホームタブの条件付き書式から新しいルールを選び、数式を使用して書式設定するセルを決定を選択します。
条件付き書式の数式
=$B2=”重点”
適用先をC2:C100などに設定すると、B列が重点の行にある売上セルだけが自動で黄色になります。
列記号Bの前にドル記号を付けることで、横方向に書式をコピーしても判定列がB列のまま固定されます。
行番号2にはドル記号を付けないため、各行ごとに区分を判定できます。
条件付き書式は色を自動表示する仕組みであり、SUMIFは区分を合計する仕組みです。
この役割を分けることで、入力者が変わっても表のルールを維持しやすくなります。
【操作のポイント】SUMIFの条件と条件付き書式の条件を同じ語句にそろえ、重点と重点対象のような表記ゆれを避けます。
数式を入力してオートフィルする手順
月別や担当者別に重点売上を求める場合は、集計式を横または下へコピーすることがあります。
たとえばF1に1月、G1に2月という月見出しがあり、売上表に月列がある場合は、複数条件を扱えるSUMIFS関数が向いています。
=SUMIFS($C$2:$C$100,$B$2:$B$100,”重点”,$A$2:$A$100,F$1)
この式は、区分が重点で、かつA列の月がF1と一致する売上を合計します。
範囲は絶対参照にし、見出しセルだけは列または行を相対参照に残しておくと、フィルハンドルをドラッグしたときに正しく連続計算されます。
先頭セルに数式を入れたら、セル右下の小さな四角を右方向へドラッグしてオートフィルしましょう。
色を条件にしたいという要望の多くは、実際には色が示す分類ごとの集計を求めています。
【操作のポイント】数式をコピーする前に、条件範囲と合計範囲に付けるドル記号の位置を確認します。
SUMIFで色を直接判定できない理由
| セル | 値 | 背景色 | SUMIFの判定 |
|---|---|---|---|
| C2 | 1200 | 黄色 | 値だけを参照 |
| C3 | 800 | なし | 値だけを参照 |
続いては、SUMIF関数で背景色をそのまま条件に指定できない理由を確認していきます。
SUMIFは、セルに入力された文字列、数値、日付、比較記号をもとに判定する関数です。
セルの値と書式情報の違い
Excelのセルには、値や数式のほかに、塗りつぶし、文字色、罫線、表示形式といった書式情報があります。
SUMIFが通常参照するのは値の部分であり、黄色や赤色といった書式情報ではありません。
たとえばC2に1200が入力されていても、背景を黄色から緑色へ変更しただけでは、SUMIFの結果は変化しません。
見た目を変えただけでは再計算の条件にならない点が、色集計でつまずきやすい理由です。
色別の集計を必要とする表では、初めから入力データと表示ルールを分けて設計すると後工程が楽になります。
【操作のポイント】手入力の色は集計条件ではなく、注意喚起や状態表示として使うと役割が明確になります。
条件付き書式の表示色と実際の色
条件付き書式で黄色に見えているセルは、通常の塗りつぶしとは別のルールによって表示されている場合があります。
そのため、VBAや古いExcel関数で色番号を取得しても、期待した結果にならないことがあります。
特にカラースケール、データバー、アイコンセットなどは、値に応じて見た目が変わる機能です。
この場合も、表示色を読み取るのではなく、色の条件になっている元の数値や文字を集計条件に使うのが安全です。
条件付き書式のルール管理から数式を確認し、同じ条件をSUMIFまたはSUMIFSへ反映しましょう。
【操作のポイント】条件付き書式の色を集計したいときは、先にルールの数式や閾値を確認します。
色だけで管理する表の注意点
黄色は確認中、赤色は至急、緑色は完了というように、色だけで状態を伝える表は直感的です。
一方で、印刷時の白黒表示、色覚の違い、コピー先での配色変更によって意味が伝わりにくくなることがあります。
また、担当者が塗りつぶしを忘れると、合計対象の漏れに気付きにくくなるでしょう。
状態列に確認中や完了を入力し、必要に応じて入力規則のリストを設定する方法がおすすめです。
データの意味は文字や数値で保存し、色は補助表示にすることが、正確な集計につながります。
【操作のポイント】状態列には候補を統一した入力規則を設定し、表記ゆれを防ぎます。
フィルターとSUBTOTALによる色別集計
| 商品 | 売上 | 抽出対象 |
|---|---|---|
| ノート | 1200 | 黄色 |
| ペン | 800 | 対象外 |
| ファイル | 1500 | 黄色 |
続いては、すでに背景色だけが付いている表で、フィルターとSUBTOTAL関数を使って合計する方法を確認していきます。
この方法は、色の付いた行を一時的に確認したいときに役立ちます。
色フィルターで対象行を抽出する操作
表内の任意のセルを選び、データタブからフィルターを実行すると、1行目の見出しに下向き矢印が表示されます。
売上列または色を付けた列の矢印をクリックし、色でフィルターからセルの色でフィルターを選択します。
一覧に表示された黄色を選ぶと、黄色のセルを含む行だけが残ります。
フィルターは非表示にする機能であり、データを削除する操作ではありません。
抽出後に合計を確認したい場合は、通常のSUMではなくSUBTOTALを使うことが重要です。
【操作のポイント】フィルターは1行目が見出しの範囲で実行し、合計行を表の外側に置きます。
SUBTOTAL関数で表示行だけを合計する数式
黄色の行だけを表示した状態で、合計を表示するセルに次の数式を入力します。
=SUBTOTAL(109,B2:B100)
109は、手動で非表示にした行とフィルターで非表示になった行の両方を除外して合計する指定です。
B2:B100は売上が入っている範囲に合わせて変更します。
フィルターで黄色だけを表示している間は黄色の売上合計が表示され、フィルターを解除すると全体の合計へ戻ります。
SUBTOTALは表示されている行だけを計算できるため、色フィルターとの相性が良い関数です。
【操作のポイント】フィルター抽出後の合計には、SUMではなく109を指定したSUBTOTALを入力します。
フィルター集計を使う場面と限界
色フィルターは、担当者が手作業で色を付けた既存ファイルをすぐ確認したい場合に便利です。
一方で、毎月の報告書で自動集計する用途には、フィルターを毎回操作する手間がかかります。
また、同じ色が別の意味で使われていると、意図しない行まで合計されるかもしれません。
一時確認には色フィルターとSUBTOTAL、定期集計には区分列とSUMIFまたはSUMIFSという使い分けが適しています。
表を運用する段階で、どちらの集計が必要なのかを決めておくと作業が安定します。
【操作のポイント】色フィルターの結果は操作時点の表示に依存するため、提出用の集計には条件列の数式を優先します。
補助列とGET.CELL関数による色番号取得
| 売上 | 色番号 | 判定 |
|---|---|---|
| 1200 | 6 | 合計対象 |
| 800 | 0 | 対象外 |
続いては、補助列へ色番号を表示し、その番号を条件にして集計する方法を確認していきます。
GET.CELL関数は名前の定義から利用する旧来のマクロ関数であり、通常のワークシート関数一覧には表示されません。
名前の定義で色番号の式を登録する手順
数式タブの名前の管理から新規作成を選び、名前に色番号など任意の名称を入力します。
参照範囲には、次の式を入力します。
=GET.CELL(63,INDIRECT(“RC[-1]”,FALSE))
63はセルの塗りつぶし色に関する番号を取得する指定で、RC[-1]は数式を入れたセルの左隣を参照する指定です。
たとえばD列へ色番号を表示するなら、左隣のC列にある売上セルの色を読み取れます。
環境や配色によって番号は異なるため、黄色を付けたセルで実際の値を確認してください。
色番号はファイルのテーマや使用中の色によって変わる可能性があるため、別ブックへの流用時には注意が必要です。
【操作のポイント】名前の定義では、参照先を固定セルにせず、補助列の左隣を参照する相対形式で登録します。
補助列へ色番号を表示する数式
D2セルへ、作成した名前を使う数式を入力します。
=色番号
入力後にF9キーで再計算するか、数式を編集してEnterキーを押すと色番号が反映されます。
D2のフィルハンドルを下へドラッグすれば、D3以降も各行のC列の色番号を取得できます。
その後、黄色に該当する番号が6であれば、次のSUMIFで売上を合計できます。
=SUMIF(D2:D100,6,C2:C100)
補助列に数値として色番号を出せれば、SUMIFの条件として扱えるようになります。
【操作のポイント】色を変更した直後に番号が更新されない場合は、再計算を実行して表示を確認します。
GET.CELL関数を使う場合の注意点
GET.CELL関数は便利ですが、条件付き書式による見た目の色を正しく取得できない場合があります。
また、通常の関数よりも仕組みが分かりにくく、共同編集するファイルでは保守が難しくなることがあります。
色番号だけを根拠に重要な金額を確定する運用は避け、必要なら区分列を追加して二重に確認しましょう。
長期間使う業務表では、GET.CELLよりも区分列とSUMIFSの設計が安全です。
【操作のポイント】GET.CELLは既存の色付き表を救済する方法として使い、新規表では条件列による管理を優先します。
SUMIFSとテーブル機能による集計管理
| 日付 | 区分 | 担当 | 売上 |
|---|---|---|---|
| 4月 | 重点 | 田中 | 1200 |
| 4月 | 通常 | 佐藤 | 800 |
続いては、複数の条件を持つ売上表でSUMIFS関数とテーブル機能を使う方法を確認していきます。
色の意味を区分列へ置き換えると、担当者別、月別、商品別の集計にもそのまま応用できます。
複数条件を指定するSUMIFS関数
重点かつ4月の売上だけを合計する場合は、SUMIFS関数を使います。
=SUMIFS(D2:D100,B2:B100,”重点”,A2:A100,”4月”)
最初に合計範囲であるD列を指定し、その後に条件範囲と条件を一組ずつ入力します。
条件が増えても、条件範囲と条件を追加するだけです。
SUMIFSは色の代わりに登録した区分を使って、複雑な集計を自動化できます。
【操作のポイント】SUMIFSでは、すべての条件範囲の行数と列数を合計範囲にそろえます。
テーブル化による範囲拡張
データ範囲を選択してCtrlキーとTキーを押すと、Excelのテーブルとして設定できます。
テーブルでは新しい行を追加したときに数式の範囲が自動で広がるため、SUMIFやSUMIFSの集計漏れを減らせます。
テーブル名を売上表に変更した場合、数式は次のように読みやすく記述できます。
=SUMIFS(売上表[売上],売上表[区分],”重点”)
列名がそのまま数式に表示されるので、C2:C100のようなセル範囲より意味を確認しやすい点が利点です。
ただし、列名を変更すると数式の見え方も変わるため、見出し名は簡潔に統一しましょう。
【操作のポイント】定期的に行を追加する一覧表はテーブル化し、集計範囲を自動拡張させます。
入力規則による区分の統一
区分列に重点、通常、保留などを直接入力する場合は、データの入力規則でリストを作成すると便利です。
データタブのデータの入力規則からリストを選び、候補を登録します。
入力者はセルの矢印から候補を選べるため、重点と重点案件のような表記ゆれを防げます。
SUMIFの集計精度は、条件となる文字列が統一されているかどうかで決まります。
区分に応じた条件付き書式を設定すれば、入力規則、数式、色表示が一つのルールで連動します。
【操作のポイント】集計に使う区分は自由入力にせず、候補リストから選択する形にします。
集計結果が合わないときの確認項目
| 確認項目 | 起こりやすい原因 |
|---|---|
| 条件文字列 | 余分な空白や表記ゆれ |
| 合計範囲 | 行数の不一致 |
| 色番号 | テーマ色や再計算の影響 |
続いては、色付きセルに関連する集計結果が期待どおりにならない場合の確認項目を解説していきます。
数式がエラーにならなくても、条件の設定違いによって合計が0になることがあります。
文字列の空白と表記ゆれ
重点という条件で集計できない場合は、セル内に全角スペース、半角スペース、改行が含まれていないか確認します。
見た目が同じでも、重点の後ろに空白が入っているとSUMIFでは別の文字列として扱われます。
TRIM関数や置換機能で不要な空白を取り除き、入力規則で候補を統一しましょう。
合計が0の場合は、最初に条件文字列が完全一致しているかを見ると原因を絞り込めます。
【操作のポイント】条件セルを数式バーで確認し、末尾の空白や見えない改行を調べます。
条件範囲と合計範囲のずれ
SUMIFの条件範囲がB2:B100なのに、合計範囲がC3:C101になっていると、1行ずれたデータが合計されます。
数式をコピーした後に参照範囲が動いているケースもあるため、数式バーで先頭と末尾のセル番地を確認してください。
SUMIFSでも、合計範囲と各条件範囲は同じ大きさである必要があります。
集計値が少しだけ違うときは、範囲の開始行と終了行を見直すことが有効です。
【操作のポイント】数式作成時には、条件範囲と合計範囲を同じ最終行まで選択します。
色変更後に色番号が更新されない場合
GET.CELL関数で色番号を使っている場合、塗りつぶしを変更しても補助列の番号がすぐ変わらないことがあります。
これは色変更が通常の再計算条件として扱われないためです。
F9キーで再計算する、該当セルを編集してEnterキーを押す、数式を再入力する方法で更新を試します。
頻繁に色を変える運用では、更新忘れが集計ミスにつながります。
色番号の取得は便利ですが、再計算のタイミングまで管理する必要があります。
【操作のポイント】色を変更した表を提出する前には、補助列と合計セルの再計算結果を確認します。
まとめ エクセルで色付きセルの合計をSUMIFで求める方法
エクセルで色付きセルの合計をSUMIFで求める場合、SUMIF関数が背景色を直接判定できない点を理解することが大切です。
最もおすすめなのは、色が示す意味を区分列に入力し、その区分をSUMIFまたはSUMIFSで集計する方法です。
たとえば重点の売上を集計するなら、=SUMIF(B2:B100,”重点”,C2:C100)のように、区分列を条件範囲、売上列を合計範囲に指定します。
既存の色付き表を一時的に確認するだけなら、色フィルターで対象行を表示し、=SUBTOTAL(109,範囲)で可視セルだけを合計できます。
色番号を補助列へ取得して集計する方法もありますが、条件付き書式や再計算の影響を受けるため、恒常的な業務集計では注意が必要です。
入力規則、条件付き書式、テーブル機能を組み合わせ、データの意味を文字や数値で保存する表へ整えていきましょう。
