Сверка оплат в Excel: как собрать свою таблицу без ручной проверки

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

А началось всё с постоянной сверки банковской выписки с заказами.

Что стало вместо ручной проверки оплат?

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

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

Раньше главный вопрос звучал так: деньги пришли или нет? Теперь вопрос точнее: какой счёт и какой заказ они закрывают?

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

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

Почему своя таблица удобнее готового сервиса?

Запрос «акт сверки по счетам на оплату» часто приводит к решениям для взаиморасчётов. Мне нужна была другая вещь: увидеть связь между банковским поступлением, конкретным заказом и его готовностью к отгрузке.

У нас один платёж может быть авансом. Другой закрывает доплату. Третий относится к нескольким счетам. Если в таблице есть только колонка «оплачено», она скрывает главное - как именно распределились деньги.

Я добавила отдельные поля:

• сумма заказа;
• сумма оплаты;
• уже распределённая сумма;
• нераспределённый остаток;
• статус;
• причина ручной проверки.

Если правило меняется, я не жду ответа поддержки. Описываю помощнику, что теперь считать правильным, и сначала проверяю результат на тестовых строках.

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

Из чего состоит сверка платежей с заказами?

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

На листе Заказы нужны такие поля:

OrderID | Номер счета | Дата заказа | Срок оплаты | Сумма заказа | Распределено | Остаток | Статус

На листе Оплаты:

PaymentID | Дата платежа | Назначение платежа | OrderID | Сумма платежа | Распределено | Остаток | Статус проверки

OrderID может быть номером заказа, номером счёта или другим стабильным ключом. Главное, чтобы он одинаково записывался в обеих таблицах. 00125 и 125 для поиска могут оказаться разными значениями. Пробелы и разные типы данных тоже мешают сопоставлению.

Рабочий слой связывает заказы и оплаты. Он суммирует платежи по ключу, сравнивает результат с суммой заказа и назначает статус:

• Оплачено;
• Оплачено частично;
• Просрочено;
• Не оплачено;
• Несопоставлено.

Для точного поиска помощник может использовать VLOOKUP или совместимую формулу. Google в справке по функции пишет:

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

Что поставить перед сборкой таблицы?

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

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

Claude Code встраивается в редактор и работает рядом с файлами. Я пишу: «Свяжи оплаты с заказами по номеру счёта и покажи частичную оплату». Помощник уточняет данные, создаёт нужные изменения и объясняет, что сделал.

Для загрузки выписки можно использовать Excel или Google Таблицы. В Google Таблицах банковский CSV или XLSX загружается через Файл → Импортировать. Google также описывает импорт CSV по URL через IMPORTDATA, но для первого варианта достаточно ручной загрузки.

Официальная подписка Claude Code Pro стоит $20 в месяц. Рублёвый эквивалент и официальный способ оплаты в России в проверенных материалах не подтверждены, поэтому переводить эту сумму в рубли не буду.

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

Как сделать сверку оплат в Excel по шагам?

1. Опиши исходные данные.

   Создай папку проекта и открой её в VS Code. Положи туда обезличенную банковскую выгрузку и пример списка заказов. Не проси помощника сразу писать программу.

   Разобрать исходные данныеСкопировать промпт

   У меня небольшое производство. Я принимаю авансы и доплаты по заказам, а банковскую выписку сверяю со списком счетов вручную. В папке проекта лежат пример банковской выгрузки и список заказов. Посмотри названия файлов и колонок. Пока не пиши код. Определи, где дата платежа, сумма, назначение платежа, номер заказа или счета, а в списке заказов - номер заказа, срок оплаты и сумму заказа. Если колонок не хватает или формат непонятен, задай вопросы. Отдельно перечисли, какие данные нельзя надёжно сопоставить автоматически.

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

2. Создай два рабочих листа.

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

   Создать листы заказов и оплатСкопировать промпт

   Создай в рабочей таблице два листа: Заказы и Оплаты. На листе Заказы сделай колонки OrderID, Номер счета, Дата заказа, Срок оплаты, Сумма заказа, Распределено, Остаток и Статус. На листе Оплаты сделай колонки PaymentID, Дата платежа, Назначение платежа, OrderID, Сумма платежа, Распределено, Нераспределенный остаток и Статус проверки. Не меняй исходные файлы. Добавь несколько тестовых строк без настоящих персональных данных и объясни, куда загружать новую банковскую выписку.

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

3. Задай общий ключ.

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

   Настроить точное сопоставлениеСкопировать промпт

   Свяжи листы Заказы и Оплаты по колонке OrderID. Используй только точное совпадение, не приблизительный поиск. Проверь, чтобы OrderID был одного типа в обоих листах: текст или число. Удали лишние пробелы только в отдельной нормализованной колонке, не меняя исходное значение. Не считай 00125 и 125 одинаковыми, пока явно не задано правило приведения. Покажи отдельный список строк, для которых ключ не найден.

   При точном поиске помощник может применить VLOOKUP с параметром FALSE или совместимую формулу. Сначала проверь несколько известных заказов вручную: номер, сумма и дата должны подтянуться из правильной строки.

4. Добавь статусы оплаты.

   Теперь таблица должна отличать полную оплату от частичной и не прятать платежи без связи.

   Добавить статусы оплатыСкопировать промпт

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

   Техническая ошибка #N/A не должна быть итогом для владельца. Google объясняет: «Если возвращается значение #N/A, это означает, что значение не найдено». В рабочей таблице понятнее показывать Несопоставлено, причину и сумму.

   Так собирают программы без программиста, и называется это вайб-кодингом.

5. Учти текущую дату.

   Просрочка должна меняться сама, а не после ручной правки каждой строки.

   Настроить просрочку по датеСкопировать промпт

   Добавь проверку срока оплаты с помощью текущей даты. Если срок оплаты прошёл, платежей нет и заказ не закрыт, показывай статус Просрочено. Если срок ещё не прошёл, показывай Не оплачено. Если есть только часть суммы, показывай Оплачено частично, а после срока добавь пометку о просрочке. Используй функцию TODAY или её совместимый вариант. Не меняй статус Оплачено из-за прошедшего срока.

   Функция TODAY() возвращает текущую дату без времени и обновляется при пересчёте таблицы. Поэтому проверь, не записана ли дата проверки обычным текстом.

6. Настрой подсветку строк.

   Цвет нужен не для красоты. Он помогает быстро увидеть, где нужна проверка.

   Настроить условное форматированиеСкопировать промпт

   Настрой условное форматирование для строк листа Заказы. Статус Оплачено подсвечивай зелёным, Оплачено частично - жёлтым, Просрочено - красным, Не оплачено - серым, Несопоставлено - оранжевым. Правило должно окрашивать всю строку по значению колонки Статус. Не меняй суммы и формулы. Покажи, на какой диапазон распространяются правила.

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

7. Добавь загрузку и запуск.

   Когда ручной импорт работает, можно попросить помощника подготовить обновление по расписанию.

   Добавить безопасное обновление выпискиСкопировать промпт

   Подготовь обновление листа Оплаты из новой выгрузки CSV или XLSX. Сохраняй исходный файл в архиве и не удаляй предыдущие загрузки. Перед добавлением проверяй стабильный PaymentID, чтобы повторная загрузка той же строки не создавала дубль. После загрузки пересчитывай распределение оплат и статусы заказов. Сначала сделай ручной запуск через Файл → Импортировать. Затем объясни, что понадобится для запуска по расписанию через Apps Script, и не включай расписание без моего подтверждения.

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

Какие файлы появятся после сборки и что в них менять?

В рабочей папке или таблице появятся:

• Заказы - список счетов и заказов;
• Оплаты - банковские поступления;
• Сверка - результат сопоставления и статусы;
• Журнал запусков - дата обновления, количество строк и найденные ошибки;
• Архив - исходные выгрузки до обработки.

В листе Сверка регулярно проверяй четыре вещи:

• одинаково ли записан OrderID;
• распознаны ли даты как даты, а не как текст;
• не появились ли дубли после повторной загрузки;
• не остался ли платёж в нераспределённом остатке.

