機能と数式 | VBA | セミナー | オンラインソフト | お問い合わせ | その他
Top > Excel > 機能と数式

曜日の色を自動的に変える



下図のように日付データが入力されているとします。B列に表示されている曜日が、土曜日は青、日曜日は赤で表示されるようにしてみましょう。これには条件付き書式を使います。なお、下図のように日付と曜日を表示する方法は「曜日を表示する」を参照してください。



条件付き書式は一般的に「セルの値が○以上だったら」のような条件を設定します。今回は「日付が○曜日だったら」という条件になりますので、何とかして日付が何曜日に該当するかを調べなければなりません。これには、いくつかの関数を使えますが、ここではTEXT関数を使います。TEXT関数の詳細については「TEXT関数」を参照してください。また、「曜日を表示する」の最後では、TEXT関数を使ってワークシート上で曜日を判定する方法も解説しています。

さて、TEXT関数の働きを確認しておきましょう。セルC2に「=TEXT(B2,"aaaa")」という数式を入力します。



TEXT関数の引数に指定したのはセルB2ですが、セルB2はセルA2を参照しているだけです。そのセルA2にはシリアル値が入力されています。TEXT関数の結果は「水曜日」と表示されていますが、これはB列の「水曜日」とは意味が違います。B列は表示形式でシリアル値を「水曜日」と表示しているだけですが、TEXT関数の結果は文字列の「水曜日」が返っています。

では、条件付き書式を設定します。セルC2の式は不要ですから削除しといてください。
曜日の色を自動的に変えたい範囲(ここではセル範囲B2:B10)を選択して、[書式]-[条件付き書式]をクリックします。



表示される[条件付き書式の設定]ダイアログボックスで、「数式が」→「=TEXT(B2,"aaaa")="土曜日"」と入力します。前半部分は、先ほど削除したセルC2の数式をそのままコピーしてもけっこうです。これは「TEXT関数の結果が"土曜日"だったら」という条件になります。



このとき、条件に設定した「=TEXT(B2,"aaaa")」の引数に設定した「B2」と、アクティブセルが一致している点に留意してください。複数のセルに同じ条件付き書式を設定する場合は、このようにアクティブセルを相対参照で指定するのが基本です。相対参照で指定すれば、引数のアドレスが自動的に調整されてすべてのセルに設定されます。

[書式]ボタンをクリックして文字の色を青に設定してください。[OK]ボタンをクリックすれば完了です。



同じように、もう一つ「=TEXT(B2,"aaaa")="日曜日"」という条件付き書式を追加してやれば、日曜日の色を変えることも可能です。





祝日の色も変えたい


ここまでくると、祝日の色も自動的に変えたくなるでしょう。その方法もご紹介します。ただし簡単ではありませんから覚悟してくださいね。

残念ながらExcelには、任意の日付が"祝日に該当するかどうか"を判定する機能も関数もありません。祝日というのは国によって異なりますし、日本国内でも法律が改正になれば新しい祝日ができたり、それまでの日が変更になる可能性もあるからです。祝日かどうかを判定する機能はありませんが、あらかじめ入力されている日付と一致しているかどうかを判定することならできます。祝日の色を条件付き書式で設定するときは、あらかじめ祝日のリストを作っておかなければなりません。



上図は別のシートに祝日の一覧を入力したところです。セルA1の日付が「2006/1/1」と年まで指定されている点に留意してください。本当はこれでは後で問題が発生するのですが、一般的にこうしたリストを作成する場合「1月1日」のように月と日だけを入力します。するとExcelはそれが「今年の日付」であると解釈します。そのため、実際に入力されるのは「2006/1/1」のように年を含んだシリアル値になるのです。これによってどういう問題が発生し、それを解決するにはどうしたらいいかは後述しますが、まずは一般的によくやるケースで解説を続けましょう。

上図のように祝日の一覧を入力したらA列の日付部分に名前を付けます。ここでは、セル範囲A1:A15に「祝日リスト」という名前を付けました。



さて、元のシートに戻り、祝日の色が自動的に変化するよう条件付き書式を設定しましょう。
任意の日付が「祝日に該当するかどうか」ということは、その日付が「祝日リストに含まれているかどうか」で判定できます。これにはMATCH関数を使います。下図は確認のため、セルC2に「=MATCH(B2,祝日リスト,0)」という式を入れて、セルB2の日付(=セルB1の日付)が祝日リストに含まれているかどうかを判定しています。



11月1日は祝日リストに含まれていませんから、"見つからなかった"という意味で「#N/Aエラー」が返りました。この数式をセル範囲C3:C10にコピーします。



