Готовый скрипт в Power Query для сверки счетов 62 и 76 без ручного поиска ошибок.​

Введение: задача, которая занимает часы

Конец месяца. На столе три вещи: оборотно‑сальдовая ведомость по счёту 62 (авансы полученные), оборотно‑сальдовая ведомость по счёту 76 (расчёты с покупателями и заказчиками) и уже остывший кофе. Нужно свести дебет с кредитом, найти ошибки и понять, почему в одной ведомости одна сумма, а в другой — другая.​

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

Решение — автоматизировать эту сверку в Power Query. Я написал скрипт, который:

• автоматически подгружает обе выгрузки;

• если ведомостей несколько, объединяет все файлы в две большие таблицы: 62‑е с 62‑ми, 76‑е с 76‑ми;

• находит расхождения по заданным правилам;

• выдаёт список возможных ошибок в чистом, понятном виде.​

В статье разбираю, как это работает, какую логику заложить в проверки и как использовать готовый Excel‑файл, чтобы адаптировать его под вашу компанию. Пишу как инженер, а не как бухгалтер, поэтому если заметите неточность в терминологии — напишите об этом в комментариях.​

Часть 1: Бухгалтерский контекст и логика расчётов

Что такое счета 62 и 76 в 1С и почему они должны сходиться

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

В идеальной картине мира, при корректных документах и датах, суммы по 62 и 76 должны согласовываться между собой, с учётом схем платежей и авансов. На практике же:

• документ проведён с ошибкой;

• дата платежа не совпадает с датой отправки товара;

• сумма округлена или проведена несколькими частями;

• случился сбой в 1С или банке.​

Результат — расхождения, которые приходится искать вручную. В компании, где больше 200 контрагентов, это быстро превращается в часы рутинной работы каждый месяц.​

Схемы расчётов: как связаны суммы

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

Схема 1: аванс 20%

Клиент платит 20% от суммы контракта в качестве аванса, а оставшиеся 80% — после поставки. Тогда:
Аванс = Сумма контракта / 6
Потому что 20% от суммы с НДС 20% — это 20/120, а 20/120 = 1/6.​

Схема 2: аванс 10% с НДС

Более сложный вариант: аванс 10% от суммы и дополнительно НДС 18% (по сути, расчёт идёт через налоговую базу). В этом случае:
Аванс = (Сумма * 10) / 110
Здесь 110 = 100% базы + 10% аванса, отсюда и деление на 110.​

Что должно совпадать

В нормальной ситуации выполняются такие условия:

• значение по счёту 62 (кредит) близко к рассчитанному авансу по одной из схем;

• значение по счёту 76 (дебет) примерно равно тому же авансу;

• если разница между расчётным значением и фактическими данными больше порога (например, 0,01 рубля для учёта округлений), это строка, которую нужно проверить.​

Часть 2: Архитектура Power Query‑скрипта

Входные данные

Скрипт ожидает на входе два и более файлов:

• оборотно‑сальдовую ведомость по счёту 62;

• оборотно‑сальдовую ведомость по счёту 76.​

Файлы — стандартные выгрузки из 1С в формате оборотно‑сальдовых ведомостей по соответствующим счетам.​

Этапы обработки данных

Скрипт работает в семь логических этапов:​

Загрузка и фильтрация файлов

• Находим в указанной папке все файлы с выгрузками по 62 и 76 счетам.

• Если выгрузок несколько, объединяем их в две большие таблицы: все 62‑е в одну, все 76‑е — в другую, даже если суммарно получится больше миллиона строк. Power Query спокойно переварит такой объём.​

  Раскрытие вложенных таблиц

• Раскрываем вложенные таблицы из файлов, чтобы получить обычные плоские таблицы с данными.​

  Очистка служебных строк

• Удаляем итоговые строки типа «Итого» и «Ответственный:», а также прочие служебные строки, которые не являются операциями.​

  Парсинг дат и извлечение периода

• Приводим даты к нормальному виду: убираем лишние служебные символы и время, оставляем только дату.

• Из данных определяем год, месяц и последний день месяца, за который сформирована выгрузка.​

  Объединение таблиц 62 и 76

• Соединяем таблицы по контрагенту: для каждог�� контрагента видим связку данных по 62 и 76 счетам.​

  Расчёт расхождений по двум схемам

• Считаем ожидаемый аванс по двум схемам: 20% и 10/110.

• Сравниваем расчётные значения с данными по счёту 76.​

  Фильтрация ошибок

• Оставляем только строки, где расхождения превышают порог (учитываем только реально проблемные операции).​

Часть 3: Как работает скрипт

Подготовка файла

Для начала положите файл «Сверка.xlsx» в одну папку с выгрузками из 1С. Скрипт берёт путь к этой папке прямо из книги Excel.​

На листе «Инфо» используется формула, которая определяет путь к текущему файлу на вашем компьютере:
=ЛЕВСИМВ(ЯЧЕЙКА("имяфайла");НАЙТИ("[";ЯЧЕЙКА("имяфайла"))-2)

