如果有那么一张表格每个月都要做同样的统计贴同样的公式改同样的图表范围那你大概率能理解我下面要说的事。上个月我打开那份密密麻麻的家庭消费流水打算照老规矩用Excel透视表汇总出“各分类支出”和“月度趋势图”结果发现汇总区域忘了扩展图表引用了空行颜色还乱成一团——那一刻我决定换一种方式用pandas读取Excel配合matplotlib直接出图。十几行代码跑完误差、格式、图表全齐了整个过程不到一分钟。这篇文章不是系统性的pandas教程而是围绕“读取Excel并绘图”这条完整实操链路展开的记录从环境准备、read_excel参数选择、数据预处理到图表的坐标体系理解、中文显示排错最后封装成一个每月可复用的脚本。适合那些想在重复性报表统计里减少手工操作的人也适合刚开始接触pandas、想拿真实表格练手的同学。我不太喜欢讲大而全的理论所以下面所有内容都跟着我实际踩过的坑走。1. 一张手工账单把我逼到pandas面前先说结论pandas读取Excel并不是什么高深操作它的本质就是把Excel当数据源用代码按固定规则把内容读进内存里的表格结构DataFrame中然后继续处理、统计、出图。真正让它值得学的是它能把“每次都要重复做”的统计流程固化成脚本。我个人的触发点是一份流水账单。每月末家里会把当月每一笔支出记在一张表里列有“日期”“分类”“项目”“金额”有时候还有一个备注列。以前我用Excel的做法是插入数据透视表把“分类”拖到行标签把“金额”拖到求和项再插入一张柱状图和一张折线图。听起来不复杂但时间长了就会遇到几个麻烦。第一是数据范围。每个月记录的行数不一样透视表的数据源区域有时候会残留旧的范围导致新增的行统计不进去。第二是图表格式。Excel新插入的图表默认配色和样式每次都不同想要统一得手动调。第三是人为失误。我试过不止一次在更新某个月的汇总时忘了把上个月的“合计”行选中范围排除掉结果图表里多出一个莫名其妙的“总计”柱子。这几个问题单独看不严重但它们每个月都出现积累起来就很消耗耐心。pandas处理这件事的优势体现在三个层面规则写死在代码里读哪个Sheet、取哪几列、过滤掉哪些无效行都是代码逻辑每次执行结果一致人不会累也不会忘。统计与绘图同源汇总用groupby得到的结果可以直接传给绘图库表格和图表不会出现“数据对不上”的尴尬。能应对更大的数据量Excel在几万行以内体验还行再往上就卡pandas底层是向量化计算几十万行的表也基本瞬间完成统计。当然我不是说Excel透视表没用。如果你的场景是一次性的、临时性的、数据量又小打开Excel手动做可能更快。但一旦发现自己在“每月重复同样的动作”就该考虑把流程变成代码了。这就是我这次尝试的核心动机。2. 读取Excel的read_excel到底该怎么用参数其实就这几个很多人第一次用pd.read_excel读取表格时会觉得函数参数很多、记不住。实际上日常场景里掌握几个关键参数就够了。先看我最常用的一段代码import pandas as pd df pd.read_excel( 月度流水.xlsx, sheet_name消费记录, header0, usecolsA:E, dtype{金额: float}, parse_dates[日期] )这里每个参数都需要解释清楚因为它们对应着Excel表格里的各种“坑”。2.1 环境准备为什么要装openpyxl在用read_excel之前要保证环境里有读取Excel文件的后端库。pandas本身并不直接解析Excel二进制格式它需要依赖第三方库来当“翻译官”。读取.xlsx格式时用的是openpyxl读取老的.xls格式时用的是xlrd。安装命令很简单pip install pandas openpyxl如果只装了pandas没装openpyxl运行时大概率会看到类似“Missing optional dependency openpyxl”的报错。这不是你的代码写错了而是缺少底层解析器。实际开发里我甚至建议把openpyxl作为项目的固定依赖装好因为后面如果要把结果写回Exceldf.to_excel同样需要它。2.2 sheet_name对应工作簿里的哪张工作表Excel文件里可能有多个Sheet一个Sheet就是一张工作表。如果你不指定sheet_namepandas默认读取第一个Sheet。大多数时候这没问题可一旦工作簿里第一张是“说明”“目录”之类的Sheet读取结果就会很灾难。参数可以传Sheet名称字符串也可以传位置序号0代表第一个Sheet甚至可以传一个列表同时读取多个Sheet# 按名称读取 df1 pd.read_excel(path, sheet_name消费记录) # 按位置读取0表示第一个Sheet df2 pd.read_excel(path, sheet_name0) # 一次性读多个Sheet返回一个字典键是Sheet名值是DataFrame dfs pd.read_excel(path, sheet_name[消费记录, 收支汇总])我自己的习惯是显式传sheet_name不依赖默认行为。因为今天文件第一个Sheet可能是流水下个月可能就有人插入了一张新Sheet到最前面代码如果隐含“默认第一个”就会悄然读到错误的数据。显式传参相当于给代码加了一道保险。2.3 header与usecols表头在哪一行只取哪些列Excel表格不一定都规规矩矩从A1单元格开始。常见的情况有两种第一行是标题第二行才是列名或者前几行是表头说明文字真正列名在第3行。header参数就是告诉pandas“列名在第几行”行号从0开始计数# 列名在第4行索引为3 df pd.read_excel(path, header3)如果表格完全没有表头可以设置headerNone然后手动给列命名df pd.read_excel(path, headerNone, names[日期, 分类, 项目, 金额])usecols则用来控制读取哪些列。它接受Excel列字母如A:E或列序号。这个参数最大的价值不是节省那一点点内存而是避免把备注列、无关列带进来干扰后续处理。比如备注列里写着“待报销”“已转出”这类文本画图时完全用不到早点排除干净后面省心。注意当同时指定headerNone和names时名称数量必须与列数匹配不匹配会直接报错。2.4 dtype与parse_dates两个最容易忽视的读取参数dtype用于强制指定列的数据类型parse_dates用于把某些列解析成日期类型。说出它们的常见应用场景你就明白为什么重要了金额列如果不指定dtype{金额: float}当该列中出现空值或文本时pandas可能会把整列推断成object类型后面做sum就会得到字符串拼接而不是数值相加画图时甚至会报错。日期列如果不解析读进来时通常显示为datetime还好但有时你拿到的是字符串“2024/01/05”这样要画时间趋势图就得先转换。用一个参数省掉后面的to_datetime调用。实测下来读取阶段把这两个参数设置正确后面的清洗和绘图会非常顺如果偷懒不设置大概率会在汇总时报一些让你摸不着头脑的类型错误。df pd.read_excel( 月度流水.xlsx, sheet_name消费记录, header0, usecolsA:E, dtype{金额: float}, parse_dates[日期] ) print(df.dtypes)这段代码运行后你会看到“日期”列的类型是datetime64[ns]金额是float64。这个结果本身就是一次体检类型正确说明读进来的数据可以放心进入下一步统计。3. 读进来先别画图三分钟体检帮你避开后面的大坑读取数据只是万里长征第一步。我见过很多人read_excel一执行完立刻df.plot()结果图是出来了但数字总感觉不对。问题几乎都出在“读进来后没有体检数据”这一步。3.1 用info()和head()快速判断数据是否正常读取后必做的第一件事是看一眼数据和结构。我的习惯是连敲两行df.info() print(df.head())info()会列出每一列的非空数量、数据类型这是对数据完整性最直观的判断。比如一个Sheet看起来有200行数据但df.info()显示“日期”列只有180个非空值那很可能Excel里存在空行或部分单元格没填。head()则是看一眼前几行的样子主要核对列名是否正常。遇到列名末尾带空格、列名是“项目(1)”这种由Excel自动生成的情况在这里就能立刻发现。3.2 缺失值和无效行怎么处理流水表里最常见的无效数据有三类完全空白的行、Excel里的“合计”“小计”行、以及金额为零或负数的异常记录。空白行通常是因为在表格中间删除数据后留下了空行或者人为地隔几行加了个分隔行。处理方式很简单df df.dropna(howall)howall表示只有当整行都是空值时才删除。这样可以保留那些只有个别单元格为空的记录不至于误伤数据。“合计”行这类文本行需要用文本过滤去掉。比如备注列里含有“合计”字样或者分类列等于“总计”都可以用布尔索引过滤df df[~df[分类].isin([合计, 小计, 总计])]还有一类情况是日期列里有无效值比如“2024-13-45”这种格式错误的内容。parse_dates无法识别时该单元格会变成NaT缺失日期。这类行如果不剔除后面按月份聚合时会出现一个神秘的“NaT月份”图表上就会多出一个不知道是什么的柱子。df df.dropna(subset[日期])3.3 类型检查在统计之前先统一口径体检的最后一环是确认汇总要用的列类型。前面dtype已经强制了金额为float但如果数据源是一个没有经过dtype设置的旧文件这里就需要补一步转换df[金额] pd.to_numeric(df[金额], errorscoerce)errorscoerce的作用是能转成数字的就转转不成的置为NaN。这是个很有用的参数它不会因为某一行出现了“约100元”这种文本就直接让整个转换报错而是把这些脏数据标记出来方便我们后续决定是删掉还是手动修正。提醒一下如果你发现金额列里混入了太多无法转换的文本说明源表格录入规范有问题。这时候应该回头去改Excel模板而不是在代码里硬补。体检做完的判断标准是每一列的类型符合预期、没有意外缺失的整列、没有“合计”这类文本残留。满足这三点数据就算“干净”了可以进入绘图环节。4. 绘图的底层逻辑先明白figure和axes再谈画什么图很多初学pandas绘图的人有一个共同困惑明明df.plot()就能出图为什么还要理解figure和axes我的回答是不理解这两个概念你就不理解为什么有时候画出来的图特别小为什么想叠加两个图却互相覆盖为什么一套代码在不同表格上尺寸不一致。4.1 figure像画板axes像画纸matplotlib里的figure可以理解成一张空白画板而axes是画板上的一张坐标纸。一个画板上可以放一张坐标纸单图也可以放多张子图。你所有的标题、图例、坐标轴其实都是画在某张axes上的画板只负责承载它们。了解了这一点下面这句代码的含义就非常清晰了import matplotlib.pyplot as plt fig, ax plt.subplots(figsize(10, 6))plt.subplots(figsize(10, 6))一次性创建了画板和坐标纸figsize决定了画板的物理尺寸。返回的ax就是后续绘图的主战场。当你在ax上画线、画柱形、设置标题本质上就是在“这张坐标纸上涂鸦”。最后调用plt.show()才把成品展示出来。pandas的DataFrame直接提供.plot()方法本质上是对matplotlib的封装。它支持两种用法df.plot(axax, kindline) # 把图画到已有的ax上 df.plot(kindbar) # 不传ax时matplotlib自动创建新的画布我推荐在脚本里始终显式创建fig, ax然后通过ax把图赋到指定坐标纸上。这样当你想在一个画板上放多张子图时可以方便地用plt.subplots(1, 2)创建两张纸分别把不同的统计图放上去。4.2 三种图的选型逻辑Excel里选图表类型靠直觉在代码里选图也一样核心是看你想强调什么。折线图linex轴是连续的时间或有序阶段强调变化趋势。月度支出趋势、每日余额变化都是典型。柱状图barx轴是分类变量强调不同类别的对比。各分类支出总额、各月份支出对比都适合。饼图pie展示占比结构。哪类消费占总支出的比例最高可以直观看到。我自己的经验是趋势分析优先折线图类别对比优先柱状图占比结构才用饼图。不要迷信饼图当分类超过六个时饼图就成了“五颜六色的色块大杂烩”远不如一张横向柱状图清晰。4.3 一个能直接跑的绘图代码继续用月度流水的场景。按分类汇总金额再看各分类占比然后用折线图看每天支出趋势import matplotlib.pyplot as plt # 解决中文乱码和负号显示问题下一节详述 plt.rcParams[font.sans-serif] [SimHei] plt.rcParams[axes.unicode_minus] False # 按分类汇总金额 category_sum df.groupby(分类)[金额].sum().sort_values(ascendingTrue) # 1. 分类支出柱状图 fig, ax plt.subplots(figsize(10, 6)) category_sum.plot(kindbarh, axax, color#4C72B0) ax.set_title(月度分类支出汇总) ax.set_xlabel(支出金额元) fig.tight_layout() fig.savefig(分类支出汇总.png, dpi200, bbox_inchestight) plt.show() # 2. 每日支出折线图 df[日期] pd.to_datetime(df[日期]) daily_sum df.groupby(日期)[金额].sum() fig, ax plt.subplots(figsize(12, 5)) daily_sum.plot(kindline, axax, markero) ax.set_title(每日支出趋势) ax.set_ylabel(支出金额元) fig.tight_layout() fig.savefig(每日支出趋势.png, dpi200, bbox_inchestight) plt.show()这里面有两个细节值得展开说一下。一个是groupby(分类)[金额].sum()的返回结果类型是SeriesSeries的.plot()方法跟DataFrame的plot()是兼容的。它自动把“分类”作为x轴刻度标签把汇总金额作为柱子高度。sort_values(ascendingTrue)是为了让柱状图从低到高排列画出来最有层次的对比效果。另一个是fig.savefig()与plt.show()的配合。如果直接运行plt.show()弹窗显示图表有时代码会阻塞等待窗口关闭。批量生成报告时我更倾向于用savefig先保存图片再决定要不要显示。dpi200控制分辨率bbox_inchestight会自动裁剪掉多余留白这个参数几乎每次保存图片我都带着。5. 实测中逃不掉的三类报错中文乱码、Sheet名、脏列名绘图代码看着简单真正跑起来的时候踩坑概率最高的就是下面三个问题。我按实际排查的顺序把它们写在这里每一个都附带着完整的排查链路而不是直接跳到最后答案。5.1 中文乱码图表上的方块字到底怎么修第一个遇到的坑几乎每个用matplotlib画中文图的人都会踩图标标题正常写了中文结果运行时所有中文都变成了一排排的方块。这个问题的根因是matplotlib默认字体里没有中文字形。排查链路是这样的在脚本里执行print(plt.rcParams[font.sans-serif])查看当前可用的无衬线字体列表。如果列表里没有中文字体matplotlib就会用默认字体去渲染中文然后得到方块。解决方案是把系统里已有的中文字体名加到rcParams里。我用的方案是import matplotlib.pyplot as plt plt.rcParams[font.sans-serif] [SimHei] plt.rcParams[axes.unicode_minus] FalseSimHei是系统自带的中文字体等于是把默认字体替换成黑体。第二行的axes.unicode_minus False解决的是另一个问题当坐标轴上有负号时如果不设置这个参数负号会显示成一个带斜杠的方块。原因是matplotlib默认使用Unicode的减号字形而这个字形在中文字体里没有导致渲染异常。如果你运行环境里没有SimHei比如某些精简版的Linux系统可以从系统字体列表里选一个比如Noto Sans CJK SC、Songti SC或Arial Unicode MS。判断方法也很简单一行代码列举所有可用字体名然后逐个看哪个能正常显示from matplotlib import font_manager fonts [f.name for f in font_manager.fontManager.ttflist] print(sorted(set(fonts)))把这个列表里的字体名填入font.sans-serif即可。这个问题的本质不是什么高深原理就是字体资源的映射问题但它在实战中出现的频率极高值得写进任何人的“必踩坑清单”。5.2 读取报错Sheet名里有空格和特殊字符如果你明确写了sheet_name消费记录但运行时报错Worksheet named 消费记录 not found第一反应可能是Sheet名不对。我用一个真实案例说明排查过程有一次我从某系统导出的Excel文件里明明在工作簿里看到了“消费记录”这个Sheet但代码怎么都读不到。后来我打开Excel的VBA编辑器或者重新审视工作簿结构时才发现真实Sheet名是“消费记录 2024”末尾带了一个数字后缀。也就是说肉眼看到的名称和实际Sheet名并不一致。这个问题的排查手段很直接把工作簿里的所有Sheet名列出来看看。import pandas as pd xls pd.ExcelFile(月度流水.xlsx) print(xls.sheet_names)输出结果是所有Sheet的准确名称列表。把这个列表打印出来之后你就不会再去猜测“是不是多了一个空格”而是直接拿到真实名字复制进代码即可。如果确定Sheet名无误但读取还是报错那就要检查是不是文件路径的问题。路径中含有中文字符在某些旧的版本下偶尔有兼容问题但新版本pandas基本已经解决。更常见的是相对路径问题脚本和工作簿不在同一目录时相对路径会找不到文件。我的建议是一律用绝对路径或者用Path(__file__).parent这种手段确保路径始终指向脚本所在目录。5.3 脏列名读取后列名带着空格或者查不到第三种坑发生在列名上。比如Excel里“项目”列的列名实际上是“项目 ”末尾有个人的空格你用df[项目]去取就会直接KeyError。再比如有人习惯在Excel的表头里用“金额元”这种带括号的列名读进来之后列名变成了“金额元”如果之前凭印象写了df[金额]照样取不到。这个问题的常规解法是读到DataFrame之后把列名做一次统一清洗。df.columns df.columns.str.strip()strip()会去掉列名两端的空格这是一行值得写进任何pandas脚本的“防御式代码”。如果要更彻底可以用列表推导式把列名里的中文括号、特殊字符也清理掉df.columns [ col.strip().replace(, ().replace(, )) for col in df.columns ]我通常会在看完df.info()和df.head()之后顺手执行这个清洗让后面的代码面对的都是规整列名避免因为一个不可见字符而排查半天。这里再补一个高频问题有些Excel表格第一行是空行header0会把第一行当成列名于是列名全是“Unnamed: 0”。解决办法是读取时指定header为实际有列名的那一行或者读取后把Unnamed列给drop掉。总之看到“Unnamed”的列名可以先怀疑表头位置设置不对。6. 把这套流程封装成每月复用的账单报告脚本当上面的流程跑通之后你会进入一个新的阶段不想再反复复制粘贴这些代码而是希望每个月把新表下载下来运行一个脚本自动得到统计数据、图表和汇总结果。这一步是pandas读取Excel这件事真正产生价值的地方。6.1 用函数组织核心流程我的做法是把核心步骤拆成三个函数读取、清洗、绘图。这样职责分开哪一步出问题能快速定位。from pathlib import Path def load_and_clean(excel_path, sheet_name消费记录): df pd.read_excel( excel_path, sheet_namesheet_name, header0, usecolsA:E, dtype{金额: float}, parse_dates[日期], ) df.columns df.columns.str.strip() df df.dropna(howall) df df[~df[分类].isin([合计, 小计, 总计])] df df.dropna(subset[日期, 金额]) return df def build_category_bar(df, ax): category_sum df.groupby(分类)[金额].sum().sort_values(ascendingTrue) category_sum.plot(kindbarh, axax, color#4C72B0) ax.set_title(月度分类支出汇总) ax.set_xlabel(支出金额元) return category_sum def build_daily_line(df, ax): daily_sum df.groupby(df[日期].dt.date)[金额].sum() daily_sum.plot(kindline, axax, markero) ax.set_title(每日支出趋势) ax.set_ylabel(支出金额元) return daily_sum封装的好处不仅仅是省了几行重复代码。它强迫你固定住流程的“输入输出接口”函数接收原始表路径返回清洗后的DataFrame绘图函数接收这个DataFrame和坐标纸对象。以后别人拿给你的表格结构变化了只需要改load_and_clean里对应的清洗逻辑后面的绘图代码完全不用动。6.2 生成报告并存图也存一个Excel结果表绘图之后还有一个常见需求自己看图表方便但整理账目时可能需要一张“各分类汇总表”。所以我在脚本末尾会把统计结果也写回Excel格式统一整理import matplotlib.pyplot as plt plt.rcParams[font.sans-serif] [SimHei] plt.rcParams[axes.unicode_minus] False def main(): excel_path 月度流水.xlsx df load_and_clean(excel_path) fig, axes plt.subplots(1, 2, figsize(16, 6)) build_category_bar(df, axes[0]) build_daily_line(df, axes[1]) fig.tight_layout() out_path Path(excel_path).with_name(月度报告.png) fig.savefig(out_path, dpi200, bbox_inchestight) plt.show() category_sum df.groupby(分类)[金额].sum().sort_values(ascendingFalse) category_sum.to_excel(Path(excel_path).with_name(月度汇总.xlsx), sheet_name分类汇总) if __name__ __main__: main()这里用到了Path(excel_path).with_name(...)它的作用是把“月度流水.xlsx”这个输入文件名替换成“月度报告.png”和“月度汇总.xlsx”保持输出文件始终和输入文件在同一目录也避免硬编码输入路径。6.3 关于我自己的使用建议按我实际跑了几个月账单脚本的经验有几点体会值得单独拿出来说。第一不要追求一遍写出完美脚本。第一版能把数据读出来、图画出来就够了后续再逐步增加清理逻辑、增加子图、调整样式。代码是越用越顺手的不是一下设计好的。第二这个流程不只适用于家庭账单。你完全可以用同样的模式处理任意周期性Excel报表每周销售明细、每日库存变化、月度考勤记录。核心永远是“读取—清洗—汇总—绘图”换的只是列名和业务口径。第三如果某一天你发现图表里的数字和Excel透视表对不上不要急着怀疑代码。优先检查源文件里有没有被隐藏的过滤条件、是否有人工改过的单元格、是否有合并单元格导致的行数偏移。这类问题在数据源头不在pandas。我的习惯是每个月拿到新Excel后先跑一遍load_and_clean打印出前几行和df.info()确认数据源正常再放心生成那张“月度报告.png”。你觉得麻烦但经历过一次“图表看起来正常、数字其实是错的”之后就会明白这一步省不掉。如果你想继续往深了走可以把这些函数再扩展比如加上按周的滚动趋势、加一列“上月对比”的差异比例或者把生成的图表和汇总表打包发送到某个共享目录。pandas读取Excel只是入口绘图是呈现真正让你省时间的是把整套流程沉淀成自己的工具。