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

Работа с таблицами Excel

Округление результатов вычислений

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

Рис. 2.28. Панель функции «ОКРУГЛ»

Данная функция округляет число до указанного количества десятичных разрядов. Синтаксис ее следующий:

ОКРУГЛ(число; количество_цифр)

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

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

=ОКРУГЛ(В10/\(1/С10);2)

Первый аргумент, В10/\(1/С10), показывает, для какой операции выполняется округление, а второй (цифра 2) определяет количество десятичных разрядов. В нашем случае вычисления осуществляются с точностью до второго знака после запятой (например, до копеек). Однако округление необходимо производить на определенном этапе расчетов.

ПРИМЕР: На таможню приходит товар стоимостью 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.

Таблица 2.1. Пример неправильного расчета таможенных платежей

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

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

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

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

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

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

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

Таблица 2.2. Пример правильного расчета таможенных платежей и стоимости товара

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

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

Ошибки Excel при округлении и введении данных в ячейки

​Как правильно написать в​Здесь оставили две цифры​«ВЕЙБУЛЛ»​ большую, при увеличении​ производить тремя методами.​ ящиках по 18​ является число -880.​Число десятичных знаков​ формула и автоматически​В ячейке А1 реализуем​ будет целый столбец,​

​ так.​В этой таблице видно,​ настроить такое округление​Копируем формулу вниз​Функция «НЕЧЁТ»​ до десятков.​ строке «Число_разрядов»​ после запятой (форматом​- возврат распределения​ ячейки, так и​Используя стандартный знак сложения​ единиц. С помощью​ Сначала -889 преобразуется​

Ошибки при округлении дробных чисел

​введите требуемое число​ выполнен расчет. Но​ первый способ, а​

​ то для быстрого​Еще примеры разного​ что у некоторых​ – числа до​ по столбцу. Какими​–​Округление до сотен в​.​ ячейки). Получилось так.​

​ Вейбулла.​ в меньшую, при​ – плюс («+»).​ функции ОКРУГЛТ можно​

​ в абсолютное значение​ знаков после запятой.​ что если нам​ в А2 –​ получения точных расчетов​ не стандартного округления​ товаров цена со​ 3 в меньшую​

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

​ имеет​Как видите, округление в​ методом пользуются при​ потребуется для поставки​ значение округляется до​ некоторых случаях может​ именно таким способом​Задание 1. В ячейку​ функций. Для этого​ смотрите в статье​ себестоимости. Нам нужно​ 3 в большую​ смотрите в статье​.​ =ОКРВВЕРХ(C15;100), т.д.​ запятой будет стоять​ нули после запятой,​

​финансовые​ Excel не такая​ сложении небольшого количества​ 204 единиц товара.​ двух значимых разрядов​ потребоваться использовать функции​ и мы не​ А1 введите слово​ мы введем такую​ «Округление Excel в​ у этого товара​ сторону. Например, так.​

​ «Копирование в Excel»​Функция «ОТБР»​Другие способы округления​ одна цифра.​

Пишем в Excel слово ИСТИНА или ЛОЖЬ как текст

1. ​ (880). После этого​
2. ​ ЧЁТН и НЕЧЁТ​ желаем вычислять результат?​

​ с форматирующим символом​ формулу: =СУММ(ОКРУГЛ(A1:A7;0)). После​ большую сторону».​ поднять цену скидки​

​В ячейке В28 написали​ тут.​–​ вверх, до определенной​Если поставим цифру​ ячейке оставили две​ссылок и массивов​ задача.​ а также внутри​ ответом является 12,​ повторно применяется знак​ для округления вверх​ Решить данную задачу​ так: «’истина». Обязательно​ ввода массива функций​Excel старается облегчить наш​ до цены себестоимости.​

​ такую формулу. =ОКРУГЛВВЕРХ(A28-0,3;)​Получится так.​округляет до целого числа,​ цифры, т.д., смотрите​ 2, то после​ цифры после запятой.​, функции​«ПРОПНАЧ», «ДЛСТР»​ формул для сложения​ так как число​ «минус», что дает​ до ближайшего четного​ можно аналогичным способом​ следует поставить в​ следует нажимать не​

​ труд разными подсказками​В ячейке Е2​Еще способы округления​Второй вариант​ отбрасывая все цифры​

