Сводная таблица Excel: просрочка счетов >10 дней по клиентам
Как создать сводную таблицу в Excel для анализа просрочки: расчет дней с Bill_Date, фильтр счетов старше 10 дней с Bal_Amt > 0, группировка по Party_Name с count Bill_No и суммами Bill_Amt, Amt_Paid, Bal_Amt. Пошаговое руководство по дебиторке.
Как создать сводную таблицу в Excel для расчета количества дней с даты счета (Bill_Date) до текущей даты, чтобы отфильтровать счета старше 10 дней с непогашенным остатком (Bal_Amt > 0)?
Данные содержат столбцы: Bill_No, Party_Name, Bill_Date, Bill_Amt, Amt_Paid, Bal_Amt.
Требуемый отчет должен группировать по Party_Name и показывать: количество счетов (Count of Bill_No), сумму Bill_Amt, Amt_Paid и Bal_Amt для просроченных счетов.
Сводная таблица в Excel идеально подходит для анализа просрочки: сначала добавьте вспомогательные столбцы с формулами дней просрочки (=СЕГОДНЯ()-Bill_Date) и отметкой просрочки (=ЕСЛИ(И(просрочка>10;Bal_Amt>0);“Да”;“Нет”)). Затем создайте сводную таблицу с группировкой по Party_Name, фильтром на просроченные счета (Bal_Amt>0 и >10 дней) и показом количества Bill_No, сумм Bill_Amt, Amt_Paid, Bal_Amt. Это даст четкий отчет по дебиторке за минуты — без лишних сортировок.
Содержание
- Что такое сводная таблица в Excel и зачем она нужна для анализа просрочки
- Как сделать сводную таблицу: подготовка данных с расчетом дней просрочки
- Добавление фильтра в сводной таблице для счетов старше 10 дней (Bal_Amt > 0)
- Группировка сводных таблиц по Party_Name и датам (Bill_Date)
- Настройка значений в сводной таблице: количество счетов, суммы Bill_Amt, Amt_Paid, Bal_Amt
- Группировка в сводной таблице Excel: по датам, числам и произвольно
- Примеры и файлы для скачивания: анализ дебиторки в Excel
- Источники
- Заключение
Что такое сводная таблица в Excel и зачем она нужна для анализа просрочки
Представьте: у вас куча счетов с датами Bill_Date, суммами Bill_Amt, оплатами Amt_Paid и остатками Bal_Amt. Ручной подсчет просрочки по каждому клиенту Party_Name? Забудьте. Сводная таблица — это инструмент, который мгновенно группирует данные, считает суммы, количества и фильтрует нужное.
Почему для просрочки? Она берет текущую дату (СЕГОДНЯ()), вычитает Bill_Date и показывает только счета старше 10 дней с Bal_Amt > 0. В итоге — отчет: кто сколько должен, сколько счетов просрочено. Планета Excel хвалит ее за простоту группировки по датам, а аудиторы из ISACA используют именно для дебиторки.
Но без подготовки данных ничего не выйдет. Давайте разберем по шагам.
Как сделать сводную таблицу: подготовка данных с расчетом дней просрочки
Сначала данные. Допустим, таблица выглядит так:
| Bill_No | Party_Name | Bill_Date | Bill_Amt | Amt_Paid | Bal_Amt |
|---|---|---|---|---|---|
| 001 | Клиент А | 2026-02-15 | 10000 | 4000 | 6000 |
| 002 | Клиент Б | 2026-02-20 | 15000 | 0 | 15000 |
Добавьте два столбца справа: “Дни_просрочки” и “Просрочка”.
-
В ячейке G2 (Дни_просрочки):
=СЕГОДНЯ()-C2(C — Bill_Date). Протяните вниз. Сегодня 2026-03-04, так что для 15 февраля выйдет ~17 дней. -
В H2 (Просрочка):
=ЕСЛИ(И(G2>10; F2>0); "Да"; "Нет")(F — Bal_Amt). Это отметит только нужные счета.
Зачем? Сводная таблица не считает даты сама — ей нужны готовые поля. Excel для всех советует именно так для группировки.
Теперь выделите всю таблицу (A:H), вкладка “Вставка” > “Сводная таблица”. Выберите новый лист. Готово — панель полей справа.
Просто, правда? Но без фильтра увидите все счета.
Добавление фильтра в сводной таблице для счетов старше 10 дней (Bal_Amt > 0)
Фильтр в сводной таблице — ключ к просрочке. Перетащите “Просрочка” в раздел “Фильтры”. Кликните стрелку у “Просрочка” > снимите “Нет” > OK. Теперь только “Да”.
Хотите точнее? Вместо “Да/Нет” сделайте числовой фильтр:
- Столбец “Overdue”:
=ЕСЛИ(И(G2>10; F2>0); 1; 0)
Перетащите Overdue в “Фильтры” > Значение фильтров > равно 1.
Или фильтр по Bal_Amt: перетащите Bal_Amt в “Фильтры” > Значение > больше 0. Комбинируйте с датами позже.
ISACA рекомендует это для аудита: сразу видны риски. А если данных много? Обновляйте таблицу Ctrl+Alt+F5 — фильтр сработает заново.
Что если клиенты меняются? Группировка спасет.
Группировка сводных таблиц по Party_Name и датам (Bill_Date)
Перетащите Party_Name в “Строки”. Сводная сгруппирует клиентов автоматически. Под каждым — счетчики.
Для дат: Bill_Date в “Строки” под Party_Name. Правой кнопкой на любой дате > “Группировать” > Месяцы/Кварталы. Получится просрочка по клиентам и периодам.
Хотите дни? Группировка по дням не нужна — у вас уже Дни_просрочки. Перетащите его в “Строки” или “Значения” (среднее/макс).
Планета Excel показывает: для цен шаг 30, для дат — месяцы. Здесь шаг для дней: правой кнопкой на Дни_просрочки > Группировать > От 0 до 100 шагом 10. Видишь: 0-10, 11-20 и т.д.
Быстро? Да. Но нужны суммы и counts.
Настройка значений в сводной таблице: количество счетов, суммы Bill_Amt, Amt_Paid, Bal_Amt
Перетаскиваем в “Значения”:
- Bill_No > Количество (Count of Bill_No) — число просроченных счетов.
- Bill_Amt > Сумма (Sum of Bill_Amt).
- Amt_Paid > Сумма.
- Bal_Amt > Сумма.
Правой кнопкой на значении > “Параметры полей значений” > Сумма/Количество. Формат: Числа > 0 знаков.
Пример отчета:
| Party_Name | Count Bill_No | Sum Bill_Amt | Sum Amt_Paid | Sum Bal_Amt |
|---|---|---|---|---|
| Клиент А | 2 | 25000 | 4000 | 21000 |
| Клиент Б | 1 | 15000 | 0 | 15000 |
Общий итог снизу. Excel для всех подчеркивает: группировка + значения = магия.
Проблемы? Если Bill_Date текст — преобразуйте в дату (Данные > Текст по столбцам).
Группировка в сводной таблице Excel: по датам, числам и произвольно
Стандартная группировка дат: месяцы, дни недели. Но для просрочки — по Дни_просрочки шагом 30 (1мес).
Правой кнопкой > Группировать > Числа > Шаг 30. Или вручную: выделите диапазон > Данные > Группировать.
Произвольная: несколько полей в Строки (Party_Name + Дни_просрочки). Развернуть/свернуть +/–.
Планета Excel дает пример: группировка продаж по ценам шагом. Аналогично для Bal_Amt >5000.
Обновление: если добавите строки, правой кнопкой на сводной > Обновить. Автообновление — через таблицу (Ctrl+T).
Хитрость: Срезы (Вставка > Срезы) для фильтров — клик и готово.
Примеры и файлы для скачивания: анализ дебиторки в Excel
Протестируйте на реальных данных. Скачайте шаблон: 1000 строк счетов, формулы готовы. Сводная покажет топ-должников.
Шаги для вашего файла:
- Подготовка (как выше).
- Сводная: Строки Party_Name, Фильтры Просрочка=Да, Значения как в таблице.
- Диаграмма: Вставка > Столбчатая от сводной.
В ISACA пример aging report — точно ваш случай. А Excel для всех имеет файл для группировки.
Не сработало? Проверьте: даты числом? Bal_Amt >0? Сегодняшняя дата обновляется автоматически.
Источники
- Excel для всех — Группировка данных в сводной таблице Excel с примерами файлов: https://www.excel-vba.ru/chto-umeet-excel/gruppirovka-dannyx-v-svodnoj-tablice/
- Планета Excel — Приемы группировки сводных таблиц по датам и числам: https://www.planetaexcel.ru/techniques/8/131/
- ISACA — Анализ просроченной дебиторки с помощью сводных таблиц в Excel: https://www.isaca.org/resources/isaca-journal/issues/2015/volume-1/audit-accounting-data-using-excel-pivot-tables-an-aging-of-accounts-receivable-example
Заключение
Сводная таблица превращает хаос счетов в четкий отчет по просрочке: группировка по Party_Name, фильтр >10 дней и Bal_Amt>0, counts и суммы — все на месте. Начните с формул Дни_просрочки и Просрочка, и дебиторка под контролем. Регулярно обновляйте — увидите тренды. Пробуйте на своих данных, и сэкономите часы рутины.
Сводная таблица в Excel предназначена для анализа данных с помощью группировки элементов.
Для группировки данных по датам или числам в сводной таблице:
- Правой кнопкой мыши кликните на поле в строках.
- Выберите «Группировать».
Это позволяет анализировать просроченные счета, группируя Bill_Date по месяцам или дням, с расчетом дней от даты счета до сегодня (TODAY() - Bill_Date).
Скачайте пример файла для практики группировки в сводных таблицах.
В сводных таблицах Excel группировка данных позволяет сгруппировать по датам (месяцы, кварталы) или числам (шаг, например, 30 для цен).
Для дат заказа в сводной таблице:
- Выделите поле
Bill_Date. - Правой кнопкой — «Группировать», укажите месяцы.
Это полезно для подсчета сумм Bal_Amt по просрочке старше 10 дней. Скачайте пример для группировки продаж по ценам и датам в сводной таблице.
Для анализа просрочки в Excel добавьте столбец Age = TODAY() - Bill_Date и Overdue = IF(AND(Age > 10, Bal_Amt > 0), 1, 0).
В сводной таблице:
- Строки:
Party_Name. - Фильтр:
Overdue = 1. - Значения:
Count Bill_No,Sum Bill_Amt,Amt_Paid,Bal_Amt.
Это отфильтрует счета старше 10 дней с остатком, сгруппировав по клиентам для дебиторки.
