Как сравнить две таблицы Excel, когда времени нет: три истории из рабочей жизни

Строки расположены в разном порядке. Некоторые исчезли, другие появились впервые, третьи сохранили свои номера, но изменили суммы. Казалось бы, все решаемо. Но главная проблема в том, что результат нужен не завтра.

Конечно, хорошо бы заранее освоить ВПР, Power Query и другие инструменты Excel. Ещё лучше – иметь готовые шаблоны на все случаи жизни. Но кто же знал, что именно сегодня придётся срочно сравнивать два реестра, а коллега, который обычно помогает с такими задачами, окажется недоступен?

Рассмотрим три истории. Ситуации и цифры условные, но проблемы вполне реальные.

История первая. Бухгалтер и тридцать минут до совещания

Девять двадцать утра. Ничто не предвещало беды.

Финансовый директор заглядывает в бухгалтерию:

– У нас почему-то не сходятся расчёты с поставщиком. Через полчаса совещание с коммерцией. Посмотрите, пожалуйста, в чём разница.

На экране два Excel-файла. В первом 586 операций, во втором 572. Можно отсортировать обе таблицы, попробовать ВПР, добавить несколько вспомогательных столбцов. Можно открыть Power Query и настроить объединение. В принципе, задача решаемая. Особенно если ты делал это раньше.

Но сегодня аналитик, который обычно помогает с подобными выгрузками, в отпуске. А самостоятельно разбираться с полным внешним объединением запросов прямо перед совещанием – развлечение на любителя.

Хуже всего то, что обычное визуальное сравнение здесь почти бесполезно. Даже если первые сто строк совпали, это ничего не говорит об остальных.

Один документ может присутствовать только в учёте, другой – только в реестре поставщика. В третьем совпадает номер, но отличается сумма. А четвёртый встречается дважды, и ещё нужно понять почему.

Пропущенная строка опасна. Но ещё опаснее – быть уверенным, что ты ничего не пропустил.

В действительности бухгалтеру не нужно изучать все 586 операций. Ему необходимо быстро разделить их на понятные группы: что совпало, где изменились значения, какие записи отсутствуют в одном из файлов и что требует дополнительной проверки. Остальное – уже предмет бухгалтерского анализа.

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

И, возможно, на совещание удастся прийти не с объяснением «мы ещё сверяем», а с перечнем найденных расхождений. И это уже деловой подход.

История вторая. Менеджер и клиент, который не любит ждать

Пятнадцать сорок семь. Уже можно думать об окончании рабочего дня.

Клиент просит обновить коммерческое предложение. Заказ хороший, терять не хочется. Вчера менеджер подготовил спецификацию по старому прайсу. Сегодня поставщик прислал новую версию. В ней 468 артикулов вместо прежних 450.

Какие товары подорожали? Какие исчезли из ассортимента? Где изменились остатки?

Клиенту совершенно неинтересно, каким образом менеджер собирается получить ответы. Ему нужны актуальные цены, наличие и сроки поставки.

Назвать старую цену – риск потерять маржу. Пообещать товар, которого больше нет на складе, – получить проблему с исполнением заказа.

Конечно, можно вручную просматривать прайсы. Можно настроить ВПР и подтягивать значения по артикулу. А если подобные обновления приходят регулярно, разумно один раз построить процесс на Power Query.

Но клиент ждёт сейчас.

Поэтому задача сводится к простому вопросу: что изменилось между двумя версиями прайса?

Например:

Первые три позиции требуют внимания. Четвёртая не изменилась – зачем тратить на неё время?

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

И ещё: исчезновение товара из прайса не всегда означает отсутствие на складе. Это лишь расхождение, причину которого нужно уточнить.

Зато менеджер уже знает, что именно уточнять.

А клиент получает ответ быстрее.

История третья. Селлер и чёрный ящик маркетплейса

Вчера вечером предприниматель скачал отчёт маркетплейса.

Сумма к перечислению – 184 000 рублей.

Сегодня он выгружает отчёт за тот же период повторно. Теперь к перечислению 176 400 рублей. Минус 7 600 рублей.

Вроде бы не катастрофа. Но неприятно. И главное – непонятно.

Это пересчитали комиссию? Появилось удержание? Провели возврат? Изменили логистические расходы? Или в новом файле появились операции, которых не было во вчерашней выгрузке?

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

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

Две версии отчёта содержат сотни или тысячи строк. Порядок операций изменился. Некоторые записи появились позже. Возможно, исправлены суммы или отдельные реквизиты.

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

Предположим, выяснилось, что по нескольким операциям выросли удержания за логистику. Теперь есть конкретные строки, с которыми можно обращаться в поддержку или разбираться самостоятельно.

Это ещё не доказательство ошибки маркетплейса. Изменение может быть вполне обоснованным. Но вместо вопроса «Куда делись мои деньги?» появляется предметный: «Почему изменились удержания по этим операциям?»

Правда, бывают случаи сложнее. Маркетплейс может изменить структуру отчёта, объединить несколько операций в одну или пересчитать показатели по другой методике. Тогда простое сопоставление строк не даст полного ответа – потребуется разбирать логику формирования отчётности.

Сверка не заменяет финансовый анализ. Она помогает понять, с чего его начинать.

Что общего у этих трёх историй?

Бухгалтер работает с документами, менеджер с товарами, селлер с операциями маркетплейса.

Но техническая задача у всех одинаковая.

Есть две таблицы. Нужно найти соответствующие записи и понять, что между ними изменилось.

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

Если идентификатор уникален, задача относительно проста. Если встречаются дубликаты или одной операции соответствуют несколько строк, нужна дополнительная проверка.

Именно поэтому обычное сравнение двух Excel-файлов может оказаться неожиданно непростой задачей.

Чем сравнивать? Какой инструмент лучше, эффективнее и удобнее? В нашем случае это не одно и то же

У Excel достаточно возможностей для подобных операций.

ВПР и ПРОСМОТРX хорошо подходят для сравнительно простых проверок, когда есть понятный ключ и немного полей.

Power Query особенно удобен, если приходится регулярно обрабатывать однотипные выгрузки. Один раз настроенный процесс можно обновлять при поступлении новых данных.

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

Например, для таких случаев существует Structo Match – бесплатный браузерный сервис для сверки двух Excel- или CSV-таблиц.

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

Содержимое выбранных файлов обрабатывается локально в браузере, без отправки содержимого файлов на сервер. В текущей версии поддерживается до 10 000 строк на файл.

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

Разумеется, Match не универсален. Он не сравнивает оформление и формулы Excel-книг и не заменяет полноценную обработку сложных отчётов с разной структурой. Зато для обычной сверки двух табличных реестров не требуется предварительно создавать запросы или писать формулы.

Попробовать Structo Match на встроенном примере

Вместо заключения

Хорошо владеть Excel – полезный профессиональный навык. Знать Power Query – ещё лучше. А иметь заранее настроенные шаблоны регулярных сверок – вообще замечательно.

Только жизнь почему-то не всегда предупреждает, какие навыки понадобятся в ближайшие полчаса.

Бухгалтеру нужно объяснить расхождение. Менеджеру – ответить клиенту. Селлеру – разобраться с изменившейся выплатой.

И всем троим важно одно: не потратить остаток дня на поиск отличий в двух таблицах.

Потому что самая дорогая часть любой сверки – зачастую не вычисления.

Это время человека, который пытается понять, что произошло.

Сверьте два файла на встроенном примере

Откройте Match, запустите готовый пример и посмотрите, как строки распределяются по категориям расхождений.

Открыть пример Match