从Excel记账到Python数据分析：家庭支出自动化统计实战指南

1. 从Excel记账到Python分析：我为什么迈出这一步

1.1 记账四年，Excel总表越来越难伺候

我家记账记了快四年，一直用的Excel。一开始确实够用——每月底花十几分钟把微信、支付宝的账单手工录入一张总表，再用SUMIF、SUMIFS这些函数按分类汇总一下，看看餐饮花了多少、交通超没超预算。这个阶段Excel是神器，公式熟了之后，自动求和也就几秒钟的事。

但到了第二年开始，我越来越觉得不对劲。消费渠道多了以后，账单碎片化严重，微信一笔、支付宝一笔、信用卡一笔，还有现金付款的遗漏。月底补录的时候，经常对着聊天记录和账单流水一条一条找"昨天那笔68块钱到底是吃饭还是买水果"，那种感觉真的很消耗耐心。更尴尬的是，每次想换一个统计角度——比如从"按月看"换成"按支付方式看"，或者想看看"每月餐饮支出的环比变化"，又得重新调整透视表字段，拖来拖去，表格结构一变，之前的布局全乱了。

1.2 Excel记账的三个死穴

总结下来，Excel记账最让我抓狂的是三件事。

第一，手工汇总耗时。记账本身不难，难的是"分析账目"。月底要把整月几十行数据重新核对、分类、汇总，一旦发现漏记一笔，所有汇总公式引用的区域就可能跟着错位。我和很多朋友聊过，绝大多数人放弃记账，不是懒得记，而是月底汇总太烦。

第二，透视表有门槛且不灵活。透视表功能确实强大，但前提是你得熟悉"行、列、值、筛选"四个区域的逻辑。偶尔拖出来一个汇总还行，想多维对比就得反复调整布局，而且透视表的样式很"素"，想导出去做月度复盘或者给家人看，还得再排版。

第三，图表模板僵化。Excel自带的图表类型不少，但默认样式相当一言难尽。想自定义配色、加标注、做组合图，每一步都要小心翼翼地调整。想生成一张能在手机上看得舒服的月度分析图，常常得花比记账更长的时间。

1.3 想明白"要解决什么问题"再动手

后来我意识到，自己真正想要的不是一套更复杂的表格技巧，而是一个能自动完成"读取账本→清洗数据→统计汇总→生成图表"的流水线。我需要保留Excel作为录入工具，因为家人也习惯用它记账，但分析部分完全可以交给Python。pandas负责清洗和聚合，openpyxl负责读写Excel，matplotlib负责画图，三者搭配起来，整个流程从一两个小时压缩到几十秒。

这个项目适合两类人：一类是正在用Excel记账、想进一步挖掘数据价值的人；另一类是刚学Python不久、想拿真实场景练手的朋友。它不需要你懂什么高深的算法，核心就是DataFrame操作加几个绘图函数，但跑通之后你会对Python处理数据的能力有一个非常直观的感受。

2. 账本Excel结构设计：好数据是统计分析成功的一半

2.1 表结构要简单到"一眼就能录入"

做数据分析的人都知道一句话：Garbage in, garbage out。Excel账本如果结构乱，后面Python写得再漂亮也是白搭。我最初的账本非常随意，日期有写"1月5日"的，也有写"2024/1/5"的；金额有带"¥"的，有带"元"的；分类更是五花八门，"买菜"和"生鲜"其实是一回事，"孩子培训班"和"书本文具"被我并到了"教育"里，完全是看心情。

后来我重做了账本结构，只保留六个字段，每一列都有明确规则：

这个结构并不复杂，家人录入时也不会觉得有负担。关键是要坚持第三列"项目"写得具体一点，比如"超市买菜"而不是"买东西"，事后回溯时才不会对着"一笔巨款"发愣。

2.2 日期和金额的规范比想象中更重要

