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, і зіставте її з власними прогалинами, а не лише з назвами тем.

Що перенести з вправ у робочу практику

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

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