Эта статья, скорее для ознакомления и хотелось бы получить советы по данной работе.
Итак, Excel-файл весит 500+ мегабайт, состоит из сотен тысяч строк, десятков листов и формул, которые «протягиваются» по 30+ столбцам — это не работа, а страдание. Именно с таким «монстром» я столкнулся, когда в компании собрались данные из разных отделов в один файл.
Вкратце структура файла — Лист «Массив» (Data_Lake — в левой части 34 столбца с которым работают специалисты и на котором отрабатывают основные формулы и правая часть с 46 столбцами, куда подтягиваются сырые данные, с которыми будет производиться обработка). И множеством листов со справочниками, правками.
Открытие этого Excel‑файла занимает 10 минут, а если обновить хотя бы часть формул — можно идти пить чай. Работать с такими данными просто невозможно, особенно если тебе нужно анализировать их, строить отчёты или готовить выгрузки. Поэтому решил попробовать все перевести на PostgreSQL.
Для этого всего лишь требовалось переписать формулы с Excel на SQL. Хорошо, что большинство формул это условия ЕСЛИ, ИЛИ.
Вот самая простая формула:
«=ВПР(AN228087&"-"&AX228087;'Мэппинг'!A:E;4;ЛОЖЬ)»И соответствующая ей функция на SQL:
CREATE OR REPLACE FUNCTION statia_po_shety()
RETURNS void AS $$
BEGIN
WITH data AS (
SELECT
m."id",
ms."Статья" AS "Статья УУ по Счету"
FROM "Массив" m
LEFT JOIN "cvi" cvi ON m."id" = cvi."id"
LEFT JOIN "Мэппинг счетов" ms
ON ms."ОБЪЕД" = COALESCE(cvi."Счет Дт", '') || '-' || COALESCE(cvi."Счет Кт", '')
)
UPDATE "Массив" m
SET "Статья УУ по Счету" = d."Статья УУ по Счету"
FROM data d
WHERE m."id" = d."id";
END;
$$ LANGUAGE plpgsql;
Эта функция запускается по триггеру, когда происходит вставка или изменение одной строки, чтобы не обновлять весь массив данных.
А вот и «монстр» из Excel
Это лишь часть формулы, но, чтобы вы понимали масштаб:
=ЕСЛИ(ЕСНД(ВПР(AL228133;'Мэппинг регистратор'!$A:$C;2;ЛОЖЬ);
ЕСНД(ВПР($A228133;'Правки БУ'!A:Q;ПОИСКПОЗ(K$1;'Правки БУ'!$A$1:$Q$1;0);ЛОЖЬ);
ЕСЛИ(ИЛИ(ЕСНД(M228133;0)="!Не брать";
ЕСНД(N228133;0)="!Не брать";
ЕСНД(O228133;0)="!Не брать";
ЕСНД(R228133;0)="!Не брать";
ЕСНД(S228133;0)="!Не брать");"!Не брать"; и т.д. Полная формула занимает десятки строк и комбинирует ЕСЛИ, ВПР, ИЛИ, ПСТР, ЕПУСТО и прочие «прелести» Excel. Протяжка по 300 000 строк делает такой расчёт абсолютно нерабочим.
Как переписал это в PostgreSQL
Создана функция update_statya_uu(), которая:
Объединяет все необходимые данные во временную таблицу
temp_aggПоочерёдно применяет логику
IF...THEN, аналогичнуюЕСЛИиВПРв Excel-
Выполняет обновления только при необходимости
Пример фрагмента функции:
CREATE OR REPLACE FUNCTION update_statya_uu()
RETURNS void AS $$
DECLARE
rec RECORD;
result_text TEXT;
BEGIN
CREATE TEMP TABLE temp_agg AS
SELECT
m.*,
c."Документ" AS cvi_document,
c."Организация" AS cvi_organization,
c."Тип документа.1" AS cvi_tip_dok_1,
m."Тип документа" AS m_tip_dok,
c."Субконто Кт" AS cvi_subkonto_kt,
c."Субконто2 Кт" AS cvi_subkonto2_kt,
c."Субконто3 Кт" AS cvi_subkonto3_kt,
c."Субконто1 Дт" AS cvi_subkonto1_dt,
c."Субконто2 Дт" AS cvi_subkonto2_dt,
c."Счет Кт" AS cvi_schet_kt,
c."Счет Дт" AS cvi_schet_dt
FROM "Массив" m
LEFT JOIN "cvi" c ON m.id = c.id;
-- Проходим по временной таблице по одной записи и вычисляем значение для "Статья УУ"
FOR rec IN SELECT * FROM temp_agg LOOP
р
SELECT "Статьи затрат УУ" INTO result_text
FROM "Мэппинг регистратор"
WHERE "Регистратор" = rec.cvi_document
LIMIT 1;
IF FOUND THEN
UPDATE "Массив" SET "Статья УУ" = result_text WHERE id = rec.id;
CONTINUE;
END IF;
SELECT "Статья УУ" INTO result_text
FROM "Правки БУ"
WHERE "Документ БУ Ключ" = rec."Key"
LIMIT 1;
IF FOUND THEN
UPDATE "Массив" SET "Статья УУ" = result_text WHERE id = rec.id;
CONTINUE;
END IF;
IF rec."Статья УУ по затрате" = '!Не брать' OR
rec."Статья УУ по запчастям" = '!Не брать' OR
rec."Статья УУ по НГ" = '!Не брать' OR
rec."Статья УУ по Счету" = '!Не брать' OR
rec."Статья по Тип док." = '!Не брать' THEN
UPDATE "Массив" SET "Статья УУ" = '!Не брать' WHERE id = rec.id;
CONTINUE;
END IF;Логика последовательная: от приоритетных правил к более общим, с проверкой условий, похожих на Excel-формулы.
Производительность
? Обработка 300 000 строк: ~2 минуты
? Время обновления сократилось в 10 раз
⛓ Работает по триггеру для обновлений отдельных строк
В конечно итоге, работа по переносу заняла пару недель и вот часть структуры:
И на все столбцы, всех таблицы у нас 57 триггеров
Но перенести этот массив было полбеды. Из-за специфики, специалистам надо прогонять 300 тысяч строк каждый день. И тут вступил в дело Python.
Общая схема ETL-процесса:
Excel (.xlsx) → DataFrame → Pickle → PostgreSQL (таблицы cvi, Массив) → JOIN → Data_Lake → Приведение типов
1. Предобработка: загружаем Excel → DataFrame
df = pd.read_excel(excel_file_path)
df.to_pickle(pickle_file_path)Как показала практика .pkl читается быстрее
Обрабатываем столбец "Дата" — Excel часто сохраняет даты в виде чисел, и это надо исправить:
df['Дата'] = pd.to_datetime('1899-12-30') + pd.to_timedelta(df['Дата'], unit='D')2. Подготовка базы: создаём схему и таблицу
CREATE SCHEMA IF NOT EXISTS "public";
CREATE TABLE IF NOT EXISTS "cvi" (...);*cvi - таблицы куда поступают сырые данные (правая часть Data_Lake)
Поля автоматически приводятся к varchar(255), а даты — к date. Если нужен более точный тип — можно легко адаптировать.
3. Очистка таблиц перед загрузкой
TRUNCATE TABLE "Массив" RESTART IDENTITY CASCADE;
TRUNCATE TABLE "cvi" RESTART IDENTITY CASCADE;*Массив - результат обработки с cvi (левая часть Data_Lake)
Это нужно, чтобы избежать дублирования, т.к. обновляется всё.
4. Параллельная загрузка данных в PostgreSQL
CSV или bulk-загрузка подошли бы, но мы выбрали psycopg2 + executemany с параллельной обработкой чанков:
chunk_size = 6000
max_workers = 8Для каждого чанка — отдельный поток:
with ThreadPoolExecutor(max_workers=max_workers) as executor:
futures = [executor.submit(load_chunk, chunk) for chunk in chunks]Дальше создается витрина Data_Lake (витрина):
CREATE TABLE "Data_Lake" AS
SELECT m.*, c.*
FROM "Массив" m
JOIN "cvi" c ON m.id = c.id;И производится перевод в нужный формат данных (из text в numeric(12,2)), если надо.
В конечно итоге с момента загрузки сырых данные (300 тысяч строк, в 46 столбцах) в таблицу cvi, срабатыванием триггеров и формированием витрины данных (Data_Lake с 78 столбцами в 300 тысяч строк) заняло у меня:
Дополнительно:
добавляются еще связи
ETL реализовать в Apache Airflow
добавить веб-интерфейс для дашборда
Вроде на этом все. Естественно буду благодарен за советы по улучшению и оптимизации работы.
Комментарии (26)
SergeyProkhorenko
28.07.2025 14:47А Microsoft Access или 1С не пробовали? Зачем сразу из пушки по воробьям?
Kerman
28.07.2025 14:47Я для себя сделал вывод, что если компания живёт в экселе - то у неё очень плохо с разработчиками.
Размерами никого не удивить, сотни тысяч строк и десятки таблиц - это небольшая эскюэльненькая базочка. Да, постгрес, да и mysql ворочают и побольше и побыстрее и на скромном железе. В принципе, это логичный путь для развития внутренней системы.
Только есть одно но.
RDBMS не имеют интерфейса. В отличие от экселя. Мало данные загнать туда, надо ещё для них сделать UI, который их вносит, редактирует, показывает. И это не так просто, как кажется.
Feedman
28.07.2025 14:47Возможно, что в компании их нет совсем.
P.S. в который раз вспоминается Г. Кузнецов из Компьютеров, который мечтал о гибриде электронных таблиц и rdbms на персоналках.
GBR-613
28.07.2025 14:47Для этого предназначался MS Access. Правда, я никогда не слышал, чтобы им кто-то пользовался.
arider77
28.07.2025 14:47Очень даже часто приходилось видеть MS Access в работе. Ещё в конце девяностых переводил бумажную "систему" работы с зерном (поставщики, элеваторы, разнарядки, потребители\продажа) в MS Access и убедился, что далеко не я один занимаюсь подобным. Уровень визуализации - вполне. MS Office стоит на 99% ПК. Так что работало и работает.
Многие университетские знакомые начинали какие-то проекты и работы выполнять в MS Access.
dayroon
28.07.2025 14:47"Троллейбус из буханки.JPG", но Эксель умеет работать с ODBC и можно привычный пользователям экселевский отчёт заполнять прямо запросом к базе, или селективной хранимкой.
Более того, к Экселю можно писать SQL запросы, он и сам предоставляет родной ODBC драйвер.
10011001010010010
28.07.2025 14:47Зря. Такая дичь была, надо было её дальше растить и мечтать о книге гиннеса. А теперь сделали как положено - кому это интересно? Ну что мы, БД на 20 таблиц не видели?
Vorin
28.07.2025 14:47Не знаю какую конкретно задачу решаете вы, но я по долгу службы много раз решал задачу больших экселей. Гораздо эффективнее решил бы эту задачу power pivot. Во первых данные грузятся сразу в модель реляционных таблиц. Во вторых остаётся возможность после обновления пользоваться этим файлом локально, без конекта и доп настройки клиента и питонов, в третьих есть VBA и многие формулы можно реализовать прям в нем (хотя это крайне редко нужно), в четвёртых есть формулы поддерживающие контекст (аналог scope из mdx). Плюсом идёт независимость наличия надстройки на клиенте, модель реализованная в power pivot работает в файле, где она не установлена из коробки.
А если вам надо переехать, то тогда простой вопрос - зачем вы оставляете Эксель "мордой" для субд, раз уж сервер и питон в деле, почему сразу тонкий клиенте на вебе не написать?
astentx
28.07.2025 14:47Как уже отметили, у Excel есть большой плюс: GUI и привычность пользователю. И обычно шаг от наколеночного решения до опромышливания - гигантский. Если хочется убрать вырвиглазный ВПР (индекс поискпоз куда гибче, кстати), то в качестве первого приближения можно было бы использовать стандартный Power Query:
Всё остаётся в том же файле с тем же интерфейсом.
Можно разделить большой файл сырых данных на отдельные файлы а-ля горячее/холодное хранение и обновлять только горячие данные без ожидания обновления всех формул.
Гибкость не хуже SQL. Есть много настроек через кликание мышкой.
Никакого питона, который неоткуда взять случайному пользователю в компании.
Ноль дополнительной инфраструктуры (ведь Postgres и планируемый Airflow мы не в воздухе запускаем)
Никаких дополнительных проблем с доступами и полномочиями: все на уровне того же файла.
Можно по-прежнему собирать данные из разных источников и локально крутить-вертеть независимо от технологий и интеграций: экспорт поддерживается в том или ином виде почти везде.
Если когда-то наступят светлые времена, можно прозрачно для пользователя подменить некоторые таблицы на таблицы СУБД: для пользователя ничего не изменится, есть pushdown.
Питоны - это классно, но только для упрощения жизни себе. Для того, чтобы такое решение можно было распространить в компании, надо провести очень немало организационных мероприятий и приложить ему усилий, а все это помимо никуда не девающейся сложность настройки и последующей актуализации/доработки.
avgordeenko
28.07.2025 14:47Здравствуйте. А откуда в целом берутся данные в экселе? Если есть возможность, нужно переходить на нормальные источники данных. Эксель может служить в качестве визуализации итогового отчета, или как источник каких-то рукотворных справочников. Но основную инфу лучше брать из учетных систем.
Igordru Автор
28.07.2025 14:47все тянется из 1с, но по неведомым мне причинам у 1с-ников часто сервер тупит
Exi_stence
28.07.2025 14:47Для себя выбрал такие критерии выбора системы.
До 100 тыс строк - excel. Если много формул и виснет на просчете - оптимизировать формулы или выносить их в VBA (что не желательно, тк надо менять расширение).
Больше 100 тыс и до 5 млн. Хорошо справляется Access. К нему так же замечательно коннектится excel через надстройку power pivot. PP вообще достаточно эффективно обрабатывает и хранит данные, но его нельзя использовать как источник данных.
Под специфический задачи: простенькие etl процессы - power query; визуализация/аналитика - power bi.
Если больше данных или нужны дополнительные свойства, в тч совместная работа. То лучше уже выбирать полноценные субд, но это кратно другие расходы
Vorin
28.07.2025 14:47Можете объяснить, что значит "PP нельзя использовать как источник данных"?.
Exi_stence
28.07.2025 14:47PP - так сократил power pivot. В нем можно сделать подключения к внешним источникам данных. Но подключиться к power pivot как к источнику данных нельзя.
Vorin
28.07.2025 14:47Я понял, что PP это power pivot, не понял, что значит нельзя использовать как источник данных. PP это дата моделлер, который позволяет собрать данные от нескольких провайдеров в одну модель данных внутри файла. Оперировать сущностями этой модели и видоизменять их. По сути встроенный ETL.
Эту модель можно далее использовать для визуального отображения в виде, например, сводных или таблично на листе. То есть для сводной модель данных power pivot уже является источником. Поэтому тезис "нельзя подключиться" у меня и не клеится.
Для кого модель данных не может выступать источником? Вы хотите чтобы модель данных одного файла использовалась в других ресурсах (например использовать эту модель в других файлах)?
economist75
28.07.2025 14:47Подобные задачи хорошо реализуются в экосистеме python (все модные штучки лезут отсюда), без платных или рискованных решений типа PowerBI. Объем данных помещается в RAM 16GB, а значит можно использовать Python+Pandas или SQL+DuckDB, все это в блокноте Jupyter или MS VScode в режиме блокнота. Плюсы - нет сервера БД, скорость выше, простой язык запросов df.query(). Утка дает партицирование и очень быструю работу, быстрее самой быстрой SQLite. Оцениваю трудоемкость Pandas/DuckDB vs SQL/PG как 1:4.
ETL пишется на Pandas в pipe(), а вот запускать ETL-скрипты лучше не в монструозном Airflow, а в легком Dagster (проще стартовать, встроенная валидация данных вполне хороша). Трудоемкость AirFlow vs Dagster 3:1.
ETL-cкрипты сейчас принято не писать, а экспортировать из блокнотов, где они создаются естественным образом, вперемешку с документацией, графиками, описанием данных, доктестами и весь CI/CD сводится к выполнению ячейки с единственной строкой вида exp_py('excel'). Работа в блокноте vs classic IDE 1:5. И да, настоящая совместная работа аналитиков - это блокнот в JupyterLab/Hub в режиме одновременной правки, когда много курсоров, как в GoogleDocs, только еще и с исполнимым кодом. Писать исследование в 4-е руки оказалось не в 2, а в 3 раза быстрее, за счет более полного заимствования кода из "прежних" наработок, которые, в случае блокнотной работы, все находятся в одном открытом блокноте. И да, это иногда падает, но не сильно. Насчет сложности деплоя всей python-среды в офисе - его нет. Блокнот работает в браузере, любом.
Дэшборды тоже делаются нынче просто: Streamlit. И его код app.py также получается через CI/CD из блокнота с exp_py('dashboard'). Трудоемкость Streamlit vs Superset/Metabase 1:2, хотя от 50+ активных листателей дэшборда лучше Superset с аналитической БД.
MilPavel
28.07.2025 14:47MS Access + MS SQL сервер легко решают такие задачи. И активно используются. Потому, что: есть образцы, база Борей, например. И документация. И репутация. В Visual Basic есть пошаговая отладка, и компиляция, точки останова и много чего ещё.
ALexKud
28.07.2025 14:47Если задача решается слишком сложно, как вы описали, значит ее можно упростить и довольно значительно. Можно применить такой вариант- создать нужную инфраструктуру в sql server и настроить передачу данных напрямую из 1C в таблицы sql server и там же в sql server сделать нужные процедуры и функции обработки данных для отображения в exel файлах в качестве дашбордов через sql запросы в самих exel файлах. Но тут надо хорошо знать. SQL и уметь работать с 1С по связи с sql server напрямую(в документации 1с все описано). Зато никаких питонов и прочей обвязки и можно оптимизировать sql запросы по скорости работы. Если база данных будет расти то оптимизация ее настоятельно рекомендуется.
Igordru Автор
28.07.2025 14:47То есть не надо будет подключаться к MS SQL, на котором хранятся данные с 1с?
economist75
28.07.2025 14:47В документации 1С описано что работа с 1С-данными в БД на любом движке напрямую (не средствами платформы 1С) является нарушением лицензионных условий. Никаких DBeaver итп. Разложено много граблей на этом пути - 1С обеспечивает агрессивную конкурентную защиту экосистемы и 1С-сообщества ростом сложности и искусственными ограничениями. Монопольное положение (95%+ рынка учетных решений в РФ) позволяет творить и не такую дичь. Поэтому "никаких питонов и Excel-ей" лишь усугубит эту нездоровую для всего бизнеса ситуацию. Но есть и повод для оптимизма: монстр начал поедать себя. 10000 таблиц в современной 1С базе данных оказались неподъемными, и вместо одного 1С-специалиста нужно держать пять, а это неизбежный разброд и шатания.
economist75
28.07.2025 14:47В случае IN-MEMORY или быстрых файловых БД на основе указателей (DuckDB, SQLite) сложная оптимизация SQL-запросов и нетривиальное создание БД не понадобятся, т.к. данные и так будут выдаваться мгновенно. Скидывая огромные регистры 1С (например, таблицу всех проводок с начала года на неск млн строк и сотни столбцов) в банальный TXT-файл - мы на Python его сериализуем и загружаем в RAM или читаем файлы партиций в сжатом типизированном виде. Это будет самый быстрый доступ к данными из возможного.
rettsu
28.07.2025 14:47Работал с файлом Эксель на 5,5 млн строк (были разбиты на 12 листов - по количеству месяцев). Вес - 1 Гб. В том же файлы сводные, дэшборд. Все весьма живенько крутилось на ноутбуке HP Omen не самой мощной конфигурации.
Тяжёлые формулы можно попробовать оптимизировать или загнать в VBA и запускать расчет тогда, когда это реально нужно.
winorun
Попробуйте ознакомится с R. Может под Вашу задачу не сильно подойдет. Но ознакомится лишним не будет.