【Excel関数】h:mm表示の時間が合計とずれる原因と秒の端数

h:mmで表示した時間が合計とずれる原因と、ROUNDで分に確定させる式を示すアイキャッチ Excel関数

年間の所定時間を所定日数で割って、1日の所定時間を出す。給与計算のシートでは、よくある計算です。結果はh:mmで表示するのが普通ですが、表示どおりに足しても、SUMの合計とは数分ずれることがあります。手元の実装でも、割り算で出した所定時間に秒の端数が残っていて、丸めの処理を入れました。この記事では、h:mm表示が秒を隠す仕組みと、合計や比較がずれる条件、対処を実測で整理します。15分刻みの打刻を丸める話は、【Excel関数】時間の丸めは順序で結果が変わる理由と対処で扱っています。この記事は「割り算で出した値」が対象で、条件が違います。

先に結論

  • h:mm表示は、秒を切り捨てて見せるだけで、値は変わらない
  • 表示どおりに足した値と、実際の合計がずれる。比較(=、>=)も、見た目と中身が食い違う
  • 秒まで見るならh:mm:ss。中身を確定させるなら、ROUNDなどで丸める
  • 端数の原因は浮動小数点ではなく、割り算の結果が分の倍数でないこと

h:mm表示は秒を隠す

説明用の数字で、年間の所定時間(時間)を所定日数で割り、24で割ってシリアル値にします(【Excel】日付が数字になる仕組みとVBA・PQでの扱い)。=1957/244/24は8:01:14でした。h:mmの表示は「8:01」で、秒は見えません。

h:mmは、秒を四捨五入せず切り捨てて見せました。8:00:00から8:00:59までの60通りは、すべて「8:00」と表示されました。7:59:31は「7:59」、23:59:59は「23:59」です。見た目が同じでも、値は違います。=1957/244/24=TIME(8,1,0)はFALSEですが、TEXT(…,"h:mm")で文字にして比べるとTRUEになります。

割り算(時間÷日数)h:mmの表示h:mm:ssの表示
1957÷2448:018:01:14
2010÷2518:008:00:29
1990÷2497:597:59:31
2100÷2628:008:00:55
2000÷2458:098:09:48
2088÷2618:008:00:00

表示どおり足しても、合計が合わない

上の表の最初の5行を、h:mmで表示して足します。表示は8:01・8:00・7:59・8:00・8:09で、目で足すと40:09です。SUMの結果は、[h]:mm表示で40:11、[h]:mm:ss表示で40:11:56でした。2分ずれます。20日分なら、表示の8:01を20回足すと160:20ですが、実際の値の合計は160:24でした。

ずれるのは、合計が表示ではなく、秒を含む実際の値で計算されるためです。比較も同じで、1990÷249は7:59:31なので、>=TIME(8,0,0)がFALSEになります。

原因は浮動小数点ではない

端数は浮動小数点のずれではないか、と疑われがちです。そこで、15分の倍数になる割り算を全部調べました。時間は1500〜2800、日数は200〜300の範囲で、結果が0.25時間刻みになる1,074通りです。n/日数/24と、15分の倍数を直接24で割った値を比べると、不一致は0件でした。2088÷261のように割り切れれば8:00:00で、=2000/250/24=TIME(8,0,0)もTRUEです。端数が出るのは、割り算の結果がそもそも分の倍数にならないときです。

対処1:h:mm:ssで秒まで表示する

表示形式をh:mm:ssに変えると、秒の端数が見えます。ずれに気づくための手段で、値は変わりません。合計も変わりません。

対処2:ROUNDで分に確定させる

=ROUND(1957/244/24*1440,0)/1440

1440は1日の分数です。結果は8:01:00で、=TIME(8,1,0)と一致します(TRUE)。先の5行にこの式を入れると、表示は8:01・8:00・8:00・8:01・8:10になり、SUMは40:12、h:mm:ssでも40:12:00でした。表示どおりに足した値と合計が一致します。0分から23時間59分までの1,440通りでも、丸めた値のh:mm表示が期待どおりの文字になりました。

注意点は、丸めると表示が変わることです。h:mmの表示は切り捨てなので、1990÷249(7:59:31)は「7:59」でした。ROUNDで丸めると8:00:00です。切り捨てを確定させるなら、ROUNDDOWN・INT・FLOOR(値,TIME(0,1,0))を使い、いずれも7:59:00でした。最も近い分にするならMROUND(値,TIME(0,1,0))で8:00:00です。切り上げ・切り捨て・四捨五入のどれにするかは、運用のルールで決めます。この記事では断定しません。

15分単位に丸める(MROUND・CEILING・FLOOR)

丸める単位は、分でなくても指定できます。15分単位ならMROUND(値,TIME(0,15,0))で、=MROUND(1957/244/24,TIME(0,15,0))は8:00:00になり、=TIME(8,0,0)と一致します(TRUE)。仕様はMROUND 関数(Microsoft サポート)にあります。

同じ割り算の結果を、そのまま・MROUND・CEILING・FLOOR・ROUNDで15分に丸めたときの違いを並べた表

MROUNDは最も近い15分、CEILINGは切り上げ、FLOORは切り捨てです。ROUNDで書くときは、15分が1日の96分の1なので、=ROUND(値*96,0)/96とします。1957÷244(8:01:14)はMROUND・FLOOR・ROUNDが8:00:00、CEILINGが8:15:00でした。2000÷245(8:09:48)はMROUND・CEILING・ROUNDが8:15:00、FLOORが8:00:00でした。

7分30秒ちょうどの境目は、MROUNDで8:07:30が8:15:00になりました(今回の環境の結果です)。Microsoftのページには、小数の倍数でちょうど中間のときの丸め方向は定義されていないとあります。境目の値が出うる計算では、この結果に頼らず、丸め方を決めて使ってください。8:07:29は8:00:00でした。

丸める位置で合計が変わる

1日1957÷244時間(8:01:14)を20日分足した合計は、丸め方で3通りに分かれました。

方法20日分の合計
丸めない160:24:35
1日ごとに15分へMROUND、そのあと合計160:00:00
合計してから15分へMROUND160:30:00

どれが正しいかは、運用のルール次第です。日ごとの値を表示するなら、割り算の直後に丸めておくと、日ごとの値と合計が一致します。丸める位置を先に決めておくのが安全です。

エラー値と倍数0

  • MROUNDは、数値と倍数の符号が違うと#NUM!になりました(数値が負で倍数が正、数値が正で倍数が負の両方)。両方が負なら、値が返りました
  • 倍数を0にすると、MROUNDとCEILINGは0、FLOORは#DIV/0!でした
  • 負の数のCEILINGとFLOORは、今回の環境では#NUM!にならず、値が返りました(-0.3333を15分の倍数に丸めると、CEILINGは-31/96、FLOORは-32/96)

確認した範囲

Microsoft 365(Excel 16.0、日本語版)で確認しました。Excel 2021より前のバージョン、Mac、他の言語版は未検証です。年間の所定時間や日数は説明用の数字で、実際の制度の扱いは断定していません。

まとめ

割り算で出した時間をh:mmで表示すると、秒が切り捨てで隠れます。表示どおりに足しても合計がずれ、比較も食い違います。原因は浮動小数点ではなく、割り算の結果そのものです。まずh:mm:ssで秒を確かめ、合計や比較に使うなら、ROUNDやMROUNDで丸めて中身を確定させます。丸め方と丸める位置は、先に決めてください。

コメント

タイトルとURLをコピーしました