При задействовании формул в Эксель нередко мы получаем дробные числа и возникает необходимость произвести их округление в ту или иную сторону. В этой статье я постарался собрать всю информацию, которая касается округления чисел в Excel.
В Эксель есть два основных способа округления чисел - округление с помощью числового формата и задействование функций.
Округление числовым форматированием
Первый способ изменяет число лишь визуально и это обязательно стоит учитывать. Например, если в ячейке А1 находится некоторое значение со множеством знаков после запятой, и мы с помощью числового формата оставим лишь два (см.рис.ниже), то это не означает, что значение фактически было округлено. В этом легко убедиться, выбрав ячейку - в строке формул будет отображаться полное значение.
Незнание этого нюанса может привести к ошибке в расчетах и вот простой пример. Есть документ с прайс-листом и в этом же документе ведется журнал заказов.
В какой-то момент произошло изменение цен на некоторый процент.
Новая цена рассчитывается и округляется числовым форматированием до двух знаков после запятой, а затем уже эта цена будет фигурировать в журнале заказов.
В итоге мы получаем неверный результат, ведь сумма заказа должна была быть 2210,90. Вроде бы мелочь, но в большом объеме данных таких ошибок может быть много и их просто можно сразу не обнаружить.
Поэтому при работе со значениями, требующими округления необходимо применять функции.
Функции округления в Excel
Эксель содержит более десятка функций, предназначенных для округления.
Какие-то из этих функций оставлены для совместимости, какие-то условно скрыты (например, ОКРВВЕРХ.ТОЧН), то есть не отображаются в подсказке при вводе функции, и отсутствуют в списке стандартных функций программы, но если ввести функцию полностью, то появится возможность ею воспользоваться.
Но по факту в работе обычно задействуется лишь четыре функции, которые позволяют получить необходимый результат и давайте их рассмотрим.
Функция ОКРУГЛ (ROUND)
Первая и, пожалуй, основная функция округления — это функция ОКРУГЛ (ROUND). С ее помощью можно округлить число до указанного количества разрядов. Например, в озвученном ранее примере нужно было новую цену округлить именно с помощью данной функции.
Нам нужно оставить два знака после запятой, поэтому указываем это количество разрядов во втором аргументе и получаем необходимый результат.
Есть еще две функции, относящиеся, если так можно сказать, к тому же семейству - это функции ОКРУГЛВВЕРХ (ROUNDUP) и ОКРУГЛВНИЗ (ROUNDDOWN). Объединяет эти функции похожий синтаксис - все три функции округляют число до указанного количества десятичных разрядов. Отличаются они лишь способом округления. В случае с функцией ОКРУГЛВВЕРХ округление происходит в большую сторону относительно указанного разряда, что также называется округлением с избытком, а в случае ОКРУГЛВНИЗ, соответственно, в меньшую - округление с недостатком.
Все три функции работают по классической схеме, изложенной в курсе математики шестого класса средней школы. На всякий случай напомню принципы округления. Функция ОКРУГЛ отбрасывает все разряды после указанного во втором ее аргументе и если первая из отброшенных цифр больше или равна пяти, то к последнему оставшемуся разряду добавляется единица, то есть округление производится с избытком. Если же первая из отброшенных цифр меньше пяти, то цифра последнего разряда не изменяется.
Функцию ОКРУГЛВВЕРХ удобно применять в задачах, требующих округлить результат с избытком. Например, есть 600 карандашей, которые нужно разложить по коробкам. В каждую коробку можно поместить 14 карандашей и необходимо вычислить количество коробок, которые понадобятся. Делим общее количество карандашей на количество, умещающихся в коробку, и получаем дробное число.
Соответственно нам нужно округлить его до ближайшего большего целого, чтобы получить необходимое количество коробок.
Функция ОКРУГЛВВЕРХ решает эту задачу и всегда увеличивает на единицу последний оставшийся разряд числа. При решении этой задачи в качестве второго аргумента укажем ноль, ведь нам нужно округлить число до целого.
Обратную задачу решает функция ОКРУГЛВНИЗ, то есть ее применяют в тех случаях, когда нужно получить округление с недостатком.
Для примера используем те же цифры, но в другой задаче. Есть 600 рублей. Карандаш стоит 14 рублей. Необходимо узнать, сколько карандашей можно купить на имеющуюся сумму.
Результат нужно округлить, что называется, с недостатком, ведь карандаши продаются целыми. Применяем функцию ОКРУГЛВНИЗ. Эта функция всегда оставляет последний разряд без изменений. В нашем случае будет откинута дробная часть, так как в качестве второго аргумента снова укажем ноль.
Итак, в этих простых примерах вторым аргументом был ноль и в этом случае число округляется в указанном направлении до ближайшего целого. Но точно также функции работают и с другими разрядами после запятой. К тому же можно использовать отрицательные значения в качестве второго аргумента функций и тогда округление будет происходить в целой части числа до соответствующего разряда. Для наглядности я создал простую табличку, где одно и то же число округляется с помощью функции ОКРУГЛ.
Значение второго аргумента функции вынесено в заголовки столбцов. В нижней строке таблицы представлено начальное (неокругленное) число. Выше представлено число после округления до указанного в заголовке разряда. Данная табличка должна дать исчерпывающее представление о том, какого результата можно добиться, задействовав в качестве второго аргумента функции то или иное значение.
Особенности вычисления Эксель
Необходимость округления чисел в Эксель может возникнуть из-за особенности хранения чисел в самом Excel. Этот момент неочевиден и для многих может стать неожиданностью.
Вот простой пример. В ячейку А1 введем 10,12, а в А2 - 10,11, а затем в ячейке А3 вычтем эти значения. Получим 0,01. Затем в ячейке рядом введем тоже значение и с помощью простой формулы сравним их.
Формула сравнения вернет ЛОЖЬ, что означает, что в ячейках находятся разные значения. Если для ячейки с рассчитанным значением увеличить количество отображаемых знаков с помощью инструментов числового форматирования, то выяснится, что значение не равно 0,01.
Такое поведение Excel обусловлено заложенными в программу принципами. Я не буду подробно на этом останавливаться, а оставлю ссылку на статью, в которой объясняется, как Excel хранит и вычисляет числа с плавающей запятой и почему это может повлиять на результаты некоторых вычислений.
На практике подобные проблемы возникают нечасто, поэтому большинство пользователей даже не задумывается о таких вот особенностях программы.
В данном случае нужно лишь округлить результат с помощью функции ОКРУГЛ, чтобы в дальнейших вычислениях все прошло гладко.
Функция ОКРУГЛТ
Итак, мы разобрали способы округления числа до указанного разряда. В Эксель есть еще группа функции, которые позволяют округлить число с указанной точностью.
Пожалуй, наиболее часто используемая из них, это функция ОКРУГЛТ (MROUND). У функции два аргумента - число и точность, с которой это число необходимо округлить.
Функция округляет число до ближайшего значения, которое будет кратно указанной точности. Чтобы понять логику функции, округлим с ее помощью число 10 с точностью 3. Получим 9.
То есть функция округляет значение до ближайшего числа, кратного трем.
Математически это можно сформулировать так - если разделить 10 на 3, то получим 3,3. Округляем полученное значение до целого, используя обычные правила округления и получаем 3. В итоге 3 умножить на 3 равно 9.
Если изменить число на 11, то на выходе получим 12. Логика та же - 11 делим на 3 и получаем 3,7. Округляя до целого, получаем 4. В итоге 3 умножаем на 4 и получаем 12.
На первый взгляд может показаться, что функция не особо полезна, но вот несколько примеров.
Допустим есть столбец со значениями в финансовом формате. Это могут быть цены, оклады сотрудников, затраты на что-либо или что-то еще.
Суммы указаны с точностью до копейки, а нам нужно их округлить с точностью до 50 копеек. Вот в этом случае как раз и можно воспользоваться функцией ОКРУГЛТ, указав в качестве второго аргумента 0,5 рубля.
Или возникла необходимость округлить значения до 100 рублей. В этом случае просто указываем необходимую точность во втором аргументе функции.
У этой функции, так же, как и у функции ОКРУГЛ, есть аналоги - функции ОКРВВЕРХ и ОКРВНИЗ, которые производят округление с избытком или недостатком. Сейчас эти функции перемещены в категорию функций, оставленных для совместимости со старыми версиями Эксель, а им на замену пришли функции ОКРВВЕРХ.МАТ и ОКРВНИЗ.МАТ. Отличаются они наличием третьего аргумента, который определяет направление округления отрицательных чисел. Логика их работы совпадает с функцией ОКРУГЛТ, просто округление происходит либо с избытком, либо с недостатком, то есть аналогично ранее рассмотренным функциям ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.
Дополнительные функции округления
Также стоит упомянуть еще несколько функций, которые могут быть задействованы в задачах округления.
Функция ЦЕЛОЕ (INT) позволяет округлять дробное число до ближайшего меньшего целого. В случае с положительными числами это будет означать, что функция отбросит дробную часть. Если же речь идет об отрицательных числах, то функция действительно будет округлять значение до ближайшего меньшего.
Если же нужно просто отбросить дробную часть числа, то проще задействовать соответствующую функцию ОТБР (TRUNC), которая ничего не округляет, а удаляет все, что находится после запятой.
А вот функции ЧЁТН (EVEN) и НЕЧЁТ(ISODD) округляют значение до ближайшего целого четного и, соответственно, нечетного числа.
Все эти функции довольно специфические и, сказать по правде, не припомню, когда я ими пользовался на практике. Чаще всего в работе применяются функции ОКРУГЛ и ОКРУГЛТ, реже их вариации, вроде ОКРУГЛВВЕРХ или ОКРУГЛВНИЗ.
Также предлагаю ознакомиться с бесплатным мини-курсом для начинающих.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм