一言まとめ

例えば、セルの値が「完了」だったら、その行全体の色をグレーにしたい時がある。

[条件付き書式]→[カスタム数式]で、セルの複合参照を利用することで、行全体に書式を適用することができる。

例えば、こんな感じのタスク管理用のシートがあるとしましょう。

「状態」を「完了」にすると自動的にその行がグレーになってくれたら便利ですよね。

「ある列のセルの値に合わせて、その行全体の書式を変えたい」ってよくあることだと思います。
「条件付き書式」を使えば割りと簡単にできますよという話です。もちろんExcelでも同じことができます。

「条件付き書式」で行全体に書式を適用する方法

「条件付き書式」はセルごとに適用する機能ですが、セルの複合参照を利用することで、擬似的に行全体に書式を適用することもできます。

やり方は簡単です。テーブルヘッダ以外のセルを選んだ状態で [表示形式]→[条件付き書式] を実行すると下記の画面が出てきます。「セルの書式設定の条件」を「カスタム数式」にして、ここでは条件を=$D2="完了"に設定しています。

解説

どうして条件を=$D2="完了"にすると、D列のセルの値が「完了」になると行全体に設定した書式が適用されるのかを疑問に思った方向けに説明します。

セルの参照方法

セルには3つの参照方法があります。「絶対参照」と「相対参照」と「複合参照」です。

表記上の違い

• 絶対参照：$D$2
• 相対参照：D2
• 複合参照：$D2 or D$2

違いは見て取れるように、$(ドル記号)が 行/列 の先頭に付いているかどうかですよね。行も列も付いていれば絶対参照、付いていなければ相対参照、どちらかに付いていれば複合参照です。

機能上の違い

セルをコピーした時に現れます。逆にいうとコピーしなければ違いはありません。

$(ドル記号)が付くと、セルをコピーしても、参照している 行/列 が変わることはない。付いていると、コピー元のセルの位置が基準となって、コピー先のセルとの相対位置で参照している 行/列 が算出されます。

下記の例を見て下さい。分かりやすいように、[表示]→[すべての数式]で、セルの数式そのものを表示させたものです。そして、各表の左上の赤で表示しているセルがコピー元です。

絶対参照は一番分かりやすくて、コピーしても何も変わらないから特筆すべきところはないです。

相対参照の表をちょっと説明します。一番最後のセルの数式が=F5となったのは、コピー先(C5)は、コピー元(A2)から見て、2つ右(E → F)、3つ下(3 → 4 → 5)だからです。このように、コピー先の数式の行と列は、コピー先とコピー元の位置関係に合わせて変化します。

そして、複合参照は$(ドル記号)が付いてる 行/列 はコピー先でも変わらず、$が付いてない 行/列 は相対参照と同じ原理で変化します。

$を手入力してもいいのですが、実はF4キーを押すと、各参照方法の間で切り替えることもできます。

[条件付き書式]→[カスタム数式]で複合参照を使った時の挙動

「条件付き書式」は適用範囲内の一個一個のセルに対して、条件式でテストして、戻り値がtrueであれば書式を適用する、falseであれば適用しないことになっています。そして、条件式に相対参照や複合参照が使われる場合、セルをコピーした時と同じように、条件式自体も変化します。

今回の例で言うと、条件式(=$D2="完了")が適用範囲内のすべてのセルにコピーしたら下記のようになります。なお、コピー元は適用範囲(A2:D6)の開始セルです。つまりA2です。

ここまでくれば、なぜ行全体に書式が適用されるのかが分かりますよね？ご覧の通り、行ごとの条件式が全く同じですから。

応用編

理屈が分かったらいろんな応用も考えられます。

部分一致

要件例：”完了”、”対応”、”終了”のいずれかが見つかれば書式を適用。
数式例：=REGEXMATCH($D2,"完了|対応|終了")

文の中の一部だけマッチすれば書式を適用したい場合もあるでしょう。そういう時は正規表現を使ったマッチング関数が便利です。

期限が切れたタスクの書式

要件例：日付が今日より前であれば書式を適用。
数式例：=$C2 - TODAY() <= 0

この例では、期限が過ぎたタスクの行を薄い赤で、期限より5日以上も過ぎた場合は鮮やかな赤で表示させています。

それから、ルールの順番にも注意して下さい！最初にマッチしたルールより下のルールは無視されますので、優先させたいルールは上のほうに置くようにします。

Add Reminders などのアドオンを使えば期限が過ぎた時にメールで通知させることも簡単にできます。

まとめ

昔はExcelを使って関数やらVBAやらを書いたりして業務効率化するのが、今ではそれ専用のサービスを使ってさらに効率よく行うことが普通になってきました。

とは言え、Excelの使い方の自由さ、信頼性と普及率の高さから、今でも使っている人たちが多いですよね。ちょっとした関数を使うだけで一瞬でインタラクティブ性を持たせることができるのはやはり便利です！さらに Google Apps Script と組み合わせれば、ちょっとした業務効率化のツールなら手軽に作れていいよね〜