ボランティア
今日は一日、Excelで表の作成。横軸に日付と曜日が並んで、縦軸に利用者さんの名前が並ぶと。そして、休日の欄(土日祝日他)は全て黄色で塗りつぶす。
縦軸の利用者さんの名前や種別は他のシートから引用でOK。相変わらず1行のデータを2行に反映させるようなことをやっているので4月8日に書いた方法で(今度は気分でINT使いましたが。TRUNCの5文字よりもINTの3文字の方が見やすいし)。
で、横軸は年と月を入れるとそれに対応した日付*1(4月は30日までとか)と曜日が出てくる。ユーザー定義の表示形式にして、日付はd、曜日はaaaで簡単に表示。日付を切るのは29日以降だけ考えれば良くて、例えば、31日なら、IF(DAY(28日のセル+3)<4,"",28日のセル+3)でよし。
それで、土日祝日の列はざーっと黄色く塗りつぶすと。条件付き書式でそれは簡単にできますね。土日の判定はWEEKDAY(日付の入っているセル,2)>=6*2でOK・・・。
でもここでボランティア先では力尽きた・・・。
何と言っても、祝日の判定が面倒・・・。今やってみていますが。とりあえず“休日一覧”って言うシートに列:月と行:日の休日用のテーブルを作って、祝日の場所に1を入れてみているところです。こうすれば祝日などはINDIRECT(ADDRESS(DAY(日付のセル)+1,月のセル+1,,,"休日一覧"))<>""*3で判定可能。ですが、祝日の場所に数字1を入れる作業が面倒・・・。なぜか？
http://www.h3.dion.ne.jp/~sakatsu/holiday_topic.htm
これを見ると日付が変動する祝日がすごく多いのね・・・。成人の日、春分の日、海の日、敬老の日、国民の休日(敬老の日と秋分の日に挟まれる日付がそうなるらしい)*4、秋分の日、体育の日・・・。まぁ、いちいち考えて入れていきますがね・・・。
こんなのもある・・・。
“祝日が日曜にあたるときは、その日後において、その日に最も近い「国民の祝日」でない日を休日”
- 5/3(日) → 5/6(水) が振替休日（追加）
- 5/4(日) → 5/6(火) が振替休日（追加）
- 5/5(日) → 5/6(月) が振替休日（従来通り）
とりあえずこれは簡単そうなので入れましたが。5月6日の1日前〜3日前が日曜日だったら5月6日を休日にする*5ってことで良いのですよね。ああ、まだ振替休日は入れていない。月曜日で、前日の日曜日と祝日がかぶったら休日って判定にしよう(こんなぬるい判定で良いのか？？？)。5月6日が休みになる日は祝日ってわけではないから、上で祝日の場所に1を入れると決めたので、ここは区別を付けるために2にしておこう。年末年始の休みやお盆休みも2にしておくと良いかも知れない(この施設ではいつがそうなのかとかまだ知りませんが)*6。
ってわけで振替休日、AND(WEEKDAY(日付のセル)=2,INDIRECT(ADDRESS(DAY(日付のセル),月のセル+1,,,"休日一覧"))=1)=TRUE*7。
これで振替休日入った？2006/1/2や2003/11/24はとりあえず休日になっている・・・*8。不安だがまぁよし。ああ、懸案だった5月6日の問題も2007年は日曜日、でも次の日は休みじゃない、2008年は火曜日だが5月4日、日曜日の振り替えで休日。何とか正確に動いている？
後はひたすら日付が変動する祝日に対応して休日テーブルうめて行かなきゃなぁ。第何月曜日になっている祝日は、祝日になる可能性のある7日間のうち、月曜のものがあったら、値を1にすればいいし、春分秋分は上のサイトに計算方法載っているからそれを使えばいいし・・・。簡単だけど・・・面倒・・・。本当に、祝日、全部固定されていたら良かったのに・・・(まぁ、春分と秋分は仕方ないけれど)。何でも月曜日にしすぎだよ・・・。うう、無い頭を使った。今日はこれくらいで終わりにしようかな？
しかし、気合い入れて作っている割に、出来上がりはしょぼそうだな・・・。だって本来、祝日があったら適当に日付の列選択して色変えれば済むことだもんなぁ・・・。作業量に見合わないことを明らかにしている気が・・・。
2006/06/04追記
その後、どう考えてもこういう場合にはINDIRECT(ADDRESS())よりもINDEX()使った方がいいな、ということが判明。
詳しくは↓
http://d.hatena.ne.jp/walter/20060604#1149371223
ここから下は上の続きです。
気合いで、とは言ってもごく簡単に、第2月曜、第3月曜、春分、秋分の問題は解決しました。
月曜日の問題は上にも書いてあるとおり、祝日になる可能性のある7日間のうち、月曜のものがあったら、値を1にすると言うだけのものです。第2月曜は8〜14日、第3月曜は15〜21日ですね。式としては1月8日の例で言うと、IF*9。
春分秋分も上のサイトの公式当てはめただけ。短期的に見ると、春分は20か21日(2099年まで考えると19日もあり)、秋分は22か23日(2099年までは)なので、例えば9月22日の欄にIF(秋分の公式=22,1,"")って言うのを入れたりしただけです。
でも、国民の休日の問題は解決していません。ベタにIF(OR(年数入っているセル=2009,・・・って感じで決めうちで書いてしまいましょうか？敬老の日と春分の日の条件にかぶって来るんで決めうちの方が楽に書けるかも知れません。
2026年までの国民の休日は3回とも全て9月22日のようです。2032年は9月21日のようですが。というわけで、9月22日の欄はIF(OR(AND(J22=1,J24=1)=TRUE,秋分の公式=22),1,"")*10こう書いておきました。2032年以降なんかどうでも良いです。
終わった〜。
その後、上記のHPを見ると、国民の休日の判定は2099年までの秋分の公式の範囲内では(正確に言うと2150年まで成立するらしい)、秋分が水曜日、ということだけ判定すればいいようなので、3月21日と3月22日に、自分が火曜日で次の日付に1が立っているって条件を付け足しておきました*11。これで、*8と考え合わせて、
*1:一日目だけDATE(年のセル,月のセル,1)で後は左隣+1を延々と続ける
*2:条件付き書式、1つ目
*3:条件付き書式、2つ目。今更ですが、DAY()+1や月のセル+1しているのはコメント行対策です。4月8日の記事にも訳分からない+1などがあったと思いますが、コメント行を入れているためです
*4:とりあえず2030年まででも2009年と2015年と2026年しかないから果たして対応する必要あるのか？
*5:結局5/3か5/4が5/5が(5/5は振替休日で処理できるからいらないね)日曜なら5月6日のセルに2を入れるって書き方になりましたが
*6:年末年始の休みを2にするのは振替休日にさせないために区別つけるのは分かるのですが、ちょっと考えると5月6日は2で区別する必要はないんだなぁ・・・。5月6日が日曜ならば5/3〜5/5は絶対に日曜になることは無いんだから
*7:条件付き書式、3つ目。これで3つだけ使えるという条件の項は全て埋まってしまいました。いざとなったらOR使ってつないでいけばいいのでしょうが。最後のTRUEはいらんな。DAY()に+1していないのはコメント行のことを考えると実質-1、つまり前日のを参照するためです。月のセル+1もコメントのために+1しているだけ
*8:祝日の変遷が激しく休日の目安には使えないし、そもそも過去の表が必要になるものではないので、出来上がり状態では今の祝日の制度に対応している、2003年2004年以降(2003年は5月4日が日曜だから5月6日が休日になってはいけないのね)だけ入れられるようにしておこう・・・。
*9:WEEKDAY(DATE(年の入っているセル,B$1,$A9)))=2,1,"")という感じ((ここでも、シートにコメント行があるので、コメント部分無ければ、A$1,$A8になります
*10:相変わらずコメント行のせいでずれていますが。コメント無ければAND(I21=1,I23=1)です
*11:秋分の日が水曜だったら前日を国民の休日にするという方法より、3月21日、3月22日が火曜日の場合にの次の日が秋分の日だったら国民の休日にするという方法を使う方が、3月24日・秋分の日が水曜でも3月23日が国民の休日になることはないので良さげですね。まぁ、少なくとも2150年まで3月24日・秋分の日が水曜日になることはないらしいのでどうでも良いですが
