000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как округлить / округлить вверх / вниз диапазон номеров ячеек в Excel?

Как запретить Excel округлять числа (десятичные / дробные)

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

В этом руководстве я расскажу вам, как запретить Excel округлять цифры.

Принципы округления в Excel

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

В зависимости от выбранного метода принципы округления различаются. В основном используется стандартный метод: числа от 5 до 9 округляются в большую сторону, а числа от 1 до 4 — в меньшую. При округлении в Excel до целого числа, например, 1,4 будет 1, а 1,5 — 2. В отличие от этого, специальные функции позволяют округлять цифры и знаки, а также определять, являются ли числа целыми или дробными.

Правильно округляем в Microsoft Excel

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

В финансовых расчетах важно округлять до правильного десятичного знака. Например, если цена нетто составляет 1,99 рубля или гривны, а цена брутто рассчитывается как 2,3681, то общая цена за сто единиц товара никогда не будет равна 236,81 рубля (гривны). Иное дело цены на бензин: здесь последние три цифры уже что-то значат.

Читайте так же:
Как объединить тексты из нескольких ячеек в текстовое поле в Excel?

Разнообразие функций программы

В Excel существует 15 различных функций округления: «ОКРУГЛ», «ОКРУГЛВЕРХ», «ОКРУГЛВНЗ», «ОКРУГЛВНЗ», «ОКРУГЛВНЗ. ИСПРАВЛЕНО», «OKRUGLEVNYZ. ИСПРАВЛЕНО», «ОКРУГЛ. РУБЛЬ» и «ФИКСИРОВАН». Лучше использовать привычную функцию с расчетом или правильным параметром, чем одну специальную функцию. Проблемы вызывают функции «ОКРУГЛВЕРХ» и «ОКРУГЛВНИЗ», которые дают неочевидные результаты при использовании отрицательных чисел. Например, «=КРУГЛВЕРХ (-2,5;0)» дает «-3» вместо «-2». В таких случаях рекомендуется использовать функции «OKRIGHT» и «OKRNYZ».

Второе число является параметром. Однако в нем указывается не количество цифр после десятичной точки, а кратность, необходимая для округления. Если имеется два десятичных знака, используйте значение, кратное «0,01». У вас есть возможность округлить до других кратных значений. База пени за просроченный налог округляется до ближайшего кратного числа рублей (гривен) и выше. Аналогичные результаты можно получить с помощью функции OKRUGLT.

Математическое округление

По умолчанию Excel округляет числа, заканчивающиеся на «5», в большую сторону, используя математическое округление. Это неприемлемо в некоторых случаях — например, в бухгалтерии при сложении округленных чисел. Чтобы избежать ошибок, используйте гауссово округление в VBA. Откройте редактор VBA, нажав комбинацию клавиш Alt+F11. Это запрограммирует его с именем «MATHROUND». Введите следующий код:

Function•MathRound(ByVal•X•As•Double, Optional•Factor•As•Long•=•0)
MathRound •=•Round(X,•Factor)
End•Function

Закройте редактор VBA. Теперь в таблице для гауссовского («банковского») округления вместо «OK» будет доступна функция «MATHROUND».

Как это сделать?

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

Для этого можно использовать Быстрое форматирование на вкладке Главная в разделе Числа. Нажмите стрелку в нижней части раздела и в открывшемся окне на вкладке Numeric установите количество знаков после запятой для параметров Numeric, Monetary, Financial или Interest Rate и установите количество знаков после запятой для параметров Numeric, Monetary, Financial или Interest Rate.

Читайте так же:
Как найти совпадающее значение поиском вверх в Excel?

3. ОПАСНО Откройте Файл | Настройки и выберите категорию Дополнительно. Убедитесь, что в разделе «При пересчете этой книги» опция «Установить точность как на экране» отключена.

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

5. Если вы хотите округлить на другой основе, например, 5 копеек, используйте вычисление сразу или, для положительных значений, функцию «=CROLL(число; 0,05)».

6. ПРОБЛЕМА С НЕПРАВИЛЬНЫМИ СУММАМИ Excel округляет суммы в большую сторону чаще, чем в меньшую. Чтобы избежать бухгалтерских ошибок, вычисляйте последнюю часть суммы как разницу с итогом.

7. В функции «MATHROUND» используется метод гауссова округления, что в некоторых случаях приводит к искажению результатов. Функция математического округления VBA «MATHROUND» предотвратит это.

8. Неточности вычислений и округления могут привести к различиям во внешнем виде одних и тех же величин. Округление приводит к желаемым результатам.

Как в расчёте процентов в Excel получить больше знаков после запятой

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

Результат является неверным из-за грубого округления. Чтобы изменить количество знаков после запятой и увеличить точность при вычислении процентов в Excel, кликните правой кнопкой мыши по ячейке, и в контекстном меню выберите там пункт «Формат ячеек». Установите «Процентный» числовой формат и увеличьте количество «Число десятичных знаков»:

В результате значение будет гораздо более точным:

Выводы

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

Читайте так же:
Как найти наиболее часто встречающееся текстовое значение в списке в таблицах Google?

На рисунках в этой статье вы можете увидеть оператор ROUND, который соответствует оператору в английской версии «Офиса».

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector