知らずに落ちると抜け出せなくなるVBAの落とし穴です。
平均などの計算でシート関数とVBAで違う値が出た場合は、
この落とし穴に落ちていないかご確認ください。
VBAのRound関数はシート関数「ROUND」と異なり、
四捨五入ではなく「銀行丸め」と呼ばれる端数(小数)処理を行います。
(銀行丸めも四捨五入の方式のひとつなので、
「ではなく」という表現は厳密には正しくないかもしれませんが)
銀行丸めとは「ピッタリX.5の小数を近い偶数に寄せる端数処理」のことで、
以下のような結果となっています。
| 対象値 | Round(VBA) | ROUND(シート) |
|---|---|---|
| 0.5 | 0 | 1 |
| 1.5 | 2 | 2 |
| 2.5 | 2 | 3 |
| 3.5 | 4 | 4 |
| 4.5 | 4 | 5 |
| 5.5 | 6 | 6 |
| 6.5 | 6 | 7 |
| 7.5 | 8 | 8 |
| 8.5 | 8 | 9 |
| 9.5 | 10 | 10 |
通常の四捨五入とは違う結果になりますのでご注意ください。
対策は簡単で、単にWorksheetFunction.Roundを使用するだけでOKです。
さてあとは豆知識みたいなものですが、この「銀行丸め」、
実は四捨五入よりこちらの方が丸めの精度が良いとされています。
四捨五入の理屈としては、
- 切り捨てチーム：0,1,2,3,4
- 切り上げチーム：5,6,7,8,9
で5つずつ平等に切り分けている的なイメージです。
より数学っぽく書くと、
- 切り捨て：.0～.499999…
- 切り上げ：.5～.999999…
で同じ大きさの区間二つに分けています。
しかし「この割り方で平等になる」という理屈が成り立つには、
| すべての小数は出現確率が同じである |
ことが条件なのですが、実際はそんなことはありません。
詳しい計算は省きますが、何となくイメージしてみると
.423と.500の出現確率は後者の方が高そうな気がしませんか？
前者はかなりレアなのですが、後者は分母が分子の倍数なら頻繁に発生します。
例えば「1ヶ月あたりの平均売上」を「年間売上/12」で計算してみましょう。
0.500は「年間売上が6の倍数かつ12の倍数でない」なら出現しますので、
8.33%という相当な確率で「ピッタリX.5」が出現することになります。
このくらいしょっちゅう出てくる「.5」は他の小数より出現率が高いため、
0.5を切り上げると切り上げチームがちょっと有利になってしまうんですね。
よって通常の四捨五入はわずかに正のバイアスがかかることが知られています。
現に先ほどの表の合計を計算して見ると、
| 対象値 | Round(VBA) | ROUND(シート) |
|---|---|---|
| 0.5 | 0 | 1 |
| 1.5 | 2 | 2 |
| 2.5 | 2 | 3 |
| 3.5 | 4 | 4 |
| 4.5 | 4 | 5 |
| 5.5 | 6 | 6 |
| 6.5 | 6 | 7 |
| 7.5 | 8 | 8 |
| 8.5 | 8 | 9 |
| 9.5 | 10 | 10 |
| 50 | 50 | 55 |
こんな風に「ただ合計するより四捨五入してから合計した方が合計値が高くなる」現象がよく起きます。
これを解決するのが「銀行丸め」であり、先ほどのチーム分けを
- 切り捨て：.0～.499999…
- 切り上げ：.5000…001～.999999…
こう分けておき、さらにピッタリ「X.5」になるときは、
- 切り捨て：Xが0,2,4,6,8
- 切り上げ：Xが1,3,5,7,9
でチーム分けをすることで、両者をなるべく平等にしています。
ちなみにこんな仕組みなので、
?round(2.5) ' 2 ?round(2.5000000001) ' 3
と、.5を少しでも上回れば切り上げに切り替わります。
整数(小数第0位)の端数処理において四捨五入では小数第1位しか見ませんが、
「銀行丸め」では末尾の値まで見て判断をしてくれます。
と、以上が銀行丸めの仕組みと存在意義です。
知ってどうという話ではありませんが、心の片隅にでも置いておいてください。
