エクセル 時刻の計算（小数の計算） ファイルにより計算結果が異なる

時間の計算を15分単位で切下げるためFLOOR関数を使っています。
まったく同じ時間、関数なのにファイルにより計算結果が異なるので困っています。

具体例は

2016/8/04　18：00：00　に対し floor関数で15分単位で切り下げた場合、

Aのファイルは　2016/8/4　18：00：00
Bのファイルは　2016/8/4　17：45：00となります。

計算過程を確認したところ、
Aのファイルは　FLOOR(42586.75,　0.010416667) = 42586.75
Bのファイルは　FLOOR(42586.75,　0.010416667) = 42586.73958

と全く同じ関数、引数なのに、ファィルにより計算結果がことなっています。（同じPCです）

※上の42586.75　は2016/8/4　18：00：00のシリアル値
　　0.010416667は　0：15：00　と入力したセルをC5と参照したところ表示された値です。
　　
業務で使うため、計算結果がことなる理由を把握したいです。
どうぞよろしくお願いいたします。

No.9 ベストアンサー 回答者： WindFaller 回答日時： 2016/10/07 13:04

あまり、大勢の人があれこれ違うことを言ってもしょうがない話ですが、この現象を、浮動小数点(丸め)誤差といいます。

ある意味では、時間丸め誤差といっても過言ではないのは、一般の浮動小数点誤差にはない現象が含まれます。

この浮動小数点誤差は、簡単にいえば、Excelは、２進で計算されているからということになるのですが、ベテランの人でも、なかなか気が付きにくい話なのです。

対策自体は、すでに出ている内容ですが、
一体、どうしてこうなったか、それが問題ですよね。

・2016/8/4　18：00：00　この表示されている日付時間はどうやって作ったか？
ドラッグコピーなどで作りませんでしたか？

時間の元の数字というのは、１０進からすると無限小数でできていて、それをExcelでは時間表示で人間に分かるように、時間・分・秒で表示されます。その時に丸めているのですが、これを「時間丸め」といって、浮動小数点丸め誤差とは若干違う問題が含まれています。

おそらく、単独で入力したときには、間違いは出ないはずです。ところが、ドラッグコピー(フィルハンドルでドラックして連続した数字を出すこと)したり、時間値の無限小数を足し算・引き算を含め四則演算(今回のFLOOR関数も含めます)をすると、誤差が生じてしまいます。

元の数値の"18:00:00" は、若干数字が足りなくても、18:00:00と出てしまうのです。だから、[割り切れない数値]の時間値をドラッグコピーしてはいけないのです。

割り切れる数(0.5=30分,0.25=15分)

なお、日付は、整数ですから、問題は発生しません。

それでも、ドラッグコピーをしなくては大変だという時もあります。

画像でも分かるかとは思いますが、
=DATEVALUE("2016/8/4")+TIME(8,1*(ROW(A1)-1),0)
こんな数式を考えてみました。

他にも、値をコピーして、上書きにした後に、区切り位置で、改めて数値にする方法など、さまざまです。

もちろん、単独の場合は、皆様のご指摘の通り、時間文字列に、TIMEVALUE関数　や、"8:00"*1 でも、時間の数値になります。

プログラミングでは、整数法や小数点固定法(文字列にして数値に戻す)などがありますが、Microsoftでは、ROUND関数を勧めているような気がします。桁数は、必要に応じてですが、私は、昔の経験値で時間に対しては、小数点6桁を丸めるようにしています。

B4:
2016/8/4 08:02:00

=ROUND(B4,5)　42586.334720000000000 　01:59.8　　足りません。
=ROUND(B4,6)　42586.334722000000000 　01:20.0

と差が出てしまいますが、今回のFLOOR関数では、その問題は出ません。

添付画像では、1分ごとで、1時間中に、正しいのは2つしかありません。後は、全部違ってしまいました。

何かのお役に立てれば幸いです。

この回答へのお礼

浮動小数点(丸め)誤差がでるファイルとでないファイルがあるのが一番の疑問で今回質問いたしました。
その点も考慮し、アドバイスいただきありがとうございます。
誤差がでるファイルは行を挿入したり、コピーしたり、他の関数を使ったり
最初の状態から変えています。
どちらにせよ、浮動小数点(丸め)誤差対策ができていないので生じた現象なので、皆様のアドバイスをもとに修正いたします。

お礼日時：2016/10/08 00:26

No.10 回答者： angkor_h 回答日時： 2016/10/07 17:22

no.4です。

･　小数点以下だけを対象に、基準値は15分-αにする(末尾は切り捨て)。
誤差累積を24時間分以内にとどめる、という意味です。
年月日が整数、時分秒が小数点以下に当たるので、元式は、
整数部分+小数点以下の15分切り下げ値
＝QUOTIENT(年月日時分秒,1)+FLOOR(MOD(年月日時分秒,1),15分-α)

･　αは1日秒数の逆数よりも小さな値にし、秒表示に誤差を与えない。
誤差が24時間分溜まっても秒表示値に現れないようにする、という意味です。
α<1日秒数の逆数=秒/1日の秒数=0.00001157/86400=0.000000000134
よって、次の値が最小値、ということになります。
基準値=15分-α=0.010416666667-0.000000000134=0.010416666533
結果、基準値は、以下の範囲が適正値、のはずです。
0.010416666666~0.010416666534

ご質問の問題は、「0.010416667」>15分なので、
超過分が大きすぎたことが誤差を生む潜在的要因、なのです。

この回答へのお礼

アドバイスいただきありがとうございます。
実は切り下げる部分は、15分、10分、など動的に変わるので、うまく関数に組み込めるかいろいろと思考錯誤してみます。
丁寧に教えていただきありがとございます。

お礼日時：2016/10/08 00:34

No.8 回答者： GooUserラック 回答日時： 2016/10/07 09:47

計算をするときの丸め方ですが、秒単位で丸めるとしたら「=ROUND(A1*86400,0)/86400」の方がの方がよいと思います。

ちなみに「86400」は「24×60×60」です。

この回答へのお礼

通常の方法で丸めたら、その後の集計で誤差がでて丸めるのをやめた経緯があります。

この方法でまるめるのも、試してみます。教えていただきありがとうございました。

お礼日時：2016/10/08 00:16

No.7 回答者： d-q-t-p 回答日時： 2016/10/07 09:46

時刻計算の際には 演算に伴う浮動小数点数の誤差対策が必須です。

対策方法としては
①適度に微小な値を足してから切り捨てる
②先に一つ下の桁で丸めてから切り捨てる
③TEXT関数で文字列にしてから数値化する

などが考えられます。

この回答へのお礼

アドバイスありがとうございました。
③は試していないので、そちらも考慮してみます。

お礼日時：2016/10/08 00:02

No.6 回答者： tom04 回答日時： 2016/10/07 08:40

こんにちは！

＞Aのファイルは　2016/8/4　18：00：00
のシリアル値部分は計算結果になるのでしょうか？

そうであれば浮動小数点誤差の影響が考えられます。
=FLOOR(ROUND("計算結果",5),"0:15")
のように計算結果を小数点以下５桁に丸めてみたらどうなりますか？m(_ _)m

この回答へのお礼

シリアル値部分は計算結果になります。
やはり浮動小数点誤差の影響なのですね。
計算結果を丸めてみるとそのあとの集計で誤差がでたのでとりあえず、
round関数は使わないでいました。

アドバイスをいただきありがとうございました。

お礼日時：2016/10/07 23:59

No.5 回答者： GooUserラック 回答日時： 2016/10/07 02:02

見えているものと、実際のセル値は違っているので良くあります。

書式を「標準」にして３つのセルに「42586.749995」「42586.75」「42586.750005」をそれぞれ入力して下さい。
多分セル上には「42586.75」「42586.75」「42586.75001」と表示されてしまうかと思います。このとき数式バーを見ると元の数のままになっているのが確認できると思います。
３つのセルの書式を「yyyy/m/d hh:mm:ss」にすると、３つとも「2016/8/4 18:00:00」と表示されましたよね。しかも数式バーを見ても「2016/8/4 18:00:00」と全てなってしまっていますよね。
つまり、この区別がユーザーでは出来ないのです。
念の為、書式を「標準」に戻せば、数値は変わっていないのが判ると思います。

手入力で直接「2016/8/4 18:00:00」と入力した場合は問題ありませんが、計算などで出した値だとこのようなことになる場合が多いです。

この回答へのお礼

具体例まで教えていただきありあがおうございました。

上の差異はファィルによってもちがうのかなと感じました。

もう少し確認してみます。

お礼日時：2016/10/07 08:42

No.4 回答者： angkor_h 回答日時： 2016/10/07 01:16

floor関数は、基準値の倍数より小さい数値を返します。

0.010416667は末尾が切り上げられており、15分+αになっているので、
参照数値が15分の倍数の場合は、常に-15分値が返されます。
また、参照数値に日付が含まれるため、この誤差が日付で変動します。

解決策は以下で良いと思います。
小数点以下だけを対象に、基準値は15分-αにする(末尾は切り捨て)。
αは1日秒数の逆数よりも小さな値にし、秒表示に誤差を与えない。

ご質問に於ける、ファイル間で違う結果はよくわかりませんが、
そんな誤差を含む微妙な計算処理であろうことは想像できます。

この回答へのお礼

一応、日付と秒の誤差対策はしていたのですが、floor関数の処理時に　２つの関数で違う結果がでてしまいました。

もしよろしければ
解決策の
小数点以下だけを対象に、基準値は15分-αにする(末尾は切り捨て)。
αは1日秒数の逆数よりも小さな値にし、秒表示に誤差を与えない。
の具体例を教えていただけないでしょうか。

お礼日時：2016/10/07 08:40

No.3 回答者： くんこば 回答日時： 2016/10/07 01:00

日付もそうですが、時間で範囲計算する場合は、極力整数計算出来るようにデータを考え直した方がいいですよ？

シリアル値はやっかいなんで、極力使わない方がいいです。

この回答へのお礼

アドバイスありがとうございました。

お礼日時：2016/10/07 08:37

No.2 回答者： 銀鱗 回答日時： 2016/10/07 00:52

16ビットに収めるために桁落ちしたから…ですね。

微小誤差と言います。

時間の計算は時にシリアル値ではなく文字列に直してから計算したほうが良いです。
=FLOOR(42586.75, "0:15:00")
と、すると良いでしょう。

この回答へのお礼

文字列に直したら2つのファイル共に、同じ結果になりました。
アドバイスありがとうございました。

お礼日時：2016/10/07 08:36

No.1 回答者： いいいちろう 回答日時： 2016/10/07 00:32

桁落ちというのが原因らしいです。

http://www.primestaff.co.jp/fumitaka_doc/2009/01 …

この回答へのお礼

アドバイスありがとうございます。

お礼日時：2016/10/07 08:34