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

1. 从Excel记账到Python分析我为什么迈出这一步1.1 记账四年Excel总表越来越难伺候我家记账记了快四年一直用的Excel。一开始确实够用——每月底花十几分钟把微信、支付宝的账单手工录入一张总表再用SUMIF、SUMIFS这些函数按分类汇总一下看看餐饮花了多少、交通超没超预算。这个阶段Excel是神器公式熟了之后自动求和也就几秒钟的事。但到了第二年开始我越来越觉得不对劲。消费渠道多了以后账单碎片化严重微信一笔、支付宝一笔、信用卡一笔还有现金付款的遗漏。月底补录的时候经常对着聊天记录和账单流水一条一条找昨天那笔68块钱到底是吃饭还是买水果那种感觉真的很消耗耐心。更尴尬的是每次想换一个统计角度——比如从按月看换成按支付方式看或者想看看每月餐饮支出的环比变化又得重新调整透视表字段拖来拖去表格结构一变之前的布局全乱了。1.2 Excel记账的三个死穴总结下来Excel记账最让我抓狂的是三件事。第一手工汇总耗时。记账本身不难难的是分析账目。月底要把整月几十行数据重新核对、分类、汇总一旦发现漏记一笔所有汇总公式引用的区域就可能跟着错位。我和很多朋友聊过绝大多数人放弃记账不是懒得记而是月底汇总太烦。第二透视表有门槛且不灵活。透视表功能确实强大但前提是你得熟悉行、列、值、筛选四个区域的逻辑。偶尔拖出来一个汇总还行想多维对比就得反复调整布局而且透视表的样式很素想导出去做月度复盘或者给家人看还得再排版。第三图表模板僵化。Excel自带的图表类型不少但默认样式相当一言难尽。想自定义配色、加标注、做组合图每一步都要小心翼翼地调整。想生成一张能在手机上看得舒服的月度分析图常常得花比记账更长的时间。1.3 想明白要解决什么问题再动手后来我意识到自己真正想要的不是一套更复杂的表格技巧而是一个能自动完成读取账本→清洗数据→统计汇总→生成图表的流水线。我需要保留Excel作为录入工具因为家人也习惯用它记账但分析部分完全可以交给Python。pandas负责清洗和聚合openpyxl负责读写Excelmatplotlib负责画图三者搭配起来整个流程从一两个小时压缩到几十秒。这个项目适合两类人一类是正在用Excel记账、想进一步挖掘数据价值的人另一类是刚学Python不久、想拿真实场景练手的朋友。它不需要你懂什么高深的算法核心就是DataFrame操作加几个绘图函数但跑通之后你会对Python处理数据的能力有一个非常直观的感受。2. 账本Excel结构设计好数据是统计分析成功的一半2.1 表结构要简单到一眼就能录入做数据分析的人都知道一句话Garbage in, garbage out。Excel账本如果结构乱后面Python写得再漂亮也是白搭。我最初的账本非常随意日期有写1月5日的也有写2024/1/5的金额有带¥的有带元的分类更是五花八门买菜和生鲜其实是一回事孩子培训班和书本文具被我并到了教育里完全是看心情。后来我重做了账本结构只保留六个字段每一列都有明确规则字段示例说明日期2024-03-15统一为字符串格式按年-月-日填写分类餐饮使用固定的分类列表不要自由发挥项目超市买菜每笔支出的简单描述金额36.50纯数字保留两位小数支付方式支付宝微信、支付宝、信用卡、现金等备注周末家庭采购可选方便事后回忆这个结构并不复杂家人录入时也不会觉得有负担。关键是要坚持第三列项目写得具体一点比如超市买菜而不是买东西事后回溯时才不会对着一笔巨款发愣。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_envWindows系统下激活环境family_budget_env\Scripts\activatemacOS或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文件IOmatplotlib负责画图。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变成规整的DataFrame4.1 读取Excel之前先处理路径问题所有分析的起点是把这个Excel文件交给pandasimport pandas as pd df pd.read_excel(rD:\family_budget\家庭支出2024.xlsx, engineopenpyxl) print(df.head())两个细节需要解释。第一路径前面加了一个r这是Python的原始字符串标记。Windows路径里大量出现反斜杠如果不加r\f、\t这些字符会被转义成特殊字符经常导致文件找不到。当然写成正斜杠也没有问题比如D:/family_budget/家庭支出2024.xlsx。第二engineopenpyxl是明确告诉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[日期], errorscoerce)errorscoerce的作用是无法解析的值会被变成NaTNot 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, errorscoerce) 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, engineopenpyxl) # 删除关键字段为空的行 df df.dropna(subset[日期, 分类, 金额]) # 日期统一 df[日期] pd.to_datetime(df[日期], errorscoerce) df df.dropna(subset[日期]) # 金额去掉杂质转成数值 def clean_amount(x): if isinstance(x, str): x x.replace(¥, ).replace(元, ).replace(,, ).strip() return pd.to_numeric(x, errorscoerce) df[金额] df[金额].apply(clean_amount) # 分类去空格 df[分类] df[分类].astype(str).str.strip() return df df load_and_clean_excel(rD:\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(ascendingFalse) print(category_summary)sort_values(ascendingFalse)让最高的分类排在最前面。在此基础上算占比很直观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(rD:\family_budget\家庭支出2024.xlsx, sheet_name预算, engineopenpyxl)然后和分类汇总结果做一次mergemerged pd.merge( category_summary.reset_index(), budget, on分类, howleft ) merged[差额] merged[金额] - merged[预算] merged[状态] merged[差额].apply(lambda x: 超支 if x 0 else 未超支)howleft保证了即使分类汇总里有预算表中不存在的分类那行数据也不会丢只是预算列显示为NaN。最后用print(merged)看一眼哪些分类超支了一目了然。5.5 把汇总结果写回Excel方便留存分析结果总不能每次都打印在终端里我习惯把汇总写回Excel的多张工作表with pd.ExcelWriter(rD:\family_budget\支出汇总.xlsx, engineopenpyxl) as writer: monthly_summary.to_excel(writer, sheet_name月度汇总, indexFalse) category_summary.reset_index().to_excel(writer, sheet_name分类汇总, indexFalse) merged.to_excel(writer, sheet_name预算对比, indexFalse)这个操作会生成一个新的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系统一般自带SimHeimacOS可以换成PingFang SC或Hiragino Sans GBLinux则要装中文字体并用fc-list :langzh查看可用的字体名称。6.2 月度支出趋势折线图折线图适合观察支出随时间的波动。核心代码如下fig, axes plt.subplots(2, 2, figsize(14, 10)) axes[0, 0].plot(monthly_summary[月份], monthly_summary[金额], markero, linewidth2, color#2E86AB) axes[0, 0].set_title(月度支出趋势) axes[0, 0].set_xlabel(月份) axes[0, 0].set_ylabel(支出金额元) axes[0, 0].tick_params(axisx, rotation45)markero会让每个数据点带上圆形标记比光秃秃的折线直观很多。rotation45则是把x轴的月份标签旋转45度避免文本重叠。6.3 分类占比饼图饼图用于展示分类占比这是家庭支出分析里最有视觉冲击力的一张图axes[0, 1].pie( category_summary.values, labelscategory_summary.index, autopct%.1f%%, startangle90, counterclockFalse ) axes[0, 1].set_title(全年分类支出占比)autopct%.1f%%会在扇区内部标注百分比保留一位小数。startangle90让第一块扇区从正上方开始counterclockFalse则让扇区按顺时针排列视觉上更贴合阅读习惯。6.4 分类对比柱状图与预算对比图柱状图适合比较不同分类之间的绝对金额。全年分类支出对比axes[1, 0].bar(category_summary.index, category_summary.values, color#F18F01) axes[1, 0].set_title(全年分类支出对比) axes[1, 0].tick_params(axisx, rotation45)预算对比则同时画两组柱状图一组是实际支出一组是预算基准merged_valid merged.dropna(subset[预算]) bar_width 0.35 x range(len(merged_valid)) axes[1, 1].bar(x, merged_valid[金额], widthbar_width, label实际支出) axes[1, 1].bar([i bar_width for i in x], merged_valid[预算], widthbar_width, label预算) axes[1, 1].set_xticks([i bar_width / 2 for i in x]) axes[1, 1].set_xticklabels(merged_valid[分类], rotation45) axes[1, 1].legend() axes[1, 1].set_title(分类实际支出与预算对比)这里用x和x bar_width实现两组柱子的并排效果再通过set_xticks把坐标轴刻度放在两组柱子的中间位置。颜色一深一浅一眼就能看出哪几个分类超过预算基准线。6.5 保存图片与整体排版四张图用子图合并到一个画布之后别直接plt.show()就收工。建议先调整布局再保存高清图片plt.tight_layout() plt.savefig(rD:\family_budget\家庭支出分析.png, dpi300) plt.show()tight_layout()会自动调整子图间距避免标题和坐标轴互相挤压。dpi300是分辨率参数保存出来的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 openpyxlWindows或pip list | grep openpyxlmacOS/Linux确认是否真的没装最后运行pip install openpyxl解决。装完之后再跑一次df.info()看到DataFrame正确生成就是通过了。7.2 Matplotlib图表中文全部变成方块图表上的中文全变成空心的矩形方块时第一反应不要是数据有问题而是字体没有配置好。这个坑的烦人之处在于Python不会报任何异常只有图上的文字在无声抗议。排查思路分三步第一步确认是否写了中文字体配置没写就补上plt.rcParams[font.sans-serif] [SimHei]第二步确认系统里真的有这个字体Windows下可以打开字体设置搜索SimHeimacOS和Linux可以用fc-list :langzh列出所有已安装的中文字体第三步如果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做一次显式转换。需要特别说明的是errorscoerce这个参数一定要带上否则一旦某个单元格写成了未知或者空值整个转换会直接中断报错。转换完成后用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手工处理任何数据。工具选型的核心逻辑从来不是谁更高级而是能不能真正解决你手头那个具体的问题。