«Прейскурант» - перенос системы расчёта цен питомника
Разбор унаследованной сметы в excel и пересборка расчёта прейскуранта с «умной формулой», где цену можно задать вручную.
Клиент Питомник растений (внутренняя система заказчика)
Что было не так
Организация рассчитывает прейскурант на несколько лет вперёд. Цена каждой позиции считается из локальных смет (стоимость выращивания, заготовки, содержания).
Расчёт жил в excel и накапливался годами. К началу работы это был набор файлов с сотнями позиций, где прейскурант ссылался на 17 сметных листов, и как устроены эти связи и формулы, нигде не было описано. Люди, которые их закладывали, уже не участвовали в процессе.
Агроном при этом подготовил новую сметную базу на следующий период. В ней 36 детализированных смет с разбивкой по возрасту растений, породам (хвойные и лиственные) и типам черенкования, на месте прежних 17 общих.
Нужно было перевести расчёт на новую, более дробную базу, сохранить цены и не потерять ни одной позиции. Вручную это неподъёмно, и в документе, по которому организация работает годами, появились бы ошибки.
Что решили и почему
Решили пересобрать систему расчёта заново с прозрачной логикой.
Сначала нужно было понять старую конструкцию и разобрать, откуда берётся каждая цена (какие формулы и межлистовые ссылки за ней стоят). Без этого непонятно, что именно воспроизводить.
Дальше сопоставить старые позиции с новой сметной базой (какой из 36 новых смет теперь соответствует каждая позиция).
Главное решение - «умная формула» с ручным переопределением. Цена считается из сметы автоматически, но по любой позиции её можно задать вручную, и формулы при этом продолжают работать. Так система умеет и считать сама, и учитывать исключения.
Плюс дашборд, чтобы заказчик управлял базой сам, и документация, чтобы через три года систему снова мог понять любой новый человек.
Как делал
- 01
Разобрал старую систему. Выяснил, как связаны листы и какие формулы и межлистовые ссылки формируют цену. Написал скрипты извлечения и трассировки связей, потому что глазами это не читается.
- 02
Провёл анализ по листам (что в старом архиве и что в новых сметах) и оформил отчётом для заказчика.
- 03
Составил маппинг позиций старой базы на новую и оформил отдельным файлом для сверки заказчиком.
- 04
Собрал список вопросов агроному по ценообразованию. Там, где логика не выводилась из файлов, её нужно было получить от специалиста.
- 05
Собрал новую базу скриптами. Работа шла итерациями. Каждая версия проверялась на целостность формул и сходимость цифр, и только потом собиралась следующая.
- 06
Настроил «умную формулу» с ручным переопределением цены по позиции.
- 07
Прогнал проверки целостности (сходятся ли формулы, не потерялись ли ссылки, совпадают ли расчётные суммы со сметами). Отдельно провёл краш-тест перед сдачей.
- 08
Собрал дашборд для управления базой.
- 09
Написал сопроводительное письмо, инструкцию по работе с файлами и бриф, чтобы систему могли вести без меня.
Стек проекта
- python + openpyxl - чтение, анализ и сборка книг excel
- Скрипты трассировки формул и межлистовых ссылок - восстановление недокументированной логики
- Автоматический маппинг позиций между старой и новой сметной базой
- «Умная формула» с override - расчёт из сметы плюс ручное переопределение по позиции
- Скрипты проверки целостности - сходимость формул, сохранность ссылок, сверка сумм
- COM-автоматизация excel - операции, недоступные через обычную запись файла
- Дашборд управления базой
- Документация: маппинг для заказчика, инструкция, бриф, вопросы предметному специалисту
Результат
Разрозненные excel-файлы сведены в связанную базу с прозрачным расчётом.
Прейскурант переведён на новую детализированную сметную базу, цены остались корректными.
Каждая позиция связана со сметой, и видно, откуда взялась цифра.
Заказчик управляет базой через дашборд и может задать цену вручную, формулы при этом продолжают работать.
Вместе с базой переданы инструкция и бриф, систему можно вести самостоятельно.
Похожие кейсы
Движок статейника - конвейер производства SEO-статей
Конвейер производства SEO-статей с проверкой на каждом шаге. Собран после неудачной первой версии, которая давала страницы дорвейного качества.
- Было
- Органический трафик нужен почти каждому проекту, но статьи дорого делать.
- Сделали
- Пересобрал подход целиком.
- Стало
- Статьи производятся конвейером.
Автоматизация и интеграции на python
Набор python-инструментов под повторяющуюся рутину (отчётность, сборка рекламных кампаний, сбор заявок и подготовка документов).
- Было
- В работе постоянно повторяются задачи, которые съедают время и делаются руками.
- Сделали
- Решение - набор инструментов, каждый под свою повторяющуюся задачу.
- Стало
- Рутинные задачи (отчётность, сборка кампаний, сбор заявок, перенос данных, подготовка документов) выполняются автоматически.