如何用 Python 实现 Excel 任务自动化

Excel 是一个很棒的数据分析常用工具。

数据分析人员可以随时用 Excel 修改、检查和显示大量的数据，更轻松地获得洞见并做出明智的选择。

Excel 的多功能性使用户可以进行各种数据分析活动，从简单的数学运算到复杂的统计分析。此外，Excel 通过使用第三方程序（如 Python）或内置编程语言 VBA 实现自动化。

由于 Excel 的多功能性和实用性，它经常被用于各行各业的数据分析，包括银行、医疗保健和市场营销。

但作为一个数据分析师，你可能经常发现自己在使用 Excel 时每天都在重复一些琐碎的任务。

这些任务可能包括复制和粘贴数据、格式化单元格，以及创建图表等等。随着时间的推移，这可能会变得单调和耗时，让你没有更多的时间来关注数据分析的更重要的方面，如识别趋势、异常值和获得洞见。

这就是为什么使用 Python 实现 Excel 的自动化可以改变游戏规则，帮助你简化工作流程，腾出时间进行更有意义的分析。

在本教程中，我将向你展示一些使用 Python 编程创建、更新和分析 Excel 电子表格的有用方法。让我们深入了解一下。

如何用 Python 合并两个独立的电子表格

数据分析师经常要在许多电子表格上工作，当你要把这些文件合并在一起时，会比较麻烦。

下面的代码可以帮助你将两个独立的文件合并在一起：

import pandas as pd

# 读取两个 Excel 文件

file1 = pd.read_excel('file1.xlsx')file2 = pd.read_excel('file2.xlsx')

# 使用 concat() 方法合并两个文件
merged_file = pd.concat([file1, file2], ignore_index=True)

# 将被合并的文件写入一个新的 Excel 文件
merged_file.to_excel('merged_file.xlsx', index=False)
1
2
3
4
5
6
7
8
9
10
11

在这段代码中，我们首先导入 Pandas 库，我们将用它来读入和操作 Excel 文件。

然后，我们使用 read_excel() 方法来读入 file1.xlsx 和 file2.xlsx。接下来，我们使用 concat() 方法将这两个文件合并在一起。ignore_index=True 参数确保两个文件的索引值被重置，所以合并后的文件中不会出现重复的索引值。

最后，我们使用 to_excel() 方法将合并后的文件写入一个名为 merged_file.xlsx 的新 Excel 文件中。我们还设置了 index=False，以确保索引列不包括在输出文件中。

如何用 Python 导入和导出数据

这项任务涉及到使用 Python 库，如 Pandas，将 Excel 文件读入一个 DataFrame 对象。然后你可以用 Python 操作它，并对它进行分析。

你也可以使用同样的库将数据从 Python 导出到 Excel 文件中：

import pandas as pd
# 导入 Excel 文件

df = pd.read_excel('filename.xlsx', sheet_name='Sheet1')

# 导出到 Excel 文件
df.to_excel('new_filename.xlsx', index=False)
1
2
3
4
5
6
7

以上代码导入了 Pandas 库，并从工作簿的 Sheet1 中读取一个名为 filename.xlsx 的 Excel 文件，将数据存储在一个名为 df 的 Pandas 数据框中。然后使用 to_excel 方法将数据框导出到一个名为 new_filename.xlsx 的新 Excel 文件中。index=False 参数用于排除输出文件中的行索引。

本质上，该代码使用 Pandas 将原始 Excel 文件的内容复制到一个新文件中。

如何使用 Python 清理和转换数据

这项任务涉及使用 Python 库，如 Pandas，来清理和转换 Excel 中的数据。

这可能包括删除重复的数据、根据特定标准过滤数据，以及对数据进行计算。

import pandas as pd

# 删除重复数据
df = df.drop_duplicates()

# Filter data
df = df[df['column_name'] > 10]

# 执行计算
df['new_column'] = df['column1'] + df['column2']
1
2
3
4
5
6
7
8
9
10

上面的代码片段使用 Pandas 库在一个名为 df 的 Pandas 数据框上执行数据清理和操作任务。

首先，它使用 drop_duplicates 方法从 df 中删除重复的行。其次，它通过选择 column_name 列的值大于 10 的行来过滤 df 数据框，并将过滤后的结果分配给一个名为 data_df 的新数据框。

最后，一个名为 new_column 的新列被添加到 df 中，其中包含 column1 和 column2 的数值之和。

总的来说，这段代码通过删除重复的数据、过滤特定的行，以及在原始数据框中添加新的计算列，有效地清理和处理了数据。

如何用 Python 进行数据分析

这项任务涉及使用 Python 库，如 Pandas 和 NumPy，对 Excel 数据进行数据分析。