日期列是后期统计分析的核心维度，也是最容易乱的一列。我强烈建议在Excel里就规规矩矩写成"2024-03-15"这种中间用短横线连接的形式，不要用"2024.3.15"也不要用"3月15日"。因为pandas解析日期时，ISO格式最不容易产生歧义。

金额列也有讲究。首先必须让这一列是"真数字"，不能用文本格式存储。Excel里经常出现一个坑：单元格左上角有个绿色小三角，说明它是文本型数字，SUMIF会把它漏掉。另外不要在金额列里加单位，不要写"36.5元"，更不要用右对齐还是左对齐来判断格式。选中整列，设置单元格格式为"数值"，小数位数设为2。

还有两个很重要但容易被忽略的规则：一是不要合并单元格，二是不要在数据中间插入"小计"或空行。合并单元格会让pandas读进来时出现大量NaN，小计行则会被当成真实支出参与统计。这两个问题我都在实际项目中踩过，后面复盘部分会详细讲。

2.3 提前建立"分类字典"和预算表

除了总表，我还会在同一个Excel文件里放一个"预算"工作表，结构很简单：分类、月度预算金额。比如餐饮2500、交通800、购物1000、居住3500、医疗500、教育500、娱乐500、其他300。

分类字段需要提前定好列表，录入时严格从列表里选。但实际操作中家人往往记不住这么多规矩，今天写"买菜"，明天写"生鲜"，后天写"超市食品"。这时候不要强迫每个人都规范，而是建立一张"分类映射表"，让Python在清洗阶段自动把这些别名归一到标准分类里。这个过程在后续代码中一行replace就能搞定，但前提是你意识到这个问题的存在。

3. 环境准备：Python与pandas/openpyxl/matplotlib三件套

3.1 只要会pip，环境十分钟能搞定

很多朋友听到"编程"两个字就有畏难情绪，其实这个项目的环境准备工作非常简单。你只需要一个能运行Python解释器的环境，外加三个第三方库。

我自己用的是Windows加VSCode的组合。Python安装的时候，记得在安装向导第一个界面勾选"Add Python to PATH"这个选项，这是新手最容易忽略的一步。不勾选的话，后面在命令行敲python会提示找不到命令。

安装完Python之后，我建议为这个项目单独建一个虚拟环境，避免以后其他项目的依赖互相打架。操作也简单：

python -m venv family_budget_env

Windows系统下激活环境：

family_budget_env\Scripts\activate

macOS或Linux下激活环境：

source family_budget_env/bin/activate

然后安装三个库：

pip install pandas openpyxl matplotlib

这里插一句题外话：pandas底层的Excel读写其实需要一个引擎。很久以前pandas自带xlrd读取.xls，但新版pandas对.xlsx文件的读写，基本依赖openpyxl。很多人安装了pandas之后直接读Excel报错，就是因为少了openpyxl这个库。后面我会专门说这个坑。

3.2 三个库的分工，不要混着用

这个项目的技术选型其实就一句话：pandas负责数据结构，openpyxl负责Excel文件IO，matplotlib负责画图。

pandas是绝对的核心。它读进来的Excel表格会变成一个DataFrame，你可以把它理解成一张增强版的Excel表，只不过所有行和列都能用代码来操纵。按条件筛选、分组求和、排序、透视表，这些在Excel里要拖半天的操作，在pandas里往往是一两行代码的事。

openpyxl则是底层IO工具。pandas读取.xlsx文件时，会把openpyxl作为解析引擎来使用。你不需要直接调用openpyxl的API，但它必须存在。同理，当我们要把统计结果写回Excel时，pandas的ExcelWriter也是默认用openpyxl来写。

matplotlib负责可视化。它是最经典的Python绘图库，功能覆盖面广，学习资料多。matplotlib默认的样式确实不算惊艳，但它胜在可控性极高——字体、颜色、坐标轴、图例、标注，每一处都能精细调整。对于家庭支出这种极简分析场景，它的能力已经绰绰有余。

3.3 验证环境是否装好

环境配好之后，先别急着写代码，用一行命令确认依赖没问题：

python -c "import pandas; import openpyxl; import matplotlib; print('ok')"

终端输出"ok"就说明三件套都通了。这一步能帮你排除掉90%的"代码明明一样，怎么我这就报错"的环境问题。

4. 数据清洗是第一关：把Excel变成规整的DataFrame

4.1 读取Excel之前，先处理路径问题

所有分析的起点，是把这个Excel文件交给pandas：

import pandas as pd df = pd.read_excel(r"D:\family_budget\家庭支出2024.xlsx", engine="openpyxl") print(df.head())

两个细节需要解释。

第一，路径前面加了一个r，这是Python的原始字符串标记。Windows路径里大量出现反斜杠，如果不加r，\f、\t这些字符会被转义成特殊字符，经常导致文件找不到。当然写成正斜杠也没有问题，比如"D:/family_budget/家庭支出2024.xlsx"。

第二，engine="openpyxl"是明确告诉pandas用哪个引擎。pandas也会自动推断，但标注清楚会让报错信息更容易理解。

读完数据后，第一件事永远是看结构：

print(df.info()) print(df.shape) print(df.head())

4.2 三个高频脏数据场景，一次清理干净

按我的经验，家庭Excel账本常见的脏数据就三类。

第一类是日期格式不统一。Excel里有的单元格显示"2024/3/15"，有的显示"2024年3月15日"，pandas读进来后可能是字符串，也可能被解析成Timestamp，类型非常混乱。统一转换的方法是：

df["日期"] = pd.to_datetime(df["日期"], errors="coerce")

errors="coerce"的作用是：无法解析的值会被变成NaT（Not a Time）。转换之后再执行一次df = df.dropna(subset=["日期"])，把解析失败的坏行丢掉。

第二类是金额里有杂质。比如有人会在金额列写"36.5元"，或者Excel导入时带了千分符写成了"1,234.56"。清洗函数用一个就很干净：

def clean_amount(x): if isinstance(x, str): x = x.replace("¥", "").replace("元", "").replace(",", "").strip() return pd.to_numeric(x, errors="coerce") df["金额"] = df["金额"].apply(clean_amount)

第三类是分类列的同义不同名。前面提到的"买菜"和"生鲜"其实都算餐饮，"打车"和"滴滴出行"都算交通。用一个映射字典归一化：

category_mapping = { "买菜": "餐饮", "生鲜": "餐饮", "外卖": "餐饮", "下馆子": "餐饮", "地铁": "交通", "打车": "交通", "滴滴出行": "交通", "油费": "交通", "日用品": "购物", "衣服": "购物", "网购": "购物", } df["分类"] = df["分类"].replace(category_mapping)

还要顺手处理分类列里的空格和大小写问题，str.strip()和str.title()都是常用手段。归一化完成后，可以打印所有分类看一眼：

print(df["分类"].value_counts())

这一眼能帮你发现那些漏网的分类别名。

4.3 把清洗逻辑封装成函数，一劳永逸

清洗步骤每次都写一遍太浪费，我的做法是封装成一个函数，以后每个月运行直接调用：

import pandas as pd def load_and_clean_excel(file_path): df = pd.read_excel(file_path, engine="openpyxl") # 删除关键字段为空的行 df = df.dropna(subset=["日期", "分类", "金额"]) # 日期统一 df["日期"] = pd.to_datetime(df["日期"], errors="coerce") df = df.dropna(subset=["日期"]) # 金额去掉杂质，转成数值 def clean_amount(x): if isinstance(x, str): x = x.replace("¥", "").replace("元", "").replace(",", "").strip() return pd.to_numeric(x, errors="coerce") df["金额"] = df["金额"].apply(clean_amount) # 分类去空格 df["分类"] = df["分类"].astype(str).str.strip() return df df = load_and_clean_excel(r"D:\family_budget\家庭支出2024.xlsx")

这段代码运行完，你再打印df.info()，会发现日期是datetime64类型，金额是float64，分类是object但值已经干净统一，这才算拿到了一张真正可以被分析的表。

