Таблица учёта оплат клиентов: шаблон Excel с формулами

Шаблон Excel, в котором по каждому счёту видны остаток, дни просрочки и статус, а сводка показывает, кто из клиентов платит в срок.

10 мин чтения

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

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

Какие столбцы нужны в таблице учёта оплат

В шаблоне к статье каждый счёт занимает одну строку, столбцов четырнадцать. Восемь заполняют данными счёта и оплаты, три серых считаются формулами (остаток, дни просрочки и статус), ещё три нужны для работы с должником.

Данные счёта

  • Клиент — название, одинаковое во всех строках: по нему собирается сводка. Записи ООО «Ромашка» и «Ромашка» ООО сводка посчитает как двух разных клиентов.
  • ИНН — 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 через «Главная → Условное форматирование → Создать правило → Использовать формулу для определения форматируемых ячеек», формулу вписывают в поле «Форматировать значения, для которых следующая формула является истинной». Знак $ перед буквой закрепляет столбец, и каждая строка проверяется по своим ячейкам.

  1. Просрочка, красная заливка строки: =И(ЕЧИСЛО($J6);$I6>0;$J6>0)
  2. До срока три дня или меньше, жёлтая заливка: =И(ЕЧИСЛО($I6);$I6>0;$F6>=СЕГОДНЯ();$F6-СЕГОДНЯ()<=3)
  3. Счёт оплачен, серый шрифт: =И($E6<>"";$I6<=0)
  4. Обещание нарушено, красный жирный шрифт в столбце 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. В журнале семь счетов:

  1. «Ромашка», № 101 от 01.09: 120 000 ₽, срок 15.09, оплачено 120 000 ₽ 14.09 — 0 дней, оплачен в срок.
  2. «Ромашка», № 118 от 01.10: 95 000 ₽, срок 15.10, оплаты нет, клиент обещал заплатить 05.11 — 15 дней, обещал оплатить.
  3. «Лютик», № 104 от 07.09: 64 000 ₽, срок 21.09, оплачено 64 000 ₽ 05.10 — 14 дней, оплачен с опозданием.
  4. «Лютик», № 121 от 05.10: 80 000 ₽, срок 19.10, 20.10 пришло 30 000 ₽, оставшиеся 50 000 ₽ обещали до 27.10 — 11 дней, обещание нарушено.
  5. «Клевер», № 109 от 14.09: 42 000 ₽, срок 28.09, оплаты нет — 32 дня, просрочен; в комментарии записан спор о количестве в накладной.
  6. «Клевер», № 125 от 12.10: 150 000 ₽, срок 02.11 — 0 дней, ожидает оплаты, строка жёлтая: до срока три дня.
  7. «Василёк», № 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 клиента — заполните последний столбец. «Комментарий» мастер предложит в поле «Назначение», а этот текст клиент увидит в счёте: чтобы заметки для себя туда не попали, выберите для колонки «— не импортировать». После загрузки по счетам идут напоминания до срока и после, дожим просрочки по сценарию, а статус каждого счёта виден в реестре. Новые счета удобнее выставлять уже в сервисе со ссылкой на оплату: оплата по ней закрывает счёт сама, без разноски.

Частые вопросы

Какие столбцы нужны в таблице учёта оплат клиентов?

Клиент, ИНН, номер и дата счёта, сумма, срок оплаты, оплачено и дата оплаты — их вносят руками. Остаток, дни просрочки и статус считают формулы. Для работы с должником добавляют обещанную дату, комментарий и e-mail клиента.

Как посчитать дни просрочки оплаты в Excel?

Для неоплаченного счёта — СЕГОДНЯ() минус срок оплаты внутри МАКС(0;…), чтобы до срока не было отрицательных чисел. Для оплаченного — дата оплаты минус срок: число дней фиксируется и больше не растёт. Обе ветки собирают в одну формулу через ЕСЛИ по остатку, столбцу задают формат «Числовой».

Как подсветить просроченные счета в таблице?

Через условное форматирование с формулой: «Главная → Условное форматирование → Создать правило → Использовать формулу для определения форматируемых ячеек». Для просрочки подойдёт =И(ЕЧИСЛО($J6);$I6>0;$J6>0), где I — остаток, J — дни просрочки; ЕЧИСЛО не даёт окрасить пустые строки. Вторым правилом выделяют счета, до срока которых три дня или меньше.

Зачем хранить в таблице оплаченные счета?

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

Клиент оплатил два счёта одним платежом — как внести?

Разнесите сумму по счетам, на которые указывает назначение платежа или договорённость с клиентом: в каждой строке поставьте в «Оплачено» свою часть и одну и ту же дату оплаты. Если из назначения непонятно, какие счета оплачены, сначала уточните у клиента.

Как начать учёт оплат клиентов в шаблоне

Заведите в шаблон все неоплаченные счета и оплаченные за последний квартал: тогда сводка с первого дня покажет долю оплат в срок и среднюю задержку по каждому клиенту. Даты оплат возьмите из банковской выписки или учётной программы. Затем впишите на лист «Сводка по клиентам» всех клиентов с листа «Оплаты» и проверьте, что в строке «Не вошло в сводку» ноль.