Заявление, сделанное подписчиком ExcelTips (Чак) в ответ на вопрос «Требуется помощь», вызвало ряд интересных комментариев со стороны многих других подписчиков ExcelTips. Чак заявил, что, по статистике, значение 0,5 следует округлять в большую половину времени и в меньшую половину времени, потому что оно находится точно посередине двух целых значений. Приводилась аналогия: если теннисный мяч балансировал на сетке, по статистике мяч должен падать в левой половине времени и вправо в другой половине.

Похоже, что такие заявления вызывают сильные чувства даже у других статистиков. (Кажется, что у всех дисциплин есть свои различные религиозные войны, где чувства накаляются.) Как заметил один корреспондент, это «проблема старых« заборов против столбов »при подсчете интервалов между числами». Спор заключается в том, куда что-то «упадет», когда оно находится прямо на столбике забора. Проблема с аналогией с теннисным мячом и сеткой (или ограждением и столбами) состоит в том, что сетка в центре корта — не единственная точная разделительная линия.

Например, предположим, что на левом конце теннисного корта есть линия, обозначенная «4.0», а на другом конце — линия, отмеченная «5.0». Это означает, что сеть имеет отметку «4.5». В то время как теннисный мяч может балансировать на отметке 4,5 и падать в любую сторону, теоретически мяч также может балансировать на линии на любом конце корта (4,0 и 5,0) и падать в любую сторону от них.

Один корреспондент выразил мнение, что округление 0,5 в большую или меньшую сторону (наполовину в одну сторону и половина в другую) неуместно, поскольку оно вносит систематическую ошибку в данные. Рассмотрим ситуацию, когда вы имеете дело с одной цифрой справа от десятичной точки: у вас есть числа 7.0, 7.1, 7.2 и т. Д., Вплоть до 7.9. При округлении этих цифр пять значений всегда будут округляться в меньшую сторону (от 7,0 до 7,4), одно значение может округляться в любую сторону (7,5), а четыре значения всегда будут округляться в большую сторону (от 7,6 до 7,9). Другими словами, со временем значения 5,5 округляются в меньшую сторону, а значения 4,5 — в большую. При истинно равномерном применении статистической вероятности 5 значений должны округляться в меньшую сторону, а 5 в большую, но «колебание» центрального значения (7.5) дает смещение в пользу округления в меньшую сторону и против округления в большую сторону.

Итак, какая теория округления верна? Следует ли округлять 7,5 в половине случаев и в половине случаев в меньшую сторону или всегда следует округлять в большую сторону? Microsoft явно решила, что всегда округляет до 7,5 (теннисный мяч всегда падает вправо для положительных значений и влево для отрицательных значений). Означает ли решение Microsoft, что всегда округлять 0,5 в большую сторону? Ваш ответ определит ваша позиция в разворачивающейся религиозной войне.

Что ж, возможно, вам помогут другие данные. Похоже, что на весь этот вопрос существует стандарт ANSI. Один подписчик указал, что он всегда следовал стандартам ASTM E29 и ANSI Z25.1, оба из которых определяют, что точное дробное значение 0,5 следует округлять до ближайшего числа, заканчивающегося четной цифрой. Если вам нужно выполнить этот тип округления, то правильная формула для использования следующая:

=IF(A1-INT(A1)-0.5,EVEN(ROUNDDOWN(A1,0)),ROUND(A1,0))

Чтобы увидеть, как это может повлиять на результат округления, я сгенерировал серию из 25 000 случайных чисел от 1 до 100, где каждый результат имел до двух десятичных знаков. Затем я округлил значения до целого значения, используя обычную функцию ОКРУГЛ в одном столбце, а в следующем столбце я округлил числа, используя приведенную выше формулу. Затем я просуммировал каждый столбец, чтобы увидеть, какой метод округления дал результаты ближе к исходной сумме. В моем тесте результаты были более чем на 50% ближе к исходной сумме при использовании приведенной выше формулы, а не только с помощью функции ROUND в Excel.

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

Одна вещь, которую я заметил в своем тестировании, заключалась в том, что в первом наборе тестовых данных (случайные числа с двумя десятичными знаками) было 234 значения, которые точно соответствовали критериям точности 0,5 (и, таким образом, подходили для округления в большую или меньшую сторону). ). В списке с тремя десятичными знаками количество совпадений упало до 14 значений, с четырьмя десятичными знаками было 2 значения, а с пятью десятичными знаками было 0 значений. Само собой разумеется, что чем меньше значений соответствует критерию окончания на .5, тем меньше необходимости применять «округление вверх или вниз»

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

Разумеется, любое обсуждение округления должно предполагать, что вы округляете необработанные значения, а не ранее округленные значения. Например, если исходное значение составляет 14,46, и вы округлите его до 14,5, было бы неправильным позже округлять 14,5 до 15. Правильная процедура — проверить исходное значение 14,46, которое следует округлить до 14. Таким образом, вы всегда следует использовать ОКРУГЛ как один из последних шагов в работе с числами, а не как один из первых. Это означает, что при использовании агрегатных функций, таких как SUM или AVERAGE, вы не должны применять их к значениям, которые уже были округлены. Вместо этого вы должны СУММАТЬ или СРЕДНЕЕ необработанные значения, а затем округлить СУММУ или СРЕДНЕЕ. Вы получите более точные результаты, запомнив этот совет.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2829) применим к Microsoft Excel 2007, 2010, 2013 и 2016.