Как округлить число в excel? быстрое округление чисел

Практическая работа с функциями Excel с примерами и описанием

Примеры использования функций в Excel с описанием в картинках. Практическое применение функций в формулах при работе на офисе.

Округление до целого числа функциями ОКРУГЛ и FormatNumber в VBA.Решение проблем с округлением чисел до целого используя стандартную функцию ОКРУГЛ и VBA-функции FormatNumber или Round. Алгоритм банковского округления до целого числа.

Функция ПРЕДСКАЗ для прогнозирования будущих значений в Excel.Примеры анализов прогнозирование будущих показателей с помощью функции ПРЕДСКАЗ при определенных условиях. Как спрогнозировать объем продаж или спрос на товары в Excel?

Функции и формулы для преобразования номера в название месяца.Примеры функций и формул для преобразования порядковых номеров в название месяцев года и в обратном направлении. Как из названия месяца получить его порядковый номер в году?

Примеры работы функции ДЕЛЬТА для сравнения двух чисел в Excel.Примеры применения функции ДЕЛЬТА для выполнения промежуточных сравнений двух числовых значений в сложных формулах. Калькулятор возврата инвестиций и расчетный анализ вероятности.

Функции LN и LOG для расчета натурального логарифма В EXCEL.Примеры работы функций LN, LOG И LOG10 для математических вычислений при расчете натурального логарифма числа. Инвестиционный и депозитный калькулятор для расчета сложных процентов с капитализацией.

Пример функции БДСУММ для суммирования по условию в базе Excel.Примеры динамической работы функции БДСУММ. Как суммировать значения в базе данных при динамически изменяемых условиях в критериях функции БДСУММ?

Пример функции СУММЕСЛИ для суммирования в Excel по условию .Примеры работы логической функции СУММЕСЛИ при суммировании ячеек только с переделенным значением по условию. Как выборочно суммировать значения в диапазоне ячеек?

Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel.Примеры работы функции СЧЁТЕСЛИ для подсчета количества ячеек со значениями при условии в статистическом анализе или в решении подобного рода задач.

Функция ЧИСТРАБДНИ считает чистые рабочие дни в Excel.Пример работы функций ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД для расчетов отпускных увольняющихся сотрудников и начисления заработной платы по отработанным дням.

Функция РАБДЕНЬ для расчета количества рабочих дней в Excel.Примеры формул для подсчета количества рабочих дней между датами. Как рассчитать разницу в рабочих днях с помощью функций РАБДЕНЬ и РАБДЕНЬ.МЕЖДУ.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Оператор Round

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

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

SubTest_1()

Dima1 AsSingle,a2 AsSingle,a3 AsSingle,a4 AsSingle

a1=Round(1.5,)

a2=Round(2.5,)

a3=Round(3.5,)

a4=Round(4.5,)

MsgBox“Round(1.5, 0)=”&a1&vbNewLine&_

“Round(2.5, 0)=”&a2&vbNewLine&_

“Round(3.5, 0)=”&a3&vbNewLine&_

“Round(4.5, 0)=”&a4

EndSub

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

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

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

Обратите внимание, что в результатах нашего примера все полученные числа – четные.Проверим погрешность:

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

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

Description

The Microsoft Excel ROUND function returns a number rounded to a specified number of digits.

The ROUND function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

Please read our ROUND function (WS) page if you are looking for the worksheet version of the ROUND function as it has a very different syntax.

It is very important to note that the VBA ROUND function behaves a little peculiar and uses something commonly referred to as bankers rounding. So before using this function, please read the following:

The ROUND function utilizes round-to-even logic. If the expression that you are rounding ends with a 5, the ROUND function will round the expression so that the last digit is an even number.

For example:

Round(12.55, 1)
Result: 12.6   (rounds up)

Round(12.65, 1)
Result: 12.6   (rounds down)

Round(12.75, 1)
Result: 12.8   (rounds up)

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

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

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

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

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

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

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

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

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

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

Как округлить до целого числа в Excel?

