万年カレンダーと掃除当番表

質問

お世話になります。町内会のお仕事なんですが、教えていただけるでしょうか。
月曜始まりで１週間で次の人に代わる名前表を作りたいのです。
火曜から日曜までのセルは「捺印スペース」になります。
完成品は印刷して、ゴミ当番の回覧板に挟んで使います。
もう３年も手書きで作っているので、なんとか自動化できないものかと思いご相談いたしました。

・年度を書き換えるとその年のカレンダーに書き換わる。
・同時に「新年度スタート」の氏名から順に当番氏名を月曜日に落とし込む。
・「新年度スタート」の位置は手動入力。
・当番氏名欄にある16軒を順にカレンダーに割り振りたい。
・当番氏名は年度をまたいで続きますが、カレンダーとしては３末で打ち切りの１年分１枚だけです。
・2016年４月４日（月曜日）から８月１日まで手動で見本を記入しました。
・当番氏名が減った場合は書き直せばいいかと思いますが、増えた場合にも対応できるようにしたい。

すべての月曜日セルに「新年度スタート」の位置を読み取る関数を書くのかと想像していますが、まったくわかりません。
年度を変えたとき、難しいようでしたら「すべての関数を手書きで書き直す」ということでも構いません。書き換え方を教えてください。
よろしくお願いいたします。

回答No.1

kagakusuki · ベストアンサー

画像の文字が潰れていて、どこにどんなデータが入力されているのか良く解りませんので、取りあえず仮の話として、A1セルに「2016」や「平成28」等の年の値が入力されていて、AA3セルに「当番氏名」、AB3セルに「開始位置」と入力されていて、AA4～AA99のセル範囲が当番の氏名の入力欄として使用されていて、尚且つ、その当番の氏名は途中に空欄が挟まっている事無く連続したセル範囲に入力されているものとします。
　又、A3～Y3のセル範囲には4月～3月の月が1列おきに入力されていて、その下の4行目～34行目の範囲には各月の日付が自動的に表示される様にし、各日付の右隣のセルには、その日の当番の氏名が自動的に表示される様にするものとします。
　又、A1セルに年を示す値が入力されていない場合や、当番の氏名が1人も入力されていない場合、当番の氏名が並んでいる途中に空欄のセルが挟まっている場合、AB列に「新年スタート」と入力されているセルが存在しない場合、及びAB列に「新年スタート」と入力されているのと同じ行内のAA列のセルに当番の氏名が入力されていない場合には、A4～X34のセル範囲には何も表示されないものとします。

　まず、A3セルの書式設定の表示形式を[ユーザー定義]の

"4月"

として下さい。
　次に、A3セルに次の関数を入力して下さい。

=IF(ISERROR(($A$1&"年4月7日")/(INDEX($AA:$AA,MATCH("新年度スタート",$AB:$AB,0))<>"")/(COUNTIF($AA$3:$AA$99,"*?")=ROWS($AA$3:INDEX($AA$3:$AA$99,MATCH("*?",$AA$3:$AA$99,-1))))),0,($A$1&"年4月7日")-WEEKDAY($A$1&"年4月7日",2))

　次に、A4セルの書式設定の表示形式を[ユーザー定義]の

daaa

として下さい。
　次に、A4セルに次の関数を入力して下さい。

=IF(SUM($A$3),IF(MONTH(DATE(YEAR($A$3),INT(COLUMNS($A:A)/2+4),ROWS(A$4:A4)))=MOD(INT(COLUMNS($A:A)/2+3),12)+1,DATE(YEAR($A$3),INT(COLUMNS($A:A)/2+4),ROWS(A$4:A4)),""),"")

　次に、B4セルに次の関数を入力して下さい。

=IF(SUM(A4)<1,"",IF(WEEKDAY(A4)=2,INDEX($AA$3:$AA$99,MOD(INT((A4-$A$3)/7)+MATCH("新年度スタート",$AB:$AB,0)-ROW($AB$3)-1,COUNTIF($AA$3:$AA$99,"*?")-1)+2),""))

　次に、A4～B4のセル範囲をコピーして、C4～X4のセル範囲に貼り付けて下さい。
　次に、A4～X4のセル範囲をコピーして、A5～X34のセル範囲に貼り付けて下さい。