这可能包括计算汇总统计，如平均值和标准差，或者通过基于特定标准的数据分组来创建自定义报告。

import pandas as pd
import numpy as np

# 计算汇总统计
df.describe()
# 创建自定义报告
df.pivot_table(values='column_name', index='category_name', columns='date')
1
2
3
4
5
6
7

该代码利用 Pandas 和 NumPy 库，在名为 df 的 Pandas 数据框上执行数据分析和报告任务。

首先，它使用 describe 方法计算数据框中数字数据的汇总统计。这个方法对数据的分布、中心趋势和分散性产生了有价值的见解。

其次，代码使用 pivot_table 方法从数据框中创建自定义报告。这种方法总结和汇总了数据框中的数据，并可以产生各种格式的表格。

在这段代码中，它生成了一个新的数据框，其中 column_name 值被 category_name 和 date 列分组。

总的来说，这段代码在数据框上执行统计分析和报告任务，以便从数据中获得洞见。

如何用 Python 创建图表

这项任务涉及使用 Python 库，如 matplotlib 或 seaborn，从 Excel 数据中创建图表和图形。

你可以定制这些图表，以显示特定的数据，并将其格式化以满足特定的要求。

import pandas as pd
import matplotlib.pyplot as plt
# 创建条形图
df.plot(kind='bar', x='category_name', y='sales')
plt.show()
# 创建散点图
df.plot(kind='scatter', x='column1', y='column2')plt.show()
1
2
3
4
5
6
7

该代码导入了两个库，Pandas 和 matplotlib.pyplot，分别使用别名 pd 和 plt。

然后，Pandas 的 plot 方法被用来创建两种类型的图。第一种类型的图是一个柱状图，显示 df 数据框中 category_name 和 sales 列之间的关系。

第二种类型的图是一个散点图，显示同一数据框中 column1 和 column2 列之间的关系。对柱状图使用参数 kind=‘bar’，对散点图使用参数 kind=‘scatter’。

最后，调用 show 方法来在屏幕上显示这些图。总之，这段代码用 Pandas 和 matplotlib 创建了一个条形图和一个散点图，以使 df 数据框中的数据可视化。

如何用 Python 做数据可视化

这项任务涉及使用 Python 库，如 Plotly 和 bokeh，从 Excel 数据中创建交互式数据可视化。

这些可视化允许用户以新的方式探索数据，例如通过放大特定的数据点或基于特定标准过滤数据。

import pandas as pd
import plotly.express as px
# 创建热力图
fig = px.imshow(df.corr())
fig.show()
# 创建折线图
fig = px.line(df, x='date', y='sales', color='category')
fig.show()
1
2
3
4
5
6
7
8

该代码使用 Pandas 和 plotly.express 库来创建两种类型的可视化。首先，使用 plotly.express 的 imshow 方法创建一个热图，将 df 数据框中各列之间的关联性可视化。

其次，使用 plotly.express 的 line 方法创建一个折线图，显示 date 和 sales 列之间的关系，同时根据数据框的 category 列来区分类别。两个图都是用 show 方法显示的。

如何用 Python 自动生成报告

这项任务涉及到使用 Python 脚本来自动化从 Excel 数据生成报告的过程。

你可以设置这些脚本在一个定期的时间表上运行，比如每天或每周。它们也可以在有新数据时自动更新。

import pandas as pd
# 创建日报
df_daily = df[df['date'] == '2022-01-01']
df_daily.to_excel('daily_report.xlsx', index=False)
# 创建周报
df_weekly = df.groupby('category').sum()
df_weekly.to_excel('weekly_report.xlsx', index=False)
1
2
3
4
5
6
7

该代码通过创建一个新的数据框 df_daily 来创建一个日报，其中只包括 date 列等于 2022-01-01 的记录。这是通过使用 Pandas 的布尔索引功能实现的。

之后，使用 to_excel 方法将过滤后的数据导出到一个名为 daily_report.xlsx 的 Excel 文件中，其中不包括索引列。

接下来，代码通过将 df 数据框按 category 列分组，并将所有其他列的值相加来创建一个周报。这是用 Pandas 的 groupby 和 sum 方法完成的。

结果被保存在一个名为 df_weekly 的新数据框中。最后，to_excel 方法被用来将汇总的数据导出到一个名为 weekly_report.xlsx 的 Excel 文件中，其中不包括索引列。

总之，该代码使用 Pandas 库创建了两个报告。第一个报告是日报，只包括特定日期的数据，第二个报告是周报，按类别汇总数据。两份报告都使用 to_excel 方法导出为 Excel 文件。

如何用 Python 中的宏和脚本实现重复性任务的自动化

这项任务涉及使用 Python 来自动化 Excel 中的重复性任务，如数据输入或格式化。你可以通过创建可以自动执行的宏或脚本，或者通过使用 Python 与 Excel 应用程序直接交互来实现这一目标。

