Округление до кратных значений с помощью MROUND

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

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

MROUND принимает два аргумента:

• число — значение, которое нужно округлить
• кратность — шаг, до которого нужно округлить

Функция возвращает ближайшее кратное указанному шагу. Например, =MROUND(23, 5) округляет 23 до ближайшего числа, кратного 5, то есть до 25, поскольку 23 ближе к 25, чем к 20.

MROUND округляет до ближайшего кратного значения — вверх или вниз. При равном расстоянии округление выполняется вверх.

Поэтому =MROUND(22, 5) возвращает 20 (это значение ближе к 20), =MROUND(23, 5) возвращает 25, а точная середина =MROUND(22.5, 5) округляется вверх до 25. Результат определяется размером шага, а не количеством десятичных знаков.

В розничной торговле часто требуется округлять цену до ближайших 5 центов. Используйте в качестве кратности значение 0.05.

=MROUND(4.93, 0.05) возвращает 4.95, а =MROUND(4.91, 0.05) возвращает 4.90. Благодаря этому цены укладываются в удобные для кассовых систем пятицентовые интервалы.

В табелях учёта времени часто выполняется округление до ближайших 15 минут. Поскольку время в электронных таблицах хранится как доля дня, 15 минут — это "0:15" или значение 1/96.

Если зафиксированное время находится в A2, формула =MROUND(A2, "0:15") округляет его до ближайшей четверти часа. MROUND распознаёт значение времени и корректно округляет его.

У MROUND есть одно строгое правило: число и кратность должны иметь один и тот же знак. Сочетание положительного числа с отрицательной кратностью приводит к ошибке #NUM!.

Поэтому =MROUND(-23, -5) работает и возвращает -25, а =MROUND(23, -5) завершается ошибкой. Оба аргумента должны быть либо положительными, либо отрицательными.

Чем MROUND отличается от ROUND? ROUND управляет количеством десятичных знаков, а MROUND — размером шага.

С помощью степени десяти MROUND можно использовать вместо ROUND: =MROUND(8.47, 0.01) работает как округление до двух десятичных знаков и возвращает 8.47. Но MROUND особенно полезна, когда шаг не является степенью десяти, например равен 5, 25 или 0.05.

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

• CEILING(number, multiple) округляет вверх до кратного значения
• FLOOR(number, multiple) округляет вниз до кратного значения

Поэтому =CEILING(23, 5) возвращает 25, а =FLOOR(23, 5) возвращает 20 — независимо от того, какое значение ближе.

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

=FLOOR(47, 10) возвращает 40, что означает 4 награды. FLOOR гарантирует, что результат не превысит фактически потраченную сумму, поэтому в данном случае это консервативный и правильный выбор.

Краткое руководство по округлению до кратных значений:

• MROUND — для округления до ближайшего кратного значения (вверх или вниз).
• CEILING — когда всегда нужно округлять вверх до кратного значения, например при расчёте упаковки или вместимости.
• FLOOR — когда всегда нужно округлять вниз, например при подсчёте заработанных полных единиц.

Все три функции принимают одни и те же два аргумента: число и шаг. Выбирайте функцию в зависимости от того, важна ли दिशा округления.

Шаг не обязательно задавать непосредственно числом. Можно сохранить его в ячейке и ссылаться на неё, чтобы изменение одной ячейки повторно округляло все значения.

Если шаг округления находится в B1 (например, равен 25), а значение — в A2, формула =MROUND(A2, $B$1) округляет A2 до ближайшего числа, кратного 25. Знаки доллара фиксируют B1, поэтому формулу можно без проблем скопировать вниз по столбцу.

Проверьте, насколько хорошо Вы поняли MROUND.

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

• MROUND(number, multiple) округляет до ближайшего кратного значения; при равном расстоянии округление выполняется вверх.
• Число и кратность должны иметь один и тот же знак, иначе возникает ошибка #NUM!.
• CEILING всегда округляет вверх до кратного значения, а FLOOR всегда округляет вниз.

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