Excel。2017年の祝祭日対応のカレンダーを作成してみよう
＜DATE&ROW&TEXT&MATCH&IF&EOMONTH関数・表示形式・条件付き書式＞
以前にもカレンダー関連の記事を書いたことがありますが、
2017年になりましたので、
改めて、Excel2013で、2017年の祝祭日対応のカレンダーを作成してみましょう。
次のようなカレンダーを作成します。
さて、このようなカレンダーを作るのにあたり、
可能な限り修正箇所は少なくしていきたいところですね。
具体的には、年と月を入力したらその年月のカレンダーを表示したい。
土日祭日がわかるようにセルを塗りつぶしたい。ということをやっていきます。
では、B2に2017。C2に1と入力します。ここの数値で年月の日付を作るわけです。
すなわち、B5に2017/1/1という日付を作りたいので、
DATE関数を使うためのB2とC2というわけですね。
ただ数値のままだとわかりにくいので、
セルの書式設定ダイアログボックスの表示形式にある、
ユーザー定義を使って、年と月が表示できるようにしていきます。
それでは、B2をクリックして、セルの書式設定ダイアログボックスを表示しましょう。
表示形式の分類からユーザー定義を選択して、
種類を、0”年”として、OKボタンをクリックしましょう。
同じように、C2には、0”月”と設定しましょう。
次に、日付を作っていきます。
B5にDATE関数ダイアログボックスを表示します。
年には、$B$2 オートフィルで数式をコピーしますので絶対参照を設定します。
月には、$C$2
日には、ROW()-4 ここには、1という数値がほしいのと、
オートフィルで数式をコピーしますので、連続した数値を入力したいわけです。
そこで、行数を算出するROW関数を使い、1という数値にするために、今回は-4しました。
よって、ROW()-4。
そして、OKボタンをクリックして、1月31日まで数式をコピーします。
B5の数式は、
=DATE($B$2,$C$2,ROW()-4)
しかし、この数式だと、例えばC2を2月にしてみると、
このように、2月や4月など月末日が28日や30日だと、翌月が表示されてしまうわけですね。
こうならないように数式をアレンジしていきます。
色んな方法があるのですが、
今回は月末日かどうかを判断させる方法で数式を修正していきます。
そこで、末日を算出するのがEOMONTH関数。
そして、それを判断するのでIF関数も登場しますので、B5の数式は、
=IF(EOMONTH(DATE($B$2,$C$2,1),0)>=DATE($B$2,$C$2,ROW()-4),DATE($B$2,$C$2,ROW()-4),"")
見た目長くなってウンザリのようにみえますが、次のようなことをやっているだけです。
あとは、数式をオートフィルでコピーします。
C列の曜日を作成して行きましょう。
通常ですとIF+WEEKDAY関数というテクニックを使うのですが、
煩雑な数式になってしまうのと、条件付き書式でも楽に設定したい観点から、
TEXT関数を使って曜日を算出します。
C5をクリックして、TEXT関数ダイアログボックスを表示しましょう。
値には、B5
表示形式には、”aaa” aaaで、曜日の表示形式になりましたね。
ちなみに”aaaa”で～曜日という表示になりますよね。
OKボタンをクリックして、オートフィルで数式をコピーしましょう。
いよいよ、土・日・祝祭日の行を塗りつぶす作業に取り掛かりましょう。
条件付き書式の登場ですね。
A5:D35を範囲選択して、ホームタブの条件付き書式の【あたらしいルール】をクリックして、
新しい書式ルールダイアログボックスを表示して、
【数式を使用して、書式設定するセルを決定】を選択します。
次の数式を満たす場合に値を書式設定のボックスに、
=$C5="土"
と入力して、書式ボタンから青色系でセルを塗りつぶすようにしましょう。
OKボタンをクリックします。
同じように、
=$C5="日"
として、赤色系でセルを塗りつぶすようにしましょう。
条件付き書式の数式ですが、列を固定してあげる複合参照にしてあげると、
行で塗りつぶすことが出来ますので、複合参照が苦手な方は、
アルファベットにドルと覚えておくといいですね。
さぁ、土日は出来ましたので、祭日に取り掛かりましょう。
日本の祭日はラッキーマンデーや、春分・秋分の日のように祭日が固定されておりません。
そのため、別のシートに祭日の一覧表を作成しておく必要があります。
今回は、【休日】というシートを作り、次のような表を用意しました。
そして、A2:A18に【休日一覧】という名前を定義しておきましょう。
この休日一覧に日付があるのかどうかを、
照合させる数式を条件付き書式に設定するのですが、数式を直接手入力すると、
間違えやすいので、一度、別の列を使って数式を作成して、
その数式を、条件付き書式で使用するという方法でやっていきます。
それでは、シートを戻りまして、照合させる関数。MATCH関数が登場します。
F5をクリックして、MATCH関数ダイアログボックスを表示します。
検査値には、$B5 複合参照にするのは、条件付き書式で使うからですね。
検査範囲には、休日一覧 これは、先ほど名前の定義をした休日一覧ですね。
照合の種類には、0 完全一致ですね。
OKボタンをクリックして、オートフィルで数式をコピーしましょう。
F5の数式は、
=MATCH($B5,休日一覧,0)
すると、数字が表示されている日が休日一覧にあるということになります。
1なら、休日一覧の1件目のデータと合致するというわけです。
#N/Aが表示されているところは、休日一覧に該当する日はないということなので、
祭日ではないということになるわけですね。
この数式をコピーして、条件付き書式に追加していきましょう。
新しい書式ルールには、
=MATCH($B5,休日一覧,0)>0
として、オレンジ色系のセルを塗りつぶしする書式を設定しましょう。
これで、祝祭日のセルの行に塗りつぶしの設定ができました。
あとは、用途に合わせて、テーブルにするなど、
綺麗にクリンナップしていただければOKですね。
※2017年 平成29年の祭日一覧です。※
休日
祝日
2017/1/1
元日
2017/1/2
振替休日
2017/1/9
成人の日
2017/2/11
建国記念の日
2017/3/20
春分の日
2017/4/29
昭和の日
2017/5/3
憲法記念日
2017/5/4
みどりの日
2017/5/5
こどもの日
2017/7/17
海の日
2017/8/11
山の日
2017/9/18
敬老の日
2017/9/23
秋分の日
2017/10/9
体育の日
2017/11/3
文化の日
2017/11/23
勤労感謝の日
2017/12/23
天皇誕生日
