挫折する人多数！スプレッドシートの『カスタム数式』の使い方を徹底紹介

こんにちは、いろんな効率化が大好きなたぬ（@tanuhack）です！

スプレッドシートの条件付き書式を使えば、自分が設定したルールに従って、文字色や背景色を自動で変えてくれるようになるので便利ですよね。

スプレッドシートの条件付き書式で、特定の値が入力されるとセルや行に色付けする方法

既存のルールを使うのではなく、『カスタム数式』を使えば、より柔軟な『誰もがパッと見て、分かりやすいスプレッドシート』を作れるようになります。

しかしこのカスタム数式は、書き方にクセがあるのがゆえに中々使いこなせないと嘆いている人が多数存在するのも事実。

そこで今回は、スプレッドシートのカスタム数式の使い方を余すことなく紹介し、あなたをカスタム数式の即戦力に仕立て上げたいと思います。

1 カスタム数式の復習
- 1.1 カスタム数式の中で使える数式とは
- 1.2 カスタム数式の書き方
2 複数条件を設定する方法
3 日付を設定する方法
4 空白を設定する方法
5 テキストに値が含まれるかを設定する方法
6 最後に

カスタム数式の復習

まずは使い方を復習します。

カスタム数式の中で使える数式とは

まず、大前提があります。

条件付き書式のカスタム数式で使える数式は、計算結果が『True』または『False』になるものだけです。

TrueやFalseを返す数式と言えば、『A=B』などの条件式を使ったり、AND関数やOR関数を一番外側で使ったり、が挙げられます。

極論から言えば、数式の計算結果がTrueかFalseになれば、カスタム数式として使えるという訳ですね！

カスタム数式の書き方

例として、以下の表で売上が10,000円未満の行をすべて塗りつぶしたいとします。

STEP1：スタイルを適用させたいセルを選択

見出し以外のセルを選択します。

STEP2：条件付き書式を表示

やり方が2通りあるので、好きな方でお願いします。

[表示形式] > [条件付き書式]
[右クリック] > [条件付き書式]

条件付き書式って、めっちゃ多用する機能なのにどうしてショートカットキー用意されていないんでしょうね。

右クリックしたりメニューから開いたりするの面倒いんじゃー。

【保存版】実務の効率化が加速するスプレッドシートのショートカットキー18選！

STEP3：条件とスタイルを設定

STEP4：数式を記入

『=$B2<10000』と数式を入力します。

カスタム数式のポイント

半角の『=』（イコール）から数式は始める
列の前に『$』（ダラー）を付ける
売上が入力されているのはB列
行番号は選択範囲のなかで一番上のものを記入。今回選択した範囲は『A2:B17』なので、行番号は2
『$B2』という記述のみでB列の3行目以降も、『B3、B4…』と条件判定される

つまり、『=$B2<10000』という記述だけで、範囲A2:B17の中で、『B2<10000,B3<10000,…,B17<10000』と条件を満たす行に色を付けてくれるという訳です。

カスタム数式のおさらいはここまで。次からこの記事の核となる部分を書いていきます。

複数条件を設定する方法

複数条件を使ってこそ、カスタム数式の真価が発揮されます。

カスタム数式で複数の条件を使う方法

AかつB：AND関数（AとB、どちらも同時に満たす場合Trueを返す。）
AまたはB：OR関数（AとB、どちらかが満たされた場合Trueを返す。）

例えば、次のようなシートがあるとします。

この中から赤点（35点未満）を取った生徒だけをマーキングしたいときは、OR関数って次のような数式を作成します。

条件付き書式設定ルール

範囲：B3:B7
カスタム数式：=OR($C3<35,$D3<35,$E3<35,$F3<35,$G3<35)

このように、複数条件を使えば、全教科の中で赤点を１つでも取った生徒を瞬時にあぶり出すことができるというですね。

…

これから先は、スプレッドシートでカスタム数式を使う上で欠かせない『日付』や『空白』、『テキスト』に注目して、より実践的なテクニックを紹介しています。

日付を設定する方法

おそらくカスタム数式を使う上で避けては通れないのが『日付データ』を扱った数式の作成だと思います。