　以上です。

お礼

ありがとうございます。これから始めます！

補足

回答No.１についてはできたようです。魔法のようですね。

回答No.２はこれからですが「祝日や振替休日の日を自動的に計算して表示させる」これもせっかくですからお願いできるでしょうか。
たかがゴミ当番表なんですが、これ全国的に頭抱えてる人は多いと思います。やったとかやらないとか忘れたとか。カレンダーに明記してしまえば間違いはなくなります。しかし作るのは大変です。
フリーソフトも探してみましたが、だいたい「シフト表」か「日直」なんですよね。週替りで交代していく方式は見つかりませんでした。
ぜひもう少しご指導いただいて、ゴミ問題を解決したいと思います。

回答No.7

kagakusuki

>三つとも同じ計算式のようなのですが。

　何度も申し訳御座いません。回答文を作成する際に(記憶には無いものの)途中で編集操作の「元に戻す」でも使ってしまったのか、一旦、修正した筈の式が何故か元に戻っておりました。
　正しい関数は次の様になります。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1+1,1,14-WEEKDAY(DATE($A$1+1,1,0),3)),"")

お礼

ありがとうございました。完成しました！　再来年のも作っちゃいました！
大変お手数をお掛けする質問だったのですね。本当に助かりましたm(_ _)m
ぜひ公開の方もご検討ください。楽しみにしています。

回答No.6

kagakusuki

>画像でもこちらでも「2017/1/11」と計算された成人の日は、紙のカレンダーで「2017/1/9」になっているようです。これはどうしたらよろしいでしょうか。

　申し訳御座いません。元々はA1セルに入力されている西暦年における各祝日の日付を求める関数であったものを基にして、「A1セルに入力されている"年度"における各祝日の日付を求める関数」に作り変えた際に、1月～3月に関しては翌年の祝日の日付を表示する様にしたつもりであったのですが、「成人の日」を求める際に必要となる「曜日の算出」を行っている部分で、「A1セルに表示されている数字の翌年の西暦年の日付の曜日」を求めなくてはならないように変更すべきところを変更し忘れて、変更前のままの「A1セルに表示されている数字の西暦年の日付の曜日」を求めるようになっておりました。
　従って、回答No.4で述べましたSheet2のB23セルの関数である

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1+1,1,14-WEEKDAY(DATE($A$1,1,0),3)),"")

は誤りで、正しくは次の様な関数となります。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1+1,1,14-WEEKDAY(DATE($A$1,1,0),3)),"")

>回答者様の画像で「2016/9/22」と計算されているセルが、こちらでは「（日付不明）」と計算されました。微妙な違いが出るものなのかと思いましたが、「春分の日」「秋分の日」欄が参照先なのですね。日付を入力する必要がありました。

　はい、回答No.5で触れました様に、地球以外の天体の影響などによって地球の動きに誤差が生じ、それにより春分や秋分が起きる時刻もある程度変動してしまうため、秋分の日を求める関数では、Sheet2のE列に入力されている日付の方を優先する様になっており、Sheet2のE列にその年の春分の日に該当する日付が入力されていない場合で、尚且つ、秋分が起きる瞬間の時刻の計算結果が日付が変わる前後1時間以内の場合には計算結果を信用せず、「（日付不明）」と表示する様になっております。
　偶々、2016年の場合には、秋分が起きる日時が9月22日23時21分という日付が変わる39分前であったため、日付が変わる前後1時間以内なので、Sheet2のE列に日付が入力されていなかった場合には、Sheet2のB13セルに「（日付不明）」と表示された訳です。
　それに対し、2017年の春分が起きる日時は3月20日 19時29分という日付が変わる3月20日24時の4時間31分も前(或いはその前の日付が変わった3月20日0時の19時間29分後)であったため、日付が変わる前後1時間以内ではないので、春分の日に関してはSheet2のD列に日付が入力されていない時にもSheet2のB26セルに日付が表示された訳です。

【参考URL】
　国立天文台 > 暦計算室 > 暦要項 > 二十四節気および雑節 平成28年（2016）
　　http://eco.mtk.nao.ac.jp/koyomi/yoko/2016/rekiyou162.html

