Excel для бухгалтера: 4 готовые учебные таблицы

Что входит в набор и как с ним работать

Реестр первичных документов: статус, срок и повторная запись

Банк против учетной выгрузки: ключ и исключения

Дебиторская задолженность: срок, возрастная группа и контроль итога

Две выгрузки: нормализация типов и поиск расхождений

Как адаптировать столбцы и не разрушить формулы

Мини-экзамен: найдите четыре ошибки

Что перенести из упражнений в рабочую практику

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

• Результат 1: найти повторную запись и отделить просроченные документы.
• Результат 2: сопоставить банковские операции с учетом и получить список исключений.
• Результат 3: распределить дебиторскую задолженность по срокам и сверить общий остаток.
• Результат 4: привести коды к одному типу и найти содержательное расхождение между двумя выгрузками.

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

В каждой книге есть лист «Упражнение» с исходными данными и формулами и лист «Эталон» с паспортом шаблона. Формулы построены на функциях, доступных в Excel 2016 и более новых версиях. Открытие и пересчет дополнительно проверены в установленном Excel 16.0. Динамические массивы, FILTER и обязательная зависимость от XLOOKUP не используются.

Что входит в набор и как с ним работать

Оптимальный порядок работы одинаков для всех четырех файлов. Сначала откройте лист «Упражнение» и не переходите к эталону. Просмотрите исходные столбцы, найдите ячейки с формулами и сверьте контрольный показатель. Затем определите строку, из-за которой контроль не равен нулю или ожидаемому итогу. Только после этого открывайте «Эталон».

Желтым обозначены исходные данные, светлым фоном — расчетные столбцы. Ошибка выделяется отдельно, но ее причина не объясняется на листе упражнения. Так сохраняется смысл мини-теста: недостаточно увидеть красную ячейку, нужно понять, какое правило сработало.

Реестр первичных документов: статус, срок и повторная запись

Первый файл имитирует краткий реестр первичных документов. В нем есть ID строки, дата, номер, контрагент, сумма, срок оплаты и дата фактической оплаты. Расчетные столбцы определяют статус, составляют ключ документа и отмечают повторный ввод.

Скачать упражнение «Реестр первичных документов»

Статус рассчитывается относительно фиксированной даты контроля. Это важно: учебный результат не изменится завтра только потому, что книгу открыли в другой день. В рабочем файле такую дату можно вынести в отдельное поле отчетного периода или заменить на TODAY(), если нужен ежедневный скользящий контроль.

=IF(G12<>"","Оплачено",IF(F12<$B$10,"Просрочено","Ожидается"))

Для поиска повторов использован расширяющийся диапазон в COUNTIFS. Первый документ с уникальным ключом не отмечается, а вторая запись того же документа получает статус «ДУБЛЬ». Поэтому контроль возвращает одну повторную строку, а не две строки одной пары.

=IF(COUNTIFS($I$12:I12,I12)>1,"ДУБЛЬ","")

Паспорт шаблона

• Исходные столбцы: ID, дата, номер, контрагент, сумма, срок оплаты, дата оплаты.
• Формула: вложенный IF для статуса; составной ключ; COUNTIFS для повторов.
• Контроль: 1 повторная запись и 1 просроченный неоплаченный документ.
• Ограничение: ключ не проверяет юридическую силу документа и не учитывает исправления, сторно и разные правила нумерации поставщиков.
• Признак ошибки: строка PR-006 содержит «ДУБЛЬ».

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

Банк против учетной выгрузки: ключ и исключения

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

Скачать упражнение «Сверка банка с учетом»

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

=A12&"|"&B12&"|"&C12&"|"&ROUND(D12,2)

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

=IF(COUNTIFS($L$12:$L$15,E12)=1,"СОПОСТАВЛЕНО","ИСКЛЮЧЕНИЕ")

Паспорт шаблона

• Исходные столбцы: дата, документ, направление и сумма отдельно для банка и учета.
• Формула: составной ключ и COUNTIFS для подсчета точных совпадений.
• Контроль: 4 сопоставленные строки и 1 исключение.
• Ограничение: простое точное соответствие не распределяет суммы и не находит близкие совпадения.
• Признак ошибки: PAY-105 отсутствует в учетном наборе.

Практическая сверка заканчивается не цветным статусом, а списком действий. Для каждого исключения нужны причина, ответственный и решение: провести документ, исправить ключ, подтвердить комиссию, отнести платеж на выяснение или зафиксировать допустимую разницу. Именно поэтому формула должна отделять исключения, а не скрывать их через IFERROR с нулевым результатом.

Дебиторская задолженность: срок, возрастная группа и контроль итога

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

Скачать упражнение «Контроль дебиторской задолженности»

Сначала стоит рассчитывать остаток без категорий:

=E12-F12

Дни просрочки не должны быть отрицательными. Если срок еще не наступил или долг уже закрыт, результат равен нулю. Вложенный IF после этого распределяет открытые остатки по группам «1–30», «31–60», «61–90» и «Более 90 дней».

=IF(G12=0,0,MAX(0,$B$10-D12))

