我把 Excel 扔了：用 Python 自动化报表，下班时间从 8 点变 6 点

给所有还在用 “筛选 – 复制 – 粘贴” 加班到深夜的职场人

开篇：那个让我决定 “反了” 的月底

上个月 30 号，晚上 8 点半，财务部的小王第 3 次催我：“杨哥，这个月的销售报表什么时候能好？老板明天一早就要。”

我盯着屏幕上那个 50MB 的 Excel 文件 —— 它已经卡了 15 分钟。第 37 张工作表里，有 8 万行数据等着我：合并单元格、手动计算公式、颜色标记的异常值……

手指机械地按着 Ctrl+C、Ctrl+V，眼睛盯着屏幕上跳动的数字，脑子里只有一个念头：“我这 985 毕业的，就是来当人肉计算器的？”

晚上 10 点，我终于把报表发出去。关电脑时，手都在抖。走到公司楼下，看到便利店老板正准备关门，他笑着问我：“又加班啊？你们程序员不是挺厉害的吗，怎么天天加班？”

那一刻，我破防了。

第二天，我决定 “反了”。用了一周时间，我把所有 Excel 报表工作自动化了。现在，每月底我只需要：

今天，我把这套方法完整教给你。不需要编程基础，跟着做就行。

一、先算笔账：你每个月在 Excel 上浪费多少时间？

1. 我的 Excel 时间统计（自动化前）

每月固定工作：

• 销售日报：每天 1 小时 × 22 天 = 22 小时
• 周汇总表：每周 3 小时 × 4 周 = 12 小时
• 月终报表：每月 8 小时 = 8 小时
• 临时分析：每月 6 小时 = 6 小时

总计：48 小时 / 月 = 6 个工作日

触目惊心的事实：我每个月有四分之一的工作时间在手工处理 Excel！

2. 自动化后的时间对比

简单说：以前月底加班 3 天，现在 3 分钟搞定。

二、准备工作：5 分钟搭建 Python 环境

1. 安装 Python（就像装 QQ 一样简单）

Windows 用户：

Mac 用户：

验证安装：打开命令行（Windows：按 Win+R，输入cmd；Mac：打开 “终端”），输入：

python –version

看到Python 3.11.x就成功了。

2. 安装必备库（复制粘贴就行）

在命令行输入：

pip install pandas openpyxl xlrd xlwt matplotlib

等 2-3 分钟，安装完成。

三、第一个自动化脚本：10 分钟搞定日报

场景：每天要汇总 5 个分店的销售数据

原始工作流程：

耗时：1 小时 / 天

现在，用 Python 实现

创建文件daily_report.py，把下面代码完整复制进去：

"""
daily_report.py – 自动化销售日报生成
0基础也能看懂，跟着注释一步步来
"""

# 1. 导入需要的库（刚才安装的）
import pandas as pd # 数据处理库，Excel的灵魂替代品
import os # 操作系统库，用来找文件
from datetime import datetime, timedelta

# 2. 设置文件路径（改成你自己的Excel文件位置）
# 假设5个分店的Excel在"D:/销售数据/"文件夹下
data_folder = "D:/销售数据/"
output_file = "D:/报表输出/销售日报.xlsx"

# 3. 获取昨天的日期（自动计算，不用手动改）
yesterday = datetime.now() – timedelta(days=1)
yesterday_str = yesterday.strftime("%Y-%m-%d")

print(f"开始生成{yesterday_str}的销售日报…")

# 4. 读取所有分店数据
all_data = [] # 创建一个空列表，用来存放每家店的数据

# 遍历5个分店文件
for shop_id in range(1, 6):
# 构建文件名，比如"shop_1_2024-05-25.xlsx"
file_name = f"shop_{shop_id}_{yesterday_str}.xlsx"
file_path = os.path.join(data_folder, file_name)

# 检查文件是否存在
if os.path.exists(file_path):
print(f"正在读取：{file_name}")

# 用pandas读取Excel文件
# sheet_name=0：读取第一个工作表
# usecols="B:D"：只读取B、C、D三列（假设B是产品名，C是销量，D是销售额）
df = pd.read_excel(file_path, sheet_name=0, usecols="B:D")