日々の売上管理や日報、勤怠管理表、タスク管理など…

スプレッドシートでToDo管理！？新機能『チェックボックス』の紹介

カスタム数式でよく使う日付関連のシチュエーション3選

今日の日付に色を付けたいとき
今日より過去の日付に色を付けたいとき
今日から◯日以上△日以下などに色を付けたいとき

この辺ができるようになったら、実務レベルで困ることは無いと思います。

それでは具体的に見てみましょう。

今日の日付を含む、行すべてを塗りつぶす

『今日』について扱う関数と言えば、そう、TODAY関数ですね。

なので、TODAY関数をカスタム数式に組み込みます。

過去の日付を含む、行すべてを塗りつぶす

今日だけではなく、未来や過去の日付に色を付けたいときも出てきますよね。

そんなときは不等号を使えばOKです。

例として『過去の日付を含む、行すべてを塗りつぶす』を実現させます。

ただ、このままだと空白のとこにも色が付いてしまいます。

なので、IF関数を使って『空白ではない』かつ『過去の日付』のものだけに色付けしたら下図のようになりました。

今日から3日以上7日以下の日付を含む、行すべてを塗りつぶす

『TODAY()+1』や『TODAY()ｰ1』のように、TODAY関数に加算減算すると、今日より1日後や1日前を基準にいろいろ設定することができます。

『今日から3日以上7日以下の日付を含む、行すべてを塗りつぶす』を実現するには、この場合複数条件なので、AND関数を使用して2つの条件式を記入すれば良いと言うわけです。

空白を設定する方法

スプレッドシートで空白を判別する方法は、"（ダブルクオーテーション）を2回続けて『""』とします。

例えば『A1セルが空白なら』という条件式を作りたい場合は、『A1=""』とする感じですね。

逆に『A1セルが空白でないなら』という条件式を作りたい場合は、『A1<>""』と設定します。

ただ、『空白か』『空白じゃないか』を判定するだけなら、セルの書式設定の条件で『空白セル』もしくは『空白じゃないセル』を選択すればいいだけなので、あえてカスタム数式を使うまでもありません。

なので、カスタム数式で空白に携わった条件を扱う場合は、複数条件で使わないと真価が問われないです。

例：『空白でない』かつ『今日』にTrue

=AND($A1<>"",$A1=TODAY())

テキストに値が含まれるかを設定する方法

正規表現を使用する方法が一番簡単です＼(^o^)／

正規表現についての詳細は割愛します。気になる方は、下の記事をどうぞっ。

知らないと損！正規表現を使ってスプレッドシートの文字列を便利に抽出する方法

ちなみになんですけど、これから紹介する内容は、その記事を読まなくても、使いこなせると思います。なので、どうかこらえて読み進めてみてください。

カスタム数式の中で正規表現を使うためには『REGEXMATCH』という関数を使います。

構文

=REGEXMATCH("検索対象文字列","検索する文字列（正規表現）")

(※検索対象文字列に検索する文字列が含まれている場合『TRUE』を返す。含まれていない場合は『FALSE』を返す。)

例えば、A1セルの内容に『福岡』という文字列が含まれているか判定したいときは、『=REGEXMATCH(A1,"福岡")』とすればいいです。

"福岡"の部分を"福岡.+"としてあげると、『福岡タワー』や『福岡空港』みたいのように福岡の後に何かしら文字が無いとTrueを返さなかったりと、自分のニーズに合わせて柔軟に対応できます。

.	任意の1文字
+	1回以上の繰り返しにマッチ

『.で任意の一文字、+で1回以上の繰り返し』

つまり、『福岡.+』でテキストが福岡から始まって、その後に1つ以上文字列がない場合はFalseを返します。（下図参考）

最後に

今回はスプレッドシートのカスタム数式について、これでもかというくらい色んなことを紹介しました。

カスタム数式を使いこなせるようになれば、誰もがパッと見て分かりやすいスプレッドシートを作成することが出来るようになります。

頑張って情報が整理されたスプレッドシートを作ってみてくださいね。

それでは。