More than 3 years have passed since last update.

【悲報】“達人”芳坂和行氏に学ぶ、エクセル「演算誤差」対策講座がPC Onlineから消えていた

Last updated at 2020-04-16Posted at 2017-03-09

PC ONLINEのサイトから衝撃の講座 EXCEL演算誤差対策講座が2016年8月以降に削除されたようだ。
しかし、この講座　EXCEL 2007くらいを対象にしているが、EXCEL2016でもあっさり発現してしまう。Excel2016の計算結果がおかしいどうせEXCELがこの世から消えるまでこれは治らないので、マイクロソフトから消されたのだろうか。（追記ただし関数は改善するときもあるらしい）
とりあえずリンクとアーカイブを示し、主要な点を補い、オリジナルのマクロを添付した。（入力がめんどくさいからね。）
とにかく
税率、利率を乗除する計算、小数点があって、桁数が極端に違う計算は必ず誤差が出る。
VLookUp,IFで地雷が爆発しやすい
と思っておくと間違いない。
なので、
Roundを使う
１セルで複雑な計算を1度に計算しない
というのが基本。

##まとめのまとめ
###第１回のまとめ

小数点以下の数には誤差がある。

丸写しは芸がないので、実際に演算誤差を発生させるマクロを組みました

Macro1.BAS

#If VBA7 then
 Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
#Else  
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub Macro1()
Range("A1:B3").Clear
Range("A1").Select: Columns("A:A").ColumnWidth = 8.38: DoEvents: Sleep 500
ActiveSheet.Range("A1").Formula = "=1.9-1.8": Sleep 500
Range("A1").Select
Columns("A:A").ColumnWidth = 41
Range("A1").Select
With Range("A1")
.NumberFormatLocal = "0.00": Range("A3").Value = Len(CStr(Range("A1").Value)) - 2
.NumberFormatLocal = "0.000": Sleep 500: Range("A3").Value = 3
.NumberFormatLocal = "0.0000": Sleep 500: Range("A3").Value = 3 + 1
.NumberFormatLocal = "0.00000": Sleep 500: Range("A3").Value = 3 + 2
.NumberFormatLocal = "0.000000": Sleep 500: Range("A3").Value = 3 + 3
.NumberFormatLocal = "0.0000000": Sleep 500: Range("A3").Value = 3 + 4
.NumberFormatLocal = "0.00000000": Sleep 500: Range("A3").Value = 3 + 5
.NumberFormatLocal = "0.000000000": Sleep 500: Range("A3").Value = 3 + 6
.NumberFormatLocal = "0.0000000000": Sleep 500: Range("A3").Value = 3 + 7
.NumberFormatLocal = "0.00000000000": Sleep 500: Range("A3").Value = 3 + 8
.NumberFormatLocal = "0.000000000000": Sleep 500: Range("A3").Value = 3 + 9
.NumberFormatLocal = "0.0000000000000": Sleep 500: Range("A3").Value = 3 + 10
.NumberFormatLocal = "0.00000000000000": Sleep 500: Range("A3").Value = 3 + 11
Range("A2").Value = "演算誤差が発生しました！！"
Range("A1").Interior.Color = vbRed
.NumberFormatLocal = "0.000000000000000": Sleep 500: Range("A3").Value = 3 + 12
.NumberFormatLocal = "0.0000000000000000": Sleep 500: Range("A3").Value = 3 + 13
.NumberFormatLocal = "0.00000000000000000": Sleep 500: Range("A3").Value = 3 + 14
.NumberFormatLocal = "0.000000000000000000": Sleep 500: Range("A3").Value = 3 + 15
End With
End Sub

計算方法と数によって誤差は見えたり見えなかったりする。

= IF(0.3-0.2=0.1,"y","n") y
= IF(0.3-0.2 -0.1 =0,"y","n") n

CompareERR.Bas