11月3日は"文化の日"です。祝日リストに存在していますから"13番目に見つかった"という結果が返りました。しかし、この式をそのまま条件付き書式に設定することはできません。条件付き書式では、条件に「正しい(TRUE)か正しくない(FALSE)か」という数式しか設定できないからです。ここでは、MATCH関数が「数値を返したかどうか」を判定します。任意の値が数値かどうかはISNUMBER関数でわかります。ISNUMBER関数は、引数に指定した値が数値のときTRUEを返し、数値でなかったときにFALSEを返す関数です。セルC2の式を「=ISNUMBER(MATCH(B2,祝日リスト,0))」に変更し下のセルにコピーします。



この式を条件付き書式の条件に設定しましょう。条件付き書式を設定するセル範囲を選択して[条件付き書式の設定]ダイアログボックスを開きます。3番目の条件に上で作った数式を指定して、文字の色を赤にします。C列の式は必要ありませんので削除してください。



[OK]ボタンをクリックすると、祝日リストに含まれる11月3日だけに赤文字の書式が適用されます。



来年は使えない?


ここまでの流れに間違いはありません。しかし重要な問題点が残されています。それは、祝日の判定が来年は使えないということです。実際に試してみましょう。セルA2に「2007/5/1」と来年の日付を入力して、フィルハンドルをセルA10までドラッグします。すると、



祝日である5月3日が赤になりません。原因は先に書きましたが、祝日リストが今年(2006年)の日付になっているからです。2007年の5月3日を2006年のリストで探したのですから見つかるはずがありません。だから条件付き書式は、その日を祝日と判定してくれなかったのです。

これを解決する方法はたくさんあります。それこそアイデアしだいでしょう。どれが良いということはありませんし、ユーザーのスキルや使い方に合わせた方法を採用するべきです。ここでは、月と日の数値を使ったマトリックス表による解決策をご紹介します。

今年(2006年)の日付と来年(2007年)の日付を比較しようとするから一致しないのですから、年はこの際無視して、○月○日が祝日に該当するかどうかを判定します。それには祝日リストを次のように作成します。

クリックすると拡大します。拡大画像はクリックすると消えます

ワークシートの上から1日・2日・3日…とし、列は右方向に1月・2月・3月…という大きなマトリックス表を作ります。たとえば5月3日はセルF4になります。このマトリックス表で、祝日に該当するセルに祝日名を入力します。この表全体を数式で検索しますので名前を付けておきましょう。セル範囲B2:M32に「祝日表」という名前を付けました。1行目とA列は範囲に含めていない点に留意してください。

クリックすると拡大します。拡大画像はクリックすると消えます

元のシートに戻って、この「祝日表」から祝日を検索するような条件付き書式を設定しましょう。
「祝日表」は、○行目と○列目が交差するセルという形式で任意の日付を調べることができます。たとえば5月3日(セルF4)は「祝日表」の5列目3行目です。任意の日付からの数値を取得できれば、その数値を使って「祝日表」を検索できます。さっそくやってみましょう。C列に次のような数式を入力します。



セルC2は「=MONTH(B2)」、セルD2には「=DAY(B2)」という数式です。それぞれ、日付から月と日の数値を返す関数です。この2つの式を10行目までコピーします。



ある範囲(マトリックス)から、列と行を指定して交差するセルを調べるにはINDEX関数を使います。セルD2には「=INDEX(祝日表,D2,C2)」という数式を入力して10行目までコピーしました。



どうやら成功のようです。セルD2とセルC2は、それぞれDAY関数とMONTH関数で日と月の数値を調べているのですから、これらの関数を合体させると次のようになります。



この「=INDEX(祝日表,DAY(B2),MONTH(B2))」が何らかの文字列を返したら、そこには"祝日名"が入力されているとわかります。この数式を使って3番目の条件付き書式に「=INDEX(祝日表,DAY(B2),MONTH(B2))<>""」という数式を設定し、文字色を赤にします。



5月3日(憲法記念日)と翌日の5月4日が赤に変わりました。しかし、5月5日(子供の日)が青になってます。この日は土曜日なのですから青でもいいですが、「祝日だったら赤」の方を優先したいですね。そんなときは条件付き書式の順序を入れ替えます。条件付き書式は3つまで条件を指定できますが、複数の条件に一致するデータの場合は、先(上)に指定した条件が優先されるのです。そこで、最後(3番目)に指定した祝日の判定を1番上と入れ替えます。この際、土曜日の判定は最後でいいでしょう。





これで、年に拘わらず月と日だけで祝日の判定ができるようになりました。ちなみに、今回作成したような「祝日表」なら、カレンダーなどで祝日名を表示したり、会社の創立記念日など独自の休日を判断するようなときにも、簡単に応用ができます。






このエントリーをはてなブックマークに追加