Excelのシリアル値
ちょっと勤務管理表を Excel で作り直してたりする。
今の勤務先が使ってる表が、めちゃくちゃ労力を必要とする仕様なだけに、なんとかしたい。
現在の年月と出社/帰宅の時間は、入力しなくちゃ話にならないのはわかる。休憩時間の手計算も大目に見よう。
でも、日付や曜日、休日のフラグなんかも自分で入力しなくちゃダメって…
勤務管理表に工数を掛けられないっていったって、いくらなんでも端折りすぎだろう(^^ゞ
そんな難しいことを要求されるわけでもないだろうに…
とりあえず、以下に説明する方法で作ったサンプルも置いとく。
VBA マクロは一切使ってないので、ウィルスとかは気にしなくていい…はず。
VBAがど〜たら…といった注意メッセージがでたら、無効にして…の前に、誰かに改竄された可能性があるので、ファイルを開かないこと。
シリアル値とは
Excelで扱うシリアル値とは、日付や時間を扱うための値のこと。
たとえば数値で 1 ならば、1900年1月1日という意味になる。
40000で、2009年7月6日が表現できる。
日付の概念として、数字の 1 を 1 日として扱っている…というのを覚えるとわかりやすい。
シリアル値に 1 を足せば、翌日という意味になる。
じゃあ、時間は？
1日は24時間なので、Excelでは 1 を24で割った小数点の値を1時間として扱っている。
つまり 1÷24=0.041666667 が1時間を意味する。
同じ調子で、1÷24÷60=0.000694444 が1分。
さらに60で割れば 1 秒となる。
そんなシリアル値は、セルの書式設定で日付や時間として表示させてあげれば、ちゃんと見える。
Excelで勤務管理…とか、時間を扱おうというなら、これを覚えておいて損はない。
9時なら、(1/24*9)で表せるし、比較もできる。
日付を跨った帰宅時間の場合、24時間 = 1 を足せば、何時間会社に拘束されていたのか楽に算出できる。
逆に、9:30 を 9.5 に直したいときは、=(9:30のセル)/(1/24) と入れると、9.5 が出てくる。
そりゃそうだよね…。(1/24*9.5)/(1/24)という計算式だったら、9.5しか残らないもん。
このシリアル値は Excel としての仕様なので、関数で使おうが、VBAマクロで使おうが、値は変わらない。
ちなみに、シリアル値が 1 未満になると1900年1月1日未満ということになる。
なので、1900年1月0日という不思議な日付が出てくるｗ
時間だけを計算したい場合には不便なので、あんまり表には出ないようになっているけど…
そういう仕様だと思ってもらえればいいんじゃないかな。
ついでに、有名だけど1900年2月29日が出てくるのは、ロータス1-2-3との互換バグ。
相対参照と絶対参照
相対参照は、セルをコピーしたときに、参照するセルの位置が変わる参照の仕方。
最初はA1形式で関数を組むだろうから、特に説明しなくてもいいよね。
絶対参照は、セルをコピーしたときにも参照するセルの位置が変わらない参照の仕方。
勤務管理表だと、年月を参照したセルをコピー＆ペーストする機会が多いので覚えておくと楽。
=A1
と入力していたのを
=$A$1
と入力すれば絶対参照になる。
この $ の記号を、セルを表す数値の前に入れるだけで、コピペしても参照するセルの位置が変わらなくなる。
そうは言っても Excel なんで、絶対参照されているセルが移動すると、もともとそのセルを参照していた関数とかも自動で書き換わるのが楽だね*1。
今回のサンプルファイルは、テンプレートとして加工して使ってもらうのが前提。
会社で今使ってる勤務管理表に関数を取り込むなり、見栄えを整えてそのまま利用してもらったり、お好きにどーぞってな具合。
じゃなきゃ、公開なんてできないよね。
日付の入力
とりあえず、何年の何月分なのか、入力するセルは当然あるだろうね。
勤務管理をする以上、月単位で集計するはず。無いわけがない。
ここでは、年数を A1 のセル、月数を B1 のセルとして入力するものとする。
ついでに書いておくと…
| A4 | B4 | C4 | D4 | E4 | F4 | G4 |
|---|---|---|---|---|---|---|
| 日付 | 曜日 | 内容 | 出社 | 帰宅 | 勤務 | 備考 |
こんな感じで入力しておく。
そしたら、次にやることは日付を並べること。
例示として、セル A5 から縦に並べる。
計算のはじめの日だけは、シリアル値を入れておくといい。
入力されている数値からシリアル値を作るには、DATE 関数を使う。
〆日が月末なら
=DATE(A1,B1,1)
になるだろうし、〆日が20日なら
=DATE(A1,B1,21)
になるだろう。
で、〆日を20日にすると、月末の処理を変えなきゃいけないんで、1日からはじめるものと決め打ちしちゃう。
2日目以降は、1日目のセルに 1 を足していく。
セルの書式設定（セルを右クリックして、該当するリストをクリック）で、表示形式タブに分類(C)があるので、ユーザー定義を選択する。
種類(T)には d とだけ入力して OK をクリックすると、見た目には日付だけが表示される。
3月だと、ちゃんと31日まで表示されるようになる。
じゃあ、セル B1 に 2 を入れたらどうなるか？
28日の次は、翌月の1日なので 1 に戻るｗ
29日以降、〆の処理は IF 関数で条件分岐してあげればいい。
考え方としては、シリアル値で表せる月が、利用したい月と異なれば表示しない…と、単純。
シリアル値から月だけを抜き出すのが、MONTH 関数。
セルに入力するべき値の月と、勤務管理表で利用したい月とを比較して、同じなら安心して翌日。
違うなら、空白を示す "" を入力。
=IF(MONTH(A32+1)=$B$1,A32+1,"")
これを29日以降コピペすると…30日でエラーになるｗ
そりゃそうだ。比較すべき値が消えてるもんな。
じゃあ、もう一段比較を増やして、参照するセルが空白なら空白でしとけ…と。
=IF(A32="","",IF(MONTH(A32+1)=$B$1,A32+1,""))
曜日の入力
日付がきちんと表示できるようになったのなら、今度は曜日を自動で表示させよう。
一番簡単なのは、シリアル値を書式設定で曜日表示(日付で d だったところを aaa にすれば日〜土まで表示)させること。
でも、問題は他のセルから参照したときに、曜日で扱いにくいことなんだよね。
たとえば、セルが "日" で表示されていても、関数で "日" として処理されない。
そりゃそうだ。もととなるデータが曜日じゃないんだから。
ということで、関数で対応する。
シリアル値を曜日に変換した結果を出すのが WEEKDAY 関数。
シリアル値と種類を引数として入力すると、曜日に対応した数字が戻ってくる。
…そう、曜日じゃないｗ
種類の欄は、1を入れると日曜を 1 として土曜の 7 まで対応。
わかりにくいけど、こんな感じ。
1 が、1=日曜〜 7=土。
2 が、1=月曜〜 7=日。
3 が、0=月曜〜 6=日。
これを理解したなら、セルの書式設定で aaa (曜日表示の指定方法)を設定してあげれば早い。
直感的に分かりにくいのが難点。
で、これを CHOOSE 関数で、対応する曜日表示に変換してあげる。
日付の隣のほうが分かりやすいので、B5から下にこんな関数。
=CHOOSE(WEEKDAY(A5,1),"日","月","火","水","木","金","土")
もしくは
=CHOOSE(WEEKDAY(A5,2),"月","火","水","木","金","土","日")
これで、曜日表示にも対応した。
表全体（セルA5 〜 セルG35）を選択して、メニューから書式(O)→条件付き書式(D)をクリック。
アクティブになっているセルがA5の場合、数式に
=$B5="土"
と入れて、右下の書式からセルとか文字とかの色を変えれば、行がそのとおりに変わる。
ちなみに $B$5 じゃないのは、B列だけ変えないで、行は変えたい…という意図によるもの。
これ、条件を三つまで指定できるので、追加で日曜日分も設定しておく。
もちろん、数式は…
=$B5="日"
に変化させる〜♪
この時点だと B1 の月を 1 〜 12 のどれかに変えると、自動的に色まで変わる。
ちょっとした達成感♪
…あ！
31日まで無い月で、エラー（#VALUE）でてらｗ
ってことで、日付と同じ対応でごまかしとく。
B5のセルを例に挙げると、こうなる。
=IF(A5="","",CHOOSE(WEEKDAY(A5,1),"日","月","火","水","木","金","土"))
別に、29日〜30日分のセルだけでも良かったけど。
その他
ここまでやると、祝日にも対応させたくなってくる。
で、祝日の定義をググってみると…
うげ。
結果は、ここを見ると良いと思うよ。
http://www.nao.ac.jp/QA/faq/a0301.html
ん〜、やり方は沢山考えられるな〜(^^ゞ
春分や秋分を、勝手に計算する方法とかがわからんし…これは全部を計算で済ますのは、僕には無理っぽい。
振り替え休日も考慮しなきゃダメだろうし、各地域や各組織ごとに休日があるだろう。
それらに対応するのは…む〜、別シートしか思いつかない orz
公開するファイルなだけに、できるだけマクロや別シートは使わないようにしようって思ってたのにｗ
出社時間と退社（帰宅）時間で勤務時間を自動計算する必要もあるし、それなら休憩時間とか夜勤時間帯とかの考慮も必要だろうね。
フレックスによるコアタイムとか、場合によってはサマータイムなんて対応も必要？
本気で対応させようとすると、経理の人が工数を掛けてでもやる作業に思えるｗ
とりあえず、春分の日/秋分の日と同じだけ、他の変動する休日を調べてみた。
※ これは振替休日にしていないので、利用する場合は振替休日に対応してほしー。
| 西暦年 | 春分日 | 秋分日 | 成人の日 | 海の日 | 敬老の日 | 体育の日 |
|---|---|---|---|---|---|---|
| 2010年 | 3月21日 | 9月23日 | 1月11日 | 7月19日 | 9月20日 | 10月13日 |
| 2011年 | 3月21日 | 9月23日 | 1月10日 | 7月18日 | 9月19日 | 10月12日 |
| 2012年 | 3月20日 | 9月22日 | 1月9日 | 7月16日 | 9月17日 | 10月10日 |
| 2013年 | 3月20日 | 9月23日 | 1月14日 | 7月15日 | 9月16日 | 10月9日 |
| 2014年 | 3月21日 | 9月23日 | 1月13日 | 7月21日 | 9月15日 | 10月8日 |
| 2015年 | 3月21日 | 9月23日 | 1月12日 | 7月20日 | 9月21日 | 10月14日 |
| 2016年 | 3月20日 | 9月22日 | 1月11日 | 7月18日 | 9月19日 | 10月12日 |
| 2017年 | 3月20日 | 9月23日 | 1月9日 | 7月17日 | 9月18日 | 10月11日 |
| 2018年 | 3月21日 | 9月23日 | 1月8日 | 7月16日 | 9月17日 | 10月10日 |
| 2019年 | 3月21日 | 9月23日 | 1月14日 | 7月15日 | 9月16日 | 10月9日 |
| 2020年 | 3月20日 | 9月22日 | 1月13日 | 7月20日 | 9月21日 | 10月14日 |
| 2021年 | 3月20日 | 9月23日 | 1月11日 | 7月19日 | 9月20日 | 10月13日 |
| 2022年 | 3月21日 | 9月23日 | 1月10日 | 7月18日 | 9月19日 | 10月12日 |
| 2023年 | 3月21日 | 9月23日 | 1月9日 | 7月17日 | 9月18日 | 10月11日 |
| 2024年 | 3月20日 | 9月22日 | 1月8日 | 7月15日 | 9月16日 | 10月9日 |
| 2025年 | 3月20日 | 9月23日 | 1月13日 | 7月21日 | 9月15日 | 10月8日 |
| 2026年 | 3月20日 | 9月23日 | 1月12日 | 7月20日 | 9月21日 | 10月14日 |
| 2027年 | 3月21日 | 9月23日 | 1月11日 | 7月19日 | 9月20日 | 10月13日 |
| 2028年 | 3月20日 | 9月22日 | 1月10日 | 7月17日 | 9月18日 | 10月11日 |
| 2029年 | 3月20日 | 9月23日 | 1月8日 | 7月16日 | 9月17日 | 10月10日 |
| 2030年 | 3月20日 | 9月23日 | 1月14日 | 7月15日 | 9月16日 | 10月9日 |
ここから、後日「国民の休日」を思い出したので追記。
トラックバックもしたけど、自分で不便を感じたので。あえて書いておく。
| 西暦年 | 国民の休日 |
|---|---|
| 2015年 | 9月22日 |
| 2025年 | 9月22日 |
この国民の休日…もとは5月のゴールデンウィークでしか意識されなかったけど、祝日と祝日に挟まれた平日は休日にしようという法律がまだ有効…。
で、秋分の日と敬老の日が近く、さらに平日を挟んで並ぶことがある。
もちろん、そこは休日になる…ということで、この考慮も必要。
サンプルには、そこまで入れてなかったと思う…う〜ん。
まぁ、列を追記して、参照する関数を（不具合対応も混みで）修正すれば対応できると思う。
追記ここまで。
で、管理管理サンプル2.xls というのを作って、ある程度頑張ってみた。
こちらは出社時間と帰宅時間を入れると、あらかじめ設定してある休憩時間を除いた値が入るようになってる。
でも、バグがありすぎて使い物にならない orz
たとえば…
12:00〜13:00までだったら勤務は 0:00 …になるけど、12:01〜13:00だったら0:30…つまり30分勤務したってことになる。
ありえないよ…こんなの。
「単位の丸め」のところで直せるかなって思ったけど、出社を1時間単位で切り上げちゃうと、今度は 8:30 や 9:30 が出勤な会社に対応できなくなってくる。
だから、アルゴリズム自体がオカシイと言われてしまえば、そのとおり。
VBAマクロだったら、力技でいろいろ条件分岐させちゃうんだけどな…。
関数だけでやろうとすると、逆に思いつかないや(^^ゞ
マクロの便利さを、改めて思い知ったよ。
普通に残業した時間や休日出勤を残業時間に入れたり、法律で定められた夜勤時間なんかを別に集計する機能とかも考えた。
でも、考えただけで実装する気力がないや…えへ。
そっちのほうが、重要だとは思うんだけど…思うんだけど…
そもそも発端となった勤務管理表で、その辺は考慮しなくていいと断言されたので、今回は無視しちゃおう…orz
最初やりたかった「祝日を自動で休日にする」機能は盛り込めたから、自分的に満足。
振替休日の分は、確認が足らないのでもしかするとバグでまくりかもしれん。
ま、自分で作ったアルゴリズムなんだから、問題があれば直せばいいや〜♪
この先 20 年は、同じアルゴリズムを考えなきゃいけないときに楽できるだろうしｗ
祝日に関する法律が変わらない限り、20年後、また休日を調べれば済む話。
一つ目のテンプレートファイルを作るのは早かったけど、二つ目は難儀したなー。
