Как быстро проверить акт сверки в экселе на расхождения

Коротко

Проверка акта сверки в Excel — это сопоставление двух выгрузок по одному ключу: номеру документа, дате и сумме. Глазами сверяют только короткие акты; на сотнях и тысячах строк расхождения ищут формулами, а вручную разбирают уже найденные разницы.

Почему сверка глазами не работает

На сотне строк взгляд ещё держится, на третьей сотне — уже нет: одинаковые суммы и похожие номера сливаются, пропущенная строка остаётся незамеченной. Excel решает задачу иначе: обе выгрузки сводятся в один файл, строки сопоставляются по ключу, и каждая разница получает пометку.

Расхождения чаще даёт не арифметика, а подготовка данных: суммы сохранены как текст, даты распознаны как текст, в названии контрагента стоит неразрывный пробел, при копировании из учётной программы сдвинулись строки. Поэтому сверка начинается с очистки, а не с формул.

Подготовка выгрузок: что сделать до формул

Перенесите из своего учёта и из акта контрагента только четыре колонки: дата, документ, дебет, кредит. Шапку, сальдо на начало и на конец в таблицу сравнения не берите — они мешают сортировке и сбивают формулы.
Снимите объединение ячеек: выделите фрагмент, «Формат ячеек» → вкладка «Выравнивание» → уберите галочку «Объединение ячеек». Пустые столбцы удалите.
Проверьте, что суммы — числа: текст выравнивается по левому краю. Поставьте числовой формат и убедитесь, что разделитель дробной части один и тот же.
Проверьте даты: если ДАТАЗНАЧ возвращает ошибку, дата записана текстом. Поставьте формат «Дата» и приведите все даты к одному виду.
Уберите лишние пробелы функцией СЖПРОБЕЛЫ, приведите названия к одному регистру через ПРОПИСН или СТРОЧН, непечатаемые символы найдите через ПЕЧСИМВ. Неразрывный пробел в названии выглядит обычным, но ломает поиск.
Отсортируйте обе таблицы по одному и тому же полю — по номеру документа или по дате.

Ключ, по которому сопоставляются строки

Сравнивать таблицы можно только по признаку, который однозначно указывает на строку. Номер документа подходит не всегда: у контрагента он может быть с префиксом или без, а одна отгрузка разбита на несколько строк. Надёжнее собрать ключ из нескольких полей — даты в одном формате, номера документа и суммы — в отдельном столбце через формулу с амперсандом.

Когда ключ собран в обеих таблицах, появляется основа для любой формулы поиска: ВПР, ИНДЕКС с ПОИСКПОЗ или СУММЕСЛИ. Дальше вопрос только в том, что именно вы ищете — отсутствующий документ, другую сумму или лишнюю строку.

Формулы, которые находят расхождения

ВПР ищет документ из своей таблицы в таблице контрагента и возвращает сумму. Ошибка #Н/Д означает, что такого документа у контрагента нет.
ИНДЕКС вместе с ПОИСКПОЗ работает быстрее ВПР на больших выгрузках и не зависит от того, слева или справа стоит искомый столбец.
СУММЕСЛИ складывает все строки по ключу. Если у контрагента одна сумма покрывает несколько ваших отгрузок, сравнение сумм по отдельным строкам даст ложную разницу — сравнивайте итоги по ключу.
Разница двух сумм в отдельном столбце и условное форматирование по правилу «не равно нулю» подсвечивают расхождение цветом. Порог можно задать в копейках, чтобы не ловить хвосты округления.
СЧЁТЕСЛИ по ключу показывает, сколько раз документ встречается в каждой таблице: ноль — документа нет, два и больше — задвоение.

Что делать с найденными расхождениями

Найденная разница — ещё не результат сверки. По каждой строке нужно понять причину: документ не проведён у одной из сторон, отражён другой датой, попал в другой период, сумма изменена корректировкой. Причина записывается рядом с расхождением, иначе через неделю разбор придётся начинать заново.

Разногласия фиксируют в самом акте сверки: у каждой стороны остаётся свой экземпляр с перечнем несогласных позиций. Если расхождение тянется с прошлых периодов, сверяйте по периодам, а не всю историю сразу: сначала закрывают год, в котором возникла разница, потом идут дальше.

Частые вопросы

Сколько строк можно сверить вручную?

Ориентир простой: если таблица не помещается на несколько экранов, ручная сверка даёт пропуски. Формулы нужны уже на сотнях строк, а на тысячах — обязательны.

Почему ВПР не находит документ, который точно есть?

Почти всегда дело в ключе: лишний пробел, неразрывный символ, разный регистр, дата или сумма сохранены как текст. Проверьте длину строки через ДЛСТР в обеих таблицах — разная длина при визуально одинаковом тексте указывает на скрытый символ.

Контрагент отразил одну сумму, а у нас несколько документов. Как сравнивать?

Сравнивайте не построчно, а итог по ключу через СУММЕСЛИ: складывайте свои строки и сопоставляйте с одной суммой контрагента. Построчное сравнение в таком случае покажет разницу там, где её нет.

Что значит расхождение в копейках?

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

Нужен ли макрос?

Для регулярной сверки одинаковых выгрузок макрос экономит время: он прогоняет один и тот же набор формул по новым файлам. Для разовой проверки достаточно формул и условного форматирования.

Проверка актуальности

Проверьте ответ на сегодняшний день

Нормы меняются каждый год, а ответы в интернете за ними не успевают. Чтобы вы опирались на действующий закон, мы настроили ИИ для проверки актуальности: он сверит эту справку с действующей редакцией законов и подскажет, что изменилось.

Сверка — это рутина, которую можно отдать программе

Соберу ИИ-агента, который сверяет документы за вас

Сверка актов, счетов и УПД повторяется каждый месяц и занимает часы. Я собираю ИИ-агента под одну задачу: он разбирает документы, сопоставляет строки по ключу и показывает расхождения.

Агента подключаю к CRM, 1С, почте и мессенджерам и проверяю на сотне настоящих случаев из вашей работы — чтобы вы платили за работающего агента, а не за презентацию.

Читать об услуге «ИИ-агенты под задачу компании»