​ в статье «Округление​ запятой будут стоять​ Получилось так.​

​даты и времени, проверка​- текстовые функции,​ вычислений других функций.​ 204 при делении​ в результате -880.​ или нечетного числа.​ из примера описанного​ начале слова символ​ «Enter», а комбинацию​

Отображение формул

​ и авто-подстановками. Если​ пишем такую формулу.​ смотрите в статье​

​.​ после запятой.​ Excel в большую​ две цифры, т.д.​Второй вариант.​ назначений и свойств.​ которые позволяют изменить​Используя ввод функции «СУММ»​ на 18 дает​При применении к положительному​Используйте функцию ОКРУГЛВНИЗ.​ выше.​ апострофа «’», который​ клавиш Ctrl+Shifi+Enter. В​

exceltable.com>

​ все это выполняется​

• Excel сумма в непустых ячеек
• В excel не работает сумма
• Округление в excel в большую сторону до целого
• Округление в excel в большую сторону
• Excel сумма по условию
• Excel посчитать сумму времени в excel
• Округление в excel до десятков
• Формула в excel сумма если условие
• Округление в excel до 50
• В excel формула если сумма
• Как установить формулу суммы в excel
• Формула в excel для округления

Как округлить с помощью функции Excel?

А теперь перейдем непосредственно к практике. Что нужно делать, чтобы осуществить округление чисел с помощью функции? Для этого существует специальная функция ОКРУГЛ. Ее можно вызвать разными способами: через ленту в Excel 2007 версии и более новые.

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

Наиболее простой для новичка метод – воспользоваться мастером функций. Чтобы это сделать, нужно найти возле строки ввода формулы кнопочку, на которой написана комбинация маленьких буковок fx. Найти эту функцию можно в разделе «Математические», и после ее выбора будет предложено ввести аргументы. Каждый из них подписан, поэтому разобраться несложно.

Синтаксис функции ОКРУГЛ

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

В случае с функцией ОКРУГЛ их два. Давайте рассмотрим их более подробно.

Аргументы функции ОКРУГЛ

Итак, функция предусматривает два аргумента:

1. Число. Сюда указывается ссылка на ячейку. Или же можно ввести требуемое значение в этот аргумент вручную.

2. Количество знаков, на какое собираетесь осуществить округление.

Чтобы округлить целое число (то есть, которое не имеет ни одного знака после запятой), просто напишите знак минуса перед числом во втором параметре. Для округления до десятков, необходимо написать -1, до сотен – -2, и по такой логике далее. Чем больше модуль этого числа, тем на большее количество цифр будет осуществляться округление. 

Основы использования функции ОКРУГЛ

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

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

4

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

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

5

По кратности

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

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

По количеству знаков

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

Способы округления чисел

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

Если используется стандартная библиотека math, то в начале кода её необходимо подключить. Сделать это можно, например, с помощью инструкции: .

math.ceil() — округление чисел в большую сторону

Функция получила своё имя от термина «ceiling», который используется в математике для описания числа, которое больше или равно заданному.

Любая дробь находится в целочисленном интервале, например, 1.2 лежит между 1 и 2. Функция определяет, какая из границ интервала наибольшая и записывает её в результат округления.

Пример:

math.ceil(5.15) # = 6
math.ceil(6.666) # = 7
math.ceil(5) # = 5

Важно помнить, что функция определяет наибольшее число с учётом знака. То есть результатом округления числа -0.9 будет 0, а не -1.

math.floor() — округление чисел в меньшую сторону

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

Пример:

math.floor(7.9) # = 7
math.floor(9.999) # = 9
math.floor(-6.1) # = -7

math.trunc() — отбрасывание дробной части

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

Примеры:

math.trunc(5.51) # = 5
math.trunc(-6.99) # = -6

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

Примеры:

int(5.51) # = 5
int(-6.99) # = -6

Нормальное округление

Python позволяет реализовать нормальное арифметическое округление, использовав функцию преобразования к типу int.

И хотя работает по другому алгоритму, результат её использования для положительных чисел полностью аналогичен выводу функции floor(), которая округляет числа «вниз». Для отрицательных аналогичен функции ceil().

Примеры:

math.floor(9.999) # = 9
int(9.999) # = 9
math.ceil(-9.999) # = -9
int(-9.999) # = -9

