EXCEL – 祝日対応で開始曜日可変の万年カレンダーの作り方（2150年まで対応）

以前、Excelでの出勤簿が必要となり、それに利用する年間カレンダーも作成する必要がありました。
Excelカレンダーは他にも用途がありそうなので、年度が変わっても休日の対応が出来る万年カレンダーをExcelで作る方法をご紹介します。

画像のA1～G8セルがカレンダーで、I列～T列が作業列となります。
A1セルに「年」を4桁数値で、B1セルに「月」、B10セルに「締日」を数値で、A2セルに「開始曜日（日～土のいずれか）」を入力すると、その年月のカレンダーになります。
まずは作業列から以下に解説します。

休日と祝日を決める：作業セル（M列～T列）

この作業列で、祝日・年末年始・盆休などの休日を設定しています。
なお、土日はカレンダー上で休日とするので含んでいません。

年度（M2セル）

年度は、A1セルに入力した値を参照しています。

=A1

休日の設定（N列）

N列では、年間の休日や祝日を設定しています。各休日の数式は下記の通りです。入力の順序は順不同で構いません。
日付で決まっている休日・祝日は単純ですが、そうでないものがありますので解説を付けます。
また、式の右横にコメントを入れています。

=IF(M$2="","",DATE(M$2,1,1)) '元旦（1月1日）
=IF(M$2="","",DATE(M$2,1,2)) '年始休み（1月2日）
=IF(M$2="","",DATE(M$2,1,3)) '年始休み（1月3日）
=IF(M$2="","",DATE(M$2,1,4)) '年始休み（1月4日）

=IF(M$2="","",DATE(M$2,1,14)-WEEKDAY(DATE(M$2,1,14),3)) '成人の日（1月第2月曜）
=IF(M$2="","",DATE(M$2,2,11)) '建国記念の日（2月11日）

=IF(M$2="","",IF(AND(M$2>=1851,M$2<=1899),DATE(M$2,3,
 INT(19.8277+0.242194*(M$2-1980)-INT((M$2-1983)/4))),
 IF(AND(M$2>=1900,M$2<=1979),DATE(M$2,3,
 INT(20.8357+0.242194*(M$2-1980)-INT((M$2-1983)/4))),
 IF(AND(M$2>=1980,M$2<=2099),DATE(M$2,3,
 INT(20.8431+0.242194*(M$2-1980)-INT((M$2-1980)/4))),
 IF(AND(M$2>=2100,M$2<=2150),DATE(M$2,3,
 INT(21.851+0.242194*(M$2-1980)-INT((M$2-1980)/4)))))))) '春分の日

=IF(M$2="","",DATE(M$2,4,29)) '昭和の日（4月29日）
=IF(M$2="","",DATE(M$2,5,3)) '憲法記念日（5月3日）
=IF(M$2="","",DATE(M$2,5,4)) 'みどりの日（5月4日）
=IF(M$2="","",DATE(M$2,5,5)) 'こどもの日（5月5日）

=IF(OR(M$2="",M$2=2020),"",
 DATE(M$2,7,21)-WEEKDAY(DATE(M$2,7,21),3)) '海の日（2020年以外 7月第3月曜）
=IF(M$2<>2020,"",DATE(M$2,7,23)) '海の日（2020年のみ 7月23日）
=IF(M$2="","",IF(M$2=2020,DATE(M$2,7,23),DATE(M$2,7,21)-WEEKDAY(DATE(M$2,7,21),3)))
 '海の日（2020年のみ7月23日 それ以外は7月第3月曜）※式をまとめました。

=IF(M$2="","",DATE(M$2,9,21)-WEEKDAY(DATE(M$2,9,21),3)) '敬老の日（9月第3月曜）

=IF(M$2="","",IF(AND(M$2>=1851,M$2<=1899),DATE(M$2,9,
 INT(22.2588+0.242194*(M$2-1980)-INT((M$2-1980)/4))),
 IF(AND(M$2>=1900,M$2<=1979),DATE(M$2,9,
 INT(23.2588+0.242194*(M$2-1980)-INT((M$2-1980)/4))),
 IF(AND(M$2>=1980,M$2<=2099),DATE(M$2,9,
 INT(23.2488+0.242194*(M$2-1980)-INT((M$2-1980)/4))),
 IF(AND(M$2>=2100,M$2<=2150),DATE(M$2,9,
 INT(24.2488+0.242194*(M$2-1980)-INT((M$2-1980)/4)))))))) '秋分の日

=IF(OR(M$2="",M$2=2020),"",
 DATE(M$2,10,14)-WEEKDAY(DATE(M$2,10,14),3)) '体育の日（10月第2月曜）
