写这篇文章的起因很简单。月初接到一个临时任务要把几份Excel里的销售流水整理成图表汇报。我第一反应是用Excel的函数和图表硬啃但当我看到那几张表加起来有接近十万行、列名还不统一的时候果断放弃了。老老实实打开Python用pandas把数据全读进来再做点清洗最后用matplotlib出了几张图前后不到一个下午。最近在不少技术群里看到新人问“怎么用Python读Excel”“怎么把Excel数据画成图”所以我把这套入门流程完整走一遍从安装到绘图的坑都写出来。不管你是刚接触Python的运营、财务还是转行做数据分析的同学这套pandas加matplotlib的组合都值得花半天学会。读Excel只是pandas最基础的一环但你只要跨过这一步后面处理CSV、数据库导出文件、甚至网页抓下来的表基本就是同一套逻辑。1. pandas为什么适合处理Excel数据1.1 直接用Excel处理大文件的痛点我见过太多人用Excel处理上千行数据就开始卡顿更不用说十万行。Excel本身不是做大数据处理的工具它的强项是展示和简单的交互操作。一旦你需要对多张表做关联、分组统计、去重、透视操作步骤会变得极其繁琐而且难以复用。比如你要统计“每个区域每个月销售额的同比变化”Excel里得加辅助列、多层透视、写复杂公式稍不留神就出错。pandas解决的是“数据加载、清洗、变换、分析”这一整个流水线。它把Excel表格抽象成DataFrame——你可以理解成一张放在内存里的“超级表格”既有行索引也有列名能像SQL一样筛选、分组、连接又能像Excel一样直接对单元格做运算。最关键的是相同的处理逻辑可以脚本化下个月再跑一遍改个文件名就行。1.2 pandas读Excel的适配场景pandas读取Excel时底层依赖的是xlrd、openpyxl或odfpy这些解析库。它不仅能读取传统的.xlsx也能读取老的.xls和ODF格式。日常我用的最多的是openpyxl引擎因为它对.xlsx的支持最完善还能保留格式如果只是读值格式不太重要。读取这件事本身看似简单但实际工作中你需要处理的细节非常多指定读取哪个工作表、跳过空行、将某列设为索引、只读取某些列、处理日期列、避免把充当地址线的数字读成科学计数法等等。这些参数如果没掌握后续画图时会出现各种诡异问题。直接用pandas还有个好处是它自带很多“数据体检”方法。数据读进来后一个info()就能看到每列的类型和非空数量describe()能快速看数值列分布。做汇报前先做这步往往能发现底层脏数据的问题比如某个月的销售额出现了负值或者订单日期格式乱七八糟。2. 环境准备与首次读取2.1 安装pandas和绘图库第一步永远是装环境。我用的是Anaconda自带的Python环境如果没装Anaconda直接在命令行执行也会很容易pip install pandas openpyxl matplotlib这里有个小细节早期pandas读取.xlsx需要单独安装xlrd特定的版本但现在更推荐安装openpyxl并且读取.xlsx文件时显式指定引擎为openpyxl。我建议初学阶段统一用openpyxl避免碰到“Unknown engine”或版本冲突的问题。装好之后验证一下import pandas as pd import matplotlib.pyplot as plt print(pd.__version__)如果你看到版本号正常输出比如2.x就说明环境没问题。注意matplotlib在写代码时建议加一句plt.rcParams[font.sans-serif] [SimHei]否则绘图中文字体会变成方块这个后面再说。2.2 第一个read_excel示例我现在模拟一份实际数据文件假设文件叫sales_data.xlsx里面有一张工作表叫“订单明细”列分别是订单日期、区域、销售员、产品、数量、单价、销售额。最基础的读取方式import pandas as pd df pd.read_excel(sales_data.xlsx, sheet_name订单明细) print(df.head())代码只有两行但背后发生了什么pandas会调用openpyxl打开工作簿定位到“订单明细”工作表读取所有非空行的值然后自动推断每列的数据类型能转成数字的读成int64或float64长得像日期的读成datetime64其余大部分是object也就是字符串。打印出来的head()会展示前五行带有一个默认生成的数字索引RangeIndex。这一步验证是否读对了文件路径和表名。如果文件名或路径写错会直接抛出FileNotFoundError。这个错误信息很明确基本不会踩坑。2.3 常用参数详解sheet_name、index_col、usecols、dtype光是df pd.read_excel(sales_data.xlsx)还不够实际工作里Excel表往往有很多猫腻。下面这些参数是我认为必须掌握的sheet_name指定工作表。可以传工作表名字符串、工作表位置整数0代表第一个、甚至列表一次读多个表。传列表时返回的是OrderedDict每个表一个DataFrame这在处理“多个门店报表”的场景很常用。header指定哪一行作为列名。默认header0也就是第一行是列名。但有些表格前两行是大标题和副标题实际列名在第三行那就要header2。如果读了之后发现列名变成了Unnamed: 0这类东西基本就是header没设对。index_col设置某一列作为行索引。比如index_col订单编号后续按索引取数会更方便。不是必填但当你需要做时间序列图时直接把日期列设为索引特别有用。usecols只读取部分列。比如一张表有20列但分析只需要其中5列用usecols[区域, 销售额, 日期]可以显著减少内存和读取时间。如果列名有特殊字符也可以传入列号列表像我一般写usecolsA:B,D这种Excel列号范围。dtype强制指定列类型。这个很关键。比如订单ID是18位数字Excel里会显示成科学计数法读进pandas会被当成int64。表面上看着没问题但一打印就变成6.700180e17转成字符串还会丢精度。正确做法是读取时强制指定为字符串dtype{订单ID: str}。parse_dates把某列解析为日期。如果Excel里日期是按2024-01-01格式存的pandas默认就能识别。但如果是20240101这种纯字符串或者混合格式就需要parse_dates[订单日期]或自定义日期函数。我见过最头疼的情况是同一个日期列里Excel上半部分是日期格式下半部分突然变成了文本。这种脏数据只有读取后通过类型检查才能发现。我的建议是第一次读取时先不加任何复杂参数读进来后立刻用df.dtypes看类型。发现问题再回到dtype参数强制修正。初学时我会建议你把下面这段代码存成模板每次读Excel直接用import pandas as pd df pd.read_excel( sales_data.xlsx, sheet_name订单明细, header0, dtype{订单ID: str}, parse_dates[订单日期] ) print(df.info()) print(df.head())df.info()会显示DataFrame的列数、行数、非空数量和每列类型。这一步相当于给数据做了一次全面体检。我看到很多新手读完数据就急着画图结果图里全是空值或者类型错误问题其实在读取阶段就已经埋下了。3. 数据初探从读取到能绘图的第一步3.1 查看数据基本信息和基本清洗数据读进来后绝对不能直接拿去画图。第一步是了解数据结构第二步是把明显的问题处理掉。拿上面的订单数据举例我先跑这几行print(df.shape) # 行数和列数 print(df.columns) # 所有列名 print(df.isna().sum()) # 每列缺失值数量 print(df.describe()) # 数值列的描述统计describe()对数值列输出count、mean、std、min、25%分位数等。这些指标能快速暴露问题比如“销售额”这一列最小值是负数说明可能有退款数据没处理好count如果远小于行数说明缺失较严重。清洗阶段最常见的操作有三个删掉完全没用的列、处理缺失值、修正类型。删除列df df.drop(columns[备注, 内部编号])处理缺失值要分情况。如果缺失集中在某几行可以直接删掉df df.dropna(subset[订单日期, 销售额])如果某列缺失率很高比如超过50%但又是次要信息直接删除这列更省事。如果是数值列可以用均值或中位数填充但这在分析时容易引入偏见建议根据业务判断。修正类型的一个经典坑是“数值列被读成object”。通常是因为这一列里有少数单元格是文本比如“暂缺”“N/A”。pandas为了不丢信息会把整列推断成object。处理办法是先用pd.to_numeric强制转换不能转换的部分变成NaNdf[销售数量] pd.to_numeric(df[销售数量], errorscoerce)这样处理后文本值会变成NaN再配合dropna或fillna解决。只要类型对了后面画图时坐标轴才不会乱。3.2 分组聚合是绘图前的关键准备很多时候你要画的不是“每一行订单”而是“每个区域的月度销售额”“每个产品的销量占比”这种汇总结果。pandas里的groupby就相当于Excel的透视表。比如计算每个区域的销售总额area_sales df.groupby(区域)[销售额].sum().reset_index() print(area_sales)注意我加了reset_index()因为groupby之后结果是个Series索引是“区域”。不加的话后续用matplotlib画图时x轴数据还得通过area_sales.index取容易绕晕。加上reset_index()之后区域变成普通列取数更直接。如果要做“每月销售额趋势”需要先把日期列设为索引再按月份重采样df df.set_index(订单日期) monthly_sales df[销售额].resample(M).sum()这里resample(M)的意思是按月汇总。得到的是一个以月末日期为索引的Series非常适合直接画折线图。如果Excel里的日期列时间粒度是具体到时分秒resample(M)会自动按月归类。我建议你在正式绘图前把要画的数据先print出来看一眼。确认索引、列名、数值范围都符合预期再开始写绘图代码。很多人画图失败不是绘图代码错了而是数据这一步没搞对。4. pandas配合matplotlib输出报表图表4.1 解决中文显示问题这节开始前先处理一个所有中文用户都会遇到的问题——matplotlib默认不支持中文。如果你不设置字体画出来的图里所有中文字都变成一个个小方框。两种靠谱的解决方法。第一种在代码里设置字体import matplotlib.pyplot as plt plt.rcParams[font.sans-serif] [SimHei] plt.rcParams[axes.unicode_minus] FalseSimHei是黑体大部分Windows环境都有。如果你的机器没有可以改成Microsoft YaHei微软雅黑或KaiTi。axes.unicode_minus要设成False否则负号显示不正常。Mac用户可以用Arial Unicode MS。第二种方案是下载字体文件放到matplotlib字体目录并在代码里注册。这个稍麻烦但对Linux服务器很有效。如果你只是本地跑第一种方案就够了。4.2 折线图观察销售额走势假设我们要画“最近12个月销售额走势”。先准备好按月汇总的数据monthly_sales df[销售额].resample(M).sum()然后绘图months monthly_sales.index.strftime(%Y-%m) values monthly_sales.values plt.figure(figsize(10, 5)) plt.plot(months, values, markero, linewidth2, color#2E86AB) plt.title(月度销售额趋势, fontsize14) plt.xlabel(月份) plt.ylabel(销售额元) plt.xticks(rotation45) plt.grid(alpha0.3) plt.tight_layout() plt.show()这里months monthly_sales.index.strftime(%Y-%m)把日期索引转成字符串主要是为了让x轴标签更整洁避免显示成2025-01-31 00:00:00。markero给每个数据点加个小圆点方便看清具体值。tight_layout()解决标签被裁掉的问题绘图后保存图片时非常有用。如果你想把图保存下来把plt.show()换成plt.savefig(monthly_sales.png, dpi200, bbox_inchestight)bbox_inchestight会自动调整边界确保标题和坐标轴标签完整。4.3 柱状图对比不同区域销量画区域对比图通常用柱状图。先把分组后的数据准备好area_sales df.groupby(区域)[销售额].sum().sort_values(ascendingFalse).reset_index()注意sort_values(ascendingFalse)是把区域按销售额从高到低排序。这样画出来柱子从左到右依次变矮视觉效果更直观。plt.figure(figsize(8, 5)) bars plt.bar(area_sales[区域], area_sales[销售额], color[#1B9E77, #D95F02, #7570B3, #E7298A, #66A61E]) plt.title(各区域销售总额对比, fontsize14) 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}, hacenter, vabottom, fontsize9) plt.tight_layout() plt.show()柱子上方的一行for bar in bars:循环是给每个柱子添加数值标签。f{height:,.0f}的格式是带千分位分隔符的整数比如12,345,678比直接显示科学计数法友好得多。这是很多教程默认不教你、但汇报时必备的细节。4.4 饼图看产品类别占比饼图适合展示“类别的构成”比如各销售员业绩占比。注意饼图不适合超过5~6个分类的数据否则会挤成一团。product_sales df.groupby(产品)[销售额].sum().sort_values(ascendingFalse) plt.figure(figsize(7, 7)) plt.pie(product_sales.values, labelsproduct_sales.index, autopct%.1f%%, startangle90, counterclockFalse) plt.title(各类产品销售额占比) plt.tight_layout() plt.show()autopct%.1f%%把百分比保留一位小数显示在扇区上。startangle90让第一块从正上方开始counterclockFalse保证顺时针排列。如果某个类别的占比太小饼图里标签会重叠这时候可以把labels参数关掉改用图例legend()或者直接用柱状图更清晰。4.5 在一张图里画多个子图一个汇报页面经常需要同时展示趋势和对比可以用subplots把多个图放到一张大图里。我常用的方式是fig, axes plt.subplots(1, 2, figsize(12, 5)) # 左图月度趋势 axes[0].plot(months, values, markero, color#2E86AB) axes[0].set_title(月度销售额趋势) axes[0].tick_params(axisx, rotation45) # 右图区域对比 axes[1].bar(area_sales[区域], area_sales[销售额], color#D95F02) axes[1].set_title(区域销售总额对比) plt.tight_layout() plt.show()fig, axes plt.subplots(1, 2, figsize(12, 5))创建了一行两列的子图数组。之后通过axes[0]和axes[1]分别操作。关键是每个子图都要自己设置标题、坐标轴、刻度不然要么内容缺失要么格式不统一。tight_layout()在多个子图时尤其重要否则子图之间的标题会叠在一起。5. 实战中那些会让人卡住的问题5.1 read_excel直接崩溃文件路径与文件名最常见的问题就是文件路径。有人把Excel文件和Python脚本放在同一个文件夹但文件名有个看不见的空格或者中文括号导致FileNotFoundError。还有一种情况是文件虽然在桌面上但脚本的工作目录不在桌面代码里直接写sales_data.xlsx就会找不到文件。我一般建议在代码开头用绝对路径或者用os.chdir明确切换工作目录。绝对路径虽然长但一劳永逸df pd.read_excel(rC:\Users\用户名\Documents\sales_data.xlsx)字符串前加r是为了让反斜杠不被当成转义字符。如果你写的路径出现\U、\t这种组合不加r会直接把路径搞坏。如果文件名是中文且操作系统编码不是UTF-8比如Windows极少数情况下会报UnicodeDecodeError。解决方法是把文件重命名为英文名或者在读取前用df pd.read_excel(path, engineopenpyxl)强制指定引擎因为openpyxl路径编码处理得比较稳。5.2 明明Excel里有数据读取后却全是NaN遇到过好几次读者拿到一张别人发来的表第一行和第二行是标题中间还有合并单元格。read_excel默认header0数据就会从第三行开始读第二行被当成列名列名变得乱七八糟实际数据全部跑到Unnamed: N下面。这时候你要先用Excel打开看前几行长什么样再确定header参数。如果列名在第三行就写header2。如果有多级表头可以header[0,1]之后再用columns df.columns.droplevel(0)把第一级去掉。还有一类NaN是公式问题Excel里某个单元格明明有公式但公式引用的其他文件不存在读取时就会变成#REF!或空值。pandas只能读值没法帮你重新计算外部引用。这得跟数据提供方确认让他们先跑一遍公式再保存。5.3 日期列读出来是数字或乱码Excel里的日期本质上是数字距离1900年1月1日的天数如果单元格格式设置不当pandas读取时可能得到类似45292这样的数字。解决方法是读取时显式告诉pandas这一列是日期df pd.read_excel(data.xlsx, parse_dates[订单日期])如果parse_dates没能正确识别可以自己转换df[订单日期] pd.to_datetime(df[订单日期], errorscoerce)errorscoerce表示无法解析的字符串变成NaTNot a Time而不是报错中断。转换之后再检查有没有NaT出现如果有说明原表里有非日期格式的脏数据需要找出来单独处理。5.4 绘图时颜色和线条太丑matplotlib默认的颜色其实已经不错但多张图放在一起时会显得单调。我更推荐使用专业色板。最简单的方式是直接指定十六进制颜色custom_colors [#264653, #2A9D8F, #E9C46A, #F4A261, #E76F51]#号后的六位数字是RGB的十六进制表示。#264653是深蓝绿#E76F51是珊瑚橙这套搭配是我在图表配色里用得最多的对比度适中打印出来也清晰。如果你不习惯手动选色也可以安装seaborn它提供了漂亮的默认主题调用import seaborn as sns后直接绘图即使你只有matplotlibseaborn的set_style也能让matplotlib图表立竿见影地变好看。5.5 图太多导致内存卡顿当你循环读取上百个Excel文件并逐一绘图时如果不注意释放内存进程会越来越慢最后直接卡死。解决方案有两个一个是用del删除不再使用的DataFrame并调用gc.collect()回收内存另一个是用理解式逐文件处理处理完立即保存图片for file in excel_files: df pd.read_excel(file) # 处理数据 plt.close(all)每次绘图后记得plt.close(all)否则会打开大量隐藏的Figure窗口占用内存。这个坑在批量出报告时特别常见我自己曾因为没关图脚本跑到一半电脑就无响应了。6. 一页搞定完整流程的小模板写到这里我想把上面散落的知识点串成一个可以直接运行的完整流程。假设你现在要做一份“各区域月度销售趋势分析”下面是懒人版基准代码稍微改改列名就能用。import pandas as pd import matplotlib.pyplot as plt plt.rcParams[font.sans-serif] [SimHei] plt.rcParams[axes.unicode_minus] False # 读取 df pd.read_excel( sales_data.xlsx, sheet_name订单明细, parse_dates[订单日期], dtype{订单ID: str} ) # 清洗去空类型修正 df df.dropna(subset[订单日期, 销售额, 区域]) df[销售额] pd.to_numeric(df[销售额], errorscoerce) df df.dropna(subset[销售额]) # 聚合每月每区域销售额 df[月份] df[订单日期].dt.to_period(M) report df.groupby([月份, 区域], as_indexFalse)[销售额].sum() # 透视列是区域行是月份 pivot report.pivot(index月份, columns区域, values销售额).fillna(0) # 绘图每个区域一条线 plt.figure(figsize(12, 6)) for area in pivot.columns: plt.plot(pivot.index.astype(str), pivot[area], markero, labelarea) plt.title(各区域月度销售额趋势) plt.xlabel(月份) plt.ylabel(销售额元) plt.legend() plt.xticks(rotation45) plt.grid(alpha0.3) plt.tight_layout() plt.savefig(区域月度销售额趋势.png, dpi200, bbox_inchestight) plt.show()里面有两个小技巧值得解释。df[订单日期].dt.to_period(M)可以把日期转换成“年月”的周期类型比resample更适合多列分组场景。pivot把“月份-区域”的宽表转成“每一行是一个月、每一列是一个区域”的透视表这样画多条折线时直接遍历每一列就是一个区域。如果某些月份某个区域没有销售数据pivot会得到NaN用fillna(0)填成0否则画图时会出现断线。这一点在真实数据里特别常见别忘了加。最后再分享一点实在经验pandas读Excel本身不复杂但它像所有工具一样需要用真实数据喂一遍才会熟练。如果你手里没有现成的Excel强烈建议用df.to_excel自己造一点假数据出来练手sample_data { 订单日期: pd.date_range(2025-01-01, periods365, freqD), 销售额: np.random.randint(100, 2000, size365), 区域: np.random.choice([华东, 华南, 华北, 西南], size365) } sample_df pd.DataFrame(sample_data) sample_df.to_excel(sample_sales.xlsx, indexFalse)这里用到了numpy的随机函数需要import numpy as np。自己造数据的好处是你可以完全控制每列类型和缺失情况把坑规避掉专心练习绘图和聚合逻辑。等这些基础操作都顺畅了再拿真实的脏数据来练你才知道读文件时那些参数到底为什么存在。我个人在实际项目里的体会是pandas读Excel只是万里长征第一步真正花时间的是读取之后的“数据体检”和“聚合变换”。这两个环节做得越仔细后面画的图就越不容易返工。你可以在项目里给自己定一个规矩读进数据后先打印df.dtypes、df.isna().sum()确认无误后再开始处理。这个小习惯帮我省掉了无数次半路排查数据异常的麻烦也推荐你试一试。
