演算誤差についてはこちら
https://qiita.com/Q11Q/items/84b380b20ef51eca929c
というかどうしてこの記事は復活しないのだろう。
これまでの理解
- 四捨五入をRoundを使わずに行う方法が必要
- 特にExcelとAccessのRoundは別物。
- また、VBAのRoundもWorksheetfunctionのRoundと別物
- このためにはRoundを使わずにRoundを実現する
- 例えば小数点2位で四捨五入して小数点1位にしたい。
- 0.35 -> 0.4 -0.35 -> -0.4
- こういうときはこうする。
Int(ABS(0.35)*10+0.5)/10*Sng(0.35)- まず絶対値ABSで求めてマイナスを取る。
- これを10倍して3.5
- 小数点1位に0.5を足す。小数点1位が5から9までは繰り上がる。これが四捨五入。INTで切り捨てる。
- 10で割る。
- Sng関数（Excelのワークシート上はSIGN関数で異なるので注意。で符号を戻す。
もう少しルールを説明する
今回の四捨五入は絶対値四捨五入。絶対値で計算する。
厳密にいうと上記の関係は正の場合には0.4> 0.35 負の場合には -0.35 > -0.4 となり、実は負の数は小さい数に丸められてしまう。
ExcelのWorkSheetFunctionのRoundは絶対値で四捨五入している。
今回も次のように四捨五入する -0.75 -> -0.8
値はB1からA1に変化する
2つの数の差をとって、1を引き、それを100倍する。
通常の場合
値がB1からA1に変化する
| A1 | B1 | C1 |
|---|---|---|
| 3 | 4 | =ROUND(A1/B1-1,3)*100 |
3÷4=0.75 1-0.75=-0.25 -0.25*100=-25
25%の減少、0.75倍
Excelが計算できない絶対的例外
3970と4000が今回発見された絶対困難数である。
まず答えを求める
上記のルールで計算する
3970÷4000=0.9925 0.9925-1=-0.0075 -0.0075 * 100 =-0.75 小数点2位で絶対値四捨五入するので答えは -0.8
次に上記の計算結果を見てみる
| A1 | B1 | C1 |
|---|---|---|
| 3970 | 4000 | =ROUND(A1/B1-1,3)*100 |
答えは-0.7となる
通常の方法では解決できない。
この式は実は2回演算誤差が発生する。
1度目はA1/B1
2度めはA1/B1の結果から-1したときである。
上記の式では2回の計算で発生した演算誤差を1つのRoundでは解消できないことがわかる。
0.5を足すのも難しい
この数字は -0.749999
となっている。このため、10倍して0.5を足しても0.79であり、繰り上がらない。
Excel 2019はもっと凶悪な嘘をつく
3970 4000 -0.7500000000000000000000000000000 -0.75 TRUE
3970 4000 -0.7500000000000000000000000000000 -0.75 TRUE
=ROUND(A1/B1-1+(0.000001*(A1/B1-1)),4)*100 -0.75 =C1=D1
=ROUND(A2/B2-1,4)*100 -0.75 =C2=D2
Excel2019はいくら広げても演算誤差が見えない。一見するとただしい上に、比較してもTrueになる。
それでは=ROUND(A2/B2-1,3)*100にしてみる
3970 4000 -0.7500000000000000000000000000000 -0.75 TRUE
3970 4000 -0.7000000000000000000000000000000 -0.75 FALSE
また-0.7になってしまう。つまり、厳密な比較になっていない。
Excel2019は見た目だけごまかしているだけで演算誤差は発生している。かえって悪い。
変化率を出すためには必ずこう書かなければならない。
上記の絶対値四捨五入で、％で小数点1位に丸める。
| A1 | B1 | C1 |
|---|---|---|
| 3970 | 4000 | =ROUND(ROUNDDOWN(A1/B1-1+(0.0000001*SIGN(A1/B1-1)),5),3)*100 |
これは絶対値で微小値0.0000001を加算している。
四捨五入する0.01%つまり1万分の1より100分の1小さい数を足して絶対値で切り下げることで演算誤差を消してRoundする。
これは自分が思いついたのではなく芳坂氏が夢枕に立って教えてくれたものだ。
VBA VBS
そのまま行う方法とCDecを使っても良い
CDECは必ず2回必要である。
WordkSheetではABSを使わない方法を披露したが、VBAでは可読性が必要なのでINTで押してもいいと思う。
Sub test()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim Rn As Range, Ro As Range
Dim Inew As Long, Iold As Long
Set Rn = ws.Range("A1")
Set Ro = ws.Range("B1")
Inew = Rn.Value
Iold = Ro.Value
Debug.Print (Inew / Iold) - 1
Debug.Print Abs((Inew / Iold) - 1)
Debug.Print Abs((Inew / Iold) - 1) + (1 / 1000000)
Debug.Print (Abs((Inew / Iold) - 1) + (1 / 1000000)) * 1000
Debug.Print Int((Abs((Inew / Iold) - 1) + (1 / 1000000)) * 1000 + 0.5)
Debug.Print Int((Abs((Inew / Iold) - 1) + (1 / 1000000)) * 1000 + 0.5) / 1000
Debug.Print Int((Abs((Inew / Iold) - 1) + (1 / 1000000)) * 1000 + 0.5) / 1000 * Sgn((Inew / Iold) - 1)
Debug.Print Int((Abs((Inew / Iold) - 1) + (1 / 1000000)) * 1000 + 0.5) / 1000 * Sgn((Inew / Iold) - 1) * 100
Debug.Print Int(CDec(CDec(Inew / Iold) - 1) * 1000 + (0.5 * Sgn(Inew / Iold - 1))) / 10
End Sub
なぜ0.000001か
これは実際に四捨五入を行う桁が小数点4位、1万分の1 0.0001 のためこれより更に2桁小さい数を足しているためである。1桁でも良いかもしれないが、小数点2位がただしいか見る場合があるため、それより1つ多くなっている。
Access SQL
T_masというテーブルに Fnew と Foldという2つのフィールドがあり、3970と4000が入っている場合。
クエリ1の式1フィールドは次のようになる。
SELECT Int((Abs([Fnew]/[FOld]-1)+0.000001)*1000+0.5)/1000*Sgn([Fnew]/[FOld]-1)*100 AS 式1
FROM T_Mas;
変化率の計算は要注意
今回衝撃的なのは整数位の計算をしていて演算誤差が2回も発生しているという点である。しかも変化が小さいところで起きている。
これは通常の A1/B1-1ではExcelは間違った計算をしているため、単純に式を採用してはならないということを意味している。
今回はたまたま4000だったから分かったのだが、通常はわからない。しかもこの解決方法は通常の演算誤差の解決方法では無理で、微小値をたさなければもっと複雑になる。こうした組み合わせがよもや整数位に存在しているということは本当に驚いている。
AccessはExportに注意
なお、上記のクエリをExcelに出力したとき、やはり0.7に戻ってしまうかもしれない。これが正しいか間違いか現在確認中である。
芳坂様ありがとうございます
芳坂氏がいなければ到底この演算誤差を乗り越えることは無理だった。
丸めの処理でも微小値が使えるよ
そうでした。しかし16桁ではなく単精度浮動小数点数の限界を使えと。さらにそれは最小値の100分の1にもあたると。あなたの記事は未だに多くの人を苦しみから救っています。
それにしてもこれでもまだ解消されない演算誤差の条件や組み合わせはあるのだろうか。できればあいたくないものですが。