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

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

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

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

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

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

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

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

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

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

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

ОКРУГЛТ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как округлить число форматом ячейки

Впишем в ячейку А1 значение 76,575. Щелкнув правой кнопкой мыши, вызываем меню «Формат ячеек». Сделать то же самое можно через инструмент «Число» на главной странице Книги. Или нажать комбинацию горячих клавиш CTRL+1.

Выбираем числовой формат и устанавливаем количество десятичных знаков – 0.

Результат округления:

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

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

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



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

С помощью функции ОКРУГЛ() (округляет до необходимого пользователю количества десятичных разрядов). Для вызова «Мастера функций» воспользуемся кнопкой fx. Нужная функция находится в категории «Математические».

Аргументы:

1. «Число» - ссылка на ячейку с нужным значением (А1).
2. «Число разрядов» - количество знаков после запятой, до которого будет округляться число (0 – чтобы округлить до целого числа, 1 – будет оставлен один знак после запятой, 2 – два и т.д.).

Теперь округлим целое число (не десятичную дробь). Воспользуемся функцией ОКРУГЛ:

• первый аргумент функции – ссылка на ячейку;
• второй аргумент – со знаком «-» (до десятков – «-1», до сотен – «-2», чтобы округлить число до тысяч – «-3» и т.д.).

Как округлить число в Excel до тысяч?

Пример округления числа до тысяч:

Формула: =ОКРУГЛ(A3;-3).

Округлить можно не только число, но и значение выражения.

Допустим, есть данные по цене и количеству товара. Необходимо найти стоимость с точностью до рубля (округлить до целого числа).

Первый аргумент функции – числовое выражение для нахождения стоимости.

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

Для округления в большую сторону – функция «ОКРУГЛВВЕРХ».

Первый аргумент заполняем по уже знакомому принципу – ссылка на ячейку с данными.

Второй аргумент: «0» - округление десятичной дроби до целой части, «1» - функция округляет, оставляя один знак после запятой, и т.д.

Формула: =ОКРУГЛВВЕРХ(A1;0).

Результат:

Чтобы округлить в меньшую сторону в Excel, применяется функция «ОКРУГЛВНИЗ».

Пример формулы: =ОКРУГЛВНИЗ(A1;1).

Полученный результат:

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

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

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

В программе Excel для округления до целого числа применяется также функция «ОТБР». Она просто отбрасывает знаки после запятой. По сути, округления не происходит. Формула отсекает цифры до назначенного разряда.

Сравните:

Второй аргумент «0» - функция отсекает до целого числа; «1» - до десятой доли; «2» - до сотой доли и т.д.

Специальная функция Excel, которая вернет только целое число, – «ЦЕЛОЕ». Имеет единственный аргумент – «Число». Можно указать числовое значение либо ссылку на ячейку.

Недостаток использования функции «ЦЕЛОЕ» - округляет только в меньшую сторону.

Округлить до целого в Excel можно с помощью функций «ОКРВВЕРХ» и «ОКРВНИЗ». Округление происходит в большую или меньшую сторону до ближайшего целого числа.

Пример использования функций:

Второй аргумент – указание на разряд, до которого должно произойти округление (10 – до десятков, 100 – до сотен и т.д.).

Округление до ближайшего целого четного выполняет функция «ЧЕТН», до ближайшего нечетного – «НЕЧЕТ».

Пример их использования:

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

Если в ячейки табличного процессора вводятся большие числа (например, 78568435923100756), Excel по умолчанию автоматически округляет их вот так: 7,85684E+16 – это особенность формата ячеек «Общий». Чтобы избежать такого отображения больших чисел нужно изменить формат ячейки с данным большим числом на «Числовой» (самый быстрый способ нажать комбинацию горячих клавиш CTRL+SHIFT+1). Тогда значение ячейки будет отображаться так: 78 568 435 923 100 756,00. При желании количество разрядов можно уменьшить: «Главная»-«Число»-«Уменьшить разрядность».

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

