休日表示の4回目。今回は「春分・秋分の日」のセルに自動で背景色を入る方法の紹介です。地球の自転・公転から計算で算出されるので、毎年日付が変動する祭日です。
もくじ
自動で背景色を付ける
自動で色付けを行うには、「条件付き書式」の機能を使います。
「条件付き書式」を使って、セルに背景色を付ける方法は、こちらの記事を参考にしてください。
背景色を付ける方法自体は、全く同じで、条件分岐の方法が変わるだけです。
「祭日」の背景色も「日曜日」と同じ赤色で、自動適用させていきます。
春分・秋分の日セルに背景色をつける
「春分・秋分の日」の計算方法については、下記サイトなどを参考にしています。
現在は、1980年 〜 2099年に当てはまりますので、実際に使用する計算式は、このようになります。
春分の日： 20.8431 + 0.242194 × ( 西暦年 – 1980 ) – (( 西暦年 – 1980 ) / 4 )
秋分の日： 24.2488 + 0.242194 × ( 西暦年 – 1980 ) – (( 西暦年 – 1980 ) / 4 )
春分の処理
上記の式は、天文学上の計算で「太陽が春分点または秋分点（天の赤道と黄道が交差する点）を通過する日」を算出したもので、法律の定める「春分・秋分の日」とは違いますが、計算結果的には一致するはずです。
この式を元に、春分の日になる可能性がある日付のセルに、それぞれ設定していきます。
春分の日になる可能性のある日付は、例年であれば 20、21日あたりが多いかと思いますが、計算上では 20 〜 23日の間になります。
３月の 20 〜 23日のセルに、「春分の日であれば背景色を付ける」という設定を行います。
例として、23日の設定をしていきます。
23日の背景色を付ける範囲を選択します。
ホームタブの「条件付き書式」から、「新しいルール(N)…」を開き、「数式を使用して、書式設定するセルを決定」を選択します。
数式欄には、このように入力しています。
=AND(MONTH($D$5)=4,INT(20.8431+0.242194*(YEAR($D$5)-1980)-INT((YEAR($D$5)-1980)/4))=23)
内容は、次の通り。
| コード | 意味 |
|---|---|
| AND(MONTH($D$5)=4 | D5 が 4月 および |
| INT（ | 端数切り捨て |
| 20.8431 | 1980年の春分点（1980年の20.8431日目 = 20日 20時14分04秒) |
| + 0.242194 | プラス 1年の端数（1年=356.242194なので0.242194日=5時間48分46秒） |
| * (YEAR($D$5)-1980) | 掛ける 1980以降の年数 |
| – INT( | マイナス 端数切り捨て |
| (YEAR($D$5)-1980)/4) | 閏年の数（1980年以降の年数割る4） |
| =23) | イコール 23日 であれば |
相変わらずわかりにくい説明ですが、「ふ～ん」くらいの理解でOKです。
天文学の計算なので簡単に、「指定月および計算結果が23日だったら処理が実行される」くらいの理解で問題ないと思います。
入力して、書式(F)…を押します。
「塗りつぶし」タブの「背景色(C):」から、任意の色を選びます。色は好みでわかりやすい色を選んでください。
OKを押して、設定を保存です。
これで、23日の設定ができました。
同様の設定を 20 〜 22日のセルにも設定しますが、20日はこの表では当月、23日は前月となっているため注意が必要です。
23日はこの表では前月となるため 4 としています。
20日は当月なので、最初の「AND(MONTH($D$5)=4 」の値を ”=4” ではなく ”=3” として3月を入力します。
=AND(MONTH($D$5)=3,INT(20.8431+0.242194*(YEAR($D$5)-1980)-INT((YEAR($D$5)-1980)/4))=23)
秋分の日の処理
秋分の日も同様に、上記数式を参考に計算式を設定します。
秋分の日になる可能性のある日付は、22 〜 24日になります。
該当のセルに設定をしていきます。
数式欄には、下記のように入力します。
=AND(MONTH($D$5)=4,INT(23.2488+0.242194*(YEAR($D$5)-1980)-INT((YEAR($D$5)-1980)/4))=23)
内容は、次の通り。
| コード | 意味 |
|---|---|
| AND(MONTH($D$5)=4 | D5 が 4月 および |
| INT（ | 端数切り捨て |
| 23.2488 | 1980年の秋分点（1980年の23.2488日目 = 20日 5時58分16秒) |
| + 0.242194 | プラス 1年の端数（1年=356.242194なので0.242194日=5時間48分46秒） |
| * (YEAR($D$5)-1980) | 掛ける 1980以降の年数 |
| – INT( | マイナス 端数切り捨て |
| (YEAR($D$5)-1980)/4) | 閏年の数（1980年以降の年数割る4） |
| =23) | イコール 23日 であれば |
秋分点の値が変わるだけで算出できます。
以降の書式(F)…ボタンからの色選択は、上記と同じです。
ここまで設定が完了すると、23日の条件付き書式ルールの管理を見てみると、こんな感じになっています。
上から、春分の日、秋分の日、固定日の休日、日曜日の設定です。
まとめ
今回までの設定が問題なく入力できていると、入力フォームに「年月」と「最初のシフト」を入れるだけで、自動で曜日変更、シフト表示、公休表示、日祝日表示が出来るようになっています。
これで自動入力シフト表の完成です。
随分長い期間かかってしまいましたが、今回でこの連載も最終回と致します。
前提が単純なシフトなので、それほど難しいことをしているわけではありませんが、何かの参考にしていただけると幸いです。
【Excel2010】最小限の入力で自動作成するシフト表 第1回【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第2回 日付Part1【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第3回 日付Part2 月末処理【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第4回 曜日Part1 平常日【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第5回 曜日Part2 月末処理【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第6回 シフトPart1 通常週【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第7回 シフトPart2 月度始め【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第8回 シフトPart3 月末処理1【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第9回 シフトPart4 月末処理2【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第10回 シフトPart5 月初処理【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第11回 休日表示Part1【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第12回 休日表示Part2【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第13回 休日表示Part3【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第14回 休日表示Part4【関数】
【Excel2010】最小限の入力で自動作成するシフト表 第15回 休日表示Part5 最終回【関数】