=IF(M$2<>2020,"",DATE(M$2,7,24)) '体育の日（スポーツの日）（2020年のみ 7月24日）
=IF(M$2="","",IF(M$2=2020,DATE(M$2,7,24),DATE(M$2,10,14)-WEEKDAY(DATE(M$2,10,14),3)))
 '体育の日（2020年のみ7月24日 それ以外は10月第2月曜　※2020年8月現在）※式をまとめました。

=IF(M$2="","",DATE(M$2,11,3)) '文化の日（11月3日）
=IF(M$2="","",DATE(M$2,11,23)) '勤労感謝の日（11月23日）

=IF(OR(M$2="",M$2>=2019),"",
DATE(M$2,12,23)) '天皇誕生日（2018年以前の12月23日）
=IF(M$2<2020,"",DATE(M$2,2,23)) '天皇誕生日（2020年以降 2月23日）
=IF(OR(M$2="",M$2=2019),"",IF(M$2>=2020,DATE(M$2,2,23),DATE(M$2,12,23)))
 '天皇誕生日（2018年以前の12月23日と2020年以降の2月23日）※式をまとめました。

=IF(OR(M$2="",M$2<=2015),"",IF(M$2=2020,DATE(M$2,8,10),DATE(M$2,8,11)))
 '山の日（2016年以降 2020年は8月10日、それ以外は8月11日）

=IF(M$2<>2019,"",DATE(M$2,4,30)) '国民の休日（2019年のみ 4月30日）
=IF(M$2<>2019,"",DATE(M$2,5,1)) '即位の日（2019年のみ 5月1日）
=IF(M$2<>2019,"",DATE(M$2,5,2)) '国民の休日（2019年のみ 5月2日）
=IF(M$2<>2019,"",DATE(M$2,10,22)) '即位礼正殿の儀（2019年のみ 10月22日）

=IF(M$2="","",DATE(M$2,8,14)) '盆休（8月14日）
=IF(M$2="","",DATE(M$2,8,15)) '盆休（8月15日）
=IF(M$2="","",DATE(M$2,8,16)) '盆休（8月16日）
=IF(M$2="","",DATE(M$2,12,29)) '年末休み（12月29日）
=IF(M$2="","",DATE(M$2,12,30)) '年末休み（12月30日）
=IF(M$2="","",DATE(M$2,12,31)) '大晦日（12月31日）

成人の日（1月第2月曜）など第n週の○○曜の場合

=IF(M$2="","",DATE(M$2,1,14)-WEEKDAY(DATE(M$2,1,14),3)) '成人の日（1月第2月曜）

「成人の日」は1月の第2月曜なので、2月1日が火曜日の場合に14日が第2月曜となり、日付が最大となります。その最大値から、第2月曜が13日の場合は1、12日の場合は2…を引き算すれば良いので、Weekday関数（引数：週の基準を3）でその差し引く数値を決めています。
その他の第n週○○曜の祝日も同様です。

春分の日と秋分の日

「春分の日」と「秋分の日」は特殊で、国立天文台の暦象年表に基づいて前年2月の官報に公告されることで決定するものなので、あくまで天文学による予測となります。
天文学では原則として、「春分の日」は春分点（天の赤道と黄道が交差する点。秋分点は180度反対側）を太陽が通過した瞬間を含んだ日であり、その通過から再び春分点に戻るまでを1年=365.242194日とするものであり、1年を365日としているカレンダーでは、0.242194（約5時間49分）ずつ遅れるわけです。ところが4年に1度、1年を366日とする閏年があるので、そこでほぼリセットされます。「秋分の日」も同様です。
計算方法については他でも紹介されておりますが、扱う数値が複雑なので様々あり、1951年から2150年までの計算方法としては、多分、下記が合っていると思います。

'春分の日
=int(19.8277+0.242194*(西暦年-1980)-int((西暦年-1980)/4)) '1851-1899年
=int(20.8357+0.242194*(西暦年-1980)-int((西暦年-1980)/4)) '1900-1979年
=int(20.8431+0.242194*(西暦年-1980)-int((西暦年-1980)/4)) '1980-2099年
=int(21.8510+0.242194*(西暦年-1980)-int((西暦年-1980)/4)) '2100-2150年

'秋分の日
=int(22.2588+0.242194*(西暦年-1980)-int((西暦年-1980)/4)) '1851-1899年
=int(23.2588+0.242194*(西暦年-1980)-int((西暦年-1980)/4)) '1900-1979年
=int(23.2488+0.242194*(西暦年-1980)-int((西暦年-1980)/4)) '1980-2099年
=int(24.2488+0.242194*(西暦年-1980)-int((西暦年-1980)/4)) '2100-2150年

上記をIF関数で条件分岐させれば、1851～2150年まで対応できます。

祝日の曜日を取得（O列）

