人事花名册常用Excel函数,整理员工信息可以直接参考

身份证号码里面本身就包含完整出生年月日信息，完全不需要手动抄写，利用MID函数直接截取对应字符。

公式：=MID(D2,7,8)

参数解读：

D2是身份证号码单元格；

MID(D2,7,8)：从身份证第7位开始，向后提取8位字符，输出格式为YYYYMMDD，例如19900520。

很多人在这里会遇到一个小困惑，输出的是一串8位数字，并不是我们习惯看到的1990-05-20日期样式。这里并不是公式出错，只是输出的是文本字符串。

👉格式转换小技巧：

方法1：拿到8位数字结果之后，选中该列，自定义单元格格式，调整为0000-00-00；

方法2：搭配TEXT函数实现一步到位：=TEXT(--MID(D2,7,8),"0000-00-00")，直接输出横杠分隔的标准出生日期。

拿到身份证，自动计算员工当前周岁，表格打开就自动更新，不需要每过一年手动修改一遍表格里面的年龄。

公式：=YEAR(TODAY())-MID(D2,7,4)

参数解读：

TODAY()函数：自动读取电脑系统的当前日期，表格每次打开都会自动刷新；

MID(D2,7,4)，提取身份证当中4位出生年份；

YEAR函数提取系统当前的年份，两个年份做减法，得到年龄。

💡客观说明：

这个公式计算的是年份差值，操作简单，适合人事做大批量台账快速统计。但有一个小局限，如果今年生日还没有过，该公式不会自动减一岁。

举个例子：1996-11-20出生，当前时间2026-09，还没过生日，实际周岁29，公式会算出30。

如果业务场景需要严格计算真实周岁，这里给大家补充进阶精确公式：=DATEDIF(TEXT(MID(D2,7,8),"0-00-00"),TODAY(),"Y")

这个公式会完整对比月和日，生日未到会自动扣减一岁，统计结果更加精准。人事可以根据自己台账的需求选择公式。

部分企业员工档案会需要登记生肖信息，手动去对照万年历查找效率很低，一个公式就可以批量生成。

公式：=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(D2,7,4),12)+1,1)

原理讲解：

MID(D2,7,4)取出出生年份；

MOD对12做取余数运算；

根据余数，从预先写好的生肖字符串当中取出对应的汉字。

粘贴公式回车之后下拉填充，所有人员生肖直接生成，不用额外查表。

工龄统计是人事工作当中的刚需，不管是核算年假、工龄工资，还是员工档案统计，都需要用到工龄。DATEDIF是专门用来计算两个日期间隔的函数，可以自动统计整年工龄，表格打开自动更新。

公式：=DATEDIF(H2,TODAY(),"Y")

参数解读：

H2=入职时间所在单元格；

TODAY()代表系统今日日期；

"Y"代表只统计完整的整年数。

举个例子：员工入职日期2018-05-10，到2026-09，满足完整8个整年，公式输出结果8。

⚠️重要注意点：入职时间单元格，必须是Excel标准日期格式。如果只是手动输入的文本文字，例如2018.5.10，DATEDIF函数会直接报错#VALUE!。

👉小拓展：如果需要统计精确到月份的司龄（多少年多少月），可以使用组合公式：=DATEDIF(H2,TODAY(),"Y")&"年"&DATEDIF(H2,TODAY(),"YM")&"个月"

很多公司会设置工龄工资作为员工福利，比较常见的规则为每工作满1年，每月增加50元工龄工资。当表格已经自动算出工龄之后，工龄工资就可以一键批量运算。

示例规则：每满1年，每月工龄工资增加50元。

基础公式：=I2*50

I2代表存放整年工龄的单元格。

举例：工龄7年，7*50=350元，即每月工龄工资350元。

现实当中很多企业并不是简单的线性增长，会使用阶梯式工龄工资。这里给大家一个拓展参考案例：

规则示例：13年每年50；46年每年80；7年及以上每年100。

嵌套IF阶梯公式：=IF(I2<=3,I2*50,IF(I2<=6,3*50+(I2-3)*80,3*50+3*80+(I2-6)*100))

直接复制，根据自家企业的薪资规则修改数字就可以直接使用。

