記事一覧表示

Excelだけでカレンダーを作りたい

MicrosoftExcel

概要

バイトで事務員作業をやらされた時に、年間カレンダーを書かされたんですが、使い古しのExcelファイルに一日毎に手打ちで入力させられてて馬鹿馬鹿しくなったので、生成自動化を検討してみた。

やった事一覧

  • 日付に曜日（カレンダーの横軸）を設定
  • 日付にカレンダーの行数（カレンダーの縦軸）を設定
  • 日付に祝日を設定
  • カレンダー作成


作業履歴

- 日付に曜日（カレンダーの横軸）を設定

　TEXT関数を使うと、入力された日付データを曜日に変換してくれるので、以下のように書いてみた。

f:id:ooutimatuki:20190124144052p:plain

　引数を日本語に書き直すとこんな感じ。

=IFERROR(TEXT(DATEVALUE(作成するカレンダーの西暦が入ったセル&”/“&月が入ったセル”/“&日が入ったセル), "aaa"), "")

　DATEVALUE関数で 西暦/月/日 と言う文字列を日付データに変換し、TEXT関数にオプション aaa 1 と一緒に渡す事で曜日を出力している。IFERROR関数は入力した日付が存在しない所を空欄にしている。と言うのも、1〜12月のそれぞれの月の最後の日にちがバラバラだったので、一番長い31日までの日付を入力しており、存在しない日付は空欄にしたかったからである。日付の最後の方の曜日は以下の通り。

f:id:ooutimatuki:20190124144118p:plain




- 日付にカレンダーの行数（カレンダーの縦軸）を設定

　カレンダーにおいて、日付を表示する行が変わるのは日曜日なので、出力する日付以前の日付の曜日の日曜日を数えて、それを行数にした。関数は以下の通り。

f:id:ooutimatuki:20190124154624p:plain

　引数を日本語に書き直すとこんな感じ。

=IF(COUNTBLANK(出力する日付の曜日が入っているセル), "", COUNTIF(月の初めから出力する日付までの曜日が入っているセル群, "日"))

　月の初めからその日付までに日曜日が何回入っているかをCOUNTIF関数で数えている。また、COUNTBLANK関数で日付に曜日が入っているかを確認し、その日付が実際に存在しているかを調べている。




- 日付に祝日を設定

　調べてみると、数年間(2017〜2036年)の日本の祝日をExcelとして保存しているサイトを発見した。このサイトから祝日データをExcelに貼り付けた Holiday シートを作成し、そこから祝日を表示する事にする。コードは以下のように書いてみた。

f:id:ooutimatuki:20190127065829p:plain

　引数を日本語に書き直すと以下の通り。

=IFERROR(VLOOKUP(DATEVALUE(作成するカレンダーの西暦が入ったセル&”/“&月が入ったセル”/“&日が入ったセル), Holiday!祝日の日付と名前が書かれている範囲, 範囲内の祝日の名前が書かれている列の左からの列数, 0), "")

　VLOOKUP関数を使って、指定した日付の祝日の名前をHolidayシートから出力している。もし、指定した日付がHolidayシートに無かった場合、IFERROR関数を使って、何も出力しないようにしている。




- カレンダー作成

　まず、ここまで作った曜日、カレンダーの行数、祝日を以下のように月毎に縦方向に並べた。

f:id:ooutimatuki:20190127083623p:plain

　次に、縦方向に並べたデータから、カレンダーの形になるように数字を振るため、以下のようなコードを書いた。

f:id:ooutimatuki:20190127083639p:plain

　引数を日本語に書き直すと以下の通り。

=IFERROR(MATCH(MIN(その月のカレンダーの行数が入った全てのセル)+日付を配置する行の左端のカレンダーの行数が入ったセル&日付を配置する列の一番上の曜日が入ったセル, その月のカレンダーの行数が入った全てのセル&その月の曜日が入った全てのセル, 0), "")

　MATCH関数を使って、カレンダー上の行数と曜日の数字の組み合わせが、先ほど作成した縦のデータ表の上から何行目にあるか（要するに日付）を出力している。MIN関数は、月毎にカレンダーの行数の初めの値が違う為（月の初日が日曜日の場合は1、違う場合は0）、カレンダーの一行目の行数を月毎に合わせている。また、MATCH関数の引数で配列同士の計算（比較）を行っているので、式を記述し終わった時にCtrl + Shift + Enterを押す必要がある2。
　次に、祝日と日曜日の日付は休みを示すために背景と文字を赤色に塗った。コードは以下の通り。

f:id:ooutimatuki:20190127083659p:plain

　引数を日本語に書き直すと以下の通り。

=OR(日付を配置する列の一番上の曜日が入ったセル="日", COUNTBLANK(INDEX(その月の祝日の名前が入った全てのセル, MATCH(MIN(その月のカレンダーの行数が入った全てのセル)+日付を配置する行の左端のカレンダーの行数が入ったセル&日付を配置する列の一番上の曜日が入ったセル, その月のカレンダーの行数が入った全てのセル&その月の曜日が入った全てのセル, 0)))=0)

　MATCH関数を使って、カレンダー上の行数と曜日の数字の組み合わせが、先ほど作成した縦のデータ表の上から何行目にあるか（要するに日付）をINDEX関数に渡し、日付の祝日の名前を取ってきている。それをCOUNTBLANK関数で空白かどうかを判定し、空白ではなかった場合（空白のセルが0個の場合）に何らかの祝日があるとして、そのセルの背景と文字を赤く塗っている。


分かった知識一覧

  • DATEVALUE(日付を表す文字列) : 日付を表す文字列を日付データに変換する関数。引数の形式は色々。
  • TEXT(数値, “オプション”) : 数値データを色々な表示形式で文字列に変換する関数。因みにオプションの前後に文字列を入れてもエラーにならずに出力される（例. ”aaa曜日” → 土曜日 ）。
  • IFERROR(処理, 処理にエラーがあった時の処理) : 処理にエラーが出た時に、別の処理をさせる関数。
  • COUNTBLANK(セル範囲) : セル範囲中の値が空白であるセルの数を返す関数。


改善点

  • 祝日のExcelデータを現在は、Webページのコピペに頼っている。本当は、コピペせずとも、ファイルを開けた瞬間に自動更新したい。Excel以外のツールが必要になると思われる。ExcelVBAとか？勉強してないから良く分からないけど…
  • 同じ月の中の日付はプログラムのコピペで作れるが、月毎では、コピペした後に参照データを選び直さないといけない。これは月毎のカレンダーの位置のズレが、参照先のデータのズレと一致しないからだと考えられる。理想は、一回のコピペで次月のカレンダーを作りたいけど、どうしたら良いんだろう？何か、Excelの仕様上の問題な気もするしなぁ…

  1. オプションの参考ページ。↩

  2. CSE数式と呼ばれている。↩