在人力数据分析这个方向看得多了我发现真正把同行拖垮的往往不是分析思路而是Excel里那套复制粘贴、VLOOKUP、透视表、调格式的流水线。每月初收各部门花名册、入职台账、离职台账串数据就得大半天交上去的报表老板只看几个数字。这篇要拆解的脚本就是用Pandas把这整套流程压缩到十分钟内跑完——从Excel难民到人力数据分析师中间其实只差一个能自动跑的脚本。代码不复杂适合已经在写基础Python、想把Pandas落到业务报表上的同学也适合HR、行政、人效分析岗位想给自己减负的人。1. 项目思路先盘清需求再写代码别上来就堆函数写这类脚本最忌讳的就是一上来就查函数文档把groupby、pivot_table、merge全用一遍最后发现和实际业务对不上。我会花半小时把需求盘清楚这个报表给谁看每个月都要哪些指标源数据从哪来、长什么样梳理清楚之后代码反而是最不费劲的部分。1.1 一个普通HR的月度报表现场假设你在一家几百人的公司做人力数据每个月的固定动作是这样的先从HR系统导出在职员工花名册从招聘同事那里要入职台账从离职流程里拉离职台账。三张Excel到手之后开始痛苦的串数环节——用VLOOKUP把离职日期匹配到花名册、用COUNTIF统计各部门人数、再用透视表拉年龄和司龄分布。这些操作本身不算难但每个月重复做一遍而且每次都可能因为新增了一列、换了一个人操作而出现误差。有一次我帮某公司处理历史报表发现他们连续三个月的离职率对不上最后查出来是离职台账里有人重复离职、被COUNTIF统计了两次。这类低级错误在手工流程里几乎无法避免因为Excel只给你看结果不给你保留过程。这也是我最终转向Pandas做整套逻辑的原因。1.2 为什么选Pandas而不是继续堆Excel公式日常沟通里很多人问我Excel透视表也能做这些为什么要学Pandas我的回答是透视表能做到操作出来但做不到可重复、可追溯、可批量。下面这张表最能说明问题。对比项Excel手工操作Pandas脚本月度重复耗时半天到两天几十秒一致性每次操作都可能出偏差逻辑固定结果可复现过程记录无只能靠截图和备注代码即文档数据量几万行就开始卡几十万行无压力格式调整每次手工设置自动输出统一格式Pandas在这类场景里的优势是天然的Excel的每一张表都可以看作一个DataFrame结构天然匹配读取和写出Excel文件有成熟引擎支持清洗空值、转日期、分组统计这些操作比Excel公式直观得多。如果你只是想快速看一个数Excel没问题但如果你每月、每周甚至每天都要产出同一套报表脚本化是唯一能长久坚持的方案。1.3 脚本整体流程设计我习惯把这个脚本拆成五个环节每个环节独立成函数方便单独调试读取源数据在册花名册、入职台账、离职台账统一指定数据类型。数据清洗去空行、去重、日期解析、剔除关键字段缺失的记录。指标计算部门结构、入离职率、年龄/司龄/学历/薪酬分布。报告输出用ExcelWriter写多Sheet工作簿自动调列宽、冻结表头。一键运行main函数串联全流程进阶玩法用定时任务每月自动执行。这样设计的原因是实际工作里源数据的脏程度远超想象清洗如果不单独成一个环节后面所有计算都会失真。而把计算逻辑独立开每次业务口径有调整只需要改对应函数不用动整个脚本主体。2. 环境准备与源数据清洗报表出错九成是栽在这里很多初学者把注意力放在指标计算上结果数据源没处理好后面全盘皆输。我的经验是清洗环节决定了报表可信度的上限。这一节把环境和清洗讲透代码可以直接抄。2.1 依赖安装脚本依赖非常轻只需要两个库pip install pandas openpyxlpandas负责数据处理openpyxl负责把DataFrame写成带格式的Excel文件。注意pandas默认写.xlsx时用的引擎就是openpyxl所以必须装。读.xls老格式的话需要xlrd但现在大部分公司都已经用xlsx了不建议为旧格式额外折腾。2.2 源数据结构与常见的脏数据以某中型公司为例花名册一般包含工号、姓名、部门、职级、入职日期、出生日期、学历、性别、月薪等字段。入职台账通常有工号、姓名、入职日期离职台账则是工号、姓名、离职日期。看起来字段不多但脏数据千奇百怪工号是数字格式读进来变成了1.0这种float甚至科学计数法。日期有的单元格是真日期有的却是文本2024/11/1混着来。部门列里存在全角空格比如技术部和技术部 会被分组统计成两个部门。离职台账里有人没有填离职日期因为还没走完流程就被拉进了表里。花名册里掺着全空的备注行、合并单元格残留。这些如果不处理后续每个指标都会出问题。所以清洗代码要写得保守一点宁可多滤掉异常也不要让脏数据进计算。2.3 清洗代码逐行拆解import pandas as pd emp_dir data/ emp_df pd.read_excel(emp_dir 员工花名册.xlsx, dtype{工号: str}) entry_df pd.read_excel(emp_dir 入职台账.xlsx, dtype{工号: str}) leave_df pd.read_excel(emp_dir 离职台账.xlsx, dtype{工号: str}) # 1. 去掉全空行 emp_df emp_df.dropna(howall) entry_df entry_df.dropna(howall) leave_df leave_df.dropna(howall) # 2. 按工号去重保留最后一次出现的记录 emp_df emp_df.drop_duplicates(subset[工号], keeplast) # 3. 日期解析解析失败置为NaT for col in [入职日期, 出生日期]: emp_df[col] pd.to_datetime(emp_df[col], errorscoerce) # 4. 剔除核心字段缺失的记录 emp_df emp_df.dropna(subset[工号, 姓名, 部门])这里有几个关键点要着重说明。dtype{工号: str}是很多新手忽略的Excel里工号如果是数字pandas默认会读成int或float超过15位的工号还会丢失精度转成字符串后保留完整内容同时也能兼容00123这种带前导零的编号。dropna(howall)只删整行全空的记录不会误删部分字段缺失的数据。日期转的时候用errorscoerce解析不了的值会变成NaT不会直接抛异常中断脚本。最后按工号去重加keeplast是因为实际场景中同一工号可能因为信息更正在表里出现多次后更新的记录通常更准确。注意在做任何groupby统计之前先打印一下emp_df.info()和emp_df[部门].unique()亲眼确认数据长什么样。这一步虽然简单但能避免后面一堆诡异结果。3. 核心指标计算逻辑拆解每个公式都讲透清洗做完就到了重头戏——指标计算。我按部门结构、入离职、年龄司龄、薪酬分布四个模块来讲每个模块我都做了代码示例和口径说明方便你直接改成自己公司的逻辑。3.1 在职人数与部门结构groupbyagg一把梭部门结构是管理层最常看的一张表核心是各业务部门的人数、占比、平均司龄。dept_stats emp_df.groupby(部门).agg( 人数(工号, count), 平均司龄(入职日期, lambda s: (pd.Timestamp.today() - s.mean()).days / 365) ).reset_index() dept_stats[占比] (dept_stats[人数] / dept_stats[人数].sum() * 100).round(2) dept_stats dept_stats.sort_values(人数, ascendingFalse)这里用groupby().agg()一次算出多个指标比分别调用size()和mean()再合并要清晰。平均司龄的计算稍微讲究一点先用today() - 入职日期得到每个员工的司龄天数求平均值后再除以365这样先平均再换算避免了算完天数再平均绕弯子的问题。占比保留两位小数并且按人数降序排列这样管理层打开表第一眼看到的就是人员规模最大的部门。如果公司部门有层级结构还可以加一层部门二级的分组用groupby([一级部门, 二级部门])逻辑完全不变。3.2 入离职与离职率时间窗口过滤是关键入离职统计的难点不在函数而在时间窗口怎么界定。很多公司的月度报表口径不统一有的用当月入职人数/期末在册有的用当月入职人数/月均在册。我脚本里把窗口参数抽出来想切口径直接改变量。month_start pd.Timestamp(2024-11-01) month_end pd.Timestamp(2024-12-01) m_entry entry_df[(entry_df[入职日期] month_start) (entry_df[入职日期] month_end)] m_leave leave_df[(leave_df[离职日期] month_start) (leave_df[离职日期] month_end)] entry_count len(m_entry) leave_count len(m_leave) # 离职率口径当月离职人数 / 期末在册人数 * 100 turnover_rate round(leave_count / len(emp_df) * 100, 2)这里有一个经常踩的坑 month_start但 month_end。如果你用了 month_end12月1日当天入职的人会被重复统计进11月报表因为日期边界重合。凌晨的这个边界问题在涉及跨月统计时尤其容易出错建议统一用左闭右开区间也就是包含开始、不包含结束。离职率的口径我多说一句。业界有离职人数/期末在职和离职人数/月均在册两种算法月均算法更平滑但需要期初和期末两个时点的人数数据要求更高。我的默认脚本用期末人数简化处理如果你的公司要求月均口径改成leave_count / ((start_count end_count) / 2) * 100即可两个人数可以从期初和期末的花名册切片里拿到。3.3 年龄与司龄结构pd.cut把连续值变成区间年龄和司龄分析本质上就是把连续变量分段。Excel里通常手动加辅助列用IF嵌套判断写起来又长又容易错。Pandas的cut()函数是干这个的标准工具。today pd.Timestamp.today() emp_df[司龄年] ((today - emp_df[入职日期]).dt.days / 365.25).round(1) bins [0, 3, 5, 8, 15, 100] labels [3年以下, 3-5年, 5-8年, 8-15年, 15年以上] emp_df[司龄区间] pd.cut(emp_df[司龄年], binsbins, labelslabels, rightFalse) tenure_dist emp_df.groupby(司龄区间, observedFalse).size().reset_index(name人数)rightFalse这个参数容易被忽略它表示区间是左闭右开也就是[3, 5)包含3但不包含5这样可以避免3年整的人被切到上下两个区间的情况。年龄段的写法完全一样把入职日期换成出生日期把bins换成[0, 25, 30, 35, 40, 50, 100]就得到标准年龄结构表。observedFalse是配合分类数据类型使用的作用是即便某个区间在当月没有人分组结果里也会保留这一行、人数为0。做环比对比时这个细节非常重要否则某个月某个年龄段恰好零人下个月又出现人两张表的行数都对不上。3.4 薪酬区间分析把重复逻辑抽成通用函数薪酬分布和上面的年龄段逻辑高度相似唯一的区别是分段的列和区间值不同。这时候硬编码是最常见的坏味道我建议抽一个通用函数。def segment_distribution(df, value_col, bins, labels): df df.dropna(subset[value_col]).copy() df[区间] pd.cut(df[value_col], binsbins, labelslabels, rightFalse) summary df.groupby(区间, observedFalse).agg( 人数(姓名, count), 平均薪酬(月薪, mean) ).reset_index() summary[人数占比] (summary[人数] / summary[人数].sum() * 100).round(2) return summary salary_bins [0, 5000, 8000, 12000, 20000, 50000] salary_labels [5k以下, 5k-8k, 8k-12k, 12k-20k, 20k以上] salary_summary segment_distribution(emp_df, 月薪, salary_bins, salary_labels)这个函数里dropna(subset[value_col])单独拿出来的原因是薪资字段经常有缺失如果不删掉cut会因为遇到NaN直接报错或者把缺失值归入某个错误区间。平均薪酬在区间内更有业务含义所以我顺手加进了agg里。注意分段边界要根据你公司实际的薪酬水平调整不要照抄我这里的5k/8k/12k。我见过有人直接套用网上模板结果整家公司80%都在最低档一张报表毫无区分度。先用emp_df[月薪].describe()看看分位数再定bins。4. 报告自动化输出多Sheet、格式调整、一键运行指标算完还不算结束报表的呈现质量决定了别人愿不愿意看。Pandas写Excel默认是纯数据格式很丑。我习惯在输出阶段花一点功夫让成品打开就能直接用不需要再手工调整。4.1 用ExcelWriter一次写出多Sheet工作簿月度人力报表一般包含好几张子表汇总页、部门结构、入离职情况、年龄结构、薪酬分布。用pd.ExcelWriter可以在同一个工作簿里写多个Sheet。output_path output/人力月度报表.xlsx with pd.ExcelWriter(output_path, engineopenpyxl) as writer: dept_stats.to_excel(writer, sheet_name部门结构, indexFalse) tenure_dist.to_excel(writer, sheet_name司龄结构, indexFalse) salary_summary.to_excel(writer, sheet_name薪酬分布, indexFalse)我特意创建一个汇总Sheet把核心指标放在最前面方便老板不看细节也能抓到重点。这个汇总表可以用一个简单的DataFrame按指标名称和数值两列摆开也可以用现有计算好的变量拼接。indexFalse是必须的否则pandas会把行号写进A列表格里多出一列没用的索引。4.2 自动调整列宽与冻结表头打开Excel最烦的就是表头看不见、列宽乱七八糟。这段代码放在writer的with块里在所有Sheet写完后统一处理格式。from openpyxl.utils import get_column_letter wb writer.book for ws in wb.worksheets: ws.freeze_panes A2 for col_idx in range(1, ws.max_column 1): max_len 0 for row in ws.iter_rows(min_colcol_idx, max_colcol_idx): cell_value row[0].value if cell_value is not None: max_len max(max_len, len(str(cell_value))) ws.column_dimensions[get_column_letter(col_idx)].width min(max_len * 1.2 2, 30)freeze_panes A2表示固定第一行向下滚动时表头始终可见。列宽这块我取了该列所有单元格的最大字符长度再乘1.2加上一点余量这样中文和数字都能完整显示。设置上限30是为了防止遇到超长文本时把列宽撑爆。最后再加一行代码把数字列的格式统一成两位小数否则平均值会出现一堆小数位for ws in wb.worksheets: for row in ws.iter_rows(min_row2): for cell in row: if isinstance(cell.value, float): cell.number_format 0.004.3 一键运行与定时触发所有功能都串到一个main()函数里用if __name__ __main__:保护起来这样脚本既可以被命令行调用也可以被其他程序import。def main(): # 读取与清洗 # 指标计算 # 报表输出 print(f报表已生成{output_path}) return output_path if __name__ __main__: main()日常使用直接在命令行跑python monthly_report.py就行。如果公司电脑允许还可以用系统自带的任务计划程序设定每月1号早上9点自动执行一次脚本跑完把Excel放到共享目录。我在某公司落地过这个方案HR同事到岗看到的已经是成品报表唯一的门槛就是他们得接受系统会自动生成这个事实。提示不要一开始就上定时任务。先手工跑两周确认每期数据都对得上再自动化。自动化只是放大你现有流程的质量流程本身有bug的话定时任务只会让你每周收到一份错误报表。5. 实际踩坑记录这些问题不处理报表全是错的这个脚本的核心逻辑不复杂但我实际落地时踩过的坑不少每一个都直接导致过报表数据失真。整理成速查表之前先讲三个最典型的场景。5.1 工号变科学计数法与精度丢失第一次跑脚本时我导出汇总表发现工号全变成了1.23457E14一开始以为是Excel显示问题后来一查源数据才发现pandas在读Excel的时候就把工号按数字读进来了超过15位的数字精度直接丢失。解决办法就是read_excel时加dtype{工号: str}让pandas从一开始就把工号当文本处理。如果源表里工号已经是数字格式且丢失了精度那就只能回HR系统重新导出文本格式的源文件这一步没办法用代码救回来。5.2 日期列读出来是数字或字符串Excel里同一列日期经常有的是真日期有的是文本pandas读进来后可能是字符串、可能是datetime、甚至可能是数字。我的统一处理方式是读进来后用pd.to_datetime加errorscoerce强转再dropna(subset[日期列])把无法解析的扔掉。这样不管源表里混了多少种格式最终进入计算的日期一定是datetime类型。5.3 部门列存在全角空格和隐藏字符统计部门人数时出现过技术部和技术部 这种长短不一的写法groupby之后同一个部门被拆成了两条记录。排查过程非常费劲因为Excel里看起来完全一样但字符编码不同。我的清洗习惯是统一加一步emp_df[部门] emp_df[部门].astype(str).str.strip().str.replace(\u3000, )把半角空格、全角空格全部清掉必要时再加str.lower()统一大小写。整理成速查表方便你直接对照排查现象可能原因解决方案工号变成科学计数法Excel数字格式导致精度丢失read_excel加dtype{工号: str}日期列读出来是数字单元格是文本日期或格式混乱pd.to_datetime(..., errorscoerce)部门分组出现同名两项全角/半角空格、隐藏字符str.strip()配合str.replace()清理离职日期缺失导致统计错离职流程未完结也在台账里判断NaT后再算时间窗口别直接比较报表行数对不上源表存在重复工号drop_duplicates(subset[工号])平均薪酬出现一大堆小数未设置单元格格式统一设置number_format 0.006. 最后一点实操经验最后分享一个我在实际项目里养成的习惯每次跑完报表不要只看汇总数字随手抽查两三个部门的明细是否和源表一致。具体做法是在脚本里加一行print(dept_stats[[部门, 人数]].head(10))大数据量时再顺手存一份明细Sheet。因为这个脚本的输入是人在维护的Excel只要源数据哪天变了格式、多了一列或者少了一行输出的结果就可能悄悄变化。自动化不是让你彻底不看报表而是把精力从机械操作腾出来放到检查数据和解释业务上。脚本给你的自由是你可以把时间花在为什么这个月离职率涨了这种真正有价值的问题上而不是花在这个单元格怎么又串行上。
