Python 数据处理进阶（十九）Pandas 与 Excel 的分工：哪些活该留给 Excel

写在前面前 18 篇讲了一路 Pandas 有多能干这一篇唱个反调有些活Excel 确实比 Pandas 合适。比如老板要一份「能自己改数、图表跟着变」的表比如财务要按公式核对每一行比如一份文件要在群里传阅、每个人签个字。这些场景硬用 Python 做做出来的东西没人会用。核心判断只有一句话Pandas 负责「算」Excel 负责「给人用」。具体怎么分工这篇用代码把两边的边界走一遍。先确认示例数据在手边importosimportmkdataifnotos.path.exists(data/sales.csv):mkdata.main()print(示例数据就绪,os.path.exists(data/sales.csv))这段会检查第 1 篇生成的data/目录在不在不在就自动补一次已经有数据时只会打印一行示例数据就绪 True不影响后面的结果。一、Excel 的杀手锏公式会自己重算Pandas 算完就是静态值改一个数要重跑全流程Excel 里改一个单元格整张表的公式自动重算。这是它不可替代的地方。下面用 openpyxl 写一个带公式的小表再用 pandas 读回来看看会发生什么。importosimportpandasaspdfromopenpyxlimportWorkbook,load_workbook os.makedirs(output,exist_okTrue)pathoutput/公式演示.xlsxwbWorkbook()wswb.active ws.title报价ws.append([产品,数量,单价,金额])rows[(A-笔记本,3,49.9),(B-鼠标,5,29.9),(D-显示器,2,599.0)]fori,(name,qty,price)inenumerate(rows,start2):ws.append([name,qty,price,fB{i}*C{i}])# 金额写成公式ws.append([合计,None,None,SUM(D2:D4)])wb.save(path)print(已写入公式,path)# pandas 读公式没被 Excel 计算过读到的是空值df_readpd.read_excel(path,sheet_name报价)print(pandas 直接读)print(df_read.to_string(indexFalse))print(----)# data_onlyTrue读缓存的计算结果没有缓存也是空df_cachedpd.read_excel(path,sheet_name报价,engineopenpyxl).fillna((空))print(把 NaN 标出来看)print(df_cached.to_string(indexFalse))已写入公式 output/公式演示.xlsx pandas 直接读 产品 数量 单价 金额 A-笔记本 3.0 49.9 NaN B-鼠标 5.0 29.9 NaN D-显示器 2.0 599.0 NaN 合计 NaN NaN NaN ---- 把 NaN 标出来看 产品 数量 单价 金额 A-笔记本 3.0 49.9 (空) B-鼠标 5.0 29.9 (空) D-显示器 2.0 599.0 (空) 合计 (空) (空) (空)结论很关键openpyxl 写进去的公式文件里没有「计算结果」除非用 Excel 打开过并保存。所以你要做「给别人改的表」→ 写公式让人用 Excel 打开你要做「自己分析的数据」→ 别绕 Excelpandas直接算完再写值你要读别人表里的公式结果 → 用openpyxl.load_workbook(path, data_onlyTrue)前提是那个文件被 Excel 保存过。fromopenpyxlimportload_workbook pathoutput/公式演示.xlsxwbload_workbook(path,data_onlyTrue)# 只读缓存值wswb[报价]print(data_only 读到的 D 列)forrowinws.iter_rows(min_row2,values_onlyTrue):print( ,row)data_only 读到的 D 列 (A-笔记本, 3, 49.9, None) (B-鼠标, 5, 29.9, None) (D-显示器, 2, 599, None) (合计, None, None, None)二、透视表两边各怎么写「按城市和产品算销售额」这个需求Excel 是拖字段Pandas 是pivot_table。下面在 Excel 里写出透视结果再用 Pandas 算一遍对比两者。importpandasaspd dfpd.read_csv(data/sales.csv,encodingutf-8-sig)# Pandas 版透视表行城市列产品值金额求和ptpd.pivot_table(df,index城市,columns产品,values金额,aggfuncsum,fill_value0,marginsTrue,# 加一行/一列合计对应 Excel 的「总计」margins_name总计,).round(2)print(pandas 的 pivot_table)print(pt.to_string())print(----)# 写到 Excel方便对照着看pt.to_excel(output/透视表_pandas版.xlsx,sheet_name透视)print(已导出 output/透视表_pandas版.xlsx)pandas 的 pivot_table 产品 A-笔记本 B-鼠标 C-键盘 D-显示器 总计 城市 上海 0.0 299.0 1246.0 0.0 1545.0 北京 49.9 0.0 178.0 0.0 227.9 广州 0.0 179.4 356.0 0.0 535.4 杭州 349.3 59.8 0.0 2396.0 2805.1 总计 399.2 538.2 1780.0 2396.0 5113.4 ---- 已导出 output/透视表_pandas版.xlsx对照记住三句话Excel 的「行/列/值」pivot_table(index, columns, values)Excel 的「值字段设置求和/计数/平均」aggfuncExcel 的「总计」marginsTrue。pivot_table默认会丢掉缺失分组要保留就加dropnaFalse——这就是为什么表里那个「空城市」在 pandas 版里能看到在手工透视表里常被忽略。三、导出「给人用」的 Excel三个要素既然是交付就不能只写数据。至少做到三点表头清楚、列宽合适、关键列有格式。importosimportpandasaspdfromopenpyxlimportload_workbookfromopenpyxl.stylesimportAlignment,Font,PatternFillfromopenpyxl.utilsimportget_column_letter dfpd.read_csv(data/sales.csv,encodingutf-8-sig)df[金额]df[金额].fillna((df[数量]*df[单价]).round(2))cleandf[df[金额]0].drop_duplicates()os.makedirs(output,exist_okTrue)pathoutput/交付版_销售明细.xlsxwithpd.ExcelWriter(path,engineopenpyxl)aswriter:clean.to_excel(writer,sheet_name明细,indexFalse)clean.groupby(城市,dropnaFalse)[金额].sum().round(2).reset_index().to_excel(writer,sheet_name按城市,indexFalse)bookload_workbook(path)forwsinbook.worksheets:forcellinws[1]:cell.fillPatternFill(solid,fgColor0F766E)cell.fontFont(colorFFFFFF,boldTrue)cell.alignmentAlignment(horizontalcenter)fori,colinenumerate(ws.iter_cols(values_onlyTrue),start1):widthmax(len(str(v))*2forvincol)ws.column_dimensions[get_column_letter(i)].widthmin(max(width4,10),32)ws.freeze_panesA2# 金额列第 7 列加千分位ifws.title明细:forcellinws[G][1:]:cell.number_format#,##0.00book.save(path)print(已导出,path)print(sheet,load_workbook(path).sheetnames)已导出 output/交付版_销售明细.xlsx sheet [明细, 按城市]格式三件套的取舍表头填色让人一眼分清表头列宽避免####冻结首行让滚动时不丢字段名。这三样加上number_format就是「看起来是正规交付物」的最低标准。四、分工表照着选就行需求选 Pandas选 Excel数据量 10 万行以上✅会卡每周重复做同一种汇总✅ 脚本跑手工重做需要保留公式、让人改数✅ 公式自动重算需要图表随数据联动✅多人传阅、要签字审批✅要合并几十个文件✅concat手工粘贴复杂文本清洗✅str系列公式很痛苦一次性临时分析都行✅ 更快交付物要「看起来正式」生成后交 Excel✅图 1按「数据量」和「人工协作需求」画一个象限就能判断该用 Pandas 还是 Excel四象限中间那条线不是硬边界是经验分水岭。一句话总结Pandas 管计算和清洗Excel 管呈现和协作。最好的形态是两者接力——Pandas 算完、格式化成 xlsx、交给 Excel 做最后的图表和分发。常见问题Q1read_excel读公式列全是 NaN。两个原因一是文件从没用 Excel 打开保存过没有缓存值二是你读的是公式本身。想读缓存值load_workbook(path, data_onlyTrue)然后逐行取pandas.read_excel默认读的是公式文本或空值。Q2用 openpyxl 打开文件报BadZipFile。这文件不是真正的 xlsx可能是 CSV 改了后缀或者导出失败了。先file看一眼或pd.read_csv试试。Q3写公式时行列引用总是错位。记住 openpyxl 是 1-based、而且表头占第 1 行数据从第 2 行开始fB{i}*C{i}里的i要用enumerate(..., start2)。小结这一篇的核心不是学新 API而是建立边界感Pandas 算得快、适合批量但结果是静态的Excel 算得慢、但公式会重算、图表会联动、人能直接改。让 Pandas 干它擅长的清洗、汇总、批量让 Excel 干它擅长的呈现、协作、交互中间用to_excel和read_excel交接。下一篇聊一个更硬核的话题交出数据之前怎么用 10 个校验确保它没问题。