VBA技術解説
VBAの少数以下の演算誤差について

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2020-01-29

VBAの少数以下の演算誤差について


ワークシートで時間計算をしていると、時に不可解な結果に見舞われることがあります。
エクセルの時間は、1日を1とした数値で管理されているため、1時間は1の24分の1の数値になります。
この少数以下の計算において、エクセルが正しく計算できないというものです。
エクセルに限らず、一般的にコンピューターでは少数の計算を正しく行う事が出来ません。

時間計算で困ったときの確実な対処方法
Excelで非常に多い質問の一つが時間計算です、日付・時刻はエクセルのデータ形式の一つですが、特に時間計算は面倒で対処方法も書籍・ネットでもあまりみかけません。日付に関しては便利な関数も用意されていますし、なにより、1日が数値の1(これをシリアル値と言います)で表されているので、大抵はなんとかなっているようです。


今回は、VBAで少数以下を扱ったときに発生する演算誤差について、どのようなものかの紹介と解決方法を解説します。


少数以下の演算誤差とは

コンピューターの中は2進数です。
2進数は、桁の数値が2になると桁上がりするものです。
つまり、1桁上がるごとに2倍されていきます。
では、少数以下はどうなるかと言うと、1桁下がるごとに1/2になっていきます。

10進数 2進数
256 100000000
128 10000000
64 1000000
32 100000
16 10000
8 1000
4 100
2 10
1 1
0.5 0.1
0.25 0.01
0.125 0.001
0.0625 0.0001
0.03125 0.00001
0.015625 0.000001
0.0078125 0.0000001
0.00390625 0.00000001
0.001953125 0.000000001

この表をみるとなんとなくわかるかと思いますが、表の中間の10進数値を2進数で正確に表すことはできません。
例えば、10進数の0.1は、0.00011001100110011・・・このような2進数になってしまいます。

VBAでは、少数を扱えるようにするために浮動小数点数が使われます。
データ型としては、Single(単精度浮動小数点数型)とDouble(倍精度浮動小数点数型)が存在します。
変数宣言のデータ型については、以下を参照してください。
第12回.変数宣言のDimとデータ型|VBA入門
マクロVBA入門者が、まず最初につまずくのが、このDimで変数を宣言することでしょう。変数とは、数値や文字列など(すなわちデータ)を一時的に格納する入れ物 と良く説明されますが、まずはこの考えで良いでしょう。この入れ物には、いろいろなタイプの入れ物があります。
第13回.定数宣言のConstと型宣言文字|VBA入門
前回は変数を説明しましたが、変数があれば、当然の定数もありConstで宣言します。つまり、変数は値が変わるもの、定数は値が変わらないものです。定数とは、数値や文字列の代わりに使用される、意味を持たせた名前のことです。

Single(単精度浮動小数点数型)は4バイト、Double(倍精度浮動小数点数型)は8バイト、違いは精度の違いです。
しかし、0.1のような数値は正確な少数として表現できません、どこかで桁落ちしてしまいます。
これにより、正確な少数を扱えていないのでVBAでは誤差が発生してしまいます。

演算誤差の発生を確認する場合は、イミディエイト ウインドウで、
?0.1*3=0.3
これは
False
と出力されます。
この他、少数の四則演算を適当な数値でいろいろ確認してみてください。
TrueになったりFalseになるのが確認できると思います。

少数以下の演算誤差を実際のVBAで確認

Sub test1()
  Debug.Print 0.1 + 0.2 = 0.3
  Debug.Print 0.2 + 0.3 = 0.5
  Debug.Print 0.3 + 0.4 = 0.7
  Debug.Print 0.4 + 0.5 = 0.9
  Debug.Print 0.5 + 0.6 = 1.1
  Debug.Print 0.6 + 0.7 = 1.3
  Debug.Print 0.7 + 0.8 = 1.5
  Debug.Print 0.8 + 0.9 = 1.7
  Debug.Print 0.9 + 1 = 1.9
End Sub

これを実行すると、イミディエイト ウインドウに、

False
True
True
True
True
False
True
False
True

Falseと出力されている数値の組み合わせがあり、演算誤差が確認できます。
これですと確認しづらいですし、VBAをいろいろ変更したいので以下のようなVBAで話を進めます。

Sub test2()
  Dim i As Long
  Dim i1
  Dim i2
  Cells.Clear
  For i1 = 0.1 To 0.9 Step 0.1
    i2 = i1 + 0.1
    i = i + 1
    Cells(i, 1) = i1
    Cells(i, 2) = i2
    Cells(i, 3) = (i1 + i2 = i1 * 2 + 0.1)
  Next
End Sub

実行すると、アクティブシートに以下のように出力されます。

0.1 0.2 TRUE
0.2 0.3 TRUE
0.3 0.4 TRUE
0.4 0.5 TRUE
0.5 0.6 TRUE
0.6 0.7 FALSE
0.7 0.8 TRUE
0.8 0.9 FALSE
0.9 1 TRUE

