Excelで時間管理や検算を行う時のめんどくせぇ部分

#■Excelで時間管理するときのアレコレ
まず、時間の検算をそもそもExcelでやるのが正解なのかわからないんだけどさ・・・
労務の業務をやっていると、検証（検算）時にExcelで労働時間の計算とかやるんです。

が、細かな小数点以下の差異で、システムのデータと突合させたときにTRUEになってくれないってことがよくあって、その相談をよく受ける。
解決方法はいくつかあるにはあるのだけど、要件が複数発生すると実際にどれがスマートなのかわかりづらいし、実現できたとしても結構冗長だったり。
というかそもそも作業やってるねーちゃん達は「シリアル値なにそれ？」みたいな感じなので、できるだけ簡単にしておかなければ相談が絶えない。
ってことで、毎回「このケースならこれだったかな？どうだったかな？」と考えるよりまとめておきたいので記事にしてみる。

##前提とか
　・労務にとって労働時間管理＝お金の管理と同義
　・Excelの時間管理はシリアル値と呼ばれる小数（0～0.999888426）
　・労働時間は24時間超えるので、必然的に整数の部分も関係してくる
　 （1.0=24H,2.0=48H)
　・それを無理やり書式設定([hh]:mm)で累積表示させてる
　・でも時間は、12進数だったり、24進数だったり、10進数だったりする
　・分秒は、60進数（ミリ秒は使わないのでスルー）
　・残業代とかの金額は10進数なので、時間も10進数に変換することがある
　・突合チェックもする、そのあと計算にも使う
　・Excelでやんなきゃいいじゃんってのはナシ

そもそもなんでシステム通りに労働時間計算や給与支払いが行われないかというと、従業員さんが正しく勤怠を締めてないとか、会社によって特殊な裁量があって勤怠管理上と給与支払い上で利用する時間が違うからってのと、勤怠システムと給与システムが一緒（同いつメーカー）のもの利用されてないケースが結構あるから、連携時にずれちゃったりもする。
例）AM休と備考があるのに、AM休の申請してなくて
　　遅刻が発生してたり、通常労働判定になって残業に加算されてたり、たりたり

システムはちゃんと利用している・・・が、それで全てを包括できているわけじゃないってことです。
その対策にExcelを利用するのが正解かってのは別の話ってことで。

##ゴールはどこだ
大きく分けると2つ。
　１）システムの結果と突合させて差異を探ること
　２）修正後の勤怠から、各種金額の理論値を作成すること（10進数に直して単価を乗算）

###１．文字列で管理しちゃうパターン
シリアル値だったり、検算結果（1/24とか）を利用すると、小数点以下の細かな差異が発生してEXACTとかでTUREになってくれない。
「11:15」はシリアル値で0.46875。計算で求めると0.46875012なので、まぁ一致しないよね。
ならば、文字として管理しちゃえばいいじゃないかというありきたりな発想。

=TEXT(値,"[hh]:mm")

こう記述しちゃえば、比べるときにも楽ちんちん。
文字に変換していても、計算するときには書式設定が時間表示ならExcelが自動的に解釈してくれるので問題ない（横暴）。

と思いきや、なんかたまに桁落ちする。
とあるツールで、差を取った時の答えが"0：00"になるパターンで桁落ち？が発生した際には、マイナス値に入るらしくエラーになり、その後の処理に影響が発生してしまった。
で、これが100％再現するわけではないのでなんか厄介（すぐ気付ければいんだけど）。
IF関数で判断してあげればいいだけなんですけどね。

しかし、いくら自動で判別してくれるとは言え、やはり文字を計算に利用しようという考え自体がそもそも間違いと思ったほうがいいかもしれない。
ただそうなると、計算に利用する際にはいちいちtimevalue関数でシリアル値に戻してから計算しなきゃいけないのが面倒になる。

###２．10進数で管理しちゃうパターン
金額まで算出するときは、最終的に10進数に直すので、10進数で最初から管理したい。
けど、「12.31」が「12:19」かどうかを人が直観的に判断するのは無理なんで、やっぱり日付形式で計算は行っていくほうがいいんじゃないかなという結論に至ってしまう。
ただ小数点2桁で区切れるので滅茶苦茶な計算方法になることはほぼないのは利点ではないかな。

ある程度元データの信頼性が高い場合は、隣の列に10進数変換後の値を持っておいて
こちらで突合するという方法がとれる。
個人的にはこれが好きなのだけど、列が増えるのが・・・という意見が絶えない。

###３．端数のないシリアル値に変換しておくパターン
TEXT 関数と TIMEVALUE 関数を一度くぐらせるとか、Time関数使うとかでエクセル関数がはじき出したシリアル値をベースに使っていったらいいんじゃないかという考え。
計算がかかったら、それもTEXTで文字にして、TIMEVALUEでシリアルに戻す。

=TIMEVALUE(TEXT(値,"[hh]:mm"))

・・・一つ一つ これやるのかい？　というのがやはり悩みの種。
エンジニアとしては冗長かどうかはともかく正確であるほうが好ましいのだけど、この60進数ってのがなかなか厄介だ。
あと、やっぱり差を取った時の答えが"0:00"になるような場合にマイナス値になったりする。
でもってIF関数先生ががんばってくれる。

##まとめ
やっぱりケースバイケースだなと思うが、基本として
　１）突合する時は、文字列に変換しての突合を行う
　２）1÷24など、計算で少数（シリアル値）を出した場合には、特に１）を意識する
　３）可能ならば10進数変換後に計算を行う

これはシリアル値という少数を、表示形式という仕組みで時刻に見せているため
いかに端数を無視できる状況を作るかということが大事という考えによります。
このあたり、勤怠システムとかつくってらっしゃる方はどういう考えの基作ってるんですかね。

###関数だからといえど
検証基準をしっかり定めてから検証ファイルってのは作るものだなと思いますね。
バラバラ作ってあるから、相談の内容が一致してこないんだと思う。

とはいえIT齧ってきてない人に、INPUT＞PROCESS＞OUTPUTの原理を理解してもらうのは一苦労です。