Go to Qiita Advent Calendar Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@Mokeshi_

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

Posted at

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

丸め処理の種類については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
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?