Намеренная проблема здесь не в арифметике. Для AR-004 срок оплаты раньше даты самого счета. Без отдельного контроля формула добросовестно покажет просрочку, хотя исходные данные уже нелогичны. Это полезный принцип для любой аналитики: правильная формула не исправляет ошибочные исходные данные.

Паспорт шаблона

• Исходные столбцы: счет, контрагент, дата счета, срок оплаты, сумма, оплачено.
• Формула: вычитание для остатка, MAX для дней, вложенные IF для возрастной группы.
• Контроль: общий открытый остаток 28 500,00 грн и 1 ошибка дат.
• Ограничение: упражнение не учитывает валюту, кредит-ноты, несколько оплат одного счета и резерв сомнительных долгов.
• Признак ошибки: в AR-004 срок 05.09.2026 предшествует дате счета 15.09.2026.

Две выгрузки: нормализация типов и поиск расхождений

Четвертое упражнение показывает проблему, которая часто выглядит как «Excel не видит одинаковые коды». В одном файле код может быть числом 1001, в другом — текстом 001001. Если сразу запускать поиск, результат будет зависеть от типа ячейки и ведущих нулей.

Скачать упражнение «Сверка двух выгрузок»

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

="ID-"&TEXT(VALUE(TRIM(A12&"")),"000000")

После нормализации сумма из второго набора подтягивается через INDEX и MATCH. Это осознанная альтернатива XLOOKUP, которая работает в Excel 2016. Для Excel 365 XLOOKUP может сделать формулу короче, но не меняет главный принцип: тип и структура ключа должны быть одинаковыми с обеих сторон.

=IFERROR(INDEX($K$12:$K$16,MATCH(B12,$J$12:$J$16,0)),"Нет ключа")

Паспорт шаблона

• Исходные столбцы: код, номенклатура и сумма в каждом из двух наборов.
• Формула: TEXT, VALUE и TRIM для ключа; INDEX+MATCH для поиска; IF для сравнения.
• Контроль: 1 несоответствие суммы и 0 отсутствующих ключей.
• Ограничение: правило шести знаков создано для упражнения; в рабочих данных нужно отдельно согласовать длину, ведущие нули, допустимое отклонение и обработку дублей.
• Признак ошибки: ID-001003 имеет 7 500,00 грн в наборе A и 7 550,00 грн в наборе B.

Как адаптировать столбцы и не разрушить формулы

Самое опасное изменение — не переименование заголовка, а вставка нового столбца внутри логического блока без проверки ссылок. После любой адаптации выполните короткую процедуру.

1. Сохраните неизменный контрольный файл. Работайте с копией, чтобы иметь эталон формул и результатов.
2. Определите роль нового столбца. Это исходное поле, расчет или контроль? Исходные данные не должны случайно содержать формулы, а расчетные столбцы — значения, введенные вручную.
3. Добавляйте поле рядом со связанными данными. Например, валюту — рядом с суммой, а ответственного — рядом со статусом исключения.
4. Скопируйте формулу из соседней корректной строки. Проверьте первую, среднюю и последнюю строку диапазона. Особенно внимательно посмотрите на знаки доллара в абсолютных и смешанных ссылках.
5. Расширьте все контрольные диапазоны. Если данных стало больше, COUNTIFS, SUM и MATCH не должны заканчиваться на прежней последней строке.
6. Снова воспроизведите намеренную ошибку. Дубликат должен дать «ДУБЛЬ», отсутствующая банковская пара — «ИСКЛЮЧЕНИЕ», нелогичная дата — «ОШИБКА ДАТЫ», разные суммы — «НЕСООТВЕТСТВИЕ».

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

Пять сигналов, что адаптация сломала файл

• новая строка не влияет на контрольный итог;
• формула в середине столбца отличается от соседних без объясненной причины;
• пустой ключ превращается в ноль и выглядит как корректное совпадение;
• дата отображается как текст и не участвует в вычитании;
• после сортировки ответы перестают соответствовать своим ID.

Мини-экзамен: найдите четыре ошибки

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

1. В реестре первичных документов найдите повторную запись. Объясните, почему отмечена только вторая строка пары.
2. В сверке банка найдите операцию без пары в учете. Проверьте, что остальные четыре строки сопоставлены.
3. В дебиторской задолженности найдите нелогичную пару дат и независимо пересчитайте общий остаток.
4. В двух выгрузках отделите различие типов кода от настоящего расхождения сумм.

Эталон для самопроверки

• Упражнение 1: PR-006 повторяет ПН-903 поставщика В; повторных строк — 1; просроченный документ — PR-002.
• Упражнение 2: PAY-105 от 05.09.2026 на 1 800,00 грн не имеет пары; исключений — 1; сопоставлений — 4.
• Упражнение 3: в AR-004 срок оплаты раньше даты счета; контрольный остаток — 28 500,00 грн; ошибок дат — 1.
• Упражнение 4: ID-001003 имеет разницу 50,00 грн; несоответствий — 1; отсутствующих ключей — 0.

Если формулы еще приходится восстанавливать методом проб, стоит пройти темы последовательно — от адресации ячеек до сложных формул, сортировки и фильтров. Для этого проверьте, что сейчас содержит актуальная программа обучения Microsoft Excel, и сопоставьте ее с собственными пробелами, а не только с названиями тем.

Что перенести из упражнений в рабочую практику

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

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