【Excel】明日から使える「日付・曜日入力」の自動化
給食管理の帳票は決してソフトだけでは完結しないので、多かれ少なかれExcelに頼っている職場がほとんどであると思います。前回はVBAについて栄養士向けにnoteを書きました。
日付の書き換えは自動化するべき
毎日、毎週、毎月必ず必要になる帳票類の場合、確実に変化していくのが日付であり曜日です。手動で変更することは容易に行えるし、たいして時間もかからないのだから、なにを学ぶことが？と思われる人もいるかもしれない。
でも、少しでも楽をしたいなら、学ぶことはたくさんある。
例えば、ワイの失敗談でいうと請求書の日付表示がそう。給食ソフトで対応できない請求書はExcelで作っていたのだけど、発注日というのは印刷かけてFAXで送るだけなので、基本的にはその当日を表示してほしいわけです。
以前は頻繁に送るものでもないし、その場で入力し直せばいいと思考停止していました。でも身を削ってwordやExcelを学んでみたら、ものすごく簡単に常に当日表示することができることがわかった。この１年間なんて無駄な作業していたんだと考えてしまって、その時の感動はたぶん実際に動かせた人にしかわからないものだと思います。
Excelでは関数を使えば日時を常に自動的に表示することが可能です。大体思い描いたことはなんだってできます。自動化はじめの一歩です！
今日の日付を表示する
＝TODAY()
TODAY関数と呼ばれており、当日の日付を表示してくれます。個人的には栄養士・管理栄養士がまず使う関数として挙げるとしたら、合計値を出すSUM関数よりもTODAY関数だと思います。下記の「翌日の日付を表示する」と合わせて帳票類の自動化には間違いなく役立ちます。
ちなみに、日付に関しては以前こんなnoteも書きました
明日の日付を表示する
＝TODAY()+1
TODAY関数に＋１とすると、翌日の日付が表示されます。またー１とすると昨日の日付となります。数値を増減させれば、その値分だけ日付が前後にずれるというわけです。このあたりは難しさもなく理解できると思いますが、実務ではめちゃくちゃ使います。
・発注日や請求日はほぼ当日の日付表示になるので =TODAY()
・翌日納品指定の場合は納品日として =TODAY()+1
・発注日から2日後の納品指定の場合は =TODAY()+2
どうでしょう、普段入力している作業もTODAY関数を使うと、ほぼ自動化できます。もちろん、祝日や休日を挟んだ場合の対応などを考えるのであれば、もう少し工夫は必要になるかもしれません。ただ、それを抜きにして考えたら、これほど楽に明日から実行に移せる自動化というのもないと思います。
来週の月曜日以降を表示する
次週月曜 =CEILING(TODAY()+6,7)-5
次週火曜 =CEILING(TODAY()+5,7)-4
次週水曜 =CEILING(TODAY()+4,7)-3
次週木曜 =CEILING(TODAY()+3,7)-2
次週金曜 =CEILING(TODAY()+2,7)-1
次週土曜 =CEILING(TODAY()＋1,7)
次週日曜 =CEILING(TODAY(),7)＋1
TODAY関数はExcelを開いた当日が反映されるので、たとえば翌週の月曜日から一週間分の日付入力を自動化して印刷をかけたい場合は、特定の曜日に作業を行わなければなりません。
もし木曜日に、次週の月曜日から一週間分の日付入力を自動化させたいと思ったら、
＝TODAY()+4
として、それ以降5,6,7,8...と増やす必要があります。ただし、これは木曜日限定の書き方になります。もし間に合わなくて金曜日に出そうと思ったら１日ずれてしまうのです。
だから、どの曜日であっても翌週の月曜日からの入力を自動化できるのが望ましいわけです。その方法がCEILING関数とTODAY関数を合わせた方法です。そうなんです、関数は組み合わせて使うことができるんです。
翌週月曜日の指定は
次週月曜 =CEILING(TODAY()+6,7)-5
となり、他の曜日に関しては上記の通りです。これで何曜日であっても翌週月曜日が指定できます。
CEILINGというのは天井、上限といった意味の英語です。よくシーリングライトとかシーリングファンとかいいますよね。そのシーリングなんです！CEILING関数の機能としては、基準値を倍数として切り上げた数値を返すというものです。
CEILING(数値, 基準値)
たとえば、CEILING(6,5)なら、6を5の倍数で切り上げるので10が返ってきます。ちなみに逆に切り下げる関数はFLOOR関数です。つまり、数値＝自分の目線から見上げる（CEILING）のか、見下ろす（FLOOR）なのかということなんです！
ちなみに曜日に関しては、もう少し難しい話になります。このあたりはわえなび（@waenavi_jp）さんのサイトがとても丁寧でわかりやすいです。決してTODAY関数のように直感的に理解できるわけではありませんが、じっくりと考えれば次第にわかるようになってくると思います。
たぶん、はじめはまず使ってみるのが大事です。そして、使えることがわかったら、改めて理解する。そして自分の頭で考えて再現できるようにするのが大事なんだと思います。
来月の1日からの月末までの日付を表示する
=DATE(YEAR（TODAY()),MONTH(TODAY())+1,1)
とDATE関数を使うと、今日（TODAY）の次の月（MONTH）の1日が表示されます。ここでは、年月日を指定するためにYEAR関数、MONTH関数、そして起点となるTODAY関数を使っています。
ちなみに月は最小で28日なので、末尾の1を28まで揃えることで対応はできますが、29日から31日はこれでは対応できません。
そこでIF関数をつかって、このようにしてみます。
=IF(DATE(YEAR(TODAY()),MONTH(TODAY())+1,N)
<=EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),0),
DATE(YEAR(TODAY()),MONTH(TODAY())+1,N),"")
IF関数は条件に合致するかしないか、返す値を変えられる関数です。ここでは来月ある日が、来月の月末の日付以下であればその日を表示し、月末を超えていれば表示しないというものとなっています。
ちょっと見づらいのが難点！
ある日＝DATE(YEAR(TODAY()),MONTH(TODAY())+1,N)
月末を返す関数はEOMONTH関数になるので、次のようになっています。
ある日の月末＝EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),0)
#EOMONT (開始日、月）
#月が０の場合はその開始日の月
として比較しています。このNを、たとえば隣の列に連番を入れておいて、そのセルを参照するようにすれば、このような形になります。
Excelは賢い子なので、IF文で条件付しないと32以降は1月1日が表示されるようになってしまうのですが、うまくいっています！ここでのは載せませんが、NをROW関数で代用するなどいくつか方法はあります。
＋α 曜日を表示する書式設定を行う
日付に関してはこれまでの関数で指定できるのですが、曜日に関数ではな、セルの書式設定を行います。日付さえ決まれば曜日は決定されるからですね。あとは表示の仕方の問題であるというだけになります。
ユーザー定義関数で上記のように（aaa）を追記すると（月）といった形で日付に加えられて表示されるようになります。
ちなみに
aaa： 曜日の頭文字を表示（ex. 月）
aaaa： 曜日という文字を含めた表示の仕方（ex. 月曜日）
といった形になります。括弧などは自由につけられるので、ぜひ色々試してみてください！
まとめ 書き方一覧
今日 ＝TODAY()
翌日 ＝TODAY()+1
次週月曜 =CEILING(TODAY()+6,7)-5
次週火曜 =CEILING(TODAY()+5,7)-4
次週水曜 =CEILING(TODAY()+4,7)-3
次週木曜 =CEILING(TODAY()+3,7)-2
次週金曜 =CEILING(TODAY()+2,7)-1
次週土曜 =CEILING(TODAY()＋1,7)
次週日曜 =CEILING(TODAY(),7)＋1
翌月の月末まで
=IF(DATE(YEAR(TODAY()),MONTH(TODAY())+1,N)
<=EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),0),
DATE(YEAR(TODAY()),MONTH(TODAY())+1,N),"")
少しでもお役に立てたなら嬉しいです！
Excelをもっと学びたいという方にはこちらのnoteも時間があれば読んでみてください。学習期間10ヶ月程度ですが、初心者なりに勉強になった本を取り上げています。
いつもありがとうございます。これからも役に立つnoteにしていきます。