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. Введение в формулы массива.

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

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

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

Очень осторожно с этой приблудой. Точность_как_на_экране применяется ко всем листам книги и после сохранения вернуть прежние значения не получится.

Благодарю

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

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

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

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

>>остальное отбрасывается вообще
Не отбрасывается, а не отображается в ячейке. Формат — лишь визуальное представление значения Выделите ячейку и посмотрите в строке формул — там то число, которое находится в ячейке

>>В формате ячейки ставлю «2 знака после запятой».
А кто Вам мешает поставить 4?

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

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

🙂 это она пол поменяла 🙂 🙂 🙂

В советское время счёт-фактура была «она» 😉

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

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

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

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

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

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

У Вас возможно включено текстовое представление ячейки.
Чтобы формула работала, ячейки должны иметь числовой формат.
Для этого их сначала надо очистить от данных, а потом форматировать в чсловое представление.
Без очистки переформатирование ячеек чаще всего не помогает.
Хорошо, если табличка небольшая, а если массив на нескольких листах?
Лучше подумать об этом заренее.

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

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

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

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

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

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

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

В школе не этому учили )
=ОКРУГЛ(ОКРУГЛ(1,4746;3);2)

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

Зачем же Вы округляете, если для вычислений нужны разряды, которые при округлении обрезаются?!

=СУММПРОИЗВ(ОКРУГЛ(G4:G6;2))
Не нужно вводить с помощью «трехпальцевого метода» )

Красавчик!

Только наоборот
=ОКРУГЛ(СУММПРОИЗВ(A1:A3);2)

Забыли:
Для этого мы введем такую формулу: =СУММ(ОКРУГЛ(A1:A7;0)). После ввода массива функций следует нажимать не «Enter», а комбинацию клавиш Ctrl+Shifi+Enter. В результате Excel сам подставит фигурные скобки {} – это значит, что функция выполняется в массиве.

Спасибо огромное! Ваш коммент сильно помог! Все заработало как надо 🙂

Спасибо! Очень помогли!

Спасибо Kovt, Ваш коммент именно мне очень помог, очень, очень сильно!

Была проблема: переумножить несколько значений с десятичными. Округлял итого и разница была, сделали как написал комментатор kovt, (Файл-Параметры-Дополнительно-При пересчете этой книги: задать точность) и ВУАЛЯ! ВСЁ ОК!!! спасибо добрый человек!!!

не бьется сумма! как узнать в каких ячейках ошибки, например, пробел или точка?

Анна, если я правильно понял, вас интересует, как проверить, число ли в ячейке? С этой задачей справится функция ЕЧИСЛО()

Чтобы не пользоваться формулами и округлениями я ввожу суммы в копейках. формат числа: 0"."00
Но тогда 100 000 копеек выглядят как 1000.00, а хочется:
1 000.00 или даже 1 000
я готов даже написать свою функцию на VBA, но не знаю как Excel заставить использовать ее при форматировании числа

Вадим, посмотрите комментарий в другой ветке. Может быть, подскажет вам решение.

Что касается кавычек, то это особенность WordPress — движка, на котором работает этот сайт. Автоматически он заменяет " на лапочки «. Поправил.

Спасибо за правки. но первую кавычку, сразу после 0, WordPress оставил )
Прочитал комментарии по ссылке. Моя проблема отличается от той. У меня сумма в копейках должна быть представлена рублями, т.е. число в ячейке при форматировании надо поделить на 100. Приведенное мною решение как бы делит на 100, но при этом не разбивает «поделенное» на разряды(
Но спасибо, что пытались
P.S. Вот бы была какая-то возможность вставить свою функцию в процесс форматирования числа…


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