データ型で少数以下の演算誤差に対応

先のVBAでは、データ型を一切指定してませんでした。
このVBAにデータ型を指定して演算誤差に対応していこうという趣旨になります。
VBAでは、何も指定せずに少数を記述した場合はDoubleになります。
ちなみに、整数は 32767 まではInteger、これを超えるとLongの扱いになります。
したがって、30000 * 2 はオーバーフローとなります。
このような場合は、30000& * 2 このようにLongの型文字&を付けるかCLng関数を使ってください。

Double(倍精度浮動小数点数型)

まずはVBA既定のDoubleでやってみます。

Sub test2()
  Dim i As Long
  Dim i1
  Dim i2
  Cells.Clear
  For i1 = 0.1 To 0.9 Step 0.1
    i2 = i1 + 0.1
    i = i + 1
    Cells(i, 1) = i1
    Cells(i, 2) = i2
    Cells(i, 3) = (i1 + i2 = i1 * 2 + 0.1)
  Next
End Sub

結果は先と同様に、

0.1 0.2 TRUE
0.2 0.3 TRUE
0.3 0.4 TRUE
0.4 0.5 TRUE
0.5 0.6 TRUE
0.6 0.7 FALSE
0.7 0.8 TRUE
0.8 0.9 FALSE
0.9 1 TRUE

Single(単精度浮動小数点数型)

Sub test4()
  Dim i As Long
  Dim i1 As Single
  Dim i2 As Single
  Cells.Clear
  For i1 = 0.1 To 0.9 Step 0.1
    i2 = i1 + 0.1
    i = i + 1
    Cells(i, 1) = i1
    Cells(i, 2) = i2
    Cells(i, 3) = (i1 + i2 = i1 * 2 + 0.1)
  Next
End Sub

実行結果は、

0.100000001 0.200000003 TRUE
0.200000003 0.300000012 TRUE
0.300000012 0.400000006 TRUE
0.400000006 0.5 FALSE
0.5 0.600000024 TRUE
0.600000024 0.700000048 TRUE
0.700000048 0.800000072 TRUE
0.800000072 0.900000095 TRUE
     

あれれ、と言う感じですよね。
そもそも、0.1そのものが正しくセルに入っていません。
しかも、最後の1行が出力されていません。
Forで0.9までにしているので、最後が0.9をわずかに超えてしまっているという事です。

0.1等の数値定数にSingle型の型宣言文字を指定して、
0.1!
全ての数値をこのようにしても結果は同じです。

Singleは、Doubleに比べて精度が落ちる分、かえって演算誤差が出づらい傾向があります。
しかし、上記の通り演算誤差が完全になくなるわけではありません。
Singleを使えば演算誤差が出ないといった乱暴な意見を見かけることもありますが決してそのようなことはありません。
?0.4!-0.3!
イミディエイトで簡単に確認できます。

Currency(通貨型)

Currencyは少数4桁の固定小数点数です。
Currencyは、整数形式の64ビット(8バイト)数値として保存されます。
10,000でスケーリングされて、小数点の左側に15桁、右側に4桁の固定小数点数です。
少数が4桁以内であれば、演算誤差は出ません。

Sub test5()
  Dim i As Long
  Dim i1 As Currency
  Dim i2 As Currency
  Cells.Clear
  For i1 = 0.1 To 0.9 Step 0.1
    i2 = i1 + 0.1
    i = i + 1
    Cells(i, 1) = i1
    Cells(i, 2) = i2
    Cells(i, 3) = (i1 + i2 = i1 * 2 + 0.1)
  Next
End Sub

\0.10 \0.20 TRUE
\0.20 \0.30 TRUE
\0.30 \0.40 TRUE
\0.40 \0.50 TRUE
\0.50 \0.60 TRUE
\0.60 \0.70 TRUE
\0.70 \0.80 TRUE
\0.80 \0.90 TRUE
\0.90 \1.00 TRUE

ご覧の通り、通貨型なので書式設定されていないセルに出力すると通貨記号が付いてしまいますが、演算誤差は発生しません。
少数が4桁までなら演算誤差を気にする必要がありません。

Decimal(10進型)

Decimalは、スケーリング係数(少数桁数)と正負を示す値とともに、96ビット(12バイト)の符号なし整数として保存されます。

DimのAsで宣言できるデータ型にはありませんが、VBAではDecimal型があります。
Decimalを扱うには、CDec関数 を使用します。
変数に入れる場合は、変数の型はVariantで宣言します。

Sub test6()
  Dim i As Long
  Dim i1
  Dim i2
  Cells.Clear
  For i1 = CDec(0.1) To CDec(0.9) Step CDec(0.1)
    i2 = i1 + 0.1
    i = i + 1
    Cells(i, 1) = i1
    Cells(i, 2) = i2
    Cells(i, 3) = (i1 + i2 = i1 * 2 + 0.1)
  Next