# 添加一列“分店ID”
df["分店"] = f"分店{shop_id}"

# 添加到总数据列表
all_data.append(df)
else:
print(f"警告：文件不存在 – {file_name}")

# 5. 合并所有数据
if all_data: # 如果有数据
# 用pandas合并所有DataFrame（类似Excel的“追加查询”）
combined_data = pd.concat(all_data, ignore_index=True)

print(f"数据合并完成，共{len(combined_data)}行记录")

# 6. 数据清洗（自动处理常见问题）
# 删除销售额为空的记录
combined_data = combined_data.dropna(subset=["销售额"])

# 删除重复记录（基于产品名和分店）
combined_data = combined_data.drop_duplicates(subset=["产品名", "分店"])

print(f"数据清洗后，剩余{len(combined_data)}行记录")

# 7. 计算汇总指标
# 按分店汇总
shop_summary = combined_data.groupby("分店").agg({
"销量": "sum",
"销售额": "sum"
}).reset_index()

# 计算总计
total_sales = shop_summary["销售额"].sum()
total_quantity = shop_summary["销量"].sum()

# 8. 与前一天对比（自动读取前一天数据）
# 这里简化处理，实际中你可以读取历史数据文件
print(f"昨日总销售额：¥{total_sales:,.2f}")
print(f"昨日总销量：{total_quantity:,}件")

# 9. 保存到Excel
# 创建一个Excel写入对象
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
# 保存详细数据到“明细”工作表
combined_data.to_excel(writer, sheet_name='明细', index=False)

# 保存汇总数据到“汇总”工作表
shop_summary.to_excel(writer, sheet_name='汇总', index=False)

# 再创建一个“概览”工作表，放关键指标
overview_data = {
"指标": ["总销售额", "总销量", "平均单价", "数据日期"],
"数值": [
f"¥{total_sales:,.2f}",
f"{total_quantity:,}件",
f"¥{total_sales/total_quantity:,.2f}" if total_quantity > 0 else "N/A",
yesterday_str
]
}
overview_df = pd.DataFrame(overview_data)
overview_df.to_excel(writer, sheet_name='概览', index=False)

print(f"报表已生成：{output_file}")

# 10. 自动发送邮件（可选）
# 如果你需要自动发送，取消下面代码的注释，并填写你的邮箱信息
'''
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders

# 邮件配置
sender_email = "your_email@163.com"
sender_password = "你的授权码" # 不是登录密码，是SMTP授权码
receiver_email = "boss@company.com"

# 创建邮件
msg = MIMEMultipart()
msg['From'] = sender_email
msg['To'] = receiver_email
msg['Subject'] = f"{yesterday_str} 销售日报"

# 邮件正文
body = f"""
您好！

附件是{yesterday_str}的销售日报，请查收。

关键指标：
– 总销售额：¥{total_sales:,.2f}
– 总销量：{total_quantity:,}件
– 平均单价：¥{total_sales/total_quantity:,.2f}

详细数据请查看附件。
"""
msg.attach(MIMEText(body, 'plain'))

# 添加附件
attachment = open(output_file, "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', f"attachment; filename=销售日报_{yesterday_str}.xlsx")
msg.attach(part)

# 发送邮件
try:
server = smtplib.SMTP('smtp.163.com', 587)
server.starttls()
server.login(sender_email, sender_password)
server.send_message(msg)
server.quit()
print("邮件发送成功！")
except Exception as e:
print(f"邮件发送失败：{e}")
'''

else:
print("错误：没有找到任何数据文件")

print("日报生成完成！")

运行脚本

你会看到：

开始生成2024-05-25的销售日报…
正在读取：shop_1_2024-05-25.xlsx
正在读取：shop_2_2024-05-25.xlsx
…
数据合并完成，共8560行记录
数据清洗后，共8523行记录
昨日总销售额：¥1,234,567.89
昨日总销量：8,523件
报表已生成：D:/报表输出/销售日报.xlsx
日报生成完成！

恭喜！ 你刚刚用 1 分钟，完成了以前 1 小时的工作！