事項の振替休日を計算するために、前項で設定した休日・祝日の曜日を取得しています。
O2セルに下記計算式を入れ、下方にオートフィルします。
ちなみに振替休日（P列）のIF関数の論理式でWEEKDAY関数を使用すればこの列は不要です。

=TEXT(N2&"","aaa")

第1引数（値）を、N2&”” としているのは、もしN2が空欄だった場合に、空白=0 と解釈され、土曜を返してくる場合があるからです。なので、0 にならないように（空白にするために） &”” としています。

振替休日（P列）

休日・祝日設定した日が日曜日だった場合に翌日も休日としますが、翌日の月曜も休日である場合、その翌日の火曜も休日である場合…と連休になった場合を一週間考慮しています。
P2セルに下記計算式を入れ、下方にオートフィルします。

=IF(N2="","",IF(O2="日",LOOKUP(1,0/(N2+ROW($1:$7)-1=N2:N8),N2:N8)+1,
 IF(N2+2=N3,N2+1,"")))

O列を作らずに WEEKDAY関数を使用した場合は下記のようになります。

=IF(N2="","",IF(WEEKDAY(N2,1)=1,LOOKUP(1,0/(N2+ROW($1:$7)-1=N2:N8),N2:N8)+1,
 IF(N2+2=N3,N2+1,"")))

分解して計算式の解説

上記式を分解して解説します。分かりにくいのは以下のLOOKUP関数だと思います。これは、つまりは連続する休日・祝日の最後の日を求めていることになります。

LOOKUP(1,0/(N2+ROW($1:$7)-1=N2:N8),N2:N8)+1

まず、ROW($1:$7) は {1,2,3,4,5,6,7} の配列です。ただ、{1,2,3,4,5,6,7} とすると年末から年始にかけての休日に振替休日がある場合に不具合が生じます（LOOKUP関数の検査範囲は昇順で並んでいる必要があるので）。
N2+ROW($1:$7)-1 は、{N2+1-1,N2+2-1,N2+3-1,N2+4-1,N2+5-1,N2+6-1,N2+7-1} といった意味合いです。
このLOOKUP関数の第2引数（検査範囲）である 0/(N2+ROW($1:$7)-1=N2:N8) は、{0/(N2=N2), 0/(N2+1=N3), 0/(N2+2=N4), 0/(N2+3=N5), 0/(N2+4=N6), 0/(N2+5=N7), 0/(N2+6=N8)} といった意味になります。分母はそれぞれ、真（TRUE）か偽（FALSE）、つまり、1か0を返しますので、配列のそれぞれは、0/1=0 か 0/0=#DIV/0! となります。配列のうち、N2=N2 は必ず真となるので 0/(N2=N2) は 0、それ以外は、0 かエラー（#DIV/0!）となります。
LOOKUP関数は、検索値（この場合は1）が検索範囲に見つからない場合に、検索値以下の最大値を返しますので、この場合は検索範囲の配列の最後の0を返します。つまりは、N2から連続する最後の休日を返し、1を加算して翌日を振替休日としています。

次のIF関数は、「国民の休日」への対応です。前後が祝日である平日は、国民の休日となり、休日となります。

IF(N2+2=N3,N2+1,"")

休日数のカウント（S1セル）

休日・祝日と振替休日の日数をカウントしています。

=COUNT(N:N,P:P)

すべての休日を昇順で並べる（T列）

行番号がS1セルのカウント数に達するまで、N列とP列の休日・祝日をT列1行目から昇順に並べます。
下記の式をT1セルに入力して下方へオートフィルします。

=IF(S$1 < ROW(),"",SMALL((N:N,P:P),ROW()))

作業セル（I列～K列）

WEEKDAY関数の引数を決定する（I1セル）

A2セル入力曜日に対して、日付欄で使用するWEEKDAY関数の引数（週の基準）を決定するためのLOOKUP関数を入れています。

=LOOKUP(A2,{"金","月","水","土","火","日","木";15,11,13,16,12,17,14})

締日の決定（I2セル）

B10セルに入力した締日の数値に対して、15日以降（月の後半）であれば当月（B1セルに入力した月）を締日とし、14日以前（月の前半）であれば翌月を締日としています。

=IF($B$10>=15,IF(DAY(DATE($A$1,$B$1,$B$10))=B10,
 DATE($A$1,$B$1,$B$10),DATE($A$1,$B$1+1,0)),DATE($A$1,$B$1+1,$B$10))

スタート日の決定（J1セル）

締日と同様に、締日が15日以降の場合は1ヶ月前の締日翌日をスタート日とし、14日以前の場合はその翌日（当月）をスタート日としています。
DAY(DATE($A$1,$B$1,$B$10))=B10 は、例えばB10セルの締日が31日（月末）の場合、2月は月末が28日なので、31日の1ヶ月前とすると2月3日を返してしまうことへの対応です。例えば、DATE(2019,2,31) は2月28日を返しますので、この論理式が真の場合（つまり月末の場合には締日と月末日が等しい場合）は1ヶ月前の締日翌日を返し、偽の場合は当月の1日を返しています。