End Sub

実行結果は、

0.1 0.2 TRUE
0.2 0.3 TRUE
0.3 0.4 TRUE
0.4 0.5 TRUE
0.5 0.6 TRUE
0.6 0.7 TRUE
0.7 0.8 TRUE
0.8 0.9 TRUE
0.9 1 TRUE

何も問題ありません。
Decimalが扱える桁数範囲内であれば演算誤差を気にする必要はありません。
Decimalのデータ範囲については、CDec関数をを参照してください。
CDec関数は、引数をDecimal型(10進型)に変換します。CDec関数 CDec(expression) 引数expressionには任意の文字列式または数式を指定します。この引数は必ず指定します。

少数以下の演算誤差にデータ型以外で対応する

Currency(通貨型)で扱えない少数以下5桁の場合で説明します。

整数に直して計算

そもそも少数が無ければ演算誤差は出ないのですから、整数にして計算すれば良いのです。

Sub test7()
  Dim i As Long
  Dim i1 As Long
  Dim i2 As Long
  Cells.Clear
  For i1 = 1 To 9 Step 1
    i2 = i1 + 1
    i = i + 1
    Cells(i, 1) = i1 / 100000
    Cells(i, 2) = i2 / 100000
    Cells(i, 3) = ((i1 + i2) / 100000 = (i1 * 2 + 1) / 100000)
  Next
End Sub

実行結果は、

0.00001 0.00002 TRUE
0.00002 0.00003 TRUE
0.00003 0.00004 TRUE
0.00004 0.00005 TRUE
0.00005 0.00006 TRUE
0.00006 0.00007 TRUE
0.00007 0.00008 TRUE
0.00008 0.00009 TRUE
0.00009 0.0001 TRUE

ここでは少数5桁なので100000倍して100000で割っています。
この結果は当然問題ありません。
ただし、少数桁数が多いと整数がLongに入りきれなくなる場合が出てきます。
64bitエクセルならLongLongを使えば良いでしょう。
これは結果として、Currencyの内部処理を自力でやっていることと同じだと考えれば良いでしょう。

有効桁数で丸める

そもそも通常扱う少数の桁数はある程度きまっていることが多いはずです。
少数の桁数が決まっているのなら、その桁数に丸めてしまえば良いのです。
演算誤差は、ずっと小さい少数で発生しているので、必要な桁数に丸めてしまえば誤差は無くなります。

Sub test8()
  Dim i As Long
  Dim i1 As Double
  Dim i2 As Double
  Cells.Clear
  For i1 = 0.00001 To 0.00009 Step 0.00001
    i2 = Round(i1 + 0.00001, 5)
    i = i + 1
    Cells(i, 1) = i1
    Cells(i, 2) = i2
    Cells(i, 3) = (Round(i1 + i2, 5) = Round(i1 * 2 + 0.00001, 5))
  Next
End Sub

実行結果は、

0.00001 0.00002 TRUE
0.00002 0.00003 TRUE
0.00003 0.00004 TRUE
0.00004 0.00005 TRUE
0.00005 0.00006 TRUE
0.00006 0.00007 TRUE
0.00007 0.00008 TRUE
0.00008 0.00009 TRUE
0.00009 0.0001 TRUE

少数の桁数は、Roundの第二引数で調整できます。
実際に使う場合は、Roundする少数桁数を必要な桁数より少し多めに指定すると良いでしょう。

VBAの少数以下の演算誤差の最後に

何通りかのVBAを紹介しましたが、実際に使う場合にどれが良いかはその時々で変わってくると思います。
少数以下4桁までならCurrencyが簡単かつ確実だと言えるでしょう。
Decimalはより精度が高くなりますが、型宣言できないため扱いづらい面があります。
整数にしたりRoundする方法は、より柔軟ではありますがVBAの記述が面倒になります。
したがって、適宜使い分けると良いでしょう。

WEB上ではVBA以外の例として消費税計算の例が存在しますが、
VBAでは、税込算出/税抜算出において(余程特種な数式ではない限り)演算誤差が出ることは無いはずです。

コンピューターというものは便利なようでつまらないところで気を使わなければならない代物ですね。



新着記事NEW ・・・新着記事一覧を見る

Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)
VBAのString型の最大文字数について|エクセル雑感(6月20日)
VBAで表やグラフをPowerPointへ貼り付ける|VBAサンプル集(6月19日)
アクティブシート以外の表示(Window)に関する設定|VBA技術解説(6月17日)
マクロ記録での色のマイナス数値について|エクセル雑感(6月16日)
ツイッター投稿用に文字数と特定文字で区切る|エクセル雑感(6月15日)
日付の謎:IsDateとCDate|エクセル雑感(6月14日)
IFステートメントの判定|エクセル雑感(6月13日)
インクリメンタルサーチの実装|ユーザーフォーム入門(6月12日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