Округление чисел в microsoft excel

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

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

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

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

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

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

Вопрос: как округлить числа в Excel, интересует многих пользователей. Именно о нем мы и поговорим.

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

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

формула, как округлить число в большую сторону

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

1. Первый способ: с помощью использования формата ячейки убрать все цифры, которые следуют за запятой. Но в таком случае есть маленький нюанс: если убрать после запятой все нули в имеющемся столбце цифр, а также в ячейке с итогами, то можно получить неверный результат.
2. Второй способ предполагает использование формулы. Для этого необходимо в ячейку, в которой находится сумма, поставить функцию «ОКРУГЛ»: на закладке «Формулы» выбираете раздел «Библиотека функций», в котором нам нужны «Математические». И уже в этом разделе находим и нажимаем нужную функцию «ОКРУГЛ».

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

Далее идет строка «Число_разрядов», в которой следует указать количество разрядов, до которого вам необходимо округлить число. Так, если в этой строке поставить «3», то останется 3 знака после запятой. При этом, если поставить «0», то число автоматически округлится до цулого значения. Если есть необходимость округлить целое число до десятков, то для этого в строке «Число_разрядов» следует написать отрицательное значение. Например, мы имеем число 123 456, которое хотим округлить до сотен. Для этого ставим в графу значение «-2» и получаем число 123 500.

В Excel есть еще несколько функций, которые помогут с округлением. Например,

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

Как округлять до десятых и сотых (один и два знака после запятой, соответственно).

Для округления до десятых следует умножить переменную на 10 (десять), после чего округлить до целых и разделить на 10 (десять). Пример: Cint(int( -3.1415926535*10))/10 = 3.1 Cint(fix( -3.1415926535*10))/10 = 3.1

Для округления до сотых следует проделать ту же самую операцию, только умножать и делить не на 10, а на 100. Пример: Cint(int( -3.1415926535*100))/100 = 3.14 Cint(fix( -3.1415926535*100))/100 = 3.14

Пример кода округляющего значения из textbox, запускающийся нажатием кнопки CommandButtom1:

Private sub CommandButtom1_click() textbox2.value = Cint(int(textbox1.value *100))/100 END SUB

Видео с примером работы макроса:

Как видите, ничего сложного в округлении чисел в vba Excel нет. Удачи Вам в изучении программы

Похожее:

1. Макрос определяющий пустая ли ячейка или заполненная в VBA ExcelМакрос проверки заполнения ячеек. Периодически при создании.
2. Функция VAL в VBA Excel или как преобразовать TextBox в число (цифру).Использования функции преобразования текста в число в.
3. Макрос для быстрой замены формул на значения (числа) в выделенных ячейках документа Excel.Когда удобно менять формулы на значения нажатием.

Как используют маркетологи неумение массового потребителя округлять цифры?

Оказывается, большая часть людей на свете не имеет привычки оценить реальную стоимость продукта, что активно эксплуатируют маркетологи. Все знают слоганы акций типа «Покупайте всего за 9,99». Да, мы сознательно понимаем, что это уже по сути десять долларов. Тем не менее наш мозг устроен так, что воспринимает только первую цифру. Так что нехитрая операция приведения числа в удобный вид должно войти в привычку.

Очень часто округление позволяет лучше оценить промежуточные успехи, выражающиеся в численной форме. Например, человек стал зарабатывать 550 долларов в месяц. Оптимист скажет, что это почти 600, пессимист — что это чуть больше 500. Вроде бы разница есть, но мозгу приятнее «видеть», что объект достиг чего-то большего (или наоборот).

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

Важно проявлять изобретательность и по возможности на загружаться ненужной информацией. Тогда успех будет незамедлительным

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

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

С помощью кнопки

Выделите ячейки, которые нужно отформатировать.

На вкладке Главная
выберите команду Увеличить разрядность
или Уменьшить разрядность
, чтобы отобразить больше или меньше цифр после запятой.

С помощью

встроенного числового формата

На вкладке Главная
в группе Число
щелкните стрелку рядом со списком числовых форматов и выберите пункт Другие числовые форматы
.

В поле Число десятичных знаков
введите число знаков после запятой, которые вы хотите отображать.

С помощью функции в формуле

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

Первый аргумент — это число, которое необходимо округлить. Он может быть ссылкой на ячейку или числом.

Второй аргумент — это количество цифр, до которого необходимо округлить число.

Предположим, что ячейка A1 содержит число 823,7825
. Вот как можно округлить его.

Чтобы округлить до ближайшей тысяч

и

Введите =ОКРУГЛ(A1;-3)
, что равно 100
0

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

Чтобы округлить до ближайших сотен

Введите =ОКРУГЛ(A1;-2)
, что равно 800

Число 800 ближе к 823,7825, чем к 900. Наверное, теперь вам все понятно.

