スプレッドシートの条件付き書式「カスタム数式」使い方まとめ！セルや数式の記入方法を解説
スプレッドシートではセルの値や条件に応じて文字の色や塗りつぶし色を変更できる条件付き書式という機能があります。
その中で色々な条件が設定できるものの、使い方が難しいカスタム数式について、例を交えながら解説します。
スプレッドシートの条件付き書式とは？
スプレッドシートの条件付き書式とは、条件に応じてセルの見た目や値の表示、いわゆる書式を切り替えることができる機能です。
例えば、以下のような条件付き書式がよく設定されます。
- 特定列の値がしきい値以上の値が入ると、該当行の背景が色掛けされる
- 曜日のカレンダーのシートで土曜と日曜のみセルの背景色を変える
- 締切日の列が3日前になると、日付の文字が赤色になる
こうしたセルの背景の色掛けや日付の文字色を手動で変更するのは大きな手間です。
条件付き書式を活用することで、注意すべきデータを強調したり、表データを見やすくできます。
自由度が高い条件付き書式のカスタム数式
条件付き書式には、あらかじめスプレッドシート側で設定可能な条件が設けられています。
- 文字列に関する条件(一致する・含む・含まないなど)
- 日付に関する条件(設定日より前の日付、後の日付)
- 数値に関する条件(大きい、小さい、等しいなど)
それ以外に自由度の高い設定ができる条件付き書式の機能として、「カスタム数式」が用意されています。
カスタム数式では、スプレッドシートに用意されている数式や関数を利用し、書式の条件を設定することができます。
カスタム数式を使いこなせばレベルアップに
非常に自由度が高いカスタム数式は、多岐にわたる条件を設定可能な反面、その自由度の高さから使い方が複雑で難しいため、使いこなせない人も多いです。
ただし、カスタム数式を使いこなせば、これまでよりもスプレッドシートの見た目を各段にパワーアップさせることができます。
さらに使えるようになることで、より分かりやすい表データの資料作成に繋がります。
そこで、今回スプレッドシートの条件付き書式で使用するカスタム数式を、事例を交えながら分かりやすく紹介していきます。
スプレッドシートのカスタム数式の設定方法
まず、スプレッドシートのカスタム数式を設定する方法について紹介します。
１．スプレッドシートでカスタム数式を設定したいセル範囲を選択します。
２．メニューバーの「表示形式」をクリックし、「条件付き書式」を選択します。
３．スプレッドシートの右側に「条件付き書式設定ルール」が表示されるので、選択した範囲が指定されているか確認します。
４．続いて書式ルールの「セルの書式設定の条件」のプルダウンの1番下にあるからカスタム数式を選択します。
５．「値または数式」というところに条件式を設定し、書式設定のスタイルを入力した上で「完了」ボタンをクリックします。
以上の5Stepでカスタム数式自体の設定は完了です。
ここからカスタム数式で数式を入力する欄で活用するセル変数の指定方法や関数・数式の記入方法を紹介します。
カスタム数式でのセルの指定方法
カスタム数式ではセルを数式の中で指定することができます。
ただし、そのまま「A1」や「C5」といったセルの位置を入力しても認識してくれません。
セルの列記号の前に$(ダラー)をつける
セルを数式内に指定する際は、「＄(ダラー)」マークを付けて、表現します。
そのため、「$A1」や「$C5」という形でセルを指定します。
$マークを忘れてしまったままだと、入力欄で認識されないままになります。
セルの行番号は何番を指定すればよい？
セル指定では(列記号)(行番号)という形で表現しますが、この行番号はいくつにすればいいのか？といった質問をよく受けます。
行番号ですが、基本的には「指定した範囲の1番目の行番号」を入力する形になります。
先程カスタム数式の手順を紹介したケースでは条件付き書式の対象範囲は「A2:F17」を指定しています。
その中で、金額を表すE列が1000円以下の列に色をつけるというカスタム数式の条件付き書式では、「$E2」と指定しています。
では対象範囲の1行目ではない行番号を指定するとどうなるのでしょうか？
例えば、$E1をカスタム数式に指定すると同じ条件で条件付き書式が適用される範囲が変わります。
この場合、その行の条件式は1行上の数値で判定されることになります。
逆に1行下の$E3をカスタム数式に指定しても、条件付き書式の適用範囲が変化します。
今度は、1行下の数値が条件判定で使用され、行の背景色の色掛けが行われるようになっています。
もちろんこうしたカスタム数式の条件指定もありますが、基本的には同一行の特定列で判定するのが一般的です。
「カスタム数式に指定するセルの行番号は対象範囲の1行目」とおぼえておきましょう。
カスタム数式で利用可能な関数・数式の条件
カスタム数式ではどんな数式や関数も利用できるわけではありません。
カスタム数式に設定できる数式・関数には条件が設けられています。
カスタム数式で利用可能な条件は「計算結果がTrueまたはFalseになるもの」です。
そのため、AverageやSumといった結果で数値を出力するものを設定してもエラーとなります。
※ただし、最終的な計算結果をTrue/Falseにすれば途中でSumなどの計算結果が数値のものも利用可能です。
「=」や「＜」、「＞＝」といった等号・不等号の数式が条件を満たします。
カスタム数式で入力した数式でうまく設定できない場合は、カスタム数式の利用条件を満たしているか確認しましょう。
カスタム数式は複数の条件を組み合わせることが可能
カスタム数式では、１つ列を条件判定するだけでなく、複数列を条件判定に利用することができます。
その場合には、スプレッドシートの関数として用意されているAND関数、またはOR関数を利用します。
複数条件をすべて満たす時に条件付き書式を適用する場合は、AND関数を使います。
=AND($C2="田中",$E2>1500)
複数条件のうちいずれか１つでも満たす時に条件付き書式を適用する場合はOR関数を使います。
=OR($C2="田中",$C2="伊藤")
このANDとORを組み合わせることでさらに細かな条件での書式の設定が可能になります。
カスタム数式を使った条件付き書式の実例
カスタム数式を使った条件付き書式の実例を紹介します。
日付が今日の行の色を変える
日付を入力している列が今日の行のセル背景色を変える場合は、TODAY()関数を利用します。
=$B2=TODAY()
※B列に日付が入っている場合を想定
上記のような書き方で、今日の日付の行にのみ書式を適用可能です。
日付の曜日が土日の場合にセルの色を変える
カスタム数式を使えば、特定列に設定された日付が土曜または日曜か判定し、セルの背景色を変更することも可能です。
上記の記事にて、カスタム数式でスプレッドシートの曜日で土日だけ背景色を塗る方法を紹介しています。
スプレッドシートでスケジュール表やシフト表を作成している場合に便利です。
まとめ・終わりに
今回、スプレッドシートの条件付き書式で利用可能なカスタム数式を紹介しました。
カスタム数式はセルの指定方法や関数・数式の使い方が少し難易度が高く、あまり使いこなせていないケースが多いです。
しかし、うまく活用できるとスケジュール表やシフト表、さらには売上管理表などが劇的に見やすくなります。
スプレッドシートで扱うデータ量が増えるほど、データの視認性を高める効果があります。
今回セルの指定方法や複数条件の指定方法や使える数式の条件を解説しましたので、ぜひカスタム数式を活用してみてください。