Excel VBA の関数による数値丸めの違い

エクセルでの処理を他の言語などに移植するとき、キャスト・四捨五入・切り捨てなどの動作が異るため、計算結果にずれが生じてしまう。
エクセルの各丸め処理の違いを把握しやすいよう、比較できるマクロを記述してみた。

丸め処理の種類についてはwikipediaに詳しく記述されている
https://ja.wikipedia.org/w/index.php?title=%E7%AB%AF%E6%95%B0%E5%87%A6%E7%90%86&redirect=no

|Excel VBAのキャスト比較||||||||
|:------------:|:------------:|:------------:|:------------:|:------------:|:------------:|:------------:|:------------:|
|キャスト前|暗黙のキャスト|Int()|Round()|Worksheet Function. Round()|Worksheet Function. RoundDown()|Worksheet Function. RoundUp()|CLng()|
||最近接偶数への丸め(負数は絶対値について)|負の無限大への丸め|最近接偶数への丸め(負数は絶対値について)|四捨五入|0への丸め|無限大への丸め|最近接偶数への丸め(負数は絶対値について)|
|-10|-10|-10|-10|-10|-10|-10|-10|
|-9.9|-10|-10|-10|-10|-9|-10|-10|
|-9.8|-10|-10|-10|-10|-9|-10|-10|
|-9.7|-10|-10|-10|-10|-9|-10|-10|
|-9.6|-10|-10|-10|-10|-9|-10|-10|
|-9.5|-10|-10|-10|-10|-9|-10|-10|
|-9.4|-9|-10|-9|-9|-9|-10|-9|
|-9.3|-9|-10|-9|-9|-9|-10|-9|
|-9.2|-9|-10|-9|-9|-9|-10|-9|
|-9.1|-9|-10|-9|-9|-9|-10|-9|
|-9|-9|-9|-9|-9|-9|-9|-9|
|-8.9|-9|-9|-9|-9|-8|-9|-9|
|-8.8|-9|-9|-9|-9|-8|-9|-9|
|-8.7|-9|-9|-9|-9|-8|-9|-9|
|-8.6|-9|-9|-9|-9|-8|-9|-9|
|-8.5|-8|-9|-8|-9|-8|-9|-8|
|-8.4|-8|-9|-8|-8|-8|-9|-8|
|-8.3|-8|-9|-8|-8|-8|-9|-8|
|-8.2|-8|-9|-8|-8|-8|-9|-8|
|-8.1|-8|-9|-8|-8|-8|-9|-8|
|-8|-8|-8|-8|-8|-8|-8|-8|
|-7.9|-8|-8|-8|-8|-7|-8|-8|
|-7.8|-8|-8|-8|-8|-7|-8|-8|
|-7.7|-8|-8|-8|-8|-7|-8|-8|
|-7.6|-8|-8|-8|-8|-7|-8|-8|
|-7.5|-8|-8|-8|-8|-7|-8|-8|
|-7.4|-7|-8|-7|-7|-7|-8|-7|
|-7.3|-7|-8|-7|-7|-7|-8|-7|
|-7.2|-7|-8|-7|-7|-7|-8|-7|
|-7.1|-7|-8|-7|-7|-7|-8|-7|
|-7|-7|-7|-7|-7|-7|-7|-7|
|-6.9|-7|-7|-7|-7|-6|-7|-7|
|-6.8|-7|-7|-7|-7|-6|-7|-7|
|-6.7|-7|-7|-7|-7|-6|-7|-7|
|-6.6|-7|-7|-7|-7|-6|-7|-7|
|-6.5|-6|-7|-6|-7|-6|-7|-6|
|-6.4|-6|-7|-6|-6|-6|-7|-6|
|-6.3|-6|-7|-6|-6|-6|-7|-6|
|-6.2|-6|-7|-6|-6|-6|-7|-6|
|-6.1|-6|-7|-6|-6|-6|-7|-6|
|-6|-6|-6|-6|-6|-6|-6|-6|
|-5.9|-6|-6|-6|-6|-5|-6|-6|
|-5.8|-6|-6|-6|-6|-5|-6|-6|
|-5.7|-6|-6|-6|-6|-5|-6|-6|
|-5.6|-6|-6|-6|-6|-5|-6|-6|
|-5.5|-6|-6|-6|-6|-5|-6|-6|
|-5.4|-5|-6|-5|-5|-5|-6|-5|
|-5.3|-5|-6|-5|-5|-5|-6|-5|
|-5.2|-5|-6|-5|-5|-5|-6|-5|
|-5.1|-5|-6|-5|-5|-5|-6|-5|
|-5|-5|-5|-5|-5|-5|-5|-5|
|-4.9|-5|-5|-5|-5|-4|-5|-5|
|-4.8|-5|-5|-5|-5|-4|-5|-5|
|-4.7|-5|-5|-5|-5|-4|-5|-5|
|-4.6|-5|-5|-5|-5|-4|-5|-5|
|-4.5|-4|-5|-4|-5|-4|-5|-4|
|-4.4|-4|-5|-4|-4|-4|-5|-4|
|-4.3|-4|-5|-4|-4|-4|-5|-4|
|-4.2|-4|-5|-4|-4|-4|-5|-4|
|-4.1|-4|-5|-4|-4|-4|-5|-4|
|-4|-4|-4|-4|-4|-4|-4|-4|
|-3.9|-4|-4|-4|-4|-3|-4|-4|
|-3.8|-4|-4|-4|-4|-3|-4|-4|
|-3.7|-4|-4|-4|-4|-3|-4|-4|
|-3.6|-4|-4|-4|-4|-3|-4|-4|
|-3.5|-4|-4|-4|-4|-3|-4|-4|
|-3.4|-3|-4|-3|-3|-3|-4|-3|
|-3.3|-3|-4|-3|-3|-3|-4|-3|
|-3.2|-3|-4|-3|-3|-3|-4|-3|
|-3.1|-3|-4|-3|-3|-3|-4|-3|
|-3|-3|-3|-3|-3|-3|-3|-3|
|-2.9|-3|-3|-3|-3|-2|-3|-3|
|-2.8|-3|-3|-3|-3|-2|-3|-3|
|-2.7|-3|-3|-3|-3|-2|-3|-3|
|-2.6|-3|-3|-3|-3|-2|-3|-3|
|-2.5|-2|-3|-2|-3|-2|-3|-2|
|-2.4|-2|-3|-2|-2|-2|-3|-2|
|-2.3|-2|-3|-2|-2|-2|-3|-2|
|-2.2|-2|-3|-2|-2|-2|-3|-2|
|-2.1|-2|-3|-2|-2|-2|-3|-2|
|-2|-2|-2|-2|-2|-2|-2|-2|
|-1.9|-2|-2|-2|-2|-1|-2|-2|
|-1.8|-2|-2|-2|-2|-1|-2|-2|
|-1.7|-2|-2|-2|-2|-1|-2|-2|
|-1.6|-2|-2|-2|-2|-1|-2|-2|
|-1.5|-2|-2|-2|-2|-1|-2|-2|
|-1.4|-1|-2|-1|-1|-1|-2|-1|
|-1.3|-1|-2|-1|-1|-1|-2|-1|
|-1.2|-1|-2|-1|-1|-1|-2|-1|
|-1.1|-1|-2|-1|-1|-1|-2|-1|
|-1|-1|-1|-1|-1|-1|-1|-1|
|-0.9|-1|-1|-1|-1|0|-1|-1|
|-0.8|-1|-1|-1|-1|0|-1|-1|
|-0.7|-1|-1|-1|-1|0|-1|-1|
|-0.6|-1|-1|-1|-1|0|-1|-1|
|-0.5|0|-1|0|-1|0|-1|0|
|-0.4|0|-1|0|0|0|-1|0|
|-0.3|0|-1|0|0|0|-1|0|
|-0.2|0|-1|0|0|0|-1|0|
|-0.1|0|-1|0|0|0|-1|0|
|0|0|0|0|0|0|0|0|
|0.1|0|0|0|0|0|1|0|
|0.2|0|0|0|0|0|1|0|
|0.3|0|0|0|0|0|1|0|
|0.4|0|0|0|0|0|1|0|
|0.5|0|0|0|1|0|1|0|
|0.6|1|0|1|1|0|1|1|
|0.7|1|0|1|1|0|1|1|
|0.8|1|0|1|1|0|1|1|
|0.9|1|0|1|1|0|1|1|
|1|1|1|1|1|1|1|1|
|1.1|1|1|1|1|1|2|1|
|1.2|1|1|1|1|1|2|1|
|1.3|1|1|1|1|1|2|1|
|1.4|1|1|1|1|1|2|1|
|1.5|2|1|2|2|1|2|2|
|1.6|2|1|2|2|1|2|2|
|1.7|2|1|2|2|1|2|2|
|1.8|2|1|2|2|1|2|2|
|1.9|2|1|2|2|1|2|2|
|2|2|2|2|2|2|2|2|
|2.1|2|2|2|2|2|3|2|
|2.2|2|2|2|2|2|3|2|
|2.3|2|2|2|2|2|3|2|
|2.4|2|2|2|2|2|3|2|
|2.5|2|2|2|3|2|3|2|
|2.6|3|2|3|3|2|3|3|
|2.7|3|2|3|3|2|3|3|
|2.8|3|2|3|3|2|3|3|
|2.9|3|2|3|3|2|3|3|
|3|3|3|3|3|3|3|3|
|3.1|3|3|3|3|3|4|3|
|3.2|3|3|3|3|3|4|3|
|3.3|3|3|3|3|3|4|3|
|3.4|3|3|3|3|3|4|3|
|3.5|4|3|4|4|3|4|4|
|3.6|4|3|4|4|3|4|4|
|3.7|4|3|4|4|3|4|4|
|3.8|4|3|4|4|3|4|4|
|3.9|4|3|4|4|3|4|4|
|4|4|4|4|4|4|4|4|
|4.1|4|4|4|4|4|5|4|
|4.2|4|4|4|4|4|5|4|
|4.3|4|4|4|4|4|5|4|
|4.4|4|4|4|4|4|5|4|
|4.5|4|4|4|5|4|5|4|
|4.6|5|4|5|5|4|5|5|
|4.7|5|4|5|5|4|5|5|
|4.8|5|4|5|5|4|5|5|
|4.9|5|4|5|5|4|5|5|
|5|5|5|5|5|5|5|5|
|5.1|5|5|5|5|5|6|5|
|5.2|5|5|5|5|5|6|5|
|5.3|5|5|5|5|5|6|5|
|5.4|5|5|5|5|5|6|5|
|5.5|6|5|6|6|5|6|6|
|5.6|6|5|6|6|5|6|6|
|5.7|6|5|6|6|5|6|6|
|5.8|6|5|6|6|5|6|6|
|5.9|6|5|6|6|5|6|6|
|6|6|6|6|6|6|6|6|
|6.1|6|6|6|6|6|7|6|
|6.2|6|6|6|6|6|7|6|
|6.3|6|6|6|6|6|7|6|
|6.4|6|6|6|6|6|7|6|
|6.5|6|6|6|7|6|7|6|
|6.6|7|6|7|7|6|7|7|
|6.7|7|6|7|7|6|7|7|
|6.8|7|6|7|7|6|7|7|
|6.9|7|6|7|7|6|7|7|
|7|7|7|7|7|7|7|7|
|7.1|7|7|7|7|7|8|7|
|7.2|7|7|7|7|7|8|7|
|7.3|7|7|7|7|7|8|7|
|7.4|7|7|7|7|7|8|7|
|7.5|8|7|8|8|7|8|8|
|7.6|8|7|8|8|7|8|8|
|7.7|8|7|8|8|7|8|8|
|7.8|8|7|8|8|7|8|8|
|7.9|8|7|8|8|7|8|8|
|8|8|8|8|8|8|8|8|
|8.1|8|8|8|8|8|9|8|
|8.2|8|8|8|8|8|9|8|
|8.3|8|8|8|8|8|9|8|
|8.4|8|8|8|8|8|9|8|
|8.5|8|8|8|9|8|9|8|
|8.6|9|8|9|9|8|9|9|
|8.7|9|8|9|9|8|9|9|
|8.8|9|8|9|9|8|9|9|
|8.9|9|8|9|9|8|9|9|
|9|9|9|9|9|9|9|9|
|9.1|9|9|9|9|9|10|9|
|9.2|9|9|9|9|9|10|9|
|9.3|9|9|9|9|9|10|9|
|9.4|9|9|9|9|9|10|9|
|9.5|10|9|10|10|9|10|10|
|9.6|10|9|10|10|9|10|10|
|9.7|10|9|10|10|9|10|10|
|9.8|10|9|10|10|9|10|10|
|9.9|10|9|10|10|9|10|10|
|10|10|10|10|10|10|10|10|


