Где заканчивается Excel: один процесс обработки данных в формулах, Power Query и Python
Аннотация: Один и тот же набор файлов можно обработать тремя способами, но сложность растет совсем не там, где обычно ожидаешь. Разбираю на одном процессе, в какой момент Excel превращается в хрупкую конструкцию, зачем нужен Power Query и когда уже проще написать Python-скрипт.
Обсуждение Excel и Python обычно быстро превращается в спор об инструментах. Мне такой подход кажется бесполезным. Excel может быть быстрее Python, а Python — заметно проще Excel. Все зависит не от количества строк и не от модности технологии, а от того, что происходит с данными между входом и результатом.
Поэтому для сравнения я беру один рабочий сценарий. Каждый день в папке появляются CSV-файлы с операциями из нескольких источников. Их нужно собрать, очистить, привести даты и суммы к единому виду, удалить повторы, дополнить справочником клиентов и получить итоговую выгрузку. Специально добавляю несколько неприятных условий: порядок строк меняется, иногда появляется пустой идентификатор, даты приходят в разных форматах, а один источник периодически добавляет новый столбец.
На такой задаче разница между Excel, Power Query и Python становится хорошо видна. Пока данные идеальны, все три решения работают. Настоящее сравнение начинается в тот момент, когда данные перестают быть идеальными.
Excel удобен до тех пор, пока таблица остается таблицей
Первую версию я делаю обычными средствами Excel. Получаю исходные данные на лист, рядом размещаю справочник, добавляю вычисляемые столбцы. Дату нормализую формулой, клиента подтягиваю через XLOOKUP, корректность идентификатора проверяю через IF, дубли отмечаю COUNTIF. Для небольшого файла такой вариант действительно удобен: исходная строка и результат находятся рядом, ошибку можно увидеть глазами, а правило изменить за несколько секунд. Технический предел листа Excel составляет 1 048 576 строк, но практическая граница конкретного процесса обычно наступает значительно раньше и связана не с размером сетки, а со сложностью зависимостей.
В моем примере обработка постепенно складывается примерно в такую цепочку:
- определить источник строки;
- проверить обязательный order_id;
- преобразовать created_at в дату;
- привести amount к числу;
- нормализовать status;
- найти client_id в справочнике;
- определить повторяющиеся order_id;
- исключить ошибочные строки;
- рассчитать итоговый показатель;
- перенести готовые записи в результат.
На бумаге это десять простых операций. В Excel они довольно быстро превращаются в десять зависимых друг от друга столбцов. Например, сумма может рассчитываться только после корректного преобразования типа, а итоговый статус строки — после проверки идентификатора, даты, клиента и дубля. В результате формула проверки перестает описывать бизнес-правило и начинает описывать устройство самой книги. Для меня это и есть первая граница Excel. Не количество данных, а момент, когда изменение одного правила заставляет проверять цепочку формул в нескольких местах.
Предположим, идентификатор клиента лежит в B2, сумма — в D2, а справочник клиентов хранится на отдельном листе. Нормальная формула вроде =XLOOKUP(B2;Clients[client_id];Clients[segment];NA()) читается без проблем. Но потом появляется условие: пустые ID нельзя искать, удаленных клиентов нужно помечать отдельно, отсутствующие значения отправлять на проверку, а результат использовать еще в трех формулах. Через несколько итераций рабочая книга остается функциональной, но перестает быть прозрачной. Особенно неприятно то, что ошибка часто выглядит как вполне корректное значение в ячейке. Если сотрудник случайно заменил формулу числом или протянул диапазон не до конца, визуально таблица продолжает жить.
Power Query убирает формулы, но заставляет думать о схеме данных
Следующий вариант я собираю в Power Query. Здесь меняется сама модель работы. Вместо того чтобы вычислять результат построчно на листе, я описываю последовательность преобразований. Получить файлы из папки, объединить, выбрать нужные поля, привести типы, соединить со справочником, отфильтровать ошибки и загрузить результат. Это уже гораздо ближе к небольшому ETL-процессу. Power Query умеет объединять таблицы разными типами join, а преобразования сохраняются как последовательные шаги, поэтому после появления новых исходных файлов весь сценарий можно выполнить повторно.
Критические места здесь совсем другие:
- изменение имени или отсутствие ожидаемого столбца;
- автоматическое определение неправильного типа;
- различия региональных форматов дат и чисел;
- дубли в таблице, которая считалась справочником;
- несколько совпадений при Merge вместо одного;
- новый статус, которого нет в правилах нормализации;
- ошибка внутри одного файла, из-за которой ломается обновление всей цепочки.
Особенно внимательно я отношусь к типам. Power Query умеет автоматически определять их для неструктурированных источников вроде CSV и Excel, но такое удобство легко превращается в скрытую проблему. Если один файл содержит 07.08.2026, другой 08/07/2026, а третий строку
В M часть такого процесса может выглядеть довольно компактно: Typed = Table.TransformColumnTypes( Source, { {"order_id", type text}, {"client_id", type text}, {"created_at", type date}, {"amount", type number} }, "ru-RU" ), ValidOrders = Table.SelectRows( Typed, each [order_id] <> null and [amount] <> null ), Merged = Table.NestedJoin( ValidOrders, {"client_id"}, Clients, {"client_id"}, "client", JoinKind.LeftOuter )
Мне нравится Power Query именно на этом уровне. Логика уже вынесена из ячеек, но ее все еще можно просмотреть пошагово. Можно нажать на этап до объединения, потом на этап после объединения и быстро понять, где пропали строки. Excel при этом остается оболочкой для пользователя: открыл книгу, нажал обновление, получил результат. Для регулярного отчета, которым пользуется небольшой отдел, такой компромисс часто оказывается сильнее отдельного Python-проекта.
Проблема начинается, когда одного успешного обновления становится недостаточно. Мне может потребоваться не просто отбросить неправильную строку, а сохранить ее в отдельный файл с кодом ошибки. Затем проверить количество записей до и после каждого этапа, остановить обработку при появлении неизвестного столбца, сравнить текущую схему со вчерашней, вести журнал запусков и не создавать результат при нарушении контрольного условия. Все это можно частично построить вокруг Power Query, но постепенно приходится обслуживать уже не преобразование данных, а инфраструктуру вокруг него. Здесь для меня проходит его граница.
Python становится проще именно тогда, когда задача становится сложнее
При переходе на Python я сначала вообще не меняю алгоритм. Те же исходные CSV, тот же справочник, те же правила. Разница в том, что теперь каждое условие можно превратить не только в преобразование, но и в проверяемое утверждение. Это важнее скорости. Мне не нужен скрипт, который молча обработал миллион строк. Мне нужен процесс, который откажется выпускать результат, если получил данные, которых не понимает.
Например, загрузку я могу начать с явной схемы: import pandas as pd from pathlib import Path required_columns = { "order_id", "client_id", "created_at", "amount", "status" } frames = [] for file in Path("input").glob("*.csv"): df = pd.read_csv( file, dtype={ "order_id": "string", "client_id": "string", "status": "string" } ) missing = required_columns - set(df.columns) if missing: raise ValueError( f"{file.name}: отсутствуют поля {sorted(missing)}" ) df["source_file"] = file.name frames.append(df) orders = pd.concat(frames, ignore_index=True)
Здесь появляется принципиальное отличие. В Excel отсутствие столбца часто обнаруживается человеком. В Power Query оно обычно проявляется на одном из шагов обновления. В Python я сам определяю, что считать нарушением контракта данных, и прекращаю обработку в нужном месте. Чтение CSV в pandas позволяет явно контролировать типы и отдельно разбирать сложные даты через to_datetime, вместо того чтобы полагаться на случайное определение формата.
Дальше я не удаляю плохие записи сразу. Мне полезнее разделить данные на корректные и требующие разбора. Даты преобразуются с контролем ошибок, суммы — в числовой тип, затем появляется набор флагов. orders["created_at"] = pd.to_datetime( orders["created_at"], errors="coerce", dayfirst=True ) orders["amount"] = pd.to_numeric( orders["amount"], errors="coerce" ) orders["err_id"] = orders["order_id"].isna() orders["err_date"] = orders["created_at"].isna() orders["err_amount"] = orders["amount"].isna() orders["err_duplicate"] = orders.duplicated( subset=["order_id"], keep=False ) error_columns = [ "err_id", "err_date", "err_amount", "err_duplicate" ] orders["has_error"] = orders[error_columns].any(axis=1) errors = orders[orders["has_error"]].copy() clean = orders[~orders["has_error"]].copy()
Для меня вот этот фрагмент лучше всего показывает, зачем вообще понадобился Python. Не потому, что формулы Excel плохие и не потому, что Power Query недостаточно серьезный инструмент. Просто у процесса появляется состояние. Есть вход, проверки, корректные строки, исключения, журналирование и условия остановки. Это уже не таблица, которую надо преобразовать. Это небольшая программа обработки данных.
Справочник клиентов я тоже перестаю воспринимать как обычный VLOOKUP или Merge. Перед соединением проверяю его собственную целостность. Если один client_id встречается дважды, left join способен размножить строки заказов. Это одна из ошибок, которые особенно неприятны тем, что код выполняется без исключения, а результат оказывается неверным. Поэтому перед merge проверка выглядит примерно так: duplicates = clients["client_id"].duplicated(keep=False) if duplicates.any(): bad_ids = clients.loc[duplicates, "client_id"].unique() raise ValueError( f"В справочнике повторяются client_id: {bad_ids[:10]}" ) result = clean.merge( clients, on="client_id", how="left", validate="many_to_one" )
Параметр validate здесь для меня ценнее нескольких строк экономии кода. Я заранее говорю, какую связь ожидаю: много операций могут относиться к одному клиенту, но в справочнике клиент должен существовать в единственном экземпляре. Если это предположение нарушено, обработка должна завершиться ошибкой, а не выдать красивый, но неверный Excel-файл.
Есть еще одна граница, после которой возврат к Power Query уже кажется мне шагом назад: необходимость тестировать бизнес-логику. Например, отмененный заказ не должен попадать в выручку, отрицательная сумма допустима только для возврата, дата операции не может быть позже даты формирования отчета, а после объединения количество строк не должно внезапно увеличиться. В Python такие условия становятся тестами или assertions. Они запускаются каждый раз одинаково и не требуют открывать промежуточный лист глазами.
Если процесс нужно выполнять автоматически по расписанию, ситуация становится еще очевиднее. Скрипт можно запускать без открытого Excel, передавать ему пути и даты параметрами, писать лог, возвращать ненулевой exit code при ошибке и подключать к планировщику или CI. В этот момент файл Excel остается хорошим форматом выдачи результата, но перестает быть средой, в которой живет сама логика обработки.
При этом я бы не переносил задачу в Python только потому, что там можно написать более красивый код. За Python приходится платить. Нужна среда выполнения, зависимости, структура проекта, контроль версий, обработка ошибок и человек, который сможет разобраться в коде через полгода. Excel-файл можно передать коллеге за минуту. Python-проект требует дисциплины. Поэтому переход оправдан не тогда, когда код выглядит профессиональнее, а когда стоимость ручного контроля и хрупкость предыдущего решения стали выше стоимости поддержки программы.
Где в итоге проходит граница
После этого сравнения у меня не получилось красивой формулы вроде до 50 тысяч строк использовать Excel, после 50 тысяч Power Query, после миллиона Python. Такая граница была бы удобной, но она почти ничего не говорит о реальной задаче.
Excel остается для меня лучшим вариантом, пока преобразование можно понять, посмотрев на таблицу и несколько формул. Если результат нужен человеку для дальнейшей ручной работы, правила простые, источников мало, а ошибки легко заметить глазами, перенос в код только увеличит стоимость решения.
Power Query начинается там, где повторяется сама процедура подготовки данных. Одни и те же файлы нужно регулярно собирать, очищать, соединять и обновлять. В этот момент последовательность шагов полезнее набора формул. Особенно если конечный пользователь все равно работает в Excel и ему нужен понятный сценарий обновления.
Python становится рациональным выбором, когда важен уже не только результат преобразования, но и поведение всего процесса при сбоях. Нужно проверять схему, фиксировать исключения, тестировать бизнес-правила, обрабатывать десятки файлов, запускать задачу без участия человека и гарантировать, что неправильные данные не превратятся в правильный на вид отчет.
После этого эксперимента критерий выбора инструмента у меня стал довольно простым. Если приходится долго объяснять, в каких ячейках нельзя ничего менять, Excel уже начинает мешать. Если приходится строить вокруг Power Query отдельную систему контроля за тем, успешно ли он обновился, его граница тоже близко. А если Python-скрипт требует сотни строк инфраструктурного кода ради файла, который коллега обновляет раз в месяц, значит переход произошел слишком рано.
Самая дорогая ошибка здесь — не выбрать слабый инструмент. Ее обычно можно исправить. Гораздо хуже продолжать расширять решение после того, как изменилась природа самой задачи. Таблица в какой-то момент становится процессом. И вот этот момент для меня намного важнее количества строк.