四、进阶：自动生成可视化报表

场景：老板要每周销售趋势图

传统方式：

Python 方式：全自动生成带图表的 Excel

创建weekly_report.py：

"""
weekly_report.py – 自动化周报，带图表
"""

import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
import os

# 设置中文字体（解决中文乱码）
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

# 1. 模拟一周数据（实际中从文件读取）
dates = []
sales = []

# 生成最近7天的数据
for i in range(7, 0, -1):
date = datetime.now() – timedelta(days=i)
dates.append(date.strftime("%m-%d"))
# 模拟销售额（随机数）
sales.append(round(100000 + (i * 20000) + (i % 3) * 50000, 2))

# 2. 创建DataFrame
df = pd.DataFrame({
"日期": dates,
"销售额": sales
})

print("周销售数据：")
print(df)

# 3. 创建可视化图表
plt.figure(figsize=(10, 6))

# 折线图
plt.subplot(2, 2, 1)
plt.plot(dates, sales, marker='o', linewidth=2, color='blue')
plt.title('周销售趋势')
plt.xlabel('日期')
plt.ylabel('销售额（元）')
plt.grid(True, linestyle='–', alpha=0.7)

# 柱状图
plt.subplot(2, 2, 2)
bars = plt.bar(dates, sales, color=['red', 'orange', 'yellow', 'green', 'blue', 'purple', 'pink'])
plt.title('每日销售额')
plt.xlabel('日期')
plt.ylabel('销售额（元）')

# 在柱子上显示数值
for bar in bars:
height = bar.get_height()
plt.text(bar.get_x() + bar.get_width()/2., height,
f'¥{height:,.0f}', ha='center', va='bottom')

# 饼图（按天占比）
plt.subplot(2, 2, 3)
plt.pie(sales, labels=dates, autopct='%1.1f%%', startangle=90)
plt.title('每日销售占比')

# 4. 保存图表
chart_file = "D:/报表输出/销售周报_图表.png"
plt.tight_layout()
plt.savefig(chart_file, dpi=300, bbox_inches='tight')
print(f"图表已保存：{chart_file}")

# 5. 创建带图表的Excel
output_file = "D:/报表输出/销售周报.xlsx"

with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
# 写入数据
df.to_excel(writer, sheet_name='数据', index=False)

# 计算汇总指标
total_sales = df["销售额"].sum()
avg_daily = df["销售额"].mean()
max_day = df.loc[df["销售额"].idxmax()]
min_day = df.loc[df["销售额"].idxmin()]

# 创建汇总表
summary_data = {
"指标": ["总销售额", "日均销售额", "最高销售额日", "最低销售额日", "波动率"],
"数值": [
f"¥{total_sales:,.2f}",
f"¥{avg_daily:,.2f}",
f"{max_day['日期']}: ¥{max_day['销售额']:,.2f}",
f"{min_day['日期']}: ¥{min_day['销售额']:,.2f}",
f"{(max_day['销售额'] – min_day['销售额']) / avg_daily:.1%}"
]
}

summary_df = pd.DataFrame(summary_data)
summary_df.to_excel(writer, sheet_name='汇总', index=False)

# 获取工作簿对象，准备插入图片
workbook = writer.book
worksheet = workbook.create_sheet("可视化")

# 插入图片到Excel（需要openpyxl）
from openpyxl.drawing.image import Image
img = Image(chart_file)

# 设置图片位置
img.width = 500
img.height = 300
worksheet.add_image(img, 'A1')

# 添加数据表格
from openpyxl.utils.dataframe import dataframe_to_rows
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), 1):
for c_idx, value in enumerate(row, 7):
worksheet.cell(row=r_idx, column=c_idx, value=value)

print(f"周报已生成：{output_file}")
print("包含：数据表、汇总表、可视化图表")

运行效果：

五、真实案例：我是如何解放财务部的

背景

公司财务部每月底要处理：

• 500 + 张原始凭证
• 87 张 Excel 表格
• 200 + 个公式链接
• 3 天加班时间

解决方案

我写了一个finance_automation.py脚本：

