Округление чисел в VBA Excel с помощью оператора Round и встроенной функции рабочего листа WorksheetFunction.Round. Особенности бухгалтерского и обычного округления.
Оператор Round
А вы знаете, что при использовании для округления чисел в VBA Excel оператора Round, вы можете получить совершенно не тот результат, который ожидали? И ведь это действительно так!
Скопируйте в модуль VBA следующую процедуру и запустите ее выполнение:
1 2 3 4 5 6 7 8 9 10 11 |
Sub Test_1() Dim a1 As Single, a2 As Single, a3 As Single, a4 As Single a1 = Round(1.5, 0) a2 = Round(2.5, 0) a3 = Round(3.5, 0) a4 = Round(4.5, 0) MsgBox "Round(1.5, 0)=" & a1 & vbNewLine & _ "Round(2.5, 0)=" & a2 & vbNewLine & _ "Round(3.5, 0)=" & a3 & vbNewLine & _ "Round(4.5, 0)=" & a4 End Sub |
В результате вы получите это:
Удивительно, не правда ли? Как же так получилось?
Дело в том, что оператор Round осуществляет «бухгалтерское» (или «банковское») округление, которое призвано при большом количестве таких операций свести погрешность к минимуму. Это достигается за счет того, что оператор Round использует при округлении правило, отличное от того, которое мы знаем еще со школы, когда округляемое число увеличивается на единицу, если отбрасываемое число равно пяти. Суть округления с помощью оператора Round состоит в том, что если перед отбрасываемой пятеркой стоит нечетная цифра, то она увеличивается на единицу (округление вверх), а если перед ней стоит четная цифра, то она не увеличивается (округление вниз).
Еще можно сформулировать «бухгалтерское» округление так: при отбрасывании пятерки число округляется к ближайшему четному. Обратите внимание, что в результатах нашего примера все полученные числа — четные.
Проверим погрешность:
- Сумма исходных чисел: 1.5 + 2.5 + 3.5 +4.5 = 12
- Сумма округленных чисел: 2 + 2 + 4 + 4 = 12
Результат получился просто идеальный для приведенного примера, так как суммы исходных и округленных чисел равны.
Оператор WorksheetFunction.Round
Если вам необходимо общепринятое математическое округление, используйте встроенную функцию рабочего листа — WorksheetFunction.Round.
Скопируйте в модуль VBA процедуру с использованием WorksheetFunction.Round и запустите ее выполнение:
1 2 3 4 5 6 7 8 9 10 11 |
Sub Test_2() Dim a1 As Single, a2 As Single, a3 As Single, a4 As Single a1 = WorksheetFunction.Round(1.5, 0) a2 = WorksheetFunction.Round(2.5, 0) a3 = WorksheetFunction.Round(3.5, 0) a4 = WorksheetFunction.Round(4.5, 0) MsgBox "WorksheetFunction.Round(1.5, 0)=" & a1 & vbNewLine & _ "WorksheetFunction.Round(2.5, 0)=" & a2 & vbNewLine & _ "WorksheetFunction.Round(3.5, 0)=" & a3 & vbNewLine & _ "WorksheetFunction.Round(4.5, 0)=" & a4 End Sub |
Результат будет следующий:
Получилось то, что мы и ожидали.
Проверим погрешность:
- Сумма исходных чисел: 1.5 + 2.5 + 3.5 +4.5 = 12
- Сумма округленных чисел: 2 + 3 + 4 + 5 = 14
Результат очевиден — в данном случае сумма округленных чисел на 2 единицы больше суммы исходных.
Исходя из этого результата, можно сделать вывод, что «банковское» округление с помощью оператора Round дает более точные результаты при массовых операциях с округленными числами. Но в повседневной жизни чаще встречается обычное математическое округление.
Выбирайте округление, которое вам больше подходит для решаемой задачи!
Есть ещё один вариант,
который правильно округляет, но при этом
не является функцией рабочего листа:
Format(Число, "#,##0.")
Вообще-то это не бухгалтерское округление, а математическое.
Сошлюсь на статью из Википедии.
Варианты округления 0,5 к ближайшему целому:
Математическое округление – округление всегда в бо́льшую по модулю сторону (предыдущий разряд всегда увеличивается на единицу).
Чередующееся округление – округление происходит в меньшую или большую сторону поочередно.
Чередующееся округление – это и есть бухгалтерское округление.
Обсуждение закрыто.