VBA RoundUp Function

Let’s say you want to round a number up, using VBA. There is no built-in VBA RoundUp equivalent function, instead what you can do is call the Excel RoundUp Worksheet function from your VBA code:

Excel’s worksheet functions are available to use in VBA, through the use of the WorksheetFunction object. The only worksheet functions that you can’t call, are those that already have a built-in VBA equivalent.

A reminder of the syntax of the Excel Worksheet RoundUp Function:

ROUNDUP(Number, Digits) where:

Number – The number that you would like rounded up.
Digits – The number of digits that you would like to round the number.

So, let’s look at an example, so that you can see how to access the RoundUp Worksheet function in your VBA code:

The result is:

You can round up to the nearest whole number by specifying 0 as the number of decimal places:

The result delivered:

RoundUp Function Results

Actual Number	Digits	Result
7.075711	8
7.075711	1	7.1
7.075711	2	7.08
7.075711	3	7.076
7.075711	-1	10
7.075711	-2	100
7.075711	-3	1000

VBA RoundDown Function

Let’s say you want to round a number down, using VBA. There is no built-in VBA RoundDown equivalent function either, instead again, what you would do is call the Excel RoundDown Worksheet function from your VBA code.

A reminder of the syntax of the Excel Worksheet RoundDown Function:

ROUNDDOWN(Number, Digits) where:

• Number – The number that you would like rounded down.
• Digits – The number of digits that you would like to round the number.

So, let’s look at an example, so that you can see how to access the RoundDown Worksheet function in your VBA code:

The result is:

You can round down to the nearest whole number by specifying 0 as the number of decimal places:

The result is:

RoundDown Function Results

Actual Number	Digits	Result
5.225193	5
5.225193	1	5.2
5.225193	2	5.22
5.225193	3	5.225
5.225193	-1
5.225193	-2
5.225193	-3

Other VBA Rounding Functions

VBA Ceiling – RoundUp to A Specified Significance

VBA does not have a Ceiling.Math function equivalent, so if you want to round a number up to the nearest integer or to the nearest specified multiple of significance, then you can call Excel’s Ceiling.Math worksheet function from your VBA code.

A reminder of the syntax of the Excel Worksheet Ceiling.Math Function:

CEILING.MATH(Number, , ) where:

Number – The number that you would like to round up.
Significance (Optional) – The multiple to which you want your number to be rounded to.
Mode (Optional) – Controls whether negative numbers are rounded towards or away from zero.

So, let’s look at an example, so that you can see how to access the Ceiling.Math Worksheet function in your VBA code:

The result is:

VBA Floor – RoundDown to A Specified Significance

VBA does not have a Floor.Math function equivalent either. However, once again, if you want to round a number down to the nearest integer or to the nearest specified multiple of significance, then you can call Excel’s Floor.Math worksheet function from VBA.

A reminder of the syntax of the Excel Worksheet Floor.Math Function:

FLOOR.MATH(Number, , ) where:
• Number – The number that you would like to round down.
• Significance (Optional) – The multiple to which you want your number to be rounded to.
• Mode (Optional) – Controls whether negative numbers are rounded towards or away from zero.

So, let’s look at an example, so that you can see how to access the Floor.Math Worksheet function in your VBA code:

The result is:

Работа с таблицами Excel

Формула Округления ОКРУГЛ() или ROUND() в Excel

Одним из вариантов представления числа до необходимого с заданным количеством знаков разряда является формула Округление ОКРУГЛ() или ROUND() в английском варианте. Она округляет число по стандартным математическим правилам.

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

Возьмем абстрактные числа требующие округления:

