Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel округление

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

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

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

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

Второй способ округление при помощи функции fix()

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

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

Для округления до десятых следует умножить переменную на 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 и в одном из случаев произошла ошибка переполнения. Опять поставил сотни.

Округление до целого числа функциями ОКРУГЛ и FormatNumber в VBA

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

Читать еще:  Функция log excel

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

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

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

Все что было сделано, так это округлено каждое из чисел, а затем суммирование их. В первом случае (столбец B) был округлен с помощью стандартной экселевской функции ОКРУГЛ, во втором случае (столбец 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

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

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

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

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

Читать еще:  Как вставить числа в excel

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

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

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

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

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

Округление числа в Excel

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Допустим, вы хотите округлить число до ближайшего целого числа, так как десятичные значения несущественны. Вы также можете округлить число до кратных 10, чтобы упростить приблизительную величину. Есть несколько способов округлить число.

Изменение количества знаков после запятой без изменения значения

На листе

Выделите ячейки, формат которых требуется изменить.

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

Во встроенном числовом формате

На вкладке Главная в группе Число щелкните стрелку рядом со списком числовых форматов и выберите пункт Другие числовые форматы.

В списке Категория выберите значение Денежный, Финансовый, Процентный или Экспоненциальный в зависимости от типа данных.

В поле Число десятичных знаков введите требуемое число знаков после запятой.

Читать еще:  Excel vba текущее время

Округление числа вверх

Используйте функцию ОКРУГЛВВЕРХ. В некоторых случаях может потребоваться использовать функции ЧЁТН и НЕЧЁТ для округления вверх до ближайшего четного или нечетного числа.

Округление числа вниз

Округление числа до ближайшего значения

Округление числа до ближайшего дробного значения

Округление числа до указанного количества значимых разрядов

Значимые разряды — это разряды, которые влияют на точность числа.

В примерах этого раздела используются функции ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ. Они показывают способы округления положительных, отрицательных, целых и дробных чисел, но приведенные примеры охватывают лишь небольшую часть возможных ситуаций.

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

Округляемые отрицательные числа прежде всего преобразуются в абсолютные значения (значения без знака «минус»). После округления знак «минус» применяется повторно. Хотя это может показаться нелогичным, именно так выполняется округление. Например, при использовании функции ОКРУГЛВНИЗ для округления числа -889 до двух значимых разрядов результатом является число -880. Сначала -889 преобразуется в абсолютное значение (889). Затем это значение округляется до двух значимых разрядов (880). После этого повторно применяется знак «минус», что дает в результате -880.

При применении к положительному числу функции ОКРУГЛВНИЗ оно всегда округляется вниз, а при применении функции ОКРУГЛВВЕРХ — вверх.

Функция ОКРУГЛ округляет дробные числа следующим образом: если дробная часть больше или равна 0,5, число округляется вверх. Если дробная часть меньше 0,5, число округляется вниз.

Функция ОКРУГЛ округляет целые числа вверх или вниз аналогичным образом, при этом вместо делителя 0,5 используется 5.

В общем при округлении числа без дробной части (целого числа) необходимо вычесть длину числа из нужного количества значимых разрядов. Например, чтобы округлить 2345678 вниз до 3 значимых разрядов, используется функция ОКРУГЛВНИЗ с параметром -4: = ОКРУГЛВНИЗ(2345678,-4). При этом число округляется до значения 2340000, где часть «234» представляет собой значимые разряды.

Округление числа до заданного кратного

Иногда может потребоваться округлить значение до кратного заданному числу. Например, допустим, что компания поставляет товары в ящиках по 18 единиц. С помощью функции ОКРУГЛТ можно определить, сколько ящиков потребуется для поставки 204 единиц товара. В данном случае ответом является 12, так как число 204 при делении на 18 дает значение 11,333, которое необходимо округлить вверх. В 12-м ящике будет только 6 единиц товара.

Может также потребоваться округлить отрицательное значение до кратного отрицательному или дробное — до кратного дробному. Для этого также можно применять функцию ОКРУГЛТ.

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