老板分分钟主动涨工资！超实用的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()提取身份证号中的身份证号function取第7位到第14位，然后用TEXT()函数对结果进行格式化（即转换成“xxxx-xx-xx”的样式），最后得到我们习惯的生日信息。身份证号自动提取生日注：身份证号第7-14位为出生日期。3.计算年龄 公式：=YEAR(TODAY())-MID(B2,7,4)先通过TODAY()函数获取当前日期，再用YEAR()函数提取日期的年份值。接下来使用MID()函数获取员工的出生年份，两者之差就是年龄。类似的方法也可以用在资历的计算上，只要将生日换成入职日期即可。快速计算年龄和资历4.防止身份证号输入多输入少 方法：通过“数据有效性”限制单元格长度选择身份证号列，点击“数据”→“数据有效性”，设置“文本”长度”被指定为“18”位。当输入的身份证号码超过或少于18位时，Excel会自动提醒，减少前端录入可能造成的错误。通过数据有效性限制身份证列数注意：通过将“数据有效性”→“错误警告”的样式改为“警告”，可以达到只提醒不禁止的效果。5、手机号自动加“-” 方法：通过“单元格格式”修改显示格式，右击手机号栏，按Ctrl+1进入单元格格式面板，点击在“自定义”下方，输入“000-0000-0000”。确认后，手机号码以“XXX-XXXX-XXXX”格式显示。手机号自动加上“-”注意：该方法只是修改了字段显示样式，并没有修改实际数据，所以后续统计还是可以的，这也是与Replace()函数最大的区别.6.合约到期前自动提醒 公式：=IF(G7-TODAY()<30,"Contractshouldberenewed","")首先创建“状态”栏，输入公式“=IF(G7-TODAY()<30,"应续签合同","")"。当合约到期日低于设定值（本例为“30”）时，会自动显示“Contractshouldberenewed”。此外，还可以将提醒天数设置为单独的单元格，通过公式检索单元格的值，方便用户自行修改提醒期限。合约到期前自动提醒7.避免输入重复值 公式：=COUNTIF(B:B,B1)=1选择数据栏，点击“数据”→“数据工具”→“数据验证”即可验证条件更改为自定义，然后输入公式“=COUNTIF(B:B,B1)=1”。COUNTIF()函数在这里的作用是判断B列是否有重复记录（如果有，值必须大于1），如果有停止条目，如果没有连续条目。8、自动高亮周末 公式：=WEEKDAY(E$6,2)>5首先创建周行，在考勤表中选中数值区域，点击“开始”→“样式”→“条件格式”→“新建规则”，然后在选择框中输入公式“=WEEKDAY(E6,2)>5”，最后选择E6，按两次F4锁行不锁列（即转换为E$6),然后设置一个特殊的颜色。WEEKDAY()函数在这里的作用是判断当前列是否大于5，当WEEKDAY()结果大于5时（即星期六和星期日），条件为真，自动建表为列着色。如果不为真则不处理，这样最后就形成了周末自动上色效果。周末自动高亮9.让表格自动展开 方法：Ctrl+T变成“超级表格”如果想让表格自动展开，可以使用Excel中的“超级表格”功能。具体方法是：选中数据区，按Ctrl+T，然后将“表格工具”→“表格样式”修改为“无”（即不使用超级表格的默认样式）。右键隐藏新弹出的筛选行，实现表格的自动展开。Ctrl+T实现表格自动扩容10.隔行换色 公式：=MOD(ROW(),2)=0隔行换色有两种方法，一种是转换为“超级表格”(Ctrl+T),二是借助公式和条件格式来完成。以公式法为例，首先选中数据区，点击“开始”→“样式”→“条件格式”→“新建规范”→“使用公式确定要设置格式的单元格”，然后输入选择框中的公式“=MOD(ROW(),2)=0”。这里ROW()函数的作用是获取当前行号，2减去余数后得到1,0,1,0,1,0....等一串数字。公式整体为条件格式，当行号余数的结果=0时，条件为真，表格自动为该行涂色，不为真则不处理，最终形成每行颜色变化的效果。公式+条件格式实现“隔行换色”注：相对于传统的格式刷法，超表和公式法都可以实现周末自动上色。并且无论你以后如何添加或删除，都不会造成颜色条的混乱。11、自动标记迟到早退 公式：=IF(B2>TIME(8,0,0),"迟到","")和=IF(C2先在表格后创建一个“迟到”列，输入公式"=IF(B2>TIME(8,0,0),"Late","")"，然后创建一个"早退"列，输入公式"=IF(C2自动标记迟到并离开早12.自动打印序号 公式：=ROW()-1首先点击序号列的第一组单元格，输入公式=ROW()，此时函数返回当前行数，按实际行数计算（例如本文中的“1”），发现两者相差“1”，接下来修改公式，将计算出的差值减小到公式()的后面，填完之后，就可以实现自动序号的效果了，虽然已经实现了自动序号，但是这时候还是不够聪明，可以再嵌套一个IF()在此基础上发挥作用，nd只有右边有数据的时候才会显示序号，没有数据的时候会显示空白。自动排序13.去掉数据有效性列表中的空格Value 公式：=OFFSET($O$6,,,COUNTA($O$6:$O$19))如果经常用“数据有效性”做drop-向下列表，你会发现这样的尴尬。当序列中有大量空白时，下拉列表中也会出现大量空白，对于日常操作来说非常不方便。其实这个问题可以这样解决。首先，单击“数据”→“数据验证”→“序列”，并填写“来源”框。输入公式。COUNTA()函数在这里的作用是查找当前数据源中的有效记录数，然后通过OFFSET()函数确定取值范围。由于公式限制了下拉列表的取值范围，所以我们会得到一个根本没有空值的菜单。同时，新记录也会自动添加到列表中，不影响后续操作。清空下拉列表中的空值14.评分统计 公式：=LOOKUP(J6,N6:N9,M6:M9)传统的评分统计是通过COUNT()函数和IF()函数计算的，虽然简单，但是一旦条件过多，公式就会异常复杂。类似的情况实际上可以借助LOOKUP()函数来解决。首先创建一个成绩分表，左边是成绩，右边是达到成绩的考核线。接下来，在评级框中输入公式“=LOOKUP(J6,N6:N9,M6:M9)”，按F4键，将两组值域转换为绝对地址。这里LOOKUP()的作用是直接通过目标值在子表中找到对应层级，比IF()函数简单多了。而且，这样处理的表格也方便用户随时调整考核线。简单分级考核公式注意：使用LOOKUP()函数时需要注意的是，分表的考核值必须按升序排列，否则结果会异常。写在最后 以上就是人事工作者每天都会遇到的，很多繁杂的操作往往都是从日常的积累中衍生出来的。希望对您的工作有所帮助！
