Формула округления в большую сторону excel. Как осуществить округление в Excel
Сегодня мы с вами говорим о том, как правильно использовать функцию округления в программе Microsoft Office Excel. Существует несколько различных способов, которыми можно осуществить данную операцию, однако не всякое округление в Excel может привести к желаемому результату.
Форматирование
Первое, что можно использовать для округления чисел - это формат ячейки. Разберём конкретный пример. Пусть нам даны две ячейки с небольшими значениями. А1=2,3; А2=2,3. В отдельное место запишем формулу, которая вычисляет их сумму. Если не использовать округление в Excel, то мы получим, что А3=4,6. Но что произойдет, если мы изменим формат ячейки?
Выделяем все три клетки со значениями и нажимаем на поле правой кнопкой мышки. В выпадающем меню нас интересует пункт "Формат ячеек". В открывшемся окне выбираем формат - числовой, и нужное количество знаков после запятой - 0. Нажимаем кнопку "Ок".
Что мы видим? Теперь все три ячейки приобрели значения - 2;2;5. То есть 2+2=5. При этом, если установить курсор на соответствующие ячейки, будет видно исходное значение в 2,3. Тут-то и ломается логика вычислений, с которой знаком любой первоклассник. Дело в том, что округление в Excel придерживается математических правил. То есть округление числа от 1 до 4 происходит в меньшую сторону, а от 5 до 9 - в большую. Поэтому использовать данную команду нужно с большой осторожностью.
Формулы
Провести округление в Excel можно также с помощью специальных команд в формулах. Это очень удобно в тех случаях, когда необходимо в вычислении округлить одно конкретное значение, а не всю ячейку целиком.
Существует целый набор функций, для того чтобы провести округление в Excel.
- ОКРУГЛ (число; разрядность). Для того чтобы использовать данный синтаксис, вам надо будет указать два параметра. Число - значение, которое необходимо округлить. Это может быть как значение отдельной ячейки, так и целая формула. Разрядность - количество символов после запятой.
- ОКРУГЛВВЕРХ (число; разрядность). Синтаксис аналогичен предыдущей команде, только всегда округляет число до большего (например, 2,1 до 3).
- ОКРУГЛВНИЗ (число; разрядность). Округляет до меньшего (3,9 до 3).
Таким образом, функция округления в Excel позволяет сократить количество символов после запятой. Если вы обратили внимание, то команда ОКРУГЛ работает точно так же, как и форматирование ячейки в числовой тип, однако позволяет округлить только часть выражения. Например, В1=ОКРУГЛ (А1+А2;2)*О3. Таким образом, команда округлит исключительно сумму двух ячеек до двух знаков после запятой, а после этого помножит результат на значение ячейки О3.
Целые
Отдельно мы рассмотрим округление до целых в Excel. Для проведения данной операции можно воспользоваться как уже рассмотренными командами, так и некоторыми другими.
- Формат. Для того чтобы ячейки автоматически округлялись до целочисленных значений, укажите в их формате значение - числовой, и количество знаков после запятой - 0.
- Все команды из предыдущего параграфа, применяющин параметр разрядности, могут использоваться как функция округления в Excel до целых. Для этого нужно указать разряд, равный нулю (0).
- ОКРВВЕРХ (значение; точность). Округляет число до большего значения. Точность определяет кратность результата. Например, ОКРВВЕРХ(12,753;1)=13. Но, ОКРВВЕРХ (12,753;2)=14. То есть число, кратное 2 (делящееся на 2 без остатка).
- ОКРВНИЗ (число; точность). Аналогично предыдущей команде только округляет до меньшего числа (ОКРВНИЗ(12,753;1)=12).
- ОТБР (число). Отбрасывает все знаки после запятой. Механика аналогична предыдущему случаю с точностью, равной 1.
- ОКРУГЛТ (число; точность). Формула округления в Excel, работающая по математическим правилам. Округляет до целых с определённой точностью, но самостоятельно определяет "вниз" или "вверх".
- ЧЁТН (значение). Работает так же, как и предыдущая функция при указании точности - 2, но округление производится исключительно "вверх".
- НЕЧЁТ (значение). Округляет до ближайшего большего нечетного числа. Пример: НЕЧЁТ (12,3231)=13, либо НЕЧЁТ (13,7345)=15.
Выводы
Как видите, функция округления в Excel может использоваться в самых различных вариациях и случаях. Если вы боитесь забыть о прочитанном в статье, не волнуйтесь. Встроенная помощь программы Excel всегда подскажет вам, как именно должна прописываться команда и её аргументы. Вы всегда можете воспользоваться подпрограммой вставки оператора.
На картинках в статье вы можете видеть действие оператора ROUND, который соответствует рассматриваемому нами оператору в английской версии "Офиса".
Округление - это математическая операция, позволяющая сократить количество знаков, используемых для фиксации того или иного числа, за счет некоторого снижения его точности. В программе Excel операцию округления можно осуществить автоматически. При этом она позволяет округлять как дробные, так и целые числа.
Округление дробей
Округление дробных чисел позволяет пользователю установить заданное количество знаков после запятой в десятичной дроби, которое будет отображаться в ячейке вне зависимости от действительного количества этих знаков, внесенных в программу. Для того, чтобы осуществить операцию округления, необходимо при помощи левой кнопки мыши выделить ячейку или числовой массив, в отношении которого ее следует произвести. В случае, если требуется округлить два или более числа или массива, расположенных в различных частях таблицы, выделить их можно, удерживая клавишу CTRL.Провести операцию округления дробного числа можно, выбрав в верхнем меню вкладку «Формат» и кликнув левой кнопкой мыши на позицию «Ячейка». Это действие вызовет появление меню, необходимого для осуществления операции. Кроме того, перейти в это меню можно и другим способом: кликнув правой кнопкой мыши на выделенном участке таблицы и выбрав пункт «Формат ячейки».
В данном меню следует выбрать числовой формат ячейки и в специальном окне отметить нужное количество знаков после запятой. Так, например, если этот показатель был установлен в виде цифры 2, то первоначальная дробь вида 1,58165874, подвергшаяся округлению, примет вид 1,58.
Округление целых чисел
Кроме того, программа Excel позволяет также произвести операцию округления в отношении целых чисел, сделав их более удобными для восприятия. Сделать это можно, прибегнув с использованию специальной функции, обозначающейся ОКРУГЛ. При этом следует иметь в виду, что такой же способ выполнения необходимого действия можно использовать и в отношении дробей.Указанная функция имеет два аргумента. Первый из них - ячейка или числовой массив, в отношении которого необходимо осуществить операцию округления. Второй аргумент этой функции - число разрядов для округления. Положительные разряды применяются для округления дробных чисел, а цифра, обозначающая разряд, в этом случае равна количеству знаков после запятой. Нулевой разряд повлечет за собой числа до целого значения, а отрицательный - округление до определенного разряда. Например, разряд, равный -1, вызовет округление до десятков, -2 округлит число до сотен и так далее.
В результате функция, применяемая для осуществления этой операции, будет выглядеть следующим образом. Например, существует необходимость округления числа 101, расположенного в ячейке А3, до сотен. В данном случае следует записать функцию так: =ОКРУГЛ(А2;-2). Применение этой функции обеспечит отображение в ячейке А3 указанного числа в виде 100.
Округляют числа в Excel несколькими способами. С помощью формата ячеек и с помощью функций. Эти два способа следует различать так: первый только для отображения значений или вывода на печать, а второй способ еще и для вычислений и расчетов.
С помощью функций возможно точное округление, в большую или меньшую сторону, до заданного пользователем разряда. А полученные значения в результате вычислений, можно использовать в других формулах и функциях. В то же время округление с помощью формата ячеек не даст желаемого результата, и результаты вычислений с такими значениями будут ошибочны. Ведь формат ячеек, по сути, значение не меняет, меняется лишь его способ отображения. Чтобы в этом быстро и легко разобраться и не совершать ошибок, приведем несколько примеров.
Как округлить число форматом ячейки
Впишем в ячейку А1 значение 76,575. Щелкнув правой кнопкой мыши, вызываем меню «Формат ячеек». Сделать то же самое можно через инструмент «Число» на главной странице Книги. Или нажать комбинацию горячих клавиш CTRL+1.
Выбираем числовой формат и устанавливаем количество десятичных знаков – 0.
Результат округления:
Назначить количество десятичных знаков можно в «денежном» формате, «финансовом», «процентном».
Как видно, округление происходит по математическим законам. Последняя цифра, которую нужно сохранить, увеличивается на единицу, если за ней следует цифра больше или равная «5».
Особенность данного варианта: чем больше цифр после запятой мы оставим, тем точнее получим результат.
Как правильно округлить число в Excel
С помощью функции ОКРУГЛ() (округляет до необходимого пользователю количества десятичных разрядов). Для вызова «Мастера функций» воспользуемся кнопкой fx. Нужная функция находится в категории «Математические».
Аргументы:
- «Число» - ссылка на ячейку с нужным значением (А1).
- «Число разрядов» - количество знаков после запятой, до которого будет округляться число (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 . Очень часто вы сталкиваетесь с тем, что выгружая данные из других источников или делая собственные вычисления вы получаете числа с огромным количеством значений после запятой, что в свою очередь может негативно сказаться на конечном результате вычислений, ведь вы видите число с двумя знаками после запятой (например, 98,12), а для компьютера это уже другое число (например, 98,1248247…...) и соответственно он работает с тем, что видит сам, а не то что видите вы. Но такой вариант снижает точность получаемых данных, так что используйте функции округления в конкретных и нужных вычислениях. Эта возможность станет очень полезной в , когда каждое несоответствие в 1 копейку уже нарушает баланс и гармонию в вычислениях.
Как происходит округление чисел в Excel, рассмотрим четыре главных существующих функции:
- Функция ОКРУГЛ (ROUND) – позволит вам производить округление чисел в Excel до указанного критерия.
- = ОКРУГЛ(A1;2) – округление до второго знака;
- = ОКРУГЛ(A1;1) — округление до первого знака;
- = ОКРУГЛ(A1;0) — округление до целого значения;
- = ОКРУГЛ(A1;-1) – округление до ближайшего десятка;
- = ОКРУГЛ(A1;-2) — округление до ближайшей сотни;
Напоминание! Округление чисел в Excel производится, для чисел от 1 до 4 в меньшую сторону, а от 5 до 9 в большую сторону. (Пример, число 98,1268…. при округлении будет 98,13, а при округлении до одного знака, станет 98,1)
2. Функция ОКРУГЛВВЕРХ (ROUNDUP) – позволит вам принудительно производить округление чисел в Excel в большую сторону, если оно положительное и в меньшую, если оно отрицательное. Основным критерием служит то, как далеко по числовой оси от нуля находится ваше число:
- = ОКРУГЛВВЕРХ(A1;1) -98,1268 = -98,2
- = ОКРУГЛВВЕРХ(A1;1) 98,1268 = 98,2
3. Функция ОКРУГЛВНИЗ (ROUND DOWN) – это принудительно производить округление чисел в Excel в меньшую сторону, при его положительности и в большую сторону, если оно отрицательное. Здесь, как и в предыдущей функции, критерий тот же, значение стремится к нулю по числовой оси.
- = ОКРУГЛВНИЗ(A1;1) -98,1268 = -98
- = ОКРУГЛВНИЗ(A1;1) 98,1268 = 98
4. Функция НЕЧЕТ – позволяет производить округление чисел в Excel к самому ближайшему нечётному числу. Несмотря на знаки числа, округление будет выполнено с избытком. Если число уже является нечётным, действий никаких не будет.
- = НЕЧЕТ (A1) 98,1268 = 99
При работе с большими диапазонами и использовании автокопирования, в формулах нужно использовать .
Главные функции, которые производят округление чисел в Excel, мы рассмотрели, теперь можете применять полученные знание на практике. С другими функциями вы можете ознакомится в
Бывает такое, что возникает необходимость выполнить округление в Excel, и пользователи не знают, как сделать это точно и правильно. Между тем, функция округления дает возможность округлить число в меньшую (большую) сторону до сотен, десятков или целого числа. При этом, есть несколько способов сделать это.
- Первый способ: с помощью использования формата ячейки убрать все цифры, которые следуют за запятой. Но в таком случае есть маленький нюанс: если убрать после запятой все нули в имеющемся столбце цифр, а также в ячейке с итогами, то можно получить неверный результат.
- Второй способ предполагает использование формулы . Для этого необходимо в ячейку, в которой находится сумма, поставить функцию «ОКРУГЛ »: на закладке «Формулы» выбираете раздел «Библиотека функций», в котором нам нужны «Математические». И уже в этом разделе находим и нажимаем нужную функцию «ОКРУГЛ».
После этого появится диалоговое окно, которое называется «Аргументы функций». Вам необходимо его заполнить. В строке число можно написать сразу число, которое вы собираетесь округлить. Но можно сразу прописать какую-либо необходимую функцию, например, умножение. Это избавит вас от лишних движений.
Далее идет строка «Число_разрядов», в которой следует указать количество разрядов, до которого вам необходимо округлить число. Так, если в этой строке поставить «3», то останется 3 знака после запятой. При этом, если поставить «0», то число автоматически округлится до цулого значения. Если есть необходимость округлить целое число до десятков, то для этого в строке «Число_разрядов» следует написать отрицательное значение. Например, мы имеем число 123 456, которое хотим округлить до сотен. Для этого ставим в графу значение «-2» и получаем число 123 500.