import win32com.client as win32
# 打开 Excel 文件
excel = win32.gencache.EnsureDispatch('Excel.Application')
workbook = excel.Workbooks.Open(r'filename.xlsx')
# 运行 macro
excel.Application.Run('macro_name')
# 保存并关闭 Excel
 fileworkbook.Save()workbook.Close()excel.Quit()
1
2
3
4
5
6
7
8

该代码使用 win32com.client 模块，通过 Windows API 与 Microsoft Excel 进行交互。

首先，使用 EnsureDispatch() 方法打开一个 Excel 应用程序的实例，并使用 Workbooks.Open() 方法打开指定的 Excel 文件。

接下来，使用 Application.Run() 方法执行一个宏，并将宏的名称作为参数传递。

最后，用 Save() 方法保存对 Excel 文件的修改，用 Close() 方法关闭工作簿，用 Quit() 方法终止 Excel 应用程序。

如何用 Python 爬取数据

这项任务涉及使用 Python 库，如 request 和 Beautiful Soup，从网页或其他来源爬取数据并将其导入 Excel。

然后你可以使用 Python 库（如 Pandas）分析和处理这些数据。

import pandas as pd
import requests
from bs4 import BeautifulSoup
# 从网页爬取数据
url = 'https://www.website.com/data'
response = requests.get(url)
soup = BeautifulSoup(response.text, 'html.parser')
table = soup.find('table')
df = pd.read_html(str(table))[0]
# 导出数据到 Excel 文件
df.to_excel('scraped_data.xlsx', index=False)
1
2
3
4
5
6
7
8
9
10
11

这段代码使用 requests 库向 URL “https://www.example.com” 发送 HTTP GET 请求。然后它使用 BeautifulSoup 库将响应的 HTML 内容解析成一个名为 soup 的 BeautifulSoup 对象。

然后你可以使用 BeautifulSoup 方法，如 find_all()，从 HTML 中提取特定数据：

links = []for link in soup.find_all(‘a’): href = link.get(‘href’) links.append(href)

这段代码找到了 HTML 中的所有锚点标签，并提取了每个锚点标签的 href 属性值，将它们添加到一个名为 links 的列表中。

如何使用 Python 将 Excel 与其他应用程序集成起来

这项任务涉及到使用 Python 将 Excel 与其他应用程序集成，如数据库或网络服务。

你可以使用 Python 库（如 pyodbc）连接到数据库，或者使用 API 连接到网络服务来做到这一点。这可以实现不同应用程序之间的无缝数据传输和分析。

import pandas as pd
import pyodbc
# 连接到数据库
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=db_name;UID=user_id;PWD=password')
# 从数据库读取数据
query = 'SELECT * FROM table_name'
df = pd.read_sql(query, cnxn)
# 导出到 Excel 文件
df.to_excel('database_data.xlsx', index=False)
1
2
3
4
5
6
7
8
9

该代码使用 pyodbc.connect() 方法建立了与 SQL Server 数据库的连接，其中驱动程序、服务器名称、数据库名称、用户 ID 和密码被作为参数提供。

然后，使用 pd.read_sql() 方法定义并执行一个 SQL 查询，从数据库中的一个表中获取数据，其中 SQL 查询和连接对象被作为参数提供。检索到的数据会被存储在一个 pandas 数据框中。

最后，使用 to_excel() 方法将数据框中的数据导出到一个名为 database_data.xlsx 的 Excel 文件中，通过设置索引参数为 False，导出中不包含索引列。
我的职业生涯开始和大多数码农一样，刚开始接触都是最基础的软件测试、编程语法。那时候在B站CSDN到处找学习资源，在这个吃技术的IT行业来说，不断学习是至关重要的。但是我之前做的是最基础的业务工作，随着时间的消磨，让我产生了对自我价值和岗位意义的困惑。

我的程序员之路，一路走来都离不开每个阶段的计划，因为自己喜欢规划和总结，所以，我和朋友特意花了一段时间整理编写了下面的《python架构师学习路线》，也整理了不少【网盘资源】，需要的朋友可以公众号【Python大本营】获取网盘链接。

希望会给你带来帮助和方向。

总结

Python 是一种多功能的语言，你可以用它来实现许多 Excel 任务的自动化。你也可以使用各种库，如 Pandas、openpyxl、xlwings 和 pyautogui，来操作数据、提取信息、生成报告，并使重复性任务自动化。

自动化可以节省时间和精力，减少错误，并提高生产力。熟练掌握 Python 对于任何使用 Excel 的专业人士来说都是一项有价值的技能，无论你是数据分析师还是金融分析师。通过学习 Python，你可以将你的工作提升到新的高度。