Округление чисел в Excel: 4 рабочих способа

Как числа хранятся в Excel и отображаются на экране

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

Пользователь может настроить способ отображения этого числа на экране. Причем округление происходит по общепринятым нормам: числа от 0 до 4 округляются в меньшую сторону, а от 5 до 9 — в большую.

Настройка отображения чисел через панель инструментов

Проще всего округлить число, а точнее его отображение на экране, через кнопки на панели инструментов. Чтобы их найти, должна быть открыта вкладка «Главная», где находится подраздел «Номер». Здесь находятся необходимые функции «Увеличить разрядность» или «Уменьшить разрядность”.

Выберите ячейку с числом и нажмите кнопку с нужным действием. Функция «увеличить разрядность» добавит нули в конец дроби.

Функция «Уменьшить разрядность» удалит цифры с конца, а окончания округлит.

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

Формат чисел через контекстное меню

Нужную степень округления, отображаемую на экране, можно изменить и другим способом — через контекстное меню ячейки.

1. Выберите нужную ячейку (или диапазон, состоящий из нескольких ячеек) и щелкните по ней правой кнопкой мыши. В контекстном меню выберите пункт «Форматировать ячейки…”.
2. Откроется окно, где в верхнем меню переходим на вкладку «Число» (эта вкладка должна быть установлена ​​по умолчанию). В списке слева выберите формат «Число». Справа находим надпись «Количество знаков после запятой», рядом с ней текстовое поле для выбора количества знаков после запятой. Вы можете ввести его самостоятельно или достичь нужного значения, нажимая стрелку вверх или вниз.
3. Затем нажмите «ОК», после чего изменения вступят в силу.

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

Читайте также: Как найти среднее арифметическое число в Excel

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

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

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

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

Принципы округления в Excel

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

Принципы округления зависят от выбранного метода. Но в большинстве случаев используется стандарт: числа от 5 до 9 округляются в большую сторону, а от 1 до 4 — в меньшую. Так, например, при округлении в Excel до целого числа 1,4 станет 1, а 1,5 станет 2. В то время как специальные функции позволяют выбирать другие варианты округления, на которые влияет как разрядность, так и знак, и даже целое число или дробное число.

Особенности округления чисел Excel

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

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

Округление с помощью кнопок на ленте

Самый простой способ изменить округление — выбрать ячейку или группу ячеек и, находясь на вкладке «Главная», нажать кнопку «Увеличить разрядность» или «Уменьшить разрядность» на ленте. Обе кнопки находятся на панели инструментов «Число». Только отображаемое число будет округлено, но при расчетах будет использоваться до 15 цифр.

При нажатии кнопки «Увеличить разрядность» количество указанных знаков после запятой увеличивается на единицу.

Кнопка «Уменьшить разрядность», соответственно, уменьшает количество цифр после запятой на единицу.

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

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

В открывшемся окне настроек формата ячейки перейдите на вкладку «Число». Если формат данных не числовой, его необходимо указать, иначе не получится настроить округление. В центральной части окна возле надписи «Количество знаков после запятой» просто введите количество знаков, которое вы хотите видеть при округлении. После этого примените изменения.

Установка точности расчетов

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

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

Применение функций

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

Среди основных функций, регулирующих округление, следует выделить следующие:

• «ОКРУГЛ» — округление до указанного количества знаков после запятой по общепринятым правилам округления;
• «ROUNDUP» — округление до ближайшего числа в большую сторону по модулю;
• «ОКРУГЛВНИЗ» — округляет до ближайшего числа по модулю;
• «ОКРУГЛ» — округляет число с заданной точностью;
• «ОКРУГЛВВЕРХ» — округляет число по модулю в большую сторону с заданной точностью;
• «ОКРВНИЗ» — округляет число в меньшую сторону по модулю с заданной точностью;
• «ОТБР» — округляет данные до целого числа;
• «ELV» — округляет данные до ближайшего четного числа;
• ODD — округляет данные до ближайшего нечетного числа.

Для функций ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ используется следующий формат ввода: Имя функции (число;число_цифр). То есть, если, например, вы хотите округлить число 2,56896 до трех цифр, то используйте функцию ОКРУГЛ(2,56896; 3). В результате получается число 2569.

Для функций «ОКРУГЛ», «ОКРУГЛВВЕРХ» и «ОКРУГЛВНИЗ» используется следующая формула округления: Наименование функции (число, точность). Итак, чтобы округлить число 11 до ближайшего кратного 2, мы вводим функцию «Округлить (11; 2)». Выход 12.

Функции НАЙТИ, ЧЕТНЫЕ и НЕЧЕТНЫЕ используют следующий формат: Имя функции (число). Чтобы округлить число 17 до ближайшего четного числа, используйте функцию «ПЗВ (17)». Получаем результат 18.

Функцию можно вводить как в ячейку, так и в функциональную строку, после выбора ячейки, в которой она должна находиться. Перед каждой функцией должен стоять знак «=».

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