5. 用groupby把消费结构拆明白：月度、分类、预算对比

5.1 新增"月份"字段的正确姿势

分析家庭支出最基础的维度就是时间。拆月度的直觉做法是用字符串切片取出前7位，比如把"2024-03-15"变成"2024-03"。但这个方法在数据类型混乱时会翻车，而且一旦数据结构调整，代码就得跟着改。更稳妥的做法是使用pandas的周期类型：

df["月份"] = df["日期"].dt.to_period("M")

这个操作会把日期列变成Period类型，显示效果类似"2024-03"，但它保存的是"月周期"而不是字符串，理论上跨年、排序都不会出问题。如果你希望后续图表里显示得干净一点，可以再转成字符串：

df["月份_显示"] = df["月份"].astype(str)

5.2 三行代码搞定月度趋势

月度支出统计是这份账本最重要的输出之一：

monthly_summary = df.groupby("月份")["金额"].sum().reset_index() monthly_summary["月份"] = monthly_summary["月份"].astype(str) print(monthly_summary)

groupby("月份")["金额"].sum()的含义是：按月份分组，对每组金额求和，最终得到一个以月份为索引的Series。reset_index()则是把索引放回列，方便后续画图。

如果你想看月度平均每天支出多少，还可以在此基础上除以月的天数，但这需要再引入一个"当月天数"字段。家庭分析的维度不必贪多，先跑通最核心的再慢慢加。

5.3 分类汇总、占比与Top N

分类维度能回答"钱到底花到哪去了"这个灵魂拷问：

category_summary = df.groupby("分类")["金额"].sum().sort_values(ascending=False) print(category_summary)

sort_values(ascending=False)让最高的分类排在最前面。在此基础上算占比很直观：

category_ratio = category_summary / category_summary.sum() * 100 print(category_ratio.round(1))

只看Top 4或者Top 5分类，往往是分析重点：

top5 = category_summary.head(5) others = category_summary.iloc[5:].sum()

这里把排名靠后的分类合并成一个"其他"合计，再用pd.concat拼回一张表，饼图就会简洁很多。

5.4 和预算表做融合对比

花超没超预算，是这个项目最实用的输出。先把预算表读进来：

budget = pd.read_excel(r"D:\family_budget\家庭支出2024.xlsx", sheet_name="预算", engine="openpyxl")

然后和分类汇总结果做一次merge：

merged = pd.merge( category_summary.reset_index(), budget, on="分类", how="left" ) merged["差额"] = merged["金额"] - merged["预算"] merged["状态"] = merged["差额"].apply(lambda x: "超支" if x > 0 else "未超支")

how="left"保证了即使分类汇总里有预算表中不存在的分类，那行数据也不会丢，只是预算列显示为NaN。最后用print(merged)看一眼，哪些分类超支了一目了然。

5.5 把汇总结果写回Excel，方便留存

分析结果总不能每次都打印在终端里，我习惯把汇总写回Excel的多张工作表：

with pd.ExcelWriter(r"D:\family_budget\支出汇总.xlsx", engine="openpyxl") as writer: monthly_summary.to_excel(writer, sheet_name="月度汇总", index=False) category_summary.reset_index().to_excel(writer, sheet_name="分类汇总", index=False) merged.to_excel(writer, sheet_name="预算对比", index=False)

这个操作会生成一个新的Excel文件，里面有三张工作表。之后就算不打开Python，直接用Excel看汇总也没问题。

6. 可视化落地：四张核心图表与中文字体深坑

6.1 先解决中文字体和负号，否则后面白搭

matplotlib是英文世界的库，默认字体不包含中文字形。如果直接绘图，图表上所有中文都会变成一个个小方块，这是几乎所有新手都会遇到的第一道坎。解决方案是在绘图脚本开头加两行配置：

import matplotlib.pyplot as plt plt.rcParams["font.sans-serif"] = ["SimHei"] plt.rcParams["axes.unicode_minus"] = False

