Формула в эксель округление до целого числа. Округление числа в Excel

Округление в Excel, в первую очередь, необходимо для удобного форматирования чисел.

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

Округлить число до определенного количества знаков после запятой

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

В противном случае, конечные расчеты в документе не сойдутся.

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

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

На рисунке указан список основных функций округления и результат их использования:

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

• Откройте ранее используемый документ или создайте новый и наполните его необходимыми данными;
• Во вкладке для работы с формулами откройте выпадающий список с математическими функциями и найдите те, которые предназначены для округления, как показано на рисунке ниже;

• Введите аргументы функции и заполните все поля диалогового окна, как показано на рисунке;

• Полученная функция будет записана в поле для формул ячейки. Чтобы применить ее ко всем остальным ячейкам, скопируйте ее.

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

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

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

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

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

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

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

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

Функции округления чисел в Эксель

Разработчики Excel очень хорошо проработали программу по части округления чисел, поэтому сейчас мы разберем 10 функций, выполняющих различные округления:

1. Округление согласно законов математики можно выполнить функцией ОКРУГЛ(Число; Количество разрядов ) . При этом, если первый незначимый разряд меньше пяти – последний разряд округляется в меньшую сторону. В противном случае – в большую сторону. Укажите второй аргумент отрицательным, чтобы округлить разряды слева от десятичной запятой.

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

1. Округление до ближайшего кратного выполняем функцией ОКРУГЛТ(Число; Кратность )

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

Округлить в заданном направлении позволяют следующие четыре специальных функции:

1. Функция ОКРВВЕРХ(Число; Кратность ) округляет в большую сторону число до значения, кратного второму аргументу

Функция ОКРВВЕРХ Excel

1. ОКРВНИЗ(Число; Кратность ) делает аналогичное преобразование, но в меньшую сторону:

1. ОКРУГЛВВЕРХ(Число; Количество разрядов ) – увеличивает число до ближайшего большего с указанным количеством десятичных разрядов

1. ОКРУГЛВНИЗ(Число; Количество разрядов ) – уменьшает число до ближайшего меньшего с указанным количеством десятичных разрядов

Округление вниз в Эксель

Так же, можно округлять числа, приводя их четным, или нечетным, что реализовано в двух функциях:

1. ЧЁТН(Число ) – округление до ближайшего большего целого четного числа. Такое округление для отрицательного числа – наоборот, получаем ближайшее меньшее четное:

Округление к четному

1. НЕЧЁТ(Число ) – работает аналогично предыдущей функции, но приводит заданное число к нечетному

Округление к нечетному

1. Функция ЦЕЛОЕ(Число ) – округляет до ближайшего меньшего целого:

Округление к целому

1. Чтобы просто удалить десятичный «хвостик» числа – пользуемся функцией ОТБР(Число ) :

Отбрасывание десятичной части

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

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

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

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

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

Округление происходит следующим образом:
-Если число меньше 5, то следующий разряд остается неизменным.
-Если число равно или больше 5, то к следующему разряду добавляется единица.

Например, число 2553,5675643 после округления до сотых будет выглядеть 2553,57
В общем, округление по школьной программе:)

Теперь рассмотрим как работает функция ОКРУГЛ() и какие имеет аргументы.

Синтаксис функции прост - ОКРУГЛ(Число; Число разрядов) .

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

Пример:
Округлим число 2553,5675643
Запись функции следующая:

"=ОКРУГЛ(2553,5675643 ;2 )" вернет результат 2553,57
"=ОКРУГЛ(2553,5675643;0 )" вернет 2554
"=ОКРУГЛ(2553,5675643;-2 )" вернет 2600

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

Функция:
ОКРВВЕРХ() - функция округляет число до ближайшего целого, которое кратно числу, указанного в аргументе "точность ". Округление происходит в большую сторону.

Примеры:
"=ОКРВВЕРХ(2553,57;1 )", результат 2554
"=ОКРВВЕРХ(2553,57;3 )", вернет результат кратный 3 т.е. 2556

Для округления отрицательных чисел, аргумент "точность" должен быть также отрицательным, иначе функция вернет ошибку "#ЧИСЛО! "
"=ОКРВВЕРХ(-2553,57;-2 )", результат -2554

И еще один пример округления, но уже дробного числа
"=ОКРВВЕРХ(0,578;0,02 )" результат 0,58

ОКРВНИЗ() - функция аналогична ОКРВВЕРХ(), с той лишь разницей что округление происходит в меньшую сторону.
"=ОКРВНИЗ(2553,57;2 )" результат 2552

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

Примеры:
"=ОКРУГЛВВЕРХ(2553,34;1 )", результат получится 2553,4
"=ОКРУГЛВВЕРХ(2553,34;-1 )", результат 2560
"=ОКРУГЛВНИЗ(2553,34;1 )", результат 2553,3
"=ОКРУГЛВНИЗ(2553,34;-1 )", результат 2550

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