1. Перейдите на вкладку «Формулы» и нажмите кнопку «Математика». В открывшемся списке выберите соответствующую функцию, например «ОКРУГЛ».
2. Это открывает окно аргументов функции. В поле «Число» можно ввести число вручную, но если мы хотим автоматически округлить данные для всей таблицы, нажмите кнопку справа от окна ввода данных.
3. Окно аргументов функции свернуто. Теперь щелкните верхнюю ячейку в столбце, данные которого мы собираемся округлить. После того, как значение будет введено в окно, нажмите кнопку справа от этого значения.
4. Снова откроется окно аргументов функции. В поле «Количество разрядов» прописываем разрядность, которая нам нужна для сокращения дробей и применяем изменения.
5. Число округляется в большую сторону. Чтобы таким же образом округлить все остальные данные в нужном столбце, наведите указатель мыши на правый нижний угол ячейки с округленным значением, нажмите левую кнопку мыши и перетащите его вниз в конец таблицы.
6. Теперь все значения в столбце будут округлены.

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

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

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

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

• ОКРУГЛ — до указанного количества цифр.
• ОКРУГЛВВЕРХ — увеличивается до определенного количества символов.
• ОКРУГЛЕНИЕ ВНИЗ — до нужного количества цифр.
• КРУГЛЫЙ (MROUND) — во все стороны до указанного кратного.
• ОКРВНИЗ (ПОЛ) и ОКРВНИЗ.МАТ — с необходимой точностью вниз.
• ОКРВВЕРХ (СПАСИБО) и ОКРВВЕРХ.МАТ — с необходимой точностью с увеличением.
• INT (INT) — до ближайшего целого числа.
• TRUNC — до нужного количества знаков после запятой.
• EVEN (ELV) – до четного числа целых чисел.
• 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(число; точность)

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

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

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

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

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

FLOORMAT(число; точность, режим)

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

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

=RIGHT BOUND.MAT(-17,2,0) дает результат -18.

=ПРАВАЯ ГРАНИЦА.MAT(-17,2,1) возвращает -16.

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

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

ОКРВВЕРХ.

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

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

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

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

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

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

UPPER.MAT(число; точность, режим)

Второй аргумент является необязательным. Если отсутствует, +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 (число) — до четного числа.

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

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

Например:

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

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

=ELV(2,4) равно 4

=СНВ(-2,4) равно -4

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

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

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

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

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

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

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

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

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

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

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

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

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

Когда функция ОКРУГЛВВЕРХ применяется к отрицательным значениям, считается, что они увеличиваются по мере их увеличения, когда значение фактически уменьшается. Например, результатом =ROUNDUP(-0.5, 0) будет -1, как в строке 7 на снимке экрана ниже. Абсолютное значение просто округляется без учета знака. А то минус уже заменяется, если он был.

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

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

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

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

Чтобы извлечь дробную часть десятичного числа, вы можете использовать функцию НАЙТИ, чтобы удалить десятичные разряды, а затем вычесть целое число из исходного числа.

=A3 — ВЫБРАТЬ(A3; 0)

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

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

=АБС(A3-ИНВ(A3;0))

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

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

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

При округлении используется формула ОКРУГЛВВЕРХ с параметром, равным 0. Например, =ОКРУГЛВВЕРХ(5,5, 0) изменяет десятичное число с 5,5 на 6.

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

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

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

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

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

=ЗАВИСТЬ(5.5, 0)

=ЦЕЛОЕ(5,5)

Однако для отрицательных чисел они дают разные результаты — INTEGER преобразует отрицательные десятичные дроби, уменьшая значение, а FLUSH делает результат больше — ближе к 0:

=ВНИЗ(-5.5, 0) возвращает -5.

=ЦЕЛОЕ(-5,5) возвращает -6.

Разумнее использовать –

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

ОТБР

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

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

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

=НЕЧЕТНОЕ(5,5) возвращает 7.

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

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

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

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

• Если вам нужно округлить до 0,5, используйте функцию ИСТИННАЯ ВНИЗ, например:

=ВПРАВО ВНИЗ(A3, 0,5).

• Чтобы изменить до 0,5, используйте функцию ВВЕРХ

=ОКРУГЛВВЕРХ(A3; 0,5).

• При легком округлении до 0,5 используйте функцию ОКРУГЛ. Например = КРУГЛЫЙ (A3, 0,5).

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

Как видите, функцию ОКРУГЛ можно использовать только для положительных значений. При применении к отрицательным числам возвращается ошибка #ЧИСЛО!.

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

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

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

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

• Чтобы округлить до 5:
  =ВПРАВО ВНИЗ($A3; 5)
• До 5:
  =ПРЯМОВВЕРХ($A3; 5)
• В любом случае до 5:
  = КРУГЛЫЙ ($ A3; 5)

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

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

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

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

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

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

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

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

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

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

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

=ОКРУГЛ($A$2, «1:00»)

=ОКРУГЛ($A$2, ВРЕМЯ(1,0,0))

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

•  до часа вперед

=ОКРУГЛВВЕРХ($A$7, «1:00»)