Power Query забирает этот путь из таблицы:

// получаем путь к папке с выгрузками Путь = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]{0}[Путь],

По этому пути скрипт находит все выгрузки из 1С в папке, в названии которых есть «Оборотно‑сальдовая ведомость по счету 62» и нужное расширение:

#"фильтр по 62 ведомости" = Table.SelectRows( Source, each Text.Contains([Name], "Оборотно-сальдовая ведомость по счету 62") and Text.Contains([Extension], ".xls") ),

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

//Оставляем 1 и 7 столбец #"Removed Columns" = Table.SelectColumns( #"Expanded Table Column1", { Table.ColumnNames(#"Expanded Table Column1"){0}, Table.ColumnNames(#"Expanded Table Column1"){6} } ),

Затем убираем лишние строки: первые строки из шапки и строки, где в числовом столбце стоит текст (они появляются при объединении нескольких книг Excel):

// убираем верхние строки до слова "Кредит" во втором столбце
#"Removed Top Rows" =
Table.PromoteHeaders(
Table.Skip(
#"Removed Columns",
List.PositionOf(
Table.Column(
#"Removed Columns",
Table.ColumnNames(#"Removed Columns"){1}
),
"Кредит"
)
)
),

После этого данные очищаются: выделяем дату, приводим типы, убираем визуальный шум. Аналогичные шаги выполняются для ведомости по счёту 76.​

Объединение и фильтрация операций

Когда обе таблицы подготовлены, объединяем их по контрагенту:

Source = Table.NestedJoin( #"Сверка 62", {"Контрагенты"}, #"Сверка 76", {"Контрагенты"}, "76", JoinKind.LeftOuter ),

Определяем месяц, год и последний день месяца по максимальной дате в таблице:

// Определяем месяц и год максимальной даты в исходной таблице
month = Date.Month(List.Max(Source[Дата])), year = Date.Year(List.Max(Source[Дата])), end_of_month = Date.EndOfMonth(#date(year, month, 1)), end_of_month_5_days = Date.AddDays(end_of_month, -5),

Дальше отфильтровываем лишние строки: итоги, пустые строки и ответственных лиц, оставляя только реальные операции:

// Оставляем только строки-операции:
// 1) служебные строки Поступление/Корректировка
// 2) убираем пустые строки, "Итого" и строки с ответственным
#"Фильтр по типу строки" = Table.SelectRows( #"Заполнение вниз", each ( Text.StartsWith([Контрагенты], "Поступление") or Text.StartsWith([Контрагенты], "Корректировка") ) and [Контрагенты] <> null and [Контрагенты] <> "Итого" and [Контрагенты] <> "Ответственный:" ),

Поиск расхождений

Дальше считаем расхождения. Сначала проверяем схему 20% ([Кредит] / 6), затем схему 10/110. Если ни одна схема не попадает в допуск, строку помечаем как «Требуется проверка».​

// Считаем расхождение: // сначала схема 20% ([Кредит] / 6), затем схема 10/110, // если ни одна не попадает в допуск — помечаем как "Требуется проверка" #"ищем расхождения" = Table.AddColumn( #"Строки с примененным фильтром", "Расхождения", each if [76.Дебет] <> null then let calc20 = [Кредит] / 6, calc10 = [Кредит] * 10 / 110, diff20 = calc20 - [76.Дебет], diff10 = calc10 - [76.Дебет], threshold = #"Порог сходимости" in if Number.Abs(diff20) <= threshold then Number.Round(diff20, 2) else if Number.Abs(diff10) <= threshold then Number.Round(diff10, 2) else "Требуется проверка" else null ),

На последнем шаге убираем строки без проблем, оставляя только те, где действительно есть, что проверить:

// Убираем строки, где Ошибки = 0 или пусто — оставляем только реально проблемные
#"Убрал 0 и null" =
Table.SelectRows(
#"ищем расхождения",
each [Расхождения] <> null and [Расхождения] <> 0
)

Интерпретация результата

В итоговой таблице вы видите только строки, где есть расхождения по авансам — то есть те операции, которые требуют внимания. Число в столбце «Расхождения» показывает величину несоответствия между расчётным авансом по одной из схем (20% или 10/110) и фактическими данными по счёту 76.​

Если в ячейке указано число, это величина расхождения в рублях с учётом заданного порога: чем оно ближе к нулю, тем более корректно проведён платеж. Если вместо числа стоит текст «Требуется проверка», это означает, что ни одна из схем не уложилась в допустимое отклонение — бухгалтеру нужно открыть эту операцию в 1С и проверить документы, даты и суммы вручную.​

Заключение

Power Query — мощный инструмент, который способен сэкономить часы рутинной работы на сверке. Скрипт из этой статьи загружает выгрузки, очищает данные, сравнивает счета 62 и 76 по заданным схемам и выводит только проблемные строки, которые действительно нужно проверить.​

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

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

 Исходный файл можно скачать с GitHub