Как округлить числа в большую и меньшую сторону в Excel

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

Существует несколько методов выполнения этой задачи. Первый — редактирование формы отображения значений ячеек. Второе — использование функции. Разница между этими методами огромна. Тип отображения ячейки необходим в ситуациях, когда необходимо отобразить небольшое количество символов или распечатать таблицу. Тогда достаточно изменить внешний вид ячейки. Это не меняет того, что на самом деле в нем содержится.

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

Как округлить число, задав формат ячейки?

Давайте откроем таблицу и подведем курсор к ячейке А1. Далее запишите туда дробное число 76,575. После этого щелкните правой кнопкой мыши и выберите опцию «Формат ячеек». Появится окно. Его также можно вызвать, нажав Ctrl+1 или на вкладке «Главная» (инструмент «Число»).

В появившемся окне нас интересует формат чисел, в котором мы выбираем необходимое теперь количество десятичных знаков. Давайте представим, что они вообще сейчас мешают. Здесь вы можете установить это значение на 0.

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

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

Как правильно округлить число в Excel

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

Точность результата зависит от того, сколько символов в дробной части человек решил оставить. Чем он больше, тем выше точность. Поэтому настоятельно рекомендуется округлять значения только тогда, когда в этом есть реальная практическая необходимость.. Иногда даже малейшее округление может совершенно исказить расчеты. Это, кстати, одна из самых распространенных причин, почему синоптики так часто ошибаются. Даже эффект бабочки был обнаружен, когда из-за незначительных различий между округленным значением и настоящим прогнозировался сезон дождей.

Как округлить число вверх и вниз?

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

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

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

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

Почему Excel округляет большие числа?

Практически в любом калькуляторе или программе, если ввести слишком большие числа, они округляются до вида Е+ и так далее. Эксель не является исключением. Почему это происходит?

Если число содержит более 11 цифр, то оно автоматически преобразуется в 1,111E+11. Такое представление числа называется экспоненциальным. Сформировать такой способ представления вручную достаточно сложно. Для этого вам необходимо вычислить логарифм числа и выполнить еще несколько операций.

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

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

Как округлить с помощью функции Excel?

А теперь перейдем непосредственно к практике. Что нужно сделать, чтобы округлить числа с помощью функции? Для этого есть специальная функция. КРУГЛЫЙ ДЕРЕВО. Вызвать его можно по-разному: через ленту в версиях Excel 2007 и новее.

Второй способ — написать от руки. Это более продвинутый вариант, поскольку вам нужно хотя бы знать синтаксис.

Самый простой способ для новичка — использовать мастер функций. Для этого необходимо рядом со строкой ввода формулы найти кнопку, на которой написано сочетание маленьких букв fx. Эту функцию вы можете найти в разделе «Математика», и после ее выбора вам будет предложено ввести аргументы. Каждый из них подписан, поэтому разобраться легко.

Синтаксис функции ОКРУГЛ

Если используется ручной ввод, то необходимо понимать, как правильно написать формулу. Порядок ввода значений называется синтаксисом. Любая функция имеет универсальный общий синтаксис. Сначала пишется знак равенства, затем имя функции, затем аргументы, которые пишутся в скобках и разделяются запятой. Количество аргументов может отличаться от функции к функции. В некоторых их вообще нет, а в ряде их хоть 5, хоть больше. 

В случае функции ОКРУГЛ их два. Давайте рассмотрим их более подробно.

Аргументы функции ОКРУГЛ

Итак, функция имеет два аргумента:

1. Число. Это ссылка на ячейку. Альтернативно вы можете ввести желаемое значение в этот аргумент вручную.
2. Количество цифр, до которых вы собираетесь округлить.
   

   

Чтобы округлить целое число (то есть такое, которое не имеет десятичных знаков), просто напишите знак минус перед числом во втором параметре. Для округления до десятков нужно писать -1, до сотен – -2 и следовать этой логике дальше. Чем больше модуль этого числа, тем больше цифр будет округлено. 

Основы функций КРУГЛЫЙ ДЕРЕВО

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

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

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

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

По кратности

Excel дает возможность округлять числа не до ближайшего, а до кратного определенному. Для этого есть специальная функция, которая называется КРУГЛЫЙ. С ее помощью можно добиться необходимой точности округления. 

Есть два основных аргумента. Первый – это непосредственно число, которое необходимо округлить. Второе — это число, которое должно быть кратно заданному. Оба аргумента можно передать вручную или через ячейку. 

По количеству символов

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

Округление в большую сторону в Excel с помощью функции ОКРУГЛВВЕРХ.

Пользователь может самостоятельно задать направление округления. Использование функции КРУГЛВВЕРХ можно убрать лишние цифры или округлить целое число до того, которое окажется больше.

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

Основное отличие этой функции от КРУГЛЫЙ ДЕРЕВО заключается в том, что функция всегда округляет вверх. Если в числе есть какие-либо цифры, округление осуществляется до определенного их количества.

Синтаксис функции ОКРУГЛВВЕРХ

Эта функция принимает два аргумента. В целом функция выглядит так.

=ROUNDLVVERH(76,9)

Теперь давайте более подробно рассмотрим ее аргументы.

Аргументы функции ОКРУГЛВВЕРХ

Синтаксис этой функции, как мы видим, очень прост. Аргументы следующие:

1. Номер. Это любое число, требующее округления.

2. Количество цифр. Здесь вводится количество цифр, которое останется после округления.

Таким образом, по синтаксису эта формула ничем не отличается от КРУГЛЫЙ ДЕРЕВО. Модальность числа определяет, какие числа будут сокращаться. Если второй аргумент положительный, то округление производится справа от десятичной точки. Если он отрицательный, то слева. 

Округление в меньшую сторону в Excel с помощью функции ОКРУГЛИТЬ

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

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

Синтаксис функции ОКРУГЛВНИЗ

Итак, синтаксис абсолютно аналогичен предыдущему примеру. Соответственно, он особо не отличается. Но если есть такое желание, Excel дает возможность самостоятельно использовать эту функцию.

Для начала необходимо перейти к нужному документу, открыть нужный лист и начать вводить знак равенства в строке ввода формулы. После этого необходимо непосредственно указать название формулы ОКРУГЛИТЬ ВНИЗ, затем введите два аргумента.

В целом формула выглядит так.

=РаундСтрока(3,2, 0)

Теперь давайте подробнее рассмотрим, какие аргументы имеет эта функция.

Аргументы функции ОКРУГЛИТЬ

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

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

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