Изменение количества знаков после запятой без изменения значения

На листе

Во встроенном числовом формате

Округление числа вверх

Округление числа до ближайшего значения

Округление числа до ближайшего дробного значения

Округление числа до указанного количества значимых разрядов

Значимые разряды - это разряды, которые влияют на точность числа.

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

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

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

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

Функция ОКРУГЛ округляет дробные числа следующим образом: если дробная часть больше или равна 0,5, число округляется вверх. Если дробная часть меньше 0,5, число округляется вниз.

Функция ОКРУГЛ округляет целые числа вверх или вниз аналогичным образом, при этом вместо делителя 0,5 используется 5.

В общем при округлении числа без дробной части (целого числа) необходимо вычесть длину числа из нужного количества значимых разрядов. Например, чтобы округлить 2345678 вниз до 3 значимых разрядов, используется функция ОКРУГЛВНИЗ с параметром -4: = ОКРУГЛВНИЗ(2345678,-4) . При этом число округляется до значения 2340000, где часть "234" представляет собой значимые разряды.

Округление числа до заданного кратного

Иногда может потребоваться округлить значение до кратного заданному числу. Например, допустим, что компания поставляет товары в ящиках по 18 единиц. С помощью функции ОКРУГЛТ можно определить, сколько ящиков потребуется для поставки 204 единиц товара. В данном случае ответом является 12, так как число 204 при делении на 18 дает значение 11,333, которое необходимо округлить вверх. В 12-м ящике будет только 6 единиц товара.

Может также потребоваться округлить отрицательное значение до кратного отрицательному или дробное - до кратного дробному. Для этого также можно применять функцию ОКРУГЛТ .

Вопрос: как округлить числа в Excel, интересует многих пользователей. Именно о нем мы и поговорим.

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

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

Округление числа с помощью формата ячеек

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

Округлять будем число 23,5168 в ячейке А1 . Кликаем по нему правой кнопкой мыши и из контекстного меню выбираем «Формат ячеек» .

Дальше, на вкладке «Число» из списка «Числовые форматы» выберите «Числовой» . В поле «Число десятичных знаков» поставьте нужное значение. Нажмите «ОК» .

Количество знаков после запятой уменьшилось до 2-х. Как видите, округление числа происходит по математическим законам: если отбрасываемая цифра меньше пяти – число округляется в меньшую сторону, если больше пяти или равно – в большую.

Значение в ячейке изменилось только визуально. В строке формул по-прежнему стоит число 23,5168, и именно оно будет браться для любых расчетов, в которых будет указана ячейка А1 .

Чтобы уменьшить или увеличить количество знаков после запятой, можно использовать и следующий способ. На вкладке «Главная» в группе «Число» Вы найдете две кнопочки «Увеличить разрядность» и «Уменьшить разрядность» .

Выделим ячейку А1 и нажмем один раз на кнопку «Уменьшить разрядность» . Получим вот такой результат.

Функции для округления в Excel

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

Необходимые для расчетов функции, находятся на вкладке «Формулы» в разделе «Математические» . Рассмотрим, что делает каждая из них.

Выделяем ячейку С1 и вызываем нужную функцию.

ОКРУГЛ – округлит число до заданного количества десятичных разрядов. В поле «Число» – укажите адрес нужной ячейки, в поле «Число разрядов» – укажите количество знаков после запятой. Если написать «0» , округление произойдет до целого числа.

В строке формул будет написано следующее: =ОКРУГЛ(А1;3) . А1 – ссылка на ячейку; 3 – количество десятичных разрядов.

Округлить число в Excel можно и до десятков, сотен, тысяч . Для этого в поле «Число разрядов» нужно написать -1; -2; -3 соответственно.

ОКРУГЛВВЕРХ – округлит число в большую сторону, то есть, округлит значение до ближайшего большего числа. Запись: .

ОКРУГЛВНИЗ – округлит в меньшую сторону, то есть, до ближайшего меньшего числа. Запись: =ОКРУГЛВНИЗ(число;число разрядов) .

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

ЦЕЛОЕ – позволит округлить значение до ближайшего меньшего целого. Запись: =ЦЕЛОЕ(число) .

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

НЕЧЕТ – округлит значение до ближайшего целого нечетного числа. Причем, если значение в ячейке положительное – в большую сторону, отрицательное – в меньшую. Запись: =НЕЧЕТ(число) .

ЧЕТН – округлит значение до ближайшего целого четного числа. Запись: =ЧЕТН(число) .

ОТБР – позволит отбросить дробную часть, оставив целое число. По законам математики 23,5168 должно было округлиться до 24, а у нас число 23 – дробная часть просто отброшена. Запись: =ОТБР(число;число разрядов) .

ОКРУГЛТ – округлит число с заданной точностью. Если в поле «Точность» поставить «7» , число 23,5168 округлится до ближайшего числа, которое кратно семи. Запись: =ОКРУГЛТ(число;точность) .

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