Учёт дебиторской задолженности в Excel Таблица, которая сама считает просрочку
Вести учёт дебиторской задолженности в Excel можно, если таблица устроена правильно. Одна строка — один документ. Оплаты — отдельным листом, с привязкой к документу. Срок оплаты считается из отсрочки, а дни просрочки и старение — формулами от даты отчёта. Ниже всё это на примере, готовые формулы и бесплатный шаблон .xlsx. И честно о том, где Excel ломается.
Обновлено 1 октября 2026 · шаблон Excel с формулами · читать 11 минут
Учёт дебиторки в Excel: четыре листа вместо одного
Самая частая таблица выглядит так: список клиентов и колонка «Долг», которую бухгалтер раз в неделю переписывает из оборотки 1С. Сколько должны, видно. Какая накладная не оплачена, с какого дня просрочка и кто обещал заплатить — нет. А после каждой правки прошлое исчезает: вчерашний долг перезаписан сегодняшним.
Рабочая таблица хранит не остатки, а события. Отгрузили — строка в реестре отгрузок. Пришли деньги — строка в реестре оплат с номером документа, который они гасят. Остаток, срок и просрочку таблица считает сама. Так устроена и 1С, только без сроков и старения.
Достаточно четырёх листов. Справочник клиентов с отсрочкой и лимитом. Реестр отгрузок. Реестр оплат. Сводная, где всё складывается по клиентам и корзинам просрочки. Пятый лист — нерабочие дни, о нём ниже.
Если вы пока только выбираете, вести ли учёт в таблице или сразу в программе, сравнение вариантов — в статье о программе для учёта дебиторской задолженности.
Контрагент, БИН/ИИН, отсрочка по договору, кредитный лимит, ответственный и контакт. Одно написание названия на всю книгу.
Накладные и акты выполненных работ, по строке на документ. Срок, оплачено, остаток и просрочка — формулами.
Каждая оплата с датой, суммой и документом, который она гасит. Оплата без документа видна сразу.
Долг по клиентам, корзины 0–30, 31–60, 61–90, 90+, превышение лимита и нераспределённые оплаты.
Одна строка — один документ: какие колонки нужны
В реестр попадает каждая накладная и каждый акт выполненных работ, по которым покупатель платит с отсрочкой. Шесть колонок заполняете вы: дата документа, вид и номер, контрагент, договор, сумма и отсрочка в днях. Отсрочку шаблон подставляет из справочника клиентов. Если по конкретному документу договорились иначе, впишите число поверх формулы.
Дату берите ту же, от которой договор считает срок оплаты. Обычно это дата накладной или акта, но бывают договоры «с даты счёта-фактуры» или «с даты приёмки». Почему это важно и для просрочки, и для давности, разобрано в статье о дате возникновения дебиторской задолженности.
Если срока нет ни в договоре, ни в документе, не ставьте условные 30 дней молча. По закону такой долг покупатель обязан оплатить в разумный срок, а после требования — в течение семи дней (ГК РК, ст. 277, п. 2). Отправьте требование и считайте срок от него.
Документы в реестре не удаляйте, даже оплаченные. Статус «оплачен» и нулевой остаток — тоже история: по ней видно, как клиент платил раньше. Если в 1С долг виден только суммой по договору, неоплаченные накладные для реестра восстанавливают по карточке счёта 1210 — как, показано в статье о дебиторке в 1С для Казахстана.
Срок оплаты, остаток, просрочка и корзина: формулы для Excel
Формулы ниже — для русского Excel, где аргументы разделяют точкой с запятой. В английском те же функции называются WORKDAY, SUMIFS, IF и MAX. Дата отчёта лежит в одной ячейке на листе «Сводная», в шаблоне у неё имя ДатаОтчёта, и вся книга считает просрочку от неё. Поставите туда =СЕГОДНЯ() — таблица будет пересчитываться каждое утро.
Срок оплаты — не просто дата плюс отсрочка. Если последний день выпал на выходной или праздник, срок переносится на ближайший рабочий день. Накладная ТОО «Ертіс Агро» №388 от 14 августа с отсрочкой 30 дней: 13 сентября — воскресенье, значит, срок 14 сентября. На 29 сентября просрочка 15 дней, а не 16. В корзине разница в день редко что-то меняет, а в расчёте пени и в претензии меняет.
Функция РАБДЕНЬ сама пропускает субботы и воскресенья. Праздники и перенесённые выходные она возьмёт из списка на отдельном листе: его нужно заполнить один раз в начале года.
Если последний день срока приходится на нерабочий день, днем окончания срока считается ближайший следующий за ним рабочий день.
ГК РК, ст. 175
=РАБДЕНЬ(A6+F6-1; 1; Праздники)Дата документа + отсрочка; выходной переносится на ближайший рабочий день.=СУММЕСЛИМН(ОплатыСумма; ОплатыДокумент; B6; ОплатыКлиент; C6)Все оплаты этого клиента по этому документу. ОплатыСумма и соседи — именованные диапазоны листа «Оплаты».=E6-H6Сумма документа минус оплаты. Меньше нуля — переплата.=ЕСЛИ(I6<=0; ""; МАКС(0; ДатаОтчёта-G6))От срока оплаты до даты отчёта. В день срока просрочки ещё нет.=ЕСЛИ(J6=0; "не просрочено"; ЕСЛИ(J6<=30; "0–30"; ЕСЛИ(J6<=60; "31–60"; ЕСЛИ(J6<=90; "61–90"; "90+"))))Дальше сводная складывает остатки по корзинам функцией СУММЕСЛИМН.Частичная оплата, оплата без документа и аванс: где таблица ломается первой
Частичная оплата в такой таблице не проблема. ТОО «Сарыарқа Құрылыс» получило товар по накладной №214 на 2 600 000 ₸ и 5 августа заплатило 1 000 000 ₸ с номером накладной. Строка оплаты легла на документ, остаток 1 600 000 ₸, просрочка 71 день — корзина 61–90. Если оплата закрывает два документа, разбейте её на две строки.
Оплата без номера документа — вот где начинается ручная работа. ТОО «Балқаш Фуд» 25 сентября перевело 760 000 ₸ с назначением «оплата по договору поставки». Таблица не знает, что это за накладная, и честно показывает: долг 760 000 ₸ с просрочкой 20 дней и рядом нераспределённая оплата на те же деньги. Пока бухгалтер не впишет документ, клиент выглядит должником и рискует получить напоминание.
Правило разноски — как в 1С: оплата без документа закрывает самый старый неоплаченный документ того же договора, остаток переходит на следующий. При двадцати клиентах это пять минут в неделю. При двухстах — отдельная работа.
Аванс под будущую поставку таблице положить некуда: документа ещё нет. Держите его в оплатах без документа и разнесите в день отгрузки. Главное, не вычитать аванс одного клиента из долга другого. Чем аванс отличается от долга в учёте, разобрано в статье об авансах полученных и выданных.
Сводная: долг по корзинам и по каждому клиенту
Сводная отвечает на вопросы директора одной страницей. Сверху — старение: сколько не просрочено, сколько в каждой корзине и какая доля просрочки. Ниже — строка на каждого клиента: долг, просрочка по корзинам, нераспределённые оплаты и превышение лимита.
В примере долг по документам 6 296 000 ₸, просрочено 5 316 000 ₸. Но 760 000 ₸ из них — та самая оплата «Балқаш Фуд», которую ещё не разнесли. Реальный долг — 5 536 000 ₸. Эту строку сводная показывает отдельно, чтобы директор не звонил клиенту, который уже заплатил.
Красным в сводной подсвечен клиент сверх лимита. У «Сарыарқа Құрылыс» лимит 1 500 000 ₸, долг 1 600 000 ₸: следующую партию — только после оплаты или с разрешения директора. Как назначать такие лимиты, разобрано в статье о расчёте кредитного лимита покупателю.
Раз в месяц сверяйте итог с оборотно-сальдовой ведомостью по счёту 1210 за вычетом авансов. Не сходится — значит, в таблицу не попала отгрузка или оплата легла не на тот документ. Как читать корзины и что делать с каждой, подробно — в статье об отчёте по старению.
Шаблон учёта дебиторской задолженности в Excel: что внутри
Шесть листов, формулы протянуты на 500 строк. В реестрах и справочнике — строки-примеры с условными данными из этой статьи: на них видно, как срабатывают частичная оплата, перенос срока с выходного, оплата без документа и превышение лимита. Очистите примеры и вносите свои данные с 6-й строки.
Шаблон работает в Excel 2010 и новее и в LibreOffice. Макросов нет. Для импорта в debitorka.kz нужны пять колонок: контрагент, БИН/ИИН, сумма долга, срок оплаты и номер документа. В шаблоне всё это уже есть: остаток и срок — в «Отгрузках», БИН — в «Клиентах».
- 1Как пользоватьсяПорядок заполнения в семи шагах и что делать с оплатами без документа.
- 2СводнаяДата отчёта, старение по корзинам, долг по клиентам, лимиты и нераспределённые оплаты.
- 3ОтгрузкиРеестр накладных и актов: срок, оплачено, остаток, дни просрочки, корзина и статус.
- 4ОплатыКаждая оплата с документом. Колонка «Проверка» ловит оплаты без документа и опечатки в номере.
- 5КлиентыОтсрочка, лимит, ответственный и контакт. Выпадающий список в реестрах берёт клиентов отсюда.
- 6Нерабочие дниПраздники и перенесённые выходные года для переноса срока по ст. 175 ГК РК.
Когда Excel справляется, а когда пора искать замену
Таблица честно работает, пока в ней один автор и она обновляется чаще, чем клиенты платят. Дальше её точность держится на одном человеке.
- Клиентов с отсрочкой до пары десятков, отгрузки несколько раз в неделю.
- Таблицу ведёт один человек и обновляет оплаты каждый день.
- Клиенты пишут номер накладной в назначении платежа.
- Напоминания и звонки — дело одного менеджера, который всё помнит.
- Оплаты частями и без номеров каждый день: разноска съедает часы.
- Файл правят двое, и через месяц никто не знает, чья версия верная.
- Таблица расходится с 1С, и спор «где правда» идёт на каждой планёрке.
- Нужно напоминать клиентам до срока и помнить, кто что обещал.
Та же таблица, только её не нужно вести
debitorka.kz делает с данными 1С то же, что шаблон, но без ручного ввода. Внешняя обработка сама отправляет из 1С проводки по расчётам с покупателями, документы и договоры. Долг считается по каждому документу, оплаты разносятся сами, срок берётся из документа или договора, старение — от него.
Таблица отвечает на вопрос «сколько». Сервис отвечает и на «что дальше»: автонапоминания должникам до срока и после, обещания с датами, доска с этапами взыскания. Если 1С нет, долги загружаются из Excel по шаблону — это тариф «Старт» за 0 ₸.
На какие нормы опирается шаблон
Сам учёт дебиторки в Excel законом не регулируется: структура таблицы и корзины старения — практика управленческого учёта. Две нормы влияют на расчёт срока оплаты, они сверены с текстом Гражданского кодекса на adilet.zan.kz в редакции сентября 2026 года.
- Гражданский кодекс РК (Общая часть)ст. 175 — окончание срока в нерабочий день; ст. 277, п. 2 — срок исполнения, если он не указан: разумный срок, после требования — семь дней
Частые вопросы об учёте дебиторки в Excel
Как вести учёт дебиторской задолженности в Excel?
Держите отгрузки и оплаты на разных листах: одна строка — один документ, одна строка — одна оплата с номером документа. Остаток, срок оплаты, дни просрочки и корзину считайте формулами от даты отчёта, а итог по клиентам и корзинам собирайте на сводном листе.
Как посчитать просрочку дебиторской задолженности в Excel?
Дни просрочки = дата отчёта − срок оплаты, если остаток больше нуля: =ЕСЛИ(I6<=0;"";МАКС(0;ДатаОтчёта-G6)). Срок оплаты считайте как дату документа плюс отсрочку функцией РАБДЕНЬ, чтобы срок с выходного переносился на рабочий день.
Как сделать старение дебиторки в Excel?
Присвойте каждому документу корзину по дням просрочки: 0–30, 31–60, 61–90 или 90+. Затем сложите остатки по корзинам функцией СУММЕСЛИМН (SUMIFS). В шаблоне debitorka.kz корзины и сводная считаются сами.
Как учитывать частичную оплату в таблице?
Внесите оплату отдельной строкой с номером документа, который она гасит. Колонка «Оплачено» суммирует все оплаты по этому документу функцией СУММЕСЛИМН, остаток считается как сумма документа минус оплаченное.
Что делать с оплатой без номера накладной?
Разнести её на самый старый неоплаченный документ того же договора, как это делает 1С, или уточнить у клиента. Пока документ не указан, держите оплату отдельно: иначе клиент будет выглядеть должником.
Переносится ли срок оплаты, если он выпал на выходной?
Да. Если последний день срока приходится на нерабочий день, днём окончания срока считается ближайший следующий рабочий день (ГК РК, ст. 175). В Excel это делает функция РАБДЕНЬ со списком праздников.
Покажем, как ваша таблица выглядит, когда долги приходят из 1С
- 1Созвон на 30 минутПодключаем обработку к вашей 1С. Нет 1С — разбираем вашу таблицу.
- 2Долг по документамОстаток, срок и просрочка по каждой накладной. Оплаты без номера разнесены на самый старый документ.
- 3Сверка с вашей таблицейВидно, где Excel разошёлся с 1С и почему.