"""
财务自动化脚本 – 处理凭证、对账、生成报表
"""

import pandas as pd
import os
from datetime import datetime

class FinanceAutomation:
def __init__(self):
self.today = datetime.now().strftime("%Y%m%d")

def process_invoices(self, folder_path):
"""处理发票文件夹中的所有Excel发票"""
print(f"开始处理发票文件夹：{folder_path}")

all_invoices = []

# 遍历文件夹中的所有Excel文件
for file_name in os.listdir(folder_path):
if file_name.endswith(('.xlsx', '.xls')):
file_path = os.path.join(folder_path, file_name)

# 读取发票数据
df = pd.read_excel(file_path)

# 标准化数据格式
df = self._standardize_invoice(df)

all_invoices.append(df)

print(f"已处理：{file_name}")

# 合并所有发票
if all_invoices:
combined = pd.concat(all_invoices, ignore_index=True)

# 保存到总表
output_path = f"D:/财务数据/发票汇总_{self.today}.xlsx"
combined.to_excel(output_path, index=False)

print(f"发票处理完成！共{len(combined)}条记录")
print(f"保存到：{output_path}")

return combined
else:
print("未找到发票文件")
return None

def _standardize_invoice(self, df):
"""标准化发票数据"""
# 重命名列（不同人导出的Excel列名不同）
column_mapping = {
'发票号码': 'invoice_no',
'开票日期': 'invoice_date',
'客户名称': 'customer',
'金额': 'amount',
'税额': 'tax',
'价税合计': 'total',
'Invoice No.': 'invoice_no',
'Date': 'invoice_date',
'Customer': 'customer',
'Amount': 'amount',
'Tax': 'tax',
'Total': 'total'
}

# 重命名列
df = df.rename(columns={k: v for k, v in column_mapping.items() if k in df.columns})

# 确保必要列存在
required_columns = ['invoice_no', 'invoice_date', 'customer', 'amount']
for col in required_columns:
if col not in df.columns:
df[col] = None

return df

def generate_financial_report(self, invoice_data):
"""生成财务报表"""
print("开始生成财务报表…")

# 按客户汇总
customer_summary = invoice_data.groupby('customer').agg({
'amount': 'sum',
'tax': 'sum',
'total': 'sum'
}).reset_index()

# 按月份汇总
invoice_data['month'] = pd.to_datetime(invoice_data['invoice_date']).dt.strftime('%Y-%m')
monthly_summary = invoice_data.groupby('month').agg({
'amount': 'sum',
'total': 'sum'
}).reset_index()

# 创建详细报表
report_file = f"D:/财务报告/财务报表_{self.today}.xlsx"

with pd.ExcelWriter(report_file, engine='openpyxl') as writer:
# 原始数据
invoice_data.to_excel(writer, sheet_name='原始数据', index=False)

# 客户汇总
customer_summary.to_excel(writer, sheet_name='客户汇总', index=False)

# 月度汇总
monthly_summary.to_excel(writer, sheet_name='月度趋势', index=False)

# 关键指标
metrics = {
'指标': ['发票总数', '总金额', '平均每单金额', '最大客户', '最小客户'],
'数值': [
len(invoice_data),
f"¥{invoice_data['total'].sum():,.2f}",
f"¥{invoice_data['total'].mean():,.2f}",
customer_summary.loc[customer_summary['total'].idxmax(), 'customer'],
customer_summary.loc[customer_summary['total'].idxmin(), 'customer']
]
}

metrics_df = pd.DataFrame(metrics)
metrics_df.to_excel(writer, sheet_name='关键指标', index=False)

print(f"财务报表已生成：{report_file}")
return report_file

# 使用示例
if __name__ == "__main__":
# 创建自动化对象
fa = FinanceAutomation()

# 处理发票文件夹
invoice_data = fa.process_invoices("D:/原始发票/")

if invoice_data is not None:
# 生成财务报表
fa.generate_financial_report(invoice_data)

print("财务自动化处理完成！")

实施效果

实施前：

• 每月底加班 3 天
• 人工核对错误率：3%
• 报表延迟率：40%

实施后：

