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

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

Округление до целого – функции ЦЕЛОЕ и ОТБР

Для простого округления до целого без необходимости использования второго аргумента (числа разрядов) в 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 до кратного

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

ОКРУГЛТ

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

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

При этом оба аргумента могут быть только неотрицательными (положительными или равными нулю), иначе формула возвращает ошибку #ЧИСЛО!

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

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

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

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

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

=ОКРВВЕРХ(10.1, 2)  

вернет 12, а

=ОКРВНИЗ(10.9, 3.33)

вернет 9.99

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Округление данных на месте с !SEMTools

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

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