#If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
#Else
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
Sub CompareErr()
If MsgBox("Clear All OK ? (Cancel is Cancel)", vbOKCancel) = vbCancel Then Exit Sub
ActiveSheet.UsedRange.Clear
Range("a1").Select
ActiveCell.FormulaR1C1 = "=IF(0.3-0.2=0.1,""y"",""n"")"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(0.3-0.2-0.1=0,""y"",""n"")"
Range("A2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B2").Select
ActiveCell.FormulaR1C1 = "!!": Sleep 1000
Range("B1").Select
ActiveCell.FormulaR1C1 = "0.3"
Range("C1").Select
ActiveCell.FormulaR1C1 = "-"
Range("D1").Select
ActiveCell.FormulaR1C1 = "0.2"
Range("E1").Select
ActiveCell.FormulaR1C1 = "="
Range("F1").Select
ActiveCell.FormulaR1C1 = "0.1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "0.3"
Range("C2").Select
ActiveCell.FormulaR1C1 = "-"
Range("D2").Select
ActiveCell.FormulaR1C1 = "0.2"
Range("E2").Select
ActiveCell.FormulaR1C1 = "-"
Range("F2").Select
ActiveCell.FormulaR1C1 = "0.1"
Range("G2").Select
ActiveCell.FormulaR1C1 = "="
Range("H2").Select
ActiveCell.FormulaR1C1 = "0"
Range("G2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("E1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("I1").Select: Sleep 2000
ActiveCell.FormulaR1C1 = "←EXCELはこれが等しいのに"
Range("I2").Select
ActiveCell.FormulaR1C1 = "←EXCELではこれは0ではない!!!"
MsgBox "式を詳しく見てみるよ!", vbOKOnly, "Go TO Next"
Range("A3").Select
ActiveCell.FormulaR1C1 = "0.3-0.2"
Range("A4").Select
ActiveCell.FormulaR1C1 = "を下に入れる"
Range("A5").Select
ActiveCell.FormulaR1C1 = "=0.3-0.2"
Range("A5").Select
Selection.Copy
Range("A7").Select
ActiveSheet.Paste
Range("A6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "幅を広げて桁数を増やしてみる"
MsgBox "幅を広げて桁数を増やしてみる OK"
Range("A6").Select
Columns("A:A").ColumnWidth = 32.5
Range("A7").Select
Selection.NumberFormatLocal = "0.00": Sleep 300
Selection.NumberFormatLocal = "0.000": Sleep 300
Selection.NumberFormatLocal = "0.0000": Sleep 300
Selection.NumberFormatLocal = "0.00000": Sleep 300
Selection.NumberFormatLocal = "0.000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000": Sleep 300
MsgBox "ふやすよー", vbOKOnly, "まだまだ"
Selection.NumberFormatLocal = "0.00000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000000000000000": Sleep 300
Range("A8").Select
ActiveCell.FormulaR1C1 = "やっぱりゼロか。桁を戻そう"
MsgBox "ゼロなので桁を戻します"
MsgBox "OK"
Range("A7").Select
Selection.NumberFormatLocal = "0.0"
Columns("A:A").ColumnWidth = 19.5
Range("A9").Select
ActiveCell.FormulaR1C1 = "0.3-0.2=0.1": Sleep 1000
Range("A9").Select
ActiveCell.FormulaR1C1 = "0.3-0.2-0.1"
Range("A4").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Range("A11").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=0.3-0.2-0.1"
Range("A6").Select
Selection.Copy
Range("A12").Select
ActiveSheet.Paste
Range("A11").Select
Application.CutCopyMode = False
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 27.13
Range("A13").Select
Application.CutCopyMode = False
MsgBox "桁を増やします"
Selection.NumberFormatLocal = "0.00": Sleep 300
Selection.NumberFormatLocal = "0.000": Sleep 300
Selection.NumberFormatLocal = "0.0000": Sleep 300
Selection.NumberFormatLocal = "0.00000": Sleep 300
Selection.NumberFormatLocal = "0.000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000000000000": Sleep 300
Range("B5").Select
Columns("A:A").ColumnWidth = 31.5
Range("A13").Select
Selection.NumberFormatLocal = "0.000000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000000000000000": Sleep 300
Columns("A:A").ColumnWidth = 36.88
Selection.NumberFormatLocal = "0.000000000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.0000000000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000000000000000000000": Sleep 300
Columns("A:A").ColumnWidth = 42.75
Selection.NumberFormatLocal = "0.0000000000000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.00000000000000000000000000000000000": Sleep 300
Selection.NumberFormatLocal = "0.000000000000000000000000000000000000": Sleep 300
Range("A14").Select
ActiveCell.FormulaR1C1 = "やっぱりゼロ！"
Range("A15").Select
ActiveCell.FormulaR1C1 = "0.3-0.2-0.1＝0"
Range("A15").Select
Selection.Copy
Range("A16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=0.3-0.2-0.1=0": Sleep 2000
Range("A16").Select
ActiveCell.FormulaR1C1 = "'=0.3-0.2-0.1=0を下に入れてみる": Sleep 300
Range("A16").Select
Selection.Copy
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "'=0.3-0.2-0.1=0を下に入れてみる": Sleep 300
Range("A17").Select
ActiveCell.FormulaR1C1 = "等しいならTrue、等しくないならFalse": Sleep 1000
Range("A18").Select
ActiveCell.FormulaR1C1 = "どうなる": Sleep 300
MsgBox "どうなる"
Range("A18").Select
ActiveCell.FormulaR1C1 = "どうなる？": Sleep 1000
MsgBox "どうなる？？？？"
Range("A19").Select
ActiveCell.FormulaR1C1 = "=0.3-0.2-0.1=0"
Range("A20").Select
ActiveCell.FormulaR1C1 = "等しくな---------い！"
Range("A20").Select
ActiveCell.FormulaR1C1 = "False！等しくな---------い！"
MsgBox "The End", vbOKOnly, "Oh My God!"
End Sub

整数化すれば誤差のない計算ができる。
整数化するには、必要な桁数だけ10倍、100倍などした後、ROUND関数で小数点以下を四捨五入する。
E3セル =4.3-4.2 ならば = Round(E3*10,0)/10

###第２回のまとめ

誤差のある数には、小数点以下の数、時間のシリアル値、パーセント、15桁を超える数がある。
誤差はセルの中だけでなく、数式や関数の計算の中でも発生する。
※ただし
=STDEVP(50000000,50000001)
はEXCEL2016では修正されているようだ。(おそらくEXCEL2007)

Excel の統計関数 STDEVPA と STDEVP の違い
この資料では、Microsoft Excel の STDEVPA 関数と、それと密接な関係のある STDEVP 関数との違いについて説明します。特に、Microsoft Office Excel 2007 および Microsoft Office Excel 2003 の STDEVPA 関数の計算結果と以前のバージョンの Excel の STDEVPA 関数の計算結果がどのように異なるかという点について説明します。

FuncErr.BAS

Sub FuncErr()
If MsgBox("Clear All OK ? (Cancel is Cancel)", vbOKCancel) = vbCancel Then Exit Sub
ActiveSheet.UsedRange.Clear
    Columns("A:A").ColumnWidth = 61.75
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "計算の中で誤差が発生"
    Range("A2").Select
    Columns("B:B").ColumnWidth = 49.13
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "'=IF((41/10-4)*10>=1,""大きい"",""小さい"")"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=IF((41/10-4)*10>=1,""大きい"",""小さい"")"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "=41/10"
    Range("A3").Select
    Selection.NumberFormatLocal = "0_);[赤](0)"
    Selection.NumberFormatLocal = "0.0000000000_);[赤](0.0000000000)"
    Range("A3").Select
    Selection.Copy
    Range("A4").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "'41/1"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "'41/10"
    Range("A4").Select

    Selection.NumberFormatLocal = _
        "0.000000000000000000000000000000000_);[赤](0.000000000000000000000000000000000)"
    Range("A4").Select
    Selection.ClearContents
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "=41/1"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "=41/10-4"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "'41/10-4=0.1"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "'(41/10-4)*10=1"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "=(41/10-4)*10"
    Range("A5").Select
    Selection.NumberFormatLocal = "0.0"

    Selection.NumberFormatLocal = "0.00000000000000"
    Range("B5").Select
    Selection.Copy
    Range("B6").Select
    ActiveSheet.Paste
    Range("A5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A6").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.NumberFormatLocal = "0.000000000000000"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "実は１ではない"
    Range("A6").Select
    Range("A8").Select
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "'STDEVP(50000000,50000001)"
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "=STDEVP(50000000,50000001)"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "EXCEL2016 64Bitでは修正されている？"
    Range("B8").Select
End Sub

誤差によって間違ってしまう計算には、比較、検索、丸め、文字列化、誤差の累積がある。
経理・会計事務所向けエクセルスピードアップ講座小数をif関数、vlookup関数に使うときは演算誤差に注意
 経理・会計事務所向けエクセルスピードアップ講座エクセルの時間計算で悩まない超簡単な方法
表示形式で数値の桁数を指定すれば誤差を見えなくすることができる。
数値は上から15桁までが有効であり、16桁目以降は不正確である。

###第３回のまとめ

誤差対策の方法には、整数化、微小値を使う方法、ROUND 関数で数をそろえる方法がある。
計算結果に微小値を加減することで、誤差があっても正確な比較、検索、丸めをすることができる。
『E3>=0.1』という比較を『E3+0.01>=0.1』に変える。つまり、計算結果に小さな数を足して、少しだけ大きな数にする
　微小値の大きさは、誤差より大きく、数の変化の単位より小さい範囲にする。普通は数の変化の単位の10分の1で良い。
「数の単位が 0.1 だから、その10分の1ということで、0.01 を使っているわけですね」
「エクセルでは 15 桁まで正確に計算できるから、計算で使う数の 16 桁目を誤差の最大値と考えればいいの。例えば『4.3-4.2』という計算では『4.3』と『4.2』と計算結果の『0.1』を使うよね。この中で一番大きい『4.3』の 16 桁目の大きさ、つまり 0.000000000000001 が一回の計算で発生する誤差のおよその最大値になる」
-「丸めの処理でも微小値が使えるよ。例えば、A1 に『=4.3-4.2』、A2 に『=ROUNDDOWN(A1,1)』と入れると答えは『0』になってしまうけれど、数の変化の単位が 0.1 だとしたら、その10分の1の『0.01』を足して『=ROUNDDOWN(A1+0.01,0)』にすれば答えは『0.1』になる」

ROUND関数で数をそろえる方法
ハル
「ROUND 関数で数をそろえるというのは、どんな方法ですか？」
エリカ
「例えば『ROUND(E3,1)>=ROUND(0.1,1)』のように、両方の数を小数点以下1桁に四捨五入してから比較するの。『ROUND(0.1,1)』は『0.1』と同じ数になるから『ROUND(E3,1)>=0.1』でもいいよ」

「オートフィルでも誤差が出るんですね。気を付けます」

AutoFillErr.BAS

#If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
#Else
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
Sub AutofilErr()
    Range("A2").Select
    Selection.ClearContents
    Selection.End(xlUp).Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "7.1"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "7.2"
    Range("A2:A3").Select
    Selection.AutoFill Destination:=Range("A2:A11"), Type:=xlFillDefault
    Range("A2:A11").Select
    Range("A11").Select
    Selection.NumberFormatLocal = "0.00"": Sleep 500"
    Selection.NumberFormatLocal = "0.000"": Sleep 500"
    Selection.NumberFormatLocal = "0.0000"": Sleep 500"
    Selection.NumberFormatLocal = "0.00000"": Sleep 500"
    Selection.NumberFormatLocal = "0.000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.0000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.00000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.000000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.0000000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.00000000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.000000000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.0000000000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.00000000000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.000000000000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.0000000000000000"": Sleep 500"
    Selection.NumberFormatLocal = "0.00000000000000000"": Sleep 500"
End Sub

「表示桁数で計算する」オプションを使うとセルの数値や計算結果を自動的に丸めることができる。

PrecisAsDips.BAS

Sub PrecisAsDips()
ActiveWorkbook.PrecisionAsDisplayed = True
End Sub

「『9000.05085』ですか… あれ？『9000.05084999999』になりました… どういうことですか？」

Num9K.BAS

Sub Num9K()
If MsgBox("Clear All OK ? (Cancel is Cancel)", vbOKCancel) = vbCancel Then Exit Sub
    ActiveSheet.UsedRange.Clear
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "9000.05084999999"
    Range("A1").Select
    Selection.NumberFormatLocal = "0.0000000000000000000"
    Selection.NumberFormatLocal = "0.000000000000000000"
    Columns("A:A").ColumnWidth = 41.5
    Range("A2").Select
    Range("A2").Formula = "=ROUNDDOWN(A1,4)"
    Range("A3").Value = 9000.0509
End Sub

小数点以下の丸めは正確にできないことがある。

###第４回のまとめ

IEEE 754 浮動小数点演算規格は現在広く使われている数値計算の標準規格。
「IEEE の中のワーキンググループの番号で、規格の番号でもあるの。IEEE 754 は浮動小数点演算の規格。実はこの IEEE 754 規格がエクセルの演算誤差の原因」
※つまりEXCELは永遠にこの誤差を抱え続ける
浮動小数点数は『(仮数)×(基数)の(指数)乗』という形式で表現された数値のこと。
IEEE 754では2進数を使うため、ほとんどの10進小数は正確に表現できない。(※)

※（参考）小数の表現 - 弘前学院聖愛中学高等学校
2進数の小数は0.5 0.125 0.0625 と2の自然数乗の逆数で小数点1位では0.5のみ、小数点2位は全くない。小数点3位は1個、小数点４位は1個つまり0.1000から0.9999のうちたった3つしかまともに2進数の小数から十進数の小数に変換できない。（組み合わせて加減すればもう少しある）

演算誤差対策のない関数

fnIntax01.BAS

Function fnIntax01(Price As Double, taxrate As Double) As Double
'演算誤差対策なし
fnIntax01 = Fix(Price * taxrate)
End Function

VBA の通貨型や10進型では、誤差のない10進小数を格納できる。

　演算誤差のない関数。
　ただし整数になる。
　Priceに価格、Taxrateに税率（0.08 or 8% )を代入する

fnIntax03.BAS

Function fnIntax03(Price As Double, taxrate As Double) As Double
fnIntax03 = Fix(CCur(Price) * CCur(taxrate))
End Function

fnIntax04.BAS

Function fnIntax04(Price As Double, taxrate As Double) As Double
fnIntax04 = Fix(CDec(Price) * CDec(taxrate))
End Function

##出だしはこんな感じ
◆“達人”芳坂和行氏に学ぶ、エクセル「演算誤差」対策講座
皆さんは、エクセルが“計算が苦手”ということをご存じでしょうか？表計算ソフトが計算ミスをするなんて、にわかには信じられないことですが、実際にエクセルは、「小数」の計算において、間違った答えを出すことが稀にあります。実はこれ、パソコンが数値を計算する仕組みそのものに由来する現象で、「演算誤差」や「丸め誤差」などと呼ばれるもの。文字通り、計算結果に「誤差」が生じ、正しい結果が求められないという困った問題なのです。に由来する現象で、「演算誤差」や「丸め誤差」などと呼ばれるもの。文字通り、計算結果に「誤差」が生じ、正しい結果が求められないという困った問題なのです。
「コンピューターなら正確だ」と信じているからこそ、エクセルを使って計算しているのに、それが信じられないとすれば、私たちは一体どうすればよいのでしょうか？
そこで日経PC21では、この「演算誤差」によるミスを防ぐための“対策講座”を、ホームページ限定で開講することにしました。講師は、エクセルの“達人”芳坂和行氏。第３回表計算大会で「技能賞」、第５回表計算大会で「エクセル賞」を獲得した名うての実力者です。
「演算誤差」の問題も、その仕組みを理解し、対策のポイントをきちんと押さえれば、確実に対応できるようになります。小数計算で思わぬ失敗をすることがないよう、演算誤差対策のノウハウを身につけましょう。

楽しく読んでいただけるように、講座全体をストーリー仕立てにしています。ここで、登場人物を紹介しておきましょう。

エリカ
（高山絵里香）
某販売会社に勤める会社員。日経PC21を読んでパソコンを勉強中。難しいことは苦手。でもエクセルはなんとなく面白いと感じている。誤差についても詳しくはないが、聞かれるままに後輩に教える羽目に…
ハル
（松本波留）
エリカの会社の後輩。新人だが結婚しており、双子の女の子の母。休憩時間にはパソコンで絵を書いて子供たちにメールしている。時間を見つけては資格試験の勉強をしている努力家だが、つい、うとうととしてしまうことも…

##追記
配列数式講座も消えていた
https://web.archive.org/web/20050224002535/http://pc21.nikkeibp.co.jp/special/hr/hr1.shtml
https://web.archive.org/web/20050313165321/http://pc21.nikkeibp.co.jp/special/hr/hr2.shtml
https://web.archive.org/web/20050313165538/http://pc21.nikkeibp.co.jp/special/hr/hr3.shtml
https://web.archive.org/web/20050313165645/http://pc21.nikkeibp.co.jp/special/hr/hr4.shtml
https://web.archive.org/web/20050309001106/http://pc21.nikkeibp.co.jp/special/hr/hr5.shtml
https://web.archive.org/web/20050309001106/http://pc21.nikkeibp.co.jp/special/hr/hr6.shtml

##このように評価もよいのだが...

Excelはたしかに便利だが演算誤差があることを頭に入れておかないと痛い目にあることが稀にある。勉強したい人はこのあたりの記事がおすすめ。 http://t.co/KwzYgzfY
— dora_bt (@dora_bt) 2012年8月29日

詳しくはここ http://j.mp/d8TKqL [日経PC21 / エクセル「演算誤差」対策講座]
— neta (@neta__) 2011年2月10日

これもうエクセル関係ない世界に突入してるｗ | エクセル「演算誤差」対策講座第４回演算誤差の正体 IEEE 754 浮動小数点数の仕組み http://t.co/at3T7xjAgq
— ELD-R-ESH-2 (@eldesh) 2013年10月29日

地銀が正しい

日経PC21 / エクセル「演算誤差」対策講座「エクセルが“計算が苦手”ということをご存じでしょうか？ …にわかには信じられないことですが、実際にエクセルは、「小数」の計算において、間違った答えを出すことが稀にあり」 http://t.co/8u6xuUILzT
— いし@名誉駅長57ヶ月目 (@mk_ishi) 2015年10月7日

##不幸は続くよどこまでも
###PC と Windows RT コンピューターでの計算結果の相違
Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2016 for Mac

Windows RT コンピューターで式を作成したり、Excel ワークシート関数を使った場合の計算結果が、従来の x86 ベースのアーキテクチャが装備された PC での計算結果とわずかに異なることがあります。これは、Windows RT で使われているプロセッサと x86 ベースコンピューターで使われているプロセッサでは、その設計が異なるためです。このような違いは、14 桁や15 桁のような高精度の場合に発生します。

##リンクの変更
Excel で浮動小数点演算の結果が正しくない場合がある
https://support.microsoft.com/ja-jp/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel
は現在Excelの概念に含まれている
https://docs.microsoft.com/ja-jp/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
また、

Excel2016の計算結果がおかしい
2017年9月08日
ちなみの紹介したページからリンクされている「浮動小数点数の演算における丸め誤差を修正する方法」は日本語版が無くなって英語版だけになってます
How to correct rounding errors in floating-point arithmetic
https://support.microsoft.com/ja-jp/help/214118/how-to-correct-rounding-errors-in-floating-point-arithmetic

とあるが
小数点以下の桁数を設定する
に入っている。

#追記 2020/04/05
##WordとExcelを使った場合の演算誤差
差し込み印刷で小数値を正しく表示したい
例えば面積のように
1,941.11
というような表示をさせたいとき、小数点以下がおかしくなる場合があります。
これも演算誤差と推定されます。
しかし、場合の演算誤差はExcel側では一切計算していないので、非常に見つかりづらいです。
なぜこれが演算誤差か、これはOLE DBを介してWordにデータが渡ります。
この時、Excelが小細工をして表示していた場合、真のデータが渡されてしまうために起きるものと考えられます。
この時10進数ではなく2進数を使っているためです。
この演算誤差は下手をすると誤植を引き起こします。
さらにこれはExcel側でいくらチェックしても起きてしまいます。数式を使っていなくても起きます。
これを防止するにはフィールドコードを使い、小数点2桁を強制します。

{MERGEFIELD 数値 \# 0,.0 }
これでコンマ付き小数点2桁になります。Qiitaでは\で表示されますが、VBAでは円マークです。注意してください。
式だともっとはっきり
{= 20900324.444 \# "0,.00"}
{= 20900324.444 \# "0,.00㎡"}
などと書くとよいです。しかしゼロコンマピリオドゼロゼロなんて誰もわかるかボケが。
{REF A1C1 \# "0,.00㎡"}
ブックマークA1C1というところから数字を持ってきています。

このWordのブックマークとはExcelのようにA1R1などといかないため、あとで引用をしたい文字列や数字に名前を付けて設定するものです。インターネットのブックマークなどとは違います。
　しいて言えば、Excelの名前付き範囲(Nameオブジェクト)と似ているといえるでしょう。
##差し込みフィールドでもやってみた
　差し込みフィールドでもやってみます。未完成ですが、このような感じです。フィールドコードの設定がうまくいかないので、手入力で直しています。実際は%USERPROFILE%は環境変数ではなく、具体的なC:\User\Nameで表示されています。
面白いのは自動で記録したのに、それをいじろうとするとSQLは255文字しか書けないのです。しかしWordのようにこれだけ余計なものをくっつけた上に255文字っていうの、いくら何でも最低すぎる。普通の人が接続文字列のどこを省略していいかなんてわかるわけがない。
これはAccessのAction MacroのSQLが255文字という制限があるのに似ています。でもシート名は別なんですよね；。
このため、ファイル名を短くしないと無理です。
ファイルdb1.xlsxシートは一つで名前はSheet1です。

Sub InsertField()
Dim wDoc As Word.Document: Set wDoc = ThisDocument
Dim wFC As Word.Field
Dim wRng As Word.Range

    Options.ConfirmConversions = True
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource _
        "%USERPROFILE%\Documents\DB1.xlsx", wdOpenFormatAuto, False, False, , False, , , False, , , _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%USERPROFILE%\Documents\DB1.xlsx;Extended Properties=""HDR=YES;IMEX=1;Jet OLEDB:Engine Type=37;Jet OLE" _
        , "SELECT * FROM `Sheet1$`", , wdMergeSubTypeAccess
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="住所"
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="数字"
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="数字"
    Set wRng = wDoc.Fields(2).Code
    wRng.Text = " MEREFIELD 数字 \# ""0,.00㎡"" "
    Selection.EndKey Unit:=wdLine
End Sub

{MERGEFIELD 数字 \# "0,.00㎡"}

これも同じです数字のところはこういう差し込みフィールドに変えます。

このように数字の列を2つ作り、一つはFieldCodeを仕込み、一つはそのままにします。
A4,563,326.29㎡4563326.2868900001
D129.11㎡129.11000000000001
E129.12㎡129.12
F129.13㎡129.13
G129.14㎡129.13999999999999
H199.43㎡199.43000000000001
j49.21㎡49.210000000000001
k49.01㎡49.009999999999998
L49.02㎡49.020000000000003
m8.21㎡8.2100000000000009
n8.22㎡8.2200000000000006
たったこれだけなのに
ABDGJKLmnまで15のうち9も失敗しています。
Bは元が微妙だから勘弁するとしても半分以上が失敗しています。
確かに長年の経験と勘からMyI（自分の知性）を働かせてExcelがバグりそうな値を意図的に選んでますよ？
しかしDからGの流れはひどくないですか？
使い物になりゃしない。
フィールドコードで設定しないとだめなのです。
この流れを見ても2進化10進でセル内の値が近似値にどこかで変わっているといえます。
芳坂和行先生、あなたが記事にしなかった新しい演算誤差を発見しましたよ。
ビルゲイツは7つくらいワクチンを作るのと、8つ目にこれを加えてほしいですね。
ただ、Wordはわかっているのかわかっていないのかわからないのですが、このWordのフィールドコード、一つだけすごいのが、Excelの有効数字15桁よりでかい数を処理している、という点です。どこまでできるのか不明ですし、実際数として扱っているのか機械的に切っているだけなのか不明ですが、とにかくExcelがセル内で扱えない15桁以上の数字でもフィールドコードは処理している。少なくとも17桁目が存在しています。
６．小数の表示がおかしい件 waenavi
わえなびも同様の結論に至っています。もちろんパーセント表示形式をExcelで設定している場合、素のデータがWordにわたるのは理解できます。しかしながら、表示形式を設定しなくても、計算しなくても、単にデータが引き継がれるだけで誤差が起きてしまう、これが問題です。このわえなびにおいて、そうした通貨、パーセント表示等のフィールドコードの例を扱っています。
特にパーセンテージの表示はフィールドコードが2ついるというのが重要だと思います。なぜならここで演算誤差が起きる可能性が高いためです。

#Access
実際の税率計算、8%において単精度を使った場合のエラー
access　少数点の誤差・不思議現象・処理 - access 実践サンプル

Sub AcTestarithmetic_error()
' For Access VBA with DAO
Dim cDB As Database: Set cDB = CurrentDb
Dim Q As QueryDef, Qs As QueryDefs, TDF As TableDef, TDFs As TableDefs
Dim S As Single, S2 As Single, C As Currency
Dim sSQL As String
Dim dRs As DAO.Recordset, dFld As DAO.Field
On Error Resume Next
DoCmd.DeleteObject acTable, "T_arithErr"
DoCmd.DeleteObject acQuery, "Q_Calc"
On Error GoTo 0
If Err.Number <> 0 Then Err.Clear

sSQL = "Create Table T_arithErr ( [A] Single, [B] Single);"
cDB.Execute sSQL
Set TDF = cDB.TableDefs("T_arithErr")
Set dRs = cDB.OpenRecordset(TDF.Name)
With dRs
For C = 0.1 To 0.9 Step 0.1!

.AddNew
dRs.Fields(0).Value = 1
dRs.Fields(1).Value = C
.Update

Next
End With
dRs.Close
Set Q = cDB.CreateQueryDef("Q_Calc", "SELECT [t_aritherr].[a]+[T_arithErr].[B] AS 式addtion, [t_aritherr].[a]-[t_aritherr].[B] AS 式Subtraction FROM T_arithErr;")
Application.RefreshDatabaseWindow
End Sub

Q_CalcRound無効

SELECT Round([t_aritherr].[a]+[T_arithErr].[B],1) AS 式addtion, Round([t_aritherr].[a]-[t_aritherr].[B],1) AS 式Subtraction
FROM T_arithErr;

しかしRoundはだめです。Ccurも効きません。この場合も小数点１位までだとして、微小値を足して切り捨てで解決できます。

Q_Calc演算誤差解決

SELECT T_arithErr.A, T_arithErr.B, Int(([t_aritherr].[a]+[T_arithErr].[B]+0.05)*10)/10 AS 式addtion, Int(([t_aritherr].[a]-[t_aritherr].[B]+0.05)*10)/10 AS 式Subtraction
FROM T_arithErr;

Function StrictTxtSubstruction(varA, varB) As String
'厳密な引き算 十進で計算したうえで結果は文字列になる
On Error GoTo Err_Handle
StrictTxtSubstruction = CStr(CDec(CDec(varA) - CDec(varB)))
Exit Function
Err_Handle:
If Err.Number <> 0 Then Err.Clear
StrictTxtSubstruction = 0
End Function
Function StrictDBlSubstruction(varA, varB) As String
'厳密な引き算 十進で計算したうえで結果はDouble 倍精度
On Error GoTo Err_Handle
StrictDBlSubstruction = CDbl(CDec(CDec(varA) - CDec(varB)))
Exit Function
Err_Handle:
If Err.Number <> 0 Then Err.Clear
StrictDBlSubstruction = 0
End Function
Function StrictTxtAdditional(varA, varB) As String
'厳密な足し算 十進で計算したうえで結果は文字列になる
On Error GoTo Err_Handle
StrictTxtAdditional = CStr(CDec(CDec(varA) + CDec(varB)))
Exit Function
Err_Handle:
If Err.Number <> 0 Then Err.Clear
StrictTxtAdditional = 0
End Function
Function StrictDBlAdditional(varA, varB) As Double
'厳密な足し算 十進で計算したうえで結果はDouble 倍精度
On Error GoTo Err_Handle
StrictdbAdditional = CDbl(CDec(CDec(varA) + CDec(varB)))
Exit Function
Err_Handle:
If Err.Number <> 0 Then Err.Clear
StrictDBlAdditional = 0
End Function

SELECT T_arithErr.A, T_arithErr.B, Int(([t_aritherr].[a]+[T_arithErr].[B]+0.05)*10)/10 AS 式addtion, Int(([t_aritherr].[a]-[t_aritherr].[B]+0.05)*10)/10 AS 式Subtraction, StricttxtAdditional([T_arithErr].[A],[t_aritherr].[B]) AS 式Add2, StrictTxtSubstruction([T_arithErr].[A],[T_arithErr].[b]) AS 式Sub2, StrictDblAdditional([T_arithErr].[A],[t_aritherr].[B]) AS 式Add3, StrictdblSubstruction([T_arithErr].[A],[T_arithErr].[b]) AS 式Sub3
FROM T_arithErr;

文字列になっているところは左寄りになります。
ただ、厳密なものは遅いため、クエリに式を書く方がダサいけど早いでしょう。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up