Округление дробей

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

Провести операцию округления дробного числа можно, выбрав в верхнем меню вкладку «Формат» и кликнув левой кнопкой мыши на позицию «Ячейка». Это действие вызовет появление меню, необходимого для осуществления операции. Кроме того, перейти в это меню можно и другим способом: кликнув правой кнопкой мыши на выделенном участке таблицы и выбрав пункт «Формат ячейки».

В данном меню следует выбрать числовой формат ячейки и в специальном окне отметить нужное количество знаков после запятой. Так, например, если этот показатель был установлен в виде цифры 2, то первоначальная дробь вида 1,58165874, подвергшаяся округлению, примет вид 1,58.

Округление целых чисел

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

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

В результате функция, применяемая для осуществления этой операции, будет выглядеть следующим образом. Например, существует необходимость округления числа 101, расположенного в ячейке А3, до сотен. В данном случае следует записать функцию так: =ОКРУГЛ(А2;-2). Применение этой функции обеспечит отображение в ячейке А3 указанного числа в виде 100.

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

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

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

1. Выберите ячейку с числами, которые вы хотите округлить.
2. Откройте диалоговое окно «Формат ячеек », нажав Ctrl+1 или щелкните правой кнопкой мыши по ячейке и выберите «Формат ячеек » в контекстном меню.

Как округлить число в Excel - Формат ячеек

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

Как округлить число в Excel - Округлить число, изменением формата ячейки

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

Как округлить число функцией ОКРУГЛ

ОКРУГЛ - это основная функция округления чисел в Excel, которая округляет число до указанного количества знаков.

Синтаксис :

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

Число_разрядов - количество знаков для округления числа. Вы можете указать положительное или отрицательное значение в этом аргументе:

• Если число_разрядов больше 0, число округляется до указанного количества десятичных знаков. Например, =ОКРУГЛ(17,25; 1) округляет число 17,25 до 17,3.

Чтобы округлить число до десятых , укажите в аргументе число_разрядов значение равное 1.

Как округлить число в Excel - Как округлить число до десятых

Если необходимо округлить число до сотых , задайте аргумент число_разрядов равным 2.

Как округлить число в Excel - Как округлить число до сотых

Для того чтобы округлить число до тысячных , введите 3 в число_разрядов.

Как округлить число в Excel - Как округлить число до тысячных

• Если число_разрядов меньше 0, все десятичные разряды удаляются, а число округляется слева от десятичной точки (до десятых, до сотен, до тысяч и т. д.). Например, =ОКРУГЛ(17,25; -1) округляет 17,25 к ближайшему кратному 10 числу и возвращает в результате 20.
• Если число_разрядов равно 0, число округляется до ближайшего целого числа (без десятичных знаков). Например, =ОКРУГЛ(17,25; 0) округляет 17,25 до 17.

На следующем изображении демонстрируются несколько примеров, как округлить число в Excel в формуле ОКРУГЛ:

Как округлить число в Excel - Примеры формул, как округлить число, функцией ОКРУГЛ

Как округлить число в большую сторону функцией ОКРУГЛВВЕРХ

Функция ОКРУГЛВВЕРХ округляет число вверх (от 0) до заданного количества цифр.

Синтаксис :

Число - число для округления.

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

Как округлить число в Excel - Примеры формул, как округлить число в большую сторону, функцией ОКРУГЛВВЕРХ

Как округлить число в меньшую сторону функцией ОКРУГЛВНИЗ

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

Синтаксис :

Число - число, подлежащее округлению.

Число_разрядов - количество знаков, до которых вы хотите округлить число. Работает как аргумент число_разрядов функции ОКРУГЛ, за исключением того, что число всегда округлено в меньшую сторону.

Следующее изображение демонстрирует, как округлить число в Excel в меньшую сторону функцией ОКРУГЛВНИЗ в действии.

Как округлить число в Excel - Примеры формул, как округлить число в меньшую сторону, функцией ОКРУГЛВНИЗ

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