Excel2010のVBAでマクロで、条件式がうまく機能しない。

Question

お世話になっております。小島と申します。

　現在、Excel2010上で、VBAによるマクロを作成しています。

　そのマクロ内で、シート上に入力された４つの値を合計し、合計値が、1未満の場合と、1を超過した場合に、メッセージを表示する様に、

　マクロの記述を行っているのですが、合計値が1の時も、1未満と判断されてしまいます。

　マクロの記述は、以下の通りです。

If (Worksheets("メニュー＆初期設定").Cells(11, 3).Value + _

   Worksheets("メニュー＆初期設定").Cells(12, 3).Value + _

   Worksheets("メニュー＆初期設定").Cells(13, 3).Value + _

   Worksheets("メニュー＆初期設定").Cells(14, 3).Value) < 1 Then

   MsgBox "所要割合の合計が100%未満になっています。" & Chr(10) & _

         "設定された値を修正し、合計が100%になる様にして" & Chr(10) & _

         "再度『分析実行確認』をクリックして下さい。" & Chr(10) & _

         "現在の合計：クラスA " & Worksheets("メニュー＆初期設定").Cells(11, 3).Value & Chr(10) & _

         "　　　　　　クラスB " & Worksheets("メニュー＆初期設定").Cells(12, 3).Value & Chr(10) & _

         "　　　　　　クラスC " & Worksheets("メニュー＆初期設定").Cells(13, 3).Value & Chr(10) & _

         "　　　　　　クラスD " & Worksheets("メニュー＆初期設定").Cells(14, 3).Value & Chr(10) & _

         "　　　　　　合計 ⇒ " & (Worksheets("メニュー＆初期設定").Cells(11, 3).Value + _

         Worksheets("メニュー＆初期設定").Cells(12, 3).Value + _

         Worksheets("メニュー＆初期設定").Cells(13, 3).Value + _

         Worksheets("メニュー＆初期設定").Cells(14, 3).Value), _

         vbCritical, "所要割合設定内容確認"

   Exit Sub

上記マクロので、"メニュー＆初期設定"シート上の、C11(マクロ上のcells(11,3)の箇所)とC12(マクロ上のcells(12,3)の箇所)、

C13(マクロ上のcells(13,3)の箇所)、C14(マクロ上のcells(14,3)の箇所)を合計して、合計値が、1に満たない場合(未満)に、

msgboxに記載されている、メッセージを表示するというものですが、実際に1未満の場合は、キチンとこの処理を行いますし、

この後ろに、1を超えた場合(超過)に同様なメッセージを表示する処理が有りますが、キチンと処理が行われます。

ですが、合計値が1になった場合には、前述の1未満の時の処理を行ってしまいます。

if～thenの中で、計算式を記述した為かと思い、事前に倍精度変数( ～ AS Doubleで定義した変数)に値を退避して、

その上で変数を使用して、条件判断を行わせても、同じ事象となってしまいます。

if文での判断に問題があるのかと思い、Select Case文で、同じ様な判断をさせても、事象は同じでした。

この様なケースを回避するには、どうしたら良いのでしょうか。

ちなみに、C11～C14に入力されている値は、C11=0.45、C12=0.25、C13=0.20、C14=0.10です。

また、このセルの値を、C11=0.10、C12=0.20、C13=0.25、C14=0.45の入力された順番を逆さまにすると、

キチンと1未満の場合と同じ処理を行わず、合計値が1である場合の処理を行ってくれます。

どなたか、この状況を回避する方法をご教示下さい、

よろしくお願い致します。

Answer 1

私の二度目の投稿と入れ違ったみたいですが、プログラムに他の要因がなければ、この問題は誤差の問題としか言いようがありません。

悩んでも無駄です。

Answer 2

お世話になっております、小島と申します。

　本件に関して、新規に作成したExcelファイル内に、0.85～0.05まで0.05刻みで、合計が1.0になり、

　4つの値でひとつ前の値が、次の値より大きくならない組み合わせで、全部で69ﾊﾟﾀｰﾝを用意して、

　同じ処理を行うマクロを記述しテストを行ってみたところ、全部で4ﾊﾟﾀｰﾝで、同じ事象となる事が、

　判明しました。

　用意した値のﾊﾟﾀｰﾝは以下の様な状態です。

　以下の値は、処理結果が、おかしいのものです。

　1つ目:0.7　　　0.6　　　0.5　　　0.45

　2つ目:0.2　　　0.3　　　0.2　　　0.25

　3つ目:0.1　　　0.1　　　0.2　　　0.2

　4つ目:　　　　　　　　　　0.1　　　0.1

上記の様に、2つ目が1つ目を、3つ目が2つ目を、3つ目が4つ目を超えない値で、ﾊﾟﾀｰﾝを作成し、

処理を行っています。

この結果から、最後に足した値が0.1で直前の値が、0.2か0.3の時のみに、今回の事象が発生して

しまう様です。

それ以外の場合は、今回の事象は発生しませんでした。

いったい、何が原因なのでしょうか？

どなたか、ご教示、ご助言をお願い致します。

Answer 3

>逆さまの0.10，0.20，0.25，0.45となっている)時には、正常に1.0として処理を

>行ってくれるというのは、少々腑に落ちないのですが。

私の申し上げたのは純粋に理論的な話です。

実際にVBAが何をやっているのかは闇の中です。

これも一般論ですが、数値計算を行う場合コンピュータが式の通りやっているとは限りません。

計算の順番を変えたりして無駄をなくそうとしたり、また、このような問題があるのは承知の上ですから可能な範囲で丸め誤差の発生を抑えるような工夫をしているかもしれません。

