Функции округления чисел в MS Excel

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

Первая ошибка, о которой я только что сказал, это неверное применение формата числа для отображения желаемого количества знаков после запятой. Такая команда, действительно часто используется, чтобы придать данным читабельный вид, и вызывается либо с ленты Главная → Число, либо через правую кнопку Формат ячеек (что равносильно нажатию <Ctrl+1>).

Формат числа для отображения знаков после запятой

Здесь все интуитивно понятно: указываем количество знаков после запятой и наслаждаемся внешним видом. Однако вид никак не влияет на точность числа в ячейке. Поэтому не стоит надеяться на формат, когда нужно реальное округление.

Функция ОКРУГЛ

Для настоящего округления числа по математическим правилам существует функция ОКРУГЛ. Данный шедевр конструкторской мысли округляет число до заданного количества знаков. Функция ОКРУГЛ имеет следующий синтаксис (набор заполняемых параметров):

ОКРУГЛ(число или ссылка на округляемое число; количество оставляемых знаков).

Функция ОКРУГЛ

Здесь трудно ошибиться. Указываем число (или ссылку) и задаем нужную точность. Однако это далеко не все, что предоставляет Excel в плане округления. Как, например быть, если число нужно округлить до тысяч (три последние цифры всегда нули) или миллионов (шесть последних цифр – нули)? Когда я был совсем бестолковым пользователем, то поступал примерно так. Делил округляемое число на 1 000, округлял до целого и затем снова умножил на 1 000. Смешно, правда? Или вы тоже так поступаете? Если да, то читаем далее.

При работе с большими числами иногда нужно произвести округление не десятичной дроби, а разрядов, то есть сделать так, чтобы некоторое количество знаков в конце числа всегда были нулями. Например, число 123 456 нужно округлить до тысяч, т.е. до 123 000. Нет ничего проще. Это делается с помощью той же формулы ОКРУГЛ, только количество округляемых разрядов пишется с минусом. Вот как это выглядит для числа 123 456 789.

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

Так гораздо быстрее, не правда ли? Проще не придумаешь.

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

ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ

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

okrugl-04

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

ОКРУГЛТ

Довольно интересная функция ОКРУГЛТ. С ее помощью можно добиться округления не только до нужного разряда (слева или справа от запятой), но и вообще до нужной точности (кратности). К примеру, нужно рассчитать заказ изделий в штуках, но так, чтобы он был равен целому количеству ящиков. Если в ящике 6 шт, то заказываемое у поставщика количество должно быть кратным шести. Или другой пример. Нужно, чтобы расчетная цена заканчивалась либо на 00, либо на 50, то есть имела кратность 50 рублей. В обоих случаях функция ОКРУГЛТ решает задачу в одно действие.

Округление с заданной кратностью

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

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

Округление с заданной точностью можно также производить принудительно вверх или вниз (как ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ). Для этого есть функции ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ. Такие названия в версии Excel 2013. В предыдущих версиях они немного по-другому назывались, но я не помню, как именно. Функция может быть задействована, например, при расчете заказа, когда никак нельзя заказать меньше требуемой величины, но при этом нужно и кратность сохранить. То есть одна «лишняя» штука требует заказа дополнительно целого ящика. Синтаксис (параметры) тот же, поэтому идем дальше.

Округлять можно и с помощью других функций. Например, ЦЕЛОЕ, которая просто отбрасывает дробную часть и таким образом округляет вниз до ближайшего целого. Используется, к примеру, когда нужно рассчитать целое количество лет между датами (возраст человека). Или еще функции ЧЁТН и НЕЧЁТ, которые округляют числа до ближайшего четного или нечетного значения соответственно. Однако я лично такими функциями округления никогда не пользовался, поэтому затрудняюсь дать дельный совет, куда бы их приткнуть, чтобы извлечь пользу. Заинтересованной общественности придется заглянуть справку Excel, как обычно все и поступают.

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

Вот еще один трюк с округлением чисел.

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

На этом тему округления чисел в Excel будем считать раскрытой.

Всех благ и до новых встреч.

Поделиться в социальных сетях:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •