Округление в Excel: лучшие формулы

Подводя итоги в Excel: какую из 12 формул лучше всего использовать?

В этой статье объясняется использование ROUND, ROUND, ROUNDUP, FLOOR, INT, OTR и многих других круглых функций в Excel. Он также предоставляет примеры формул для преобразования десятичных чисел в целые числа или количества десятичных знаков с округлением до ближайших 5, 10 или 100, извлечения дробной части и многое другое.

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

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

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

• Округление с форматированием
• Функции округления
• КРУГЛЫЙ
• ОКРУГЛЯТЬ 
• ОКРУГЛИТЬ 
• КРУГЛЫЙ (КОРИЧНЕВЫЙ) 
• ОКРВНИЗ и ОКРВНИЗ.МАТ
• ОКРВВЕРХ (ПОТОЛК) и ОКРВВЕРХ.МАТ
• ВСЕ (INT) 
• СУНДУК 
• Четный и нечетный  
• Примеры формул
• Как округлить десятичные дроби
• Округлить отрицательные числа
• Как получить десятичную часть числа?
• Округляет десятичную дробь до ближайшего целого числа.
• Как округлить до 5/10/100/1000
• Время округления

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

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

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

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

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

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

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

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

• КРУГЛЫЙ — до указанного количества цифр.
• ROUNDUP — увеличить до определенного количества знаков.
• ОКРУГЛ ВНИЗ — до желаемого количества цифр.
• КРУГЛЫЙ (MROUND) — в любом направлении до указанного кратного.
• FLOOR и FLOOR.MAT — опустить с необходимой точностью.
• ОКРВВЕРХ (ПОТОЛОК) и ОКРВВЕРХ.МАТ — с требуемой точностью с увеличением.
• INTEGER (INT) — до ближайшего целого числа.
• TRUNC — до нужного количества знаков после запятой.
• ЧЕТНЫЙ — с точностью до целого числа.
• ODD (ODD) — до нечетного целого числа.

ОКРУГЛ.

ОКРУГЛ — это базовая функция округления чисел в Excel, которая усекается до указанного количества цифр.

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

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

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

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

Например, = ОКРУГЛ (57,55,1) округляет 57,55 до 57,6.

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

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

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

Например, = ОКРУГЛ (57,55,0) заменяет 57,55 на 58.

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

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

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

На следующем рисунке показано несколько примеров формулы ОКРУГЛ:

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

Перемещается вверх (от 0) на указанное количество цифр.

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

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

ОКРУГЛВНИЗ.

Он работает противоположно ROUNDUP, т.е уменьшает значение.

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

ОКРУГЛТ.

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

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

Например, формула = ОКРУГЛ (17,2) изменяет 17 до ближайшего кратного 2 и возвращает 18 в качестве результата.

Изменение последней оставшейся цифры вверх или вниз зависит от остальной части числового аргумента, деленной на аргумент точности»:

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

Функция ROUNDLT полезна, например, при изменении цен на удобное значение (10 копеек или 50 копеек) для облегчения оплаты наличными.

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

Примечание. Функция ОКРУГЛ возвращает ошибку # ЧИСЛО ! когда его аргументы имеют разные знаки:  

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

ОКРВНИЗ.

Функция FLOOR используется для преобразования в ближайшее кратное указанного значения.

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

Например, = FLOOR (12,5; 2) означает округление 12,5 до ближайшего числа, кратного 2, например, до 12.

Функция ПОЛ выполняет действия, основанные на следующих правилах:

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

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

Начиная с Excel 2013, функция была улучшена и появился FLOOR.MAT. Во многом он лишен недостатков и нестыковок своего предшественника:

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

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

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

= FLOOR.MAT (-17,2,0) возвращает -18.

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

В остальном все как ОКРВВНИЗ.

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

ОКРВВЕРХ.

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

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

Например, формула = OKRVVERH (12,5; 2) преобразует 12,5 в ближайшее кратное 2, то есть 14.

Функция OKRVVERH работает по тем же правилам, что и FLOOR, за исключением того, что обычно округляется от 0.

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

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

ОКРВВЕРХ.МАТ (число; [точность]; [режим])

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

Параметр «режим» определяет направление изменения отрицательного значения: 0 — вверх, 1 — вниз. Вы можете увидеть это на скриншоте.

ЦЕЛОЕ.

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

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

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

ОТБР.

Усекает числовое значение до указанного количества десятичных знаков.

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

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

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

• Если второй параметр положительный, результат обрезается до указанного количества цифр справа от десятичной точки.
• Если отрицательное, оно обрезается до указанного количества цифр слева от десятичной точки.
• Если он равен нулю или отсутствует, функция OPT работает так же, как INTEGER, поскольку оба они возвращают в качестве результата целые числа. Однако OTB удаляет только дробную часть, а INT округляет до ближайшего целого числа.
• Например, = OTIN (-7.4) возвращает -7, а = INT (-7.4) — -8, потому что это ближайшее меньшее целое число. 

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

ЧЁТН и НЕЧЁТ.

Здесь все довольно просто — округляем указанную сумму до ближайшего целого числа в определенном направлении.

НЕЧЕТНОЕ (число) — до ближайшего нечетного целого числа.

ЧЕТНОЕ (число) — до четного целого числа.

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

Функции ЧЕТНОЕ и НЕЧЕТНОЕ могут быть полезны при обработке элементов, которые находятся в парах.

Например:

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

= ODD (-2,4) — получаем -3

= ЧЕТНОЕ (2,4) равно 4

= ЧЕТНОЕ (-2,4) равно -4

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

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

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

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

Функция ROUNDUP — всегда изменяет десятичную дробь в сторону увеличения.

Функция ROUNDDOWN — делает то же самое, но в обратном порядке.

ОКРУГЛ — изменяется вверх, если за цифрой округления следует число, равное или больше 5, иначе — вниз.

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

= ОКРУГЛВВЕРХ (A3; 2) — двузначная формула Excel округляет в большую сторону.

= ОКРУГЛ ВНИЗ (A3; 2) — уменьшается до двух цифр.

= ОКРУГЛ (A3; 2) — изменяется по стандартным правилам, в зависимости от того, больше ли третий десятичный знак 5 или меньше.

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

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