【当サイトはアフィリエイト広告を利用しています】

Excelの条件付き書式は大変便利、表示も分かりやすくなる

2015年1月10日

マスターすると大変便利な条件付き書式

money and savings
money and savings / 401(K) 2013

前職はシステムエンジニアだったのですが、その頃には「Excel(エクセル)」の使い方をほとんど理解していませんでした。

退職してから書籍を購入し、Excelを独学で勉強したことで、現在はそれなりに使えるようになっています。

その時に覚えた機能で現在も重宝してるのが、条件付き書式という機能。これ、ホント便利なんです。システムエンジニア時代に理解してればもっと仕事が捗っただろうに。

Skitched 20150110 01

↑ Mac版Excelだと、「ホーム」タブの中央付近にこんな感じのアイコンがあります。これが条件付き書式。

Skitched 20150110 02

↑ もうちょっと画像を拡大すると上のような感じですね。ホームタブの中に条件付き書式のアイコンがあります。

Skitched 20150110 03

↑ 参考までに、Windows版Excelだと上のような感じ。アイコンも同じだし、位置も似てますね。

私が使ってるExcelのバージョンは以下の通りです。

◆Mac … Excel for Mac 2011
◆Windows … Excel 2007

使用しているスクリーンショット画像も上記バージョンのものなので、異なるバージョンの場合は画面内容や機能解説が違うかもしれません。ご了承ください。

条件付き書式を使って日付の表示を変えてみよう

では、条件付き書式を使って1つテストをしてみます。条件付き書式をご存知ない方は、同じようにExcelを使って実験してみてくださいね。

Skitched 20150110 04

↑ まず、日付を10個、縦に入力します。上のサンプルでは罫線を付けてますが、付けなくてもいいです。またテーブルにしてもいいし、何もせず上の通り日付を入力するだけでも大丈夫です。

このテストを実施したのは1月10日なので、5行目と6行目に当日(=1月10日)を、それよりも上の1行目〜4行目には過去の日付、後ろの7行目〜10行目には未来の日付を入れてます。テストする場合は、5行目と6行目を「テストする当日」に変更し、他の行も適宜調整してください。

Skitched 20150110 05

↑ 一応念のため、日付を入力したセルの書式(条件付き書式ではなく、通常の「セルの書式」です)を「日付」にしてます。日付を正しく入力していれば自動的にセル書式が「日付」になっていると思います。

ちなみにセル書式の変更方法は、変更したいセルを範囲指定するなどしてから、右クリック→「セルの書式」→「表示形式」タブで分類を「日付」にすればOK。

以降の解説は全て「Mac版Excel」で実施した内容です。Windows版Excelは画面が違いますが、やることはほぼ同じはずです。

Skitched 20150110 06

↑ では条件付き書式を設定していきます。まず設定したいセル範囲(私の例だとA1セルからA10セルまで縦に10個)をドラッグして範囲指定しておいてください。

範囲指定したら、条件付き書式アイコンをクリックし、一覧の中から「新しい仕分けルール」をクリック。

Skitched 20150110 07

↑ 新しい仕分けルールを次の通り変更します。

◆スタイルを「クラシック」に変更。
◆「指定の値を含むセルだけを書式設定」に変更。
◆「日付」「今日」に変更。
◆書式は「ユーザー設定書式」に変更。

まず、日付が今日のものだけに条件付き書式をセットしてみます。一番簡単な設定は上の通りなんですけど、これ、実は少し問題があってですね、

Skitched 20150110 08

↑ 「日付」で設定できる条件が上の画像にある数しか用意されてないんですよ。

「今日」だけを判定したいなら問題ないのですが、たとえば過去の日付もセットするとなると、一覧には「過去7日間」「先週」「先月」という限定された条件しか用意されてませんので、「過去の日付全てに書式を設定したい!」時には、この方法だと無理。

じゃあどうするかは、後で解説しますね。今はひとまず「日付」「今日」でセットしてください。

Skitched 20150110 09

↑ ユーザー設定書式は上のようにセットしました。日付が今日の時は、文字色を赤にして、太字にする、という指定。

Skitched 20150110 10

↑ セルの背景色も条件付き書式で設定できます。初期状態ではピンク色になってますので、今回はこのまま変更せず、ピンクのままにしておきます。

Skitched 20150110 11

↑ 条件付き書式を設定して「OK」をクリックすると、ルールの管理という画面に設定内容が追加されます。「適用先」が空白になってる時はセルの範囲指定が失敗してますので、ドラッグをやり直せば大丈夫。

ルールに「今日」、書式が「赤い太文字で背景ピンク」という状態になってれば設定OKです。もし何か違ってたら、左下にある「ルールの編集」というところをクリックすると変更できます。

全て問題なければ、ルールの管理画面でOKをクリックしてください。

Skitched 20150110 12

↑ 10行のうち、今日の日付を入力している5行目と6行目の2つだけ、書式が変更されていればテスト成功です。

もっと柔軟な条件に変更し、別の条件も追加してみよう

前述しましたが、「日付」を「今日」という設定方法では、用意されている条件のみでしか判定されません。過去や未来の日付を指定したい時は不便。

なので別の条件設定に変更してみます。

Skitched 20150110 13

↑ 再び条件付き書式アイコンをクリックし、今度は「ルールの管理」を指定。ルールの管理画面が表示されたら、左下にある「ルールの編集」をクリック。

Skitched 20150110 14

↑ ルール編集画面で、次の通り条件を変更します。

◆「日付」を「セルの内容」に変更
◆右側の条件設定項目が変更されるので、一覧の中から「次の値に等しい」を選択
◆値を入力する欄に「=today()」と入力

※スタイルの「クラシック」、「指定の値を含むセルだけを書式設定」、「ユーザー設定書式」の3箇所は変更せず最初のままにしておきます。

「today」は小文字・大文字、どちらもいいです。小文字で入力しても後で自動的に大文字変換されます。ただし today の前後、半角の「=」と半角カッコはお忘れなく。これを忘れると正常に動作しません。私も少しハマりました。

Skitched 20150110 15

↑ 「セルの値=TODAY()」と、上のように変更されていればOK。

続いて過去日付と未来日付にも条件付き書式を設定してみましょう。条件を追加するには左下の「+」をクリック。

Skitched 20150110 16

↑ まず、未来の日付に条件書式を設定します。未来の日付、つまり「今日よりも値が大きい日付」ですので、上の通り「セルの内容」を「次の値より大きい」にします。値は「=TODAY()」で最初と同じ。

ユーザー設定書式は、「文字色は濃いグリーン」「斜体文字」「背景色は薄いグリーン」に、それぞれ変更します。

Skitched 20150110 17

↑ もう1つ、過去の日付設定もやることは同じ。「次の値より小さい」にすることで、今日よりも古い過去日付の全てが対象になります。

ユーザー書式は「文字色を薄いグレー」「取り消し線」「背景色なし(=白)」に、それぞれ設定します。具体的には、

Skitched 20150110 18

↑ こんな感じです。背景色は「塗りつぶし」タブで移動してから白に変更しておきます。

Skitched 20150110 19

↑ ということで、今日の判定を変更し、過去と未来の日付条件2つを新たに追加し、全部で3つの条件付き書式が設定できました。さあ〜どうなるか。OKをクリックして結果を見てみましょう。

Skitched 20150110 20

↑ 上のようになればテスト成功です。過去日付(1〜4行目)は「背景色が白で、取り消し線が入った、薄いグレー文字」に、未来日付(7〜10行目)は「背景色が緑で、斜体で、濃いグリーン文字」に、それぞれ変わります。今日の日付は最初のテストと変わりなし。

Skitched 20150110 21

↑ では、9行目に入ってる未来日付のところを過去の日付に変更してみましょう。1年前の2014年に変更することで過去日付にしてみました。

上の画像は入力がまだ確定されてない状態なので、まだ背景色や文字色はグリーンのままです。Enterキーを押すなどして文字入力を確定させると、

Skitched 20150110 22

↑ 9行目のセルが上のように過去日付の条件設定で、自動的にパッと変わればテストは全て成功です。

このように条件付き書式を設定しておけば、その条件に合致する値に変更するだけで、文字色や背景色、太字や斜体、更にはフォントの大きさなど様々なセル書式を自動的に変更させることができます。イチイチ手作業で文字の色などを修正する必要がありません。

りくま ( @Rikuma_ )的まとめ

今回は日付を例にテストしてみましたが、もちろん文字列や数値でも条件付き書式をセットすることが出来ます。

都道府県別に色を変えたり、テストの点数に応じて書式を変更するなんてことも可能。範囲指定で条件をセットすれば、行をソートで並べ替えても条件に応じて自動的に書式が変更されるからとても便利ですよ。

私はExcelで「買い物履歴」を毎回つけてます。iPhoneのアプリ、KindleやKoboの電子書籍、いろんなものを買います。買ってすぐ支払いが完了したものは入力してハイ終わりなんですけど、そうじゃないものもあります。

Toodledoやflickrなどのプロアカウント、レンタルサーバ代やドメイン代といった類いのものは1年に1回、口座からの引き落としが発生します。またATOK Passportのように毎月幾らという月額払いのものもあったりします。

年払いや月払いのものが意外とあることを自覚せず、口座に残高があるからといって調子に乗って買い物をし過ぎると、引き落とし不能とかになって面倒なことになりますよね。

なので、今回紹介した条件付き書式を使って、

Skitched 20150110 23

↑ まだ支払いや引き落としが完了していない明細には、「まだ支払い終わってねえぞ!」と分かるよう、引落日という欄に条件付き書式をセット。今日以降の日付は全て赤くするようにしてます。こうすることで「今月末には幾ら引き落とされるな」ということも分かり、未来の残高も把握できる。

今月末の支払いは、これと、これと、ああこれもだ、などと1つ1つを手作業で赤くしたり、支払いが完了したものを黒色に変更したりするなんて面倒なので、条件付き書式をセットしておけば全部自動でやってくれます。ラクチン。

この「買い物履歴Excel」は、2013年にMacBook Airを購入する以前、まだ自宅のメイン機がWindowsだった頃に作成して記録を始めたものです。Macを買って以降、買い物履歴を記録するためだけにWindowsを起動するのが面倒でタマラン!というのもMac版Officeを購入した大きな動機でした

条件付き書式のカラクリさえ把握すれば、いろんな応用が出来ると思います。バリエーション豊富な条件設定が可能ですので、Excelを使っていろいろ管理されてるかたは是非活用してみてくださいね。

-Mac
-