　国立天文台 > 暦計算室 > 暦要項 > 二十四節気および雑節 平成29年（2017）
　　http://eco.mtk.nao.ac.jp/koyomi/yoko/2017/rekiyou172.html

補足

ありがとうございます。誠に恐縮ですが、三つとも同じ計算式のようなのですが。
元=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1+1,1,14-WEEKDAY(DATE($A$1,1,0),3)),"")
誤=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1+1,1,14-WEEKDAY(DATE($A$1,1,0),3)),"")
正=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1+1,1,14-WEEKDAY(DATE($A$1,1,0),3)),"")

回答No.5

kagakusuki

回答No.4の続きです。

　「春分の日」に関する説明として良く「昼と夜の長さが同じになる」という説明を目にする事がありますが、「春分の日」とは正確には「『春分』と言う現象が起きる日」の事であり、「春分」とは、地球から見て天球上を移動している様に見える太陽が、天球の赤道面を南から北に向かって通過する"瞬間"の事なのです。
　同様に「秋分」とは「『秋分』と言う現象が起きる日」の事であり、「秋分」とは、地球から見て天球上を移動している様に見える太陽が、天球の赤道面を北から南に向かって通過する"瞬間"の事なのです。

【参考URL】
　春分の日 - Wikipedia
　　https://ja.wikipedia.org/wiki/%E6%98%A5%E5%88%86%E3%81%AE%E6%97%A5

　春分 - Wikipedia
　　https://ja.wikipedia.org/wiki/%E6%98%A5%E5%88%86

　秋分の日 - Wikipedia
　　https://ja.wikipedia.org/wiki/%E7%A7%8B%E5%88%86%E3%81%AE%E6%97%A5

　秋分 - Wikipedia
　　https://ja.wikipedia.org/wiki/%E7%A7%8B%E5%88%86

　国立天文台 > 暦計算室 > 暦Wiki > 季節 > 二十四節気とは？
　　http://eco.mtk.nao.ac.jp/koyomi/wiki/B5A8C0E12FC6F3BDBDBBCDC0E1B5A4A4C8A4CFA1A9.html

　春分や秋分の様な「地球から見た太陽の天球上における動き」に関わる天文的な現象は、地球の公転と地軸の傾きによって生じる現象で、もし地球の公転周期や地軸の傾き方が一定であるのなら、春分や秋分も地球の公転周期に等しい一定の間隔で起きる事になります。
　しかし実際にはそうではなく、地球の公転周期や地軸の傾き方は厳密には一定ではなく、月や太陽、木星、金星等を始めとする主に太陽系内の他の天体の重力などの影響を受けて僅かながら変動します。
　この変動があるために、春分や秋分の起きる"瞬間"の時刻にずれが生じ、周期が一定と見做して計算した場合の結果とは最大で20分程度のずれが生じます。
　天体同士が重力を及ぼし合って運動する現象は複雑で、関わる天体の数が3個以上になると、その動きを厳密に計算する事は困難になります。
　そのため、方程式の様な一定の公式を使って計算したのでは、複雑な式を使った場合であっても計算結果と実際の値との間には10分前後の違いが見られる事も珍しくはないそうです。(計算式の精度が悪いのではなく、実際の値の方が変動してしまうためです)

【参考URL】
　国立天文台 > 暦計算室 > トピックス > 春分の日・秋分の日
　　http://eco.mtk.nao.ac.jp/koyomi/topics/html/topics1997.html

　国立天文台 > 暦計算室 > トピックス > 秋分の日が動き出す
　　http://eco.mtk.nao.ac.jp/koyomi/topics/html/topics2012_2.html

