Как округлить с помощью excel VBA round ()?

Оператор Round

Знаете ли вы, что при использовании оператора Round для округления чисел в VBA Excel вы можете получить совершенно другой результат, чем вы ожидали? И это действительно так!

Скопируйте следующую процедуру в модуль VBA и запустите ее:

В результате вы получите вот что:

Невероятно, правда? Как это произошло?
Дело в том, что оператор Round выполняет «бухгалтерское» (или «банковское») округление, которое призвано минимизировать ошибку при большом количестве таких операций. Это достигается тем, что оператор Round использует правило округления, отличное от того, которое мы знаем из школы, когда округляемое число увеличивается на единицу, если число, которое нужно отбросить, равно пяти. Суть округления с помощью оператора Round заключается в том, что если перед отброшенной пятеркой стоит нечетная цифра, то она увеличивается на единицу (округление в большую сторону), а если перед ней стоит четная цифра, то не увеличивается. (округление в меньшую сторону).

Вы также можете сформулировать «бухгалтерское» округление следующим образом: после исключения пятерки число округляется до ближайшего четного. Обратите внимание, что в результатах нашего примера все полученные числа четные.
Проверим ошибку:

1. Сумма исходных чисел: 1,5 + 2,5 + 3,5 +4,5 = 12
2. Сумма округленных чисел: 2 + 2 + 4 + 4 = 12

Результат оказался идеальным для данного примера, так как суммы исходных и округленных чисел равны.

Подытожим особенности округления чисел в Excel

Эти различия следует учитывать при создании макросов, выполняющих операции округления чисел. Как видите, результат, возвращаемый макросом, может полностью отличаться от ожидаемого.

Как упоминалось выше, один алгоритм не лучше и не хуже другого. Каждый из них хорош в зависимости от приложения. Стандартный алгоритм обеспечивает результаты, которые мы «ожидаем», в отличие от банковского алгоритма, который, помимо прочего, немного удивляет результатами.

жалко, что эти два алгоритма были реализованы одновременно, причем довольно хаотично. Возможно, было бы естественнее, если бы функция VBA Round () возвращала тот же результат, что и функция ROUND в Excel (ведь в английской версии Excel она еще называется ROUND).

Чаще всего при создании алгоритмов вычисления определенных значений полученные результаты имеют значение десятичной дроби с большим количеством десятичных знаков. Работать с такими данными и просматривать их не очень удобно и некрасиво.
Рассмотрим, какие функции следует использовать для округления данных в коде Excel vba.

Банковское округление чисел в Excel и VBA

Вреден ли банковский алгоритм? Само применение банковского алгоритма не является чем-то необычным, не говоря уже о плохом. Поскольку в зависимости от области применения он имеет свои весомые преимущества. Стандартное округление (всегда в большую сторону) в случае выполнения последовательных арифметических операций с большим количеством округленных этим методом значений приводит к накоплению ошибок округления. Вот небольшой пример:

Здесь то же, что и в первом примере. Каждое число округляется, а затем суммируются итоги. Итоговые суммы сравниваются с общей суммой, которая была бы получена, если бы числа не были округлены. Как видите, при применении банковского алгоритма округления чисел, используемого в VBA, итоговая сумма намного точнее, чем «исходная» сумма. Это связано с тем, что банковский алгоритм округлением в большую или меньшую сторону «амортизирует» ошибки, возникающие в результате округления.

Разный результат округления в Excel и VBA

Последствия существования «странностей» на следующем рисунке:

Как видно на фото, результаты округления (и итогов) РАЗНЫЕ!

Все, что было сделано, — это округлить каждое из чисел, а затем сложить их. В первом случае (столбец B) он был округлен с использованием стандартной функции Excel ROUND, во втором случае (столбец C) использовалась пользовательская функция, написанная на VBA, которая, однако, только округляет указанные числа, но использует функцию Round () доступен в VBA. Чтобы просмотреть соответствующий код макроса для пользовательской функции, нажмите комбинацию горячих клавиш ALT + F11:

Module1 содержит код для пользовательской функции макроса.

Первый способ округления при помощью функции int().

Эта функция печатает целое число до запятой. При округлении отрицательных чисел int округляет десятичную дробь до большего целого значения.
Например: плюс плюс минус после округления — int (-3,1415926535) будет равно 4 (четыре целых числа).

Округление до ближайшего высшего целого числа в VBA

Я пытаюсь подсчитать, сколько слоев будет складываться в одном продукте. У меня есть переменное количество (iQty), заданная ширина загруженного холста (dRTW), ширина на статью (dWidth) и количество на слой (iLayerQty).

Количество на слой рассчитывается как iLayerQty = Int (dRTW / dWidth)

Теперь мне нужно разделить сумму на количество в слое и округлить в большую сторону. В формуле Excel это было бы легко, но я стараюсь избегать вызовов WorksheetFunction, чтобы минимизировать путаницу с A1 / R1C1. В настоящее время я подхожу к этому с этим:

И в большинстве случаев он отлично работает, если числа не дают целого числа (например, ширина груза 0,5 м подходит для роллтрейлера 2,5 м). В таких случаях, конечно, добавление единицы разрушает результат.

Есть ли удобный способ изменить эту формулу, чтобы получить наилучшее округление?

Функция ОКРУГЛ и Round VBA округляют по-разному?

Функция ОКРУГЛ, доступная в Excel, использует стандартный алгоритм, в котором половины (пять в конце десятичного знака) ВСЕГДА округляются в большую сторону. То есть 2,5 округляется до 3; 10.345 — 10.35 и т.д. Это именно то правило, которое мы выучили в школе, и результат, которого большинство из нас ожидает достичь.

В случае использования функций, доступных в VBA (кроме Round (), это также CByte (), CInt (), CLng () и CCur ()), используется алгоритм, называемый банковским или гауссовским методом. Здесь «половинки» округляются один раз вверх и один раз вниз, всегда до ближайшего равного. Следовательно, 5.5, как и в случае использования функции excel, будет округлено до 6. Однако VBA будет действовать иначе, например, с числом 2.5: оно будет округлено до 2, а не до 3!

Округление значений в коде VBA в Excel. Функции int () и fix().

Чаще всего при создании алгоритмов вычисления определенных значений полученные результаты имеют значение десятичной дроби с большим количеством десятичных знаков. Работать с такими данными и просматривать их не очень удобно и некрасиво.
Рассмотрим, какие функции следует использовать для округления данных в коде Excel vba.

Несоответствия функций округления в Excel и VBA

Вы ожидаете, что Excel выполнит простое разделение: функции Excel используют один алгоритм, а функции VBA — другой. Это все равно было бы странно, но, по крайней мере, в нем была бы какая-то внутренняя последовательность. К сожалению, программисты Microsoft по какой-то причине были очень непоследовательны и, например, другие функции VBA, такие как FormatNumber или Format (при форматировании чисел с помощью этих функций, в зависимости от выбранного формата, также может происходить округление) используют алгоритм стандартного округления:

Следовательно, функция VBA Round (2,5, 0) вернет в качестве результата 2, а функция FormatNumber (2,5, 0) вернет в качестве результата 3. И где здесь логика?