Для больших выгрузок не делай отдельный сетевой запрос на каждую строку. Apps Script обрабатывает данные пакетно, а одно выполнение ограничено 6 минутами. У сервиса есть суточные квоты, и при превышении лимита выполнение прекращается.

Если меняешь формулу или логику, сначала сохрани копию файла. Я не исправляю непонятный участок наугад. Сначала прошу помощника объяснить, какой лист и какое правило он собирается менять.

Что делать, если оплата не попала в сверку?

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

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

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

Появился дубль. Повторная загрузка одной и той же выписки может добавить платежи второй раз. Для каждой строки нужен PaymentID или стабильная комбинация признаков.

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

Старую выгрузку я сохраняю в архиве. Без неё сложно понять, откуда взялся дубль и какая версия данных была загружена первой.

Форматы ключей различаются. Значения 00125 и 125 могут не совпасть. Та же проблема возникает из-за пробела в конце или когда один лист хранит номер как текст, а другой как число.

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

Вместо результата появился #N/A. Это значит, что поиск не нашёл значение. Не маскируй ошибку пустой ячейкой, если из-за этого теряется платёж. Преврати её в понятный статус и причину.

Найди формулы, которые возвращают #N/A на листе Сверка. Определи, отсутствует ли OrderID, отличается ли формат ключа или не найден лист Оплаты. Замени техническую ошибку на статус Несопоставлено и отдельную причину. Не назначай платёж заказу по одной сумме без подтверждённого ключа.

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

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

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

Проверь строки, где сумма заказа или сумма оплаты клиента больше фактического поступления на расчетный счет. Раздели сумму заказа, оплату клиента, комиссию и поступление. Не помечай такую строку как обычную несопоставленную. Добавь статус Оплачено, комиссия удержана и покажи сумму комиссии отдельным полем для проверки.

В опубликованном разборе эквайринга приведён пример: покупатель оплатил 10 000 рублей, а на расчётный счёт пришло 9 850 рублей. В такой ситуации сверка рубль в рубль даёт ложную недоплату.

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

Добавь отдельные строки для возврата и частичного возврата, связанные с исходным PaymentID и OrderID. Не меняй исходную оплату задним числом. Разделяй статусы Оплачен, Частичный возврат, Полный возврат, Возврат ожидает поступления и Нужно проверить. Покажи, как возврат меняет итоговую сумму по заказу.

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

Сколько стоит своя сверка против сервиса и подрядчика?

Seeneco за 299 ₽ в месяц даёт готовую банковскую сверку, но дополнительные счета, пользователи и внедрение оплачиваются отдельно. За год базовая цена составит от 3 588 ₽ без дополнительных услуг.

Самостоятельная сборка выглядит дешевле по прямым расходам, но оплачивается собственным временем. Я не включаю его в сумму 0 ₽, потому что точная оценка зависит от исходных файлов и того, сколько правил приходится проверять.

У этой схемы есть границы:

• она не заменяет бухгалтерскую сверку взаиморасчётов;
• без стабильного ключа автоматическое сопоставление ненадёжно;
• возвраты, комиссии и сложное распределение требуют отдельных правил;
• Apps Script имеет квоты и ограничение 6 минут на одно выполнение;
• банковскую выписку и персональные данные нужно защищать от лишнего доступа.

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

Что сделать сегодня, если не разбираешься в технологиях?

Начни с короткого маршрута:

1. Убери из примера лишние имена, телефоны и реквизиты.
2. Оставь номер заказа, дату, сумму, срок оплаты и назначение платежа.
3. Попроси помощника найти названия колонок и спорные места.
4. Собери листы Заказы и Оплаты.
5. Проверь несколько полных, частичных и несопоставленных платежей.
6. Сохрани исходную выгрузку в архив.
7. Только после ручной проверки добавляй автоматическую загрузку.

Для Google Таблиц ручной путь выглядит так: Файл → Импортировать, затем выбери, заменить лист или добавить данные. Расписание через Apps Script подключай позже. Оно удобно для регулярного обновления, но не отменяет проверки.

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

Вопросы и ответы

Это собирательная история выпускников практикума, а не рассказ одного человека: так эта работа устроена в большинстве таких дел.