　回答No.3の「春分の日を自動計算する関数」や回答No.4の「秋分の日を自動計算する関数」は、「『春分や秋分が起きる周期』が年月の経過に伴って"一定の割合"で変化して行く」と仮定して、国立天文台が発表している1955年～2017年の春分や秋分が起きた日時(分単位)のデータ(但し、2016年と2017年の発表データは観測されたデータを基に計算によって求められた推測値)を基にして、その「周期が変化して行く割合」を求め、「将来においても春分や秋分の周期は、その一定の割合で変化して行く」と仮定する事で、将来の春分の日の日付や秋分の日の日付を計算しています。
　勿論、その様な仮定が正しいという保証などはありませんし、元々、実際に起きる日時も他の天体の影響を受けて不規則に変動しているのですから、回答No.3、4の関数で求めた値も誤差が含まれているしまう事は免れず、過去のデータと比較した場合、最大で15分弱の誤差が生じております。
　つまり、春分や秋分が起きる瞬間の計算値が、23時45分～23時59分59秒の時間帯の中の時刻になった場合、もしかすると誤差によって実際に春分や秋分が起きる瞬間が「計算によって求めた日付」の翌日になるという事もあり得る訳です。
　同様に、春分や秋分が起きる瞬間の計算値が、0時00分～0時15分の時間帯の中の時刻になった場合、もしかすると誤差によって実際に春分や秋分が起きる瞬間が「計算によって求めた日付」の前日になるという事もあり得る訳です。
　又、もしかすると、過去のデータと比べた場合には15分弱の誤差で済んでいても、将来にはもっと大きな誤差が生じる恐れも無い訳ではありません。

　そのため、回答No.3、4の春分の日の日付や秋分の日の日付を表示させる関数では、Sheet2のD列に入力されている春分の日の日付や、Sheet2のE列に入力されている春分の日の日付の中から、「計算によって求められた日付」の前後1日以内の日付を探し出して表示する様になっています。(入力されている日付の中に前後1日以内に該当する日付が複数あった場合には、その中で最も古い日付が表示されます)
　そして、もし「『計算によって求められた日付』の前後1日以内」に該当する日付が無かった場合には、計算によって求めた日付を表示する様になっています。
　但し、春分や秋分が起きる瞬間の計算値が23時00分～0時59分59秒の時間帯となった場合には、「計算結果通りの日付になると信用する事は出来ない」と見做してその日付は表示せず、代わりに「(日付不明)」と表示する様になっていますので、もしSheet2のB13セルやB26セルに「(日付不明)」と表示された場合には、Sheet2のD列にその年の春分の日の日付を、Sheet2のE列にその年の秋分の日の日付を必ず入力する様にして下さい。

　因みに、春分や秋分が起きる瞬間の日時や、祝日・休日の日の日付は、国立天文台がホームページに発表しておりますので、そこで確認する事が出来ます。

【参考URL】
　国立天文台 > 暦計算室 > 暦要項
　　http://eco.mtk.nao.ac.jp/koyomi/yoko/

　国立天文台 > 暦計算室 > 暦要項 > 平成29年（2017）
　　http://eco.mtk.nao.ac.jp/koyomi/yoko/2017/rekiyou171.html

　後、書き忘れておりましたが、「山の日」が祝日として施行される様になるのは今年(2016年)からであって、2015年以前には「山の日」は存在していなかった事からも解ります様に、祝日や振替休日の日付の決め方は時代によって変化します。
　「山の日」以外の祝日や振替休日に関しても、その有無や日付の決め方は時代によって異なっており、その時代ごとの違いを全て網羅して関数で反映させる様にするのは面倒なため、今回の祝日を自動的に求める関数では「国民の祝日に関する法律」を「山の日」の採用のために改正・施行した平成28年(2016年)の施行の1つ前の改正・施行である平成19年(2007年)1月1日の施行以降にのみ対応し、2006年度以前の祝日や振替休日は計算しない様になっておりますので注意して下さい。(「山の日」が表示されるのは2016年度以降のみです)

回答No.4

kagakusuki

回答No.3の続きです。

　次に、Sheet2のA21セルに「元旦」と入力して下さい。
　次に、Sheet2のB21セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1+1,1,1),"")

　次に、Sheet2のA22セルに「振替休日」と入力して下さい。
　次に、Sheet2のB22セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(B21)=1)),B21+1,"")

　次に、Sheet2のA23セルに「成人の日」と入力して下さい。
　次に、Sheet2のB23セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1+1,1,14-WEEKDAY(DATE($A$1,1,0),3)),"")

　次に、Sheet2のA24セルに「建国記念の日」と入力して下さい。
　次に、Sheet2のB24セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1+1,2,11),"")

　次に、Sheet2のA25セルに「振替休日」と入力して下さい。
　次に、Sheet2のB25セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(B24)=1)),B24+1,"")