• 每月底加班：0 天
• 错误率：0.1%
• 报表延迟率：0%

财务总监的原话：“小杨，你这一个脚本，顶我们三个实习生。”

六、常见问题与解决方案

问题 1：Python 读取 Excel 报错 “No module named 'openpyxl'”

解决：

pip install openpyxl

问题 2：中文显示乱码

解决：

import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei'] # 设置中文字体
plt.rcParams['axes.unicode_minus'] = False # 解决负号显示问题

问题 3：Excel 文件太大，读取慢

解决：

# 只读取需要的列
df = pd.read_excel('large_file.xlsx', usecols=['A', 'B', 'C'])

# 分块读取
chunk_size = 10000
for chunk in pd.read_excel('large_file.xlsx', chunksize=chunk_size):
process_chunk(chunk)

问题 4：需要处理多个 Sheet

解决：

# 读取所有Sheet
excel_file = pd.ExcelFile('file.xlsx')
sheet_names = excel_file.sheet_names

# 逐个处理
for sheet in sheet_names:
df = pd.read_excel('file.xlsx', sheet_name=sheet)
process_sheet(df, sheet)

七、我的自动化工具箱（常用代码片段）

1. 批量重命名 Excel 文件

import os

def rename_excel_files(folder_path, prefix):
"""给文件夹中所有Excel文件加前缀"""
for filename in os.listdir(folder_path):
if filename.endswith('.xlsx'):
new_name = f"{prefix}_{filename}"
os.rename(
os.path.join(folder_path, filename),
os.path.join(folder_path, new_name)
)
print(f"重命名：{filename} -> {new_name}")

2. 自动对比两个 Excel 差异

def compare_excel_files(file1, file2, key_column):
"""对比两个Excel的差异"""
df1 = pd.read_excel(file1)
df2 = pd.read_excel(file2)

# 找出新增的行
new_rows = df2[~df2[key_column].isin(df1[key_column])]

# 找出删除的行
deleted_rows = df1[~df1[key_column].isin(df2[key_column])]

# 找出修改的行
merged = pd.merge(df1, df2, on=key_column, suffixes=('_old', '_new'))
changed_rows = merged[merged['value_old'] != merged['value_new']]

return new_rows, deleted_rows, changed_rows

3. 自动发送带附件的邮件

def send_email_with_attachment(to_email, subject, body, attachment_path):
"""发送带附件的邮件"""
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders

# 配置你的邮箱
sender_email = "your_email@163.com"
sender_password = "your_smtp_password"

# 创建邮件
msg = MIMEMultipart()
msg['From'] = sender_email
msg['To'] = to_email
msg['Subject'] = subject

# 添加正文
msg.attach(MIMEText(body, 'plain'))

# 添加附件
with open(attachment_path, 'rb') as attachment:
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition',
f'attachment; filename={os.path.basename(attachment_path)}')
msg.attach(part)

# 发送邮件
server = smtplib.SMTP('smtp.163.com', 587)
server.starttls()
server.login(sender_email, sender_password)
server.send_message(msg)
server.quit()

print(f"邮件发送成功：{to_email}")

八、最后：给 Excel 重度用户的建议

1. 不要怕，Python 比 Excel 公式简单

很多人觉得 Excel 公式简单，Python 难。其实：

• Excel 公式：=VLOOKUP(A2, Sheet2!$A$2:$B$100, 2, FALSE)
• Python：df.merge(df2, on='id')

哪个更直观？

2. 从 “小自动化” 开始

不要一开始就想自动化所有工作。从每天重复、最耗时的任务开始。

比如：

• 日报 → 周报 → 月报
• 数据清洗 → 数据汇总 → 数据可视化

3. 建立你的代码库

把常用的代码片段保存起来，下次直接复制修改。我的excel_tools.py文件里，有 30 多个常用函数。

4. 分享给同事

当你自动化了一个流程，分享给同事。你会发现：

行动起来！

现在，打开你的电脑：

如果你卡住了，记住：

• 百度 / Google 搜索：“pandas 如何 [你的问题]”
• 90% 的问题，别人都遇到过
• 复制报错信息去搜索，能找到答案