11个行政文员和HR常用Excel公式,简单粗暴超好用!
- 2026-09-24 05:10:54
我是【桃大喵学习记】，欢迎大家关注哟~，每天为你分享职场办公软件使用技巧干货！
——首发于微信号：桃大喵学习记
今天跟大家分享的是几个行政文员和HR常用的Excel公式，简单又实用，全是亲测好用至骨髓的硬核干货！
一、自动判断性别
如下图所示，需要根据身份证号自动判断性别。
公式：
=IF(MOD(MID(B2,15,3),2)=1,"男","女")
解读：
① 首先利用MID函数截取身份证第15-17位（15位身份证自动兼容）；
② 再用MOD函数对截取数÷2取余，判断奇偶；
③ 最后用IF函数判断奇数为"男"，偶数为"女"。
二、提取出生日期
如下图所示，需要根据身份证号码提取出生日期。
公式：
=TEXT(MID(B2,7,8),"0-00-00")
解读：
① 首先利用MID函数截取第7-14位（即出生日期数字）；
② 然后再用TEXT函数将结果转换为"0-00-00"格式，日期一目了然。
三、获取生肖信息
如下图所示，需要根据A列的身份证号码，在B列运用公式批量生成对应的生肖属相。
公式：
=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(B3,7,4),12)+1,1)
四、计算年龄
如下图所示，需要根据表格中A列提供的模拟身份证号码，在B列通过公式计算出每位人员对应的实际年龄。
公式：
=DATEDIF(TEXT(MID(B3,7,8),"00-00-00"),TODAY(),"Y")
解读：
① 先使用TEXT函数与MID函数嵌套组合，从身份证号码中提取出生日期；
② 再运用DATEDIF函数，计算该出生日期与当前日期的年份间隔，从而得出年龄。
五、计算工龄（精确到年）
如下图所示，需要根据表格中的入职时间，在相应列通过公式计算出每位员工的工龄，结果以年为单位显示。
公式：
=DATEDIF(C2,TODAY(),"Y")
解读：
使用DATEDIF函数，以入职时间为起始日期，以今天（当前系统日期）为结束日期，计算两者之间的整年间隔数。
六、入职时间（精确到年/月）
在实际工作中，我们常需根据入职时间计算年假天数或N+1离职补偿，此时工龄的计算精度要求较高，需精确到年与月。
公式：
=DATEDIF(C2,TODAY(),"Y")&"年"&DATEDIF(C2,TODAY(),"YM")&"个月"
解读：
利用DATEDIF函数，根据入职日期计算截至当前的实际在职时间，精确到年月。
具体操作为：先用参数"Y"计算出已满的整年数，再用参数"YM"计算出剔除整年后剩余的月份数，最后通过连接符将两部分拼接为“X年X个月”的格式。
七、身份证号码查重
如下图，表格中的模拟身份证号存在重复数据，我们需要判断哪些数据是重复的。
在目标单元格输入公式：
=IF(COUNTIF(B:B,B2&"*")>1,"重复","")
然后点击回车，下拉填充即可
解读：
① COUNTIF(B:B, B2&"*") —— 统计B列中与当前身份证相同的个数（&"*"是为了强制Excel按文本匹配，避免科学计数法导致的精度问题）；
② >1 —— 判断个数是否大于1；
③ IF —— 大于1则标记"重复"，否则留空。
八、生日提示
如下图所示，根据B列身份证号码计算距离生日的时间
公式：
=TEXT(TEXT(MID(B2,11,4),"00-00")-TEXT(NOW(),"m-d"),"还有0天生日;生日已过;今天生日")
九、迟到早退判断
如下图所示，需要根据上下班打卡时间判断迟到/早退状态：
上班打卡 > 9:00 为迟到，下班打卡 < 18:00 为早退。
公式：
=IF(AND(C2>TIME(9,0,0),D2<TIME(18,0,0)),"迟到+早退",IF(C2>TIME(9,0,0),"迟到",IF(D2<TIME(18,0,0),"早退","正常")))
十、计算员工转正日期
如下图所示，根据入职时间及转正月数，快速计算出每位员工的转正日期。
公式：
=EDATE(B3,C3)
十一、项目/任务/合同到期提醒
如下图所示，以下面项目进度表为例。根据项目进度管理规则，系统将自动识别“计划完成日”已逾期的项目。一旦该日期晚于系统当前日期，对应任务行的整行单元格将自动填充为黄色高亮，以作醒目警示提醒。
方法：
第一步：首先选中A:C列数据区域→点击【开始】选项卡-【条件格式】-【新建规则】调出新建格式规则”对话框
第二步：在弹出的“新建格式规则”对话框中，规则类型选择【使用公式确定要设置格式的单元格】，在设置格式里面输入公式：
=AND($C1<>"",$C1<TODAY())
接着点击【格式】，在弹出的对话框中选择“图案”，选择黄色，点击确定即可，如下图所示
解读：
需要特别提醒的是公式需要对C1单元格进行锁列不锁行，就是只根据C列数据数据判断。
亲爱的小伙伴们：
如果你正在为复杂繁琐的WPS表格/Excel操作困扰，希望通过掌握实用技能显著提升工作效率、减少无效加班——你可以考虑下我的WPS表格/Excel系列课程。
以上就是【桃大喵学习记】今天的干货分享~觉得内容对你有所帮助，别忘了动动手指点个赞哦~。大家有什么问题欢迎关注留言，期待与你的每一次互动，让我们共同成长！
