Бухгалтеры (и не только) знают одну «нехорошую» особенность 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. Введение в формулы массива.
Для полноты картины можно упомянуть еще об одном варианте, в параметрах Excel указать «точность как на экране» (Файл-Параметры-Дополнительно-При пересчете этой книги: задать точность как на экране)
Спасибо за статью. «соответсвенно» лучше исправить
а как быть, если в документе производится большое количество вычислений, и числа «завязаны» друг за друга. использование формул ОКРУГЛ и т.п. немного неудобно.
как можно отключить округление отображаемого числа? как сделать, чтобы ексель показывал то что есть? пример — число 12345.6789, с точностью после запятой 2, он не округлял 12345.68, а отображал 12345.67, но значение оставалось 12345.6789?
Александр, если Вы хотите, чтобы Excel отражал с точностью до двух знаков после запятой, а хранил число с максимальной точностью, просто задайте форматирование «два знака после запятой», и никакие дополнительные формулы не потребуются. Но… именно против этого и направлена статья, так как в бухгалтерских расчетах не допускается расхождение между суммой и слагаемыми…
это просто супер!за 4 года ошибок с excel это как бальзам
Счет-фактура — мужского рода, «он мой»
не получилось, пишет #ЗНАЧ, хоть тресни!
Марина, какая формула возвращает ошибку #ЗНАЧ? Возможно, вы неверно вводите формулу массива. Почитайте статью, на которую есть ссылка в конце заметки. Можете прислать мне файл в личку. Если смогу, помогу.
Пробую округлить числа в excel 2013/
формула такая: =ОКРУГЛ(C1;0)
Всё время пишет #ЗНАЧ!, что не делай.
Формат ячеек числовой.
Александр, пришлите файл в личку.
Спасибо! Уже разобрался в чём была проблема. Суть заключалась в том, что хоть ячейка в таблице и была в формате числа, но за цифрой стоял незаметный пробел.
Когда я прописывал формулу из-за этого она не срабатывала.
Убрал пробелы — всё заработало. Во всём разобрался.
Вот не рекомендуют программисты такой способ
формулу проставила и появилась ошибка, но данные которые суммируются то же состоят из формул, их тоже надо?
Столкнулась точно с такой проблемой в бухгалтерских расчетах. Вставляю формулу — появляется #ЗНАЧ! В чем может быть проблема?
Мария, почитайте выше комментарий Александра. Скорее всего, у вас в ячейке не число, например, лишние пробелы.
Спасибо большое, нашла ошибку.
А как быть с этим:
=ОКРУГЛ(1,4746;2) —> результат 1,47
По правилам округления должно быть 1,48 или я ошибаюсь?
Дмитрий, ошибаетесь. Если третья цифра после запятой < 5, то округление в меньшую сторону.
Вы правы, спасибо. Вспомнил школу )))
округляю, все вроде в норме, но в значении ячейки цифры после числа разрядов округления пропадают т.е. ноль. — число 0,8125, округляю до 2 цифр после запятой. и он выдает значение ячейки 0,8100 т.е те 0,0025 не будут учитываться в дальнейших расчетах
Как быть, если это не помогло. В формате ячейки ставлю «2 знака после запятой» затем вписываю в нее число 1950,4787, то автоматически записывается 1950,48, остальное отбрасывается вообще.
>>остальное отбрасывается вообще
Не отбрасывается, а не отображается в ячейке. Формат — лишь визуальное представление значения Выделите ячейку и посмотрите в строке формул — там то число, которое находится в ячейке
>>В формате ячейки ставлю «2 знака после запятой».
А кто Вам мешает поставить 4?
В школе не этому учили )
=ОКРУГЛ(ОКРУГЛ(1,4746;3);2)
Зачем же Вы округляете, если для вычислений нужны разряды, которые при округлении обрезаются?!
=СУММПРОИЗВ(ОКРУГЛ(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. Вот бы была какая-то возможность вставить свою функцию в процесс форматирования числа…
Спасибо ! Очень помогло! сэкономило кучу времени
СПАСИБО!!! После стольких лет мучений, я наконец-то догадалась поискать ответ в интернете )
Огромное спасибо! даже в 2020 году актуально)
Огромное спасибо!!!
И в 2021! Спасибо!
Спасибо! Получилось)))
спасибо, очень помогло
По правилам округления должно быть 1,47. Следующая за цифрой 7, это цифра 4, поэтому цифра 7 остается без увеличения.
Добрый день, столкнулся с такой проблемой:
В моем файле большое количество чисел, которым требуется корректное округление через «сумм(округл)», но у меня под определённые значения стоит фильтр, а формула сумм округл не видит фильтр и считает по всем значениям, даже по скрытым. Мне нужна формула которая считает «сумм округл» и считает по открытым ячейкам, теми, что не скрыты фильтром
Спасибо огромное. Случайно наткнулся на Ваши труды. В том числе и на эту статью, просматривая заголовки на сайте. Вы даже представить не можете, сколько времени Вы для меня сэкономили в будущем этой полезняшкой-заметкой. Утащил в «заметки»