前回の記事でExcel(エクセル)でドロップダウンリスト(プルダウン)の作り方はわかったけど、完了した項目は手動ではなく自動でグレーアウトできるようにしたいなあ。それに複数条件の指定が可能かも知りたい。
(ZACK IT編集)
Excelでは特定のセルに入っている値を条件に、
セルの書式設定を自動で変更する機能を持っているから
わざわざ手動でセルの色を変更する必要は無いよ。
またカスタム数式でANDやORで指定すれば複数条件も可能なので詳しく説明するね。
セルの背景色を自動で変更する方法
条件付き書式を使う
条件付き書式とは、例えば値が100以下だったら色付きの背景にしたり、選択範囲の中で最も大きい値を太字(Bold)にしたりすることができ様々な設定項目があるほか、カスタム数式を選択することによってANDやOR、IF関数といった数式や関数を使用できるため、複雑な条件指定にも対応している便利な機能だ。
条件付き書式を使うメリット
まず、ただ罫線を引いて作成しただけの表に比べて視認性が上がるため、どこの値が上位/下位であるかや、平均より下なのか上なのか、完了済みなのか未完了なのか即座に確認することができるため数値を扱うデータを頻繁に扱うユーザーは覚えて損はない。
条件付き書式の設定方法
まず、テスト受験者のリストで、スコアが表示されている場合に、成績優秀者1名と最下位の受験者1名を書式設定したい場合と仮定する。
1.まず、書式を設定したいセルを選択(範囲指定可能)
2,3,4.次に、ホームタブの中にある条件付き書式をクリックし、セルの強調表示ルールのリスト下部にある「その他のルール」を押下。
5,6.「上位または下位に入る値だけ書式設定」を選択し、上位/下位の何名を抽出するか設定した後、「書式」のボタンからセルの文字色や背景色等を設定したらOKボタンで確定。
セルに設定した背景色やフォント書式が反映されていれば成功だ。
今回は生徒の定期テストの5教科総合点を管理し、上位1名と最下位者を抽出するという想定の表で説明した。したがって現在は上位と下位、それぞれ複数の書式が同じ指定の範囲に同時反映されていることが確認できる。
現在有効になっている条件付き書式を確認するにはExcelのリボンメニューから「条件付き書式」をクリックし、下部にある「ルールの管理」をクリックすることで確認が可能だ。
セル単体ではなく、行全体に反映したい場合はどうしたらいいの？
条件付き書式はセル1マスだけではなく行全体や表全体に書式を反映することもできる。
今回の場合は、プロジェクト管理で”状況”の項目が”完了”になったらそのセルを含む表の行全体がグレーアウトするように設定する。
上手く設定が反映されている場合、B3セルが未対応や対応中といった文字列から、完了フラグに変更された場合に自動で選択されていた範囲が一気にグレーアウトされる。勿論、完了から対応中に戻せばグレーアウトは自動で解除される。
=$B3="完了"
”B3からG3”までのセルを範囲選択して右クリックしコピー後、下の行へ反映させたい範囲を選択しながら再度右クリックをして”書式のコピー”をすれば、表全体に同じ書式設定が反映されるため他の行で完了/未完了を切り替えれば該当の行がグレーアウトされる。
ちなみに、セル指定のB3に$マークがついているのは、B列は選択を固定するためだ。いわゆる”絶対参照”と呼ばれるものとなる。”3”も絶対参照にしたいのであれば、”$B$3”と$マークをそれぞれつけることとなる。
つまり、書式や関数を別のセルにコピー＆ペーストした場合に、参照元となるセル(B3)もずらした分だけずれてしまうのが相対参照。
列レベルで固定したい場合、行レベルで固定したい場合、どちらも固定したい場合で$マークの位置をずらしたり追加したりすることが絶対参照では可能だ。
| 列の相対参照 | 列の絶対参照 | |
| 行の相対参照 | B3 | $B3 |
| 行の絶対参照 | B$3 | $B$3 |
応用編－複数条件で条件付き書式を設定したい
条件付き書式は複数の条件を設定することが可能なので、例えば前で説明した条件を残しつつ、対応期限切れの場合はアラートの為に行を赤くしたいとする。
その場合はカスタム数式に関数も併用可能なので下記のようにAND関数を設定できれば、複数条件を基に期限切れの際のアラート設定も可能だ。
=AND($G3<TODAY(),$B3<>"完了")数式の意味をざっくり解説すると、G3セルの期限日が今日の日付(TODAY関数)より前かつ状況が完了ではない(<>”完了”)の場合、既に期限切れなのに未完了なので表の行全体を赤く設定してアラートを出すようにするためのカスタム数式設定だ。
ちなみに、今回の主題ではないが、NOTを表す記号が”<>”だ。”=(イコール)”とともにExcelではよく使う記号なので覚えておくといいだろう。
今回はTODAY関数を用いてファイルを開いている日より期限日が古かった場合に書式変更がされる設定になっているが、期限の例えば3日前にアラートを出すということも可能だ。
=AND($G3<=TODAY()+3,$B3<>"完了")上の画像でいうと、本日(TODAY関数)が3/11だとして3日足すと3/14になる。期限日も同様3/14の為3日前であるお知らせの為、書式が変更される設定だ。次の日(3/12)以降も3日足すと3/14を過ぎることになるため当然条件が書式有効だ。
条件付き書式を解除したいのに削除できない
条件付き書式の設定解除は、条件付き書式を削除したい範囲を選択した状態で「ルールの管理」から「ルールの削除」をクリックするとクリアすることができる。
また、もし罫線などの他の書式も不要でクリアしたい場合、Excelのホームタブの右端にあるピンクの消しゴムツールをクリックすると、「書式のクリア」というメニューが表示されるのでクリックすると選択している範囲にある設定済みの書式全てが削除される。
まとめ
今回説明した条件付き書式は前回説明したデータと入力規則と、ドロップダウンリスト(プルダウン)の設定方法と組み合わせることで非常に視認性の高いリスト・表を作成可能だ。
完了した行をグレーアウトするのに使ったり、期限切れを教えてくれる書式設定を作ったりと割と何でもできるのがこの条件付き書式設定だ。
仕事でToDoやプロジェクトの管理でExcelを使用する状況がある場合はぜひとも覚えておきたい。
