Как в экселе задать округление числа. Изменение количества знаков после запятой без изменения значения

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

Но, какой бы способ округления вам не понадобился, его можно реализовать в Excel. В этой статье мы рассмотрим 2 основных способа округления, с помощью форматирования ячеек и с помощью формул. В качестве примера будет использоваться Excel 2010, но статья будет актуальной и для пользователей Excel 2007, 2013 и 2016.

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

Делается это очень просто, выделите мышкой ячейки, числа в которых вы хотите округлить, и перейдите на вкладку «Главная». Здесь, в блоке настроек который называется «Число», будет находится две кнопки: «Увеличить разрядность» и «Уменьшить разрядность». Первая кнопка увеличивает количество символов после запятой, которое отображаются в ячейке, а вторая кнопка соответственно уменьшает количество отображаемых символов.

Аналогичное действие можно сделать, если кликнуть правой кнопкой мышки по ячейке, значение которой нужно округлить, и перейти в «Формат ячеек».

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

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

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

Для округления чисел в Excel существуют следующие формулы:

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

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

• Сначала нужно написать знак равно (=) и название формулы (например, ОКРУГЛ).
• Дальше открываем круглую скобку и указываем адрес ячейки, значение которой нужно округлить (например, G3).
• Дальше ставим точку с запятой (;).
• Дальше вводим количество знаков после запятой, которые вы хотите получить после округления. Если нужно округлить до целого числа, то вводим 0.
• После этого закрываем круглую скобку и нажимаем Enter, для того чтобы сохранить набранную формулу.

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

На скриншоте вверху показан процесс набора формулы. Данная формула предполагает округление числа в ячейке G3 и помещение результата в ячейку H3.

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

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

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

Введите адрес ячейки с числом, которое вы хотите округлить до целого значения (в данном случае это ячейка G3) и вставьте точку с запятой (;).

В результате в ячейке H3 вы получите число из ячейки G3, но округленное до целого значения.

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

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

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

Таблица 111.1. Функции округления Excel

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

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

Функция ОКРУГЛТ полезна для округления значений до ближайших кратных. Например, вы можете использовать ее, чтобы округлить число до ближайшего, кратного 5. Следующая формула возвращает 135: =ОКРУГЛТ(133;5) .

Округление денежных значений

Часто приходится округлять валютные значения. Например, рассчитанная цена может оказаться равной 45,78923 р. В таком случае вы захотите округлить ее до ближайшей копейки. Этот процесс может показаться простым, но для округления значений такого типа вам предлагается на выбор один из трех следующих способов:

• в большую сторону до ближайшей копейки;
• в меньшую сторону до ближайшей копейки;
• до ближайшей копейки (округление может быть или в большую, или в меньшую сторону).

Следующая формула предполагает, что значение рублей и копеек содержится в ячейке А1. Формула округляет значение до ближайшей копейки. Например, если ячейка А1 содержит 12,421 р., формула возвращает 12,42 р.: =ОКРУГЛ(А1;2) .

Если вам необходимо округлить значение в большую сторону до ближайшей копейки, используйте функцию ОКРВВЕРХ . Следующая формула, например, округляет значение в ячейке А1 до ближайшей копейки (если ячейка А1 содержит значение 12.421 р., формула возвращает 12,43 р.): =ОКРВВЕРХ(А1;0,01) .

Чтобы округлить рублевое значение в меньшую сторону, используйте функцию ОКРВНИЗ . Следующая формула, например, округляет такое значение в ячейке А1 в меньшую сторону до ближайшей копейки (если ячейка А1 содержит 12.421 р., то формула возвращает 12.42 р.): =ОКРВНИЗ(А1;0,01) .

Чтобы округлить рублевое значение до пяти копеек, используйте эту формулу: =ОКРВВЕРХ(А1;0,05) .

Использование функций ЦЕЛОЕ и ОТБР

На первый взгляд, функции ЦЕЛОЕ и ОТБР могут показаться похожими. Они обе преобразовывают значение в целое число. Функция ОТБР просто удаляет дробную часть числа. Функция ЦЕЛОЕ округляет в меньшую сторону до ближайшего целого числа, основываясь на значении дробной части числа.

На практике ЦЕЛОЕ и ОТБР возвращают разные значения только при использовании отрицательных чисел. Например, эта формула возвращает -14.0: =ОТБР(-14,2) .

Следующая формула возвращает -15,0, поскольку число -14,2 округляется в меньшую сторону до следующего целого числа: =ЦЕЛОЕ(-14,2) .

Функция ОТБР принимает дополнительный (необязательный) аргумент, который может быть полезен для усечения десятичных значений. Например, следующая формула возвращает 54,33 (значение усекается до двух десятичных знаков): =ОТБР(54.3333333;2) .

Округление до N значащих цифр

В некоторых ситуациях может понадобиться округлить значение до определенного количества значащих цифр. Например, вы хотите выразить число 1432,187 в терминах двух значащих цифр (то есть как 1400,000). Число 84 356 выражается в трех значимых цифрах как 84 300.

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

Для нецелых и отрицательных чисел решение будет немного более сложным. Следующая формула предлагает более общее решение, округляющее значение в ячейке А1 до количества значащих цифр, указанного в ячейке А2. Эта формула работает для положительных и отрицательных целых и нецелых чисел: =ОКРУГЛ(Al;А2-1-ЦЕЛОЕ(LOG10(ABS(А1)))) .

Например, если ячейка А1 содержит число 1,27845, а в ячейке А2 хранится 3, то формула вернет 1,28000 (значение, округленное до трех значащих цифр).

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

Округление чисел в 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 в меньшую сторону с помощью функции ОКРУГЛВНИЗ

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ОКРУГЛТ

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

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

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

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

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

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