Проверка коммунальных начислений

Данные в проекте модифицированы/синтетические, структура полностью соответствует реальному использованию.

Описание

Файл Excel, который проверяет правильность данных из квитанций коммунальных услуг.

Инструмент позволяет выявлять ошибки в начислениях, сверяя объёмы потребления, тарифы и итоговые суммы с фактическими начислениями.

Проверка правильности коммунальных начислений

Задача

Разобраться и проверить правильность начислений в квитанциях за 2024-2025 годы. → Создать инструмент, куда можно один раз внести логику расчетов, а дальше только вставлять данные из квитанций.

Пожелания:

Минимум ручного ввода при использовании.
Автоматический расчет ожидаемого факта за нужный месяц.
Визуальная подсветка отклонений.
Видеть структуру платежа, не только итог (объём, тариф, итог)
Возможность сравнивать периоды и смотреть динамику.

Решение

Excel-файл с заранее реализованной логикой расчета по услугам. Ожидаемый факт считается автоматически в зависимости от выбранного периода.

Дополнительно используется отдельная программа: PDF-парсер ([1] - в файлах ниже), которая “вытаскивает” данные из квитанций в сводную таблицу.

Полученные из PDF-квитанций сводные данные используются для проверки

Извлечение данных из PDF-квитанций в сводную таблицу (работа PDF-парсера) → Копирование данных в таблицу для проверки

Результат

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

Реальная история

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

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

Переплаты уже не вернуть, но теперь оплаты соответствуют потреблению.

Бонусом: понимание из чего состоит каждая услуга, как она рассчитывается.

Как это работает

Проверка данных из квитанций

ДО ПРОВЕРКИ формируется расчетная база - Вносится информация об объекте, тарифы, показания.

ПРИ ПРОВЕРКЕ На главном экране:

1 - Выбирается месяц
2 - Вносятся данные из квитанций

Excel сам подтягивает тарифы, пересчитывает объем и показывает расхождения.

Для отображения корректных данных должны быть внесены данные об объекте, показания, действующие тарифы, период.

Логика работы файла

Структура файла:

1. Проверка CHECK - главный экран. Сюда вносятся данные из квитанций.

Здесь считается ожидаемый факт. С учетом индивидуальных правил расчета по каждой услуге.

2. Исходные данные info - информация об объекте недвижимости (заполняется 1 раз).

Влияет на расчеты (площадь, количество проживающих, наличие счетчиков).

3. Показания usage - фактические данные со счетчиков (добавляются ежемесячно).

4. Тарифы tariffs - утвержденные в регионе значения (добавляются ежегодно/раз в полугодие).

Из официальных источников. Также учитывается соцнорма, норматив ТКО и т.д.

Дополнительно:

5. Реестр data - история всех проверенных квитанций. База для аналитики.

6. Аналитика analytics - динамика расходов по месяцам, сравнение с прошлым годом, самые дорогие услуги в доле.

7. Справочники helper - вспомогательный лист для выпадающих списков и т.д.

Структура файла

Сбор и подготовка данных

Раньше я вносила цифры из квитанций вручную. Сейчас это автоматизировала:

Квитанции сохраняются в папку вида «квитанции/2026-02»
PDF-парсер проходит по всем файлам, определяет тип квитанции, извлекает объем, тариф, сумму
На выходе — готовая сводная таблица, которую остается скопировать в файл проверки.

Получение данных из квитанций

Сложности и решения

У каждой услуги своя логика расчета. Социальная норма, сезонные коэффициенты, предельные тарифы и т.п. И есть ограничения - например, в нашем доме отопление не проверишь (и некоторые другие услуги по общедомовым приборам).

→ Поэтому для каждой услуги прописана своя формула расчета.

Ручной ввод показаний и тарифов.

→ Сейчас тарифы и показания вынесены на отдельные листы для удобства работы с ними. Формулы обновлять не нужно, они это учитывают (завязаны на периоде). Но в дальнейшем это тоже хотелось бы автоматизировать.

Хотелось бы иметь что-то более универсальное. Но в каждом регионе свои “правила” - даже в РО и в городе, и в области некоторые тарифы отличаются. Поэтому чтобы сделать универсальный инструмент нужно учесть много параметров и нужно действительно разбираться во всех услугах, понимать как устанавливаются тарифы, кто их устанавливает, насколько сильно это меняется от региона к региону.

На будущее — Идеи развития

Автоматизировать внесение тарифов и показаний (через API сайтов поставщиков или парсинг сайта)
Учет пеней и перерасчетов
Добавить другие виды жилья и коммунальных услуг.
Добавить кнопку на листе проверки для автоматического переноса проверенных данных в реестр.