Python自动化比对Excel员工表：快速找出新增、离职与信息变更

经常有同事拿着两个Excel表问我这两个表里的员工到底哪些是新增的哪些已经离职了还有哪些人的手机号变了以前他们要么靠肉眼一行行核要么在Excel里写一堆VLOOKUP碰上几百上千人的数据一天时间搭进去还可能出错。其实用Python处理这事半小时就能搞定而且结果能输出成一份清清楚楚的差异报告。我平时最常用的方案是把两个工作表塞进pandas用员工工号或者身份证号这类唯一标识做关联然后逐列比较把新增、离职、信息变更三类结果分别拆出来。整个过程看起来不难但实际踩过的坑挺多的比如Excel自动把身份证号转成科学计数法、两表列名不一致、同一个工号重复出现等等。这篇文章就把我从最初写死脚本到现在能应对各种脏数据的完整经验都捋一遍包含可以直接抄走的代码以及我自己实测过的避坑方式。1. 项目概述与处理思路1.1 这个比对任务到底要解决什么问题员工比对这个名字听起来简单但拆开来看不同场景下的需求完全不同。我接过最多的表单大致有这么几种人员异动比对每月社保申报前要对比上个月和这个月的员工花名册找出新增入职和离职人员。信息更正核对员工自己报了新的手机号或者紧急联系人需要对比新旧两个表确认哪些字段发生了变化。跨系统数据一致性比如HR系统和考勤系统分别导出了员工名单要确认两边人员对得上信息有没有出入。不管哪种场景核心都是“以某个唯一键为准找出两个数据集之间的差异”。这个唯一键通常是工号如果没有工号那就用身份证号再不行用“姓名部门手机号”拼一个组合键出来。纯用Excel的VLOOKUP也能干这件事但当两个表都有几百上千行、还要比对十几个字段时VLOOKUP的公式会写得特别长而且一旦出现重复值、文本前后空格、数字格式不一致结果就会变得非常不可控。Python的方案则是把整个过程变成一段可重复执行的脚本以后每个月的比对都直接跑一遍省心得多。1.2 为什么选择Python而不是Excel公式我知道很多人第一反应是Excel里不就有“条件格式-重复值”吗邮件合并什么的也能做。但真实情况是Excel的重复值功能只能告诉你“哪些值在两个区域里出现”没办法直接告诉你“A表里这个人B表里对应的手机号变成了什么”。要拿到这种逐字段的差异最顺手的就是pandas的merge操作。我做个很简单的对比处理内容Excel手工操作Python两个表按主键关联需要写VLOOKUP嵌套且要复制粘贴成值merge一句搞定找出新增/离职用MATCHISNA判断再筛选indicator参数直接分组比较多个字段差异要写很多IF公式分列比较循环列名逐个比较处理身份证号变科学计数法先改列格式一旦刷新又变回去读取时指定dtypestr一劳永逸输出最终报告手动复制汇总容易漏直接导出Excel自带多个Sheet最关键的是Python代码写完之后下次数据换一批只要表头结构不变双击就能跑出结果。这个可重复性是手工用Excel处理完全比不了的。我强烈建议所有经常做这类数据处理的人哪怕只会一点点Python也值得把这个脚本保存下来后面改一改就能应对各种比对需求。2. 环境准备与数据读取2.1 安装基础依赖先用最简单的办法装好运行环境。如果你已经有Python直接打开终端或者命令行装pandas和openpyxl就可以因为Excel文件.xlsx的读取依赖openpyxl这个引擎。pip install pandas openpyxl如果是第一次装Python建议去官网下载安装包安装的时候记得勾选“Add Python to PATH”不然后面在命令行里敲python会提示找不到命令。装完之后用下面这段代码验证一下环境是不是正常import pandas as pd print(pd.__version__)能打印出版本号就说明环境没问题了。我自己常用的是Python 3.10以上的版本pandas也建议装到2.0以上处理大文件时的速度和内存表现会好一些。另外如果你电脑里既有.xlsx又有.xls文件还要额外装一个xlrd专门用来读老格式的.xls。虽然现在用.xlsx的人越来越多但有些企业内部系统还在导出老版本格式这个坑我踩过提前装上不亏pip install xlrd2.2 处理Excel读取的常见坑把两个表格读进来最简单的方式就是pd.read_excel。但很多第一次上手的人代码写对了数据读出来却是乱的。我遇到最多的坑有这几个。第一个坑是列名被Excel自动改成非预期的样子。比如表头是“工号\n(文本)”读进来后列名带着换行符或者列名是“身份证号码 ”末尾带一个空格。这些情况在Excel里肉眼看不出来但pandas读取后列名就带了特殊字符。我的习惯是读取后立刻统一列名把空格和换行全部清掉df_old pd.read_excel(上月员工表.xlsx, dtypestr) df_new pd.read_excel(本月员工表.xlsx, dtypestr) def clean_columns(df): df.columns df.columns.str.replace(r\s, , regexTrue) return df df_old clean_columns(df_old) df_new clean_columns(df_new)把所有列都按文本读进来用dtypestr这样可以防止Excel里的长数字被读成科学计数法。我每次都先做这一步后面比对时报错的情况能少一半。第二个坑是首行不是标题。有些Excel表前面会有一行大标题比如“XX公司2024年员工名单”第二行才是真正的字段名。这种情况读进来后pandas会把大标题当成列名整个数据结构就歪了。解决办法是读取时指定header参数df_old pd.read_excel(上月员工表.xlsx, header1, dtypestr)header1表示第二行是表头。具体用哪个数字自己先打开Excel看一眼数一下第几行是字段名减一就行。第三个坑是空行或合并单元格。Excel里合并单元格在pandas中会被解析成NaN或者部分单元格为空导致读出来的DataFrame中间出现很多空行。我在读取后通常会做一次过滤df_old df_old.dropna(howall) df_new df_new.dropna(howall)这样能把整行全为空的数据去掉避免后面做关联时出现一堆无效记录。3. 员工比对核心逻辑与代码实现3.1 先做数据清洗统一列名与字段格式两个表来自不同时间或者不同系统列名经常对不上。比如旧表里的列叫“员工工号”新表里叫“工号”或者旧表叫“联系电话”新表叫“手机号码”。比对前必须先把列名统一。我的做法是建立一个映射关系直接在读取后rename。假设旧表和新表的列名分别是这样旧表员工工号、姓名、部门、手机号码、身份证号新表工号、姓名、部门、联系电话、身份证号先把旧表和新表的列名重命名成统一风格rename_dict_old {员工工号: emp_id, 手机号码: phone, 身份证号: id_card} rename_dict_new {工号: emp_id, 联系电话: phone, 身份证号: id_card} df_old df_old.rename(columnsrename_dict_old) df_new df_new.rename(columnsrename_dict_new)统一后两个表都有emp_id、name、department、phone、id_card这些列后面做比对就完全不用关心原始表头长什么样了。这也是脚本能够复用的关键——以后哪怕表里加了几列只要映射关系不变代码整体就不用动。姓名和部门这类文本字段也要清洗一遍。我写了个函数把字符串前后的空格去掉顺便把全角数字转半角避免“”和“123”这种肉眼看着一样、程序认为不一样的情况def clean_text(s): if pd.isna(s): return s s str(s).strip() # 全角转半角 result [] for ch in s: code ord(ch) if code 0x3000: code 0x20 elif 0xFF01 code 0xFF5E: code - 0xFEE0 result.append(chr(code)) return .join(result)然后对需要用到的文本列批量应用for col in [emp_id, name, department, phone]: df_old[col] df_old[col].map(clean_text) df_new[col] df_new[col].map(clean_text)身份证号这种长数字也要统一转成文本后再清洗否则有些表里是数字格式有些是文本格式比对的时会出现“1.37123e17”和“137123456789012345”对不上号的情况。身份证号还有一个特殊问题末尾可能是XExcel有时会把X认成文本有时又会忽略掉。我洗数据时会统一把id_card转成大写并去掉可能的空格for df in [df_old, df_new]: df[id_card] df[id_card].astype(str).str.upper().str.replace(r\s, , regexTrue)3.2 找出新增和离职员工数据清洗完之后就可以开始真正的比对了。我常用的主键是emp_id前提是两个表里都有这个字段并且能保证同一员工在两个表里的emp_id是一致的。如果只有一个表有工号另一个表只能用身份证号那就用id_card做主键。pandas里有个特别好用的参数叫indicator他会给合并结果增加一列_merge标明每一行来自于左表、右表还是两表都有。我利用这个字段一次性区分出新增、离职和在职。思路是这样的把旧表当成左表新表当成右表做一次外连接按emp_id关联merged pd.merge(df_old, df_new, onemp_id, howouter, indicatorTrue, suffixes(_old, _new)) left_only merged[merged[_merge] left_only] right_only merged[merged[_merge] right_only] both merged[merged[_merge] both]解释一下结果left_only这条记录只在旧表里也就是旧表有、新表没有的人说明这个人很可能已经离职。right_only这条记录只在新表里也就是新表新增的人员说明这个人是本月入职的。both两边都有的人是在职员工还需要进一步比较其他字段有没有变化。这里有个细节主键用emp_id时如果两个表里emp_id的数据类型不一致比如旧表是文本“10001”新表是数字10001merge就会匹配不上全变成left_only或right_only。所以我在合并前一定会做一次类型强制转换df_old[emp_id] df_old[emp_id].astype(str).str.strip() df_new[emp_id] df_new[emp_id].astype(str).str.strip()这是比对任务里最容易翻车的坑之一后面我会在常见问题里再展开。3.3 找出信息变更的员工对于两边都存在的员工需要逐个字段去比较。比较逻辑不复杂就是遍历我们关心的列看左边和右边的值是否相同。但这里面的坑在于两个表的同一列可能列名不同在merge的时候已经通过suffixes参数把旧表列名加上了_old后缀新表列名加上了_new后缀。所以merged表里会有name_old和name_new、phone_old和phone_new这些列。我建议把需要比较的字段单独放在一个列表里写循环处理compare_cols [name, department, phone, id_card] change_list [] for _, row in both.iterrows(): for col in compare_cols: old_val row.get(f{col}_old) new_val row.get(f{col}_new) if old_val ! new_val: change_list.append({ emp_id: row[emp_id], 字段: col, 旧值: old_val, 新值: new_val })最终把change_list转成DataFrame就是完整的信息变更明细df_changes pd.DataFrame(change_list)这种做法的好处是输出结果每一行都是一个明确的“某个员工、某个字段、从旧值变成新值”报告看起来非常直观。同样如果你只关心有没有变化不关心具体哪些字段可以把循环改成把所有变化拼接成一个字符串。需要注意的是如果某个字段在旧表里是空值在新表里填了内容这算不算变化我的经验是应该算。因为信息从无到有说明员工资料被补充更新了。但在实际报告中我会把这种情况标注为“空值→新值”避免业务方误以为数据出错了。3.4 输出完整的比对报告处理完合并结果后我习惯把结果一次性输出到一个新的Excel文件里分成四个Sheet在职员工、新增员工、离职员工、信息变更明细。这样业务同事拿到一个文件就能看到所有结果不用自己在几个表格之间跳来跳去。with pd.ExcelWriter(员工比对结果.xlsx, engineopenpyxl) as writer: both.to_excel(writer, sheet_name在职员工, indexFalse) right_only.to_excel(writer, sheet_name新增员工, indexFalse) left_only.to_excel(writer, sheet_name离职员工, indexFalse) df_changes.to_excel(writer, sheet_name信息变更明细, indexFalse)输出之前我还建议给每个Sheet里已经比对过的列做一次重命名把_old和_new后缀去掉否则业务同事看到name_old和name_new会一头雾水。我自己的做法是输出的前一段先构建一个更“干净”的展示版DataFrame只保留需要的列并重命名。在职员工这份表我一般会把新旧表和并后的关键字段并排呈现这样一眼就能看到这个人两边信息的对比report_both both[[emp_id, name_old, name_new, department_old, department_new, phone_old, phone_new, id_card_old, id_card_new]] report_both.columns [工号, 姓名(旧表), 姓名(新表), 部门(旧表), 部门(新表), 手机号(旧表), 手机号(新表), 身份证(旧表), 身份证(新表)]对于新增和离职人员直接从对应DataFrame里把关键列提出来重命名成正常的中文表头就可以。这样导出的Excel直接交给非技术同事他们不需要懂pandas也能快速看出结果。另外还有一个实用小技巧比对完成后在Excel里用条件格式高亮一下“旧值≠新值”的单元格我一般会在openpyxl输出后再手工加一次颜色。这段代码也可以写进脚本里但如果你不想引入太多openpyxl逻辑直接让业务同事在查看时手动筛选“信息变更明细”Sheet就够了。4. 常见问题与排查技巧实录4.1 重复主键如何处理这是员工比对任务里最麻烦的问题。同一个工号在旧表里出现两次或者新表里出现两次merge的结果会变成笛卡尔积行数突然暴涨后续比较结果全是错乱。我先举个例子如果旧表里工号1001有两条记录新表里也有两条那么merge以后会产生4条记录而且每一条都可能和其他字段交叉看起来就像四个不同的人。这种情况必须提前处理。处理方案很简单先查重复再决定保留哪一条。dup_old df_old[df_old.duplicated(subset[emp_id], keepFalse)] dup_new df_new[df_new.duplicated(subset[emp_id], keepFalse)]如果存在重复我会把重复记录打印出来让业务方人工确认或者在确认规律后自己指定保留规则。比如同一个工号多出来的记录是历史遗留那就保留最新一条或者保留非空字段最多的一条。一份通用的去重写法是df_old df_old.sort_values(emp_id) df_old df_old.drop_duplicates(subset[emp_id], keeplast) df_new df_new.sort_values(emp_id) df_new df_new.drop_duplicates(subset[emp_id], keeplast)keeplast’的意思是保留最后一次出现的记录。选择保留哪条取决于你的数据里哪条才是“当前有效”的信息。如果业务上无法判断唯一稳妥的办法是先把重复清单导出来发给数据负责人去确认不要自己在代码里闷头去重否则结果很可能和业务实际不符。4.2 数据类型不一致导致比对失败很多时候两个表里的同一个字段一个是文本一个是数字。比如emp_id在旧表里是字符串“1001”在新表里是整数1001。pandas的merge默认对类型敏感类型不同就匹配不上于是一个实际在职的人员被误判成了离职和新增。我遇到最典型的就是身份证号和银行卡号。这些长数字在Excel里如果被设置成文本格式读取后就是字符串如果没设置Excel会自动转成科学计数法。就算你打开Excel看着是“110101199001011234”实际上单元格里存的可能是浮点数。我在比对前统一用astype(str)把主键列、身份证号列全部转成字符串再清洗一遍空格和全角字符就再也没出过这种问题。手机号也有类似的坑。有的人在表里填了“138-1234-5678”有的人填了“13812345678”。这些肉眼很容易识别但程序觉得完全不同。我的方案是统一去除非数字字符把手机号标准化成纯数字import re def normalize_phone(phone): if pd.isna(phone): return phone return re.sub(r\D, , str(phone)) df_old[phone] df_old[phone].map(normalize_phone) df_new[phone] df_new[phone].map(normalize_phone)这样做过之后手机号比对基本不会因格式问题误报。4.3 身份证号被Excel自动转成科学计数法这个坑极其常见而且很多人是在比对结果出来之后才发现“怎么所有人身份证都不一样”。我强烈建议在读取Excel时就对所有列统一指定dtypestr。但有些文件是别人发来的里面的身份证号已经被Excel转换过了物理存储的数值本身可能已经失真这就很难完全修复。如果你发现读取后身份证号变成了类似“1.101011e17”的值要在清洗阶段先恢复格式。一个可行的处理方式是def fix_id_card(val): if pd.isna(val): return val s str(val) if e in s.lower(): # 尝试按科学计数法还原但可能丢失末尾位数 return s if . in s: # 去掉小数点如果末尾是0也要处理 return s.split(.)[0] return s诚实地说如果Excel已经破坏了原始号码程序没办法百分之百恢复。这就是为什么我一直强调在读取阶段就要用dtypestr把长数字当文本读进来不要在Excel里直接处理要比对的原始表。另外如果你自己有权限操纵生成Excel的系统尽量导出CSV或者设置列为文本格式能避免很多麻烦。4.4 大表比对的性能建议如果你的员工表有几千行甚至上万行pandas的merge操作依然没问题但用iterrows逐行去比较字段时就会很慢。我试过上万人、比对5个字段iterrows在那个例子里跑了接近一分钟虽然结果能出来但体验太差。后来我改成向量化操作效率立竿见影。向量化比较的核心思路是不再逐行循环而是直接对整列做比较。比如要找出手机号有变化的人用一行代码phone_changed both[phone_old] ! both[phone_new]这样得到的是一个布尔Series把所有变更条件组合起来一次性筛选出信息有变化的行any_change both[phone_old] ! both[phone_new] for col in [name, department, id_card]: any_change any_change | (both[f{col}_old] ! both[f{col}_new]) changed_rows both[any_change]如果你想知道具体哪些字段变了还是需要逐字段展示。这时候我建议先把变化行筛选出来再对变化行逐列生成明细。因为绝大多数员工可能没变化先筛掉没变化的行剩下要处理的数量就少多了速度自然快。如果数据量特别大比如几十万行还可以用分块读取或者合并时先把主键排序再用merge。但员工表通常也就几千到几万行向量化优化后基本上都是秒出结果。5. 一些我自己常留的后手5.1 把比对结果生成快照备份处理人员数据这种敏感信息我每次跑完脚本都会把比对结果再压缩备份一份。因为很多业务部门隔一段时间就会回来问“上个月那个比对结果里张三的身份证是多少来着”这时候如果你重新跑脚本源数据可能已经变了结果对不上。养成保存快照的习惯能少很多麻烦。我的做法很简单在导出Excel时同时加一个带日期后缀的文件夹把源表、脚本、结果文件都放进去import datetime today datetime.date.today().strftime(%Y%m%d) output_dir f员工比对_{today}这样每个月的比对记录都留底以后回溯很轻松。5.2 脚本参数化方便别人使用如果你的同事也想用这个脚本但他们完全不会改代码那最好把输入文件名、主键列、需要比较的列都提取成脚本开头的变量。我在代码最顶上写清楚OLD_FILE 上月员工表.xlsx NEW_FILE 本月员工表.xlsx KEY_COL emp_id COMPARE_COLS [name, department, phone, id_card]这样其他人的操作就变成了改文件路径、改几个列名、运行脚本、拿结果。不需要理解merge和indicator的原理也能用。我把这份脚本给过不少行政和HR同事他们反馈最多的就是“能跑就行比让我看代码强多了”。5.3 建议做一次抽样验证最后再分享一个我自己的习惯不管脚本跑了多少次每次拿到结果我都不会直接发出去而是随手抽几个人用最原始的方式在Excel里查一下。不是不相信代码而是怕源数据里有我们没见过的脏数据。比如某个人在旧表里工号是“1001”新表里被敲成了“1001 ”多一个空格清洗函数应该能处理但万一某个特殊字符没覆盖到抽查一下就能发现。我一直觉得Python可以让重复劳动变得很轻松但它不是银弹。数据质量决定了比对结果的上限脚本能帮你快速暴露问题却替不了你做业务判断。把清洗、比对、输出、备份这套流程固定下来再配合人工抽查员工比对这件事就能又快又稳地拿下。