第一行指定中文字体为黑体，第二行让坐标轴上的负号正常显示。Windows系统一般自带SimHei，macOS可以换成"PingFang SC"或"Hiragino Sans GB"，Linux则要装中文字体并用fc-list :lang=zh查看可用的字体名称。

6.2 月度支出趋势折线图

折线图适合观察支出随时间的波动。核心代码如下：

fig, axes = plt.subplots(2, 2, figsize=(14, 10)) axes[0, 0].plot(monthly_summary["月份"], monthly_summary["金额"], marker="o", linewidth=2, color="#2E86AB") axes[0, 0].set_title("月度支出趋势") axes[0, 0].set_xlabel("月份") axes[0, 0].set_ylabel("支出金额（元）") axes[0, 0].tick_params(axis="x", rotation=45)

marker="o"会让每个数据点带上圆形标记，比光秃秃的折线直观很多。rotation=45则是把x轴的月份标签旋转45度，避免文本重叠。

6.3 分类占比饼图

饼图用于展示分类占比，这是家庭支出分析里最有视觉冲击力的一张图：

axes[0, 1].pie( category_summary.values, labels=category_summary.index, autopct="%.1f%%", startangle=90, counterclock=False ) axes[0, 1].set_title("全年分类支出占比")

autopct="%.1f%%"会在扇区内部标注百分比，保留一位小数。startangle=90让第一块扇区从正上方开始，counterclock=False则让扇区按顺时针排列，视觉上更贴合阅读习惯。

6.4 分类对比柱状图与预算对比图

柱状图适合比较不同分类之间的绝对金额。全年分类支出对比：

axes[1, 0].bar(category_summary.index, category_summary.values, color="#F18F01") axes[1, 0].set_title("全年分类支出对比") axes[1, 0].tick_params(axis="x", rotation=45)

预算对比则同时画两组柱状图，一组是实际支出，一组是预算基准：

merged_valid = merged.dropna(subset=["预算"]) bar_width = 0.35 x = range(len(merged_valid)) axes[1, 1].bar(x, merged_valid["金额"], width=bar_width, label="实际支出") axes[1, 1].bar([i + bar_width for i in x], merged_valid["预算"], width=bar_width, label="预算") axes[1, 1].set_xticks([i + bar_width / 2 for i in x]) axes[1, 1].set_xticklabels(merged_valid["分类"], rotation=45) axes[1, 1].legend() axes[1, 1].set_title("分类实际支出与预算对比")

这里用x和x + bar_width实现两组柱子的并排效果，再通过set_xticks把坐标轴刻度放在两组柱子的中间位置。颜色一深一浅，一眼就能看出哪几个分类超过预算基准线。

6.5 保存图片与整体排版

四张图用子图合并到一个画布之后，别直接plt.show()就收工。建议先调整布局再保存高清图片：

plt.tight_layout() plt.savefig(r"D:\family_budget\家庭支出分析.png", dpi=300) plt.show()

tight_layout()会自动调整子图间距，避免标题和坐标轴互相挤压。dpi=300是分辨率参数，保存出来的PNG图片用于手机查看或者打印都非常清晰。

7. 复盘：运行过程必踩的5个坑及完整排查链路

7.1 报错"ModuleNotFoundError: No module named 'openpyxl'"

我第一次运行pd.read_excel遇到的就是这个报错。明明已经安装了pandas，为什么还会报缺模块？因为pandas读取.xlsx文件时默认会去调用openpyxl这个底层解析器，而openpyxl和pandas是两个独立的库，装了pandas不代表自动装了openpyxl。

排查链路其实很清晰：先看报错信息里有没有"module named"字样，有就说明缺依赖；然后执行pip list | findstr openpyxl（Windows）或pip list | grep openpyxl（macOS/Linux）确认是否真的没装；最后运行pip install openpyxl解决。装完之后再跑一次df.info()，看到DataFrame正确生成就是通过了。

