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