Округление в Excel — функции и примеры формул

Округлить число в Excel можно функцией =ОКРУГЛ(A2;0) — до целого, =ОКРУГЛ(A2;2) — до двух знаков после запятой, =ОКРУГЛ(A2;-2) — до сотен. Всегда в большую сторону округляет =ОКРУГЛВВЕРХ(A2;0), в меньшую — =ОКРУГЛВНИЗ(A2;0), до кратного (например, до 5 или 50 копеек) — ОКРУГЛТ. Кнопки «Уменьшить разрядность» и формат ячейки меняют только отображение, а само значение остаётся прежним. Округлить данные прямо в ячейках, без формул, можно процедурой !SEMTools.

Функция ОКРУГЛ: до целого или до N знаков после запятой

ОКРУГЛ (ROUND) округляет по школьным правилам: цифры от 5 и выше — вверх, меньше 5 — вниз. Второй аргумент — число знаков после запятой: 0 — до целого, 1 — до десятых, 2 — до сотых, отрицательное — до десятков, сотен, тысяч. Сравните с функциями, которые округляют в одну сторону:

Округлить до десятков, сотен и тысяч

Отбросить дробную часть: ЦЕЛОЕ и ОТБР

Для простого округления до целого без необходимости использования второго аргумента (числа разрядов) в Excel доступны две функции — функция ЦЕЛОЕ и функция ОТБР.

Функция ЦЕЛОЕ

Функция ЦЕЛОЕ округляет число до ближайшего меньшего целого — то есть всегда вниз, даже отрицательные числа. Например,

=ЦЕЛОЕ(3,7)

вернет 3, а

=ЦЕЛОЕ(-3,7)

вернет -4.

Функция ОТБР

ОТБР несколько отличается в принципах округления. Во-первых, есть необязательный второй аргумент, позволяющий выбирать, сколько знаков после запятой оставить. По умолчанию он равен нулю, поэтому с положительными числами функция работает так же, как ЦЕЛОЕ.

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

Так, ОТБР(-3,7) оставит -3, а ОТБР(-3,75;1) вернет -3,7.

Округление вверх и вниз: ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ

Для округления в большую и меньшую сторону в Excel предусмотрены функции ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ. Эти функции берут на вход два аргумента — само округляемое число, и количество десятичных знаков, до которого округлить.

Рассмотрим их по порядку.

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

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

=ОКРУГЛВВЕРХ(3,456;2)

=ОКРУГЛВВЕРХ(-3,456;2)

возвращают 3,46 и −3,46 соответственно, т.е. округляют число вверх до сотых (до двух десятичных знаков). Поскольку округление происходит по модулю, ОКРУГЛВВЕРХ фактически округляет отрицательные числа вниз.

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

В то же время ОКРУГЛВНИЗ округляет число в меньшую по модулю сторону, например,

=ОКРУГЛВНИЗ(-3,456;2)

возвращает −3,45, иными словами, округляет вниз по модулю до двух знаков после запятой.

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

Округление в Excel до кратного

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

ОКРУГЛТ

ОКРУГЛТ округляет число до ближайшего кратного указанному значению.

Функция требует два аргумента: округляемое число и кратность, до которой нужно его округлить.

Число и кратность должны быть одного знака: =ОКРУГЛТ(-17;-5) вернёт −15, а =ОКРУГЛТ(-17;5) — ошибку #ЧИСЛО!. Популярный пример — округлить цену до 50 копеек: =ОКРУГЛТ(A2;0,5).

ОКРВВЕРХ и ОКРВНИЗ

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

Обе функции требуют ввода двух обязательных аргументов - делимого и делителя.

При этом, несмотря на описание функции в Excel:

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

=ОКРВВЕРХ(10,1;2)

вернет 12, а

=ОКРВНИЗ(10,9;3,33)

вернет 9,99

ОКРВВЕРХ и ОКРВНИЗ с отрицательными числами

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

Например, ОКРВВЕРХ(-10,1;2) округляет до −10, хотя бо́льшим по модулю было бы −12.

