Как в excel настроить округление

Как округлить число в Excel? Быстрое округление чисел

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

Виды чисел

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

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

Приближенные

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

Что такое округление?

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

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

Наиболее распространенный способ – математическое округление, когда число округляется в меньшую сторону, если в числе «отбрасываемая» цифра меньше пяти и округляется в большую сторону, если в числе «отбрасываемая» цифра больше либо равна пяти.

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

Изменение количества отображаемых знаков после запятой без изменения числового значения

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

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

Как видно из примера, некорректная настройка числового формата привела к тому, что 2+3=6, что неверно.

Задать точность как на экране

Следует отметить еще одну возможность Excel, заложенную в параметрах Excel, в разделе «Дополнительно», в группе «При пересчете этой книги» — это пункт «Задать точность как на экране».

*Применение опции «Задать точность как на экране» ведет к изменению числовых значений и точности вычислений.

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

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

Функция ОКРУГЛ(число;число_разрядов) округляет число до указанного количества десятичных разрядов по классическим правилам математического округления;

Функция ОКРУГЛВВЕРХ(число;число_разрядов) округляет число с избытком до ближайшего большего по модулю;

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

Функция ОКРУГЛТ(число;точность) округляет число до кратного заданному числу, в формуле ОКРУГЛТ параметр «точность» — это кратное, до которого нужно округлить результат.

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

Функция ОКРВНИЗ(число;точность) округляет с недостатком число до ближайшего целого либо до ближайшего кратного указанному значению.

*В некоторых версиях Excel функция ОКРУГЛТ отсутствует, для ее появления необходимо установить стандартную надстройку Excel – «Пакет анализа». Е сли функция ОКРУГЛТ есть в математических функциях, но возвращает ошибку #ИМЯ?, попробуйте установить стандартную надстройку Excel – «Пакет анализа».

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

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

Функция НЕЧЁТ(число) округляет число до ближайшего нечетного целого. Положительные числа округляются в сторону увеличения, а отрицательные – в сторону уменьшения;

Функция ОТБР(число) округляет число до целого, отбрасывая дробную часть.

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

Для тех, кто использует стандартные функции Excel при написании макросов в VBA, может быть полезен сайт http://ru.excelfunctions.eu/, где есть сопоставление между русскими и английскими именами функций с их русским описанием.

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

=ОКРУГЛ(1,475;2) Округляет число 1,475 до двух десятичных разрядов, в результате получается 1,48;

=ОКРУГЛВВЕРХ(3,14159;3) Округляет с избытком число 3,14159 до трех десятичных разрядов, результат 3,142;

=ОКРУГЛВНИЗ(3,14159) Округляет с недостатком число 3,14159 до трех десятичных разрядов, результат 3,141;

=ОКРУГЛТ(10;3) Округляет число 10 до ближайшего числа, кратного 3, то есть до 9;

=ОКРВВЕРХ(2,5;1) Округляет число 2,5 до ближайшего большего числа, кратного 1, то есть до 3;

=ОКРВНИЗ(2,5;1) Округляет число 2,5 до ближайшего меньшего числа, кратного 1, то есть до 2.

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

Быстрое округление числовых значений в Excel без использования формул

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

Возможности надстройки позволяют:

1. Одним кликом мыши вызывать диалоговое окно прямо из панели инструментов Excel;

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

3. быстро выбирать на одной из трех вкладок диалогового окна нужный способ округления и задавать его точность (доступно 9 способов);

4. легко изменять область для поиска и замены чисел их округленными значениями.

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

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Допустим, вы хотите округлить число до ближайшего целого числа, так как десятичные значения несущественны. Вы также можете округлить число до кратных 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 нужно для удобного форматирования, прогнозирования и коммуникации.

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

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

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

