Как собрать таблицу личных расходов с ИИ и проверить план-факт
Категории, переводы, возвраты и контроль суммы. Запрос для Excel с прозрачными правилами вместо красивого, но неверного итога.
В банковской выписке есть покупки, возвраты и перевод между вашими счетами. Если назвать всё расходами, итог будет выглядеть внушительно — и неверно. Разберём, как сначала определить смысл операций, а затем построить таблицу, которую можно обновлять и проверять каждый месяц.
Подготовьте данные и правила
Для начала достаточно даты, описания, суммы и типа операции. Выберите период и валюту. Если используете банковскую выгрузку, оставьте только сведения, необходимые для задачи: полные реквизиты и лишние персональные данные не помогают категоризации. Сохраните исходник и работайте с отдельной копией.
- Покупки считать расходами.
- Переводы между собственными счетами исключать из расходов, сохраняя отдельной группой.
- Возврат относить к исходной категории, если связь подтверждена.
- Неопределённые операции не распределять молча.
- Даты и суммы хранить в подходящих типах, не как декоративный текст.
Проверяем план и факт на небольшом примере
| Категория | План | Факт | Факт минус план |
|---|---|---|---|
| Еда | 15 000 | 16 500 | +1 500 |
| Транспорт | 4 000 | 3 500 | −500 |
| Дом | 6 000 | 5 500 | −500 |
| Итого | 25 000 | 25 500 | +500 |
Перерасход — 500 ₽, или 2% от плана 25 000 ₽. Это не 2% от факта. Если в данных есть перевод на собственный счёт 10 000 ₽, он не должен увеличивать расход до 35 500 ₽. Возврат покупки на 500 ₽, наоборот, может уменьшить расходы соответствующей категории, если относится к этому периоду по выбранным правилам.
Проверка на восьми операциях
Возьмём отдельный учебный фрагмент выписки. Две покупки продуктов — 1 200 и 1 800 ₽. Две поездки — 500 и 700 ₽. Подарок — 2 000 ₽. Возврат части покупки — 500 ₽. Перевод на свой другой счёт — 10 000 ₽. Зарплата — 100 000 ₽.
Если считаем расходы за период с учётом этого возврата, получаем 1 200 + 1 800 + 500 + 700 + 2 000 − 500 = 5 700 ₽. Перевод внутри ваших счетов не включаем, зарплату отмечаем как доход. Это выбранная модель учёта, а не универсальное правило для всех денежных отчётов.
| Операция | Сумма | Роль в этой модели |
|---|---|---|
| Покупки продуктов | 3 000 ₽ | Расход |
| Поездки | 1 200 ₽ | Расход |
| Подарок | 2 000 ₽ | Расход |
| Возврат | 500 ₽ | Уменьшает связанные расходы |
| Перевод себе | 10 000 ₽ | Внутренний перевод |
| Зарплата | 100 000 ₽ | Доход |
Если возврат относится к покупке прошлого месяца, решите, хотите ли вы видеть текущий денежный поток или корректировать прежний период. Оба отчёта можно построить, но их итоги отвечают на разные вопросы. Запишите правило прямо в книге. Иначе каждая новая выгрузка будет менять не только данные, но и смысл показателя.
Восемь операций для проверкиCSV с исходными описаниями и видами операций — ожидаемые расходы 5 700 ₽.Запрос для таблицы Excel
Не доверяйте категории без контекста
Одно и то же описание операции может относиться к продуктам, бытовым товарам или подарку. Попросите показать сомнительные строки и уточните их вручную. Не требуется идеальная классификация каждой покупки: группа «Уточнить» полезнее уверенно неверного распределения.
Также решите, как учитывать совместные расходы. Если вы заплатили всю сумму, а другой человек вернул часть, таблица может показывать денежный поток или только вашу долю. Это разные модели. Выберите одну и запишите правило; иначе итог будет менять смысл от месяца к месяцу.
Обновление — отдельный тест книги
Добавьте в тестовой копии новую покупку на 300 ₽. Расходы этого учебного фрагмента должны стать 6 000 ₽. Затем измените её тип на внутренний перевод: сумма расходов должна вернуться к 5 700 ₽. Если меняется только общий итог операций, но не расходная сводка, формулы не связаны с классификацией так, как задумано.
Проверьте повторную загрузку той же выписки. Даты и суммы недостаточно для уверенного удаления дублей: две реальные покупки могут совпасть. Лучше использовать идентификатор операции, если он есть. Если нет — показать подозрительные пары и принять решение вручную. Отчёт без дублей ценен только тогда, когда вместе с ними не исчезли настоящие операции.
Отдельно смотрите группу «Уточнить». Неизвестную покупку не надо автоматически записывать в продукты только по названию магазина. У книги должен быть понятный остаток непроверенных строк; иначе аккуратные категории создадут ложное чувство завершённости.
Проверка перед использованием
- Число исходных операций совпадает с обработанными и исключёнными.
- Суммы по категориям складываются в итог.
- Переводы и возвраты не учтены повторно.
- Формулы обновляются при добавлении новой строки.
- Пропущенные даты, валюты и категории выделены.
Откройте файл в своём редакторе и добавьте одну тестовую строку. Если итог не изменился, диапазон формулы фиксирован слишком узко. Проверьте также фильтры: скрытые строки не должны незаметно менять смысл показанной суммы. Если валют несколько, храните отдельные итоги или явно согласуйте дату и источник курса.
Сохраните категории и правила в проекте Клава. На следующий месяц можно обновить операции и сравнить периоды. Перед расчётом проверяйте, что новые данные имеют ту же структуру и охватывают нужные даты.