「数式」を使ってセルの書式を変化させる方法

「条件付き書式」の弱点

基本的に「条件付き書式」は、「自身の値」に応じてセルの書式を変化させる機能といえる。以下の図はこれまでに何回も示してきたもので、「残在庫」が400以上のセルを強調表示した場合の例となる。

• 

  400以上を「赤色の背景」にした「条件付き書式」

売れ残りが懸念される商品を一目で識別できるのが特長といえるが、ひとつだけ弱点もある。それは「強調表示されたセル」を左方向に目で追っていかないと「売れ残りそうな商品」を確認できないことだ。

たとえば、J8セルは「残在庫」が421もあることを示しているが、「どの商品が売れ残りそうなのか？」を知るには、J8セルの行を左方向へ目で追っていき、「イラストA、緑色のTシャツだ」と確認しなければならない。

さらに列数の多い表になると、「強調表示されたセル」を左方向に目で追っていく作業はより困難になる。以下の図は「各店舗での販売数も追加したい」と表を拡張したものとなる。このような列数が増えていくと、1つ下（または上）の行と見誤ってしまう危険性もそれだけ高くなる。

• 

  列数の多い表

こういった問題を回避するには、「商品名のセル」を強調表示するように改良してやればよい。そのためには「数式」を使って、「他のセルのデータ」をもとに「条件付き書式」を指定する方法を覚えておく必要がある。

「数式」を使った「条件付き書式」の指定

では、「数式」を使って「条件付き書式」を指定する方法を紹介していこう。まずは、「条件付き書式」を指定するセル範囲を選択し、「条件付き書式」→「新しいルール」を選択する。ここまでの手順は、これまでに紹介してきた手順と同じである。

• 

  セル範囲の選択と「新しいルール」の作成開始

「新しいルール」の画面が表示されるので、ルールの種類に「数式を使用して、書式設定するセルを決定」を選択し、数式の入力欄に「=」（イコール）を入力する。この「=」を忘れると「条件付き書式」が正しく機能しなくなるので注意すること。

• 

  ルールの種類に「数式」を指定し、「＝」を入力

「=」に続けて条件となる数式を入力する。今回は「J列の数値が400以上」を条件にするので「J4>=400」と数式を入力すればよい。なお、ここで指定するセル参照（J4）は、基本的に「データ内で1番上にあるセル」を指定すればよい（詳しくは後述）。

• 

  条件の指定

あとは条件に合う場合の書式を指定するだけ。今回は「赤色の背景色、白色の文字、太字」の書式を指定した。

• 

  書式の指定

「OK」ボタンをクリックすると「条件付き書式」の指定が完了する。この結果を見ると、「J列の値が400以上か？」に応じてB列のセルが強調表示されているのを確認できる。

• 

  「条件付き書式」により指定された書式

このように、「数式」を使うと「他のセルの値」をもとに書式を変化させることが可能となる。同様の手順でC3:C18のセル範囲にも「数式」を使った「条件付き書式」を指定すると、売れ残りそうなTシャツの「デザイン、色」を一目で把握できるようになる。

• 

  セル範囲の選択と「新しいルール」の作成開始

• 

  「条件付き書式」により指定された書式

以上が「数式」を使って「条件付き書式」を指定する方法の概略となる。「他のセルの値」に応じて書式を変化させることが「意外と簡単である」と実感できるだろう。

「数式」を条件にした場合のセルの対応

「数式」を使って条件を指定するときは、最初に「選択したセル範囲」と「数式に指定したセル参照」の関係について、その仕組みをよく理解しておく必要がある。

「選択したセル範囲」には複数のセルが含まれているが、「数式に指定したセル参照」は1つしかない。このような不整合があるにもかかわらず「条件付き書式」が正しく機能してくれるのは、Excelが自動補正を行ってくれるためだ。

簡単な例を使って解説していこう。以下の図は、「北海道」～「九州・沖縄」のセル範囲（B3:B10）に「条件付き書式」を指定する場合の例となる。その条件には「=D3>=100」の数式を指定してあるため、「D3セルが100以上」が条件となる。