合同管理台账是人事风险管控的重点工作。如果全部靠手动加减年份计算到期日，一旦出现计算失误，会造成合同漏续签，带来用工风险。我们可以输入入职时间、合同年限，利用DATE函数自动生成合同到期时间。

公式：=DATE(YEAR(H2)+J2,MONTH(H2),DAY(H2)-1)

参数解读：

H2：入职时间单元格；

J2：合同期限（年，例如3、5、10）；

DATE函数重新组装年月日；DAY(H2)-1，代表到期前一日，这也是绝大多数企业劳动合同台账的记录习惯。

举个实操例子：入职2015-06-27，合同期限10年，公式计算输出到期日2025-06-26。

💡进阶实用技巧：合同到期自动高亮提醒。

可以搭配条件格式，实现距离到期3个月的合同自动标红预警。

操作路径：选中合同到期日整列 →【开始】→【条件格式】→【新建规则】→【使用公式确定要设置格式的单元格】

输入公式：=K2<TODAY()+90，设置填充红色，确认。之后所有距离到期不足90天的合同会自动变红，提醒人事及时处理续签。

很多朋友复制完网上的公式之后，频繁出现报错，大部分问题并不是公式本身错误，而是表格基础格式设置不对，这里汇总人事做表格最高频的踩坑点：

1、身份证号码单元格，一定要提前设置为【文本格式】

Excel数字格式下，超过15位的长数字，末尾数字会被强制变成0，身份证直接损坏。

正确操作：选中整列，右键设置单元格格式为文本，之后再粘贴身份证号码。如果已经粘贴完身份证再改格式，已经损坏的号码不会自动恢复。

2、日期类单元格必须是真正的日期格式

入职时间、出生日期，不能使用2020.01.01这种文本写法。DATEDIF、DATE类函数无法识别文本型日期。推荐使用2020-01-01或者2020/1/1标准日期录入。

3、复制公式后，务必修改单元格坐标

文中D2、H2、I2都是示例表格位置。如果你表格当中身份证写在B列，就要把公式内全部D2替换为B2，很多人直接照搬公式不修改行列，导致计算全部出错。

4、常见报错简单排查

• #VALUE!：单元格格式不对，存在文本日期、文本数字；

• #NUM!：DATEDIF函数的起始日期大于结束日期（入职时间晚于今日）；

遇到报错优先检查对应单元格内容，而不是反复修改公式。

5、TODAY()函数会动态更新

包含TODAY()的公式，每次打开表格，都会跟随电脑系统日期刷新工龄、年龄。如果需要把统计结果固定住，不要持续变动，可以选中结果区域，复制，右键选择性粘贴为【数值】，公式就转化为静态数字。

看完所有函数，很多人还是不知道怎么把它们组合起来，这里给一套完整搭建流程，零基础也可以照着操作：

1.整理基础表头：序号、姓名、身份证号码、性别、出生日期、年龄、生肖、入职时间、工龄（年）、合同期限（年）、合同到期日、工龄工资；

2.选中身份证整列，右键设置单元格格式为【文本】，粘贴全部员工身份证信息；

3.依次复制7套函数，粘贴到对应字段第2行单元格；

4.根据自己表格实际列位置，修改公式内部单元格坐标；

5.在得到正确结果的单元格右下角，鼠标放置，等黑色十字填充柄出现，向下拖动，整列批量运算；

6.按需设置条件格式，增加合同到期标红提醒；

7.完成表格搭建。

对于人事岗位来说，Excel函数不是用来炫技的工具，而是实实在在提升工作效率，降低人为错误的武器。

性别、出生年月、年龄、生肖、工龄、工龄工资、合同到期日，这些人事每天都要处理的数据，不需要手工一遍一遍抄写计算。掌握这7套现成公式，直接复制修改坐标即可使用。

人事岗位的工作量本来就很饱和，我们完全不应该把大量时间消耗在复制粘贴、手工算数这种机械重复工作上面。用好表格工具，把时间留给招聘沟通、员工关系处理、制度优化这类更有价值的核心工作。

不需要死记硬背复杂函数语法，把这篇文章收藏，每次整理员工台账的时候打开对照复制公式就足够。

你在做人事台账的时候，还有哪些重复繁琐的表格工作？使用Excel公式的时候踩过哪些奇葩的坑？

欢迎评论区留言，一起交流办公小技巧。