Округление в большую сторону эксель - Учим Эксель

Vba округлить в большую сторону

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

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

В итоге вы получите это:

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

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

  1. Сумма начальных чисел: 1.5 + 2.5 + 3.5 +4.5 = 12
  2. Сумма округлых чисел: 2 + 2 + 4 + 4 = 12

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

Оператор WorksheetFunction.Round

Если для вас нужно принятое математическое округление, используйте встроенную функцию рабочего листа — WorksheetFunction.Round.

Скопируйте в модуль VBA функцию с внедрением WorksheetFunction.Round и запустите ее выполнение:

Итог будет последующий:

Вышло то, что мы и ждали.

  1. Сумма начальных чисел: 1.5 + 2.5 + 3.5 +4.5 = 12
  2. Сумма округлых чисел: 2 + 3 + 4 + 5 = 14

Итог очевиден — в данном случае сумма округлых чисел на 2 единицы больше суммы начальных.

Исходя из этого результата, можно прийти к выводу, что «банковское» округление при помощи оператора Round дает наиболее четкие результаты при массовых операциях с округлыми числами. Но в ежедневной жизни почаще встречается обыденное математическое округление.

Выбирайте округление, которое для вас больше подступает для решаемой задачки!

Округление – экселевские причуды и несоответствия одной из «причуд» Excelя, которая может вызвать некую неурядицу, если о ней не знаешь — это применение программкой 2-ух различных алгоритмов округления чисел. Дальше обо всем по порядку.

Интересно почитать:  Эксель как вставить дату в

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

Последствия существования «причуд» на рисунке ниже:

Как вы сможете созидать на рисунке, результаты округления (и итоги) СУЩЕСТВЕННО ОТЛИЧАЮТСЯ!

Все что было изготовлено, так это округлено каждое из чисел, а потом суммирование их. В первом случае (столбец B) был округлен при помощи обычной экселевской функции ОКРУГЛ, во 2-м случае (столбец C) – было применено написанную в VBA функцию юзера, которая, но, не делает ничего другого, не считая как округляя обозначенные числа, но используя доступную в VBA функцию Round(). Чтоб поглядеть соответственный код макроса для пользовательской функции нажмите комбинацию жарких кнопок ALT+F11:

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

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

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

В случае использования доступных в VBA функций, (не считая Round () это также CByte (), CInt (), CLng () и CCur () ) употребляется метод, именуемый банковским либо способом Гаусса. Тут «половинки» округляются раз в большую сторону, а раз в наименьшую сторону, постоянно к наиблежайшему четному. Потому 5,5, как и в случае использования экселевской функции, будет округлено до 6. Но VBA поступит по другому, к примеру, с числом 2,5 — оно будет округлено до 2, а не до 3!

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

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

Интересно почитать:  Сводная таблица в excel из нескольких таблиц как сделать

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

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

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

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

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

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

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

Жалко, что эти два метода были реализованы сразу, не считая того, достаточно беспорядочным образом. Может быть было бы наиболее естественным, если б функция VBA Round() возвращала этот же итог, что и экселевская функция ОКРУГЛ (в конце концов, в британской версии Excelя, она именуется также — ROUND).

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

Интересно почитать:  Olap кубы в excel

1-ый метод округления при помощью функции int().

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

2-ой метод округление с помощью функции fix()

происходит аналогично с int . Различается эта функция тем, что округляет отрицательные числа до наименьшего значения. Так округлённое число Пи со знаком минус будет fix( -3.1415926535) = 3.
Если требуется не попросту убрать дробную часть, а округлить до целого числа, можно употреблять последующую функцию cint().
Пример:
Cint(fix( -3.1415926535)) = 3
Cint(int( -3.1415926535)) = 3

Как округлять до 10-х и сотых (один и два знака опосля запятой, соответственно).

Для округления до 10-х следует помножить переменную на 10 (10), опосля чего же округлить до целых и поделить на 10 (10).
Пример:
Cint(int( -3.1415926535*10))/10 = 3.1
Cint(fix( -3.1415926535*10))/10 = 3.1

Для округления до сотых следует сделать ту же самую операцию, лишь множить и разделять не на 10, а на 100.
Пример:
Cint(int( -3.1415926535*100))/100 = 3.14
Cint(fix( -3.1415926535*100))/100 = 3.14

Пример кода округляющего значения из textbox, запускающийся нажатием клавиши CommandButtom1:

Private sub CommandButtom1_click()
textbox2.value = Cint(int(textbox1.value *100))/100
END SUB

Видео с примером работы макроса:

Видите ли, ничего сложного в округлении чисел в vba Excel нет. Фортуны Для вас в исследовании программки

Схожее:

  1. Макрос определяющий пустая ли ячейка либо заполненная в VBA ExcelМакрос проверки наполнения ячеек. Временами при разработке.
  2. Функция VAL в VBA Excel либо как конвертировать TextBox в число (цифру).Использования функции преобразования текста в число в.
  3. Макрос для резвой подмены формул на значения (числа) в выделенных ячейках документа Excel.Когда комфортно поменять формулы на значения нажатием.

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

Cint(int(-3.1415926535*100))/100 = 3.1
А это надёжно? Я заместо соткой поставил 1000 и в одном из случаев произошла ошибка переполнения. Снова поставил сотки.

Ссылка на основную публикацию
Adblock
detector