Узнайте, как использовать Excel для автоматизации отчетов Excel

Excel мощен и везде. Но это также повторяющееся и ручное. Конечно, вы можете использовать такие инструменты, как VBA, для автоматизации отчетов, но использование языка общего назначения, такого как Python, позволяет автоматизировать более широкие аспекты ваших процессов отчетности (например, перемещение файлов и отправку файлов по электронной почте).

К концу этого поста вы узнаете, как:

  1. Как объединить несколько файлов Excel в один файл
  2. Суммируйте данные Excel с помощью сводных таблиц Pandas
  3. Добавление строк заголовков в отчеты Excel
  4. Добавление динамических диаграмм в ваши файлы Excel с помощью Python
  5. Стилизация файлов Excel с помощью Python

Давайте начнем!

Хотите посмотреть это как обучающее видео? Посмотрите мой полный видеоурок ниже:

Настройка вашей среды

Чтобы следовать этому руководству, мы будем работать с тремя библиотеками: os, pandas и openpyxl. Первый из них, os, связан с Python, поэтому его не нужно устанавливать. Однако два других необходимо установить с помощью pip или conda.

Давайте посмотрим, как вы можете установить эти библиотеки в Python:

Использование любого из указанных выше методов в вашем терминале установит необходимые библиотеки. Теперь давайте посмотрим на данные, которые вы можете использовать, чтобы следовать этому руководству.

Теперь, когда у нас установлены библиотеки, мы можем импортировать библиотеки и классы, которые будем использовать:

Вы можете скачать файлы здесь. ZIP-файл содержит 3 разных файла Excel, каждый из которых содержит информацию о продажах за разные месяцы. Такое хранение данных имеет смысл, но может затруднить анализ данных.

В связи с этим нам нужно сначала объединить все эти файлы. Это то, что вы узнаете в следующем разделе!

Объединение нескольких файлов Excel с Python

Библиотека Pandas хранит данные в объекте DataFrame, который можно рассматривать как таблицу Excel (хотя это немного упрощение). Давайте разберем, что мы хотим сделать, а затем посмотрим, как это сделать с помощью Python:

  1. Соберите все наши файлы в список Python
  2. Переберите каждый файл и добавьте его в Pandas DataFrame.

Давайте напишем код и посмотрим, как это сделать на Python!

Давайте разберем, что мы здесь сделали:

  1. В разделе 1 мы сначала загрузили путь, по которому сохранены файлы, и использовали функцию os.list(), чтобы получить список всех файлов, содержащихся в этой папке.
  2. В разделе 2 мы сначала создали пустой DataFrame, затем перебрали каждый файл, загрузили его в DataFrame и добавили к нашему combined DataFrame.
  3. Наконец, мы сохранили файл в отдельный файл Excel.

Это было легко, не так ли? Есть много разных способов выполнить эту задачу, и в этом вся прелесть Python!

Теперь, когда наши данные загружены, давайте узнаем, как суммировать наши данные с помощью Pandas.

Обобщение данных Excel с помощью Pandas

В этом разделе вы узнаете, как использовать Pandas для создания сводной таблицы! Для этого мы будем использовать функцию Pandas .pivot_table() с метким названием.

Эта функция предназначена для знакомства со сводными таблицами в Excel. Благодаря этому мы можем понять, как мы хотели бы обобщить наши данные. Допустим, мы хотели выяснить, какова общая сумма, проданная каждым продавцом. Мы могли бы написать следующее:

В приведенном выше коде мы использовали функцию Pandas .pivot_table() для создания сводной таблицы. Многое из этого должно быть похоже на сводную таблицу Excel. Однако по умолчанию Pandas будет использовать функцию агрегации 'mean', поэтому нам нужно установить для нее значение 'sum'.

Добавление строк заголовков в отчеты Excel с помощью OpenPyxl

В этом разделе вы узнаете, как добавлять описательные строки заголовков в отчеты Excel, чтобы сделать их более готовыми к печати. Для этого мы начнем использовать библиотеку Openpyxl.

Openpyxl работает, загружая книгу в память. Оттуда вы можете получить доступ к различным атрибутам и объектам внутри него, таким как рабочие листы и ячейки в этих рабочих листах.

Ключевое отличие состоит в том, что вы работаете непосредственно с рабочей книгой. В Pandas мы просто сохраняли в рабочую книгу. (Я признаю, что это гораздо больше, но это хороший способ думать об этом.)

В приведенном выше коде:

  1. Мы загрузили объект рабочей книги wb. Оттуда мы получили доступ к рабочему листу.
  2. Мы смогли манипулировать рабочим листом, используя метод .insert_rows() для вставки трех строк.
  3. Эти строки были добавлены с индексом 0 (первая строка) и включали три строки. Мы присвоили полезные значения двум ячейкам.
  4. Наконец, мы сохранили книгу, используя метод Openpyxl .save().

Добавление динамических диаграмм в Excel с помощью Python

В этом разделе мы рассмотрим добавление диаграммы в файл Excel. Одна из замечательных особенностей библиотеки Openpyxl заключается в том, что мы можем создавать диаграммы на основе Excel, которые остаются динамическими для данных.

Чтобы сделать нашу диаграмму динамичной, нам нужно создать Reference() объектов, которые, как следует из названия, содержат ссылки на места в нашей рабочей книге.

Мы создадим гистограмму и добавим данные и категории в книгу. Давайте посмотрим, как мы можем это сделать:

Давайте разберем, что делает приведенный выше код:

  1. Мы устанавливаем два объекта Reference: один для наших данных и один для наших категорий. Эти эталонные объекты привяжут наш график к определенным ячейкам, что позволит ему оставаться динамичным.
  2. Мы добавляем объект BarChart. Для диаграммы мы используем методы .add_data() и .set_categories() для передачи наших объектов Reference.
  3. Наконец, мы добавляем диаграмму в определенную точку привязки на нашем листе.

Стилизация отчетов Excel с помощью Python

В этом последнем разделе мы рассмотрим, как мы можем стилизовать нашу книгу с помощью OpenPyxl. OpenPyxl может добавлять стили к ячейкам в книге Excel на основе стилей, существующих в Excel.

Это означает, что мы можем добавлять такие стили, как Title и currency. Давайте посмотрим, как это работает, используя атрибут .style:

В приведенном выше примере мы использовали атрибут .style для назначения разных стилей разным ячейкам. Причина, по которой мы используем цикл for в ячейках 5–6, заключается в том, что OpenPyxl не позволяет назначать стили диапазонам, поэтому мы назначаем их по одному.

Что делать дальше?

Большой! Вы автоматизировали свои скучные отчеты Excel! Теперь, что вы должны посмотреть дальше? Я бы рекомендовал подумать о других элементах, которые вам, возможно, потребуется автоматизировать. Например:

  • Как вы можете добавить имена на рабочие листы?
  • Как вы можете автоматически отправить полученный файл по электронной почте?
  • Как вы можете стилизовать значения в виде таблиц?

Заключение

Спасибо, что прочитали весь урок! Надеюсь, вам понравилось и вы чему-то научились. Использование Python для автоматизации вашей работы может быть действительно полезным для изучения. Если вы хотите узнать об этом больше, рассмотрите возможность подписки на мой канал YouTube, нажав здесь.