Чтобы округлить до ближайших

десятков

Введите =ОКРУГЛ(A1;-1)
, что равно 820

Чтобы округлить до ближайших

единиц

Введите =ОКРУГЛ(A1;0)
, что равно 824

Используйте ноль для округления числа до ближайшей единицы.

Чтобы округлить до ближайших

десятых

Введите =ОКРУГЛ(A1;1)
, что равно 823,8

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

Чтобы округлить до ближайших

сотых

Введите =ОКРУГЛ(A1;2)
, что равно 823,78

Чтобы округлить до ближайших

тысячных

Введите =ОКРУГЛ(A1;3)
, что равно 823,783

Округлите число в большую сторону с помощью функции ОКРУГЛВВЕРХ . Она работает точно так же, как функция ОКРУГЛ, за исключением того, что она всегда округляет число в большую сторону. Например, если необходимо округлить число 3,2 до ноля разрядов:

=ОКРУГЛВВЕРХ(3,2;0)
, что равно 4

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

=ОКРУГЛВНИЗ(3,14159;3)
, что равно 3,141

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

Содержание урока

Как получить первое минимальное значение?

Для демонстрации решения задачи создадим простую таблицу:

В этом примере данные таблицы где нужно найти значение находятся в диапазоне A6:B18. Как видно на рисунке в столбце A содержаться значения, а в столбце B соответствующие им суммы. Кроме того, видно, что среди сумм встречается несколько минимальных значений разбросанных по разным строкам.

Чтобы получить значение из столбца A которому соответствует первая наименьшая сумма в столбце B выполним 2 простых шага действий:

1. В ячейке B3 введите следующую формулу:
2. Подтвердите ввод формулы комбинацией горячих клавиш CTRL+SHIFT+Enter, так как она должна выполнится в массиве. Если все сделано правильно в строке формул появятся фигурные скобки {}.

В результате мы получили значение, соответствующее первой наименьшей сумме.

Детальный разбор формулы для первого наименьшего значения

Функция ИНДЕКС является самой главной в этой формуле. Ее номинальная задача – это поиск значения в указанной таблице (диапазон A6:A18 указанный в первом аргументе функции) на основе координат, указанных в ее втором (номер строки таблицы) и третьем (номер столбца) аргументах. Так как таблица, которую указано для работы функции ИНДЕКС имеет только один столбец A, то третий аргумент в параметрах – пропущен (необязателен). В тоже время во втором аргументе сразу используется несколько функций, работающих с диапазоном B6:B18.

В первом аргументе функции ЕСЛИ тестирует: является ли значение в каждой ячейке диапазона B6:B18 наименьшим числом. Таким образом в памяти создается массив логических значений ИСТИНА и ЛОЖЬ. В данном примере массив содержит только три элемента с положительным результатом теста, так как столько же содержит одинаковых минимальных значений исходная таблица. Если результат положительный формула переходит к следующему вычислительному этапу, а если отрицательный функция возвращает в массив памяти пустое текстовое значение.

Следующий вычислительный этап формулы – это определение, какие номера строк содержат эти минимальные суммы. Данный этап нам необходим для того, чтобы определить первое минимальное значение в диапазоне B6:B18. Реализовывается такая задача с помощью функции СТРОКА, которая создает в памяти программы следующий массив, состоящий из номеров строк. От этих номеров следует вычесть номер первой строки с которой начинается диапазон исходной таблицы

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

Далее функция МИН возвращает наименьший номер строки таблицы, которая содержит минимальное значение. Этот номер использует в качестве второго аргумента функция ИНДЕКС, которая поэтому же номеру строки возвращает нам значение из диапазона A6:B18

Таким образом в памяти программы сформируется массив, состоящий из номеров строк. Далее функция МИН возвращает наименьший номер строки таблицы, которая содержит минимальное значение. Этот номер использует в качестве второго аргумента функция ИНДЕКС, которая поэтому же номеру строки возвращает нам значение из диапазона A6:B18.

ПРИМЕР

На таможню приходит товар стоимостью 3 цента за единицу в количестве 1000 штук. Курс обмена — 29,31 руб./$, а все таможенные платежи (без учета НДС) составляют 29,45%. База начисления для таможенных платежей будет равна цене в долларах, умноженной на курс доллара и количество единиц товара: $0,03 * 29,31 * 1000 = 879,30 руб.

При умножении базы начисления на ставку таможенного сбора получается сумма, которой быть не может (так как не существует пока в мире десятых и сотых долей копеек при перечислении их через банк): 879,30 руб. * 29,45% = 258,9539 руб.

Если и далее производить расчеты без округления размера таможенного платежа, то можно получить стоимость партии товара, которая равна стоимости товара плюс таможенные платежи: 879,30 руб. + 258,9539 руб. = 1138,25385 руб.

