Реестр должников в Excel
Реестр должников в Excel помогает видеть, кто должен компании деньги, по какому договору и когда ожидается оплата. В такой таблице удобно проверять остатки, выделять просроченные платежи и записывать следующий шаг по работе с клиентом.
Чтобы реестр показывал правильные суммы, одной колонки «Долг» недостаточно. Нужны срок оплаты, сумма обязательства и уже полученные деньги. Для рассрочки каждый взнос лучше учитывать отдельной строкой: иначе будущие платежи легко смешать с просрочкой.
К статье прилагается Excel-шаблон на 200 платежей с формулами и примером заполнения. Ниже разберем его структуру, порядок ведения и ограничения.
Для чего нужен реестр должников
Это внутренняя рабочая таблица с данными из договоров, счетов, графиков и документов об оплате. Она помогает ответить на практические вопросы: сколько денег еще должны клиенты, какая часть уже просрочена и с кем нужно связаться сегодня.
В реестр можно включать покупателей с рассрочкой, заказчиков с отсрочкой оплаты и другие обязательства с определенными суммами и сроками. Здесь рассматривается простой учет основной суммы платежей. Проценты, неустойки, возвраты и взаимозачеты требуют отдельного расчета и документального основания.
Сохраняйте в таблице и закрытые платежи. Их можно скрыть фильтром, а при сверке снова показать. Однако сам реестр не заменяет договоры, подтверждения оплат и историю расчетов.
Что считать одной строкой
Одна строка – одно обязательство с конкретной суммой и сроком оплаты. Это может быть счет, разовый платеж по договору или отдельный взнос рассрочки.
Например, по одному договору клиент должен внести 10 000 рублей 10 сентября и еще 10 000 рублей 12 октября. В реестре будут две строки с разными сроками. Если сентябрьский взнос оплачен частично, просрочка рассчитывается только по его невнесенной части.
Не добавляйте рядом с этими строками еще одну строку на полную стоимость договора: при суммировании обязательство будет учтено дважды. Первоначальный взнос тоже учитывайте последовательно: либо включите его отдельным полностью оплаченным платежом, либо ведите только оставшийся график.
Каждой строке присвойте уникальный ID, например Д-01/1 и Д-01/2. Фамилия клиента не подходит для идентификации платежа: у одного человека может быть несколько покупок, а у разных людей — одинаковые фамилии.
Какие столбцы включить в таблицу
В приложенном шаблоне используется один лист «Реестр». Сверху расположены дата расчета и общие итоги, ниже – таблица. Первая строка с данными имеет номер 10.
Столбец | Поле | Что указывать или проверять |
A | ID платежа | Уникальный код конкретного обязательства или взноса |
B | Клиент | Имя или название; при совпадениях добавьте код клиента |
C | Договор или счет | Номер документа и, при необходимости, номер взноса |
D | Срок оплаты | Дату, когда должен быть внесен этот платеж |
E | Сумма | Полную сумму конкретного платежа в рублях |
F | Оплачено | Всего получено в счет этой строки на дату расчета |
G | Остаток | Невнесенную часть платежа; рассчитывается формулой |
H | Просрочено | Остаток по платежу, срок которого уже прошел |
I | Дней просрочки | Сколько календарных дней прошло после срока при наличии долга |
J | Статус | Оплачен ли платеж, наступил ли срок, есть ли просрочка |
K | Следующий шаг | Конкретное действие по клиенту и нужную дату |
Суммы вводите числами, без слова «рублей» внутри ячейки. Для дат используйте формат даты Excel. Текст «в конце месяца» вместо точного срока не позволит правильно рассчитать просрочку.
Если реестром пользуются несколько сотрудников, можно добавить ответственного и отдельную дату следующего действия. Контакты лучше хранить в одном актуальном справочнике клиентов, чтобы не исправлять телефон во всех строках одного договора.
Попробуйте и оцените, как легко можно вести учет задолженностей покупателей в нашем сервисе!
Продажа товара
ДемоТак вы можете продавать товары в рассрочку!
Это была пробная продажа. Чтобы вести учет клиентов, долгов и платежей, зарегистрируйтесь в Дебиторке.
Есть вопросы? Debitorka.biz@yandex.ru
График платежей
| № | Дата платежа | Сумма, ₽ | Остаток, ₽ |
|---|
Скачать реестр должников в Excel
В готовом шаблоне настроены фильтры, закрепление заголовков и первых двух столбцов, расчет остатка и просрочки. Поля для ручного ввода выделены светло-желтым, расчетные столбцы G:J – светло-серым. Просрочка подсвечивается, а повторяющиеся ID платежей и переплата отмечаются для проверки.
В строках 10–14 находятся условные данные. Чтобы начать работу, очистите содержимое A10:F14 и K10:K14, затем внесите свои записи. Столбцы G:J очищать не нужно: в них уже стоят формулы до строки 209 включительно.
В ячейке B4 установлена дата примера – 17 сентября 2026 года. Замените ее датой, на которую вы проверяете расчеты. Обновляйте дату и сведения об оплатах вместе. В столбце F укажите 0, если денег по платежу еще не получено.
Если после начала заполнения строки появилась ошибка #Н/Д, проверьте наличие числовых сумм и корректной даты. В незавершенной строке расчет может быть недоступен. Сначала заполните обязательные поля, затем оценивайте итоги.
Шаблон не получает банковские поступления автоматически и не ведет отдельный журнал переводов. Сумма в столбце F – накопленная оплата по конкретному платежу, которую нужно сверять с подтверждающими документами.
Пример заполнения реестра
Допустим, на 17 сентября 2026 года в таблице есть пять платежей четырех условных клиентов. Суммы указаны без процентов и неустоек.
Платеж и срок | Сумма | Оплачено | Остаток |
Клиент А, взнос 1, 10.09.2026 | 10 000 ₽ | 4 000 ₽ | 6 000 ₽ |
Клиент А, взнос 2, 12.10.2026 | 10 000 ₽ | 0 ₽ | 10 000 ₽ |
Клиент Б, 17.09.2026 | 15 000 ₽ | 0 ₽ | 15 000 ₽ |
Клиент В, 04.09.2026 | 8 000 ₽ | 8 000 ₽ | 0 ₽ |
Клиент Г, 14.09.2026 | 12 000 ₽ | 0 ₽ | 12 000 ₽ |
Итого | 55 000 ₽ | 12 000 ₽ | 43 000 ₽ |
Общий остаток составляет 43 000 рублей. Просрочены только 6 000 рублей по первому платежу клиента А и 12 000 рублей клиента Г – всего 18 000 рублей.
Платеж клиента Б приходится на дату расчета, поэтому в примере получает статус «Срок сегодня». Второй взнос клиента А относится к будущей дате. По клиенту В остаток равен нулю, и текущая просрочка не показывается.
В итоге в таблице четыре непогашенных платежа у трех клиентов. Количество строк с долгом и количество должников – разные показатели: два взноса одного человека не превращают его в двух клиентов.
Какие формулы использовать
Ниже показана основная логика для полностью заполненной строки 10. В готовом файле добавлены проверки пустых и некорректных значений. Примеры записаны для русской версии Excel с разделителем «;»; названия функций и разделители зависят от языка и региональных настроек.
Остаток платежа в G10
=МАКС(E10-F10;0)
Из суммы обязательства вычитается учтенная оплата. Отрицательный долг не показывается. Если получено больше суммы платежа, отдельно проверьте переплату и ее назначение — нулевой остаток сам по себе этот вопрос не решает.
Просроченная сумма в H10
=ЕСЛИ(И(G10>0;D10<$B$4);G10;0)
В просрочку включается положительный остаток, если срок оплаты раньше даты расчета в B4. Поэтому платеж, срок которого наступает сегодня, еще не считается просроченным. В столбец D нужно заранее внести корректный срок с учетом условий договора и применимых правил его определения.
Количество дней просрочки в I10
=ЕСЛИ(H10>0;$B$4-D10;0)
Если есть просроченная сумма, из даты расчета вычитается срок платежа. По погашенной строке результат равен нулю: формула показывает текущую просрочку, а не продолжительность всех задержек в прошлом.
Общие суммы
Остаток по реестру считается формулой =СУММ(G10:G209), просрочка – =СУММ(H10:H209). Эти итоги включают все строки указанного диапазона, даже если часть записей скрыта фильтром.
Для оперативного учета в B4 можно использовать =СЕГОДНЯ(). Функция возвращает текущую дату при пересчете книги; порядок ее работы описан в справке Microsoft. При этом оплаченные суммы по-прежнему нужно обновлять самостоятельно.
Для отчета на конкретную дату удобнее оставить B4 фиксированной. Простая замена даты на прошлый месяц не восстановит прошлое состояние долга, если в F уже внесены более поздние поступления. Для истории сохраняйте снимки реестра или ведите отдельный журнал операций с датами.
Как вести реестр после каждой оплаты
Сначала определите, к какому договору и платежу относятся полученные деньги. Затем обновите сумму в F. Если клиент внес 4 000 рублей, а позже еще 2 000 рублей по тому же взносу, в ячейке должно стать 6 000 рублей, а не 2 000.
Поступившую оплату отражайте в F, сохраняя исходную сумму обязательства в E. Одновременное уменьшение E и увеличение F на одни и те же деньги занизит остаток. Если один перевод относится к нескольким взносам, распределите его по соответствующим строкам в установленном порядке. Общая распределенная сумма должна совпадать с учтенным поступлением.
После сверки проверьте остаток, статус и следующий шаг. Вместо комментария «обещал» лучше записать «20 сентября проверить оплату 6 000 рублей». Обещание клиента не меняет автоматически договорный срок в D. Если стороны оформили новый график, обновите реестр и сохраните основание изменения.
Договоры и платежные документы храните так, чтобы по номеру в C можно было быстро найти подтверждение. Назначьте одного ответственного за актуальность рабочего файла и определите, кто может вносить изменения.
Как настроить фильтры и расширить таблицу
Если вы создаете собственный реестр, выделите диапазон с заголовками и используйте команду «Главная — Форматировать как таблицу». Проверьте границы диапазона и наличие строки заголовков.
Для ежедневной работы полезны фильтры по клиенту, положительному остатку и статусу «Просрочено». Сортировку выполняйте для всей таблицы, чтобы суммы и комментарии остались связаны со своими платежами.
В приложенном файле заранее подготовлены 200 строк. Если этого стало недостаточно, расширьте таблицу, скопируйте формулы G:J и форматирование в новые строки. Затем увеличьте диапазоны общих итогов и правила проверки повторяющихся ID. Проверьте расчет на одной новой записи, прежде чем переносить большой список.
Ошибки при ведении списка должников
- Повтор одного обязательства. В таблицу попали и все взносы, и полная стоимость договора. Итоговый долг оказался завышен.
- Будущие платежи названы просрочкой. Для оценки просрочки используйте срок каждого взноса, а не только дату оформления покупки.
- Последняя оплата заменила накопленную сумму. Столбец F хранит все учтенные поступления по строке на дату расчета.
- Перенос срока сделан со слов клиента. План контакта записывайте отдельно от действующего срока оплаты.
- Формула затерта числом. После такой правки строка перестает обновляться при изменении исходных данных.
- Работа идет в нескольких копиях. Сотрудники могут отправлять сообщения по разным остаткам. Определите один актуальный файл и порядок резервного копирования.
Ограничьте доступ к реестру сотрудниками, которым он нужен для работы. Не размещайте файл с именами, контактами и суммами долгов в открытом доступе. Для обсуждения примера используйте обезличенную копию.
Когда удобнее перейти от Excel к программе
Excel подходит для простого реестра, который регулярно обновляет ответственный сотрудник. Потребность в программе возникает, когда ручная сверка занимает много времени, у клиентов несколько рассрочек, а менеджерам нужны согласованные данные об оплатах и напоминаниях.
В сервисе «Дебиторка» можно вести графики рассрочек, учитывать платежи и остатки, просматривать историю покупок клиента. Есть работа с магазинами и сотрудниками, шаблоны договоров и WhatsApp-уведомления по платежам.
При переходе сначала сверьте действующие договоры, графики и остатки. Затем организуйте внесение новых оплат и настройте сообщения под свои условия работы. Качество учета зависит от того, насколько своевременно в нем отражаются поступления и изменения договоренностей.
Частые вопросы
Можно ли вести одну строку на клиента?
Для краткой сводки – да. Но если у клиента несколько сроков оплаты, нужен подробный список обязательств. Иначе по общей сумме нельзя понять, какая часть просрочена и сколько нужно внести сейчас.
Нужно ли удалять полностью оплаченные строки?
Для текущей работы их удобно скрыть фильтром. Сохраненные записи помогают при сверке, но для полной истории отдельных поступлений нужен журнал оплат и подтверждающие документы.
Можно ли начислять пени по колонке с днями просрочки?
Приложенный шаблон этого не делает. При частичных оплатах и изменении суммы долга расчет по периодам может отличаться от простого умножения текущего остатка на число дней. Условия и основания начисления нужно проверять отдельно.