勤怠管理システムが集計した実労働時間と、打刻時刻から再計算した労働時間が合わない日があります。給与計算シートを作る中で、手動で修正された日の打刻時刻が実態と合っていない、という場面がありました。
困った場面
正規の打刻（ICカードやアプリ）ではなく、手動で修正が入った日があります。その日は、出退勤の打刻時刻欄に実際の時刻が入らないことがあります。打刻データなしを示す「-」になることもあれば、一見それらしいのに実態と噛み合わない時刻になることもあります。
一方、システムが集計した実労働時間そのものは正しい値でした。そのため、打刻時刻から実労働時間を逆算すると、この日だけ結果が合いません。
こうした日は見逃さず、人の目で確認したいところです。そこで、不整合を検知するフラグ列を作りました。
想定読者
- 勤怠管理システムの集計値と、打刻から再計算した時間を突き合わせている方
- 打刻を15分単位で丸めている方（出勤は切り上げ、退勤は切り下げ）
- 例外分岐を増やさず、確認すべき行だけを拾いたい方
解決の考え方
システムの集計値（丸め前の実労働時間）と、丸めた打刻から計算した時間を比べます。差が、丸めで生じうる範囲を超えたらフラグを立てます。
フラグは不整合を自動で直すものではありません。目視で確認する行を絞るための列です。
フラグ列の数式
列は次の構成です。
| 列 | 内容 |
|---|---|
| A・B | 出勤打刻・退勤打刻（文字列） |
| C | 休憩時間 |
| D | システム集計の実労働時間 |
| E・F | 出勤丸め・退勤丸め |
| G | 差（分）。確認用で、フラグの判定には使いません |
| H | フラグ |
E2: =IFERROR(CEILING(TIMEVALUE(A2),TIME(0,15,0)),"")
F2: =IFERROR(FLOOR(TIMEVALUE(B2),TIME(0,15,0)),"")
G2: =IFERROR(ROUND(((F2-E2-C2)-D2)*1440,0),"")
H2: =IF(AND(E2<>"",F2<>"",IFERROR(ABS((F2-E2-C2)-D2)>TIME(0,30,0),FALSE)),"打刻丸め不整合","")ABSを使うのは、丸め後の計算値がシステム集計値より大きい場合も小さい場合も拾うためです。丸め列が空のときは、フラグを立てません。
実際に5行並べると、次のようになります。
1行目は、丸め後がシステム集計より16分短くなっています。これは丸めによる正常な差で、フラグは立ちません。3行目は、打刻が17:58〜18:02なのに集計は7:45です。差が465分あるので、フラグが立ちます。
閾値30分の根拠
出勤は切り上げ、退勤は切り下げです。どちらも労働時間を短くする方向に働きます。それぞれの丸め幅は15分未満なので、合計は30分未満になります。
実機で確かめました。出勤8:00〜9:00、退勤16:00〜17:00の1分刻み3,721通りで、丸め前の差と丸め後の差の開きは0〜28分でした。30分以上になる組み合わせはなく、閾値30分でフラグが立つ行は0件でした。
秒まで含めても同じです。出勤8:45:00〜9:00:00、退勤16:00:00〜16:15:00を10秒刻みで8,281通り調べると、最大は29分40秒でした。
丸めの順序で結果が変わる仕組みは、【Excel関数】時間の丸めは順序で結果が変わる理由と対処で解説しています。
閾値を小さくすると、正常な行まで拾います。1分刻み3,721通りのうち、閾値ごとにフラグが立つ組み合わせは次のとおりです。
| 閾値 | 正常な打刻なのにフラグが立つ組み合わせ |
|---|---|
| 15分 | 1,456通り |
| 20分 | 576通り |
| 25分 | 96通り |
| 30分 | 0通り |
例えば出勤8:48・退勤13:34・休憩1時間の場合、システム集計は3:46、丸め後は3:30です。差は16分なので、15分の閾値では正常な行がフラグになります。
なお、検証環境はExcel 16.0（日本語環境）です。他のバージョンとMac版は未検証です。丸め単位は15分だけ、休憩は1時間だけを試しています。
IFERRORとANDを組み合わせる理由
ANDは、先頭の条件が偽でも、すべての引数を計算します。丸め列が空文字のとき、F2-E2-C2は#VALUE!になります。この#VALUE!がANDに伝わると、フラグ列全体がエラーになります。実際、IFERRORなしでANDに渡した行はエラーになりました。
そこで、差の判定をIFERRORで包み、エラーならFALSEにしています。ANDの引数の評価は、【Excel関数】OR・ANDは短絡評価されない原因と対処にまとめています。IFERRORの仕様は、Microsoftサポートの IFERROR 関数のページを参照してください。
よくある失敗
1. 閾値を丸めの最大誤差より小さくする
正常な行が大量にフラグになります。上の表のとおり、15分なら1,456通りが該当します。
2. 打刻が空の行も拾えると思い込む
この式は、丸め列が空なら何も返しません。打刻が「-」の行は、フラグが立ちませんでした。打刻欠損を拾うなら、別のフラグが必要です。
3. システム集計値が文字列のとき
集計値が「-」の行は、IFERRORがFALSEにするため、フラグが立ちませんでした。空欄の行は0として計算されるため、フラグが立ちました。
4. 丸め列にIFERRORを付けない
打刻が「-」だと、CEILING(TIMEVALUE(A2),…)が#VALUE!になります。その結果が参照されて、フラグ列にもエラーが広がります。
応用例
フラグが立った行を目視で確認し、正しい値を手入力で確定する運用にすると使いやすくなります。手入力と自動計算の共存は、【Excel】手入力と自動計算を共存させる上書き優先列の設計の方法と組み合わせられます。
まとめ
- システム集計値と打刻の再計算値を比べ、差が閾値を超えた行にフラグを立てる
- 閾値は、丸めで生じうる最大の差（30分未満）に合わせる。実測では分単位で最大28分
ANDは全引数を評価するので、IFERRORで守る- 打刻が空の行や、集計値が文字列の行は、この式では拾えない
コメント