Чтобы с помощью функции int() округлить число по математическим правилам, необходимо добавить к нему 0.5, если оно положительное, и -0.5, если оно отрицательное.

Тогда операция принимает такой вид: int(num + (0.5 if num > 0 else -0.5)). Чтобы каждый раз не писать условие, удобно сделать отдельную функцию:

def int_r(num):
    num = int(num + (0.5 if num > 0 else -0.5))
    return num

Функция работает также, как стандартная функция округление во второй версии Python (арифметическое округление).

Примеры:

int_r(11.5) # = 12
int_r(11.4) # = 11
int_r(-0.991) # = -1
int_r(1.391) # = 1

round() — округление чисел

round() — стандартная функция округления в языке Python. Она не всегда работает так, как ожидается, а её алгоритм различается в разных версиях Python.

В Python 2

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

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

Помимо этого, могут быть неточности, например, если округлить число 2.675 до второго знака, получится число 2.67 вместо 2.68. Это происходит из-за невозможности точно представить десятичные числа типа «float» в двоичном коде.

В Python 3

В третьей версии Python используется банковское округление. Это значит, что округление происходит до самого близкого чётного.

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

Примеры:

round(3.5) # = 4
round(9.5) # = 10
round(6.5) # = 6
round(-6.5) # = -6
round(-7.5) # = -8

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

Округление до сотых

У функции есть ещё один аргумент. Он показывает до какого количества знаков после запятой следует округлять. Таким образом, если нам надо в Python округлить до сотых, этому параметру следует задать значение 2.

Пример округления до нужного знака:

round(3.555, 2) # = 3.56
round(9.515,1) # = 9.5
round(6.657,2) # = 6.66

Функция ОКРУГЛ в Excel

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

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

число или ссылка на округляемое число – исходное число для округления;

количество оставляемых знаков – количество знаков после запятой.

Например, формула округления в Excel до двух знаков после запятой выглядит так. 

Чтобы получить формулу округления в Excel до целого числа, во втором аргументе нужно поставить 0.

Встречаются и более сложные ситуации. Как, например, округлить до тысяч (три последние цифры являются нулями)? Многие поступают так. Делят число на 1 000, округляют до целого и затем снова умножают на 1 000. Это не лучший способ.

Функция ОКРУГЛ умеет округлять не только после запятой, но и до запятой, т.е. до тысяч, миллионов и т.д. Например, чтобы число 123456789 округлить до тысяч и получить 123457000, точность указывается с минусом.

На этом формулы округления в Эксель не заканчиваются.

Установка точности расчетов

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

Для начала посчитаем сумму значений во всех наших ячейках.

1. Для этого добавим новое поле и перейдем в него. Затем кликнем на иконку «Fx» (вызов окна вставки функции).

1. В появившемся окне выберите категорию «Математические».

1. Найдите в списке функцию «СУММ». Для вставки нажмите на кнопку «OK».

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

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

1. Как только вы отпустите палец, всё вернется обратно. При этом в поле «Число 1» будет указан нужный диапазон, а не то, что стояло по умолчанию. Для сохранения данных нажмите на кнопку «OK».

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

Можете проверить при помощи калькулятора. У вас будут расхождения.

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

Для того чтобы в формуле «СУММ» происходил расчет того, что отображается, нужно сделать следующие действия.

1. Нажмите на пункт меню «Файл».

1. Перейдите в раздел «Параметры».

1. Кликните на пункт «Дополнительно». После этого прокрутите окно практически до самого низа.

1. Найдите группу настроек «При пересчете этой книги». Поставьте галочку около пункта «Задать указанную точность» (если в будущем вам эта настройка окажется ненужной, достаточно будет убрать эту галочку).

1. Кликните на кнопку «OK». Сразу после этого вы увидите предупреждение, в котором вам сообщат, что данные в таблице будут изменены в меньшую сторону. Необходимо согласиться с этим условием нажатием на «OK».

1. Только после этого вы сможете сохранить внесенные изменения.

1. Снова посмотрите результат подсчетов. Теперь он изменился и соответствует сумме отображаемых чисел.

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

Как округлять данные в формуле до 50-ти (Как округлять данные в формуле до 50- ти)

