Python 操作 Excel 并进行数据可视化

Python 结合 Excel 进行数据可视化，能将静态数据转化为直观图表，是数据分析中非常关键的技能。下面我将通过五个核心方面，带你掌握如何用 Python 操作 Excel 并进行数据可视化。

首先，用一个表格快速了解常用的 Python 库及其主要用途：

库名称主要功能典型应用场景pandas数据读取、清洗、处理和聚合从 Excel 读取数据，进行数据预处理和转换matplotlib基础绘图，支持多种静态图表类型绘制折线图、柱状图、散点图等基础图表seaborn基于 matplotlib，提供更高级、更美观的统计图表和默认样式绘制分布图、分类图、热力图等复杂统计图表openpyxl直接读写和操作 Excel 文件，包括单元格格式、公式、图表等将 pandas 处理后的数据或 matplotlib 生成的图表写入 Excelxlsxwriter创建和操作 Excel 文件，支持更复杂的图表和格式选项生成带有复杂图表和格式的 Excel 报告plotly创建交互式图表，支持缩放、平移、数据点查看等交互操作制作交互式数据分析图表和应用

接下来，我们深入看看这些库的具体应用。

1. 数据读取与预处理

数据可视化的第一步是获取并清理数据。pandas 是这方面的利器。

读取 Excel 数据：使用 pd.read_excel() 函数可以直接读取 Excel 文件，并将其转换为 DataFrame，这是 pandas 中用于数据操作的核心数据结构。

import pandas as pd # 读取 Excel 文件 df = pd.read_excel('data.xlsx', sheet_name='Sheet1') # 可以指定工作表 # 查看数据前几行和基本信息 print(df.head()) print(df.info())

处理缺失值：真实数据常有缺失，pandas 提供了方便的方法处理。

# 删除包含缺失值的行 df_cleaned = df.dropna() # 或用特定值填充缺失值，这里用该列均值填充 df_filled = df.fillna(df.mean())

数据转换：为了后续分析，常需转换数据类型或创建新特征。

# 将字符串日期转换为日期时间类型 df['date_column'] = pd.to_datetime(df['date_column']) # 从日期中提取年份、月份等特征 df['year'] = df['date_column'].dt.year df['month'] = df['date_column'].dt.month

2. 基础与高级静态可视化

matplotlib 是 Python 最基础的绘图库，seaborn 在其基础上提供了更美观的高级接口。

使用 matplotlib 绘制基础图表：

import matplotlib.pyplot as plt # 折线图：展示数据随时间的变化趋势 plt.figure(figsize=(10, 6)) # 设置图片大小 plt.plot(df['date'], df['value'], marker='o', linestyle='-', color='b') # 折线图，带数据点 plt.title('Sales Trend Over Time') # 标题 plt.xlabel('Date') # X轴标签 plt.ylabel('Sales') # Y轴标签 plt.xticks(rotation=45) # 旋转X轴刻度标签以避免重叠 plt.grid(True, alpha=0.3) # 显示网格线，设置透明度 plt.tight_layout() # 自动调整子图参数以填充整个图像区域 plt.savefig('sales_trend.png', dpi=300) # 保存图片，设置分辨率 plt.show() # 显示图片

使用 seaborn 绘制高级统计图表：Seaborn 的 API 通常更简洁，默认样式更美观。

import seaborn as sns # 设置 seaborn 样式 sns.set_style("whitegrid") # 柱状图：比较不同类别的数据 plt.figure(figsize=(10, 6)) sns.barplot(x='category_column', y='value_column', data=df, palette='viridis') # palette设置颜色映射 plt.title('Average Value by Category') plt.xlabel('Category') plt.ylabel('Average Value') plt.xticks(rotation=45) plt.tight_layout() plt.savefig('category_avg_value.png', dpi=300) plt.show() # 箱线图：查看数据分布和离群点 plt.figure(figsize=(10, 6)) sns.boxplot(x='category_column', y='value_column', data=df) plt.title('Distribution of Value by Category') plt.xlabel('Category') plt.ylabel('Value') plt.xticks(rotation=45) plt.tight_layout() plt.savefig('
category_value_boxplot.png', dpi=300) plt.show()

3. 创建交互式图表

plotly 库可以创建丰富的交互式图表，用户能够缩放、平移、悬停查看数据点具体信息等。

安装与基础交互式图表：

# 安装：pip install plotly import plotly.express as px # 创建一个交互式折线图 fig = px.line(df, x='date_column', y='value_column', title='Interactive Sales Trend Over Time') fig.show() # 在浏览器中显示交互式图表 # 保存为 HTML 文件，交互性得以保留 fig.write_html("
interactive_sales_trend.html")

更复杂的交互式图表：Plotly Express 接口简洁，支持多种复杂图表。

# 交互式散点图：查看两个变量之间的关系，可用颜色、大小表示第三个变量 fig = px.scatter(df, x='x_column', y='y_column', color='category_column', size='size_column', title='Interactive Scatter Plot', hover_data=['additional_info']) # hover_data 设置悬停显示更多数据 fig.show()

4. 将图表集成到 Excel

有时需要将 Python 生成的图表或处理后的数据放回 Excel 中汇报或分享。

使用 openpyxl 将图片插入 Excel：

