Начало работы с Python в Excel
Python в Excel в настоящее время находится в предварительной версии и может быть изменен в зависимости от отзывов. Чтобы использовать эту функцию, присоединитесь к программе предварительной оценки Microsoft 365 и выберите уровень предварительной оценки канала бета-версии .
У вас нет доступа к программе предварительной оценки? Зарегистрируйтесь с помощью учетной записи Майкрософт, рабочей или учебной учетной записи, чтобы получать уведомления о будущей доступности Python в Excel.
Python в Excel постепенно развертывается в Excel для клиентов Windows, использующих бета-канал. В настоящее время эта функция недоступна на других платформах.
Если у вас возникли проблемы с Python в Excel, сообщите о них, выбрав Справка > отзывов в Excel.
Не знакомы с Python в Excel? Начните с введение в Python в Excel.
Начало использования Python
Чтобы начать использовать Python в Excel, выберите ячейку и на вкладке Формулы выберите Вставить Python. Это сообщает Excel о том, что вы хотите написать формулу Python в выбранной ячейке.
Или используйте функцию =PY в ячейке, чтобы включить Python. Введя в ячейку =PY, выберите PY в меню автозаполнения функции со стрелкой вниз и клавишами TAB или добавьте в функцию открываемую скобку: =PY(. Теперь можно ввести код Python непосредственно в ячейку. На следующем снимке экрана показано меню Автозаполнения с выбранной функцией PY.
После включения Python в ячейке в этой ячейке отображается зеленый значок PY . При выборе ячейки Python в строке формул отображается тот же значок PY. Пример см. на следующем снимку экрана.
Объединение python с ячейками и диапазонами Excel
Чтобы ссылаться на объекты Excel в ячейке Python, убедитесь, что ячейка Python находится в режиме правки, а затем выберите ячейку или диапазон, которые нужно включить в формулу Python. При этом ячейка Python автоматически заполняется адресом выбранной ячейки или диапазона.
Совет: Используйте сочетание клавиш F2 для переключения между режимом ввод и режим правки в ячейках Python. Переключение в режим правки позволяет изменить формулу Python, а переключение в режим Ввод позволяет выбрать дополнительные ячейки или диапазоны с помощью клавиатуры.
Python в Excel использует пользовательскую функцию Python xl() для взаимодействия между Excel и Python. Функция xl() принимает такие объекты Excel, как диапазоны, таблицы, запросы и имена.
Вы также можете напрямую вводить ссылки в ячейку Python с помощью функции xl() . Например, для ссылки на ячейку A1 используйте xl(«A1») , а для диапазона B1:C4 — xl(«B1:C4») . Для таблицы с заголовками MyTable используйте xl(«MyTable[#All]», headers=True) . Описатель [#All] гарантирует, что вся таблица анализируется в формуле Python, а headers=True обеспечивает правильную обработку заголовков таблицы. Дополнительные сведения об описателях, таких как [#All], см. в статье Использование структурированных ссылок с таблицами Excel.
На следующем рисунке показано вычисление Python в Excel с добавлением значений ячеек A1 и B1 с результатом Python, возвращенным в ячейку C1.
Строка формул
Используйте строку формул для редактирования, подобного коду, например для создания новых строк с помощью клавиши ВВОД. Разверните строку формул, используя значок стрелки вниз, чтобы просмотреть несколько строк кода одновременно. Вы также можете использовать сочетание клавиш CTRL+SHIFT+U , чтобы развернуть строку формул. На следующих снимках экрана показана строка формул до и после ее развертывания для просмотра нескольких строк кода Python.
Перед развертыванием строки формул:
После развертывания строки формул:
Типы выходных данных
Используйте меню выходных данных Python в строке формул, чтобы управлять тем, как возвращаются вычисления Python. Возвращает вычисления в виде объектов Python или преобразует вычисления в значения Excel и выводит их непосредственно в ячейку. На следующем снимку экрана показана формула Python, возвращаемая в виде значения Excel.
Совет: Вы также можете использовать контекстное меню, чтобы изменить тип выходных данных Python. Откройте контекстное меню, перейдите в раздел Вывод Python, а затем выберите нужный тип вывода.
На следующем снимке экрана показана та же формула Python, что и на предыдущем снимке экрана, теперь возвращенная в качестве объекта Python. Когда формула возвращается в виде объекта Python, в ячейке отображается значок карта.
Примечание: Результаты формул, возвращаемые значениям Excel, превратятся в их ближайший эквивалент Excel. Если вы планируете повторно использовать результат в будущих вычислениях Python, рекомендуется вернуть результат в виде объекта Python. Возврат результата в виде значений Excel позволяет запускать аналитику Excel, например диаграммы Excel, формулы и условное форматирование, для значения.
Объект Python содержит дополнительные сведения в ячейке. Чтобы просмотреть дополнительные сведения, откройте карта, щелкнув значок карта. Сведения, отображаемые на карта, являются предварительным просмотром объекта, который полезен при обработке больших объектов.
Python в Excel может возвращать многие типы данных в виде объектов Python. Полезным типом данных Python в Excel является объект DataFrame. Дополнительные сведения о кадрах данных Python см. в статье Python в кадрах данных Excel.
Внешние данные
Чтобы импортировать внешние данные, используйте функцию Получить преобразование & в Excel. Преобразование get & использует Power Query для импорта внешних данных. Все данные, обрабатываемые с помощью Python в Excel, должны поступать с листа или через Power Query. Дополнительные сведения см. в статье Использование данных Power Query с Python в Excel.
Важно: Для защиты безопасности распространенные функции внешних данных в Python, такие как pandas.read_csv и pandas.read_excel, несовместимы с Python в Excel. Дополнительные сведения см . в статье Безопасность данных и Python в Excel.
Порядок вычислений
Традиционные операторы Python вычисляют сверху вниз. В ячейке Python в Excel операторы Python выполняют то же самое — вычисляют сверху вниз. Но на листе Python в Excel ячейки Python вычисляют в порядке крупных строк. Вычисления ячеек выполняются по строке (от столбца A до столбца XFD), а затем по каждой следующей строке на листе.
Инструкции Python упорядочены, поэтому каждая инструкция Python имеет неявную зависимость от инструкции Python, которая непосредственно предшествует ей в порядке вычисления.
Порядок вычислений важен при определении переменных на листе и ссылки на них, так как необходимо определить переменные, прежде чем можно будет ссылаться на них.
Важно: Порядок вычисления основных строк также применяется на всех листах в книге и основан на порядке листов в книге. Если вы используете несколько листов для анализа данных с помощью Python в Excel, обязательно включите данные и все переменные, хранящее данные в ячейках и листах перед ячейками и листами, которые анализируют эти данные.
Пересчета
При изменении зависимого значения ячейки Python все формулы Python пересчитываются последовательно. Чтобы приостановить пересчеты Python и повысить производительность, используйте режим частичного вычисления или ручного вычисления . Эти режимы позволяют активировать вычисление, когда вы будете готовы. Чтобы изменить этот параметр, перейдите на ленту и выберите Формулы, а затем откройте раздел Параметры вычисления. Затем выберите нужный режим вычисления. Режимы частичного вычисления и вычисления вручную приостанавливают автоматический пересчет как для Python, так и для таблиц данных.
Отключение автоматического пересчета в книге во время разработки Python может повысить производительность и скорость вычисления отдельных ячеек Python. Однако необходимо вручную пересчитать книгу, чтобы обеспечить точность в каждой ячейке Python. Существует три способа пересчета книги вручную в режиме частичного вычисления или ручного вычисления .
- Используйте сочетание клавиш F9.
- Перейдите к разделу Формулы >вычислить сейчас на ленте.
- Перейдите в ячейку с устаревшим значением, отображаемым с форматированием зачеркиванием, и выберите символ ошибки рядом с этой ячейкой. Затем в меню выберите Вычислить сейчас.
Ошибки
Вычисления Python в Excel могут возвращать такие ошибки, как #PYTHON!, #BUSY!и #CONNECT! в ячейки Python. Дополнительные сведения см. в статье Устранение ошибок Python в Excel.
Статьи по теме
- Общие сведения о Python в Excel
- Введение в Python — обучение | Microsoft Learn
- Устранение ошибок Python в Excel
- Безопасность данных и Python в Excel
- Python в кадрах данных Excel
- Создание графиков и диаграмм Python в Excel
- Использование данных Power Query с Python в Excel
Чтение и запись файлов Excel (XLSX) в Python
Pandas можно использовать для чтения и записи файлов Excel с помощью Python. Это работает по аналогии с другими форматами. В этом материале рассмотрим, как это делается с помощью DataFrame.
Помимо чтения и записи рассмотрим, как записывать несколько DataFrame в Excel-файл, как считывать определенные строки и колонки из таблицы и как задавать имена для одной или нескольких таблиц в файле.
Установка Pandas
Для начала Pandas нужно установить. Проще всего это сделать с помощью pip .
Если у вас Windows, Linux или macOS:
pip install pandas # или pip3В процессе можно столкнуться с ошибками ModuleNotFoundError или ImportError при попытке запустить этот код. Например:
ModuleNotFoundError: No module named 'openpyxl'В таком случае нужно установить недостающие модули:
pip install openpyxl xlsxwriter xlrd # или pip3Запись в файл Excel с python
Будем хранить информацию, которую нужно записать в файл Excel, в DataFrame . А с помощью встроенной функции to_excel() ее можно будет записать в Excel.
Сначала импортируем модуль pandas . Потом используем словарь для заполнения DataFrame :
import pandas as pd
df = pd.DataFrame( 'FC Bayern München', 'FC Barcelona', 'Juventus'],
'League': ['English Premier League (1)', 'Spain Primera Division (1)',
'English Premier League (1)', 'German 1. Bundesliga (1)',
'Spain Primera Division (1)', 'Italian Serie A (1)'],
'TransferBudget': [176000000, 188500000, 90000000,
100000000, 180500000, 105000000]>)Ключи в словаре — это названия колонок. А значения станут строками с информацией.
Теперь можно использовать функцию to_excel() для записи содержимого в файл. Единственный аргумент — это путь к файлу:
df.to_excel('./teams.xlsx')А вот и созданный файл Excel:
Стоит обратить внимание на то, что в этом примере не использовались параметры. Таким образом название листа в файле останется по умолчанию — «Sheet1». В файле может быть и дополнительная колонка с числами. Эти числа представляют собой индексы, которые взяты напрямую из DataFrame.
Поменять название листа можно, добавив параметр sheet_name в вызов to_excel() :
df.to_excel('./teams.xlsx', sheet_name='Budgets', index=False)Также можно добавили параметр index со значением False , чтобы избавиться от колонки с индексами. Теперь файл Excel будет выглядеть следующим образом:
Запись нескольких DataFrame в файл Excel
Также есть возможность записать несколько DataFrame в файл Excel. Для этого можно указать отдельный лист для каждого объекта:
salaries1 = pd.DataFrame( 'Salary': [560000, 220000, 125000]>)
salaries2 = pd.DataFrame( 'Salary': [370000, 270000, 240000]>)
salaries3 = pd.DataFrame( 'Salary': [160000, 260000, 250000]>)
salary_sheets =
writer = pd.ExcelWriter('./salaries.xlsx', engine='xlsxwriter')
for sheet_name in salary_sheets.keys():
salary_sheets[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)
writer.save()Здесь создаются 3 разных DataFrame с разными названиями, которые включают имена сотрудников, а также размер их зарплаты. Каждый объект заполняется соответствующим словарем.
Объединим все три в переменной salary_sheets , где каждый ключ будет названием листа, а значение — объектом DataFrame .
Дальше используем движок xlsxwriter для создания объекта writer . Он и передается функции to_excel() .
Перед записью пройдемся по ключам salary_sheets и для каждого ключа запишем содержимое в лист с соответствующим именем. Вот сгенерированный файл:
Можно увидеть, что в этом файле Excel есть три листа: Group1, Group2 и Group3. Каждый из этих листов содержит имена сотрудников и их зарплаты в соответствии с данными в трех DataFrame из кода.
Параметр движка в функции to_excel() используется для определения модуля, который задействуется библиотекой Pandas для создания файла Excel. В этом случае использовался xslswriter , который нужен для работы с классом ExcelWriter . Разные движка можно определять в соответствии с их функциями.
В зависимости от установленных в системе модулей Python другими параметрами для движка могут быть openpyxl (для xlsx или xlsm) и xlwt (для xls). Подробности о модуле xlswriter можно найти в официальной документации.
Наконец, в коде была строка writer.save() , которая нужна для сохранения файла на диске.
Чтение файлов Excel с python
По аналогии с записью объектов DataFrame в файл Excel, эти файлы можно и читать, сохраняя данные в объект DataFrame . Для этого достаточно воспользоваться функцией read_excel() :
Пишем файл Excel из Python
Если вдруг вам потребуется, к примеру, выгружать отчеты из вашей программы, почему бы не воспользоваться общепринятым офисным форматом – Excel? В этом нет ничего сложного, потому что есть прекрасная библиотека XlsxWriter. Приведу для вас немного примеров из документации с собственными дополнениями. Итак, поехали с установки:
pip install XlsxWriterПростейший пример, думаю, не вызовет вопросов, если вы знакомы с Excel: открыли файл, добавили лист, записали по адресу ячейки текст:
import xlsxwriter # открываем новый файл на запись workbook = xlsxwriter.Workbook('hello.xlsx') # создаем там "лист" worksheet = workbook.add_worksheet() # в ячейку A1 пишем текст worksheet.write('A1', 'Hello world') # сохраняем и закрываем workbook.close()Сразу отмечу, что можно адресовать ячейки не только по строке типа А1 или C15, а непосредственно по индексам колонки и строки, но нумерация начинается в таком случае с нуля (0).
worksheet.write(0, 0, 'Это A1!') worksheet.write(4, 3, 'Колонка D, стока 5')Формулы
Естественно, мы можем добавить в ячейки формулы, как мы делаем это руками в Excel – нужно начать выражение со знака равно (=). Пример: в конце таблицы трат введем подсчет суммы:
import xlsxwriter workbook = xlsxwriter.Workbook('formula.xlsx') worksheet = workbook.add_worksheet() # данные expenses = ( ['Аренда', 1000], ['Комуналка', 100], ['Еда', 300], ['Качалка', 50], ) for i, (item, cost) in enumerate(expenses, start=1): worksheet.write(f'A', item) worksheet.write(f'B', cost) # колонкой ниже добавить подсчет суммы worksheet.write('A5', 'Итого:') worksheet.write('B5', '=SUM(B1:B4)') # сохраняем и закрываем workbook.close()Я пользуюсь программой Numbers на macOS, в MS Office будет более привычный вид. Вот что получилось у меня:
Формат
Таблица получилась немного скучновата и невыразительна. Давайте добавим форматы ячейкам, а именно ячейки столбца B сделаем в формате денег, а графу «Итого:» и заголовки – жирными. Формат создается как отдельная переменная, и его передают третьим аргументом после аргумента-содержимого ячейки.
# формат для денег money = workbook.add_format() # формат жирности шрифта bold = workbook.add_format() worksheet.write('A1', 'Наименование', bold) worksheet.write('B1', 'Потрачено', bold) for i, (item, cost) in enumerate(expenses, start=2): worksheet.write(f'A', item) worksheet.write(f'B', cost, money) # колонкой ниже добавить подсчет суммы worksheet.write('A6', 'Итого:', bold) worksheet.write('B6', '=SUM(B2:B5)', money)
Все подробности о форматах ищите тут. Там рассказано о размере и стиле шрифта, цвете и многом другом. На английском, но думаю, с базовым уровнем даже разберетесь при помощи картинок.
Вот, что там не написано, а хотелось бы улучшить – задать ширину столбца (колонки), чтобы влезал весь текст. Делается это так:
# для каждой колонки отдельно (первый и второй аргументы совпадают) worksheet.set_column(0, 0, 15) worksheet.set_column(1, 1, 20) # или # задать колонкам в диапазоне от 0 до 1 каждой – ширину 15 worksheet.set_column(0, 1, 15) # или по названиям: worksheet.set_column('A:B', 15) worksheet.set_column('C:C', 20)В каких единицах измеряется ширина? Черт его знает, это не сказано в документации, может, в сантидюймах? Подбирайте на глазок. Мой итог:
Графики
Они есть! Давайте построим график.
Шаг 1: зададим данные. Можно писать массив прямо в колонку, а не по каждой ячейке отдельно:
data = [ [1, 2, 3, 4, 5], [2, 4, 6, 8, 10], [3, 6, 9, 12, 15], ] # можно писать сразу колонками! worksheet.write_column('A1', data[0]) worksheet.write_column('B1', data[1]) worksheet.write_column('C1', data[2])Шаг 2. Создадим график, задав его тип – в данном случае: диаграмма-столбики. Потом зададим серии данных.
chart = workbook.add_chart() # добавим три последовательности данных chart.add_series() chart.add_series() chart.add_series()Так, стоп! Что значит эта страшная строка? Она говорит, что нужно взять ячейки с листа «Sheet1» и так далее. А можно попроще? Да – задать данные через числовые координаты списком. А еще за одно в цикл завернем:
worksheet.name = 'Первый лист' for col, series in enumerate(data): chart.add_series(< # имя листа, строка начала, колонка начала, строка конца, колонка конца 'values': [worksheet.name, 0, col, 4, col], 'name': f'Серия ' >)Шаг 3: вставить наш график в нужную ячейку:
# и вставим его в ячейку A7 worksheet.insert_chart('A7', chart)Вот, как это выглядит:
Вообще, библиотека очень богатая. Доступно множество форматов и видов графиков и диаграмм. А еще можно объединять и разделять ячейки и даже включать макросы и скрипты!
Специально для канала @pyway. Подписывайтесь на мой канал в Телеграм @pyway ��
Чтение и запись данных с помощью Pandas в Microsoft Fabric
Записные книжки Microsoft Fabric поддерживают простое взаимодействие с данными Lakehouse с помощью Pandas, самой популярной библиотеки Python для изучения и обработки данных. В записной книжке пользователи могут быстро считывать данные из (и записывать данные обратно в) их Lakehouse в различных форматах файлов. В этом руководстве приведены примеры кода, которые помогут вам приступить к работе с собственной записной книжкой.
Необходимые компоненты
- Получение подписки Microsoft Fabric. Или зарегистрируйте бесплатную пробную версию Microsoft Fabric.
- Войдите в Microsoft Fabric.
- Перейдите к интерфейсу Обработка и анализ данных с помощью значка переключателя интерфейса в левой части домашней страницы.
Загрузка данных Lakehouse в записную книжку
Подключив Lakehouse к записной книжке Microsoft Fabric, вы можете изучить сохраненные данные, не выходя из страницы и прочитав его в записную книжку в случае щелчков. Выбор всех параметров поверхностей файлов Lakehouse для загрузки данных в Spark или Кадр данных Pandas. (Вы также можете скопировать полный путь ABFS файла или понятный относительный путь.)
Щелкнув один из запросов "Загрузить данные", создайте ячейку кода для загрузки этого файла в кадр данных в записной книжке.
Преобразование кадра данных Spark в кадр данных Pandas
Для справки в следующей команде показано, как преобразовать кадр данных Spark в кадр данных Pandas.
# Replace "spark_df" with the name of your own Spark DataFrame pandas_df = spark_df.toPandas() Чтение и запись различных форматов файлов
Приведенные ниже примеры кода документирует операции Pandas для чтения и записи различных форматов файлов.
В следующих примерах необходимо заменить пути к файлам. Pandas поддерживает как относительные пути, так и полные пути ABFS. Их можно получить и скопировать из интерфейса в соответствии с предыдущим шагом.
Чтение данных из CSV-файла
import pandas as pd # Read a CSV file from your Lakehouse into a Pandas DataFrame # Replace LAKEHOUSE_PATH and FILENAME with your own values df = pd.read_csv("/LAKEHOUSE_PATH/Files/FILENAME.csv") display(df) Запись данных в ВИДЕ CSV-файла
import pandas as pd # Write a Pandas DataFrame into a CSV file in your Lakehouse # Replace LAKEHOUSE_PATH and FILENAME with your own values df.to_csv("/LAKEHOUSE_PATH/Files/FILENAME.csv") Чтение данных из файла Parquet
import pandas as pd # Read a Parquet file from your Lakehouse into a Pandas DataFrame # Replace LAKEHOUSE_PATH and FILENAME with your own values df = pandas.read_parquet("/LAKEHOUSE_PATH/Files/FILENAME.parquet") display(df) Запись данных в виде файла Parquet
import pandas as pd # Write a Pandas DataFrame into a Parquet file in your Lakehouse # Replace LAKEHOUSE_PATH and FILENAME with your own values df.to_parquet("/LAKEHOUSE_PATH/Files/FILENAME.parquet") Чтение данных из файла Excel
import pandas as pd # Read an Excel file from your Lakehouse into a Pandas DataFrame # Replace LAKEHOUSE_PATH and FILENAME with your own values df = pandas.read_excel("/LAKEHOUSE_PATH/Files/FILENAME.xlsx") display(df) Запись данных в виде файла Excel
import pandas as pd # Write a Pandas DataFrame into an Excel file in your Lakehouse # Replace LAKEHOUSE_PATH and FILENAME with your own values df.to_excel("/LAKEHOUSE_PATH/Files/FILENAME.xlsx") Чтение данных из JSON-файла
import pandas as pd # Read a JSON file from your Lakehouse into a Pandas DataFrame # Replace LAKEHOUSE_PATH and FILENAME with your own values df = pandas.read_json("/LAKEHOUSE_PATH/Files/FILENAME.json") display(df) Запись данных в виде JSON-файла
import pandas as pd # Write a Pandas DataFrame into a JSON file in your Lakehouse # Replace LAKEHOUSE_PATH and FILENAME with your own values df.to_json("/LAKEHOUSE_PATH/Files/FILENAME.json") Следующие шаги
- Очистка и подготовка данных с помощью Wrangler
- Начало обучения моделей машинного обучения
Обратная связь
Были ли сведения на этой странице полезными?