Округление в Excel

В статье объясняется использование функций ОКРУГЛ, ОКРУГЛЕТ, ОКРУГЛВЕР, ОКРВНИЗ, ДРУГ, ОТБР и многих других функций округления в Excel. Также есть примеры формул для преобразования десятичных дробей в целые числа или в определенное количество десятичных знаков, с точностью до 5, 10 или 100, извлечения дробей и многое другое.

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

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

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

• Округление путем форматирования
• Функции округления
• РАУНД
• ROUNDUP 
• ROUNDDOWN 
• РАУНД-АП (MROUND) 
• ROUNDUP и mround.
• CEILING и CEILING.MAT.
• INT 
• ROBR (TRUNC) 
• ОВЕН и ОДД  
• Примеры формул
• Округление десятичных дробей
• Округление отрицательных чисел
• Как получить десятичную часть числа?
• Округление десятичного числа до целого числа.
• Как округлить до 5 / 10 / 100 / 1000
• Время округления

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

Округление путем изменения формата ячейки.

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

1. Выберите ячейки, внешний вид которых необходимо изменить.
2. Откройте диалоговое окно Форматирование, нажав Ctrl + 1, или щелкните ячейку правой кнопкой мыши и выберите тот же пункт из контекстного меню.
3. На вкладке Число выберите Числовой формат или Денежный формат и укажите количество знаков после запятой, которое вы хотите видеть. Предварительный просмотр результата появится сразу же в поле Образец.
4. Нажмите OK, чтобы сохранить изменения и закрыть диалоговое окно.

 

 

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

Функции округления чисел.

В отличие от форматирования, которое изменяет только отображение, функции округления изменяют фактическое значение в ячейке.

Ниже приведен список функций, специально предназначенных для выполнения различных типов округления в Excel.

• ROUND - до заданного количества цифр.
• ROUNDUP - увеличение до заданного количества цифр.
• ROUNDDOWN - до заданного количества цифр.
• MROUND - любая сторона до заданного кратного числа.
• FLOOR и ROUNDDOWN - до необходимого количества цифр.
• CEILING и CEILING.MAT - до желаемой точности.
• Целое число (INT) - с точностью до целого числа.
• TRUNC - до желаемого количества десятичных знаков.
• EVEN в четное целое число.
• ODD в нечетное целое число.

ОКРУГЛ.

ОБЪЕМ - это базовая функция округления чисел в Excel, которая выполняет усечение до заданного количества цифр.

Синтаксис: RUNNING(number; number_of_digits)

Число может быть числом, ссылкой на ячейку с числом или значением, вычисленным с помощью формулы.

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

• Если оно больше 0, то округляется до указанного количества знаков после запятой.

Например, =Circle(57.55; 1) округляет 57.55 до 57.6.

• Если меньше 0, все десятичные знаки удаляются, и число округляется влево от десятичного знака (до десятков, сотен, тысяч и т.д.).

Например, =OKRUL(57.55; -1) округляет 57.55 до ближайшего кратного 10 и возвращает результат 60.

• Если он равен 0, результат округляется до целого числа (без десятичных знаков).

Например, =CROLL(57.55; 0) заменяет 57.55 на 58.

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

Цифра округления - это последняя значащая цифра, которая сохраняется в результате. Она изменяется в зависимости от того, больше или меньше 5 следующая за ней цифра:

• Если цифра справа от цифры округления равна 0, 1, 2, 3 или 4, то цифра не меняется и значение считается округленным в меньшую сторону.
• Если за цифрой следует цифра 5, 6, 7, 8 или 9, то цифра увеличивается на единицу, а результат округляется в большую сторону.

На рисунке ниже показаны некоторые примеры формулы OKRUGGLE:

 

 

ОКРУГЛВВЕРХ.

Он округляет в большую сторону (от 0) до указанного количества цифр.

Синтаксис : OKRULVERT(число; число_цифра)

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

 

 

ОКРУГЛВНИЗ.

Действует противоположно тому, что OKRUGL делает UP, т.е. уменьшает значение.

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

 

 

ОКРУГЛТ.

Округляет заданное значение до указанного множителя.

Синтаксис: OKRUGLT(число; точность).

Например, =OKRUGLT(17; 2) изменяет 17 на ближайшее кратное 2 и возвращает 18 в качестве результата.

Изменится ли последняя оставшаяся цифра в большую или меньшую сторону, зависит от значения остатка аргумента числа, деленного на аргумент 'precision':

• Если остаток больше или равен половине значения аргумента, OKRUGLT изменяет последнюю цифру по нарастающей.
• Если остаток меньше половины аргумента, последняя цифра уменьшается.

Функция ОКРУГЛТ полезна, например, при изменении цен на удобное значение (10 копеек или 50 копеек) для облегчения наличных расчетов.

 

 