from openpyxl import load_workbook from openpyxl.drawing.image import Image # 加载已存在的 Excel 工作簿 workbook = load_workbook('your_excel_file.xlsx') sheet = workbook.active # 获取活动工作表 # 加载 matplotlib 或 seaborn 保存的图片 img = Image('sales_trend.png') # 确保图片路径正确 # 将图片添加到工作表的指定位置（例如 'A10' 单元格附近） sheet.add_image(img, 'A10') # 保存工作簿 workbook.save('
excel_file_with_chart.xlsx')

使用 xlsxwriter 创建带有图表的全新 Excel 文件：XlsxWriter 擅长创建新的 Excel 文件并添加格式、图表等。

import xlsxwriter # 创建一个新的 Excel 工作簿 workbook = xlsxwriter.Workbook('
new_report_with_charts.xlsx') worksheet = workbook.add_worksheet('Sales Data') # 添加工作表 # 将数据写入工作表（示例） # 先写入表头 headers = ['Date', 'Sales'] for col_num, header in enumerate(headers): worksheet.write(0, col_num, header) # 然后写入数据（这里需要将你的数据按行写入） # ... 数据写入逻辑 ... # 创建一个图表对象 chart = workbook.add_chart({'type': 'line'}) # 折线图 # 配置图表数据系列 chart.add_series({ 'categories': '=Sales Data!$A$2:$A$10', # X 轴数据范围 'values': '=Sales Data!$B$2:$B$10', # Y 轴数据范围 'name': 'Sales Trend' # 图例名称 }) # 设置图表标题和坐标轴标签 chart.set_title({'name': 'Monthly Sales Data'}) chart.set_x_axis({'name': 'Date'}) chart.set_y_axis({'name': 'Sales'}) # 将图表插入到工作表的指定位置 worksheet.insert_chart('D2', chart) # 图表左上角位于 D2 单元格 # 关闭工作簿并保存文件 workbook.close()

5. 自动化报告与高级应用

Python 的强大之处在于可以自动化整个数据处理和可视化流程。

自动化脚本示例：将数据读取、处理、可视化、保存和集成到 Excel 的步骤整合到一个脚本中。

import pandas as pd import matplotlib.pyplot as plt from openpyxl import load_workbook from openpyxl.drawing.image import Image import os def generate_automated_report(excel_file_path, output_excel_path): """自动化生成报告并插入图表""" # 1. 读取数据 df = pd.read_excel(excel_file_path) # 2. 数据处理 (示例: 按月份聚合销售数据) df['Date'] = pd.to_datetime(df['Date']) df['Month'] = df['Date'].dt.to_period('M') monthly_sales = df.groupby('Month')['Sales'].sum().reset_index() monthly_sales['Month'] = monthly_sales['Month'].astype(str) # 将Period对象转换为字符串以便绘图 # 3. 创建图表 plt.figure(figsize=(10, 6)) plt.plot(monthly_sales['Month'], monthly_sales['Sales'], marker='o', linestyle='-') plt.title('Monthly Sales Trend') plt.xlabel('Month') plt.ylabel('Total Sales') plt.xticks(rotation=45) plt.tight_layout() chart_image_path = '
monthly_sales_trend_auto.png' plt.savefig(chart_image_path, dpi=300) plt.close() # 关闭图形以释放内存 # 4. 将图表插入到原始的 Excel 文件中 workbook = load_workbook(excel_file_path) sheet = workbook.create_sheet('Monthly Report') # 创建一个新的工作表来放置图表 # 将聚合后的数据写入新工作表 sheet['A1'] = 'Month' sheet['B1'] = 'Total Sales' for idx, row in monthly_sales.iterrows(): sheet.cell(row=idx+2, column=1, value=row['Month']) sheet.cell(row=idx+2, column=2, value=row['Sales']) # 插入图片 img = Image(chart_image_path) sheet.add_image(img, 'D2') # 5. 保存包含图表的新 Excel 文件 workbook.save(output_excel_path) print(f"自动化报告已生成: {output_excel_path}") # 6. (可选) 删除临时的图片文件 # os.remove(chart_image_path) # 使用函数 generate_automated_report('sales_data.xlsx', '
automated_sales_report.xlsx')

定时自动化：利用系统任务计划程序（如 Windows 任务计划程序或 Linux 的 cron）定期运行 Python 脚本，实现报表的自动更新。

Linux/macOS (cron): 使用 crontab -e 编辑定时任务，添加类似 0 9 * * 1 /usr/bin/python3 /path/to/your_script.py 的行，表示每周一上午 9 点运行脚本。

Windows: 使用"任务计划程序"创建基本任务，设置触发器和启动程序为 Python 解释器和脚本路径。

6. 可视化实践建议

图表选择原则：

趋势分析：折线图（时间序列）。

比较分类数据：柱状图、条形图。

构成关系：饼图（部分与整体，注意类别不宜过多）、堆积柱状图。

分布情况：直方图、箱线图、密度图。

变量关系：散点图、气泡图。

多维度对比：雷达图（需谨慎使用，容易误导）。

美化与清晰度：

标题和标签：总是为图表和坐标轴添加清晰易懂的标题和标签。

颜色：选择对比度适中、易于区分的颜色，考虑色盲人群。可使用 matplotlib 的 colormap 或 seaborn 的调色。

图例：当有多条数据系列时，添加图例说明。

避免杂乱：不要在一张图表中塞入过多信息，保持简洁易懂。

希望这些内容能帮助你有效地用 Python 操作 Excel 并进行数据可视化。