Начнем писать в строке функций формулу =ОКРУГЛ( и нажмем кнопку Fx:

В окне аргументы функции в поле число указываем адрес ячейки для округления (B2), в поле число_разрядов необходимое число разрядов до которого необходимо округлить (например, 2):

Предварительно Excel уже показывает результат, нажимаем кнопку ОК и протягиваем формулу до конца столбца (до ячейки C5):

Ничего сложного, быстро и удобно.

Способ 3. Специальные функции

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

ROUND — получить округленное число с указанным количеством десятичных знаков. Поэтому, когда вы вводите ROUND (3,777; 2), где «2» — это количество цифр, вы получаете двузначную формулу округления Excel, и результат будет 3,78.
ROUNDLT — округление числа с заданной точностью. Указав OKRUGLT (3.774; 4), можно получить 4, а OKRUGLT (3.774; 5) — уже 5, что является ближайшим числом с желаемой кратностью.
ROUNDUP — это функция, которая округляет до ближайшего более высокого модуля. Указав в формуле (3,774; 2), пользователь получит 3,78, а (-3,774; 2) — значение -3,78.
ROUNDDOWN — округление до ближайшего номера модуля. В этом случае 3,774 округляется до 3,77, если выбраны 2 десятичных разряда.
ОКРВВЕРХ — округление с требуемой точностью, но не по математическим правилам, а до большего значения. Таким образом, ОКРВВЕРХ (3.774; окажется восьмеркой.
ОКРВНИЗ — аналог предыдущей функции, но числа округлены до меньшего кратного.
ОКРВВЕРХ.МАТ раундов в Excel. Но с изменением синтаксиса формул, где сначала идет аргумент, затем кратность, а в конце — направление округления отрицательного числа. ОКРВВЕРХ.МАТ (-7.24) даст -7, ОКРВВЕРХ.МАТ (-7.24; 3) даст -6 и ОКРВВЕРХ.МАТ (-7.24; 3; 1) — как на скриншоте, — девять.

ОКРВНИЗ.МАТ — аналог предыдущей функции, но с округлением в меньшую сторону.

Чтобы получить формулу округления в Excel до целого числа, используются еще три функции. OPT просто округляет данные до целого числа. ЧЕТНЫЙ округляет до ближайшего четного числа. ODD позволяет округлять данные до нечетных чисел.

Как округлить число в Excel?

Tip to Avoid Bankers Rounding:

If you want to avoid bankers rounding, you can create your own custom function as follows:

' This function overcomes the bankers Rounding that occurs in the
' built-in VBA Round function to provide true (symmetric) numeric Rounding
' Created by TechOnTheNet.com
Public Function StandardRound(pValue As Double, pDecimalPlaces As Integer) As Variant

    Dim LValue As String
    Dim LPos As Integer
    Dim LNumDecimals As Long
    Dim LDecimalSymbol As String
    Dim QValue As Double

    ' Return an error if the decimal places provided is negative
    If pDecimalPlaces < 0 Then
       StandardRound = CVErr(2001)
          Exit Function
    End If

    ' If your country uses a different symbol than the "." to denote a decimal
    ' then change the following LDecimalSymbol variable to that character
    LDecimalSymbol = "."

    ' Determine the number of decimal places in the value provided using
    ' the length of the value and the position of the decimal symbol
    LValue = CStr(pValue)
    LPos = InStr(LValue, LDecimalSymbol)
    LNumDecimals = Len(LValue) - LPos

    ' Round if the value provided has decimals and the number of decimals
    ' is greater than the number of decimal places we are rounding to
    If (LPos > 0) And (LNumDecimals > 0) And (LNumDecimals > pDecimalPlaces) Then

        ' Calculate the factor to add
        QValue = (1 / (10 ^ (LNumDecimals + 1)))

        ' Symmetric rounding is commonly desired so if the value is
        ' negative, make the factor negative
        ' (Remove the following 3 lines if you require "Round Up" rounding)
        If (pValue < 0) Then
            QValue = -QValue
        End If

        ' Add a 1 to the end of the value (For example, if pValue is 12.65
        ' then we will use 12.651 when rounding)
        StandardRound = Round(pValue + QValue, pDecimalPlaces)

    ' Otherwise return the original value
    Else
        StandardRound = pValue
    End If

End Function

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

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

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

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

И это прекрасно работает большую часть времени — за исключением тех случаев, когда числа дают целое число (например, ширина груза 0.5 м, вписывающаяся в 2.5 м rolltrailer). В таких случаях, конечно, добавление одного разрушает результат.

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

2 Ответа

Я не вижу никакой причины избегать WorksheetFunction ; я не вижу здесь никакой путаницы.

Вы также можете свернуть свою собственную функцию:

Называйте это как это:

Если использование объекта WorksheetFunction для доступа к функции ROUNDUP или CEILING не входит в таблицу, то то же самое можно сделать с некоторыми математиками.

A VBA True является эквивалентом (-1) при использовании математически. Раунд VBA существует, чтобы избежать 15-значных ошибок с плавающей запятой.

Разный результат округления в 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!

Округление десятичных чисел

Чтобы «обрезать» десятичное число, используются методы toFixed() или toPrecision(). Они оба принимают один аргумент, который определяет количество значимых и знаков после запятой, которые должны быть включены в результат:

если для toFixed() аргумент не определен, значение по умолчанию равно , то есть без знаков после запятой; максимальное значение аргумента равно 20;
если для toPrecision() аргумент не задан, число не изменяется.

var randNum = 6.25;
randNum.toFixed();
> "6"

Math.PI.toPrecision(1);
> "3"

var randNum = 87.335;
randNum.toFixed(2);
> "87.33"

var randNum = 87.337;
randNum.toPrecision(3);
> "87.3"

Примечание

И toFixed(), и toPrecision возвращают округленное строчное представление результата, а не число. Это означает, что прибавление rounded к randNum в результате даст конкатенацию строк, а не одно число:

console.log(randNum + rounded);
> "6.256"

Если нужно получить в результате JavaScript округление до сотых, используйте parseFloat():

var randNum = 6.25;
var rounded = parseFloat(randNum.toFixed(1));
console.log(rounded);
> 6.3

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

var wholeNum = 1
var dollarsCents = wholeNum.toFixed(2);
console.log(dollarsCents);
> "1.00"

Обратите внимание, что если в числе больше знаков, чем задано параметром точности, toPrecision будет выдавать результат в научном формате:

var num = 123.435
num.toPrecision(2);
> "1.2e+2"

Other VBA Rounding Functions

VBA Ceiling – RoundUp to A Specified Significance

A reminder of the syntax of the Excel Worksheet Ceiling.Math Function:

CEILING.MATH(Number, , ) where:

Number – The number that you would like to round up.
Significance (Optional) – The multiple to which you want your number to be rounded to.
Mode (Optional) – Controls whether negative numbers are rounded towards or away from zero.

So, let’s look at an example, so that you can see how to access the Ceiling.Math Worksheet function in your VBA code:

The result is:

VBA Floor – RoundDown to A Specified Significance

A reminder of the syntax of the Excel Worksheet Floor.Math Function:

So, let’s look at an example, so that you can see how to access the Floor.Math Worksheet function in your VBA code:

The result is:

Оператор Round

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

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

Обратите внимание, что в результатах нашего примера все полученные числа — четные. Проверим погрешность:

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

Практическая работа с функциями Excel с примерами и описанием

Оператор Round

Description

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

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

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

Как округлить до целого числа в Excel?

VBA RoundUp Function

RoundUp Function Results

VBA RoundDown Function

RoundDown Function Results

Other VBA Rounding Functions

VBA Ceiling – RoundUp to A Specified Significance

VBA Floor – RoundDown to A Specified Significance

Работа с таблицами Excel

Формула Округления ОКРУГЛ() или ROUND() в Excel

Способ 3. Специальные функции

Как округлить число в Excel?

Tip to Avoid Bankers Rounding:

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

2 Ответа

Похожие вопросы:

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

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

Округление десятичных чисел

Other VBA Rounding Functions

VBA Ceiling – RoundUp to A Specified Significance

VBA Floor – RoundDown to A Specified Significance

Оператор Round

Похожие записи:

Похожие записи: