時刻表示の不思議

Excel上でむっちゃ精密な時間計算をする場合の誤差について、
ちょっとまとめてみました

業務で時間計算を一気に繰り返ししているので、
作業の効率アップを狙って、ExcelのVBAでスクリプトを書くことになったんですが、
「29秒までは秒を切り捨て、30秒からは秒を1分単位へ切り上げる」処理を書くときに、
これを簡単に実現できる関数を探したんですよね

でも、なかったんです……(見つけられないだけかなあ?)
仕方がないので、自分で考えようということに……

切り上げのCEILINGと切り捨てのFLOORという命令があるんですが、
時間表示にした段階でどうしても誤差が出るようです

Excel内部では時間表示されている数値は、
小数点第18位までのシリアル値で保持されています
さらにこの数値に、例えば 0.5628 とか、整数でない数値をかけたりなんかしたら、
もっとややこしくなります(笑)

しかも、時間は俗に言う60進法ですから、
10進法とは割り算では割り切れない関係であるのは明らかです
ずっと小数点以下の誤差と一緒につきあっていかねばなりません

ちなみに、
時分を表示させる時に、書式設定→表示形式で、
ユーザー定義で[h]:mmと設定
1:00:59 を入力 → 1:00 となり、単に秒以下を表示していないだけだとわかります

でも、1/10秒以下はしっかり四捨五入?されてます
(時刻表示での限界は、1/1000秒までみたいですので)
たとえば、[h]:mm:ssと設定したセルに、
0:00:29.501 を無理矢理入力してみると、0:00:30 と勝手に丸まって表示されます
しかし、内部ではしっかり 0:00:29.501 のシリアル値は残っているんです

ちなみに 0:00:29.501 のシリアル値は 0.000341446759259258 です

参考までに、0:00:29.500 のシリアル値は、0.000341435185185185 です
千分の1秒違うだけなのに、0.000000011574074074 の差が出てます
時刻表示で詳細な入力をするのには限界があるっていうことですね(苦笑)

実際普通に秒の計算をするには、全然問題のない誤差ですが、
おおざっぱな計算を繰り返していると、急に分単位の誤差が発生してびっくりしました

あと、「Excelって不思議やねえ?」のような状態も見受けられます
表示形式による大きな違いを見てください
シリアル値 → [h]:mm:ss.000 → [h]:mm:ss なんですけど、
0.000341435185185185 → 0:00:29.500 → 0:00:29
0.000341535185185186 → 0:00:29.500 → 0:00:30

なんか変ですよね(^^;;;
ここらへんをExcelには処理を統一して欲しかったって思います
シリアル値を 0.000000000000000001 だけ増やしたんですけど、
表示形式を変えるだけで、0.5秒がどっかへ行っちゃいます
1/10秒以下は標準で切り捨てして欲しかった(^^;

っていうか、秒の29捨30入をしたいっていうこと自体が、
とってもおおざっぱなような気がしてきました(爆笑)

ちょっと恥ずかしいんですけど、
メモ程度にこの時の処理の式を書いておきます
例の式はセルA1の値の秒部分を29捨30入して分の位までにまとめる式です

=IF(A1-FLOOR(A1,”0:1″)>=0.00034144,CEILING(A1,”0:1″),FLOOR(A1,”0:1″))

BlogRanking参加してます☆応援お願いしますっ
人気blogRankingBanner←ぽちっm(_ _)m

カテゴリー: パソコン好きな家電オタク パーマリンク