>科学技術計算を行う場合には、Excelを使用できない事になりかねないと思います。

Excelの話ではなく大型機も含め、コンピュータすべてに言える話なんです。

科学計算や金利計算は何桁までの精度を保証する必要があるか前提を決めてやっている訳です。

例えば100ケタの有効数字を保障しろと言われたら、世界中のどんなコンピュータでも普通にはできません。

また、科学計算に使われるπとかeは無理数つまり無限小数ですから、絶対に正しく表現できません。

計算と言うのはそういうものなんです。

>前に100をかけて、4つの値が、全て整数の状態にしてから

一つの手段ではあります。

ただし、普通は考えられませんが

Dim 　X　AS　Long、Y　AS　Double

＜計算処理＞

X=Y*100

としても、Yの値が計算によるものであると、思っておられるものより1少ないものになると言う可能性はゼロではありません。

偏執病的と言われかねませんが、100%にするにはやはり

X=Y*100+0.000000001

と言ったように丸め誤差を絶対に発生させないようにする必要があります。

>ちなみに、Excelシート上で同じように、if関数を使用して、4つの値の合計が、1.0であるか判断

>させてみたところ、正確に判断してくれました。

前の投稿でも書きましたが、シート式の方が多少そういう面でのユーザーサービスがいろいろ行われているようです。

ただし、これも100%ではないので、Answersでもなんどか、正しく計算されないがなぜかと言う質問がでています。

直接関係ありませんが、良くコンピュータでπを何万ケタまで計算したなんてのが出てきます。

これは普通の浮動小数点の計算とは全く関係ない世界で、プログラムを作成しています。

具体的にどのようにやっているのかは私も存じません。単純に100万桁の精度の計算ができるようなプログラムを作ったなんて単純かつ非効率な話ではないはずです。

Answer 4

y sakuda様

ご返信ありがとうございます。

やはり、４つの数値を足した際に、0.0000000001等を足して、微妙に超えない様にしたり、

微妙に超える様にしたりして、こちらの意図する1.0を、疑似的にふるいにかけるしかないと

いつことですよね。

ただ、質問を投稿させて頂いた際にも、記載していましたが、4つの数値を足す際に、4つの

数値の内訳は、まったく同じ(0.45，0.25，0.20，0.10)で足される順番が変わる(数値の入って

いる場所が、逆さまの0.10，0.20，0.25，0.45となっている)時には、正常に1.0として処理を

行ってくれるというのは、少々腑に落ちないのですが。

また、4つの数値に入る値は、0.85から0.05刻みで、0.05までの値ですので、極端に半端な

値が入る事は無いため、微小な誤差が生じてしまう事は、有り得ないと思うのですが。

万一、0.05刻みの値の中から、任意の値を選んで計算した結果に、微小な誤差が生じてしまう

のであれば、利率等の金利を計算したり、科学技術計算を行う場合には、Excelを使用できない

事になりかねないと思います。

計算する際に、事前に100をかけて、4つの値が、全て整数の状態にしてから、計算して合計が

100であるかを確認する様にした方が良いのでしょうか？

ちなみに、Excelシート上で同じように、if関数を使用して、4つの値の合計が、1.0であるか判断

させてみたところ、正確に判断してくれました。

シート上のif関数と、マクロで使用するifステートメントでは、処理のされ方に違いがあるのでしょうか？

Answer 5

浮動小数点の数値はコンピュータ上では、2^-n　つまり、0.5、0.25、0.125、0.0625・・・・の合計値で表現されます。

このため、0.1等10進数では普通に表現できる数値もほとんど循環小数になってしまい、コンピュータ上では倍精度にしようと何をしようと微小な打切り誤差を含む数値となってしまいます。

ですから、4つの数字の合計を求めた場合、いくら見かけ上正確でも、0.25、0.25、0.5、0のように、たまたま打切り誤差が絶対に発生しないケース以外、正確に1.0となることは期待できません。

Excelのシート式でやった場合、Excelがいろいろサービスしてくれるので、誤差が表に出ない場合もあるようですが、この場合でも100%の保証はできません。

ですから、

(Worksheets("メニュー＆初期設定").Cells(11, 3).Value + _

   Worksheets("メニュー＆初期設定").Cells(12, 3).Value + _

   Worksheets("メニュー＆初期設定").Cells(13, 3).Value + _

   Worksheets("メニュー＆初期設定").Cells(14, 3).Value) ＋0.0000000001<1

等わずかに1より小さくてもTrueになるようにするしかないです。

ABS((Worksheets("メニュー＆初期設定").Cells(11, 3).Value + _

   Worksheets("メニュー＆初期設定").Cells(12, 3).Value + _

   Worksheets("メニュー＆初期設定").Cells(13, 3).Value + _

   Worksheets("メニュー＆初期設定").Cells(14, 3).Value) -1）>0.000000001　

（こちらは合計の上限が1ですから、仮に1.0より微妙に大きいか小さい物も含めてニアリー1.0になった場合以外は１より小さいとみなす条件です。）

、

ただ、難しいのは浮動小数点の足し算、引き算は小数点を同じ位置に揃えて、最上位の位から15ケタくらいを取り出して計算するため、4つの数字の内極めて小さい物が存在すると右にシフトされはみ出してしまい、0扱いになってしまいます。ですから、この4個の数字にどのようなものが入るかにより、微小数とする値を決める必要があり、どんな場合にも問題が起きないようにするのは不可能に近いです。

参考まで。