【Excel】条件付き書式で行を塗りつぶしたい

Excel（エクセル）の条件付き書式には様々な条件を簡単に作成できる組み込みのメニューがたくさんありますので普段から活用されている方も多いと思いますが、「セルの強調表示」にある「指定の値より大きい」～「重複する値」の各ボタンから設定できるのは、条件判定する値が入力された「セル」を強調表示する時に限られます。

今回のお題は、そこからもう１段階ステップアップした「セルだけじゃなく、そのセルが含まれる行に書式を適用したい」という時の設定のコツです。チャレンジしていても何だかうまくいかない･･･という方は参考にしてみてください。ポイントは、条件式の絶対参照の置き所です。

※解説画像はMicrosoft365のExcelを使用しています

条件付き書式を適用する範囲を選択する
条件式を作成する

条件付き書式を適用する範囲を選択する

それでは実際にやってみましょう。こちらの表を使って、点数が70に満たない行を赤く塗りつぶすという条件付き書式を設定します。

まず、表の見出し部分を除き、条件付き書式を適用したい範囲（条件判定する値が入力された列も含め、塗りつぶししたい範囲すべて）をドラッグして選択します。

この表のように順当に範囲の先頭（B3）から終端（C7）に向かってドラッグした場合は、カーソルは先頭位置のB3に置かれます。このカーソルの位置は後ほど数式を作る時に関わってくる部分なので、もし逆方向からドラッグした時はTabキーを押して先頭位置にカーソルを移動しておいた方がわかりやすくなると思います。

条件式を作成する

範囲が選択できたら、続けて[ホーム]タブ⇒「条件付き書式」⇒「新しいルール」（もしくはセルの強調表示ルールなどのサブメニューにある「その他のルール」）をクリックします。

「新しい書式ルール」ダイアログボックスが開いたら、ルールの種類から「数式を使用して、書式設定するセルを決定」をクリックします。

「次の数式を満たす場合に値を書式設定」の下にある数式入力ボックスをクリックしてカーソルを置きます。ここに「C列の点数が70より小さかったら」という条件式を入力していきます。

ここに入れる式は、現在カーソルが置かれているセルに入力されるものであり、他のセルはそこから数式がコピーされる、というイメージで作るのがコツです。コツというより、実際にそのような動きになります。先ほど範囲選択の際に、範囲の先頭にカーソルがあった方がわかりやすいよと申したのはこのためです。

という事で、この例では現在B3にカーソルがありますので、同じ行にある「C3の値が70より小さければ」という条件を作成しましょう。

C3をクリックすると、数式入力ボックスに「=$C$3」のように列記号・行番号の頭に「$」の絶対参照記号がついた状態で入力されます。ここで、この式がB3以外の他の範囲にコピーされた場合、どの位置で固定すればすべてのセルに正しく書式が適用されるか考えてみましょう。

絶対参照の置き方は「数式コピー」した時のイメージで考えよう

B3に「=$C$3」と列記号・行番号とも絶対参照で固定した式を入れると、B3はC3を参照し、C3は自セルの値を参照するので問題なさそうに見えます。しかしこれでは、その他のセルもすべてC3を参照する事になってしまいますので、本来塗りつぶされるはずの行に書式が適用されなくなってしまいます。

では、「=C$3」と行番号だけ絶対参照で固定する式にするとどうでしょう。列が相対参照になっているため、B3はC3を参照しますが、C3は自セルではなく右隣りのD3を参照してしまいます。かつ、行が３行目で固定されてしまっているので、B4～B7もC3を参照し、C4～C7はD3を参照するというますますおかしな事になってしまいます。

ではB3に「=C2」というように列も行も相対参照にして式を入れるとどうなるか。B列は同じ行のC列の値を参照しますので70より小さい値が入力されている行は塗りつぶしの書式が適用されますが、C列は自セルではなく隣のD列の値を参照しますので、結果、C3～C7は「参照先の値が空白（70より小さい）」と判定されすべてのセルに塗りつぶしが適用されてしまいます。