Он также очень полезен, когда нужно округлить время до нужного интервала. Например, чтобы округлить время до 5 или 10 минут, просто укажите в аргументе "0:05" или "0:10":

 

 

Примечание. ФункцияOKRUGLT возвращает ошибку #NUMBER!, если ее аргументы содержат разные символы:  

=OCRUGLT(15,51; -0,1) и =OCRUGLT(-15,51; 0,1).

ОКРВНИЗ.

Функция ОКРВНИЗ используется для преобразования в сторону уменьшения до ближайшего кратного заданного значения.

Синтаксис: OKVNIZ(число; точность)

Например, = OKVNIZ(12.5; 2) округляет 12.5 до ближайшего кратного 2, т.е. до 12.

Функция ОКРВНИЗ делает это на основе следующих правил:

• Когда число и точность положительны, число уменьшается, как в строках 2 и 6 на скриншоте ниже.
• Если исходное число больше нуля, а точность меньше нуля, выражение возвращает ошибку #NUMBER!, как в строке 3 на скриншоте ниже.
• Если число отрицательное и аргумент больше нуля, оно округляется в меньшую сторону, как в строке 4.
• Если исходное значение и аргумент отрицательны, они округляются в сторону нуля, как в строке 5.
• Если значение точно кратно аргументу, никаких действий не предпринимается.

 

 

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

Начиная с Excel 2013, функция была улучшена и появился OKVNIZ.MAT. Он в значительной степени устраняет недостатки и нелогичности своего предшественника:

OKVNIZ.MAT(число; [точность]; [режим]).

Если точность не указана, по умолчанию используется значение +1 для положительных чисел и -1 для отрицательных.

Третий параметр указывает, в каком направлении преобразуются отрицательные значения. Ноль - вниз, один - вверх.

=OQRVNIZ.MAT(-17;2;0) дает результат -18.

=OQRVNIZ.MAT(-17;2;1) возвращает -16.

В остальных случаях все аналогично ОКРВНИЗ.

 

 

Еще одной приятной особенностью является то, что не возникает ошибки, если число и точность имеют разные символы.

ОКРВВЕРХ.

Округляет до ближайшего кратного. Она имеет тот же синтаксис, что и функция OKRVNIZ.

Синтаксис : ОКРВЕРС(число; точность).

Например, = OKWERX(12.5; 2) преобразует 12.5 в ближайшее кратное 2, которое равно 14.

OKRVERX работает по тому же принципу, что и OKRVNYZ, за исключением того, что обычно округляется в большую сторону от 0.

• Если число и точность положительны, результат увеличивается, как в строках 2 и 6 на скриншоте ниже.
• Если округленная сумма положительна, а кратное число отрицательно, OKRVERSE возвращает ошибку, как в строке 3.
• Когда число отрицательное, а точность положительная, действие направлено вверх, как в строке 4.
• Если оба значения отрицательны, результат преобразуется в ближайшее большее абсолютное значение (т.е. меньшее), как в строке 5.

 

 

Учитывая некоторую нелогичность этой функции, в Excel 2013 Microsoft ввела функцию OKROT.MAT. Его синтаксис таков:

OKRVRVrS.MAT(число; [точность]; [режим]).

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

Параметр 'mode' задает направление изменения отрицательного числа: 0 - вверх, 1 - вниз. Это можно увидеть на скриншоте.

 

 

ЦЕЛОЕ.

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

Синтаксис : Integer(число)

Положительные числа округляются до 0, а отрицательные - до 0. То есть, операция выполняется без модуля. Например, =REAL(5,5) возвращает 6, а =REAL(-5,5) возвращает -6.

 

 

ОТБР.

Обрезает числовое значение до указанного количества знаков после запятой.

Синтаксис: OTBR(число; [число_цифр])

Num_digits - необязательный аргумент, задающий точность усечения. То есть, сколько знаков после запятой должно быть оставлено в результате. Если опущено, число будет усечено до целого числа.

Функция OTBR подчиняется следующим правилам:

• Если второй параметр положительный, результат усекается до указанного количества цифр справа от десятичной точки.
• Если оно отрицательное, оно усекается до указанного количества цифр слева от десятичной точки.
• Если он равен нулю или вообще отсутствует, функция OTBR работает аналогично Integer в том смысле, что обе возвращают целые числа. Однако OTBR просто удаляет дробную часть, а VERIFY округляет до целых чисел.
• Например, =OTBR(-7,4) возвращает -7, а =TITLE(-7,4) возвращает -8, потому что это ближайшее целое число. 

Вот как это выглядит:

 

 

ЧЁТН и НЕЧЁТ.

