Округление в Excel: ОКРУГЛ и формат ячейки
Два разных округления, которые постоянно путают: одно меняет число, другое — только его вид. Отсюда и берётся «сумма не сходится на копейку».
Коротко
В экселе два округления, и они делают разное.
Функция ОКРУГЛ меняет само число. =ОКРУГЛ(A1;2) превращает 12,3456 в 12,35 — по-настоящему: дальше складывается уже 12,35.
Формат ячейки меняет только вид. Кнопки «увеличить и уменьшить разрядность» и «Формат ячеек → Числовой» показывают 12,35, а в ячейке по-прежнему лежит 12,3456. Складываются полные числа.
Отсюда самая частая жалоба: «итог не сходится на копейку». На экране 12,35 + 12,35 = 24,70, а Excel показывает 24,69 — потому что на самом деле сложились 12,3456 и 12,3444. Лечится не форматом, а функцией: округлять надо там, где число рождается, а не там, где показывается.
Отдельная пара, которую путают с первой: ОКРУГЛВВЕРХ округляет до разряда, а ОКРВВЕРХ — до кратного. =ОКРУГЛВВЕРХ(1237;-2) даст 1300, а =ОКРВВЕРХ(1237;50) — 1250. Первое нужно в отчётах, второе — в ценниках и фасовке.
Второй аргумент ОКРУГЛ — до какого знака: 2 — до копеек, 0 — до целых, −3 — до тысяч. Отрицательные знаки нужны реже, но именно они округляют суммы до тысяч в отчётах.
То же самое формулой в Excel
=ОКРУГЛ(A1;2) — до копеек: 12,3456 → 12,35=ОКРУГЛ(A1;0) — до целых: 12,5 → 13, а 12,4 → 12=ОКРУГЛ(A1;-3) — до тысяч: 12 345 → 12 000=ОКРУГЛВВЕРХ(A1;0) — всегда вверх: 12,1 → 13. Считать упаковки и рейсы=ОКРУГЛВНИЗ(A1;0) — всегда вниз: 12,9 → 12=ЦЕЛОЕ(A1) — отбросить дробную часть; у отрицательных ведёт себя иначе, чем ОКРУГЛВНИЗ=ОКРУГЛТ(A1;0,5) — до ближайшего кратного: цены к 0,5 или 10=ОКРВВЕРХ(A1;50) — вверх до кратного 50: ценники и фасовка=ОКРВНИЗ(A1;50) — вниз до кратного 50=ОКРУГЛ(A1*B1;2) — округлять произведение, а не сомножители — иначе копейки разъедутсяФайл, где округление видно на живых числах
Восемь строк товаров: сумма, НДС и доля считаются формулами. Поменяйте разрядность в формате и сравните с ОКРУГЛ — разница видна сразу.
Файл
pervaya-formula.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Решите, что вам нужно: изменить число или только его вид. Для документов и расчётов почти всегда первое.
- Изменить число: в соседней ячейке напишите
=ОКРУГЛ(, кликните исходную, поставьте точку с запятой и число знаков. Для копеек — 2. - Чаще всего округление встраивают прямо в расчёт:
=ОКРУГЛ(B5*C5;2). Так копейки не накапливаются в итогах. - Изменить только вид: выделите ячейки и нажимайте кнопки «Увеличить разрядность» и «Уменьшить разрядность» на вкладке «Главная» — или Ctrl+1 и задайте формат вручную.
- Проверьте себя: поставьте рядом
=A1-ОКРУГЛ(A1;2). Если получится не ноль, в ячейке лежит не то, что видно.
Где обычно ломается
- Итог не сходится на копейку. Складываются полные числа, а показываются округлённые. Округляйте функцией в самом расчёте, а не форматом при показе.
- Настройка «Задать точность как на экране». В параметрах Excel есть галочка, которая округляет все числа книги до отображаемых. Она решает проблему один раз и необратимо: полные значения теряются навсегда. Пользоваться ею не стоит.
- ЦЕЛОЕ и ОКРУГЛВНИЗ расходятся на отрицательных.
=ЦЕЛОЕ(-12,4)даёт −13 (округляет вниз, то есть в меньшую сторону), а=ОКРУГЛВНИЗ(-12,4;0)даёт −12 (отбрасывает дробь). На возвратах и корректировках это разные деньги. - Округление половин. Excel округляет 0,5 вверх — 12,5 → 13. Бухгалтерское «к ближайшему чётному» он не делает; если оно нужно, придётся собирать формулой.
- ОКРУГЛ внутри промежуточных шагов. Если округлить каждый сомножитель, а потом перемножить, результат разойдётся с округлением произведения. Округляют один раз — в конце.
- Округление не помогает от ошибки представления. 0,1 + 0,2 в двоичной арифметике даёт 0,30000000000000004;
ОКРУГЛэто чинит, а формат — только прячет.