スポンサーリンク

時間の計算 FLOOR関数

日付の計算はDATEDIFを参照してください。概ね、時間は通常の四則計算やオートサムで計算可能です。以下おかしなことが起こる一例と対策を挙げてみます。

エクセルで時間を計算するときに気を付けていただきたいことがあります。コンピュータは内部で2進数が使われているのはよくご存じだと思います。0と1の組み合わせで数字も文字も処理しています。

2進数は1の次が10です。10は10進数で2です。1桁上がると数値が倍になります。10進数は1桁上がると10倍ですね。逆に1桁下がると2進数は2分の1になります。10進数は10分の1です。2進数の0.1は1が一桁下がっているので1の2分の1で10進数でいうところの0.5です。

2進数    10進数
10        2
1         1
0.1       0.5
0.01      0.25
0.001     0.125

2進数では10進数の0.1の数値が表せません。0.25、0.125などそれ以下のものを組み合わせても10進数のちょうど0.1は作れません。

エクセルでは計算時に小数点以下は近似値が使われることがあります。小数点以下の計算を組み合わせると誤差が発生し正しい答えが出ないことがあります。

セルA1に1.2セルB2に1.1セルC1に=A1-B1と入力するします。1.2-1.1は0.1なので0.1と表示されます。(図はexcel2013)

gosa

続いてセルD1に0.1と入力しセルE1に=C1-D1と入力します。セルC1は0.1、セルD1も0.1なので答えは0になるはずですが、とっても小さい負の数と対数表現されています。とにかく0になっていません。gosa_1

セルA1からセルE1までを小数点以下の表示桁数を増やすボタンで小数点以下をどんどん表示させます。

gosa_2

どんどん、0が増えてきますが、あるところから1.2-11の答えのセルC1が、0.1ではなく0.09999999という表示に変わります。エクセルはセルに表示されていない桁で四捨五入されてい表示されます。表示桁数を増やすとと四捨五入されない数値が表示されます。

gosa_3

1.2-1.1は0.01よりもほんの少しだけ少ない数だったということがわかります。なので0.1よりほんのわずかに少ない数から0.1を引くと0よりほんの少しだけ小さいマイナスの数値になります。

エクセルでの小数点以下の数値の計算はわずかな誤差により思わぬ端数が発生することがあります。

エクセルの日付は1900年の1月1日を1として1日ごとに1増えていきます。時間は1日を24で割ったものが1時間になります。

1時間  0.0416666666666667000 (1÷24)
1分   0.0006944444444444440 (1÷24÷60)
1秒   0.0000115740740740741 (1÷24÷60÷60)

すべて割り切りず循環小数です。これにより時間の計算は場合によりおかしな結果になることがあります。とはいえ、小数点以下十数桁のことなので見た目不都合が起きることは少ないです。(またどうしたわけか都合のいい結果になるようです。)

エクセルで1秒の0.0000115740740740741に1日の86400秒を掛けると全く端数のない1になる(見える?)のは不思議です。ウィンドウズの電卓で計算すると1.00000000000000224になりますが、内部で切り捨てするか何かの処理がされているのかもしれません。

しかし、時間の計算結果の比較や単位時間での丸めで誤差が発生することがあります。

誤差とは関係なしに下のような時間計算をしてみます。労働時間を求めるのには退勤時間から出勤時間と休憩時間を引けば求まります。(0時から0時までの24時間なら)gosa_4

次に基本時間を8時間として8時間を超過したものについては残業時間とする計算式を入れます。これはあくまでもエクセルで時間計算でうまくいかないことがある例のためのものです。日々の残業時間を日ごとに切り捨てることは違法です。

実労時間から基本時間の8時間を引いて端数を15分単位で切り捨てます。(労働基準法等関連法令をあたって違法でない範囲で使用してください。)

四捨五入や桁数指定での切り上げ切り捨ての関数では15分単位での切り捨てが出来ません。

FLOOR関数

基準値の倍数未満を切り捨てます。

FLOOR(数値, 基準値)

FLOOR(実労時間-基本時間、15分)で残業時間の15分単位の丸めが出来ます。3行目の残業時間は=FLOOR(D3-$E$1,”0:15″)という式で求まります。

gosa_5

しかし、4行目の計算結果がおかしくなっています。実労時間計算と基本時間の差の計算時に生じた誤差が15分単位の倍数の30分よりほんの少し小さくなってしまったからです。エクセルの画面上では時間:分で表示されているものも実際は小数点以下の値を持った数値です。表示上、端数が見えなくなっているだけです。

あくまで1例として次の方法があります。FLOOR関数で丸める際に見えている8:30や9:30などの文字列で計算してみます。TEXT関数を使用して8:30に見えている0.354166666666667 という数値ではなくて8:30という文字列で計算します。

=FLOOR(TEXT(D3,”hh:mm”)-TEXT($E$1,”hh:mm”),”0:15″)

で計算します。

gosa_7で4行目の計算も合いました。

これ以外にも

  • エクセルの設定で『表示桁数で計算する』にチェックする
  • 誤差のありそうなところに微小な少数(0.0000001など)を加算、減算しておく
  • 少数が出ないように千倍や1万倍して計算し後ほど千分の一や1万分の一に戻す

があります。ほかにもあると思いますし、先ほどの計算式も含めすべての場合に正しい結果が出るかは確認しきれていません。

累積時間を計算すると24時間以上の部分が表示されないことがある(40:00が16:00と表示されたりする)のでセルの書式設定で時間の部分を[h]とすると24時間以上も表示されます。

gosa_8

まとめ

  • エクセルは小数点以下の数値の計算は微小な誤差が生じることがある。
  • 通常の時間の計算は通常の四則計算やオートサムで問題ありません。
  • 時間は内部で少数として計算されるの思わぬ結果になることがある。計算結果と手入力の時間の比較や丸めなど。
  • 単位で切り捨てのFLOOR関数を時間計算で使用すると誤差が表面化することがある。(切り上げはCEILING関数がある)
  • TEXT関数で文字列としてから処理するとうまくいくことがある。理屈上うまくいくと思うが、何かの原因でうまく計算できないかもしれません。使用時はよく確認してください。
  • もし残業代の計算をする場合は計算結果に間違いがないかよく確認し関係法令に基づいて処理してください。未払い請求で莫大な負担がかかるようになるかもしれません

 

 

スポンサーリンク

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です