　次に、Sheet2のA26セルに「春分の日」と入力して下さい。
　次に、Sheet2のB26セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),IF(COUNTIFS($D:$D,">="&INT(($A$1+1)*365.242382779165-693879.079496599-1),$D:$D,"<="&INT(($A$1+1)*365.242382779165-693879.079496599+1)),LARGE($D:$D,COUNTIF($D:$D,">="&INT(($A$1+1)*365.242382779165-693879.079496599-1))),IF(MOD(($A$1+1)*365.242382779165-693879.079496599+"1:00",1)<"1:00"*2,"(日付不明)",INT(($A$1+1)*365.242382779165-693879.079496599))),"")

　次に、Sheet2のA27セルに「振替休日」と入力して下さい。
　次に、Sheet2のB27セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(B26)=1)),B26+1,"")

　次に、Sheet2のD1セルに「春分の日」と入力して下さい。
　次に、Sheet2のE1セルに「秋分の日」と入力して下さい。
　次に、以下の様な操作を行って、Sheet2のB13セルとB26セルに条件付き書式を設定して下さい。

B13セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=COUNTIF(B13,"*?")

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄を

=$B$13,$B$26

に変更
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　次に、なるべくならばSheet2のD2以下に表示させたいカレンダーの年の春分の日を含む各年の春分の日の日付を入力しておく様にして下さい。
　同様にSheet2のE2以下には秋分の日の日付を入力しておく様にして下さい。
(前もって何年か分の春分・秋分の日の日付を入力しておいても宜しいですし、その年の春分・秋分の日の日付だけを入力しておくだけでも構いません)
　別に春分・秋分の日の日付を入力しておかなくても、大抵の年ではSheet2のB列に春分・秋分の日の日付が自動表示される様になっているのですが、年によっては計算で求めた日付と、実際の日付が前後1日程度ずれる恐れがあるため、その様な恐れがある年の場合には、春分の日か秋分の日を計算によって求める事を止める様な関数としているからです。

　今ちょっと時間が無くなってまいりましたので、詳しくは深夜になってからにでもまた回答致しますので、今しばらく質問を締め切らずにお待ち頂く様御願い致します。

補足

ありがとうございます。
「～"2007/1/1"～」なんだろなーと思いましたが法律でしたか。2007年以降対応版ですね。祝日の法律が大幅に変わらないことを祈ります(^-^)

なにしろ初め「Sheet2のB列を25行用意する」からして「なんで？」と思っていたぐらいです。「振替休日が発生する可能性がある祝日の欄」だったのですね。

この作業をテキストでご指導くださる大変さは十分承知しております。ファイルでアップすれば、よほど簡単ですよね。正確で的確なガイダンスをいただき、回答者様のスキルの高さを知ることができます。

一通り計算式を入れて「Sheet2のA1セル」に年号を入れたところ、祝日が計算されましたが、いくつか違いが現れました。しかし計算式をクリックして参照先が示されてわかりました。

先ず、回答者様の画像で「2016/9/22」と計算されているセルが、こちらでは「（日付不明）」と計算されました。微妙な違いが出るものなのかと思いましたが、「春分の日」「秋分の日」欄が参照先なのですね。日付を入力する必要がありました。

もう一点。画像でもこちらでも「2017/1/11」と計算された成人の日は、紙のカレンダーで「2017/1/9」になっているようです。これはどうしたらよろしいでしょうか。

回答No.3

kagakusuki

済みません、回答No.2において、

>使用していない適当なシート(ここでは仮にSheet2であるものとします)のB2～B25のセル範囲にその年度における祝日や振替休日の一覧表を作成して下さい。

と書きましたが、休日を記入するセル範囲がB2～B25で済んだのは昔の話で、2016年からは「山の日」が新たに付け加わったため、「山の日」がもし日曜日である場合には振替休日も付け加えねばならない事を考えますと、休日を記入するセル範囲を2行増やしてB2～B27のセル範囲にする必要が生じて来ます。
　そのため、回答No.2の条件付き書式に設定する3つの数式の中で

=ISNUMBER(OFFSET(A4,,-MOD(COLUMN()-COLUMN($A$3),2)))*COUNTIF(Sheet2!$B$2:$B$25,OFFSET(A4,,-MOD(COLUMN()-COLUMN($A$3),2)))

となっている数式を、

=ISNUMBER(OFFSET(A4,,-MOD(COLUMN()-COLUMN($A$3),2)))*COUNTIF(Sheet2!$B$2:$B$27,OFFSET(A4,,-MOD(COLUMN()-COLUMN($A$3),2)))

の様に変更して下さい。

>祝日にも色がついているのですが、1,2,3月は赤く塗りつぶしてくれません。
2017年に変わるためですね？

　はい、その通りです。2016”年度”の1月～3月の祝日は、2017年の1月～3月の祝日ですので、2017/1/1等の様に年から入力するように御願い致します。
　又、祝日を記入する際には、年が表示されていませんと、何年の日付を入力したのかが判り難く、間違いの元となりやすいので、日付の入力欄となるセルの書式設定の表示形式は、月日だけの表示ではなく、年月日が表示される形式にされた方が宜しいかと思います。

　それで、

>祝日や振替休日の日を自動的に計算して表示させる

の件ですが、まず、Sheet2のA1セルに次の関数を入力して下さい。

=IF(ISERROR(1/((Sheet1!$A$1&"年2月")-"2007/1">0)),"",YEAR(Sheet1!$A$1&"年2月"))

　次に、Sheet2のB1セルに「年度の祝日・休日」と入力して下さい。
　次に、Sheet2のB2～B27のセル範囲のセルの書式設定の表示形式を[日付]の

2001/3/14

などの年月日を表示する形式に設定して下さい。

　次に、Sheet2のA2セルに「昭和の日」と入力して下さい。
　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1,4,29),"")

　次に、Sheet2のA3セルに「振替休日」と入力して下さい。
　次に、Sheet2のB3セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(B2)=1)),B2+1,"")

　次に、Sheet2のA4セルに「憲法記念日」と入力して下さい。
　次に、Sheet2のB4セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1,5,3),"")

　次に、Sheet2のA5セルに「みどりの日」と入力して下さい。
　次に、Sheet2のB5セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1,5,4),"")

　次に、Sheet2のA6セルに「こどもの日」と入力して下さい。
　次に、Sheet2のB6セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1,5,5),"")

　次に、Sheet2のA7セルに「振替休日」と入力して下さい。
　次に、Sheet2のB7セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(1/(WEEKDAY(B6)<4)),ISNUMBER(B6)),B6+1,"")

　次に、Sheet2のA8セルに「海の日」と入力して下さい。
　次に、Sheet2のB8セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1,7,21-WEEKDAY(DATE($A$1,7,0),3)),"")

　次に、Sheet2のA9セルに「山の日」と入力して下さい。
　次に、Sheet2のB9セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2016/1/1">0)),DATE($A$1,8,11),"")

　次に、Sheet2のA10セルに「振替休日」と入力して下さい。
　次に、Sheet2のB10セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(B9)=1)),B9+1,"")

　次に、Sheet2のA11セルに「敬老の日」と入力して下さい。
　次に、Sheet2のB11セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1,9,21-WEEKDAY(DATE($A$1,9,0),3)),"")

　次に、Sheet2のA12セルに「国民の休日」と入力して下さい。
　次に、Sheet2のB12セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(ABS(B$13-B$11)=2)),(B$11+B$13)/2,"")

　次に、Sheet2のA13セルに「秋分の日」と入力して下さい。
　次に、Sheet2のB13セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),IF(COUNTIFS($E:$E,">="&INT($A$1*365.242015488991-693691.937712636-1),$E:$E,"<="&INT($A$1*365.242015488991-693691.937712636+1)),LARGE($E:$E,COUNTIF($E:$E,">="&INT($A$1*365.242015488991-693691.937712636-1))),IF(MOD($A$1*365.242015488991-693691.937712636+"1:00",1)<"1:00"*2,"(日付不明)",INT($A$1*365.242015488991-693691.937712636))),"")

　次に、Sheet2のA14セルに「振替休日」と入力して下さい。
　次に、Sheet2のB14セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(B$13)=1)),B$13+1+(B$11-B$13=1),"")