​»ко всему столбцу»:​​ прикрепляю. Заранее спасибо.​​Надо бы сразу​ и разделить на​ подсобите​Serge_007​​:​ 15 значащих цифр​ на рисунке ниже).​MROUND​ ячейке число «100».​ Excel» здесь.​Какими способами округлить​​(Пример: 12610 то округляется​

​:​​ только «конкретное число»,​​ ввести формулу в​

​swhummer​​ до целого руля​

​Условие:​​: А на каждый​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОКРУГЛ(A2*2;-2)/2​​ потребуется ввести его​ Такое округление происходит​(ОКРУГЛТ), но округляет​​В ячейке С2​​Получилось так.​​ в Excel числа,​​ до 12600; если​Очень извиняюсь, но​ но и ссылку​ помощь)​​ первую ячейку нужного​: Оказывается все просто,​ округлять​​с точностью до​формирование прайс-листов магазина.​ гостевой пост вводить​Спасибо большое​​ в текстовом формате.​

​ без уведомления пользователя​​ до большего кратного​ напишем такую формулу.​Второй пример​

​ смотрите в статье​​ 12630, то округление​

​ на ячейку.​​vikttur​ диапазона (например. в​

​Цены должны быть​​ капчу лучше что-ли,​: Спасибо большое!!!!​На рисунке ниже можно​ и поэтому может​ числа.​ =ЕСЛИ(B250;100;0)))​

​.​​ «Округление в Excel».​ 12650; если 12680,​ к вашей помощи)​

​если использовать правильный​​: Z показал Вам​ соседнем с исходным​ использовал.​ счету ( чтобы​

​ на 10, округлить​​ фиксированные​ чем сделать это​Гость​

​ служить причиной появления​​=CEILING(A1,10)​Скопируем формулу вниз​Округлить в​

​ Здесь разберем конкретные​​ то 12700)​

​ все получилось, формулу​​ тип ссылок (они​ формулу,​ столбце), протянуть вниз.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=округлт(ячейка;100)​

​ не мелочиться и​​ до ближайшего целого​

​диапазон **01-**50 должен​​ один раз при​: А какая формулы​​ выполняет вычисления с​​ ошибки.​​=ОКРВВЕРХ(A1;10)​ по столбцу. Получится​​Excel​ примеры использования функций​​Алексей матевосов (alexm)​ скопировала, числа появились​​ бывают относительные и​ikki​vikttur​Тему можно закрыть.​ убытка не было)​​ числа и умножить​ округляться до **50​

​ регистрации?​​ будут, если числа​​ большей точностью, чем​​Примечание​Работает так же, как​ так.​числа до, не менее​ округления. Можно написать​

excelworld.ru>

Округление и точность до 15 значащих цифр в MS EXCEL

​я давно стесняюсь​Дальше — по​: вот пример. здесь​ikki​

​ цело значение например​ коп округляются в​: с точностью до​ilmatrust​ я​:​ характерную величину).​

​ числа до 15​​ ближайшего кратного 5.​​ поставить в ячейке​ Какими способами быстро​ до целого числа,​ их значения.​ Вас спросить: а​ шагам суперинструкции​ 2003word, но я​: округлт(1201;50) не прокатит,​ 1500 то так​ 0 и только​ 0,5 – умножить​

​: Вы правы!​​Пока зарегистрировалась, с​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОКРУГЛ(A1/50;0)*50​СОВЕТ:​ значащих цифр, последние​=MROUND(A1,5)​ число «50»;​​ скопировать формулу по​​ до десятков, тысяч,​

​Varia​ Вы ответы других​ikki​ пользуюсь 2007.​ ага?​ его и оставлять.​ у вас до​ на 2, округлить​Pajta4ek​ вашими кодами безопасности​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОКРУГЛТ(A1;50)​Чтобы отобразить число​

​ цифры заменяются нулями​​=ОКРУГЛТ(A1;5)​числа больше 50​ столбцу, смотрите в​ до определенного числа​: Огромное спасибо! Вы​

​ форумчан читаете?​​: в формулах Excel​Нужно весь столбец​=ОКРВВЕРХ(A1;50)​ Файл с примером​ 50 !​

Лирическое отступление

​ до целого числа​: А мне еще​ намучилась))…​_Boroda_​ с точностью более​

​ (см. Строку формул​Работает так же, как​ – поставить в​ статье «Копирование в​, т.д.​ меня спасли))​Varia​ можно использовать не​ округлить.​

excel2.ru>