【Excel】条件付き書式で行ごとに色を付ける — 行全体を一括で塗りつぶす方法と$の使い方

Excel
2021.08.15
この記事は約10分で読めます。

「状況」の列が「済」になっている行だけ、行全体をグレーにしたい。進捗表や管理表を作っていると、必ずと言っていいほど出てくる要望です。

条件付き書式そのものは使えるのに、いざやってみると「選んだセルだけ色が変わる」「なぜか1列だけ色が付く」「全部の行が同じ色になる」といった結果になって止まってしまう方が多い機能でもあります。

原因はほぼ1か所、数式に付ける $ の位置です。ここを押さえてしまえば、行ごとの塗り分けも列ごとの塗り分けも同じ考え方で作れるようになります。

この記事で分かること

  • 1つのセルの値を見て、行全体に色を付ける方法
  • $C$3 と $C3 と C$3 で結果がどう変わるのか
  • 思ったところに色が付かないときに確認するポイント

どんな場面で使うか

1つの列の状態で行全体の見え方を変えたい、という場面はどれも同じ作り方でカバーできます。

  • タスク管理表で、状況が「済」の行をグレーにして目立たなくする
  • 案件一覧で、期限を過ぎた行を赤くする
  • 在庫表で、在庫数が一定を下回った行を黄色にする
  • 名簿で、退職・解約などのフラグが立っている行を薄い色にする

いずれも「判定に使う列は1つ、色を変えたい範囲は行全体」という構造です。

基本説明：なぜ「行ごと」に色が付かないのか

まずは操作する表を確認します。ここでは B列に「No.」、C列に「状況」、D列に「アイテム」が入っていて、データが3行目から始まる表を例にします。条件付き書式を設定する前の同じ表を、サンプルファイル（記事末尾）からダウンロードできます。

条件付き書式を設定する元の表

やりたいのは「C列が『済』になっている行は、その行全体をグレーで塗りつぶす」ことです。

条件付き書式のルールに数式を書くとき、Excelは次のように動きます。

  1. 選択範囲の中で白く残っているセル（アクティブセル）を基準にして数式を読む。左上からドラッグして選べば、左上のセルが基準になる
  2. その数式を、選択範囲のすべてのセルにコピーしたつもりで1つずつ判定する
  3. 結果が TRUE になったセルにだけ書式を適用する

つまり数式は「基準のセル用に書いた1本」を、Excelが右へ下へコピーしながら使い回しています。この「コピー」の挙動を決めているのが $ です。

$ の位置で結果がどう変わるか

B3 から D8 へドラッグして選び、基準のセルが B3 のときの違いです。

数式の書き方 何が起きるか 用途
=$C$3="済" 列も行も固定される。C3の値だけを見て、範囲全体が一斉に塗られるか、まったく塗られないかのどちらかになる 今回は使わない
=$C3="済" 列だけ固定。どの列のセルを判定するときも必ずC列を見るが、行は各セルの行に合わせて動く 行ごとに塗り分けたいとき（今回）
=C$3="済" 行だけ固定。どの行のセルも3行目を見るが、見る列は1つ右にずれる。C3が「済」だとB列だけが全行塗られる 今回は使わない
=C3="済" 何も固定されない。判定するセルと同じ位置関係のセルを見に行くため、意図しない列を参照する 使わない

行ごとに色を変えたいということは、「見る列は固定したい・見る行は動かしたい」ということです。だから列を表す C の側にだけ $ を付けて =$C3 にします。

$ は「動かさない」という意味の記号だと覚えておくと、迷ったときに判断できます。

手順

Step 1: 色を付けたい範囲を選ぶ

行全体を塗りたいので、行全体ぶんの列を含めて選択します。ここでは B3 から D8 へドラッグして選択します。B3 がアクティブセルになり、数式の基準になります。

このとき、タイトル行（見出し行）は選択に含めないでください。 含めるとタイトル行も判定対象になり、意図しない色が付きます。

Step 2: 「新しいルール」を開く

範囲を選択したまま、「ホーム」タブ →「条件付き書式」→「新しいルール」を選びます。

条件付き書式メニューから新しいルールを選ぶ

新しいルールの選択画面

Step 3: 「数式を使用して、書式設定するセルを決定」を選ぶ

ルールの種類から「数式を使用して、書式設定するセルを決定」を選びます。

入力欄にカーソルを置いた状態で、判定に使いたい列の最初のセル（今回はC列の最初のデータである C3）をクリックします。

ルールの種類で数式を選び、C3をクリックした状態

クリックすると =$C$3 のように入力されます。この時点ではまだ行ごとに動きません。

Step 4: $ の位置を直す

=$C$3 の C の直後にカーソルを置き、F4 キーを数回押します。押すたびに $C$3 → C$3 → $C3 → C3 と切り替わるので、$C3 の状態で止めます。

$C3に修正した状態

キーボードで直接 $ を消しても構いません。大事なのは列（C）にだけ $ が付いている状態にすることです。

Step 5: 条件を書く

=$C3 の後ろに、判定したい条件を続けます。

=$C3="済"

文字列と比べるときは " で囲みます。「済」以外の条件にしたいときは、下の「条件を変えたいとき」の数式に入れ替えます。

条件の数式を入力した状態

Step 6: 書式を決める

右下の「書式」ボタンを押して、条件に合ったときの見た目を決めます。今回は「塗りつぶし」タブからグレーを選びました。

書式設定でグレーの塗りつぶしを選ぶ

