Excel для бухгалтера: сверки, ВПР, сводные по оборотке и выгрузки из 1С

Новичкам кажется, что главное — выучить сложные формулы. На деле больше всего времени уходит до первой формулы: оборотка из 1С приходит с многоуровневой шапкой, объединёнными ячейками, строками промежуточных итогов и суммами, которые Excel считает текстом. Пока выгрузка не превращена в ровную таблицу, где одна строка — одна запись, ВПР ничего не находит, а СУММ молча пропускает половину строк.

Где Excel помогает бухгалтеру, а где лучше остаться в 1С

Проводки, закрытие месяца и отчётность живут в учётной системе: перенесённые в Excel, они становятся второй версией правды, которую никто не обновляет. Excel берёт то, для чего в 1С нет удобного отчёта: сверку с контрагентом, приславшим акт в PDF или своём шаблоне, анализ дебиторки за несколько периодов, независимый пересчёт начислений перед закрытием, поиск задвоенного платежа в выписке. Если обе стороны работают в 1С и обмениваются документами через ЭДО, акт сверки проще сформировать в программе — об этом соседняя статья о работе в 1С:Бухгалтерии.

Как подготовить выгрузку из 1С к работе

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

Затем проверяют, что суммы — числа: если в строке состояния при выделении столбца нет «Суммы», Excel видит текст. Обычно помогают «Текст по столбцам» или ЗНАЧЕН, но иногда ошибка #ЗНАЧ! остаётся, а СЖПРОБЕЛЫ ничего не меняет. Причина описана в справке Microsoft по функции СЖПРОБЕЛЫ: функция удаляет только обычный пробел с кодом 32 из семибитной таблицы ASCII, а неразрывный пробел с кодом 160 сама по себе не трогает. В числах вида «1 250 000,00» разделителем разрядов бывает именно он, особенно если данные скопированы из браузера, PDF или интернет-банка. Формула =КОДСИМВ(ПСТР(A2;2;1)) на четырёхзначной сумме покажет код разделителя. Если там 160, помогает =ЗНАЧЕН(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);»»);» «;»»)).

Учебный пример: сверка с покупателем по двум актам

Ситуация учебная. Поставщик сверяет расчёты с покупателем на 30 сентября. По данным бухгалтерии покупатель должен 418 600 ₽, в его акте — 345 720 ₽. Документов за квартал около двухсот.

Бухгалтер оформляет свою выгрузку и акт покупателя «умными» таблицами «Наши» и «Их» со столбцами: дата, номер, дебет, кредит. У второй стороны всё зеркально: наш дебет — их кредит. Номера приводит к одному виду: у поставщика «00ПР-000417», у покупателя «417». Префикс отрезает ТЕКСТПОСЛЕ (в старых версиях — ПСТР с ПОИСК), ведущие нули убирает ЗНАЧЕН. Затем в таблицу «Наши» добавляет три столбца:

=ПРОСМОТРX([@Номер]; Их[Номер]; Их[Кредит]; "нет у них")

=ЕСЛИ(ЕЧИСЛО([@[Сумма у них]]); ОКРУГЛ([@Дебет]-[@[Сумма у них]]; 2); [@Дебет])

=СЧЁТЕСЛИМН(Их[Номер]; [@Номер])

Первая подтягивает сумму из акта покупателя, вторая считает разницу, третья показывает задвоенные номера. Без ПРОСМОТРX используют ВПР с последним аргументом ЛОЖЬ: без него функция ищет приблизительное совпадение и молча возвращает сумму соседнего документа. Ту же проверку повторяют из таблицы «Их», чтобы найти платежи, которых нет у поставщика.

Фильтр по ненулевой разнице оставляет три строки. УПД № 417 от 29 сентября на 23 600 ₽ покупатель принял в октябре. Платёж на 50 000 ₽ он отразил 30 сентября, а на счёт поставщика деньги пришли 1 октября. В УПД № 402 у покупателя 11 800 ₽ вместо 11 080 ₽ — опечатка. Контроль: 23 600 + 50 000 − 720 = 72 880 ₽, ровно разница между 418 600 и 345 720. Если сумма расхождений не равна разнице сальдо, найдено не всё.

Почему суммы сравнивают только после ОКРУГЛ

Проверка «итог расхождений равен разнице сальдо» иногда даёт ЛОЖЬ при одинаковых на экране числах. Excel хранит числа в двоичном виде, и многие десятичные дроби в нём точно не записываются. В статье поддержки Microsoft о вычислениях с плавающей запятой формула =(43,1-43,2)+1 при пятнадцати знаках показывает 0,899999999999999 вместо 0,9. Поэтому всё, что сравнивают на равенство, сначала округляют до копеек.

Вторая ловушка — формат: ячейка без копеек показывает 1 300, а хранит 1 299,87, и сумма столбца расходится с суммой видимых чисел. Параметр «Задать точность как на экране», по той же статье, действует на всю книгу и после сохранения необратимо отрезает знаки. Надёжнее ОКРУГЛ в каждой итоговой формуле.

Сводная по оборотке: чей долг растёт

Бухгалтер сохраняет ОСВ по счёту 62 в разрезе контрагентов за каждый квартал, ставит четыре очищенные выгрузки одна под другой и добавляет столбец «Квартал». По этой таблице строится сводная: в строках — контрагенты, в столбцах — кварталы, в значениях — дебетовое сальдо на конец периода. Авансы, то есть кредитовое сальдо, выводят отдельным полем: если вычесть их из дебета, покупатель с долгом по одному договору и авансом по другому будет выглядеть чистым.

Условное форматирование выделит тех, чей долг рос три квартала подряд. Итог сводной по каждому кварталу сверяют с ОСВ в 1С, а после замены данных нажимают «Обновить все» — сама сводная не пересчитывается. Если отчёт собирают каждый месяц, склейку лучше поручить Power Query; об этом соседняя статья об Excel для аналитиков.

Расчёты по зарплате и налогам

Здесь Excel не заменяет 1С:ЗУП, а проверяет её: бухгалтер выгружает начисления, считает налог своей формулой и сравнивает с программой. По п. 6 ст. 52 Налогового кодекса налог исчисляется в полных рублях: меньше 50 копеек отбрасывается, 50 копеек и больше округляется до рубля. ОКРУГЛ с нулём знаков работает так же: =ОКРУГЛ([@База]*Ставка;0), где «Ставка» — именованная ячейка, а не число внутри сотни формул. Расхождение с программой на рубль обычно означает, что округляли в разных местах: от общей базы или по каждому начислению.

С чего начать и в каком порядке осваивать

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

1. Форматы, сортировка, фильтры, удаление дублей, «умные» таблицы.
2. Очистка: «Текст по столбцам», ЗНАЧЕН, ПОДСТАВИТЬ, неразрывный пробел.
3. ВПР с ЛОЖЬ или ПРОСМОТРX, СЧЁТЕСЛИМН, СУММЕСЛИМН — на сверке двух актов.
4. ОКРУГЛ и сравнение только округлённых сумм.
5. Сводные по ОСВ и контроль итогов с 1С.
6. Power Query и макросы — когда отчёт повторяется каждый месяц.

Первые четыре шага можно пройти на своих выгрузках за прошлый квартал. Если основы шаткие, начните с общего курса, а бухгалтерские задачи добавьте потом. Онлайн-курс Нетологии «Excel: от основ до анализа данных» рассчитан примерно на два месяца: 27 часов теории и 37 часов практики, формулы, Power Query, Power Pivot, сводные и три проекта, по итогам — удостоверение о повышении квалификации. Бухгалтерских кейсов в программе нет, сверку придётся отрабатывать самостоятельно.

Упражнения и самопроверка

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

Отрабатывать всё на готовых бухгалтерских шаблонах можно на онлайн-курсе Eduson Academy «Excel и Google-таблицы для бухгалтера». По описанию на Учи.Онлайн, там 44 интерактивных урока и 25 заданий: очистка данных, Power Query, ВПР и СУММЕСЛИ, сценарные расчёты и шаблоны инвентаризации, страховых взносов и учёта доходов и расходов. Срок указан по-разному — две и три недели, тему итоговой работы школа не публикует.

Где новички ошибаются

Самая дорогая ошибка — ВПР без последнего аргумента: формула подставляет чужую сумму без всякого сигнала. Вторая — дубли ключей: номера документов обнуляются каждый год, и прошлогодний УПД № 417 попадёт в сверку рядом с нынешним, если ключ не включает год. Третья — сортировка одного столбца вместо всей таблицы. Реже замечают ручные правки поверх формул: цифра, вбитая, чтобы отчёт сошёлся, через месяц покажет старое значение при новых данных. Необъяснённое расхождение оставляют отдельной строкой с комментарием, а не прячут.

Такие ошибки быстрее всего ловит человек, который проверяет ваши файлы. На онлайн-курсе Бруноям «Excel и Google-таблицы для бухгалтеров» наставник проверяет практические работы и обычно отвечает в течение суток: 28 академических часов в 34 уроках, от базовых функций до сводных и макросов, плюс блок по Google Таблицам. Названия модулей общие, бухгалтерские кейсы в описании не показаны — уточните их у школы.

Как навык влияет на доход

Отдельной надбавки «за Excel» у бухгалтеров обычно нет, сопоставимых открытых данных о ней найти не удалось. Для ориентира: по данным агрегатора вакансий «Город работ», в сентябре 2026 года медианная зарплата в вакансиях бухгалтера по России — 65 000 ₽, средняя — 73 869 ₽. Это предложения работодателей по всем уровням опыта, а не фактические выплаты. Влияние Excel идёт через задачи: тот, кто сверяет двести документов за час, а не за день, ведёт больше контрагентов и чаще получает участки с анализом, а на аутсорсинге — больше клиентов.

Как выбрать обучение

Смотрите, на каких данных идут задания: учебные таблицы без единой ошибки не научат разбирать выгрузку из 1С с текстом вместо чисел. Проверьте, разбирает ли задания человек и в какой версии Excel записаны уроки — ПРОСМОТРX и ТЕКСТПОСЛЕ есть не везде. Удостоверение по Excel — повышение квалификации по инструменту, а не подготовка бухгалтера.

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