老板分分钟主动加薪！超实用Excel函数你知多少

老板分分钟主动加薪！超实用Excel函数你知多少

刚入职场的小伙伴，很多时候都要面临业务与环境的双重考验。如何在众人间脱颖而出，成为夜空中最亮的那个仔?今天这篇文章，将带您快速一览人事岗必学的几招Excel函数。难度不高，却超级超级实用!

1. 身份证号提取性别

公式：= IF(MOD(MID(B2,17,1),2)=1,"男","女")

首先通过MID()函数提取身份证号中的第17位，然后通过MOD()函数判断奇偶性，其中奇数为男，偶数为女。最终在IF()函数的配合下，即可得到性别值。

身份证号自动生成性别

2. 身份证号提取生日

公式：=TEXT(MID(B2,7,8),"0000-00-00")

首先通过MID()函数提取身份证号中的第7-14位，然后使用TEXT()函数对结果格式化(即转换成“xxxx-xx-xx”的样式)，最终便得到了我们平时习惯的生日信息。

身份证号自动提取生日

注：身份证号码中第7-14位为出生日期。

3. 计算年龄、司龄

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

首先通过TODAY()函数获得当前日期，然后使用YEAR()函数提取日期的年份值。接下来通过MID()函数获取员工出生年份，两者之差即为年龄。类似的方法也可用在司龄计算上，只要将生日替换成入职日期即可。

快速计算年龄、司龄

4. 防止身份证号多输少输

做法：通过“数据有效性”限定单元格长度

通过数据有效性限定身份证列位数

注：通过将“数据有效性”→“出错警告”中的样式修改为“警告”，可以实现只提醒不禁止效果。

5. 手机号码自动加“-”

做法：通过“单元格格式”，修改显示格式

手机号码自动加“-”

注：这种方法只是修改了字段显示样式，并没有修改实际数据，因此后续仍然可以进行统计，这也是和Replace()函数最大一个区别。

6. 合同到期前自动提醒

公式：=IF(G7-TODAY()<30,"合同应续签","")

首先建立“状态”列，输入公式“=IF(G7-TODAY()<30,"合同应续签","")”。当合同到期日低于设定值(本例中为“30”)时，便自动显示“合同应续签”。此外也可将提醒天数单独设成单元格，通过公式调取单元格数值，以方便用户自行修改提醒期限。

合同到期前自动提醒

7. 避免录入重复值

公式：=COUNTIF(B:B,B1)=1

如果录入时出现重复数值将禁止录入

8. 自动标亮周末

公式：=WEEKDAY(E$6,2)>5

自动标亮周末

9. 让表格自动拓展

方法：Ctrl+T转成“超级表”

想让表格自动扩展，可以利用Excel里的“超级表”功能。具体方法是：选中数据区域，按下Ctrl+T，然后修改“表格工具”→“表格样式”为“无”(即不使用超级表默认样式)。右击隐藏新弹出的筛选行，即可实现表格的自动扩展。

Ctrl+T实现表格自动拓展

10. 隔行换色

公式：=MOD(ROW(),2)=0

公式+条件格式实现“隔行换色”

注：与传统的格式刷法相比，超级表和公式法都可以实现周末自动填色。且后期无论如何添删，都不会导致色条出现混乱。

11. 自动标记迟到、早退

公式：=IF(B2>TIME(8,0,0),"迟到","") 和 =IF(C2

首先在表格后建立一个“迟到”列，输入公式“=IF(B2>TIME(8,0,0),"迟到","")”，再建立一个“早退”列，输入公式“=IF(C2

自动标记迟到、早退

12. 自动打序号

公式：=ROW()-1

尽管自动打序号已经实现，但此时仍然不够智能。可以在此基础上再嵌套一个IF()函数，只有当右侧有数据时才会显示序号，没有的话直接显示空白。

自动序列

13. 去除数据有效性列表里的空值

公式：=OFFSET($O$6,,,COUNTA($O$6:$O$19))

清除下拉列表里的空值

14. 分级考核统计

公式：=LOOKUP(J6,N6:N9,M6:M9)

传统分级统计是通过COUNT()函数与IF()函数配合计算，虽然简单，可一旦条件过多，就会导致公式异常复杂。类似情况，其实也能借助LOOKUP()函数解决。

首先建立一个分级副表，左侧为等级，右侧为达到该等级的考核线。接下来在评级框内输入公式“=LOOKUP(J6,N6:N9,M6:M9)”，将两组取值域按F4键全部转为绝对地址。这里LOOKUP()的作用是通过目标值，直接到副表中查找对应等级，相比IF()函数显得精简很多。而且这样处理后的表格，也方便用户随时调整考核线。

简单的分级考核公式

注：使用LOOKUP()函数时，要注意副表考核值必须由小到大排列，否则将导致结果异常。

写在最后

以上这些，是人事工作者每天都要遇到的，很多复杂的操作其实往往来源于日常积累。希望对您的工作有所帮助吧!