掲示板に書いた誤差関係のコメントです。
眉に唾を付けて
また誤差の話なのですが、今キャンペーン中なもので。(^_^;)
Excel の内部では、数値は 2進数で表現されていますので、10進小数はそもそも存在していません。0.1 も 0.2 も最初から、ないのです。
ところが、Excel は便利にできていて、存在しないものを存在しているかのように見せてくれます。仕組みを知らない人は 0.1 があるものだと思い込みます。計算誤差を目にして、初めて、自分の思い込みに気が付きます。
=0.1+0.2
という式も、眉に唾を付けて見れば、本当の姿は…
=0011111110111001100110011001100110011001100110011001100110011010
+0011111111001001100110011001100110011001100110011001100110011010=VALUE("10:00")+VALUE("00:15")
という式も、本当の姿は…
=0011111111011010101010101010101010101010101010101010101010101011
+0011111110000101010101010101010101010101010101010101010101010101Excel の内部ではこんな 2進数で表現されているのです。しかも、ちょうどの値ではなく近似値です。
むしろ、こんな複雑な数値を使って計算や比較や丸めをしているのに、期待する結果が得られる場合がある、ということの方が不思議に思えてきませんか？ (^_^;)
存在しない数値
=1.2-1.1 という式でも同じですよね。
1.2 も 1.1 も、Excel には存在しない数値です。実際には =1.2 の近似値 - 1.1 の近似値 という計算をしています。
結果は 0.1 の近似値で、これが 0.099999999999999900 と表示されたというわけです。ちなみに、=1.2-1.1 はExcel 内部では以下のように表現されています。先のコメントでは倍精度浮動小数点型で表記しましたが、今回は普通に 2進数で表記します。
=1.0011001100110011001100110011001100110011001100110011
-1.0001100110011001100110011001100110011001100110011010結果の 0.099999999999999900 は以下の値です。
0.00011001100110011001100110011001100110011001100110010000
実数計算の誤差は、一応、パソコンの常識ということになっていますが、パソコン雑誌の編集部が知らないくらいですので、きっとまだ知らない人がいますよ〜 (^_^;)
腕自慢 後記
>下の比較方法では大丈夫だそうです。
>この方法にした人は結構いるかもしれませんね。
>
>=IF($C$4>=I4,"間に合う","遅れる")
>
>ただ、これでも誤差による影響は出るかもしれません。
>出ないかもしれませんが。(^_^;)試してみたら、この式もやはり誤差の影響を受けますね。
細かい数字ではたくさんありますが、切りのいい数字では、例えば、時刻表の最後の快速の発車時刻を
12:30 快速
に変更して、目的値と目標時刻を
鷹巣台 12:55
としてみてください。12:55 に間に合わないと判定されます。
みなさん、誤差対策はできていましたか？
私の誤差対策
間違い探し腕自慢 Q1 で行った誤差対策について、ファイルには特に書かなかったのでここで書いておきます。
時刻データは分単位の値に変換してから計算するようにしました。整数値でしたら足し算や引き算で誤差が発生することはありません。（15桁を超える大きい数値の場合は、ありますが）
計算式は以下のようなものです。
=INT(時刻データ*1440+0.00001)
時刻データは 24時間が 1 に対応し、1 分は 1/(24*60) です。分数は 24*60 を掛けて整数部分を取り出せば得られます。
0.00001 を加えているのも誤差対策です。もしかしたら、わずかに小さな値になっているかもしれないので、切り捨てる前に、微小値を加算しておきます。
微小値の大きさについてですが、これは扱うデータの範囲により様々です。考え方は「誤差よりも大きく、データの単位よりも小さい」ということです。
今回の時刻データは、必ず 1 より小さいので 24*60 を掛けた場合、整数部分は 1440 未満になります。Excel の有効桁数は10進数で15桁ですので、整数部分が 4桁であれば、小数部分は 11桁までです。誤差が発生するとしたら小数点以下 11桁の付近になるわけです。
また、今回の時刻データは分単位のデータですので、24*60 を掛けた場合、単位は 1 で、小数部分は、ないことが期待されます。
以上のことから、加算する微小値の範囲は、小数点以下11桁目よりも大きく 1よりも小さい、という範囲になります。 0.00001 でなくても 0.2 でも 0.03 でも同じです。
微小値に常に 1.00E-15 などの値を使っている人を見たことがありますが、誤差がもっと上の桁で発生する場合は、対策になっていない可能性があります。いくつかの手法を表面的に知っているだけでは無意味です。考え方を理解しておきましょう。
分単位への変換には、もう少しわかりやすい方法もあります。（ただし、24時間未満の場合です）
=HOUR(時刻データ)*60+MINUTE(時刻データ)
Q1 で行っているもう一つの誤差対策です。
=VALUE(TEXT(時刻データ,"h:mm"))
シリアル値を文字列に変換して、それをまたシリアル値に変換します。24時間以上の値がある場合は、[h] を使います。
=VALUE(TEXT(時刻データ,"[h]:mm"))
Q1 のシートでは計算結果の表でこの方法を使っています。これはこのシートの中では必須というわけではありません。では、何のための対策かと言いますと…
最終的な表は、コピーして他の計算で使うことがあります。自分で使うだけなら気にしないのですが、もしかしたら誤差について何も知らないような人が、そのデータを使うかもしれません。「どうも計算が違うと思ったら、あいつからもらったデータに誤差があった」などと言われるのが、私は嫌なのです。(^_^;)
つまらないトラブルを未然に防ぐための、転ばぬ先の杖、先回り、です。
さて、（私のシートの）あら探し問題の解答ですが (^_^;)
AL5 =INT((Mokuhyou_Jikoku2+110)/60+0.000001)*60-300
Mokuhyou_Jikoku2 は 0 から 1439 の整数値ですので、この計算では誤差の問題は発生しません。微小値を加算する必要はないですね。
間違った結果にはなりませんが… 見落としていました。私の応募したファイルはこちらです。すべて Excel 95 で作成しました。
UDE3.LZH
Q3 ボウリングのスコア計算を Excel 97 の入力規則を使ってやってみました。Excel 95 には入力規則の機能はありませんので、こちらは応募しませんでした。
q3.txt
15分単位で切り捨てる
先日、置いておいたお話です。(^_^)
http://www.microsoft.com/japan/support/kb/articles/J013/6/20.htm
FLOOR 関数を使って時刻データを15分単位に切り捨てる、というのですが、これもどうもあぶなそうな話ではあります。
=FLOOR("2:00","0:15")
という式は、実際には以下の計算をしています。
=FLOOR(0.0833333333333333... , 0.0104166666666666...)
本当に大丈夫なのでしょうか… たまたま運悪く誤差に出会うというのも嫌ですので、私は遠慮しておきます。(^_^;)
目をつぶって使うとしても、計算結果をそのまま使ったりするのは、誤差の影響を強く受けるので、やめた方が良いでしょう。
例えば以下の式は 2:00 ではなく 1:45 になります。
=FLOOR("16:00"-"14:00","00:15")
足し算では誤差が出にくかったのですが、以下のデータを A 列に入力して
B1 =SUM(A:A)
C1 =FLOOR(B1,"00:15")とすると、B1は 214:45、C1は 214:30 になりました。
20:43
23:35
22:58
19:50
22:42
20:05
19:15
6:09
6:43
4:44
9:49
16:03
4:35
4:03
4:48
8:43
そんな話、聞いてないよ
今回の誤差関係のコメントの発端、日経PC21 のサイトを見ていましたら、（訂正の訂正は出るのかな〜）「宿題表計算」というコーナーで「勤務時間を15分単位で切り捨てるには？」という宿題が出ていました。またタイムリーな… (^_^;)
http://pc21.nikkeibp.co.jp/pc21/index.html
http://pc21.nikkeibp.co.jp/pc21/pc_03/c_01.htm普通に SUM関数で合計して FLOOR関数で切り捨てると、例えば 1日だけ、14:00 から 16:00 まで働いた人は 1:45 に切り捨てられてしまいます。
さて、どうしましょう。次回、乞うご期待。ところで、もし、あなたの作成した計算表が誤差を含んでいて、たまたま他の人に迷惑をかけてしまったとします。その責任は誰にあると思いますか？
実数計算で誤差が発生するのは Excel の仕様です。バグではありません。結局、責任は作成者であるあなた自身にあります。他に誰もいません。
もう一人挙げるとすれば、あなたに表計算ソフトの使い方を教えた人です。
私も Excel の使い方を人に説明することがあります。詳しい解説はできませんが、いつも、計算誤差の例をいくつか紹介しています。
後で「そんな話、聞いてないよ」と言われるのは嫌ですので。(^_^;)