Таким образом, цена единицы товара будет следующей: 1138,25385 руб. : 1000 шт. = 1,138254 руб.

Полученные неправильные результаты представлены в табл. 2.1.

Цена ($)	Количество	Курс (руб./$)	Таможенный платеж (%)	Таможенный платеж (руб.)	Итого стоимость партии (руб.)	Цена (руб.)
0,03	1000	29,31	29,45%	258,9539	1138,25385	1,138254

Таблица 2.1.

Поэтому некоторые значения следует округлить с точностью до копеек. А сумма таможенного платежа должна вычисляться по формуле:

=ОКРУГЛ(цена в долларах х на курс доллара х количество;2)

Цифра 2 означает, что выполняется округление с точностью до второго знака (то есть до копеек).

Таким же образом можно округлить и стоимость партии товара, в результате чего получится сумма, равная 1138,25 руб. Однако операция округления цены за единицу товара может привести к нежелательным последствиям. Это зависит от того, как происходит расчет цены. Если округлить цену товара до копеек, исходя из стоимости партии товара:

=ОКРУГЛ(1138,25/1000;2)

то результат будет равен 1,14 руб. Но получается парадокс: произведя обратный расчет, мы получим, что партия стоит 1,14 * 1000 =1140 руб. Откуда-то взялись лишние 2 рубля. Эти 2 рубля могут значительно усложнить ведение бухгалтерского учета, если в бухгалтерской программе не предусмотрена возможность задания разрядности денежной единицы при некоторых операциях. До какого же знака целесообразно задать точность в данном примере?

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

Цена ($)	Количество	Курс (руб./$)	Таможенный платеж (%)	Таможенный платеж (руб.)	Итого стоимость партии (руб.)	Цена (руб.)
0,03	1000	29,31	29,45%	258,95	1138,25	1,13825

Таблица 2.2.

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

Рис. 2.29. Функции округления в Excel

Примеры функций ОКРГУЛ и ОКРУГЛТ для округления чисел в Excel

Функция ОКРУГЛ используется для округления действительных чисел до требуемого количества знаков после запятой и возвращает округленное значение. Например, для округления числа 1,22365 до двух знаков после запятой можно ввести формулу =ОКРУГЛ(1,22365;2), которая вернет значение 1,22.

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

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

Убрать лишнее после запятой.

​. Например, чтобы все​​ формуле =ОКРВВЕРХ(C12;10), где​ так.​ Excel можно​ увеличили форматом число​Число​ для всего числа.​ заблокирована.​ i + 1)​но суть одна…​ кол-во знаков​ Excel нужно вводить​ в Excel, может​ не округляя.​ если десятичная часть​Да с любым​

​ отбросить (обнулить), лишние​​ числа делились на​​ число 10 в​​Формула такая.​округлить число точно или​ разрядов, число цифр​

​,​​ Он также может​​Но выглядит это​​End Function​

​предлагаю считать отдельно​​Геннадий гривин​​ обязательно. Иначе может​

​ отличаться. Форматирование в​​Т.е. должно быть​ больше или равна​​ количеством знаков. Тем​

​ значения после запятой?​​ три без остатка.​​ формуле, означает округлить​

​Теперь копируем эту​​ в большую (меньшую)​ после запятой.​

​Денежный​​ возникать при формат​ следующим образом:​Hugo​ целую и дробную​: «Формат» — Ячейки​

​ получиться так 0,01​​ реальности не округляет​ 11278,61​​ 5, то результат​​ более у Вас,​ 123,4556 должно быть​ Как округлить числа​

​ до десятков.​​ формулу вниз перетаскиванием.​ сторону, до целого​​Какими способами быстро​,​​ настроен для отображения​в столбце А:​​: UDF:​ часть, а потом​​ — Число -​ или 0,2.​​ числовые значения в​В формате ячейки​​ округляется в большую​ Павел, видимо АСКУЭ​​ 123.455 Спасибо.​ до кратного числа,​​Округление до сотен в​​ Получилось так.​​ числа, до десятков,​ выделить столбец, не​

​Финансовый​​ меньшего числа десятичных​26514303​

​Function GetNotNumericFromLeft (t As​​ соединять в тестовый​ Числовой и, ставь​

​Если при вводе с​​ ячейках. Они остаются​ устанавливаешь число десятичных​​ сторону, если меньше​ (судя по словам​vikttur​ смотрите в статье​​ Excel​Но, если мы ещё​ сотен​ смежные ячейки, всю​,​ разрядов, не содержит​3542301​ Range)​ формат…​ количество знаков после​ автоматическим режимом вставки​​ те же и​ знаков 2 и​ – то в​ «а система учёта​: Просто обрезать:​ „Сумма чисел кратных​по такой формуле​ в эту ячейку​, т.д.​ таблицу, смотрите в​​Процентный​ фактическое количество.​1871611​Dim j As​Guest​ запятой!​ разделителя вы ввели​ реальными отображаются в​ получаем 11278,62.​​ меньшую. При этом​ генерирует“). Ради чего​=ПСТР(D12;1;НАЙТИ(„,“;D12)+3)*1​ 3 в Excel».​ =ОКРВВЕРХ(C15;100), т.д.​ с формулой округления​Первый вариант.​ статье «Как выделить​или​​Если, например, введите число​87436109​ Integer​: Если вы хотите​

