Мои мысли о Python in Excel
22 августа 2023 года компания Microsoft выпустила предварительную версию «Python in Excel». Я разработчик xlwings и автор книги O«Reilly Python for Excel, поэтому мне, разумеется, стало интересно её потестировать. Не следует судить книгу по обложке, поэтому я решил углубиться в изучение и записать обучающий курс LinkedIn Python in Excel: Working with pandas DataFrames. Параллельно я обнаружил много багов и других проблем, после чего отправил отчёты о них в GitHub. На текущий момент они уже или были разрешены, или хотя бы подтверждены/прокомментированы, и это потрясающе. Мне остаётся лишь мечтать, чтобы столь же легко можно было бы сообщать о багах в мире Office.js и VBA.
Так что же я думаю о Python in Excel? Если вкратце:
Мы хотели получить альтернативу VBA, но получили альтернативу языку формул Excel
Интеграция ячеек ноутбуков Jupyter внутри сетки Excel была ошибочным решением
Python in Excel не подходит ни для новичков в Python, ни для интерактивного анализа данных
На данный момент у него слишком много ограничений (невозможно использовать собственные пакеты и нельзя подключаться к веб-API)
Вот примеры возможных сценариев использования, которые я вижу для текущей версии Python in Excel:
Вычислительно затратные задачи наподобие симуляций Монте-Карло
Работа с ИИ при помощи доступных пакетов (scikit-learn, nltk, statsmodels, imbalanced-learn, gensim)
Сложные визуализации при помощи Matplotlib/Seaborn
Анализ временных последовательностей (одно из слепых мест Excel)
Не уверен насчёт очистки/анализа данных: так как вам почти наверняка понадобится Power Query, то может оказаться проще и быстрее пользоваться только Power Query (вместо совместного использования Power Query и Python в Excel)
Прежде чем мы углубимся в подробности, я хочу подчеркнуть, что это лишь моё личное мнение, при этом не жалоба и не критика. В процессе изучения я несколько раз общался с командой разработчиков Excel, она очень дружелюбна и я ценю её интерес к моему мнению. В отличие от меня, им приходится работать с почти сорока годами истории Excel и кучей корпоративного излишнего мусора. С учётом этих ограничений, они прекрасно справляются!
Создавайте то, что нужно людям
История Python in Excel началась в 2015 году, когда пользователь Daniel опубликовал на странице Microsoft UserVoice следующую идею (которая позже мигрировала на форум обратной связи компании):
Идея быстро стала самой популярной среди тех, кому нужны были:
К сожалению, Python in Excel не поддерживает ни ту, ни другую функцию. Вместо того, чтобы стать альтернативой VBA, он превратился в альтернативу языка формул Excel. Хотя я не считаю это чем-то плохим, на самом деле Python in Excel стал всего лишь двухмерным ноутбуком Jupyter внутри таблицы Excel. Кстати, в нём используется настоящее ядро ноутбука Jupyter, исполняемое в Azure cloud. Это значит, что можно пользоваться магией ноутбуков наподобие %%timeit, и это здорово!
Главный архитектор проекта Джон Лэм сообщил Real Python Podcast, что эту реализацию считали «безумной идеей», но в конечном итоге она понравилась людям, принимающим решения в Microsoft. На мой взгляд, ей лучше было бы остаться безумной идеей; причины я объясню в следующем разделе.
Ошибка: размещение ноутбуков Jupyter внутри сетки
Python in Excel можно было бы просто реализовать, интегрировав ноутбук Jupyter как область задач Excel. Разработчик на Python чувствовал бы себя, как дома, имея при этом возможность напрямую выполнять чтение и запись в ячейки Excel. С другой стороны, пользователи Excel могли бы начать осваиваться с Python в «стандартном» окружении без необходимости изучать новый инструмент при переходе с Excel. В чём же проблема с размещении ячеек ноутбука внутри сетки?
Выглядит как утка, но это не утка
Все знают, как устроена электронная таблица: вводишь числа и формулы в ячейки, и всё просто работает, потому что Excel разбирается с зависимостями каждой ячейки и пересчитывает те из них, которые нужно изменять при изменении вводимых данных. И совершенно не важно, где конкретно находятся эти ячейки в таблице.
Что ж, Python in Excel нарушает это базовое правило электронных таблиц: несмотря на то, что ячейки PY очень похожи на пользовательские функции (user-defined functions, UDF), они вычисляются слева направо и сверху вниз. То же относится и к листам, то есть первый лист вычисляется перед вторым и так далее.
Повторяйтесь
Когда появились Office Scripts, стало так удобно, что код (в отличие с ситуации с VBA) хранился за пределами книги Excel, на SharePoint/OneDrive. Внезапно стало возможно использовать один и тот же код во множестве книг без необходимости копипастинга. Кроме того, при обнаружении бага достаточно было исправить код только в одном месте. Жизнь хороша. На самом деле, всё было бы ещё лучше, если бы файл Office Scripts был обычным файлом TypeScript, изменения в котором можно отслеживать на GitHub, но это уже совсем другая история.
Python in Excel хранит код в самом файле Excel. С точки зрения конечного пользователя код существует внутри ячеек, но технически он хранится в /xl/pythonScripts.xml внутри книги Excel (то есть в файле ZIP). Мы снова вернулись к копипастингу, ура!
Магия повсюду!
Если вам нужно вывести DataFrame pandas (или любой другой объект Python) в ячейки Excel, то необходимо переключиться из режима Object в режим Values (кнопка слева на панели формул). Здесь возникает две проблемы:
Когда DataFrame использует стандартный целочисленный индекс, он скрывается при превращении в значения. Если этот индекс текстовый или нестандартный, то он отображается. Магия!
Представьте, что у вас есть сто ячеек PY, и в ячейке 3 вы задаёте столбец
idкак индекс DataFrame. Это будет означать, что в ячейке 100, где вы преобразуете объект Python в значения, у вас внезапно оказывается на один столбец больше.Переключение режима с Object на Values изменяет формулу
=PY("...", 1)на=PY("...", 0). Однако это скрыто от пользователя, потому что пользователь видит в ячейке только код на Python (часть"..."формулы). Магия!Я вижу в этом две серьёзные проблемы:
Это мешает напрямую ссылаться на ячейку с объектом Python, потому что возникает риск, что когда-нибудь кто-то переключит эту ячейку в режим Values, и это поломает все формулы, которые ссылаются на эту ячейку в режиме Object.
Ячейки PY в режиме Object ведут себя иначе, чем любая другая ячейка объекта («Excel (rich) data types»). Возьмём для примера тип данных Stocks: ячейка всегда остаётся объектом, и вы можете отобразить его стоимость, введя в другой ячейке
=A1.Price. Ячейки PY могут вести себя похожим образом: если вам нужны значения, вы пишете=A1.Values. Согласованность всегда полезна.
Неинтерактивный анализ данных
В основном я использую ноутбуки Jupyter для интерактивного анализа данных или для экспериментов с инкрементальным шагом. Ты вводишь в ячейку несколько строк кода и нажимаешь Shift+Enter, чтобы вычислить значение ячейки, смотришь на промежуточные результаты и продолжаешь со следующей ячейкой.
При работе Python in Excel я не вижу результат в ячейке, если только не:
Изменю тип ячейки с Object на Values. Часто это невозможно, потому что требует достаточного пространства вокруг ячейки.
Нажму на значок предпросмотра, после чего откроется всплывающее окно. Это куча лишних действий (даже если знать сочетание клавиш
Ctrl+Shift+F5), а окно предварительного просмотра часто не особо полезно: оно слишком маленькое, а значения показаны в виде ячейки Excel, а не так, как их отображает ноутбук Jupyter. Это должны когда-то исправить, но пока, спустя почти год после выпуска первой версии, предпросмотр по-прежнему показывает#N/A, поэтому я не знаю, что это:np.nan,pd.NAилиpd.NaT.
Слишком много препятствий, поэтому, к сожалению, я не буду использовать Python in Excel для интерактивного анализа данных!
Панель диагностики
Я бы назвал панель диагностики самой большой проблемой Python in Excel. Можно начать даже с без необходимости технического названия (почему бы просто не назвать его Output, как в VS Code?).
Панель диагностики отображает то, что выводится print() в ячейках, а также все сообщения об ошибках. То есть всё то, что ноутбук Jupyter отображает непосредственно под ячейкой. Но поскольку Python in Excel утягивает вывод от ячейки, нужно вручную привязывать вывод к соответствующей ячейке. Два примера:
При изучении DataFrame первым делом мы обычно выполняем
df.info(). Поскольку это печатает вывод, то при каждом пересчёте Excel ячеек Python открывается панель диагностики. Это не просто раздражает, но и совершенно не позволяет быстро соотнести вывод панели диагностики с соответствующей ячейкой. Кроме того, там сохраняется вывод из предыдущего запуска, что ещё больше усложняет поиск нужных строк (тут бы, кстати, помог моноширинный шрифт). Какой же вывод относится кdf1, а какой кdf2? Я не пойму этого, пока не посмотрю на код, находящийся в ячейкахL2иK2:
Ошибка панели диагностики
То же самое происходит с исключениями. Попробуйте найти ошибку на скриншоте ниже (ошибка возникает в
K2, которая уже исчезла внизу панели диагностики)
Начинающему кодеру на Python крайне важно получать удобную обратную связь об ошибках. Но здесь всё не так, и поэтому я не считаю Python in Excel хорошим решением для новичков в Python.
Предложу идею: всплывающее окно предпросмотра — гораздо более подходящее место для отображения вывода без потери контекста. Кроме того, помогло бы раскрашивание ячейки или значка PY в красный цвет в случае возникновения ошибки.
Python в облаке
Как вы наверно слышали, Python выполняется в облаке в инстансах Azure Container, а не в Excel. В социальных сетях на это много ругались («как же наша конфиденциальность?»), но все эти комментаторы упускают из виду тот факт, что большинство корпораций уже доверяет Microsoft все свои данные, пользуясь OneDrive/SharePoint.
На самом деле, я согласен, что лучший способ внедрения Python в Excel — это облако. Обслуживание и установка локального окружения Python — невозможная задача для неопытных пользователей или когда в команде достаточно много людей. Единственный вариант из оставшихся — это Web-Assembly (WASM), но похоже, что команда Excel оставила его реализацию в качестве упражнения для меня (см. конец этого поста).
Однако стоит помнить о том, что за Python in Excel придётся платить сверх тарифа Microsoft 365 (пока мы не знаем, сколько) и, похоже, будет ограничение на количество запросов: по крайней мере, при записи моего обучающего курса LinkedIn я увидел следующее сообщение:
Это раздражает, даже если вы просто записываете видео для курса, а уж при использовании электронных таблиц Excel для трейдинга в хедж-фонде (да, такое всё ещё бывает) и вовсе неприемлемо. И даже если ограничений не будет, Azure cloud иногда уходит офлайн, а значит, ячейки с Python не будут работать. Об этом стоит помнить.
Иными словами, я бы не использовал Python in Excel для чего-то критического, если я не могу переключиться на самостоятельный хостинг или на локальный сервер, когда в облаке произошёл сбой.
Проблемы сценарного языка
Одна из основных причин любви к Python заключается в том, что это «сценарный язык». При помощи Python можно соединить то, что вам нужно соединить, потому что у него есть пакеты для всего. То есть обычно процесс работы выглядит так: скачиваем несколько файлов из веб-API, получаем данные из базы данных, пропускаем всё через DataFrame pandas или polars, а в конце отправляем электронное письмо с результатами.
Что ж, такой базовый рабочий процесс невозможен при использовании Python in Excel. Вы не можете устанавливать дополнительные пакеты, а ваш код работает в стальном ящике, то есть невозможно подключиться к внешнему миру через веб-API или соединение с базой данных. Официальное решение заключается в применении Power Query для получения внешних данных, потому что Python in Excel может ссылаться на Power Query. Но на самом деле, одна из причин, по которым мне нужны были Python и pandas в Excel — это именно что отказ от Power Query (я не хочу учить язык M), поэтому я не большой поклонник этого решения.
Вот чего я не понимаю:
Python in Excel выполняется в инстансах Azure Container. А инстансы Azure Container могут использовать ваш собственный контейнер со всеми нужными вам зависимостями, поэтому я не понимаю, почему команда разработчиков Excel сказала в одном из постов в Reddit, что зависимости каждый раз нужно будет устанавливать с нуля каждый раз, когда кто-то открывает книгу. Это полная чушь, ведь образ контейнера собирается один раз, а затем просто хранится со всеми зависимостями, пока кто-нибудь не откроет книгу в следующий раз.
Если я могу получать доступ к веб-API через Office Scripts, то почему не через Python in Excel? Насколько я знаю, администратор Office может ограничить доступ Office Scripts, поэтому я считаю, что нечто подобное однажды появится и для Python in Excel.
Подведём итог: из-за отсутствующих пакетов и заблокированного доступа к Интернету невозможно использовать Python in Excel во многих ситуациях, для которых людям и нужен был Python in Excel.
Matplotlib прекрасен, но немного интерактивности не помешает
В Python in Excel есть Matplotlib и Seaborn (который основан на Matplotlib), позволяющие создавать графики в случаях, когда диаграммы Excel оказываются слишком простыми. Это замечательная и нужная возможность, но выводом становится скучная статичная картинка. В xlwings такое было ещё десять лет назад. Мне не хватает доступа к современным интерактивным графикам наподобие Plotly, Bokeh, Altair, HoloViews и так далее.
Отсутствие доступа к объектной модели Excel
Люди постоянно используют Excel для создания отчётности. Очень часто им приходится объединять одну или несколько книг с данными на множестве листов. Допустим, есть лист на каждый месяц, или на каждый адрес магазина, или на каждый отдел. При работе с Python in Excel вам придётся вручную выбирать данные на каждом листе, чтобы связать все данные:
jan = xl("Jan[#All]", headers=True)
feb = xl("Feb[#All]", headers=True)
mar = xl("Mar[#All]", headers=True)
...
df = pd.concat([jan, feb, mar, ...])Если вам нужно объединить еженедельные данные или данные ста отделов, то удачи. Если бы Python in Excel давал доступ к объектной модели Excel (как VBA или Office Scripts), то это выглядело бы примерно так (в этом примере показан синтаксис xlwings):
data = []
for sheet in workbook.sheets:
data.append(sheet.tables[0].range.options(pd.DataFrame).value)
df = pd.concat(data)Кстати об отчётности: все пользуются собственным форматированием цветов и границ ячеек, но без доступа к объектной модели Excel это тоже невозможно сделать.
DataFrame великолепны, но массивы и списки NumPy тоже хороши
Если вы выбираете больше одной ячейки, функция xl() передаст DataFrame pandas. Я не против того, чтобы это происходило по умолчанию, но довольно часто вместо DataFrame pandas требуется список или массив NumPy (или DataFrame polars!). И его должно быть очень легко получить. На самом деле, сейчас можно написать собственный конвертер, решающий эту проблему, но я не хочу копипастить его в каждую книгу…! Вот как это можно исправить:
def myconverter(x, headers=False, convert=None, **kwargs):
if convert is None or convert == pd.DataFrame:
return excel.convert_to_dataframe(x, headers=headers, **kwargs)
elif convert == np.array:
return np.array(x)
elif convert == list:
return x
else:
raise ValueError(f"{convert} is not supported.")
excel.set_xl_array_conversion(myconverter)Выполнение этого кода в панели инициализации (когда она будет редактируемой, а пока поместите код в ячейку A1 первого листа) позволит обычным образом использовать функцию xl() для получения DataFrame pandas:
df = xl("A1:B2", headers=True)Но это также позволит и получить массив NumPy:
arr = xl("A1:B2", convert=np.array)или список списков:
mylist = xl("A1:B2", convert=list)Это всего несколько строк кода, но пока он недоступен в Python in Excel, из-за чего приходится дублировать и копипастить код между книгами.
Лучшая альтернатива, нежели «альтернатива языку формул Excel»
Я задался вопросом: «Неужели нет лучших альтернатив, чем добавление Python для повышения аналитических возможностей языка формул Excel»? Думаю, что есть: можно усовершенствовать сам нативный язык формул Excel. И последние годы Microsoft занималась именно этим. Всё началось с динамических массивов, появившихся в 2018 году, которые позже создали возможность для появления мощных формул наподобие =UNIQUE() и =SORT(). В последнее время Microsoft добавила следующие новые функции:
=PIVOT()=GROUPBY()
Постойте-ка, но ведь это половина причин, по которым я бы создавал DataFrame pandas в Excel! То есть вместо того, чтобы писать df.pivot_table() и df.groupby(), я могу теперь оставаться в рамках нативного Excel и писать настоящие формулы Excel без всех неудобств Python in Excel.
Что мне показалось интересным, так это процесс многострочного редактирования в ячейках PY. Почему бы не провести апгрейд нативного языка формул Excel, чтобы мне не пришлось писать подобные выражения LET (которые мне по-прежнему сложно читать):
=LET(x, 1, y, 2, x + y)Может, вместо этого позволить мне писать вот так?
let x = 1
let y = 2
x + yКроме того, почему бы не превратить таблицы Excel в нативный DataFrame Excel? Добавьте им атрибуты вместо того, чтобы придерживаться функционального подхода, и получите что-то подобное:
=MyTable[#All].GROUPBY(...)Существует множество возможностей для интеграции функциональности pandas более нативным для Excel образом!
Что я планирую делать дальше с xlwings?
Один из самых популярных способов применения xlwings всегда заключался в параллельной работе ноутбука Jupyter и Excel. Сложность здесь в том, что Python должен устанавливаться локально. Это проблема, потому что Python тяжело развёртывать и поддерживать с нужными вам пакетами. Ещё и возникают проблемы с отделами ИТ-безопасности: им не нравится полная установка Python на пользовательской машине.
Одно из решений проблемы — xlwings Server, который хостится самостоятельно и работает везде, где запускается Python. Однако xlwings Server предполагает такой сценарий использования: разработчик, пишущий макросы и UDF на Python в бэкенде. Конечный пользователь получает доступ к нему в виде современного веб-аддона Office.js без необходимости знания Python. То есть он не задумывался для интерактивного применения конечным пользователем.
Именно поэтому я начал работать над «xlwings Script», который будет основан на Web-Assembly (WASM). По сути, Web-Assembly позволяет запускать Python нативно в браузере, а аддоны Office.js выполняются в браузере внутри Excel. То есть мне «всего лишь» соединить работающий на WASM Python с объектной моделью Excel. Этого поможет мне добиться PyScript, созданный поверх Pyodide и Emscripten.
Для реализации этого мне, скорее всего, понадобится ещё несколько месяцев; это я определю по раннему proof-of-concept, который будет обладать следующими характеристиками:
Поддержка истинных собственных функций (они же user-defined function, UDF)
Предоставление доступа к объектной модели Excel
Поддержка графиков Matplolib/Seaborn
Поддержка вызовов веб-API
Возможность установки собственных пакетов
Упор на конфиденциальность: всё будет работать на пользовательской машине
Высокая скорость
Поддержка Win, macOS и Excel on the Web с самого начала
Возможность загружать внешние файлы без Power Query
Работа с фиксированными версиями Office, например, с Office 2021, а не только с Microsoft 365
Для его получения достаточно будет скачать аддон из магазина дополнений Excel
По сути, я хочу создать Office Scripts, но с Python вместо JavaScript и работающий на машине пользователя, а не в облаке Microsoft.
Заключение
Варианты использования Python in Excel безусловно существуют, но на мой взгляд, интеграция классического ноутбука Jupyter вместо размещения ячеек на листе Excel была бы гораздо лучше.
Не забывайте, что на текущий момент Python in Excel по-прежнему остаётся версией preview, так что, вероятно, в будущем мы получим больше возможностей и меньше ограничений.
Habrahabr.ru прочитано 2059 раз