=ВВЕРХ($A$7, ВРЕМЯ(1,0,0))

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

• вернуться к часу

=ВПРАВО ВНИЗ($A$12, «1:00»)

=ВПРАВОВНИЗ($A$12, ВРЕМЯ(1,0,0))

= ОКРУГЛ ($ A $ 12 * 24,0) / 24

В формулах ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ умножьте значение времени на 24 (количество часов в сутках), чтобы преобразовать время в часы. Затем вы используете одну из функций, чтобы округлить десятичное значение до целого числа, а затем разделить на 24, чтобы вернуть вычисленное значение обратно в формат времени.

Если метки времени содержат значения даты, используйте функцию ВСТАВИТЬ. Ведь в Excel дата и время хранятся как обычные числа. Целая часть представляет дни, а дробная часть представляет время. Нам просто нужно извлечь дробь, отбросить дни. Например:

=ОКРУГЛ($A$2, «1:00») — ЦЕЛОЕ ЧИСЛО(A2)

=ОКРУГЛ($A$2, ВРЕМЯ(1,0,0)) — НАЙТИ(A2)

=ОКРУГЛ($A$2*24, 0)/24 — ЦЕЛОЕ ЧИСЛО(A2)

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

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

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

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

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

= КРУГЛЫЙ ($ A3; «00:15»)

= ОКРУГЛ ($ A3; ВРЕМЯ (0; 15; 0))

• Чтобы сделать его большим:

=ВПРАВОВВЕРХ($A3; «00:15»)

=ВВЕРХ($A3, ВРЕМЯ(0,15,0))

• А теперь поменьше:

=ВПРАВО ВНИЗ($A3, «00:15»)

=ВПРАВО ВНИЗ($A3;ВРЕМЯ(0;15;0))

Если вы знаете (или можете вычислить), какая часть суток состоит из заданного количества минут, у вас не возникнет проблем с использованием функций ОКРУГЛЕНИЕ, ОКРУГЛЕНИЕ ВВЕРХ и ОКРУГЛВНИЗ.

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

• до 15 минут: = КРУГЛЫЙ(A3*96;0)/96
• Вверх: =ОКРУГЛВВЕРХ(A3*96;0)/96
• Вниз = ОКРУГЛВНИЗ (A3*96;0)/96

Округление в большую сторону до целого числа

Допустим, у вас есть такая таблица:

И мы должны округлить столбец «Необходим FTE.

Как это сделать?

Вот функция:

=ОКРУГЛВВЕРХ(D2;0)

Эта функция принимает 2 аргумента, первый — фактическое число, которое нужно округлить, второй — кратность.

Так как нам нужно точное целое число, мы написали «0» во втором аргументе».

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

=ПРИЛОЖЕНИЕ(D2;0)

Округлить до числа кратного 0.5

Допустим, у нас такая же табличка, но теперь надо округлить, чтобы число было кратно 0,5.

Как это сделать?

Формула:

= ОРКУП.МАТ(D2;0,5)

Первый аргумент функции — это число, которое необходимо округлить, а второй — кратность.

То же самое можно сделать с помощью функции CIRCLE UP.

Округлить до числа кратного 5

Теперь воспользуемся функцией ОКРУГЛ.

Допустим, у нас есть такая таблица:

Насколько круглый?

Формула:

= КРУГЛЫЙ(B2,5)

В этой формуле рассуждения те же, что и в предыдущих.

Особенность этой функции в том, что она округляет число в ту сторону, которая ближе всего (больше или меньше). В этом случае вы не можете выбирать.

Округлить в большую сторону до числа кратного 5

А теперь давайте рассмотрим, как сделать так, чтобы число округлялось в большую сторону, но при этом оставалось кратным 5.

Мы будем использовать функцию UPPER.

Формула:

=ОКРУГЛВВЕРХ(B2,5)

Округлить в меньшую сторону до числа кратного 5

В этом случае мы будем использовать функцию ПОЛ.МАТ.

Формула:

=ВПРАВО ВНИЗ.МАТ(B2,5)

Округлить в большую сторону до числа кратного 10

Допустим, у нас есть та же таблица:

Но теперь нам нужно округлить до кратного 10.

Вот формула:

= КРУГЛЫЙ (B2; 10)

Хотел вас подловить, конечно, многие заметили, что эта формула округляется в большую сторону. То есть 161 будет округлено до 160.

И если вы все еще хотите округлить, используйте ROUNDUP или ROUNDUP.MATH.

Это будет выглядеть так:

=ОКРУГЛ.МАТЕМАТИКА (B2;10)

или

=ПРАВАЯ ВЕРХНЯЯ ЧАСТЬ(B2;-1)

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

Если вам нужно округлить, формулы будут такими:

=КВАДРАТ(B2;10)=КВАДРАТ(B2;-1)

Если вы хотите «вживую» увидеть разницу между КРУГЛЫМ и КРУГЛЫМ ВВЕРХ/КРУГЛЫМ НИЗОМ, я показал это на картинке ниже:

Обратите внимание, что иногда результаты совпадают, но не всегда.