Равно как и ОКРВНИЗ(-10,1;2) округляет до −12, хотя меньшим по модулю было бы −10.

ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ

Единственное отличие ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ от их предшественников — в наличии третьего аргумента, который отвечает за направление округления для отрицательных чисел. На округление положительных он никак не влияет.

Как работает округление с их помощью? Если третий аргумент указать ИСТИНА или любой другой отличный от нуля (функция интерпретирует любое число в логическое значение, и оно станет ИСТИНА) то округление будет производиться по модулю. Если же указать его 0 или ЛОЖЬ — направление округления будет эквивалентно названию. ОКРВВЕРХ.МАТ будет округлять всегда вверх, а ОКРВНИЗ.МАТ — всегда вниз, даже отрицательные числа.

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

Округление дат и времени в Excel

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

Округлить дату до начала месяца

Можно собрать дату начала месяца с помощью функции ДАТА, предварительно взяв из указанной год и месяц, а день прописать вручную единицей:

=ДАТА(ГОД(A1);МЕСЯЦ(A1);1)

До начала следующего месяца

Начало следующего месяца - следующий день после конца этого месяца, который определяется функцией КОНМЕСЯЦА.

=КОНМЕСЯЦА(A1;0)+1

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

=КОНМЕСЯЦА(A1;-1)+1

Округление времени до часа

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

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

=ОКРУГЛТ(A1;"1:00")

=ОКРВНИЗ(A1;"1:00")

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

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

Округлить числа на месте в 2 клика с !SEMTools

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

Они часто пригождались мне в моей работе с числовыми данными. Прежде, чем округлять, мне нужно было создать датасет с рандомными числами — помогла процедура из раздела "Арифметические действия" в надстройке. Я выбрал диапазон от 90 до 100, и далее округлял до сотых, десятых, целых и сотен различными методами:

Почему округление в Excel даёт неожиданный результат

• Сумма не сходится с видимыми числами. Кнопка «Уменьшить разрядность» и формат ячейки только скрывают знаки — в расчётах участвуют полные значения: 0,333 + 0,333 + 0,333 покажут «0,33» трижды, а сумма — «1,00». Округляйте значения функцией ОКРУГЛ.
• Отрицательные числа округляются «не туда». ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ работают по модулю, ЦЕЛОЕ всегда идёт вниз (−3,7 → −4), ОТБР просто отбрасывает дробь (−3,7 → −3). Выберите функцию под своё правило.
• Ошибка #ЧИСЛО! в ОКРУГЛТ. Число и кратность разного знака. Сделайте их одного знака или используйте ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ.
• Ошибка #ЗНАЧ!. Округляется текст, похожий на число: «12.5» с точкой или «1 200» с пробелом. Сначала преобразуйте текст в числа.

Округление в Excel — частые вопросы

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

Функцией =ОКРУГЛ(A2;0) — по правилам математики. Всегда вверх — =ОКРУГЛВВЕРХ(A2;0), всегда вниз — =ОКРУГЛВНИЗ(A2;0).

Как округлить до двух знаков после запятой?

Формулой =ОКРУГЛ(A2;2). Чтобы только показать два знака, не меняя значение, задайте числовой формат с двумя знаками.

Как округлить в большую сторону?

Функцией ОКРУГЛВВЕРХ: =ОКРУГЛВВЕРХ(A2;0) — до целого, =ОКРУГЛВВЕРХ(A2;-2) — до сотен. До ближайшего кратного вверх — =ОКРВВЕРХ(A2;5).

Чем ОКРУГЛТ отличается от ОКРУГЛ?

ОКРУГЛ округляет до заданного числа знаков, ОКРУГЛТ — до ближайшего кратного: =ОКРУГЛТ(A2;5) округлит 17 до 15, а 18 — до 20.

Итоги: какую функцию округления выбрать

Надеюсь, на этой странице вы нашли то, что искали, и смогли округлить свои числа. Коротко о выборе функции:

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