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

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

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

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

В зависимости типа подсчетов функции округления в Excel могут работать неправильно, но это легко исправить. Источник - Wikipedia В зависимости типа подсчетов функции округления в Excel могут работать неправильно, но это легко исправить. Источник — Wikipedia Округление значений и связанные с этим ошибки в вычислениях зачастую имеют место при повседневной работе в Excel. Несмотря на небольшую разницу, неприятно, когда в общем итоге не хватает нескольких копеек или сумма процентов в колонке составляет 101. При финансовых расчетах важно округлять до правильного знака после запятой. К примеру, если цена нетто составляет 1,99 рубля или гривны, а стоимость брутто рассчитывается как 2,3681, то общая цена ста единиц товара никогда не будет равняться 236,81 рубля (гривны). Все иначе с расценками на бензин: здесь уже имеют значение три последние цифры.

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

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

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

Стандартное «ОКРУГЛ» в Excel использует математическое округление, то есть округляет числа, заканчивающиеся на «5», в большую сторону. В некоторых случаях это недопустимо — например, в бухгалтерии при суммировании округляемых цифр. Избежать ошибок позволит созданная в VBA функция с гауссовым округлением — до ближайшего четного числа. Для ее программирования с именем «MATHROUND» откройте редактор VBA комбинацией клавиш «Alt+F11». Введите следующий код: Function•MathRound(ByVal•X•As•Double, Optional•Factor•As•Long•=•0)
MathRound •=•Round(X,•Factor)
End•Function Закройте редактор VBA. Теперь в таблице можно будет использовать для гауссова («банковского») округления вместо «ОКРУГЛ» функцию «MATHROUND».

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

1. НАСТРАИВАЕМ ФОРМАТ Для наглядности расчетов вы используете значения, округленные до двух знаков после запятой. Зачастую настройка числового формата подходит лучше, чем преждевременное применение функции округления.

2. ПРЕДСТАВЛЕНИЕ ОКРУГЛЕНИЯ Для этого используйте быстрое форматирование на вкладке «Главная» в разделе «Число». Кликните по стрелке в нижней части раздела и в открывшемся окне на вкладке «Число» задайте число знаков после запятой для параметров «Числовой»,  «Денежный», «Финансовый» или «Процентный».

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

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

5. ДРУГАЯ ОСНОВА Для округления на другой основе, к примеру, 5 копеек, используйте вычисление или — для положительных значений — сразу функцию «=ОКРУГЛ(число; 0,05)».

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

7. ГАУССОВО ОКРУГЛЕНИЕ Функция «ОКРУГЛ» использует математическое округление, и результаты зачастую искажены. Гауссово («банковское») округление «MATHROUND» из VBA предотвратит это.

8. НЕРАВНЫЕ ПАРЫ Одинаковые значения вследствие неточностей вычисления и округления иногда выглядят разными. Желаемых результатов можно добиться с помощью правильного округления.

Теги microsoft excel