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

EXCEL
この記事は約6分で読めます。
スポンサーリンク

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

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

スポンサーリンク

休日と祝日を決める:作業セル(M列~T列)

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

年度(M2セル)

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

EXCEL
=A1

休日の設定(N列)

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

EXCEL
=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週の○○曜の場合

EXCEL
=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年までの計算方法としては、多分、下記が合っていると思います。

EXCEL
'春分の日
=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関数を使用すればこの列は不要です。

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

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

振替休日(P列)

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

EXCEL
=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関数を使用した場合は下記のようになります。

EXCEL
=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関数だと思います。これは、つまりは連続する休日・祝日の最後の日を求めていることになります。

EXCEL
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関数は、「国民の休日」への対応です。前後が祝日である平日は、国民の休日となり、休日となります。

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

休日数のカウント(S1セル)

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

EXCEL
=COUNT(N:N,P:P)

すべての休日を昇順で並べる(T列)

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

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

作業セル(I列~K列)

WEEKDAY関数の引数を決定する(I1セル)

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

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

締日の決定(I2セル)

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

EXCEL
=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日を返しています。

EXCEL
=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日ずつ増やしています。

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

休日かどうかの判定(K1~K31セル)

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

EXCEL
=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セルまで横にオートフィルします。

EXCEL
=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セルの日付を返し、そうでない場合は"・"を返しています。

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

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

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

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

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

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

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

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

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

おまけの条件付き書式

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

EXCEL
'背景色:赤 フォント色:白
=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
黄道 - Wikipedia
http://koyomi.vis.ne.jp/reki_doc/doc_0330.htm
春分・秋分の日 - 仕事に役立つエクセル実践問題集
春分・秋分の日を天文データから算出。INTとVLOOKUPだけで1851年〜2150年までの春分・秋分の日を求める
EXCEL
スポンサーリンク
スポンサーリンク
J.をフォローする
スポンサーリンク
アトリエJ.

コメント

  1. 古谷達也 より:

    初めまして。
    サイトを参考にカレンダーを作成させていただきました。
    弊社は、12月29日から1月3日までが年末年始の休日となっております。
    2日と3日を年始休みとしているため、振替休日の数式をそのまま使用すると、1月4日が振替となってしまいます。
    本来であれば、元旦が日曜であれば、翌日の火曜が振休になると思います。
    2日を休日扱いにしていなければ、2日が振休となるのでしょうが、何かいい方法はありませんでしょうか?
    お忙しいとは存じますが、よろしくお願いいたします。

  2. J.J. より:

    >古谷さま

    コメントありがとうございます。

    P列の振替休日の式は、

    “翌日の月曜も休日である場合、その翌日の火曜も休日である場合…と連休になった場合を一週間考慮しています。”

    ので、年末年始休日の場合には1日だけ考慮するようにしては、いかがでしょう?(下記式をP2に入れて下にコピー)

    =IF(N2=””,””,IF(WEEKDAY(N2,1)=1,IF(AND(N2>=DATE($M$2-1,12,29),N2<=DATE($M$2,1,3)),N2+1,LOOKUP(1,0/(N2+ROW($1:$7)-1=N2:N8),N2:N8)+1), IF(N2+2=N3,N2+1,"")))

    • 古谷達也 より:

      ご返信ありがとうございます。
      ご教授いただいた数式をいれると、見事に1月2日が振替休日となりました。
      ありがとうございます。
      現在、2年続きのカレンダーを作成しているところです。
      ホームページ通りN列に日付、O列に曜日、P列に振替休日としており、N列に2年分の祝日及び会社休日を入力しております。
      年末年始が29日から3日までなのですが、2023年の12月31日の振替休日の所に1月4日と表示され、2024年の12月29日の振替休日の所に1月1日と表示されます。
      2年分としているのが悪いのかと思い、翌年の数式等を削除してみると12月31日の振替休日のところに1月1日と表示されます。
      2年分の休日を設定するのは、無理でしょうか。
      再度の質問申し訳ございませんが、よろしくお願いします。

      • J.J. より:

        >古谷さま

        ご返信ありがとうございます。
        確かに、締日により年をまたぐので2年分は必要ですね。
        N列に2年分入れると式の変更が必要になると思いますので、簡単な方法をご説明いたします。

        何年続きでも方法は同じで、M列からR列(年、日付、曜日、振替休日の列が必要。Q列R列は省いても可です。)をコピーして任意の場所に挿入してください。

        例えば、M列からR列を、S列からX列、Y列からAD列にそれぞれコピーして挿入します。

        S2セルの式 =$A$1 を =$A$1-1 に変更します(前年度の休日がT列とV列に出ます)。
        Y2セルの式 =$A$1 を =$A$1+1 に変更します(翌年度の休日がZ列とAB列に出ます)。

        上記の列挿入で、T列がAF列になっていると思います。

        もともとのT1セルの式: =IF(S$1 < ROW(),"",SMALL((N:N,P:P),ROW()))
        挿入後のAF1セルの式: =IF(AE$1 < ROW(),"",SMALL((N:N,P:P),ROW()))

        このAF1セルの式を SMALL関数 の範囲に、前年度と翌年度の休日の列(T,V,Z,AB)を加えた下記式に変更して下にコピーします。

        =IF(AE$1<ROW(),"",SMALL((N:N,P:P,T:T,V:V,Z:Z,AB:AB),ROW()))

        以上でどうでしょうか?

        • 古谷達也 より:

          ご返信ありがとうございます。
          ご教示いただいたとおり、M列からR列を、S列からX列、Y列からAD列にそれぞれコピーして挿入し、S2及びY2セルの数式を変更、AF1セルの式に(T,V,Z,AB)を追加しました。
          A1セルの値が2023であれば、P2の振替休日は1月2日と表示されますが、A1セルの値を2022とすると、Y2セルの値が2023となると思いますが、そこで表示されるAB2セルの振替休日は1月4日となります。
          AB2セルの式のDATE($M$2-1,12,29),Z2<=DATE($M$2,1,3)がM2セルを絶対参照になっているのが影響しているのかと思いY2に変更しましたが、エラーとなりました。
          また、2021年の1月3日が日曜になると思いますが、これの振替休日が1月4日と表示されます。
          1月3日は会社休日のため、振替休日とはなりません。

          私の知識の範囲で色々とやってみましたが、中々上手くいきません。

          私の勉強不足でご迷惑をおかけしているようで、とても恐縮しております。
          お忙しいのに色々とご教示いただき、とても感謝しております。

          • J.J. より:

            >古谷さま

            お疲れさまです。

            仰るとおり、コピーするにあたり、列の絶対参照を外さないといけませんでした。失礼いたしました。
            それと、年末年始休日の場合には1日だけ振替休日を考慮する式にしていましたが、御社の設定であれば、「年末年始休日は振替休日を考慮しない」式にしないといけないと思いますので、P2セルに下記式で下にコピーし、そのP2セルをそのままV2セルとAB2セルにコピペして同じく下にコピーでどうでしょうか?

            =IF(N2="","",IF(WEEKDAY(N2,1)=1,IF(AND(N2>=DATE(M$2-1,12,29),N2<=DATE(M$2,1,3)),"",LOOKUP(1,0/(N2+ROW($1:$7)-1=N2:N8),N2:N8)+1), IF(N2+2=N3,N2+1,"")))

            よろしくお願いいたします。

タイトルとURLをコピーしました