```
Sub marume_test()
    Dim seisuu As Long
    Dim shousuu As Double
    Dim i As Double
    Dim double_to_long_address As String
    Dim item_name_address As String
    Dim round_kind_address As String
    Dim results_address As String
    
    
    double_to_long_address = Cells(1, 1).Address
    item_name_address = Range(double_to_long_address).Cells(2, 1).Address
    round_kind_address = Range(double_to_long_address).Cells(3, 1).Address
    results_address = Range(double_to_long_address).Cells(4, 1).Address
    
    
    Range(double_to_long_address).Value = "Excel VBAのキャスト比較"
    Range(item_name_address).Cells(1, 1).Value = "キャスト前"
    Range(item_name_address).Cells(1, 2).Value = "暗黙のキャスト"
    Range(round_kind_address).Cells(1, 2).Value = "最近接偶数への丸め(負数は絶対値について)"
    Range(item_name_address).Cells(1, 3).Value = "Int()"
    Range(round_kind_address).Cells(1, 3).Value = "負の無限大への丸め"
    Range(item_name_address).Cells(1, 4).Value = "Round()"
    Range(round_kind_address).Cells(1, 4).Value = "最近接偶数への丸め(負数は絶対値について)"
    Range(item_name_address).Cells(1, 5).Value = "WorksheetFunction.Round()"
    Range(round_kind_address).Cells(1, 5).Value = "四捨五入"
    Range(item_name_address).Cells(1, 6).Value = "WorksheetFunction.RoundDown()"
    Range(round_kind_address).Cells(1, 6).Value = "0への丸め"
    Range(item_name_address).Cells(1, 7).Value = "WorksheetFunction.RoundUp()"
    Range(round_kind_address).Cells(1, 7).Value = "無限大への丸め"
    Range(item_name_address).Cells(1, 8).Value = "CLng()"
    Range(round_kind_address).Cells(1, 8).Value = "最近接偶数への丸め(負数は絶対値について)"
    
    For i = 0 To 200
        shousuu = (i / 10) - 10
        Range(results_address).Cells(i + 1, 1).Value = shousuu
        seisuu = shousuu
        Range(results_address).Cells(i + 1, 2).Value = seisuu
        seisuu = Int(shousuu)
        Range(results_address).Cells(i + 1, 3).Value = seisuu
        seisuu = Round(shousuu, 0)
        Range(results_address).Cells(i + 1, 4).Value = seisuu
        seisuu = WorksheetFunction.Round(shousuu, 0)
        Range(results_address).Cells(i + 1, 5).Value = seisuu
        seisuu = WorksheetFunction.RoundDown(shousuu, 0)
        Range(results_address).Cells(i + 1, 6).Value = seisuu
        seisuu = WorksheetFunction.RoundUp(shousuu, 0)
        Range(results_address).Cells(i + 1, 7).Value = seisuu
        seisuu = CLng(shousuu)
        Range(results_address).Cells(i + 1, 8).Value = seisuu
    Next i
End Sub
```