打工人必藏|10个职场高频Excel公式,让你效率直接拉满!

📇 从身份证号提取出生日期

=--TEXT(MID(A2,7,8),"0-00-00")

1. 选中目标单元格（如 B2），输入公式

2. 下拉填充，所有身份证的出生日期将自动提取

• MID(A2,7,8)：从身份证号第 7 位开始，提取 8 位出生日期数字

• TEXT(..., "0-00-00")：将数字格式转为 “年 - 月 - 日” 文本

• --：将文本格式的日期转为可计算的常规格式

🎂 从身份证号计算当前年龄

=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(),"y")

1. 选中目标单元格（如 B2），输入公式

2. 下拉填充，年龄将随当前日期自动更新

• 先通过TEXT(MID(...))提取出生日期

• DATEDIF(..., TODAY(), "y")：计算出生日期与今天的年份差

• TODAY()：自动获取当前日期，确保年龄实时更新

👫 从身份证号判断性别

批量整理员工性别信息，避免手动录入错误

=IF(MOD(MID(A2,17,1),2)=1,"男","女")

1. 选中目标单元格（如 B2），输入公式

2. 下拉填充，自动判断所有身份证对应的性别

• MID(A2,17,1)：提取身份证第 17 位（性别码）

• MOD(...,2)：判断该数字的奇偶性（奇数为男，偶数为女）

• IF(...)：根据判断结果返回 “男” 或 “女”

🔍 多条件精准查询

根据 “姓名 + 部门” 双条件，快速定位员工考核得分

=LOOKUP(1,0/((A2:A5=E2)*(B2:B5=F2)),C2:C5)

1. 在目标单元格输入公式，替换条件区域和结果区域

2. 按回车即可返回符合双条件的结果

• (A2:A5=E2)*(B2:B5=F2)：将两个条件的逻辑值相乘，只有同时满足时结果为 1

• 0/...：将 1 转为 0，其他值转为错误值

• LOOKUP(1,0/..., C2:C5)：精准定位结果并返回

🔎 关键字模糊查找

通过书籍简称，快速匹配完整书名

=VLOOKUP("*"&D2&"*",$A$1:$B$5,1,FALSE)

1. 在目标单元格输入公式，替换查找值和数据区域

2. 按回车即可返回包含关键字的结果

• "*"&D2&"*"：通配符*表示任意字符，实现模糊匹配

• VLOOKUP(..., FALSE)：精准匹配包含关键字的内容

🔄 反向查找（左查右）

通过工号快速匹配员工姓名（工号在右侧，姓名在左侧）

=INDEX(A1:A4,MATCH(E2,B1:B4,0))

1. 在目标单元格输入公式，替换结果列、查找值和查找区域

2. 按回车即可返回左侧的姓名

• MATCH(E2,B1:B4,0)：找到工号在 B 列的位置

• INDEX(A1:A4,...)：根据位置返回 A 列对应的姓名

🔍 标记重复数据

快速识别员工编号、订单号中的重复值

=IF(COUNTIF($A$2:$A$10,A2)=1,"","是")

1. 选中目标单元格（如 B2），输入公式

2. 下拉填充，重复值将标记为 “是”，唯一值为空

• COUNTIF($A$2:$A$7,A2)：统计当前值在区域内的出现次数

• IF(...)：次数为 1 则为空，否则标记为 “是”

🔢 统计不重复数据个数

统计员工编号、客户 ID 的唯一数量

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

1. 选中目标单元格，输入公式并替换数据区域

2. 按回车即可得到唯一值的数量

• COUNTIF(A2:A10,A2:A10)：计算每个值的出现次数

• 1/...：将次数转为倒数（如重复 3 次则得到 3 个 1/3）

• SUMPRODUCT(...)：求和后得到唯一值的数量

👥 统计逗号分隔的人数

快速统计每个部门的员工人数（姓名用逗号分隔）

=LEN(B2)-LEN(SUBSTITUTE(B2,"，",""))+1

1. 选中目标单元格（如 C2），输入公式

2. 下拉填充，自动计算每个单元格的人数

• LEN(B2)：计算单元格总字符数

• LEN(SUBSTITUTE(B2,"，",""))：删除逗号后计算字符数

• 两者相减得到逗号数量，加 1 即为人数

✂️ 拆分姓名与电话

批量拆分 “姓名 + 电话” 合并的单元格

=LEFT(A2,LENB(A2)-LEN(A2))

=RIGHT(A2,LEN(A2)-(LENB(A2)-LEN(A2)))

1. 在目标单元格输入对应公式

2. 下拉填充，自动拆分所有姓名和电话

• LENB(A2)-LEN(A2)：计算汉字的个数（中文占 2 字节，数字占 1 字节）

• LEFT(...)：提取左侧的姓名，RIGHT(...)：提取右侧的电话

- END -