=IF($B$10>=15,IF(DAY(DATE($A$1,$B$1,$B$10))=B10,
 EDATE(DATE($A$1,$B$1,$B$10+1),-1),DATE($A$1,$B$1,1)),DATE($A$1,$B$1,$B$10+1))

2日目以降の日付（J2～J31セル）

下記の式をJ2セルに入力してJ31までオートフィルです。単純に、1つ上の行の値がI2セルの締日に達するまで、1日ずつ増やしています。

=IF(J1>=I$2,"",SUM(J1,1))

休日かどうかの判定（K1～K31セル）

J列の日付が、T列の休日・祝日または土日の場合に「休」とし、それ以外はカウントしています。

=IF(OR(COUNTIF($T:$T,J1),WEEKDAY(J1,2)=7,WEEKDAY(J1,2)=6),"休",1) 'K1セル

=IF(J2="","",IF(OR(COUNTIF($T:$T,J2),WEEKDAY(J2,2)=7,
 WEEKDAY(J2,2)=6),"休",COUNT(K$1:K1)+1)) 'K2～K31セル

カレンダー部（A1～G8セル）

曜日設定（スタート曜日）

A2セルにはスタート曜日（日～土のいずれか）を直接入力します。

B2セルには下記のIF関数の数式を入れ（単純にA2セルの値から条件分岐しているだけです）、G2セルまで横にオートフィルします。

=IF(A2="月","火",IF(A2="火","水",IF(A2="水","木",IF(A2="木","金",
 IF(A2="金","土",IF(A2="土","日",IF(A2="日","月")))))))

日付欄

日付欄は、3行目と4行目以降、A列とB列以降で少し違ってきます。

A3セルには下記の数式を入れます。
J1セルの値（スタート日）において、I1セル値を週の基準とするWEEKDAY関数の戻り値が1（COLUMN() = 1）（開始曜日）の場合はJ1セルの日付を返し、そうでない場合は"・"を返しています。

=IF(WEEKDAY($J1,$I$1)=COLUMN(),$J1,"・")

B3セルには下記の数式を入れます。
上記同様、J1セルの値（スタート日）において、I1セル値を週の基準とするWEEKDAY関数の戻り値が2（COLUMN() = 2）の場合はJ1セルの日付を返し、そうでない場合はA3セルの状態により、A3の日付に1を加算するか"・"を返しています。
右方にG3セルまでオートフィルします。

=IF(WEEKDAY($J1,$I$1)=COLUMN(),$J1,IF(A3<>"・",A3+1,"・"))

A4セル以降は、前日セルがI2の値（締日）に達するまで1を加算していき、締日を超えたら"・"を返しています。

A4セルには下記の数式を入れます。

=IF(OR(G3=$I$2,G3="・"),"・",G3+1)

B4セルには下記の数式を入れ、G4セルまでオートフィルします。

=IF(OR(A4=$I$2,A4="・"),"・",A4+1)

そして、A4～G4セルを8行目までオートフィルすれば完成です。
A1セルに「年」、B1セルに「月」、A2セルに「開始曜日」、B10セルに「締日」を入力するとカレンダーが出来ます。

おまけの条件付き書式

ちなみに背景色などのセルの書式は条件付き書式です。

'背景色：赤　フォント色：白
=WEEKDAY(A3,2)=7 'ルール
=$A$3:$G$8 '適用先

=AND(A3<>"",COUNTIF($T:$T,A3)) 'ルール
=$A$3:$G$8 '適用先

'背景色：青　フォント色：白
=WEEKDAY(A3,2)=6 'ルール
=$A$3:$G$8 '適用先

'背景色：グレー
=J1="" 'ルール
=$J$1:$K$31 '適用先

'背景色：赤　フォント色：白
=OR(WEEKDAY(J1,2)=7,WEEKDAY(J1,2)=6) 'ルール
=$K$1:$K$31 '適用先

=COUNTIF($T:$T,J1) 'ルール
=$K$1:$K$31 '適用先

以上、ご参考になれば幸いです。

なお、下記ページを参考にさせていただきました。

春分の日 - Wikipedia

ja.wikipedia.org

黄道 - Wikipedia

ja.wikipedia.org

http://koyomi.vis.ne.jp/reki_doc/doc_0330.htm

koyomi.vis.ne.jp

春分・秋分の日 - 仕事に役立つエクセル実践問題集

春分・秋分の日を天文データから算出。INTとVLOOKUPだけで1851年〜2150年までの春分・秋分の日を求める

mt-soft.sakura.ne.jp