Акт сверки в Excel: колонки и формулы для проверки

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

Два листа, одинаковые колонки

Откройте два листа: «Мы» (ваш список) и «Контрагент» (список из его акта). Колонки на обоих листах одинаковые:

• A: вид документа (отгрузка, возврат, платёж);
• B: номер документа;
• C: дата;
• D: сумма, причём отгрузка записывается положительным числом, возврат и платёж — отрицательным;
• E: колонка ключа, заполняется формулой.

Если знаки сумм у обеих сторон одинаковые, итог колонки показывает изменение остатка, и его можно сразу сравнить с данными акта.

Ключ и основные формулы

Ключ — уникальный признак каждого документа: номер и дата. В ячейку E2 первой строки введите: =TRIM(B2)&"|"&C2. Если дата настоящая, на обоих листах получится одно и то же число. В русскоязычном Excel TRIM называется СЖПРОБЕЛЫ. Разделителем в формулах может быть точка с запятой или запятая, в зависимости от настроек.

На лист «Мы» добавьте ещё три колонки:

• F: есть ли такой ключ у контрагента: =COUNTIF(Контрагент!E:E;E2). Если результат 0, документа у контрагента нет (по-русски СЧЁТЕСЛИ);
• G: сумма у контрагента: =SUMIF(Контрагент!E:E;E2;Контрагент!D:D) (СУММЕСЛИ);
• H: разница: =ROUND(D2-G2;2) (ОКРУГЛ).

Колонка статуса I: =IF(F2=0;"у контрагента нет";IF(H2<>0;"разница в сумме";"совпало")) (ЕСЛИ). Такие же колонки, но со ссылкой на лист «Мы», добавьте и на лист «Контрагент»: тогда станут видны и документы, которых нет у нас.

Поскольку ключ состоит из номера и даты, документ с тем же номером, но другой датой тоже получит статус «у контрагента нет». Чтобы отделить такие пары, добавьте ещё одну колонку с проверкой только по номеру: =COUNTIF(Контрагент!B:B;B2). Если по ключу результат 0, а по номеру 1, документ у контрагента есть, но дата отличается. Это классический случай конца месяца: груз отгружен в один день, а накладная внесена разными датами. Одна такая колонка выделяет из группы «у контрагента нет» расхождения по дате и сразу показывает причину.

Как читать результат

Включите фильтр и покажите строки, статус которых не «совпало». Остальные попадут в три группы: нет у контрагента, нет у нас и разница в сумме. Чтобы заполнить причину каждой, добавьте ещё одну колонку: дата, дубль, цена, возврат и так далее. Контроль: разница итогов листов «Мы» и «Контрагент» должна быть равна следующему: сумма строк «у контрагента нет», плюс разницы по строкам «разница в сумме», минус сумма строк «у нас нет». Если не равна, где-то строка осталась в стороне.

Подводные камни

• Номер хранится как текст: «125» и «00125» выглядят по-разному. Приведите к одному формату.
• Невидимые пробелы. TRIM их убирает.
• Дата осталась текстом: тогда ключ на двух листах получается разным. Преобразуйте колонку в даты.
• Десятичный разделитель: если у одной стороны точка, у другой запятая, сумма превращается в текст.
• Один и тот же номер повторяется в разные годы. Поэтому в ключ включена и дата.
• Скрытые строки и включённый фильтр: перед подсчётом итога убедитесь, что всё открыто.
• Объединённые ячейки ломают формулу, откажитесь от них.

Условный пример

На листе «Мы» 40 строк, на листе «Контрагент» 39. После формул: 35 строк «совпало», 3 строки «разница в сумме», 2 строки «у контрагента нет», а на листе «Контрагент» 1 строка «у нас нет». Разница в сумме по трём строкам объясняется ценой. Два документа, которых нет у контрагента, — накладные, внесённые в последний день месяца. Строка, которой нет у нас, — возврат, записанный контрагентом. Общая разница сошлась по приведённой выше формуле: работа завершена.

Вывод

Таблица акта сверки в Excel готовится один раз и используется каждый раз. Ключ, четыре формулы и контроль итога гораздо надёжнее ручного сравнения. Формулы находят расхождение, а причину объясняет документ. SkladQarz построчно сводит список из Excel или 1С с данными контрагента и фактическим остатком склада и передаёт реестр расхождений с рекомендациями.