Таблица учёта оплат клиентов: шаблон Excel с формулами
Шаблон Excel, в котором по каждому счёту видны остаток, дни просрочки и статус, а сводка показывает, кто из клиентов платит в срок.
Таблица учёта оплат клиентов — журнал выставленных счетов: в каждой строке сумма, срок оплаты, сколько пришло и когда. Из этих полей формулы считают остаток (сумма минус оплачено), дни просрочки (сегодня минус срок, а у оплаченного счёта — дата оплаты минус срок) и статус счёта. На втором листе те же счета сведены по клиентам: сколько каждому выставлено, сколько он должен сейчас и на сколько дней в среднем опаздывает с оплатой.
Оплаченные счета из таблицы не удаляют: по ним видно, кто из клиентов платит в срок и насколько опаздывают остальные. Этим журнал оплат отличается от реестра дебиторской задолженности, где ведут только открытые долги и раскладывают их по возрасту.
Какие столбцы нужны в таблице учёта оплат
В шаблоне к статье каждый счёт занимает одну строку, столбцов четырнадцать. Восемь заполняют данными счёта и оплаты, три серых считаются формулами (остаток, дни просрочки и статус), ещё три нужны для работы с должником.
Данные счёта
- Клиент — название, одинаковое во всех строках: по нему собирается сводка. Записи ООО «Ромашка» и «Ромашка» ООО сводка посчитает как двух разных клиентов.
- ИНН — 10 цифр у организации, 12 у ИП. По ИНН клиента находят в учётной программе, даже если название записано иначе.
- Счёт № и дата счёта — как в самом документе.
- Сумма, ₽ — сколько клиент должен заплатить по счёту, с НДС, если он есть.
- Срок оплаты — конкретная дата. Если в договоре написано «в течение 10 рабочих дней с даты счёта», дату считают один раз и вписывают в ячейку.
Данные оплаты
- Оплачено, ₽ — всё, что пришло по этому счёту. Второй частичный платёж прибавляют к первому, строка остаётся одна.
- Дата оплаты — день поступления денег, при нескольких платежах — дата последнего. Без неё задержка по оплаченному счёту не посчитается.
Работа с должником
- Обещанная дата — день, который назвал клиент. Срок оплаты при этом не меняют, и просрочка считается от него; зачем так делать, разобрано в статье про статус оплаты счёта.
- Комментарий — короткая запись с датой: «27.10 звонок бухгалтеру, ждут акт сверки».
- E-mail клиента — адрес, на который клиенту уходят счета и напоминания.
Формулы: остаток, дни просрочки и статус
Формулы ниже — для строки 6, первой строки данных в шаблоне, в русской версии Excel: аргументы разделяет точка с запятой. Столбцы: E — сумма, F — срок, G — оплачено, H — дата оплаты, I — остаток, J — дней просрочки, K — статус, L — обещанная дата. Формулу вводят в строку 6 и протягивают вниз.
Остаток
- I6: =ЕСЛИ(E6="";"";E6-G6)
Пока сумма не вписана, ячейка остаётся пустой. Отрицательный остаток означает переплату.
Дни просрочки через СЕГОДНЯ()
У открытого счёта просрочка растёт каждый день, у оплаченного — останавливается на дате оплаты. Одна формула считает оба случая:
- J6: =ЕСЛИ(ИЛИ(E6="";F6="");"";ЕСЛИ(I6>0;МАКС(0;СЕГОДНЯ()-F6);ЕСЛИ(H6="";"";МАКС(0;H6-F6))))
Если остаток больше нуля, из сегодняшней даты вычитается срок, а МАКС(0;…) ставит 0, пока срок не прошёл. В день срока формула показывает 0, на следующий день — 1. У оплаченного счёта формула берёт разницу между датой оплаты и сроком: оплата 05.10 при сроке 21.09 — это 14 дней, и число больше не меняется.
СЕГОДНЯ() берёт текущую дату при каждом пересчёте листа, поэтому дни обновлять руками не нужно. В справке Microsoft описаны две ловушки. Если у ячейки был формат «Общий», Excel сам сменит его на «Дата», и вместо числа дней вы увидите дату — задайте столбцу формат «Числовой». Если дата не обновляется, включите «Файл → Параметры → Формулы → Параметры вычислений → Автоматически». Третья ловушка встречается в выгрузках: даты, вставленные текстом, прижаты к левому краю ячейки, и статус с подсветкой по ним считаются неверно. Такие даты перевведите.
Статус через ЕСЛИ
Вложенная ЕСЛИ проверяет условия по порядку и возвращает первое подошедшее значение:
- K6: =ЕСЛИ(E6="";"";ЕСЛИ(I6<=0;ЕСЛИ(J6="";"оплачен";ЕСЛИ(J6>0;"оплачен с опозданием";"оплачен в срок"));ЕСЛИ(F6="";"нет срока";ЕСЛИ(J6=0;ЕСЛИ(G6>0;"частично оплачен";"ожидает оплаты");ЕСЛИ(L6="";"просрочен";ЕСЛИ(L6>=СЕГОДНЯ();"обещал оплатить";"обещание нарушено"))))))
Значения статуса:
- ожидает оплаты — срок ещё не прошёл, денег нет;
- частично оплачен — пришла часть суммы, срок ещё не прошёл;
- просрочен — срок прошёл, остаток больше нуля, в том числе после частичной оплаты;
- обещал оплатить — счёт просрочен, клиент назвал дату, и она ещё не прошла;
- обещание нарушено — обещанная дата прошла, а остаток не закрыт;
- оплачен в срок — остаток нулевой, деньги пришли не позже срока;
- оплачен с опозданием — остаток нулевой, деньги пришли после срока;
- оплачен и нет срока — сигналы дозаполнить строку: не внесена дата оплаты или срок.
Статус пересчитывается сам, когда вы вносите оплату или проходит срок.
Подсветка для контроля сроков оплаты
Хватает четырёх правил условного форматирования: открыли файл — и по цвету видно, кому звонить. Первые три правила задают на диапазон A6:M205 через «Главная → Условное форматирование → Создать правило → Использовать формулу для определения форматируемых ячеек», формулу вписывают в поле «Форматировать значения, для которых следующая формула является истинной». Знак $ перед буквой закрепляет столбец, и каждая строка проверяется по своим ячейкам.
- Просрочка, красная заливка строки: =И(ЕЧИСЛО($J6);$I6>0;$J6>0)
- До срока три дня или меньше, жёлтая заливка: =И(ЕЧИСЛО($I6);$I6>0;$F6>=СЕГОДНЯ();$F6-СЕГОДНЯ()<=3)
- Счёт оплачен, серый шрифт: =И($E6<>"";$I6<=0)
- Обещание нарушено, красный жирный шрифт в столбце L (правило только для L6:L205): =И(ЕЧИСЛО($I6);$I6>0;$L6<>"";$L6<СЕГОДНЯ())
Формула в пустой строке возвращает пустой текст, а Excel при сравнении считает любой текст больше числа. Без ЕЧИСЛО условие «больше нуля» выполнилось бы, и красной стала бы вся незаполненная часть таблицы.
Три дня — запас, чтобы клиент успел поставить платёж. Если ваши клиенты платят по графику раз в неделю, поставьте 7. Красное и жёлтое правила не пересекаются: первое срабатывает после срока, второе — до него и в сам день срока.
Сводка по клиентам
Второй лист показывает, как платит каждый клиент. В столбец A вписывают клиентов так же, как на листе «Оплаты», остальное считают формулы. Формулы даны для клиента в ячейке A5, диапазоны — строки 6–205 листа «Оплаты».
- Счетов: =СЧЁТЕСЛИ(Оплаты!$A$6:$A$205;A5)
- Выставлено, ₽: =СУММЕСЛИ(Оплаты!$A$6:$A$205;A5;Оплаты!$E$6:$E$205)
- Оплачено, ₽: та же формула по столбцу G, остаток — выставлено минус оплачено.
- В т. ч. просрочено, ₽: =СУММЕСЛИМН(Оплаты!$I$6:$I$205;Оплаты!$A$6:$A$205;A5;Оплаты!$I$6:$I$205;">0";Оплаты!$J$6:$J$205;">0")
- Оплачено в срок, %: счета со статусом «оплачен в срок», делённые на все оплаченные: =ЕСЛИОШИБКА(СЧЁТЕСЛИМН(Оплаты!$A$6:$A$205;A5;Оплаты!$K$6:$K$205;"оплачен в срок")/(СЧЁТЕСЛИМН(Оплаты!$A$6:$A$205;A5;Оплаты!$K$6:$K$205;"оплачен в срок")+СЧЁТЕСЛИМН(Оплаты!$A$6:$A$205;A5;Оплаты!$K$6:$K$205;"оплачен с опозданием"));"—")
- Средняя задержка оплаты, дней — по оплаченным счетам: =ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Оплаты!$J$6:$J$205;Оплаты!$A$6:$A$205;A5;Оплаты!$I$6:$I$205;"<=0";Оплаты!$J$6:$J$205;">=0");"—")
Под списком клиентов стоит строка «Не вошло в сводку»: общий остаток с листа «Оплаты» минус остатки по клиентам. Если там не ноль, какой-то клиент записан с опечаткой или его нет в списке.
Подойдёт и сводная таблица: «Вставка → Сводная таблица», в строки — «Клиент», в значения — «Сумма, ₽», «Оплачено, ₽» и «Остаток, ₽». Клиентов она соберёт сама, но обновлять её нужно вручную: «Данные → Обновить всё». Разбивку долга по возрасту и долю просрочки в портфеле считают в отчёте по дебиторской задолженности.
Пример: таблица оплат на 30 октября
Пример с условными данными. Компания работает с четырьмя клиентами, сегодня пятница, 30.10.2026. В журнале семь счетов:
- «Ромашка», № 101 от 01.09: 120 000 ₽, срок 15.09, оплачено 120 000 ₽ 14.09 — 0 дней, оплачен в срок.
- «Ромашка», № 118 от 01.10: 95 000 ₽, срок 15.10, оплаты нет, клиент обещал заплатить 05.11 — 15 дней, обещал оплатить.
- «Лютик», № 104 от 07.09: 64 000 ₽, срок 21.09, оплачено 64 000 ₽ 05.10 — 14 дней, оплачен с опозданием.
- «Лютик», № 121 от 05.10: 80 000 ₽, срок 19.10, 20.10 пришло 30 000 ₽, оставшиеся 50 000 ₽ обещали до 27.10 — 11 дней, обещание нарушено.
- «Клевер», № 109 от 14.09: 42 000 ₽, срок 28.09, оплаты нет — 32 дня, просрочен; в комментарии записан спор о количестве в накладной.
- «Клевер», № 125 от 12.10: 150 000 ₽, срок 02.11 — 0 дней, ожидает оплаты, строка жёлтая: до срока три дня.
- «Василёк», № 127 от 19.10: 36 000 ₽, срок 05.11, оплачено 10 000 ₽ 26.10 — остаток 26 000 ₽, частично оплачен.
Сводка по тем же строкам:
- «Ромашка»: выставлено 215 000 ₽, остаток 95 000 ₽, весь просрочен; в срок оплачено 100 %, средняя задержка 0 дней.
- «Лютик»: выставлено 144 000 ₽, остаток 50 000 ₽, весь просрочен; в срок — 0 %, средняя задержка 14 дней.
- «Клевер»: выставлено 192 000 ₽, остаток 192 000 ₽, просрочено 42 000 ₽; оплаченных счетов пока нет.
- «Василёк»: выставлено 36 000 ₽, остаток 26 000 ₽, просрочки нет.
- Итого: выставлено 587 000 ₽, оплачено 224 000 ₽, остаток 363 000 ₽, в т. ч. просрочено 187 000 ₽.
Самый большой остаток у «Клевера», но 150 000 ₽ из 192 000 ₽ ещё в сроке, а просроченный счёт стоит из-за спора о количестве: сначала нужна сверка по накладной. Звонить первым нужно «Лютику»: прошлый счёт он оплатил на 14 дней позже срока, а теперь не выполнил обещание по остатку. «Ромашка» просрочила больше всех, 95 000 ₽, но прошлый счёт оплатила в срок и назвала дату, поэтому 05.11 достаточно посмотреть выписку.
Кто и когда обновляет таблицу
Контроль оплат клиентов работает, когда у каждого события в таблице есть исполнитель и срок. Вариант для компании, где счета выставляет менеджер, а выписку смотрит бухгалтер:
- Выставили счёт — менеджер в тот же день добавляет строку: клиент, ИНН, номер, дата, сумма, срок, e-mail.
- Пришли деньги — бухгалтер каждый рабочий день разносит выписку: сумму в «Оплачено», дату в «Дата оплаты». Платёж с непонятным назначением или один платёж на несколько счетов сначала разбирают по правилам сверки оплат со счетами, потом вносят.
- Поговорили с клиентом — менеджер в тот же день ставит обещанную дату и пишет комментарий. Если клиент перенёс дату, прежнюю записывают в комментарий («обещали 27.10, не оплатили»), а в столбец ставят новую.
- Понедельник — руководитель открывает сводку, сортирует клиентов по просрочке и решает, кому звонит менеджер, а с кем он разговаривает сам.
- Последний рабочий день месяца — бухгалтер сравнивает итог столбца «Остаток» с задолженностью покупателей в учётной программе. Разницу ищут среди оплат, которые не попали в таблицу, и счетов, которых в ней нет.
Храните один файл в общей папке или облаке и не рассылайте копии по почте. Чтобы формулы не стёрли случайно, включите «Рецензирование → Защитить лист»: ячейки для ввода в шаблоне останутся открытыми.
Когда таблица не успевает за счетами
Шаблон работает, пока его вовремя заполняют. С ростом числа счетов появляются четыре слабых места:
- Оплата видна только после разноски. Таблица узнаёт о деньгах, когда бухгалтер внёс выписку. Если разноска отстаёт на два дня, менеджер звонит клиенту, который заплатил позавчера.
- Напоминает человек. Красная строка показывает просрочку, но письмо клиенту кто-то должен написать и отправить. Когда красных строк несколько десятков, напоминания становятся отдельной ежедневной работой.
- История перезаписывается. В строке одна дата оплаты и одна обещанная дата. Три частичных платежа и два нарушенных обещания остаются только в комментарии, и сводка их не учитывает.
- Файл расходится с учётом. Кто-то вписал число поверх формулы или отсортировал только часть столбцов, и итог перестаёт сходиться с учётной программой.
Пора искать другой инструмент, если разноска и напоминания отнимают больше времени, чем разговоры с должниками, или о просрочке вы узнаёте только на сверке в конце месяца.
Открытые счета из шаблона можно загрузить в «Сальдо» одним файлом. Отфильтруйте на листе «Оплаты» строки с остатком, скопируйте их вместе с шапкой в новый файл Excel и сохраните его в формате CSV. Заголовки шаблона мастер импорта узнает сам, поэтому не переименовывайте и не переставляйте столбцы. Для напоминаний нужен e-mail клиента — заполните последний столбец. «Комментарий» мастер предложит в поле «Назначение», а этот текст клиент увидит в счёте: чтобы заметки для себя туда не попали, выберите для колонки «— не импортировать». После загрузки по счетам идут напоминания до срока и после, дожим просрочки по сценарию, а статус каждого счёта виден в реестре. Новые счета удобнее выставлять уже в сервисе со ссылкой на оплату: оплата по ней закрывает счёт сама, без разноски.
Частые вопросы
Какие столбцы нужны в таблице учёта оплат клиентов?
Как посчитать дни просрочки оплаты в Excel?
Как подсветить просроченные счета в таблице?
Зачем хранить в таблице оплаченные счета?
Клиент оплатил два счёта одним платежом — как внести?
Как начать учёт оплат клиентов в шаблоне
Заведите в шаблон все неоплаченные счета и оплаченные за последний квартал: тогда сводка с первого дня покажет долю оплат в срок и среднюю задержку по каждому клиенту. Даты оплат возьмите из банковской выписки или учётной программы. Затем впишите на лист «Сводка по клиентам» всех клиентов с листа «Оплаты» и проверьте, что в строке «Не вошло в сводку» ноль.