​Webrater​​ запятую, то она​ строке формул.​​Надеюсь расписал проблему​ разница между не​ тратили деньги на​Если не домножать​Как умножить время​Другие способы округления​

​ установим формат ячейки​​Округление в Excel до​ в Excel ячейки,​Экспоненциальный​ в ячейке с​Т.е. необходимо в​

​For j =​​ вообще убрать десятичную​: количество знаков убирает,​ останется в том​​При суммировании большого количества​ разумно.​ округленным значением и​ точность в системе?​​ на единицу, будет​ на число (деньги),​​ вверх, до определенной​ с одной цифрой​ целого числа.​ таблицу, др.».​.​главной​ каждой ячейке удалить​ 1 To Len (t)​ часть, то есть​ но считает все​ месте, где вы​ таких округлений ошибки​AndreTM​ округленным прибавляется к​ Чтобы потом отбрасывать​ текстовое значение.​ например, часы работы​ цифры, т.д., смотрите​ после запятой, то​​Как убрать числа​Второй способ​В диалоговом окне​форматирование (по умолчанию​ две последнин цифры.​If Not IsNumeric (Mid (t,​ что бы по​​ равно с большим​ ее вводили.​ могут быть очень​: Похоже, просто форматом​​ результату измерения на​ лишние знаки?​Serge​ на стоимость одного​​ в статье «Округление​ число округлится до​ после запятой форматом,​.​десятичных знаков​ для всех ячеек​Z​ j, 1)) Then​

​ дефолту вводились целые​​ хвостом.​То есть количество знаков​ большими. Поэтому при​ вы не отбросите​

​ следующем часовом интервале​​Пусть сбыт покажет​: Просто обрезать 2:​ часа, смотрите в​​ Excel в большую​ одной цифры после​​ смотрите в статье​Как отменить округление в​введите количество запятой,​ на новый лист),​

​: Прокрустово ложе только​​If Mid (t, j,​ числа попробуйте след​Тельмина федосеева​​ после запятой можно​ расчетах и вычислениях​​ знаки числа…​ с сохранением знака​ Вам документ, на​=ЛЕВСИМВ(A1;ДЛСТР(A1) -1)​ статье „Как умножить​ сторону“.​ запятой.​ «Число Excel. Формат».​​Excel​

planetaexcel.ru>

Оператор Round

А вы знаете, что при использовании для округления чисел в VBA Excel оператора Round, вы можете получить совершенно не тот результат, который ожидали? И ведь это действительно так!

Скопируйте в модуль VBA следующую процедуру и запустите ее выполнение:

1 2 3 4 5 6 7 8 9 10 11

SubTest_1() Dima1 AsSingle,a2 AsSingle,a3 AsSingle,a4 AsSingle a1=Round(1.5,) a2=Round(2.5,) a3=Round(3.5,) a4=Round(4.5,) MsgBox»Round(1.5, 0)=»&a1&vbNewLine&_ «Round(2.5, 0)=»&a2&vbNewLine&_ «Round(3.5, 0)=»&a3&vbNewLine&_ «Round(4.5, 0)=»&a4 EndSub

В результате вы получите это:

Удивительно, не правда ли? Как же так получилось?
Дело в том, что оператор Round осуществляет «бухгалтерское» (или «банковское») округление, которое призвано при большом количестве таких операций свести погрешность к минимуму. Это достигается за счет того, что оператор Round использует при округлении правило, отличное от того, которое мы знаем еще со школы, когда округляемое число увеличивается на единицу, если отбрасываемое число равно пяти. Суть округления с помощью оператора Round состоит в том, что если перед отбрасываемой пятеркой стоит нечетная цифра, то она увеличивается на единицу (округление вверх), а если перед ней стоит четная цифра, то она не увеличивается (округление вниз).

Еще можно сформулировать «бухгалтерское» округление так: при отбрасывании пятерки число округляется к ближайшему четному

Обратите внимание, что в результатах нашего примера все полученные числа — четные.
Проверим погрешность:

1. Сумма исходных чисел: 1.5 + 2.5 + 3.5 +4.5 = 12
2. Сумма округленных чисел: 2 + 2 + 4 + 4 = 12

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