仕事の中にExcelがどっぷり浸透している割に、日付がテキストとして扱われていることが多いな、、、と感じる今日このごろ。
ああ、、、もったいないなぁ。。。。
日付はデータとして扱ったほうが、得られる情報が多くなりますし、計算もできるようになりますので、できることが増えます。
さらに！日付をデータとして扱う人が増えれば、社会全体の業務書類の汎用性は爆上がり間違いなし！！（ホントか？！（笑））
この記事を読んだら、ぜひ！日付はデータとして扱ってほしいです！
さて、日付の取り扱いを説明するなら、カレンダーがてっとり早い！
ということで、下のような万年カレンダーを関数と表示形式を使って作りながら、日付の扱い方を説明していきます。
日付は関数で計算、祝日はデータ一覧から関数で抽出。表示する色は条件付き書式で表現してます。
では、表示形式の説明から始めます。実は、これだけでかなり便利になります。
表示形式は知ってるので飛ばして読みたい方は
↓ここをクリック！↓
☆関数で日付計算するへ飛ぶ☆
この表示形式は、数字にカンマを振ったり、通貨単位を入れたりとよく使うと思いますが、日付も便利に使えるんです。今回説明するのは「その他の表示形式」です。具体的にどこの事を言っているのかといいますと、、、
これですね。
ここを選択すると、セルの書式設定というウインドウが開きます。フォントや罫線などを設定するところですね。
この表示形式を設定したいセルには、あらかじめ値を入れておくことをおすすめします。親切なExcelさんは、それにあった表示形式をコーディネートしてくれます。まあ、、、気にいるかどうかは別ですが（笑）。
その上で、今回は「日付」なので、先に、分類の「日付」を選択し、標準的な日付表示を選んでおいてから、「ユーザー定義」を選びます。
この「ユーザー定義」のウインドウの説明文に、
「基になる組み込み表示形式を選択し、新しい表示形式を入力してください。」
とあります。このとおり、新しい表示形式を自分で作れるんです！これ、結構自由度が高いです。
そして、「組み込み表示形式」というのが、ウインドウ内の分類リストの中にある数値や日付などになります。この組み込み形式には決まり事があり、数値を表す記号は＃（シャープ）や０（ゼロ）だったり、日付の年はyyyyだったり、gggeだったりします。
「こんなのいちいち覚えてられっか！」
うんうん。全くだ（笑）。
・・・というわけなので、組み込み形式を覚えたり、自分で探したりしなくてもいいように、セルへ値を先に入れておいたり、先に日付表示を選んでおいたりするわけです。こうすることで基の組み込み形式や表示サンプルが表示されるので、そのままベースとして使ったり、表示サンプルをみながら編集したりと便利です。
ここで、万年カレンダー作成から少し脱線しますが、この表示形式でどんなことができるのかを少し書いておきます。
まず、上のスクショでは「yyyy/m/d」が選んであって、サンプルに「2023/3/1」と表示されています。組み込みの法則としては、yyyy が年、mが月、dが日付を表しているのがわかると思います。このアルファベットの並びを使って自由に設定できるわけです。
あと、PCって口下手なんで、代わりに書いておこうと思うんですが、、、PCさん、実はこの日付が何曜日か知っています。。。
じゃあ、教えてもらいましょう!
次のように「yyyy/m/d」と表示されているボックスを選択して、dのあとに続いて「aaa」と入力してみてください。
ほら！サンプルに曜日が現れましたね！
先に言ってよ〜って思いませんでした？まあ、WEEKDAY関数とかあるんだから当然って言えば当然なんですが、私は教えてもらうまで気づきませんでしたよ。
さらに！先程の「aaa」に続けて「a」を足し、４つ続けると、
ご丁寧に「曜日」が付きました！
では、このアルファベットの組み合わせを使っていくつか作ってみましょう。結果がサンプルに表示されるので確認しながらできるので便利です。
まずはオーソドックスに曜日をカッコでくくりたいですね。aaaをカッコでくくればいいです。
ほら簡単。
もう少し情報量を増やして、月と日付は２桁揃え、時間も一緒に表示できるようにしてみます。
このように簡単に表示を自分で変えることができます。
そして、情報を増やすことばかりではありません。逆に隠すこともできます。
例えば表のタイトルを「2023年３月 売上集計表」だとしましょう。そういうときは、こんな感じで、
どうでしょうか？データの一部が隠れてタイトルになってます。
これ、便利なところは、ごちゃごちゃと表示があっても、値は「日付データ」であるという点です。例えば、タイトルを３月から４月に変える時、テキストの場合、セルを選択して、３月の「３」に合わせて「４」を上書きして変えますが、表示だけ変えてある場合は、セルの日付を変えるだけなので、テキストは気にせずに、セルに直接「4/1」と入力すれば４月に変わります。その上、日付ですから関数計算も可能です。数字のカンマや通貨単位の 表示があっても計算ができるのと同じです。便利ですよね〜。
今回、このカレンダー日付は表示形式を「d」だけにしてます。
さて、万年カレンダーに戻ります。
まずは、説明しやすいように日付だけで作ります。祝日はその後で追加しますね。
下のようなカレンダー表を準備します。
もう表示形式はわかりますね？上の図のような組み込み表示形式がなければ自分で作りましょう！
このカレンダーの日付は、表示月がある週は前後の月の日付も表示することにします。よくあるカレンダーと同じです。
こうやって書くと難しそうに思えますが、実はとても単純で簡単です。日付データ用に特別な計算があるわけでもなく、１日経過させたいなら＋１、戻したいなら−１するだけです。年とか月は考えなくても日数がズレれば一緒についてきます。なので、考え方としては、月初が何曜日で、そこから各日付のマスは何日ズレているのか？を表現すればカレンダー完成なわけです。
では、まずは月初めが何曜日か？を調べましょう。先程文中で出てきましたWEEKDAY関数使います。
WEEKDAY関数は調べたい日付の曜日が指定曜日から数えて何日目か？を教えてくれる関数です。
使い方は、
=WEEKDAY(調べたい日付データ,数え始める曜日の指定)
です。
ここでは調べたい日付データはセルA1に入っているので「A1」、数え始める曜日はカレンダーが日曜日始まりなので「1」になります。
この数え始める曜日は、当然７種類あり、それぞれに対応した数字がありますが、説明はカット！（笑）
WEEKDAY関数使うときにちゃんとExcelに説明がでますのでご安心を！！
そして、この調べたい日付のセルA1はカレンダーの基準としますので、絶対参照（フィルで動かさない）にしておきます。式にすると
=WEEKDAY($A$1,1)
です。
さて、この関数から得られる結果（以後、「戻り値」と呼びます）は、月初が日曜日なら１、月曜なら２、火曜なら３・・・と、曜日が日曜日から離れていくにつれて１ずつ増えていきます。
そして、カレンダーの一番初めの左上のマス（日曜日）に入る日付を考えてみます。セルA1の月初から何日ズレているか？を表現できればいいいわけですから、月初が日曜ならセルA1から０日のズレ、月曜なら−１日、火曜なら−２日・・・と１日ずつズレが増えていけばいいですね。
どうでしょうか？？この２つ、共通点が見えませんか？
そうです。どちらも１つずつ数字が増えますね。しかも、戻り値から−１して、月初から引き算すれば、思っている結果と一緒になります！
では、これを式に表してみると、
=$A$1-(WEEKDAY($A$1,1)-1)
とできます。
同じように他の曜日を考えてみます。
月曜のマスに入る条件は、月初が日曜なら＋１日、月曜なら０日、火曜なら−１日、、、となります。おっと、プラスが出てきてしまいました。
でも、日曜マスと同じように月曜マスの場合も１つずつ数字が増えてますね。戻り値から考えてみましょう。わかりやすい月初が月曜の時、戻り値は「２」でした。これをズレなし、つまり「０」にするわけですから、「ー２」すれば解決です。さて、日曜日の戻り値は「１」です。「ー２」すると結果は「−１」となります。これを月初から引く。。。。マイナスの引き算ということはプラス！！
もう文で書いてもわけわからん！（笑）
なので、このまますべての曜日を下の表にまとめますと、、、
説明している私が言うことではないのですが、、、、はたして、これはうまくまとまったのだろうか（笑）この表で伝わるのでしょうか？！（笑）
結局どうするの？って聞こえてきそうなので、式の配置はどうすればいいのかをダイレクトに図にすると、
とまあ、日曜日は−１、月曜日は−２・・・・と戻り値から引く数字が１つずつ増えていけばいいってことです。
では、全部のセルの式を手入力していく・・・・。まあ、１つ書いてそれをコピペしてからマイナスの数値だけ変えれば良さそうですが、１つずつ増えていくという法則があるものですから、できればオートフィルで完結したいですよね？
ここは、もうちょっと考えましょう！
「列が右に１つずつズレていくごとに１ずつ数値を増やす方法」。
はい！もうこれ答えです。「列が１つずつズレる」です。
自身のセルの列の位置を得る方法、COLUMN関数を使いましょう。セルに=COLUMN()と入れると、左から数えた列数を数値で返してくれる変数です。一番左は「１」になります。
なので、さっきまで頑張って説明していた戻り値からマイナスする箇所を
=$A$1-(WEEKDAY($A$1,1)-COLUMN())
このようにすれば１行目はすべて解決します。
やっと１行か、、、、やってることは単純、説明すると結構長い（笑）
さて、２行目以降です。
「え〜、まだやるの〜」って聞こえないでもない（笑）
はい、安心してください！！
すでに１行目の日付が決まっているということは、そこからどれだけズラしていくのかを考えればいいだけです。行が下にいくごとに１週間、つまり７日ずつズレが増えるだけですね！
図にまとめると、
こういうことですね。
では、１行増えるごとに７の倍数で増える数値を考えましょう。
さっきは「列が増えると+1」でした。今度は「行が増えると×７」です。
列ではCOLUMN関数を使いました。行にも同じことができるROW関数というものがあります。=ROW()と入力すると、一番上の１行目を「１」として、関数がある行が何行目か？を返してくる関数です。
では、この戻り値を使って、先程の得たい結果の図と合わせて考えてると、
こうなります。
これを式にすると、
=(ROW()-3)*7
こうなりますね。
この式が先程の-COLUMN()に足されれば解決します。合体しましょう。
=$A$1-(WEEKDAY($A$1,1)-(ROW()-3)*7+COLUMN())
これで２行目以降は解決しました!
そして、お気づきの方もいらっしゃるかもしれませんが、実はこの式、１週目に入れても大丈夫です。
どういうことかといいますと、１週目は３行目ですから戻り値は「３」です。ということは、「３−３＝０」ですから、「７×０＝０」となります。１週目は７倍は無効になってますね。
なので、日付表示はこの式１つで完成！ということになります。
ふ〜、長かった（笑）
しか〜し！！このままですと表示月をすぎて月が変わっても関数が入っているマスは日付が出続けてしまいます。
では、日付を表示しない状態を考えてみましょう。表示ルールとして、「日曜日始まりのカレンダーで、表示月がその週に１日でもあれば月が変わっても日付を表示させる」としてました。ということは、表示する週のはじめ、日曜日が翌月になったら、その日以降は全部翌月になります。逆に日曜日が表示月の場合、週の途中で月が変わっても日付を表示し続けなければならないわけです。なので、表示するかどうかの判定は、日曜日だけ見ればいいことがわかりますね。
早速判定を入れていきましょう。
さて、この判定方法ですが、２つ紹介します。実際にお題のようなカレンダーを作るだけなら１つでよいのですが、実際に日付を扱う場合、どうしても月末を扱うことがでてきます。例えば同じカレンダーでも、月末で表示を止めるとかですね。業務カレンダーや勤務表なんかは前後の月はでてないことのほうが多いんじゃないですかね。というわけで！まだまだお付き合いください（笑）
それでは判定方法１つ目。
各週の一番左、日曜日の日付の「月」が、セルA1の基準日の「月」と一緒かどうかで判断します。
どうするのかといいますと、先程の日付計算の式の結果と、セルA１の値、それぞれからMONTH関数で月を抜き出し比べて判定します。MONTH関数の使い方はとても簡単で、
=MONTH(調べたい日付データ)
と日付をMONTHでくくるだけで１から１２までの数値で「月」を教えてくれます。
早速式を紹介といきたいところですが、ちょっと式が長くなるので、説明用に、先程の日付計算式を「日付計算」と置き換えて書いてみます。
=IF(MONTH(「日付計算」)<>MONTH($A$1),"",「日付計算」)
と、こうなります。実際に式に書くと
=IF(MONTH($A$1-(WEEKDAY($A$1,1)-(ROW()-3)*7+COLUMN()))<>MONTH($A$1),"",$A$1-(WEEKDAY($A$1,1)-(ROW()-3)*7+COLUMN()))
長いですね〜。しかも同じ日付計算の式が２回あるのでスマートではない気がしますね。まあ、好みでしょうが、、、、。式に名前つけて整理してもいいですし、このへんはおまかせします（笑）
それでは、もう一つの判定方法です。
この方法は、日曜日の直前の日付、土曜日が月末以上なのか？で判定します。つまり、土曜日が月末以上であるなら、その次の日曜日は翌月以降なので表示しないという理屈です。
では、月末を求めましょう。
突然ですが、「西向く侍」ってご存知ですか？（笑）
ははは、、、まあ、別の話なので、詳しく書きませんが、これは、月の日数が３１日ではない月を覚えるための、数字の語呂合わせです。このような語呂合わせが必要なぐらい各月の日数は揃っていません。その上、４年に一度、しかも２月だけ１日増えちゃいます（笑）
じゃあ、月末日付を抽出できる関数があるのか？と言えば、残念ながらMONTHやDAY関数のような月末日付をバシッ！と求める関数はありません。
お手上げのように思えますが、、、実は、月末を導き出すために、どの月でも絶対に変わらない基準があります。
それは、、、月初め、１日（ついたち）です！
えっ！？なんで？？と、思っちゃいますか？でも、月初めの前日は必ず「月末」ですよね。。。こうなるともう、なぞなぞみたいですね（笑）
では、早速、表示月の月末を出してみましょう。
数値を日付に変換するDATE関数使います。使い方は、
=DATE(年の数値、月の数値、日付の数値)
です。
このように数字で指定してやると日付データで戻り値として返してくれます。今回は、表示月つまりセルA1に入っている日付の月末を調べればよいわけです。
セルA1は日付データになってしまってますので、DATE関数にそのまま入れることはできず、年と月の日付を数値として抜き出せねばなりません。
でも、大丈夫！これは簡単です。YEAR、MONTH、DAY関数で解決できます。YEAR、DAY関数は初めて出てきましたが、使い方はMONTH関数と一緒です。
まずは来月の月初を表します。数式にすると
=DATE(YEAR($A$1),MONTH($A$1)+1,1)
これで来月初めです。来月なので月だけ＋１してます。日付は抜き出さなくても必ず月初にするので「１」ですね。
月に＋１だけだと表示月が１２月だと１３月ってなっちゃうし、年が変わる対応できないんじゃない？？と思われがちですが、DATE関数は結構融通が効きまして、月や日付を計算で進ませると、それに対応して日付として経過したものとしてくれます。これは逆もOKです。なので、実際ではありえない日付になっても、理屈があっていれば判断してくれるのです。
話がそれたようになっちゃいましたが、何が言いたいかといいますと、、、この「月末の日付を出す」を正攻法で行うとすると、上の式の戻り値から−１すれば解決です。でも、、、実は、下の式のように
=DATE(YEAR($A$1),MONTH($A$1)+1,0)
「翌月の０日」というありえない日付を設定すると、、、、これが「月末」になります。１日よりまえの０日です（笑）
まあ！便利なこと。
先程、「月末を出す関数は無い」と言いましたが、これが実質の月末を求める関数だと私は勝手に思ってます。
月末の日付だけ知りたいならこの式をDAY関数でくくればOKです。これ、結構使えますので業務で使ってみてください。
えっ？「何に使うか？」ですって？！例えば月の稼働割合を出す時なんか、毎月手入力で月の日数とか入れてないですか（笑）毎月１回だからいいやとか（笑）
私は以前、月間売上から１日あたりの売上を出す時に使ってましたよ（笑）人件費とか（笑）ね、結構使う機会ありますよ！
それではこれを使って２週目の日曜日に判定式を入れると、
=IF(G3>=DATE(YEAR($A$1),MONTH($A$1)+1,0),"",$A$1-(WEEKDAY($A$1,1)-(ROW()-3)*7+COLUMN()))
となります。
ですが、このままですとカレンダーの日付が２週（２行）空欄になるような場合、参照している土曜日のセルの値が空白になり、月末以上ではなくなるので、２行目だけ日付表示が復活してしまいます。まあ、カレンダーの表示が４週で止まることがほとんど無いので、このままでもいい気はしますが、運悪く２月が日曜始まりの２８日おわりだと、可能性が無いわけではありません。なので、参照セルが空白だったときも表示しないようにガードします。
IF文のような条件分岐で複数条件をつけるとき、とても便利なOR、ANDがあります。ORは条件のうち、どれかが一致する場合、ANDは条件がすべて一致する場合です。IF文を複数組み合わせてもいいのですが、今回の条件は参照セルが「月末以上か空白かのどちらかの場合」という２つの条件で、わかりやすいものですのでORを使います。
=IF(OR(G3="",G3>=DATE(YEAR($A$1),MONTH($A$1)+1,0)),"",$A$1-(WEEKDAY($A$1,1)-(ROW()-3)*7+COLUMN()))
となりました。これでガードまでかけれたので大丈夫ですね。
先程の表示月を比べる方法と比べて、ガードまでかけたとしても、式自体が短くなり、少しスマートになりました。
私もこちらの月末判定の方をよく使います。むしろこちらしか使わないぐらいです。ですが、、、作成するもの、表示させる条件によっては、全く使えないこともあります。両方頭にいれておくとよいかと思います。
これで日曜日の判定はできました。あとは月曜から土曜だけです。
これはもう簡単ですね。その週の日曜日が空白かどうかで表示するかどうかを判定すればよいだけです。
２行目の月曜日の式に
IF($A4="","",$A$1-(WEEKDAY($A$1,1)-(ROW()-3)*7+COLUMN()))
と入れて、あとは横方向に土曜までオートフィル。そのまま下方向にオートフィルでOKです。日曜日が空白かどうかをどの曜日も見ればいいのでセルA4は列固定の参照にしてます。ただ、１週目も同じ式で問題なく動きますので、メンテナンスのことを考えると別の式をいれるより１週目から全部同じ方がわかりやすいでしょう。
それでは、ここまでのまとめとして、カレンダーの各マスにどの式を配置すればいいのかを図にしておきます。
式自体が短いので日曜日の判定は月末を使った方になっています。
これで日付の表示は完成しました。次は、、、と行きたいとことですが、、、
はてなブログは文字数が増えてくると編集時の動きが急に鈍くなります。。。。ブラウザのせいだろうか？
すでに現時点でとても編集しにくくなってきたので、残念ながら祝日と条件付き書式での日付の色変更は後編にします。。。ワンストップで全部書きたかったな。。。。無駄話が多いからだろうな（笑）
というわけで後編に続きます。
hamo440.hatenablog.com