Здесь все просто - округлите указанную сумму до целого числа в указанном направлении.

Нечетное (число) - до ближайшего нечетного целого числа.

YEHN(число) - до четного целого числа.

• Если аргумент не является числом, функции возвращают ошибку #NUM!
• Если число отрицательное, оно изменяется от нуля вниз.

Функции ИЕХН и НЕ могут быть полезны при обработке элементов, которые встречаются парами.

Например:

=NOTH(2,4) возвращает 3

=NOTH(-2,4) - возвращает -3

=YOTH(2,4) возвращает 4

=YTN(-2,4) равно -4

 

 

Использование формул округления в Excel.

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

Как округлить десятичные дроби до нужного количества разрядов.

В зависимости от ситуации десятичные числа могут округляться в ту или иную сторону или на основе математических правил округления:

Функция RUNNING - всегда округляет десятичные числа в большую сторону.

OKCROLLBACK - делает то же самое, но в обратном направлении.

OKCROLL up изменяет вверх, если за цифрой округления следует цифра, равная или больше 5, в противном случае изменяет вниз.

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

=OCROLLWORTH(A3; 2) - формула Excel для округления до двух десятичных знаков.

=OCRUALVNYZ(A3; 2) - уменьшение, до двух цифр.

=OCRUAL(A3; 2) - округление по стандартным правилам в зависимости от того, больше или меньше 5 третий десятичный знак.

 

 

Округление отрицательных чисел.

Когда дело доходит до работы с отрицательными числами, результаты, возвращаемые функциями округления Excel, о которых мы только что говорили, могут показаться довольно нелогичными :)

Когда функция Roundup применяется к отрицательным значениям, они округляются в большую сторону, хотя на самом деле они округляются в меньшую сторону. Например, результат =OCROLLVERX(-0.5; 0) будет равен -1, как в строке 7 на скриншоте ниже. Он просто округляет абсолютное значение, игнорируя знак. А затем заменяет минус, если он был.

Известно, что функция округления уменьшает, хотя отрицательные числа могут увеличивать свое значение. Например, формула =OCROLLVNYZ(-0.5; 0) возвращает 0, как в строке 8 ниже. Причина та же: знак минус отбрасывается, число округляется в меньшую сторону, а затем минус добавляется снова. В результате результат уменьшается по абсолютной величине, но, имея перед собой знак минус, он увеличивается по законам математики.

Таким образом, логика округления отрицательных чисел очень проста. Когда вы используете функцию округления или аналогичную функцию для округления отрицательного числа, оно сначала округляется до абсолютного значения (без знака минус), затем выполняется операция округления, и только после этого к результату добавляется знак минус.

 

 

При работе с отрицательными значениями рекомендую обратить внимание на функции ОКРВПЕРТ.МАТ и ОКРВНИЗ.МАТ, описанные выше. Эта не столь очевидная логика закреплена там.

Как извлечь десятичную часть числа?

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

=A3 - SUBTRACT(A3; 0)

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

Однако если вам нужно абсолютное значение (десятичная часть без знака минус), оберните формулу в функцию ABS:

=ABS(A3-ABS(A3;0))

 

 

Как округлить десятичную дробь до целого числа.

Как и в случае с десятичными числами, существует несколько функций для округления дробного числа до целого.

ОКРУГЛВВЕРХ.

При округлении до большего целого числа используйте формулуCircleVerse с параметром, установленным на 0. Например, =CROLLWORTH(5.5; 0) изменяет десятичную дробь с 5.5 на 6.

Но для отрицательных чисел использование =OCROLLWORTH(-5,5; 0) и получение -6 кажется мне не совсем корректным.

При округлении отрицательных чисел, чтобы избежать путаницы, используйте

=OCROWTHERX.MAT(-5,5; -1; 0), что дает -5. Это математически правильно.

ЦЕЛОЕ или ОКРУГЛВНИЗ.

 При округлении до целого положительного числа с убывающим значением используйте функцию Integer или RUNNING также с параметром 0. Например, обе следующие формулы преобразуют 5,5 в 5:

=CIRCLEDOWN(5,5; 0)

=EURODRIVE(5,5)

Однако в случае отрицательных чисел они дают разные результаты - CIRCEDOWN преобразует отрицательные десятичные значения, уменьшая значение, а OKDROWN делает результат больше, в сторону 0:

=OCROWNIZ(-5,5; 0) возвращает -5.

=EUREAU(-5,5) возвращает -6.

Логичнее использовать -.

=OCRVNIZ.MAT(-5,5;-1;0) - дает -6, что соответствует правилам математики.

ОТБР

Чтобы удалить дробную часть без изменения целой части, используйте формулу OTBR с опущенным вторым аргументом. Например, =OTBR(5,5) отсекает десятичную часть (,5) и оставляет целую часть (5).

