超实用Excel函数你知多少身份证号提取性别生日，手机号码自

　　刚入职场的小伙伴，很多时候都要面临业务与环境的双重考验。如何在众人间脱颖而出，成为夜空中最亮的那个仔？今天这篇文章，将带您快速一览人事岗必学的几招Excel函数。难度不高，却超级超级实用！1。身份证号提取性别
　　公式：IF（MOD（MID（B2，17，1），2）1，男，女）
　　首先通过MID（）函数提取身份证号中的第17位，然后通过MOD（）函数判断奇偶性，其中奇数为男，偶数为女。最终在IF（）函数的配合下，即可得到性别值。
　　身份证号自动生成性别2。身份证号提取生日
　　公式：TEXT（MID（B2，7，8），00000000）
　　首先通过MID（）函数提取身份证号中的第714位，然后使用TEXT（）函数对结果格式化（即转换成xxxxxxxx的样式），最终便得到了我们平时习惯的生日信息。
　　身份证号自动提取生日
　　注：身份证号码中第714位为出生日期。3。计算年龄、司龄
　　公式：YEAR（TODAY（））MID（B2，7，4）
　　首先通过TODAY（）函数获得当前日期，然后使用YEAR（）函数提取日期的年份值。接下来通过MID（）函数获取员工出生年份，两者之差即为年龄。类似的方法也可用在司龄计算上，只要将生日替换成入职日期即可。
　　快速计算年龄、司龄4。防止身份证号多输少输
　　做法：通过数据有效性限定单元格长度
　　选中身份证号列，点击数据数据有效性，将文本长度指定为18位。当录入的身份证号码超过或不足18位时，Excel便会自动提醒，以降低前端录入可能导致的错误。
　　通过数据有效性限定身份证列位数
　　注：通过将数据有效性出错警告中的样式修改为警告，可以实现只提醒不禁止效果。5。手机号码自动加
　　做法：通过单元格格式，修改显示格式
　　右击手机号码字段，按下Ctrl1进入单元格格式面板，点击最下方自定义，输入00000000000。确定之后，手机号码即以XXXXXXXXXXX格式显示。
　　手机号码自动加
　　注：这种方法只是修改了字段显示样式，并没有修改实际数据，因此后续仍然可以进行统计，这也是和Replace（）函数最大一个区别。6。合同到期前自动提醒
　　公式：IF（G7TODAY（）lt；30，合同应续签，）
　　首先建立状态列，输入公式IF（G7TODAY（）lt；30，合同应续签，）。当合同到期日低于设定值（本例中为30）时，便自动显示合同应续签。此外也可将提醒天数单独设成单元格，通过公式调取单元格数值，以方便用户自行修改提醒期限。
　　合同到期前自动提醒7。避免录入重复值
　　公式：COUNTIF（B：B，B1）1
　　选中数据列，点击数据数据工具数据验证，将验证条件改为自定义，然后输入公式COUNTIF（B：B，B1）1。这里COUNTIF（）函数的作用，是判断B列中是否有重复记录（如果有则值值一定大于1），如果有中止录入，如果没有继续录入。
　　如果录入时出现重复数值将禁止录入8。自动标亮周末
　　公式：WEEKDAY（E6，2）gt；5
　　首先建立星期行，选中考勤表里的数值区域，点击开始样式条件格式新建规则，然后在选框内输入公式WEEKDAY（E6，2）gt；5，最后选中E6，按两下F4键锁定行不锁定列（即转换成E6），再设置一个特别的颜色即可。这里WEEKDAY（）函数的作用是判断当前列是否大于5，当WEEKDAY（）结果gt；5时（即周六、周日），条件成立，表格自动为该列刷上颜色，不成立时不处理，从而最终形成周末自动上色效果。
　　自动标亮周末9。让表格自动拓展
　　方法：CtrlT转成超级表
　　想让表格自动扩展，可以利用Excel里的超级表功能。具体方法是：选中数据区域，按下CtrlT，然后修改表格工具表格样式为无（即不使用超级表默认样式）。右击隐藏新弹出的筛选行，即可实现表格的自动扩展。
　　CtrlT实现表格自动拓展10。隔行换色
　　公式：MOD（ROW（），2）0
　　隔行换色有两种方法，一是转换成超级表（CtrlT），二是借助公式与条件格式配合完成。以公式法为例，首先选中数据区域，点击开始样式条件格式新建规格使用公式确定要设置格式的单元格，然后在选框内输入公式MOD（ROW（），2）0。这里ROW（）函数的作用是获取当前行号，和2取余后，便得到了1、0、1、0、1、0这样的数列。由于公式整体位于条件格式内，因此当行号取余结果0时，条件成立，表格自动为该行刷上颜色，不成立时不处理，最终形成了隔行换色的效果。
　　公式条件格式实现隔行换色
　　注：与传统的格式刷法相比，超级表和公式法都可以实现周末自动填色。且后期无论如何添删，都不会导致色条出现混乱。11。自动标记迟到、早退
　　公式：IF（B2gt；TIME（8，0，0），迟到，）和IF（C2lt；time（18，0，0），早退，）lt；pgt；
　　首先在表格后建立一个迟到列，输入公式IF（B2gt；TIME（8，0，0），迟到，），再建立一个早退列，输入公式IF（C2lt；time（18，0，0），早退，）。这里简单说明一下，time（）函数主要用于约束最终时间，当上班签到时间晚于约束值，或者下班签到时间早于约束值时，便会触发文字提示。lt；pgt；
　　自动标记迟到、早退12。自动打序号
　　公式：ROW（）1
　　首先点击序号列第一组单元格，输入公式ROW（），此时函数返回的是当前行数，根据实际行数计算（比如本文为1），发现两者差值为1。接下来对公式进行修改，将计算后的差值减到公式后方（），填充后即可实现自动打序号效果。
　　尽管自动打序号已经实现，但此时仍然不够智能。可以在此基础上再嵌套一个IF（）函数，只有当右侧有数据时才会显示序号，没有的话直接显示空白。
　　自动序列13。去除数据有效性列表里的空值
　　公式：OFFSET（O6，，，COUNTA（O6：O19））
　　如果你经常使用数据有效性制作下拉列表，就会发现这样一个尴尬，当我们前期为序列留出大量空白时，下拉列表也会出现大量空白，日常操作很不方便。其实这个问题可以这样解决，首先点击数据数据验证序列，在来源框中填入公式。这里COUNTA（）函数的作用，是求出当前数据源中的有效记录数，然后通过OFFSET（）函数确定取值范围。由于公式限定了下拉列表的取值域，因此我们会得到一个完全没有空值的菜单。同时新记录也将自动添加到列表中，不会影响后续操作。
　　清除下拉列表里的空值14。分级考核统计
　　公式：LOOKUP（J6，N6：N9，M6：M9）
　　传统分级统计是通过COUNT（）函数与IF（）函数配合计算，虽然简单，可一旦条件过多，就会导致公式异常复杂。类似情况，其实也能借助LOOKUP（）函数解决。
　　首先建立一个分级副表，左侧为等级，右侧为达到该等级的考核线。接下来在评级框内输入公式LOOKUP（J6，N6：N9，M6：M9），将两组取值域按F4键全部转为绝对地址。这里LOOKUP（）的作用是通过目标值，直接到副表中查找对应等级，相比IF（）函数显得精简很多。而且这样处理后的表格，也方便用户随时调整考核线。
　　简单的分级考核公式
　　注：使用LOOKUP（）函数时，要注意副表考核值必须由小到大排列，否则将导致结果异常。写在最后
　　以上这些，是人事工作者每天都要遇到的，很多复杂的操作其实往往来源于日常积累。