【Excel 関数】条件付き書式をFILTER＋スピル関数と組み合わせる応用技

この記事は約7分で読めます。

この記事で分かること

  • FILTER・UNIQUE で生成したスピル範囲を条件付き書式の参照に使う方法
  • COUNTIF とスピル演算子（#）を組み合わせて元データの行を自動ハイライトする方法
  • ドロップダウンと連動させて「選んだ条件の行が自動強調表示される」仕組みの作り方

「フィルターをかけたら色も変えたい」「条件が変わるたびに書式を手動で塗り直している」という方に向けて書いています。

どんな場面で使うか

  • ドロップダウンで「担当者」や「エリア」を選んだとき、該当行だけを自動でハイライトしたい
  • FILTER関数で抽出した結果と元データを並べて、対応行を色でつなぎたい
  • 条件付き書式のルールを変更せずに、参照先のリストを更新するだけで書式を切り替えたい
  • 「特定の値が動的リストに含まれているか」という判定を書式に反映させたい

基本説明

この記事では、次のような「売上データ」シートを例に使います（約90行あるうちの先頭部分です）。

A: 日付 B: 担当者 C: エリア D: 商品名 E: カテゴリ F: 数量 G: 売上金額
2025/01/06 田中 東京 商品A 電子機器 10 50000
2025/01/07 佐藤 大阪 商品B 文具 5 15000
2025/01/08 鈴木 名古屋 商品C 食品 8 24000
…

通常の条件付き書式との違い

通常の条件付き書式では「条件の値を直接書く」かたちになります。

例: =$C2="東京" → エリアが「東京」の行をハイライト。

問題は条件を変えるたびにルールを編集しなければならないことです。

FILTER関数と組み合わせると、ドロップダウンの値を変えるだけで条件付き書式が自動で追従するようになります。

手順

使用する関数の組み合わせ

I1セル: エリアを選ぶドロップダウン（入力値の種類:リスト）

J2セル: 選んだエリアの担当者一覧をFILTERで生成（スピル）

=SORT(UNIQUE(FILTER(B2:B100, C2:C100=I1, "")))
  1. FILTER(B2:B100, C2:C100=I1, "") → I1で選んだエリアの担当者を抽出
  2. UNIQUE(...) → 同じ担当者が複数行ある場合に重複を除く（例: 東京の田中が2行あっても1件にまとめる）
  3. SORT(...) → 昇順で並べる

条件付き書式の数式:

=COUNTIF($J$2#, $B2)>0

$J$2# はJ2からスピルした範囲全体を指します。この数式は「B列の値がJ2からのスピルリストに含まれているか」を判定します。

たとえば I1 で「東京」を選ぶと、J2 のスピルには東京エリアの担当者が展開されます。

J列（J2から下に展開）
伊藤
田中

条件付き書式の設定手順

  1. 元データ（A2:G100 など）を選択
  2. 「ホーム」タブ → 「条件付き書式」→「新しいルール」
  3. 「数式を使用して、書式設定するセルを決定する」を選択
  4. 数式欄に =COUNTIF($J$2#, $B2)>0 を入力
  5. 書式ボタンで塗りつぶし色を設定 → OK

I1のドロップダウンでエリアを変えるたびに、J2のスピルリストが更新され、ハイライトされる行が自動で切り替わります。

スピル範囲が空のときの注意

FILTER の第3引数を "" にしていると、条件に一致する行が0件のときスピル範囲が空になります。COUNTIF の参照先が空のとき、ハイライトされる行もゼロになるため動作としては問題ありませんが、"該当なし" など文字列を指定した場合は「該当なし」という文字列に一致する行がないことを確認してください。

サンプル例

ドロップダウンで選んだエリアの行を自動ハイライト

設定の全体像:

セル 内容
I1 エリアを選ぶドロップダウン（手動または別セルのスピル参照）
J2 =SORT(UNIQUE(FILTER(B2:B100, C2:C100=I1, "")))
条件付き書式（A2:G100） =COUNTIF($J$2#, $B2)>0

I1で「東京」を選ぶと → J2に東京担当者の一覧がスピル → 元データの担当者列がJ2リストと一致する行にだけ背景色が付く。

動作イメージ（I1 =「東京」のとき）:

日付 担当者 エリア 売上金額 行の状態
2025/01/06 田中 東京 50000 ✔ 背景色が付く
2025/01/07 佐藤 大阪 15000 そのまま
2025/01/08 鈴木 名古屋 24000 そのまま
2025/01/09 田中 東京 60000 ✔ 背景色が付く

I1 を「大阪」に変えれば、佐藤の行だけがハイライトに切り替わります。

複数条件リストによるハイライト

UNIQUE と FILTER を組み合わせると、「元データの担当者一覧から特定の担当者だけを除いたリスト」を作れます。たとえば「鈴木」を除いた担当者一覧を L2 に作る場合は次のようにします。

=FILTER(UNIQUE(B2:B100), UNIQUE(B2:B100)<>"鈴木")

このリストを COUNTIF の参照先にし条件付き書式に設定すれば「特定の担当者以外」の行をハイライトできます。

=COUNTIF($L$2#, $B2)<>0

<>0 にすると「リストに含まれている行」をハイライトします。除外する担当者を変えたいときは、L2の数式内の "鈴木" を書き換えるだけで済みます（L2はスピルする数式なので、$L$2# でスピル範囲全体を参照できます）。

売上金額が平均を上回る行をハイライト

スピル関数を使わない組み合わせですが、同じ考え方の応用として紹介します。

=$G2>AVERAGE($G$2:$G$100)

これをそのまま条件付き書式の数式にすれば、売上が平均以上の行だけ強調できます。FILTER で抽出した結果の平均にしたい場合は AVERAGE(FILTER(...)) にすることで条件を絞れます。

よくあるエラー

ハイライトが一切付かない

条件付き書式の数式で $J$2# と書けているか確認してください。# を忘れて $J$2 だけにしてしまうと、スピル範囲ではなくJ2セル1つだけしか参照されません。

ドロップダウンを変えても変わらない

J2の数式が I1 を参照しているか確認してください。また、I1に入力されている値とC列の値が完全に一致しているか（全角・半角・スペースの混入）も確認してください。

#SPILL! がJ2に出る

J3以降のセルに値が入っているとスピルがブロックされます。J3以降を空にしてください。

条件付き書式の「数式が使えない」バージョン

COUNTIF と # を組み合わせた条件付き書式は Microsoft 365 で動作確認しています。Excel 2019 以前ではスピル演算子（#）が使えないため、この手法は利用できません。

まとめ

  • =COUNTIF($J$2#, $B2)>0 という数式を条件付き書式に使うと、スピル範囲に基づいてハイライトできる
  • ドロップダウン → FILTER スピル → COUNTIF 判定 の流れを組み合わせると、条件切り替えが完全自動になる
  • =0 に変えれば「リストに含まれない行」のハイライトにも使える

手動でセルを塗り直す作業がなくなり、「フィルター後にいちいち色を変えている」という手間を解消できます。

対応バージョン: Microsoft 365（スピル演算子 # の使用に必要）

関連記事

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

タイトルとURLをコピーしました