Почему вы должны комбинировать Python и Excel? Короче говоря, Python удаляет ограничения, установленные встроенными формулами Excel. Те же причины, по которым Python является одним из самых популярных языков для разработчиков приложений, применяются к тем, кто работает с Python в Excel. Работа с Python в Excel приносит расширенные аналитические библиотеки Python (Pandas, Numpy), возможности автоматизации, инструменты визуализации данных (matplotlib) и масштабируемость для Excel.
Предварительные условия и установка установка Python и Pip
Проверьте установку:
Вывод: самая последняя версия Python и PIP
После того, как у вас есть недавние версии Python и PIP на вашей машине, мы можем перейти на настройку Excel.
Включение экстра-питоновских добавок (xlwings, pyxll) xlwings
Xlwings-это бесплатный мост с Excel-Python с открытым исходным кодом, позволяющий вызывать сценарии Python из Excel. Он идеально подходит для автоматизации отчетов, очистки данных и создания пользовательских функций без Visual Basic для приложений (VBA).
Во -первых, установите на машине с помощью терминала:
Затем включите это из Excel:
Pyxll
Pyxll-это профессиональный (оплаченный) инструмент интеграции Excel-Python, предназначенный для производственных сред. Это более продвинуто, чем Xlwings. Он позволяет создавать пользовательские функции Excel в Python, запускать код Python, когда рабочие тетради открывают и интегрируются с расширенными библиотеками науки о данных.
Используйте Xlwings бесплатно, автоматизацию Excel с открытым исходным кодом и быстрые интеграции Python (например, извлечение данных из CSV, чистка его пандами и отправить обратно в Excel). Выберите Pyxll для расширенных, производственных решений, которые требуют высокой производительности, пользовательских функций и профессиональной поддержки (например, создание каналов данных на рынке живого рынка в Excel с расчетами в режиме реального времени).
В этом учебном пособии будут использовать Xlwings через Pyxll, так как XLWINGS является опцией с открытым исходным кодом.
Попытка Microsoft 365 «Python in Excel» Предварительный просмотр
Microsoft 365 «Python in Excel» (бета) отличная в тех случаях, когда вам нужен немедленный доступ к Python внутри Excel без каких -либо установок. Это не истинная замена для Xlwings или Pyxll, так как эти инструменты имеют более надежные функции. Варианты использования для Microsoft 365 «Python in Excel»:
- Задачи простые или исследовательские.
- Экспериментирование или изучение питона в Excel.
Microsoft 365 «Python in Excel» дает вам немедленный доступ к библиотекам Python, таким как Pandas и Matplotlib, используя функцию = py ().
Вы можете попробовать бета -версию, если вы являетесь частью бета -канала инсайдеров Microsoft 365. Откройте Excel и введите такую формулу:
Эта функция будет работать внутри ячейки.
Запуск кода Python из Excel
Есть несколько способов сделать это в зависимости от ваших потребностей и предпочтений.
Встроенные ячейки Python
Запустите небольшие сценарии Python, используя функцию = py () непосредственно внутри ячеек Excel. Подумайте об легких расчетах и простых преобразованиях данных.
Пользовательские функции (UDFS)
UDF — это пользовательские функции, которые интегрируют логику Python в электронные таблицы. Вы можете создавать UDF при использовании Xlwings и Pyxll. UDF отлично подходят для интеграции данных в реальном времени, пользовательских финансовых расчетов и проверки данных и очистки.
Как создать UDF:
- Создайте файл кода Python:
- Откройте текстовый редактор или IDE (например, VS Code, Pycharm, Sublime и т. Д.).
- Сохраните новый файл, например, PY.
- Напишите код (например, базовый код сложения).
- Подключитесь к Excel (должны быть установлены Xlwings, а Xlwings включены в Excel):
- Укажите надстройку на свой файл udfs.py в настройках Xlwings в Excel.
- Назовите это из Excel:
- В любой ячейке введите имя функции, как определено в PY.
Excel запустит код Python и вернет 15.
VBA-To-Python Feetwarebility
Если вы уже используете макросы VBA, вы можете использовать их для запуска сценариев Python, используя Xlwings или Pyxll. Это позволяет вам представить Python в ваш рабочий процесс VBA, не восстанавливая все с нуля.
Подобно примеру UDF выше, вам необходимо установить xlwings (или pyxll в зависимости от того, какой инструмент вы используете) и xlwings включены в Excel.
Создать файл кода Python
Файл должен содержать функцию main (), чтобы соответствовать вызову VBA. Давайте позвоним в файл vba_file.py.
Настройте путь в настройках XLWINGS (или в вашем коде VBA), чтобы убедиться, что Python знает, где находится ваш скрипт.
Когда вы запустите макрос VBA внутри Excel, он будет использовать XlWings (или Pyxll). После того, как Python выполняет код, результаты можно отправить обратно в Excel или использовать для обновления вашей рабочей книги.
Рабочие процессы анализа данных
Эти рабочие процессы не будут использовать встроенный код. Они будут следить за примером UDF выше (но не все эти функции являются UDF). Это означает, что вам придется указывать Xlwings на имя вашего файла скрипта Python в Excel или правильно настроить его, используя вызовы VBA Runpython.
Импорт данных с пандами
Используйте библиотеку Pandas для импорта и анализа данных из файлов Excel. Приведенный ниже пример считывает данные из файла Excel, выполняет базовый анализ и записывает результаты обратно в рабочую книгу.
Визуализация с matplotlib
Matplotlib — это инструмент визуализации данных. Приведенный ниже пример считывает данные из файла Excel, генерирует гистограмму, сохраняет их в виде изображения и вставляет диаграмму в рабочую книгу.
В примере машинного обучения (ML) ниже используются Pandas для обработки данных Excel и Joblib для загрузки предварительно обученной модели для прогнозирования. Инструменты работают вместе для загрузки данных из файла Excel. Затем он применяет предварительно обученную модель, которая предсказывает результаты и записывает результаты обратно в рабочую книгу.
Чтение и написание файлов Excel с Python
OpenPyxl и XLSXWriter являются популярными библиотеками Python для работы с файлами Excel.
OpenPyxl Основы
OpenPyxl — отличный инструмент, когда вам нужно читать или изменить существующие файлы Excel. Подумайте о редактировании таблиц, работа с формулами, чтение данных.
Openxyl работает с файлами Excel на диске, что означает, что он читает или записывает в файлы .xlsx, сохраненные на вашем компьютере. Вам не нужны Xlwings для этого.
Пример ниже открывает файл, обновляет его и сохраняет изменения.
Основы XLSWRITER
Xlswriter создает новые файлы Excel с расширенным форматированием и диаграммами. Он не может изменить существующие файлы. Подумайте, генерируя отчет или файл с нуля.
XLSWRITER — отдельная библиотека Python. Он записывает файлы непосредственно на диск, но не взаимодействует с открытым приложением Excel.
Пример ниже создает форматированный файл Excel.
Стиль и условное форматирование
Стиль делает отчеты визуально привлекательными и легче читать.
Вы можете использовать OpenXL, чтобы настроить внешний вид вашего файла Excel. Вы можете установить такие вещи, как цвета, шрифты и границы.
Условное форматирование выделяет тенденции или важные данные автоматически на основе значений ячеек. Используйте условное форматирование по поводу стиля, чтобы получить выбросы, пороговые значения данных или сделать анализ более интуитивно понятным.
Условное форматирование ниже выделяет все клетки более 150.
автоматизация отчетов и мониторинга
автоматизация является огромной причиной, по которой разработчики любят Python, и она хорошо переводится для Excel пользователей.
Планирование сценариев
Это знакомый рабочий процесс для всех, кто запланировал сценарии раньше. Вы можете просто использовать Cron для Mac/Linux или планировщик задач Windows в Windows. Это запланирует сценарии Python в установленное время.
По электронной почте сгенерированные рабочие тетради
Встроенная библиотека SMTP и SMTP и Python работает вместе, чтобы отправить ваши рабочие книги в качестве электронного письма. Они позволяют вашему скрипту Python подключаться к электронной почте и отправлять электронные письма с помощью вложений. Эта интеграция делает ваши рабочие процессы Excel сквозными автоматизированными.
Пример ниже отправляет файл Excel в качестве электронного письма:
Соображения производительности и безопасности Управление памятью и большими наборами данных
Подобно любому другому большему файлу, при работе с большими файлами Excel производительность может быстро ухудшаться, если вы загружаете все в память одновременно. Поскольку эти файлы Excel обычно довольно большие, вот несколько советов:
- Читать файлы в кусках: У Pandas есть хорошие инструменты для этого, такие как pandas.read_csv (…, chunkize = 50000) и pandas.read_excel (). Это поможет обработать данные меньшими частями, уменьшить память и предотвратить сбои.
- Использовать базы данных: Вы можете разгрузить тяжелую обработку данных или агрегацию в базу данных (SQL, Postgres) вместо использования Python. Затем вы можете получить суммированные или отфильтрованные данные в Python для анализа или отчетности.
- Избегайте ненужных копий: Предоставление операций на месте над глубокими копиями, если только не требуется при работе с DataFrames.
Песочница и безопасность макросов
Запуск сценариев Python и макросов в Excel представляет риски безопасности, особенно если файлы поступают из неизвестных или ненадежных источников. Вот несколько советов:
- Проверить и сканировать файлы: Всегда сканируйте файлы Excel на предмет вредоносных макросов или встроенных сценариев, прежде чем запустить их.
- Используйте виртуальные среды: В корпоративных или общих средах запустите код Python внутри изолированной виртуальной среды (Venv или Conda), чтобы содержать зависимости и снизить риск воздействий на общую систему.
- Ограничить настройки макроса: Настройте настройки настройки безопасности макроса Excel, чтобы отключить или пригласить перед запуском макросов из ненадежных источников.
Заключение
Прочитав этот урок, вы можете быть на пути к работе с Python в Excel. Хотя мы только поцарапали поверхность того, что Python и Excel могут сделать вместе, это, безусловно, начало. Теперь вы можете работать умнее, обрабатывать большие данные и создавать более проницательные таблицы, чем когда -либо прежде.
Trending Stories youtube.com/thenewstack Tech движется быстро, не пропустите эпизод. Подпишитесь на наш канал YouTube, чтобы транслировать все наши подкасты, интервью, демонстрации и многое другое. Группа подпишитесь с эскизом. Джессика Вахтел — писатель по маркетингу разработчиков в InfluxData, где она создает контент, который помогает сделать данные о мире временных рядов более понятными и доступными. Джессика имеет опыт работы в разработке программного обеспечения и технической журналистике. Подробнее от Джессики Вахтел
