Vba excel округление вверх
Обновлено: 04.07.2024
Очень часто при создании алгоритмов подсчета тех или иных значений полученные результаты имеют значение десятичной дроби с большим количеством знаков после запятой. Работать с такими данными и отображать их не очень удобно и некрасиво.
Рассмотрим какими функциями следует округлять данные в коде vba Excel.
Первый способ округления при помощью функции int().
Второй способ округление при помощи функции 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 нет. Удачи Вам в изучении программы
Похожее:
-
Макрос проверки заполнения ячеек. Периодически при создании.Использования функции преобразования текста в число в.Когда удобно менять формулы на значения нажатием.
Округление значений в коде VBA в Excel. Функции int () и fix().: 1 комментарий
Cint(int(-3.1415926535*100))/100 = 3.1
А это надёжно? Я вместо сотней поставил 1000 и в одном из случаев произошла ошибка переполнения. Опять поставил сотни.
Вы должны использовать функцию Excel Round для округления плавающих чисел до определенного количества десятичных знаков. Функция VBA Round работает совершенно иначе, чем та, которую вы используете в Excel. Функция VBA Round использует логику «округлить до четного». Если число, которое вы пытаетесь округлить, имеет последнюю цифру после десятичной дроби> = 0, 6, функция округления VBA округляет ее (округление в большую сторону).
Если число, которое вы пытаетесь округлить, имеет последнюю цифру после десятичной дроби <= 0, 4, оно округляет ее (округление вниз). Предположим, что десятичная часть точно равна 0, 5, что она делает? В таких случаях он проверяет целую часть числа. Если целая часть четная, то она округляется до четного числа десятичной части. Если целая часть нечетная, то она округляется до четного числа. Этот метод округления также называется «банковским округлением».
Синтаксис функции раунда в Excel VBA
Функция VBA Round имеет следующий синтаксис:
- Выражение - плавающее число, которое вы хотите округлить.
- Decimal_places - это необязательный аргумент, который принимает целочисленное значение, которое определяет десятичные разряды, до которых число должно быть круглым. Всегда должно быть больше или равно нулю. Если не указан, по умолчанию считается ноль. Это означает, что число округляется до целого числа.
Как использовать функцию Excel VBA Round?
Мы узнаем, как использовать функцию VBA Round, с несколькими примерами в Excel.
Вы можете скачать этот шаблон VBA Round Excel здесь - Шаблон VBA Round Excel
Выполните следующие шаги, чтобы использовать функцию округления в Excel VBA.
Шаг 1. Вставьте новый модуль в редактор Visual Basic (VBE).
Шаг 2: Определите новую подпроцедуру для сохранения макроса в VBE.
Код:
Код:
Код:
Шаг 5: Нажмите F5 или кнопку Run, расположенную на самой верхней панели, чтобы запустить этот код. Вы увидите вывод, как показано ниже.
Пример №2 - округление переменной с использованием VBA Round
Шаг 1: Определите новую подпроцедуру в VBE для хранения макроса.
Код:
Шаг 2: Определите переменную с именем roundNumber как Double, которая может содержать значение округляемого числа.
Код:
Шаг 3: Назначьте числовое значение, которое должно быть округлено до этой переменной, используя оператор присваивания.
Код:
Шаг 4: Используйте комбинацию MsgBox и функцию Round, чтобы округлить это число до одного десятичного знака.
Код:
Шаг 5: Давайте нажмем F5 или кнопку Run, чтобы запустить этот код и увидеть результат.
Вы можете увидеть результат, как показано на скриншоте выше. Обратите внимание, что с логикой «округлить до четного» последнее десятичное число округляется до 10, а следующее десятичное становится само 10, вследствие чего число округляется до ближайшей четной части целого числа (то есть 24).
Предположим, что значение, которое вы хотите округлить, хранится в одной ячейке вашего листа Excel.
Все, что вы хотите, это округлить это значение до двух десятичных знаков.
Шаг 1: Определите новую подпроцедуру в модуле для хранения макроса.
Код:
Шаг 2: Запишите команду как «Диапазон (« А2 »). Значение =». Эта команда работает как место, где выходные данные будут сохранены.
Код:
Шаг 3: Теперь используйте функцию округления, чтобы округлить значение, присутствующее в ячейке A1, и сохранить результат в ячейке A2. Напишите следующий фрагмент кода: Round (Range («A1»). Value, 2).
Код:
Шаг 4: Нажмите F5 или кнопку Run, чтобы запустить этот код и увидеть результат в ячейке A2 вашего рабочего листа.
Предположим, вы хотите округлить число с помощью VBA. Вы можете сделать это с помощью функции WorksheetFunction.RoundUp.
Шаг 1. Определите новую подпроцедуру в редакторе Visual Basic, которая может хранить ваш макрос.
Код:
Шаг 2: Определите две переменные, одну для хранения числа, которое вы хотите округлить. И другой, чтобы сохранить результат обзора.
Код:
Шаг 3: Сохраните значение в переменную numToRound, которую вы хотите округлить.
Код:
Шаг 4: Теперь используйте RoundUp, чтобы округлить это число и сохранить результат в переменной numRoundUp.
Код:
Код:
Шаг 6: Нажмите F5 или кнопку Run, чтобы запустить этот код и увидеть результат.
Пример № 5 - округление числа с помощью функции VBA RoundDown
Шаг 1: В новой подпроцедуре определите две новые переменные с именами numToRoundDown и roundDownNum. Один для хранения значения числа, которое будет округлено в меньшую сторону, а другие для сохранения результата после округления в меньшую сторону.
Код:
Шаг 2: Присвойте значение, которое вы хотите округлить, до переменной с именем «numToRoundDown».
Код:
Шаг 3: Теперь используйте RoundDown, чтобы округлить это число и сохранить результат в переменной roundDownNum.
Код:
Шаг 4: Используйте функцию MsgBox, чтобы отобразить значение числа, округленного в меньшую сторону.
Код:
Это все в статье. Мы рассмотрели концепции VBA Round, Round Up и Round Down с несколькими примерами.
То, что нужно запомнить
- Эта функция использует метод округления Банкира для округления чисел, которые несколько отличаются от фактического метода округления.
- Эта функция используется для округления числа с плавающей запятой или числа с фиксированными десятичными числами до указанного числа мест.
- Это означает, что функция округляет число вверх или вниз в зависимости от числа после десятичных знаков.
- Аргумент decimal_places должен быть больше или равен нулю.
- Если decimal_places оставить пустым, он будет считаться нулем, а затем число будет округлено до ближайшего целого числа.
- Если значение decimal_places меньше нуля, возникает ошибка времени выполнения 5. «Ошибка времени выполнения« 5 »: неверный вызов процедуры или аргумент».
- На самом деле невозможно предсказать, к чему эта функция будет округлять значение, когда цифра после десятичной дроби равна 5.
- Если один или оба аргумента функции Round не числовые, функция вернет ошибку времени выполнения 13. «Ошибка времени выполнения« 13 »: несоответствие типов».
Рекомендуемые статьи
Это было руководство к VBA Round Function. Здесь мы обсудили, как использовать функцию Excel VBA Round вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Округление – экселевские причуды и несоответствия одной из «причуд» 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 (при форматировании чисел с использованием этих функций в зависимости от выбранного формата также может происходить округление) используется стандартный алгоритм округления:
Следовательно, функция VBA Round (2,5, 0) вернет как результат 2, но уже функция FormatNumber (2,5, 0) вернет как результат 3. И где здесь логика?
Подытожим особенности округления чисел в Excel
Стоит помнить об этих различиях, создавая свои макросы, которые выполняют операции округления чисел. Как вы можете видеть, результат, возвращаемый макросом, может полностью отличаться от ожидаемого.
Как уже выше упоминалось, один алгоритм не является лучше или хуже другого. Каждый хорош в зависимости от применения. Стандартный алгоритм дает результаты, которые мы «ожидаем», в отличие от банковского алгоритма, который, помимо прочего, немного удивляет результатами.
Жаль, что эти два алгоритма были реализованы одновременно, кроме того, довольно хаотичным образом. Возможно было бы более естественным, если бы функция VBA Round() возвращала тот же результат, что и экселевская функция ОКРУГЛ (в конце концов, в английской версии Excelя, она называется также - ROUND).
Предположим, у вас есть диапазон чисел с несколькими десятичными знаками, и теперь вы хотите округлить их до двух десятичных знаков. Такие как округление 2.1054 в 2.11 . Как быстро округлить диапазон ячеек в Excel?
Округление диапазона ячеек с помощью функции ОКРУГЛ
Вы можете использовать формулу КРУГЛЫЙ (число, число-цифры) для выполнения этой задачи. Например, у меня есть диапазон (A2: A10), который нужно округлить. Пожалуйста, сделайте следующее:
1. Щелкните пустую ячейку C2 и введите формулу: = ОКРУГЛ (A2; 2) , см. снимок экрана:
2, Затем нажмите Enter key, а значение в ячейке A2 будет округлено до числа с двумя десятичными знаками. Затем перетащите маркер заполнения над диапазоном ячеек, к которому вы хотите применить эту формулу. Смотрите скриншот:
Работы С Нами Kutools for Excel's Круглые вы можете быстро округлить, округлить в большую, меньшую или меньшую сторону до нужного вам диапазона ячеек без каких-либо формул.
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Округление диапазона ячеек с помощью кода VBA
С помощью следующего кода VBA вы можете быстро округлить диапазон ячеек.
1. Выберите диапазон, который нужно округлить.
2. Нажмите разработчик > Визуальный Бейсик, Новый Microsoft Visual Basic для приложений появится окно, щелкните Вставить > Модуль, и введите следующий код в Модуль:
VBA: округление диапазона ячеек:
3. Затем нажмите кнопку для запуска кода. Выберите диапазон, который вы хотите округлить, из всплывающего журнала набора, см. Снимок экрана:
4. Нажмите OK, а затем введите число десятичной дроби, которое вы хотите округлить в другом диалоговом окне, см. снимок экрана:
5. Нажмите OK. Все ячейки округлены до трех десятичных знаков. Смотрите скриншот:
Быстро округлить / округлить / округлить диапазон ячеек с помощью Kutools for Excel
Освободи Себя Круглые особенность Kutools for Excel это очень удобный инструмент, позволяющий быстро и комфортно решить эту проблему.
Если вы установили Kutools for Excel, выполните следующие действия:
1. Выберите диапазон, который нужно округлить, и нажмите Kutools > Круглые. Смотрите скриншот:
2. В Раунд без формулы диалоговом окне, введите десятичное число, которое вы хотите округлить на основе Десятичное число, А затем выберите Округление, округление в большую сторону, округление в меньшую сторону который вы хотите использовать, а затем щелкните Ok кнопки, все числа округлены по мере необходимости, см. снимок экрана:
Читайте также: