Как сверять транзакции в таблице (без ручного сопоставления строк)
Сверка двух списков транзакций сводится к поиску четырех типов расхождений. Это пропущенные строки с одной стороны, дублирующиеся строки с другой, несовпадающие суммы и проведение одного и того же платежа в разные периоды. Все остальное — лишь сопутствующий учет вокруг этих четырех пунктов.
Первая интуитивная реакция — сопоставить два файла и начать сравнивать строки вручную. Это работает, пока строк не больше двухсот, но затем превращается в работу на весь день, результатом которой становится цифра, которую никто не сможет проверить.
В этом руководстве мы разберем, почему электронные таблицы плохо справляются с этой конкретной задачей, рассмотрим три обходных пути, к которым обычно прибегают, и объясним, где каждый из них упирается в свой предел. Это описание рабочего процесса с данными, а не бухгалтерская консультация.
Почему сверка в электронных таблицах оказывается сложнее, чем кажется
Электронная таблица сравнивает ячейки. Сверка же сравнивает события, а одно и то же событие редко выглядит одинаково с обеих сторон.
Платеж по карте может отображаться один раз в вашей главной книге и дважды в выгрузке платежного процессора (разделенный на списание и комиссию). Счет от поставщика может иметь номер INV-0042 в одной системе и INV42 в другой. Перевод, инициированный 31-го числа, может быть проведен только 1-го, что сбивает баланс за весь месяц.
Ничто из этого не является ошибкой в данных. Это обычное следствие того, что две разные системы фиксируют одну и ту же реальность, и ни одна формула поиска не способна решить эту проблему сама по себе.
Существует также ловушка округления, в которую люди попадают каждый месяц. Значения валют, хранящиеся с полной точностью с плавающей запятой, могут отличаться в четвертом знаке после запятой, поэтому две суммы, которые отображаются как 1,204.50, не проходят проверку на точное равенство.
Объем данных также меняет характер проблемы. При пятидесяти строках человек может удерживать оба списка в голове. При пяти тысячах задача превращается в поиск нескольких исключений, скрытых в огромном массиве совпадений. Человеческое внимание плохо приспособлено для такой работы.
Во что это вам обходится
«Хвост» в конце месяца. Первые девяносто процентов строк сходятся за считанные минуты. Оставшаяся горстка требует часов работы, потому что для каждой строки человеку нужно вручную определить, к какому из четырех типов расхождений она относится.
Непроверяемые результаты. Когда сопоставление происходит «на глаз», весь ход мысли теряется в момент закрытия файла. Через шесть недель никто не сможет восстановить, почему две строки были сочтены одним и тем же платежом.
Уцелевшие ошибки. Дубликат, сопоставленный не с тем контрагентом, компенсирует разницу в итоговой сумме, и сверка кажется успешной. Совпадение итогов не доказывает, что сходятся сами строки.
Эти три фактора усиливают друг друга. Длинный «хвост» вызывает усталость, усталость заставляет искать простые пути, а простые пути — это именно то, из-за чего ошибочное совпадение фиксируется как верное.
Обходные пути, которые используют на практике
Вариант 1: Сравните итоги перед сопоставлением
Начните со сравнения групповых итогов, а не отдельных строк. Используйте функцию SUMIFS, чтобы подвести итоги по месяцам, счетам или контрагентам для каждой стороны, а затем сопоставьте эти два столбца.
Это позволяет локализовать расхождение до того, как вы потратите на него время. Если одиннадцать из двенадцати месяцев сходятся до копейки, вам останется сверить только один месяц вместо целого года.
Этот метод действительно полезен, но его возможности ограничены. Групповые итоги показывают, где именно кроется расхождение, но никогда не укажут, какие именно строки его вызвали, а две компенсирующие друг друга ошибки внутри одной группы взаимно уничтожатся незаметно для вас.
Вариант 2: Создайте ключ сопоставления и выполните поиск
Объедините поля, идентифицирующие событие, в один ключ (обычно это дата плюс сумма плюс очищенный номер документа). Затем используйте функцию XLOOKUP в обоих направлениях, чтобы найти строки, которые есть на одной стороне, но отсутствуют на другой.
Добавьте функцию COUNTIFS по тому же ключу для поиска дубликатов, поскольку функция поиска возвращает только первое совпадение и игнорирует последующие. Округлите суммы с помощью функции ROUND до двух знаков после запятой перед включением их в ключ — это устранит проблему несовпадения чисел с плавающей запятой, описанную выше.
Это основной рабочий метод, который выручает в большинстве случаев. Его ограничение носит структурный характер: ему нужен ключ, который означает одно и то же с обеих сторон. Различия в форматах номеров документов или комиссия, разбитая на две строки, мгновенно делают его неэффективным.
Вариант 3: Осознанная работа с четырьмя типами расхождений
Вместо одного общего прохода сопоставления выполните четыре более узкие проверки. Пропущенные строки выявляются с помощью двустороннего поиска. Дубликаты — с помощью подсчета по ключу. Несовпадения сумм — путем сопоставления только по номеру документа с последующим сравнением значений. Различия в датах — путем сопоставления в пределах определенного временного окна, а не по точной дате.
При правильном подходе это самый надежный и прозрачный метод, поскольку каждая несопоставленная строка попадает в определенную категорию, а не в общую кучу остатков.
Но он же и самый трудоемкий. Четыре прохода означают создание четырех вспомогательных столбцов с каждой стороны, и всю эту систему приходится перестраивать заново, если в любой из выгрузок изменится порядок столбцов. Наше руководство по очистке и дедупликации данных описывает этап подготовки, от которого зависит этот метод.
Общий предел возможностей. Все три метода предполагают, что одной строке с одной стороны соответствует ровно одна строка с другой. Однако одно перечисление средств может покрывать сорок транзакций. А один платеж может состоять из списания, комиссии и возврата. В обоих случаях сопоставлению по ключу просто не за что зацепиться. Именно на это и уходит куча времени.
Как сверять транзакции с помощью Powerdrill Bloom
Шаг 1: Загрузите оба файла
Загрузите выгрузку из главной книги и выписку контрагента вместе. Powerdrill Bloom проанализирует оба файла, поэтому несовпадающие имена столбцов, разные форматы дат и несогласованные стили номеров документов будут видны еще до начала сопоставления.
Шаг 2: Опишите задачу сверки на естественном языке
Запросите четыре категории по названию. Попросите найти строки, которые присутствуют в одном файле и отсутствуют в другом, а также дублирующиеся номера документов. Затем запросите несовпадения сумм, выходящие за рамки допустимой погрешности, и записи, даты которых расходятся на несколько дней.
Затем задайте вопрос, который решает самые сложные случаи. Спросите, какие группы строк на одной стороне в сумме дают одну строку на другой. Это тот самый случай связи «многие к одному», с которым не справляется сопоставление по ключу.
Шаг 3: Экспортируйте диаграмму, отчет или презентацию
Выгрузите список исключений, сводную информацию по несопоставленным суммам по категориям или краткую пояснительную записку для закрытия периода.
Почему это лучше, чем настраивать сопоставление заново каждый месяц
| Ручной способ | Powerdrill Bloom | |
|---|---|---|
| Разные форматы номеров документов | Сначала вручную очистить данные с обеих сторон | Описать разницу текстом и сделать запрос |
| Несколько строк соответствуют одной | Группировка вручную | Спросить, какие строки в сумме дают значение контрагента |
| Дубликаты | Дополнительный столбец подсчета с каждой стороны | Включаются в список исключений автоматически |
| В следующем месяце | Перестраивать все вспомогательные столбцы заново | Просто загрузить новые выгрузки |
На первую строку уходит больше всего времени. Очистка номеров документов для приведения их к единому виду в двух системах — это подготовительная работа, которая сама по себе не дает результата. Кроме того, ее приходится делать заново при каждом изменении формата выгрузки.
Распространенные ошибки
Считать совпадение итогов завершенной сверкой. Две равные по величине ошибки в противоположных направлениях дают идеальный итоговый баланс. Проверяйте количество строк и несопоставленные суммы, а не только общий итог.
Сопоставление только по сумме. В любой реальной книге учета многие транзакции имеют одинаковую сумму. Поиск по сумме свяжет совершенно разные операции и при этом будет выглядеть вполне убедительно.
Игнорирование разницы в округлении. Значения, которые выглядят одинаково на экране, все равно могут не пройти проверку на равенство. Округляйте данные с обеих сторон до одинаковой точности перед их сравнением.
Забывать о направлении проверки. Односторонний поиск находит строки, отсутствующие во втором файле, но никогда не покажет строки, отсутствующие в первом. Всегда запускайте проверку в обоих направлениях.
Удаление сопоставленных строк в процессе работы. Это кажется удобным, но полностью уничтожает историю аудита. Вместо этого помечайте строки в специальном столбце статуса и сохраняйте исходные данные нетронутыми.
Проведение сверки до закрытия периода. Запоздалые записи создают временные расхождения, которые со временем устраняются сами собой. Пытаться выловить их в середине периода — пустая трата времени.
Заключение
Сверка — это задача классификации, а не просто сопоставления. Распределите каждую несопоставленную строку по категориям (пропущенная, дубликат, неверная сумма или неверный период), и оставшаяся часть работы станет простой и понятной.
Самым затратным моментом является ежемесячное воссоздание всей этой системы с нуля, особенно когда номера документов не совпадают или один платеж распределяется по нескольким строкам. Если именно на это уходит ваше время при закрытии периода, попробуйте Powerdrill Bloom для обеих выгрузок. Читайте также наши руководства о том, как объединить два файла Excel без VLOOKUP и как составить отчет о сравнении бюджета с фактическими показателями. Страницы, посвященные отчетам о расходах и анализу денежных потоков, описывают смежные рабочие процессы.
Часто задаваемые вопросы
Что значит сверять транзакции?
Это означает подтверждение того, что две записи об одной и той же операции совпадают, и объяснение каждого оставшегося расхождения. Эти объяснения делятся на четыре группы: пропущенные строки, дубликаты, несовпадения сумм и временные различия.
Может ли Excel сверять два списка автоматически?
Сам по себе — нет. Excel предоставляет вам инструменты (в основном функции поиска, подсчета и условного суммирования), но логику сопоставления вы выстраиваете сами и перестраиваете ее заново при каждом изменении формата выгрузки.
Почему две суммы, которые выглядят одинаково, не сопоставляются?
Обычно дело в точности хранения данных. Значение, отображаемое с двумя знаками после запятой, на самом деле может содержать больше знаков, из-за чего точное сравнение не удается. Округление данных с обеих сторон до одинаковой точности решает эту проблему.
Как обрабатывать один платеж, который отображается в виде нескольких строк?
Сгруппируйте более мелкие строки и сравните итог группы с соответствующей одиночной строкой. Сопоставление по ключу на уровне строк не способно обработать этот случай, поэтому он является наиболее частой причиной ручной работы.
Нужно ли удалять несопоставленные строки?
Нет. Сохраняйте их и добавьте столбец статуса, где фиксируйте категорию и причину расхождения. Удаление уничтожает историю аудита, которая позволяет обосновать результаты сверки в будущем.