こんな万年カレンダーを作るぞ！ということで後編です。前編を見てない方は前編からどうぞ。
さて、前編では表示する月に合わせた日付の計算をし、カレンダーに自動表示させました。
続いて、後編では祝日の自動表記と、条件によって日付数字の色分けがされるようにしていきます。
早速、祝日から取り掛かりましょう！
さて、この祝日なのですが、、、
残念ながら、計算で導き出すことはできません！
できないのか〜い！！（笑）
では、どうするのか？といいますと、、、、内閣府のホームページに「国民の祝日について」というページがあって、その中に、昭和３０年から１年後ぐらいまでの、祝日一覧データが入ったCSVファイルが公開されております。ご丁寧に、ダウンロード用のURLも掲載されているので、このデータをダウンロードして使いましょう！
いっそ、スクレイピングを試してみても、、、と思いましたが、私の知識とスキルでは、PHPでもあまりうまくできなかったので、、、やめます（笑）開発環境では成功したのに、サーバー上ではできなかったんですよね。。。
まあ、祝日データは年に１回ぐらいのメンテでしょうから、そこは手動で頑張りましょう！（笑）内閣府のページは、ネット検索で「祝日」と検索すれば一発ででてきます。
さて、ダウンロードしたCSVデータの扱い方、運用方法は各自お好きなようにしていただくとして、この記事では、カレンダーのファイル内に「祝日」という名前でシートを準備して、データを一切加工せずに、項目行も含めてそのままA1始まりでコピペして使います。
この準備したデータベースから祝日を引っ張り出していくわけですが、、、まず先に表示する場所を整備しましょう。
前編で作ったカレンダーに、祝日名を表示するための行を挿入します。日付の計算式が崩れてしまいますが、すぐに直しますので気にせずにバンバン行きましょう。なんなら２週目の式をオートフィルして直せる３週目以降は先に消してしまってもいいです。行の高さ、罫線などは好みで調整してください。
こんな感じに挿入しました。
それでは、日付の式を直していきましょう。
まず、関数が崩れた原因を考えます。
これは言うまでもなく「行の挿入」によって「行数」が変わったことが原因ですね。前編で行数を使って日付を計算した事を覚えていますか？「行が増えると×７」と説明していたところです。おそらく、このあたりを修正できれば式は直りそうですね。
改めて前編で使った図解を見てみます。
これです。=ROW()を使って得られる行数の戻り値から「ー３」をして、７の倍数の乗数の１〜５までを準備していました。
この結果が、行の挿入によってどのように変わったのか見てみましょう。
このように、各行に１行ずつ挿入されているので、計算結果が「ちょうど２倍」になってしまっています。これでは日付が崩れて当然ですね。。。さあ、困りました。どう対処しましょうか。。。。
なんちゃって（笑）ノリツッコミみたいな流れですが、、、もう気づきましたか？！
前編からずっとやってきてますが、解決するためには、「得たい結果と同じになる法則を見つけて式にする」です！
今回の法則は、とてもわかりやすいですね〜。キレイに２倍になっているだけです。ならば、−３したあとに２で割れば解決じゃないですか！（笑）
早速、式を修正していきましょう。
前編でやった縦横の計算を盛り込んだ式がこれです。
=$A$1-(WEEKDAY($A$1,1)-(ROW()-3)*7+COLUMN())このうちROW()-3を７で掛ける前に２で割ればいいわけです。
=$A$1-(WEEKDAY($A$1,1)-(ROW()-3)/2*7+COLUMN())この赤枠で囲んだ箇所を入れるだけです。日付計算をしているすべての式にこの箇所はありますので、残らず修正します。
前編の最後に式を配置しましたが、同じように基準となる式だけ修正し、あとはコピーすれば間違いないと思います。図にすると下のようになるかと思います。
双眼鏡マークの置換を使っても早いと思いますが、今回は、行の挿入の仕方によっては参照先がズレたり無くなっていることもありますので、基準の３つの式はしっかり参照先を確認しながら修正したほうが無難かと思います。
それでは次は祝日を引っ張り出してきます。
Excelで、データ一覧から該当データを引っ張り出す時、まず思いつく方法って、VLOOKUP、HLOOKUP関数ではないでしょうか？さらに進化したXLOOKUPというやつもあります。
でもですね、、、私個人的な意見ですが、データの検索はINDEX＋MATCHが最強なんじゃないかと思うんですよね〜。なので！ここではINDEX＋MATCHを使った検索方法でやります。とりあえずこれだけ覚えておけば大抵の検索はできます。使い方によってですが、ソートを使わずに並び替えたり、検索結果をリストアップしたりも可能です。このへんは過去記事で書いてます。よかったら見てください！下にリンク作ってあります。
- 過去記事リンク
- 関数だけで可変するリストを作ってみる
- 関数だけでランキング表をつくってみる
それでは、INDEX、MATCHの使い方です。過去にも何度も書いてますが、、、、ちょっと使い方によせて書いてみます。
まず、INDEXです。
=INDEX(データの範囲,行番号【縦軸】,列番号【横軸】)
これはデータの範囲で指定した表の、行と列を座標指定してやると、その場所のセル値が得られます。このデータ範囲の中で、行と列を別々に指定できる点が、広く使えるポイントです！
そしてMATCHです。
=MATCH(検査値,検索範囲【1列もしくは1行】,検索方法)
これは検索範囲の中で検索値が何番目にあるかを数値で教えてくれる関数です。検索範囲は行方向、列方向どちらでも構いませんが1行、もしくは1列でなければいけません。
そして、この検索方法は次の３パターンがありまして、
- 3パターン（順不同）
- 完全一致
- 範囲の中で検索値と同じか次に小さいもの（以下）
- 範囲の中で検索値と同じか次に大きいもの（以上）
となります。
使う時の注意としては、「以下」のときは昇順、「以上」のときは降順に並び替えておく必要があります。「完全一致」は順不同で構いません。
MATCHさんはデータ全体から「以上」、「以下」、「一致」を探しているわけではなく、データの初めから順番に見て、条件に一致した時点で、検索を終了する関数です。そのため、MATCHさんのために、探しやすく準備してあげないと、スネてしまってエラーになります（笑）こういうところ、人っぽくて好きです（笑）
この「以上」、「以下」は値が特定できなくても、検索値と比べて同じか、その次に小さいもの、もしくは、大きいものを探してくれるので、テストの合格判定や平均判定などに使われている例がよく見られます。
ですが、、、通常業務で運用するって考えると、データを検索するたびに並び替えって、、、ちょっと面倒ですよね。なので私は、あまり業務では使っていなかったです。
ただ、過去記事の「関数だけで24時間のシフト表をつくってみた」の中で、関数で並び替えしたデータに、MATCHの「以上」を使ってシフトデータをつなぎ、グリグリ動くシフト表を作りました。この時、検索した結果から、その次の値の検索をするという使い方は、やりたいことにピッタリでした。並び替えが自動化できれば、値が特定できなくても検索できる、というのはとても便利でしたね。（この詳しい説明はしてませんが（笑））
さて！この2つの関数を組み合わせて使うという話なのですが、INDEX関数の行と列を指定する箇所を、MATCH関数の検索結果にすることで、一覧表を検索してデータが抜き出せるという話になるわけです。
では、祝日一覧から該当祝日を検索して表示してみます。
データ一覧画像はいちいち載せませんが、このサンプルでは、「祝日シート」のA列に日付データ、B列に祝日名一覧があります。
それでは式の配置と解説は次のようになります。
ちょっと見にくくなってしまいましたかね。。。
式はこうなってます。
=INDEX(祝日!$A:$B,MATCH(A3,祝日$A:$A,0),2)使い方で注意するのは、INDEXで指定したデータエリアのデータ総数とMATCHで指定する検索エリアのデータ総数が一致していなくてはいけません。それぞれが別関数なので、INDEXもMATCHもそれぞれが自分の指定されたエリアの順番という認識だからです。シートの行と列番号は関係ないのです。
ここでは、どちらも列全体を選択しているので一致してますね。
さて、この式の結果ですが、、、実はエラーになっています。。。
これは、VLOOKUP関数なども同じなのですが、検索して見つからなかった時はエラーとして表示されるのです。そもそもExcelは計算シートですからね。見つからない＝エラーですわな。
しかし、これはカレンダーなので、この表示を回避します。IFERROR関数というエラーが出た時に処理を実行させる関数があります。これ便利で、よく使われる場面を見ますが、、、何かしらエラーが出たら、全部その処理になってしまうという困ったことになります。
「別にいいじゃん！」って思われるかもしれませんが、エラーの原因がわかっているときは、できる限り原因を潰しておくと、別の原因で出たエラーはちゃんと抽出できるようになるんですよ。
エラー表示は全部無くせば便利じゃないか？と思われるかもしれませんが、これだと意図しない不具合が、別にあったとき、何も気づかずに放置され動きます。人間ですから何か間違いはあるものなのですが、その人間ですから間違いがあっても見つけにくいんです。だからチェック機能はなるべく、極力、なんとか（笑）生かしておきましょうということです！ただ、わかっているエラーが２つも３つもあるときは関数式が長くなって編集できなくなるので、迷わず使っていいと思います。ただ、そういうときは、本当にその関数の使い方や方法があっているか考え直したほうがいいかもしれません。
はい！なので、ここでは原因を潰します。
見つからないということは、「データ一覧に同じ日付が無い」ということですね。では無いことを証明しましょう。
式はこうなります。
=IF(COUNTIF(祝日!$A:$A,A3)=0,"",INDEX(祝日!$A:$B,MATCH(A3,祝日$A:$A,0),2))COUNTIF関数を使って、祝日データ一覧の日付の中から、A3と同じ日付をカウントさせます。見つからないということは、カウント結果は「０」です。なので、「０」のときは空白を表示します。
これで先程の原因は回避できました！
と、、、これで終わりかと思いきや、実はもう1つありまして（笑）
それは日付が空白の時です！
つい先程、祝日が無い時は空白にしてました。今度も空白にします。このような、同じ結果になる条件が2通りあるというときは、複数条件を設定できるANDとかORが便利です。式はこうなります。
=IF(OR(COUNTIF(祝日!$A:$A,A3)=0,A3=""),"",INDEX(祝日!$A:$B,MATCH(A3,祝日$A:$A,0),2)))う〜ん、長いですな（笑）
「いやいや、、、もう長すぎて無理！（笑）」って方は、IFERRORでも問題ありません！そんな方は、
=IFERROR(INDEX(祝日!$A:$B,MATCH(A3,祝日$A:$A,0),2),"")短いですね（笑）ただ、エラーの考え方だけ知っておいてください。不具合の抜け道は塞いでおくことをおすすめします。
さあ、これで祝日表示ができました！いつも長い記事書いちゃうな。。。これじゃ誰も読まないだろうな（笑）
はい、次は条件付き表示でカレンダーの色を指定して終わります！
セルA3を選択してから、下のようにウインドウを開きます。
これはセルの値や式の結果によって表示形式を変えることができるところです。ウインドウに初めからある、組み込みのものは今回使いません。上のように「新しいルール設定」を選択してください。そのあと、下のようにします。
Excel全般に言えますが、ウインドウに式を入力するのはとてもやりにくいです。入力補助機能が働くのでしょうが、入力を間違えたと思って、修正のためにカーソルキーを使って前後に移動させようとすると、セルが勝手に動いて、「クソッ！！」って叫びたくなります（笑）どこか関係ないセルに先に入力して、コピペしたほうがいいです。
さて、祝日のときにフォントを赤色にするための条件式は、
=COUNTIF(祝日!$A:$A,A3)>0これになります。さっきのエラー回避のために追加した式と演算子が違うだけのものですね。今度は祝日である事を証明するので、カウントしたら1になるものを判定します。なので「＝１」としてもよいですが、なんか２になることもあるかもしれない。。。ということで、ここは「＞０」としてます。また、もっと簡単に判断する式がありまして、
=A4<>""です。これは祝日名が表示されているかどうかで判断しています。すでに祝日判定を祝日名欄でやっていますので、それを有効利用しよう！ということですね。
この条件付き書式の式判定で気をつける事は、判定式の前にイコールが必要であるということ、あと、直入力せずに選択入力をするとセルが勝手に絶対参照になるので、コピーのときに動かしたい相対参照のセルについては＄記号を外すことです。また、ちょっと条件付き書式の数式は動作に癖が強く、式が複雑だと相対参照がうまく効かないことがあります。なるべく簡単な式で判定することをおすすめします。なので今回なら祝日名があるか？の方が使いやすいと思います。
さて、このままセルA3に条件付き書式を２つ続けて設定します。それは、日付が表示月の前月の場合と翌月の場合は、グレーにするという条件をつけます。まず前月の場合を付け加えます。A3セルを選択したままでまた「新しいルール設定」を選んでください。
そのあとは、下のようにします。
これは簡単ですね。月初より小さいものは前月です。
ウインドウで$A$1を入力する時は、セル自体を選択してもできます。その際は、勝手に絶対参照になります。
さて、翌月もこのまま設定します。また同じくA3を選択したままで「新しいルール設定」にします。
先程の「次の値より小さい」を選んだところで「次の値以上」を選びます。
そして、「＝＄A$3」としたところに、次の式を入れます。
=DATE(YEAR($A$1),MONTH($A$1)+1,1)こうですね。これは前編で使った式で、翌月月初を表します。翌月月初１日（ついたち）より大きいものは当然翌月ですね。
これで条件付き書式のルールが次のように並んだと思います。
実はこの並びに優先順位がありまして、上優先つまり、後で入力したものが優先されます。なのでこの並びですと、グレーが優先されるので、先月と翌月に祝日があっても日付はグレーになります。
それでは、このままA3から横方向に書式のみコピー。そのあと、下方向に書式のみコピーで全体に反映させます。
最後に、土曜日の日付は青色、日曜日は赤色にフォント設定します。これをしても条件付き書式が優先されますので、表示月から外れた時のグレー、祝日のときの赤が優先されます。
試しにセルA1に「5/1」と入力してみてください
ちゃんと反映されましたか？
さあ！これで完成です。もし条件付き書式がうまくいかないときはルールの修正で修正してみてください。ただ、相対参照の数式はうまくいかないときがあります。そのときは最初からやり直したほうが早いです。まあ、、、結局、うまくいくかどうかも含めて、慣れです（笑）たくさん失敗しましょう（笑）
これを書いていて、業務カレンダーのような日付固定バージョンや曜日別売上集計のような使い方もまとめてみたくなりました。そんなわけで、次は万年カレンダー（日付固定）と関数の業務転用なお題で書きます！長々とお付き合いありがとうございました！
