はじめに

皆さん、こんにちは、エンジニアの Hodoshima です。

業務の中に置いて、成果物を表の形式でまとめると言うことはよくあることだと思います。

弊社では多くの場面で、Googleスプレッドシートを用いて、色々な表を作成しています。

その際に、表の中にあるより注目してほしいセルに色をつけることはその値をより効果的に見せるために非常に有効な手段です。

しかし、個別のセル値に対していちいち色付けの設定を行うのは非常に面倒くさく、ミスも発生しやすいです。

セルの値によって自動的に色をつけてくれる機能として、「条件つき書式」というものがあります。

この条件付き書式について、閾値となる値が別のセルに設定されている時、その閾値の値を変えると自動的に条件付き書式の結果も変えてくれると、例えば、ある仕様のボーダーラインを決める際に、ボーダーラインを変えると自動的にセルの書式も変わってくれるため、非常に効果的になります。

今回は、動的な条件付き書式の設定方法とその利用例を紹介します。

---

今回は以下のような店舗と月毎の売り上げのテーブルを例として色々な条件でセルの中身を条件つき書式で色付けていきます

---

条件付き書式

この記事の題材となっているスプレッドシート上の条件書式は、次の手順で設定を行うことができます。

• (1) 条件付き書式を設定したいセルまたはセルの範囲を選択する。
• (2) マスの色設定 (もしくは文字の色設定) 欄から「条件付き書式」を選ぶ
• (3) 右に現れる「条件付き書式設定ルール」から条件と書式の設定を行い、「完了」を押す。

〇〇 (固定値) 以上 (以下、より大きい、未満)

「10 以上」や「300 未満」などのように、あらかじめ上端や下端が固定されている場合は、範囲の設定方法と下端 (上端) を入力するだけで簡単に条件つき書式を設定することができます。

(例) 「10 以上」の場合

• (1) 「セルの書式設定の条件」のセレクトボックスから、「以上」を選ぶ。
• (2) 「以上」を選んだ場合に現れる下端の値入力ボックスに 「10」を入力する。
• (3) 「完了」を押して、書式を確定させる。

下端を動的に設定できるようにする

先ほど説明した方法は、下端や上端が固定されている条件に関する条件つき書式の設定方法でした。 しかし、色の付け方を動的に変えたいというようなニーズもあります。

例えば、別のセルに売り上げ目標が記載されており、この値以上のセルに色を塗りたい場合を考えます。 一々条件付き書式の条件の下端を変えることなく、売り上げ目標の値を変えるだけで、自動的に書式が変わるようにすることができます。

条件の設定方法も単純で、先ほど説明した「固定された下端」の場合の手順の下端の入力を具体的な値から、売り上げ目標が入っているセルのセル番地を =(セル番地) の形に変えるだけです。

ただし、この場合のセル番地は絶対参照の形式で与えなくてはいけません。

相対参照で与えてしまうと、範囲内にあるセルが条件を満たすかどうかを調べる際に、起点となっているセルからの相対位置の分だけ、条件つき書式が参照する売り上げ目標の値がずれてしまい、期待通りの条件にならないからです。

(セルの参照を相対参照にしてしまい、色付けがうまくいかなかった例)

行に関する条件付き書式を一括指定する

これまで紹介した方法は全て、1 つのセルが動的に動く 1 つのセルに関する条件でしたが、今度は条件に使うセルが 1 行にまとまっている場合を紹介します。

今回例として挙げている、店舗ごとの月次の売り上げの表への応用例としては、

• 各店舗について、売り上げが最も多かった月の売り上げを色付けする

に対応します。

このように複数個のセルが絡み合う複雑な条件であっても、次のことに注意して閾値を設定することにより、行ごとの条件にすることができます。

• 列に関する条件なのか、行に関する条件なのかに注意し、適切に相対参照と絶対参照を設定する。
  • 店舗ごとの売り上げの最大は 1 行に関する条件になる。つまり、行を動かして、列は固定する必要があるため、参照の書き方は (条件付き書式にしたい一番左上のセルのセル番地が B3 なので) $B3 のようになる。
• 相対参照によるセルの参照ずれに注意する。一番左上のセルに関する条件を入力すると、相対参照の移動がうまくいきやすくなる。

「各店舗について、売り上げが最も多かった月の売り上げを色付けする」は閾値を「=max($B3:$E3)」とすることにより、「各セルの値は自分自身が属している行における最大値と一致するか?」を判定してくれ、行の最大値と一致するならば、自動的に書式を設定してくれます。

カスタム数式を使ってさらに複雑な条件に関する条件付き書式をつける

最後に、カスタム数式を用いて、さらに複雑な条件を持った条件付き書式を動的に実現させます。

例として、「奇数か偶数かをあるセル指定し、売り上げがその指定した奇数 or 偶数になっているセルの色付ける」で説明します (実生活のなかでこんな条件で色付けをすることはないと思いますが)。

セルに入力する奇数偶数の指定は、偶数が 0 、奇数が 1 となることを仕様とします。

カスタム数式では、入力する式の最終結果が TRUE か FALSE になるようにする必要があります。

スプレッドシートにおいて、整数 x を y で割った余りは MOD(x, y) で取得できます。

偶数を 2 で割った余りは 0、奇数を 2 で割った余りは 1 です。

そのため、売り上げ (整数) が奇数であるかどうかは、売り上げを 2 で割った余りが「奇数偶数の指定で入力されている 0 または 1」と等しいかどうかを判定すればよいことになります。

これを条件付き書式のカスタム数式を用いて、 TRUE か FALSE が返り値になるようにし、相対参照、絶対参照に注意すると)、例えば、

• =(MOD(B3, 2)=$G$3)

と書くことができます ((MOD(B3, 2)=$G$3) の部分で、各売り上げが奇数偶数の指定と等しいかどうかで TRUE か FALSE の返り値が返ってきます)。

カスタム書式を使うことによって、複雑な条件であっても、1 つの条件で動的にセルの条件付き書式を変えることができます。

---

最後に

今回は、Googleスプレッドシートの条件付き書式をセルの値によって動的に更新する方法を紹介しました。

表のセルを目立たせることは非常に有効な手段です。今回のようにあるセルの値によって動的に書式を変えられるようにすると、比較検討や仕様決定の際の資料として非常に便利になります。

皆さんも、動的な条件付き書式を作ってみて、比較検討や仕様決定の際に重要なポイントを目立つような資料を作ってみてください。