Excel для бухгалтера: исправление ошибки округления

Рубрика: 7. Полезняшки Excel

Бухгалтеры (и не только) знают одну «нехорошую» особенность Excel`я – «неумение» правильно суммировать. 🙂 Иногда это приводит к казусам в бухгалтерских документах, сформированных в Excel (рис. 1)

Рис. 1. Фрагмент счет-фактуры с «неверным» суммированием

Скачать заметку в формате Word, примеры в формате Excel

Видно, что общий итог по налогу (значение в ячейке G7) и стоимости товаров (Н7) отличаются на копейку от суммы по строкам (G4:G6 и Н4:Н6, соответственно). Это ошибка является следствием округления. Дело в том, что значения только отображаются в формате с двумя десятичными знаками. Фактические значения в этих ячейках содержат больше десятичных знаков (рис. 2). Excel суммирует не отображаемые значения, а фактические.

Рис. 2. Тот же счет-фактура с большим числом знаков после запятой

Чтобы значение в ячейке G7 равнялось сумме отображаемых значений в ячейках G4:G6, можно применить формулу массива, проводящую округление значений до двух десятичных знаков перед суммированием: {=СУММ(ОКРУГЛ(G4:G6;2))} (рис. 3). [1]

Рис. 3. «Правильное» суммирование с использованием формулы массива

Чуть подробнее, как работает эта формула. Excel формирует виртуальный массив (в памяти компьютера), состоящий из трех элементов: ОКРУГЛ(G4;2), ОКРУГЛ(G5;2), ОКРУГЛ(G6;2), то есть значений в ячейках G4:G6, округленных до двух десятичных знаков, а затем суммирует эти три элемента. Вуаля! 🙂

Ошибки округления можно также исключить, применив функцию ОКРУГЛ в каждой из ячеек диапазона G4:G6. Этот прием не требует применения формулы массива, однако требует многократного использования функции ОКРУГЛ. Вам судить, что проще!


[1] Идея подсмотрена в книге Джона Уокенбаха «MS Excel 2007. Библия пользователя». Если вы не использовали ранее формулы массива, рекомендую начать с заметки Excel. Введение в формулы массива.

Комментарии: 21 комментарий

Для полноты картины можно упомянуть еще об одном варианте, в параметрах Excel указать «точность как на экране» (Файл-Параметры-Дополнительно-При пересчете этой книги: задать точность как на экране)

Вот не рекомендуют программисты такой способ

Спасибо за статью. «соответсвенно» лучше исправить

а как быть, если в документе производится большое количество вычислений, и числа «завязаны» друг за друга. использование формул ОКРУГЛ и т.п. немного неудобно.
как можно отключить округление отображаемого числа? как сделать, чтобы ексель показывал то что есть? пример — число 12345.6789, с точностью после запятой 2, он не округлял 12345.68, а отображал 12345.67, но значение оставалось 12345.6789?

Александр, если Вы хотите, чтобы Excel отражал с точностью до двух знаков после запятой, а хранил число с максимальной точностью, просто задайте форматирование «два знака после запятой», и никакие дополнительные формулы не потребуются. Но… именно против этого и направлена статья, так как в бухгалтерских расчетах не допускается расхождение между суммой и слагаемыми…

Как быть, если это не помогло. В формате ячейки ставлю «2 знака после запятой» затем вписываю в нее число 1950,4787, то автоматически записывается 1950,48, остальное отбрасывается вообще.

это просто супер!за 4 года ошибок с excel это как бальзам

Счет-фактура — мужского рода, «он мой»

не получилось, пишет #ЗНАЧ, хоть тресни!

Марина, какая формула возвращает ошибку #ЗНАЧ? Возможно, вы неверно вводите формулу массива. Почитайте статью, на которую есть ссылка в конце заметки. Можете прислать мне файл в личку. Если смогу, помогу.

Пробую округлить числа в excel 2013/
формула такая: =ОКРУГЛ(C1;0)

Всё время пишет #ЗНАЧ!, что не делай.
Формат ячеек числовой.

Александр, пришлите файл в личку.

Спасибо! Уже разобрался в чём была проблема. Суть заключалась в том, что хоть ячейка в таблице и была в формате числа, но за цифрой стоял незаметный пробел.
Когда я прописывал формулу из-за этого она не срабатывала.
Убрал пробелы — всё заработало. Во всём разобрался.

формулу проставила и появилась ошибка, но данные которые суммируются то же состоят из формул, их тоже надо?

Столкнулась точно с такой проблемой в бухгалтерских расчетах. Вставляю формулу — появляется #ЗНАЧ! В чем может быть проблема?

Мария, почитайте выше комментарий Александра. Скорее всего, у вас в ячейке не число, например, лишние пробелы.

Спасибо большое, нашла ошибку.

А как быть с этим:
=ОКРУГЛ(1,4746;2) —> результат 1,47
По правилам округления должно быть 1,48 или я ошибаюсь?

Дмитрий, ошибаетесь. Если третья цифра после запятой < 5, то округление в меньшую сторону.

Вы правы, спасибо. Вспомнил школу )))

округляю, все вроде в норме, но в значении ячейки цифры после числа разрядов округления пропадают т.е. ноль. — число 0,8125, округляю до 2 цифр после запятой. и он выдает значение ячейки 0,8100 т.е те 0,0025 не будут учитываться в дальнейших расчетах


Прокомментировать