НЕЧЁТ или ЧЁТН

Чтобы изменить десятичную дробь на нечетное число, используйте функцию ДРУГОЕ:

=NOTH(5,5) возвращает 7.

Чтобы изменить десятичное число на четное, используйте функцию READ:

=CHEAT(5,5) возвращает 6.

 

 

Округлить до 0,5

Microsoft Excel предоставляет 3 функции, позволяющие округлять числа до ближайшего значения, кратного 0,5. Вы можете использовать любую из них.

• Если вы хотите округлить до 0,5, используйте функцию округления, например: 

=ROUND(A3; 0.5).

• Если вы хотите округлить до 0,5, используйте функцию OKRIGHT

=ROUND UP(A3; 0,5).

• Для округления до 0,5 используйте функцию OKRIGHT. Например, =OCROLLT(A3; 0.5). 

Как изменится сумма, зависит от остатка от деления на второй аргумент (0,5). Если остаток равен половине 0,5 или больше, он округляется в большую сторону, в противном случае - в меньшую.

 

 

Как видите, функцию OKRUGLT можно применять только к положительным значениям. Применение его к отрицательным числам возвращает ошибку #NUMBER!

Округлить до 5/10/100/1000

Округление до пяти, десяти, сотен или тысяч производится так же, как и округление до 0,5, рассмотренное в предыдущем примере.

Округлить до 5.

Вы можете использовать одну из следующих формул:

• Округлять до 5:
  =ROUND($A3, 5)
• Округлить до 5:
  =ROUND($A3, 5)
• С обеих сторон до 5:
  = ОКРУГЛЕНИЕ($A3, 5)

 

 

Округлить до 10.

Если вы хотите округлить сумму до десятых, укажите 10 во втором аргументе:

 

 

Округлить до 100.

Округление до ста выполняется аналогичным образом, если во втором аргументе указать 100:

 

 

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

Вы можете использовать те же методы для округления чисел до других кратных значений. Например, вы можете округлить цены до 50 копеек (кратно 0,5) или время до одного дня. Говоря о времени, знаете ли вы, как перевести его в час, 5 или 10 минут? Если нет, то ответы вы найдете в следующей части :)

Округление времени.

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

Пример 1. Как округлить время до часа.

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

• до часа (вверх или вниз) -

=ROUNDT($A$2; "1:00")

=ROUNDT($A$2; TIME(1;0;0))

=ОКРУГЛ($A$2*24;0)/24

•  вперед к часу -

=OKRULT($A$7; "1:00")

=ROUND-UP($A$7; TIME(1;0;0))

=ОКРУГЛВВЕРХ($A$7*24;0)/24

• вернуться на один час

=ROUNDDOWN($A$12; "1:00")

=ROUNDDOWN($A$12; TIME(1;0;0))

=ОКРУГЛВНИЗ($A$12*24;0)/24

 

 

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

Если ваши метки времени содержат значения даты, используйте функции WHOLE и RETURN. В конце концов, в Excel дата и время хранятся в виде простых чисел. Целая часть обозначает дни, а дробная часть - время. Нам нужно извлечь только дробную часть, отбросив дни. Например:

=ROUNDT($A$2, "1:00") - INTEGER(A2)

=ROUNDT($A$2, TIME(1,0,0)) - SCRAP(A2)

=ROUNDT($A$2*24;0)/24 - INTEGER(A2)

Другие формулы показаны на скриншоте:

 

 

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

Пример 2. Округление времени до ближайших 5, 10, 15 минут.

Если вы хотите округлить время в своей таблице Excel до пяти, десяти минут или четверти часа, вы можете использовать те же методы, которые приведены выше. Но замените в формулах "1 час" на нужное количество минут.

• Если округлить время в A3 до ближайших 15 минут:

=ROUNDT($A3; "00:15")

=ROUNDT($A3; TIME(0;15;0))

• Чтобы сделать это вверх:

=OKRGULT($A3; "00:15")

=OKRGULT($A3; TIME(0;15;0))

• Теперь она отключена:

=REVERSE($A3; "00:15")

=REVERSE($A3; TIME(0;15;0))

 

 

Если вы знаете (или можете рассчитать), какая часть дня состоит из определенного количества минут, то у вас не будет проблем с использованием OKRUGL, OKRUGLUP и OKRUGLWIND.

Например, если вы знаете, что 15 минут - это 1/96 часть суток, вы можете использовать одну из следующих формул для округления времени до ближайшей четверти часа.

• до 15 минут: =CROW(A3*96;0)/96
• В более высокую сторону: =OCROLL(A3*96;0)/96
• Вниз: =OKRUGGLE(A3*96;0)/96

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