　次に、Sheet2のA15セルに「体育の日」と入力して下さい。
　次に、Sheet2のB15セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1,10,14-WEEKDAY(DATE($A$1,10,0),3)),"")

　次に、Sheet2のA16セルに「文化の日」と入力して下さい。
　次に、Sheet2のB16セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1,11,3),"")

　次に、Sheet2のA17セルに「振替休日」と入力して下さい。
　次に、Sheet2のB17セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(B16)=1)),B16+1,"")

　次に、Sheet2のA18セルに「勤労感謝の日」と入力して下さい。
　次に、Sheet2のB18セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1,11,23),"")

　次に、Sheet2のA19セルに「天皇誕生日」と入力して下さい。
　次に、Sheet2のB19セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(($A$1&"/1/2")-"2007/1/1">0)),DATE($A$1,12,23),"")

　次に、Sheet2のA20セルに「振替休日」と入力して下さい。
　次に、Sheet2のB20セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(B19)=1)),B19+1,"")

※まだ途中なのですが、このサイトの回答欄には4000文字までしか入力する事が出来ない仕様となっていて、そろそろその限界を超えてしまうため、残りは又後で回答する事に致します。

回答No.2

kagakusuki

回答No.1です。

　ついでに、A4～X34のセル範囲のセルの色を自動的に変える方法に関してもお伝えしておきます。
　今仮に、土曜日の日のセルは水色、日曜日の日のセルは桃色、祝日や振替休日の日のセルは赤色に塗りつぶすものとします。

　まず、使用していない適当なシート(ここでは仮にSheet2であるものとします)のB2～B25のセル範囲にその年度における祝日や振替休日の一覧表を作成して下さい。
　次に下記の操作を行って、A4～X34のセル範囲のセルに条件付き書式を設定して下さい。

A4セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=ISNUMBER(OFFSET(A4,,-MOD(COLUMN()-COLUMN($A$3),2)))*WEEKDAY(OFFSET(A4,,-MOD(COLUMN()-COLUMN($A$3),2)))=1

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある桃色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=ISNUMBER(OFFSET(A4,,-MOD(COLUMN()-COLUMN($A$3),2)))*WEEKDAY(OFFSET(A4,,-MOD(COLUMN()-COLUMN($A$3),2)))=7

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある水色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=ISNUMBER(OFFSET(A4,,-MOD(COLUMN()-COLUMN($A$3),2)))*COUNTIF(Sheet2!$B$2:$B$25,OFFSET(A4,,-MOD(COLUMN()-COLUMN($A$3),2)))

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄を3箇所とも

=$A$4:$X$341

に変更（カーソルとマウスの左ボタンによる範囲選択が使えます）
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　条件付き書式の設定方法は以上ですが、Sheet2のB2～B25のセル範囲に、春分の日と秋分の日を除く、祝日や振替休日の日を自動的に計算して表示させる事も不可能ではありませんが、その事に関しても御伝えした方が宜しいでしょうか？(但し、昔作ったものを手直しするのに少々時間を要します)

お礼

誠に丁寧なご指導をありがとうございます。
天文台から法律の知識まで必要になるとは。
カレンダーというのは、こんなに奥が深いものだったのですね。
回答No.４については先程まで、

現在、この回答はサポートで内容を確認中です。
ご迷惑おかけいたしますが、今しばらくお待ちください。

となっていました。こんな表示は始めて見ました。
もしかして秋分の日というのは「作ってはいけない関数」だったりするでしょうか(^-^)
これから作業開始します！

あと、お願いがあります。これをVector等で公開していただけないでしょうか。「解決！ゴミ当番表（週替わり）万年カレンダー付き」
全国の町内会が泣いて喜びます。

補足

ありがとうございます。土日に色がつきました！
祝日にも色がついているのですが、1,2,3月は赤く塗りつぶしてくれません。
2017年に変わるためですね？
祝日の配列を以下のようにしてみましたが反応してくれませんでした。

4月29日　セルB2
5月3日
5月4日
5月5日
7月18日
9月19日
9月22日
10月10日
11月3日
11月23日
12月23日
1月1日
1月11日
2月11日
3月20日
3月21日