7.2 Matplotlib图表中文全部变成方块

图表上的中文全变成空心的矩形方块时，第一反应不要是"数据有问题"，而是"字体没有配置好"。这个坑的烦人之处在于Python不会报任何异常，只有图上的文字在无声抗议。

排查思路分三步：第一步确认是否写了中文字体配置，没写就补上plt.rcParams["font.sans-serif"] = ["SimHei"]；第二步确认系统里真的有这个字体，Windows下可以打开字体设置搜索SimHei，macOS和Linux可以用fc-list :lang=zh列出所有已安装的中文字体；第三步如果SimHei不可用，换成你系统上存在的其他中文字体名称，比如"Microsoft YaHei"或"Noto Sans CJK SC"。配置代码要放在所有绘图代码之前，否则不会生效。

7.3 日期列被读成字符串或Excel序列号

有时候pandas读进来的日期列不是datetime64类型，而是一串"45166"这样的数字。这是Excel内部的日期序列号，表示从1900年1月1日开始计算的天数。直接处理起来很别扭，如果没头绪会卡很久。

排查和解决的关键还是类型检查：先在df["日期"]上调用pd.api.types.is_datetime64_any_dtype判断类型，如果不是日期类型，就用pd.to_datetime做一次显式转换。需要特别说明的是，errors="coerce"这个参数一定要带上，否则一旦某个单元格写成了"未知"或者空值，整个转换会直接中断报错。转换完成后用df.dtypes验证一下，看到datetime64就说明这一关过了。

7.4 汇总金额和手算的对不上

这是最隐蔽也最容易让人怀疑人生的坑。明明代码逻辑没问题，但Python统计出来的月度总支出，跟自己手动框选Excel的SUM结果总是差几十甚至上百块。

我排查了很久才发现根源是Excel表格里存在空行和小计行。有时候在表格中间补录了几天数据，为了看清区块就插入空行，或者在月底写了一行"本月小计"。这些行在Excel里看起来无伤大雅，但pandas读取时，小计行会被当成一条真实的支出记录参与求和，空行则可能导致部分数据错位。

排查思路要注意：不要只盯着一月份看，而是打印出所有分类和月份的明细分组，逐一比对手工账本中每一笔记录的日期和金额。最容易定位问题的方法是df[df["金额"].isna()]查看空值行，再通过df[df["分类"] == "小计"]这类筛选把非正常记录过滤掉。最终我在清洗函数里明确加了两个动作：删除关键字段为空的行，以及过滤掉分类列包含"小计""合计"字样的行。从那以后，Python统计和手算结果就完全一致了。

7.5 图表里的金额显示为科学计数法

金额大的时候，比如某一类全年支出累计到了五位数以上，matplotlib在y轴上的刻度标签可能显示成"1e4"这样的科学计数法。这在家庭账本里看着极不直观。

解决办法有两种。最简单的是直接在绘图前对数值做格式化，但这会影响计算，不太推荐。更规范的做法是在绘图后修改坐标轴的刻度格式化器：

from matplotlib.ticker import FuncFormatter def format_y_tick(value, _): return f"{value:,.0f}" axes[0, 0].yaxis.set_major_formatter(FuncFormatter(format_y_tick))

FuncFormatter允许你自定义刻度标签的显示方式，format_y_tick函数把数值格式化为带千分符的整数，比如"12,345"。这样既不改变底层数据，又能让图表里的金额一眼读明白。

如果你也想做一套自己的家庭支出分析，我的建议是别一上来就追求功能齐全。先跑通"读取Excel→清洗→月度汇总→分类汇总→四张图"这个最小闭环，用真实数据看到结果，你自然会发现下一步想加什么功能。我现在这个脚本已经跑了一年多，每月底双击一次，自动生成当月分析图片和汇总Excel，整个过程不需要打开Excel手工处理任何数据。工具选型的核心逻辑从来不是谁更高级，而是能不能真正解决你手头那个具体的问题。