• 

  「選択したセル範囲」と「数式に指定したセル参照」

この結果は下図のようになり、「東北」「中部」「四国」「九州・沖縄」のセルが強調表示されることになる。このような結果になるのは、D3セルを下方向へ1つずつ補正しながら条件判定が行われているためだ。つまり、「北海道」はD3セル、「東北」はD4セル、「関東」はD5セル、……と自動補正されながら条件判定が行われることになる。

• 

  「条件付き書式」により指定された書式

状況を把握しやすいように、上図では「条件となるセル」に色を付けてある。背景が黄色のセルは「条件となるセル」で、そのなかで背景がオレンジ色のセルは「値が100以上」すなわち条件に合致するセルを示している。ちなみに、背景をグレーで示したセルは「条件とならないセル」を示している。

ここで示した例は、先ほど紹介した「売れ残りそうなTシャツ」を強調表示する場合と基本的に同じ仕組みになる。では、「選択したセル範囲」と「数式に指定したセル参照」を1行ずらして指定した場合はどうなるだろうか？

以下の図は、「条件付き書式」を指定したセル範囲（B3:B10）は前回と同じあるが、条件の数式を「=D2>=100」と1行ずらして指定した場合の例である。この場合、強調表示されるセルも1行ずつずれていくことになる。

• 

  「選択したセル範囲」と「数式に指定したセル参照」

• 

  「条件付き書式」により指定された書式

つまり、D2セルを起点に、下方向へ1つずつセル参照を自動補正しながら条件判定が行われることになる。もちろん、この自動補正は左右方向にも機能する。以下の図は、B3:C6のセル範囲に「条件付き書式」を指定し、条件の数式を「=E4>=100」と指定した場合の例となる。

• 

  「選択したセル範囲」と「数式に指定したセル参照」

• 

  「条件付き書式」により指定された書式

この場合も、数式に記述したE4セルを起点に、上下左右に1つずつ補正されながら条件判定が行われる。このように「数式」を条件にした場合は、「数式に記述したセル参照」が起点となって上下左右に自動補正されながら条件判定が行われる仕組みになっている。

セル範囲を選択するときの注意点

数式を使って「条件付き書式」を指定するときは、セル範囲を選択する際の「マウスの動かし方」にも注意しなければならい。通常、セル範囲を選択するときは左上から右下へマウスをドラッグするのが一般的である。しかし、まれに逆方向にマウスを動かしてセル範囲を選択する場合もある。

以下の図は、「B10セル」から「B3セル」に向かってマウスを動かし、B3:B10のセル範囲を選択した場合の例となる。この状態で「=D3>=100」を条件に「条件付き書式」を指定すると、以下の図のような結果になる。

• 

  「選択したセル範囲」と「数式に指定したセル参照」

• 

  「条件付き書式」により指定された書式

「選択したセル範囲」と「数式に指定したセル参照」の上端が揃っているため、「同じ行にあるD列の値をもとに条件判定が行われる」と考えるかもしれないが、実際にはそうならない。この場合、B10セル（アクティブセル：白く表示されているセル）を起点に自動補正が機能する。

具体的には、B10セルとD3セルが対応することになる。D3セルの値は100以上なので、B10セルは「条件付き書式」により強調表示される。他のセルはどうかというと、

・B9セル（四国）はD2セルと対応 → 100未満なので強調表示されない
・B8セル（中国）はD1セルと対応 → 「データなし」なので強調表示されない

という結果になる。B7セル（近畿）にいたっては、対応するセルすら存在しない状況になってしまう。

このような場合に、「同じ行にあるD列の値」で条件判定を行うには、数式を「=D10>=100」と指定しなければならない。つまり、どちらも起点が同じ行になるようにする訳だ。重箱の隅をつつくような話ではあるが、無用なトラブルを避けるためにも、頭の片隅に覚えておくとよいだろう。