• Кликнуть по ячейке с числовым значением;
• Открыть меню форматирования ячейки с помощью сочетания клавиш CTRL+1 или кликнуть по ячейке правой кнопкой и в выпадающем списке выбрать пункт “ Формат ячеек “;
• В всплывающем окне “ Формат ячеек ” перейти на вкладку “ Число “;
• В разделе “ Числовые форматы ” кликнуть по пункту “ Числовой “;
• В меню окна найти пункт “ Число десятичных знаков ” и указать точное количество знаков после запятой, которое вы хотите оставить;
• Кликните по кнопке “ ОК ” для сохранения настроек форматирования.

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

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

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

=ОКРУГЛ(число;число_разрядов)

Аргументы функции

• число – числовое значение, которое вы хотите округлить;
• число_разрядов – значение десятичного разряда, до которого вы хотите округлить первый аргумент функции.

Основы использования функции

• если аргумент число_разрядовбольше “0”, то число округляется до указанного количества десятичных знаков. Например, =ОКРУГЛ(500,51;1) вернет “500.5”;
• если аргумент число_разрядов равен “0”, то число округляется до ближайшего целого числа. Например, =ОКРУГЛ(500,51;0) вернет “501”;
• если аргумент число_разрядовменьше “0”, то число округляется влево от десятичного значения. Например, =ОКРУГЛ(500,51;-1) вернет “501”.

Функция ОКРУГЛ работает на основе математического правила, по которому она определяет в большую или меньшую сторону следует округлить число:

• Если число после запятой равно от 0 до 4, то округление будет произведено в меньшую сторону;
• Если число после запятой равно от 5 до 9, то функция округлит в большую сторону.

На скриншоте ниже показаны примеры использования функции:

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

Функция ОКРУГЛВВЕРХ округляет число в большую сторону, вне зависимости от того, какое число стоит после запятой.

=ОКРУГЛВВЕРХ(число;число_разрядов)

Аргументы функции

• число – числовое значение, которое вы хотите округлить;
• число_разрядов – значение десятичного разряда, до которого вы хотите округлить первый аргумент функции.

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

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

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

=ОКРУГЛВВНИЗ(число;число_разрядов)

Аргументы функции

• число – числовое значение, которое вы хотите округлить;
• число_разрядов – значение десятичного разряда, до которого вы хотите округлить первый аргумент функции.

На скриншоте ниже примеры использования функции:

Округление чисел в Microsoft Excel

Редактор таблиц Microsoft Excel широко применяется для выполнения разного рода вычислений. В зависимости от того, какая именно задача стоит перед пользователем, меняются как условия выполнения задачи, так и требования к получаемому результату. Как известно, выполняя расчёты, очень часто в результате получаются дробные, нецелые значения, что в одних случаях хорошо, а в других, наоборот, неудобно. В этой статье подробно рассмотрим, как округлить или убрать округление чисел в Excel. Давайте разбираться. Поехали!

Для удаления дробных значений применяют специальные формулы

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

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

Но в Excel реализована отдельная функция, позволяющая выполнять настоящее округление по математическим правилам. Для этого вам понадобится поработать с полем для формул. Например, вам нужно округлить значение, содержащееся в ячейке с адресом A2 так, чтобы после запятой остался только один знак. В таком случае функция будет иметь такой вид (без кавычек): «=ОКРУГЛ(A2;1)».

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

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

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

В последних версиях программы реализованы функции «ОКРВВЕРХ.МАТ» и «ОКРВНИЗ.МАТ». Они могут пригодиться, если нужно принудительно выполнить округление в какую-либо сторону с указанной точностью.

Как вариант, решить проблему можно при помощи функции «ЦЕЛОЕ», принцип которой заключается в том, что дробная часть просто отбрасывается, округляя вниз.

Иногда программа автоматически округляет полученные значения. Отключить это не получится, но исправить ситуацию можно при помощи кнопки «Увеличить разрядность». Кликайте по ней, пока значение не приобретёт нужный вам вид.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы округлить в меньшую сторону в 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. При желании количество разрядов можно уменьшить: «Главная»-«Число»-«Уменьшить разрядность».