久しぶりのExcel記事です。
剰余演算子(割り算の余りを求める演算子)であるModの考え方を書いていきます。
予想していた結果と違う???
普段、割り算の余りを求めたいってなったときに、以下のような結果を求めますよね。
- 9 ÷ 4.5 = 2 余り 0
- 7 ÷ 2.8 = 2 余り 1.4
- 8 ÷ 0.4 = 20 余り 0
- 9.5 ÷ 2.5 = 4 余り 0.5
これをExcel VBA のMod演算子を使用して式にすると、結果として下記のような割り算の余りが返ってくるはずです。
- 9 Mod 4.5 → 0
- 7 Mod 2.8 → 1.4
- 8 Mod 0.4 → 0
- 9.5 Mod 2.5 → 0.5
しかし、実際にイミディエイトウィンドウを使用して計算させると以下のように意外な結果が返ってきます。
- 9 Mod 4.5 → 1
- 7 Mod 2.8 → 1
- 8 Mod 0.4 → 0除算エラー
- 9.5 Mod 2.5 → 0
全然違いますよね。整数では正しく余りが求められるのに不思議です。
なぜ、このような結果が返ってくるのでしょうか。
それらの原因をこれから説明したいと思います。
Excel VBA の Mod の考え方
ExcelVBAのModといえば割り算の余りが返ってくる演算子であると皆さん認識されていると思います。
これはあながち間違えではありません。しかし、このModの計算に小数が入ってくると少々考え方が変わります。
Microsoftの説明では以下のように解説がされています。
剰余演算子は、数式 number1 を number2 で除算し、その余りを演算結果 result として返します。このとき浮動小数点数は整数に丸められます。たとえば、次に示す式では、変数 A (演算結果 result) の値は 5 になります。
A = 19 Mod 6.7
一方または両方の式が Null 値のときは、演算結果 result も Null 値になります。Empty 値を持つ式は、0 として処理されます。
これを見て分かることは浮動小数点数は整数に丸められるということ。
この中で浮動小数点数というのは小数点第一位以下を表します。
そして、【整数に丸められる】というのは四捨五入に近い意味を表します。
四捨五入に近いというのがミソで、四捨五入とは少しだけ違います。
【整数に丸められる】では、小数点以下の数値が0.5である時に1の位が偶数になるように切り捨てor切り上げられるのです。
四捨五入では0.5であれば常に切り上げられるので注意が必要です。
例)
- 3.5 → 4 (切り上げ)
- 6.5 → 6 (切り捨て)
この切り捨て切り上げ方式を銀行型丸めといいます。
では実際に、上記Microsoftの説明にある計算式、19 Mod 6.7 の結果を整数に丸めてみましょう。
19 Mod 6.7 = 5.6 →(整数に丸める)→ 6
しかし、Microsoftの説明では結果は【5】となっていますね。
ここでも違いがあります。ではどうして結果に違いが出るのでしょうか。
このMod(剰余演算子)では、計算式の結果を整数にまとめるのではなく、計算する前に計算式にある小数を整数にまとめるからです。
つまり、、、
19 Mod 6.7 →(整数に丸める)→ 7 = 5
ということなのです。
結論として、Excel VBAのMod(剰余演算子)では【計算式の小数を整数に丸めてから割り算の余りを求める】ことになります。
対応策
VBAで割り算の余りを求めるときに計算式の小数を整数に丸めたくない、ちゃんと余りを出してほしい!という方も多いでしょう。その方たちへの対応策を用意しました。
【Excel VBA で割り算の余りを求める関数 Modulo】
1 2 3 4 5 6 7 8 |
'Numerator 分子(被除数) 'Denominator 分母(除数) 'Modulo 剰余 Public Function Modulo(Numerator As Currency, Denominator As Currency) As Currency Modulo = Numerator - Int(Numerator / Denominator) * Denominator End Function |
このコードをExcelの標準モジュールに貼り付けて以下のようなコードを書くと変数aに【5.6】が返ってきます。
【コード : 19÷6.7 の余りを求める】
変数a = Modulo(19,6.7)
他の割り算も試してみて下さい。求めていた結果が返ってくるはずです。
説明は以上となります。
いかがだったでしょうか?
不明な点があればお気軽にお問い合わせください!
では、ノシ
---コメント---
[Excel VBA]剰余演算子であるModの考え方の記事にあるFunctionを利用させていただきました。
今年2021年6月末から楽天証券のMarketSpeed2でRSSが利用できるようになり、自動発注処理をプログラミングするのにまさにこの関数の機能を求めていました。
情報提供に感謝します。
【Excel VBA で割り算の余りを求める関数 Modulo】を利用させていただきました。
GPSから流れてくる緯度経度の10進数変換等への利用でセル上ではMOD関数で出来たのですがマクロ化で困っていました。
本当に助かりました。ありがとうございます。