【从零开始学excel办公自动化】第二十课:告别 Excel 加班!2小时的报表工作,我用 5 分钟做完了

10年青少年编程教育经验 | 资深Python专家

前大厂技术总监 | 现编程教育创业者

专注青少年编程教育技能提升

import pandas as pdimport numpy as npfrom datetime import datetime, timedelta# 生成模拟销售数据np.random.seed(42) # 固定随机种子，保证每次生成的数据一样regions = ['华东', '华北', '华南', '西南', '西北']products = ['手机', '电脑', '平板', '耳机', '智能手表']# 生成3个月的销售数据start_date = datetime(2024, 1, 1)dates = [start_date + timedelta(days=x) for x in range(90)]data = []for date in dates:    for region in regions:        for product in products:            # 随机生成销量和销售额            quantity = np.random.randint(1, 20)            price = np.random.randint(1000, 8000)            sales = quantity * price            data.append([date, region, product,                   quantity, sales])# 转换成DataFramedf = pd.DataFrame(data, columns=['订单日期',         '销售区域', '产品类别', '销售数量', '销售额'])# 保存为Excel文件df.to_excel('销售数据.xlsx', index=False)print("示例数据已生成：销售数据.xlsx")

import pandas as pdimport matplotlib.pyplot as plt# --------- 1. 解决中文乱码问题（必加！）--plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签plt.rcParams['axes.unicode_minus'] = False    # 用来正常显示负号# ----------- 2. 读取Excel数据 -------df = pd.read_excel('销售数据.xlsx')# 查看数据前5行，确认数据是否正确读取print("数据前5行：")print(df.head())# 查看数据的基本信息，有没有缺失值print("\n数据基本信息：")print(df.info())# ---------------------- 3. 数据清洗 ----------------------# 把订单日期转换成标准的日期格式df['订单日期'] = pd.to_datetime(df['订单日期'])# 提取月份，方便我们按月统计df['月份'] = df['订单日期'].dt.month# 处理缺失值（如果有的话），这里我们用平均值填充销售额的空值df['销售额'] = df['销售额'].fillna(df['销售额'].mean())# 过滤异常值，比如销售额为负的错误数据df = df[df['销售额'] > 0]print("\n清洗后数据形状：", df.shape)

# 1. 各区域销售额汇总region_sales = df.groupby('销售区域')    ['销售额'].sum().sort_values(ascending=False)print("各区域销售额：")print(region_sales)# 2. 月度销售趋势monthly_sales = df.groupby('月份')['销售额'].sum()print("\n月度销售额：")print(monthly_sales)# 3. 各产品销售额汇总product_sales = df.groupby('产品类别')      ['销售额'].sum().sort_values(ascending=False)print("\n各产品销售额：")print(product_sales)# 4. 区域-产品交叉统计（相当于Excel的数据透视表）pivot_sales = pd.pivot_table(    df,     values='销售额',     index='销售区域',     columns='产品类别',     aggfunc='sum')print("\n区域-产品销售透视表：")print(pivot_sales)

# 创建画布plt.figure(figsize=(10, 6))# 画柱状图region_sales.plot(kind='bar', color='#4F81BD')# 添加标题和标签plt.title('各区域销售额对比', fontsize=14)plt.xlabel('销售区域', fontsize=12)plt.ylabel('销售额（元）', fontsize=12)# 旋转x轴标签，避免重叠plt.xticks(rotation=0)# 添加网格线，方便看数值plt.grid(axis='y', alpha=0.3)# 保存图片plt.tight_layout()plt.savefig('区域销售额柱状图.png', dpi=300, bbox_inches='tight')plt.show()

plt.figure(figsize=(10, 6))monthly_sales.plot(kind='line',     marker='o', color='#C0504D', linewidth=2)plt.title('月度销售趋势', fontsize=14)plt.xlabel('月份', fontsize=12)plt.ylabel('销售额（元）', fontsize=12)plt.grid(alpha=0.3)plt.tight_layout()plt.savefig('月度销售趋势图.png',      dpi=300, bbox_inches='tight')plt.show()

# 先把我们的统计数据整理好# 这里我们用区域销售额的数据来做示例summary_data = region_sales.reset_index()with pd.ExcelWriter('销售报表.xlsx', engine='xlsxwriter')           as writer:    # 1. 把原始数据写入Excel    df.to_excel(writer, sheet_name='原始数据', index=False)    # 2. 把统计汇总数据写入Excel    summary_data.to_excel(writer,         sheet_name='统计报表', index=False)    # 获取工作簿和工作表对象    workbook = writer.book    worksheet = writer.sheets['统计报表']    # 3. 创建柱状图    chart = workbook.add_chart({'type': 'column'})    # 配置图表的数据范围    # 数据是从第2行开始，第1列是区域，第2列是销售额    max_row = len(summary_data) + 1    chart.add_series({        'name':       '销售额',        'categories': ['统计报表', 1, 0, max_row, 0],         # X轴：区域名称        'values':     ['统计报表', 1, 1, max_row, 1],         # Y轴：销售额数据        'fill':       {'color': '#4F81BD'},    })    # 设置图表标题和轴标签    chart.set_title({'name': '各区域销售额统计'})    chart.set_x_axis({'name': '销售区域'})    chart.set_y_axis({'name': '销售额（元）'})    # 把图表插入到Excel的指定位置，这里是D2单元格    worksheet.insert_chart('D2', chart)    # 我们还可以把之前的折线图也加进去    # 先写入月度数据    monthly_data = monthly_sales.reset_index()    monthly_data.to_excel(writer,       sheet_name='统计报表', startrow=10, index=False)    # 创建折线图    line_chart = workbook.add_chart({'type': 'line'})    line_max_row = len(monthly_data) + 11    line_chart.add_series({        'name':       '月度销售额',        'categories': ['统计报表', 11, 0, line_max_row, 0],        'values':     ['统计报表', 11, 1, line_max_row, 1],        'line':       {'color': '#C0504D', 'width': 2},        'marker':     {'type': 'circle'},    })    line_chart.set_title({'name': '月度销售趋势'})    line_chart.set_x_axis({'name': '月份'})    line_chart.set_y_axis({'name': '销售额（元）'})    # 插入折线图到D12的位置    worksheet.insert_chart('D12', line_chart)print("完整报表已生成：销售报表.xlsx")

from openpyxl import load_workbookfrom openpyxl.drawing.image import Image# 先把数据写入Excelwith pd.ExcelWriter('带图片的报表.xlsx',                   engine='openpyxl') as writer:    df.to_excel(writer, sheet_name='数据', index=False)# 然后加载这个Excel，插入图片wb = load_workbook('带图片的报表.xlsx')ws = wb['数据']# 插入柱状图，定位到F2单元格img1 = Image('区域销售额柱状图.png')# 可以调整图片大小img1.width = 480img1.height = 300ws.add_image(img1, 'F2')# 插入折线图，定位到F20单元格img2 = Image('月度销售趋势图.png')img2.width = 480img2.height = 300ws.add_image(img2, 'F20')# 保存文件wb.save('带图片的报表.xlsx')print("带图片的报表已生成：带图片的报表.xlsx")

plt.rcParams['font.sans-serif'] = ['SimHei']plt.rcParams['axes.unicode_minus'] = False

chunk_size = 10000chunks = []for chunk in pd.read_excel('大文件.xlsx',             chunksize=chunk_size):    # 处理每个小批次    chunks.append(chunk.groupby('销售区域')['销售额'].sum())# 合并结果final_result = pd.concat(chunks).groupby(level=0).sum()