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

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

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


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

時間計算で困ったときの確実な対処方法
・日付・時刻のシリアル値とは ・Excelにおける小数の問題 ・どんな時に問題が発生するか ・確実な時間計算方法 ・TIME関数の制限について ・単純化した結論


今回は、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入門
・データ型 ・変数の使い方 ・変数名の規則 ・良く使われる変数名 ・自動型変換、暗黙の型変換 ・変数宣言の必要性 ・Dim変数宣言のまとめ
第13回.定数宣言のConstと型宣言文字|VBA入門
・Const定数の基本 ・変数でも同じ事が出来るが・・・ ・ダブルクォーテーションについて ・型宣言文字 ・Constにデータ型を指定しない場合のデータ型 ・その他の定数 ・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関数 ・CDec関数の使用例 ・10進型 (Decimal)について ・データ型変換関数一覧

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

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では、税込算出/税抜算出において(余程特種な数式ではない限り)演算誤差が出ることは無いはずです。

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



同じテーマ「マクロVBA技術解説」の記事

手動計算時の注意点と再計算方法
VBAの用語について:ステートメントとは
オブジェクト変数とは何か
VBAの小数以下の演算誤差について
スピルでVBAの何が変わったか
CharactersプロパティとCharactersオブジェクト
ユーザーに絶対に停止させたくない場合のVBA設定
印刷範囲の設定・印刷範囲のクリア
VBAの省略可能な記述について
VBAのVariant型について
VBAのインデントについて


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

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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