В посте , я указал на огромную важность моей будущей статьи об округлении чисел. Теперь пришло время объяснить почему. Дело в том, что многие путают округление числа и форматирование числа в Excel. В первом случае число реально изменяется в соответствии с выбранным правилом округления. Во втором – число остается прежним, изменяется лишь его отображение на листе.
Непонимание этих азов может привести к получению ошибочных или неточных результатов, поэтому крайне важно изучить как , так и округление.
Разработчики Excel очень хорошо проработали программу по части округления чисел, поэтому сейчас мы разберем 10 функций, выполняющих различные округления:
Округлить в заданном направлении позволяют следующие четыре специальных функции:
Так же, можно округлять числа, приводя их четным, или нечетным, что реализовано в двух функциях:
Конечно, это не все функции округления, которые представлены в Microsoft Excel. Но эти – самые распространенные и универсальные. В моей практике хватает и десяти штук для качественной работы с числами, нуждающимися в округлении. Вам тоже вряд ли придется пользоваться другими функциями, но описанные здесь – желательно знать на зубок!
Это все о формулах округления, с радостью отвечу на Ваши вопросы в комментариях.
Кстати, в следующих постах мы будем изучать . Заходите и читайте, этот функционал редко используют обыватели, хотя поле его применения очень широко, а возможности позволяют значительно сократить времязатраты. Возможно, Вы осознаете, что некоторые задачи, которые считали невозможыми, все же решаются в Эксель, при чем легко!
При работе с таблицами часто возникает необходимость округлить число в Excel, для этого предусмотрен ряд имеющихся математических функций. Но необходимо понимать разницу между округлением и форматированием значения ячейки. Рассмотрим все нюансы поподробнее…
Любое введенное в ячейку числовое значение отображается в формате «Общий» (Главное меню или формат ячеек). В случае форматирования числа, оно отображает определённое количество знаков после запятой, которые можно настроить (, формат ячейки). Т.е. вы можете задать любое количество знаков после запятой при помощи форматирования (при этом само число в ячейке не изменится — изменится отображение).
Когда данные в ячейках используются формулами, программа работает с его фактическим значением, которое может отличаться от того что мы видим на мониторе (например как в ячейки B1 первой картинке). Округление числа происходит с помощью функций (формул) ОКРУГЛ(), ОКРУГЛВВЕРХ(), ОКРУГЛВНИЗ().
Интересная функция =ОКРУГЛТ (128;6), чтобы округлить число «127» к кратному «6» в строке формул необходимо прописать: =ОКРУГЛТ (128;6), в итоговой ячейке мы получим число «126».
Очень часто рассчитывая денежные значения в Excel, в которых используется дополнительные расчеты, получаем числа с большим количеством знаков после запятой. Денежные форматы предусматривают только два знака после запятой, поэтому значение необходимо привести в надлежащий вид, округлить число в Excel.
Для этого если ячейка В1 содержит числовой показатель 10,561 р. (этот формат можно задать нажав иконку с деньгами на второй картинке), чтобы привести значение к нужному (2 знака после запятой) достаточно в строке формул прописать: =ОКРУГЛ (В1;2), получим результат 10,56 р.
Бывают случаи, что значение необходимо округлить в большую или меньшую сторону, для этого используются формулы:
1. Округление в большую сторону, т.е. вверх: =ОКРВВЕРХ(В1;0,01), ячейка В1 получит значение 10,57 р., с округлением до следующей копейки (0,01) в большую сторону
2. Округление в меньшую сторону, вниз: =ОКРВНИЗ(В1;0,01), ячейка получит значение 10,56 р., с округлением до следующей копейки в меньшую сторону
3. А если к примеру округлить показатель до 10 копеек используйте формулу: =ОКРВВЕРХ(В2;0,10)
Для того, чтобы получить целое число в Excel , используют формулы =ЦЕЛОЕ() и =ОТБР() . На первый взгляд они могут показаться похожими, но это не так, особенно это хорошо видно на отрицательных числах. При использовании формулы с функцией ОТБР , удаляется только дробная часть числа.
Например, имеем число- 16,3543,формула: =ОТБР (-16,3543) преобразовывает значение в число -16, а формула: =ЦЕЛОЕ(-16,3543) выдаёт показатель -17, потому что, целое следующее число, идущее за «-16,3543» — это именно «-17».
Иногда функцию ОТБР используют, для усечения десятичных знаков формула:=ОТБР (16,3555555;2) даёт показатель «16,35».
Бывает так, что крупные цифровые значения необходимо округлить до определённого количества некоторых значимых цифр вверх или вниз. Для этого используем формулы с функцией ОКРВВЕРХ и ОКРВНИЗ. Например имеем число 164 358 находящееся в ячейки В1, формула: =ОКРУГЛВВЕРХ (В2;3-ДЛСТР(B1)), преобразует его в показатель «165000», Тройка в данной формуле это как раз значение, которое отвечает за количество знаков в преобразовании. Если мы заменим например её на «2» и напишем формулу =ОКРУГЛВНИЗ (В2;2-ДЛСТР(A1)), получим значение «160000».
Необходимо отметить, что все эти формулы работают только с положительными числами.
Очень часто в учетных программах, таких как 1С используется банковское округление, что говорит Википедия : Банковское округление (англ. banker’s rounding ) или бухгалтерское округление - округление здесь происходит к ближайшему четному (если число заканчивается на 5), то есть 2,5 → 2, 3,5 → 4. Для этого можно воспользоваться следующими функциями:
Функция =ЧЕТН() округляет до ближайшего четного целого. При этом положительные числа округляются в сторону увеличения, а отрицательные – к уменьшению
Функция =НЕЧЁТ() округляет число до ближайшего нечетного целого. Положительные числа округляются в сторону увеличения, а отрицательные – к уменьшению
Поделитесь нашей статьей в ваших соцсетях:Округление чисел в Excel нужно для удобного форматирования, прогнозирования и коммуникации.
Существует множество способов округления, такие как округление до целого числа, округление в большую сторону, округление в меньшую сторону и т.д. В Excel мы можем округлять числа с помощью функций. Их мы и рассмотрим в этой статье.
Если вы хотите округлить числа исключительно с целью презентации, вы можете изменить формат ячейки, выполнив следующие шаги:
Важно! Этот метод только визуально округляет число, не “отрезая” числа после запятой.
Функция часто используется в Excel для округления чисел. С ее помощью вы можете задать количество чисел после запятой.
=ОКРУГЛ(число;число_разрядов)
Функция работает на основе математического правила, по которому она определяет в большую или меньшую сторону следует округлить число:
На скриншоте ниже показаны примеры использования функции:
Функция ОКРУГЛВВЕРХ округляет число в большую сторону, вне зависимости от того, какое число стоит после запятой.
=ОКРУГЛВВЕРХ(число;число_разрядов)
Примеры использования функции ниже:
Функция ОКРУГЛВНИЗ округляет число в меньшую сторону, вне зависимости от того, какое число стоит после запятой.
=ОКРУГЛВВНИЗ(число;число_разрядов)
На скриншоте ниже примеры использования функции:
Округление в Excel, в первую очередь, необходимо для удобного форматирования чисел.
Это можно сделать как до целочисленного значения, так и до определенного количества знаков после запятой.
Стоит заметить, что округлять цифры нужно правильно. Недостаточно просто взять и удалить несколько символов после запятой в длинном значении.
В противном случае, конечные расчеты в документе не сойдутся.
Округлить можно с помощью встроенного функционала программы. По сути, цифра обновляется лишь визуально, а в ячейке остается настоящее значение числа.
Таким образом можно проводить расчеты без потери данных, при этом пользователю будет комфортно работать с большими суммами и составлять конечный отчет.
На рисунке указан список основных функций округления и результат их использования:
Чтобы совершить простую процедуру округления чисел до нескольких знаков после запятой (в данном случае – до 2-х знаков), следуйте инструкции:
Для того, чтобы округлить десятичное число до целого, можно использовать сразу несколько функций, а именно:
С помощью вышеприведенных функций можно создать целочисленное число, согласно требованию пользователя.
После выбора функции, в открывшемся диалоговом окне укажите точность равную нулю.
Таким образом, знаки после запятой учитываться не будут.
Округляют числа в Excel несколькими способами. С помощью формата ячеек и с помощью функций. Эти два способа следует различать так: первый только для отображения значений или вывода на печать, а второй способ еще и для вычислений и расчетов.
С помощью функций возможно точное округление, в большую или меньшую сторону, до заданного пользователем разряда. А полученные значения в результате вычислений, можно использовать в других формулах и функциях. В то же время округление с помощью формата ячеек не даст желаемого результата, и результаты вычислений с такими значениями будут ошибочны. Ведь формат ячеек, по сути, значение не меняет, меняется лишь его способ отображения. Чтобы в этом быстро и легко разобраться и не совершать ошибок, приведем несколько примеров.
Впишем в ячейку А1 значение 76,575. Щелкнув правой кнопкой мыши, вызываем меню «Формат ячеек». Сделать то же самое можно через инструмент «Число» на главной странице Книги. Или нажать комбинацию горячих клавиш CTRL+1.
Выбираем числовой формат и устанавливаем количество десятичных знаков – 0.
Результат округления:
Назначить количество десятичных знаков можно в «денежном» формате, «финансовом», «процентном».
Как видно, округление происходит по математическим законам. Последняя цифра, которую нужно сохранить, увеличивается на единицу, если за ней следует цифра больше или равная «5».
Особенность данного варианта: чем больше цифр после запятой мы оставим, тем точнее получим результат.
С помощью функции ОКРУГЛ() (округляет до необходимого пользователю количества десятичных разрядов). Для вызова «Мастера функций» воспользуемся кнопкой fx. Нужная функция находится в категории «Математические».
Аргументы:
Теперь округлим целое число (не десятичную дробь). Воспользуемся функцией ОКРУГЛ:
Пример округления числа до тысяч:
Формула: =ОКРУГЛ(A3;-3).
Округлить можно не только число, но и значение выражения.
Допустим, есть данные по цене и количеству товара. Необходимо найти стоимость с точностью до рубля (округлить до целого числа).
Первый аргумент функции – числовое выражение для нахождения стоимости.
Для округления в большую сторону – функция «ОКРУГЛВВЕРХ».
Первый аргумент заполняем по уже знакомому принципу – ссылка на ячейку с данными.
Второй аргумент: «0» - округление десятичной дроби до целой части, «1» - функция округляет, оставляя один знак после запятой, и т.д.
Формула: =ОКРУГЛВВЕРХ(A1;0).
Результат:
Чтобы округлить в меньшую сторону в Excel, применяется функция «ОКРУГЛВНИЗ».
Пример формулы: =ОКРУГЛВНИЗ(A1;1).
Полученный результат:
Формулы «ОКРУГЛВВЕРХ» и «ОКРУГЛВНИЗ» используются для округления значений выражений (произведения, суммы, разности и т.п.).
Чтобы округлить до целого в большую сторону используем функцию «ОКРУГЛВВЕРХ». Чтобы округлить до целого в меньшую сторону используем функцию «ОКРУГЛВНИЗ». Функция «ОКРУГЛ» и формата ячеек так же позволяют округлить до целого числа, установив количество разрядов – «0» (см.выше).
В программе Excel для округления до целого числа применяется также функция «ОТБР». Она просто отбрасывает знаки после запятой. По сути, округления не происходит. Формула отсекает цифры до назначенного разряда.
Сравните:
Второй аргумент «0» - функция отсекает до целого числа; «1» - до десятой доли; «2» - до сотой доли и т.д.
Специальная функция Excel, которая вернет только целое число, – «ЦЕЛОЕ». Имеет единственный аргумент – «Число». Можно указать числовое значение либо ссылку на ячейку.
Недостаток использования функции «ЦЕЛОЕ» - округляет только в меньшую сторону.
Округлить до целого в Excel можно с помощью функций «ОКРВВЕРХ» и «ОКРВНИЗ». Округление происходит в большую или меньшую сторону до ближайшего целого числа.
Пример использования функций:
Второй аргумент – указание на разряд, до которого должно произойти округление (10 – до десятков, 100 – до сотен и т.д.).
Округление до ближайшего целого четного выполняет функция «ЧЕТН», до ближайшего нечетного – «НЕЧЕТ».
Пример их использования:
Если в ячейки табличного процессора вводятся большие числа (например, 78568435923100756), Excel по умолчанию автоматически округляет их вот так: 7,85684E+16 – это особенность формата ячеек «Общий». Чтобы избежать такого отображения больших чисел нужно изменить формат ячейки с данным большим числом на «Числовой» (самый быстрый способ нажать комбинацию горячих клавиш CTRL+SHIFT+1). Тогда значение ячейки будет отображаться так: 78 568 435 923 100 756,00. При желании количество разрядов можно уменьшить: «Главная»-«Число»-«Уменьшить разрядность».