行を目立たなくしたい場合は、塗りつぶしを薄いグレーにして文字色もグレーにすると、他の行との差がはっきりします。

Step 7: 完成

「OK」で閉じると、条件に合った行だけが行全体で塗られます。

条件付き書式が行全体に適用された完成イメージ

C列の値を「済」に変えると、その場で行の色が変わります。条件が1つであれば、ここまでで完成です。

条件を変えたいとき（複数条件・「〜を含む」・空欄）

Step 5 の数式を入れ替えるだけで、条件を変えられます。範囲は同じ B3:D8 です。

入れ替えるときは、先に B3 から D8 をドラッグして選び直します。そのうえで「条件付き書式」→「ルールの管理」を開き、Step 1〜7 で作ったルールを選んで「ルールの編集」で数式を書き換えます。

別のセルを選んだまま開くと、数式の基準がずれます。「新しいルール」で足すと前のルールが残り、「済」の行にも色が付いたままになります。

やりたいこと 数式 この表で色が付く行
「済」以外の行 =$C3<>"済" No.2・3・5
「済」かつ No.が4以上の行 =AND($C3="済",$B3>=4) No.4・6
「済」または「保留」の行 =OR($C3="済",$C3="保留") No.1・4・6
「済」を含む行（「済み」「対応済」も） =ISNUMBER(SEARCH("済",$C3)) No.1・4・6
アイテムが空欄の行（入力漏れ） =$D3="" 全行

どの数式も、判定する列の記号（B・C・D）の前にだけ $ を付けます。数値は >=、文字列は " で囲んで比べます。

「保留」はこの表に無いので、C列のどれかを「保留」に書き換えると色が付くのを確かめられます。「〜を含む」は「未済」のような語にも一致するので、状況の言葉が揃っていない表では注意してください。

期限で判定したいときは、E列に日付を足し、「ルールの管理」の「適用先」を =$B$3:$E$8 に広げて =AND($E3<>"",$E3<TODAY()) と書きます。=$E3<TODAY() だけだと、日付が空欄の行にも色が付きます。

列全体に色を付けたい場合

考え方は同じで、固定する側を入れ替え、基準のセル B3 から見た位置で数式を書きます。

たとえば見出し（2行目）が「状況」の列だけ色を変えたいなら、B3 から D8 を選んで次の数式にします。

=B$2="状況"

行を表す 2 の側に $ を付けると、「見る行は2行目に固定、見る列は各セルに合わせて動く」という意味になります。基準のセルが B3 なので、数式も B列から書きます。条件に合うのはC列のセルだけなので、範囲内のC列に色が付きます。

やりたいこと 数式の形 固定する側
行ごとに塗り分ける =$C3="済" 列（C）を固定
列ごとに塗り分ける =B$2="状況" 行（2）を固定

よくあるエラー

色が付かない・付き方がおかしいときは、次の点を上から順に確認すると原因が見つかります。

選んだセルだけ色が付く／範囲全体が一斉に塗られる

$ が両方に付いて =$C$3 のままになっている可能性が高いです。この形は「C3という1つのセルだけを見る」という意味なので、C3が条件を満たせば範囲全部が塗られ、満たさなければ1つも塗られません。

「条件付き書式」→「ルールの管理」からルールを選んで「ルールの編集」を開き、=$C3 になっているか確認してください。

タイトル行まで色が付く

Step 1で範囲を選ぶときに、見出し行を含めてしまっています。ルールの管理画面で「適用先」を確認し、データが始まる行（今回は3行目）からの範囲に直します。

意図しない列を見に行っている

$ を付けずに =C3 としていると、判定するセルの位置に合わせて参照列まで動きます。 基準のセルがB3のとき、C列のセルはD列を、D列のセルはE列を見に行く、という具合です。正しくC列を見るのはB列のセルだけなので、No.の列にだけ色が付きます。

後から追加した行に色が付かない

条件付き書式のルールは、設定したときの範囲（適用先）にだけ効きます。表の下に行を足しても、その行は範囲外のままです。

毎月行が増えていく表なら、あらかじめ表をテーブル（Ctrl + T）に変換しておくと、行を追加したときに書式も自動で広がります。すでにルールを作った後なら、ルールの管理画面で「適用先」の範囲を広げても構いません。

「済」と入力しているのに反応しない

セルの値に余分な空白が入っていないか確認してください。他システムからコピーした値には、末尾に半角スペースが残っていることがあります。この場合は =TRIM($C3)="済" のように書くか、元データ側の空白を取り除きます。

全角と半角の違い、似た文字（「済」と「済み」）の混在も同じ理由でよくある原因です。

まとめ

  • 条件付き書式の数式は、アクティブセルを基準に、全セルへコピーされるものとして解釈される。左上からドラッグして選べば基準は左上
  • 行ごとに塗り分けたいときは列だけを固定して =$C3="済" と書く
  • 列ごとに塗り分けたいときは行だけを固定して =B$2="状況" のように書く
  • $ は「動かさない」という意味。どちらを動かしたいかで付ける場所が決まる
  • 条件を変えるときも $ の付け方は同じ。AND・OR・SEARCH を組み合わせれば複数条件や「〜を含む」にできる
  • 色が付かないときは「$の位置」「適用先の範囲」「値の余分な空白」の順に確認する

ドロップダウンで選んだ値の行に色を付けたいときは、"済" の代わりにそのセルを固定して =$C3=$G$1 のように書きます。抽出結果と連動させる発展形は、下の関連記事で扱っています。

関連記事

サンプルファイル

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

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