Округление в Excel какую из 12 формул лучше использовать?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ОКРУГЛ.

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

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

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

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

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

Например, =ROUND(57,55, 1) округляет 57,55 до 57,6.

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

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

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

Например, =ROUND(57,55, 0) заменяет 57,55 на 58.

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

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

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

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

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

Сдвиг вверх (от 0) до указанного количества цифр.

Синтаксис: ОКРУГЛВВЕРХ(число, число_цифр)

Второй аргумент работает так же, как ОКРУГЛ, но результат всегда рассчитывается в сторону увеличения.

ОКРУГЛВНИЗ.

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

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

ОКРУГЛТ.

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

Синтаксис: ROUND(число, точность)

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

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

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

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

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

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

=ОКРУГЛ(15,51, -0,1) и =ОКРУГЛ(-15,51, 0,1).

ОКРВНИЗ.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ОКРВВЕРХ.

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

Синтаксис: ТЕКУЩИЙ(число, точность)

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

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

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

Из-за отсутствия логики этой функции в Excel 2013 Microsoft ввела функцию КРУПНЫЙ ПЛАН.МАТИК. Его синтаксис:

TOPMAT(число, [точность], [режим])

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

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

ЦЕЛОЕ.

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

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

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

ОТБР.

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

Синтаксис: НАЙТИ(число, [число_цифр])

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

Функция НАЙТИ придерживается следующих правил:

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

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

ЧЁТН и НЕЧЁТ.

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

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

EVEN (число) — до четного целого числа.

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

Функции EVEN и ODD могут быть полезны при обработке парных элементов.

Например:

=НЕЧЕТНОЕ(2,4) возвращает 3

= НЕЧЕТНЫЙ (-2,4) — получаем -3

=ЧЕТНЫЙ(2,4) равен 4

=PAR(-2.4) равно -4

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

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

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

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

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

Функция ОКРУГЛВНИЗ делает то же самое, но в обратном порядке.

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

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

=ОКРУГЛ(A3, 2) – формула округления Excel до двух знаков после запятой.

=ОКРУГЛ ВНИЗ(A3, 2) – уменьшить до двух знаков после запятой.

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

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

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