超实用 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. 避免身份证号多输少输

作法：根据“数据有效性”限制表格中长短

选定身份证号列，点一下“数据信息”→“数据有效性”，将“文字长短”特定为“18”位。当入录的身份证号码超出或不够 18 位时，Excel 便会全自动提示，以减少前面入录很有可能造成的不正确。

▲根据数据有效性限制身份证件列位数

注：根据将“数据有效性”→“打错警示”中的款式改动为“警示”，能够完成只提示不严禁实际效果。

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

作法：根据“表格中格式”，改动表明格式

右键手机号字段名，按住 Ctrl 1 进到表格中格式控制面板，点一下最下边“自定”，键入“000-0000-0000”。明确以后，手机号就是以“XXX-XXXX-XXXX”格式表明。

▲手机号全自动加“-”

注：这类方式 仅仅改动了字段名表明款式，并沒有改动具体数据信息，因而事后依然能够开展统计分析，这也是和 Replace () 函数较大一个差别。

6. 合同到期前全自动提示

公式计算：=IF (G7-TODAY ()<30,”合同书应继签”,””)

最先创建“情况”列，键入公式计算“=IF (G7-TODAY ()<30,”合同书应继签”,””)”。当合同到期日小于预设值（本例中为“30”）时，便全自动表明“合同书应继签”。除此之外也可将提示日数独立设成表格中，根据公式计算读取表格中标值，以便捷客户自主改动提示限期。

▲合同到期前全自动提示

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<time (18,0,0),” 旷班 “,””)< p=””>

最先在报表后创建一个“晚到”列，键入公式计算“=IF (B2>TIME (8,0,0),”晚到”,””)”，再创建一个“旷班”列，键入公式计算“=IF (C2<time (18,0,0),” 旷班 “,””)”。这儿简易表明一下，time () 函数关键用以管束最后時间，当工作每日签到時间晚于管束值，或是下班了每日签到時间早于管束值时，便会开启文本提醒。< p=””>

▲全自动标识晚到、旷班

12. 全自动打编号

公式计算：=ROW ()-1

最先点一下编号列第一组表格中，键入公式计算 = ROW ()，这时函数回到的是当今个数，依据具体个数测算（例如文中为“1”），发觉二者误差为“1”。下面对公式计算开展改动，将测算后的误差减到公式计算后才（），添充后就可以完成全自动打编号实际效果。

虽然全自动打编号早已完成，但这时依然不足智能化。能够在这个基础上再嵌入一个 IF () 函数，仅有当右边有数据信息时才会表明编号，沒有得话立即表明空缺。

▲全自动编码序列

13. 除去数据有效性目录里的空值

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

假如你常常应用“数据有效性”制做下拉框，便会发觉那样一个难堪，在我们早期为编码序列空出很多空缺时，下拉框也会发生很多空缺，日常实际操作很不方便。实际上这个问题能够那样处理，最先点一下“数据信息”→“数据验证”→“编码序列”，在“来源于”框中填写公式计算。这儿 COUNTA () 函数的功效，是算出当今数据库中的合理纪录数，随后根据 OFFSET () 函数明确取值范围。因为公式计算限制了下拉框的取值域，因而大家会获得一个彻底沒有空值的莱单。另外新纪录也将全自动加上到目录中，不容易危害事后实际操作。

▲消除下拉框里的空值

14. 分级考核统计分析

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

传统式分级统计分析是根据 COUNT () 函数与 IF () 函数相互配合测算，尽管简易，可一旦标准太多，就会造成公式出现异常繁杂。相近状况，实际上也可以依靠 LOOKUP () 函数处理。

最先创建一个分级副表，左边为级别，右边为做到该级别的考核线。下面在定级框中键入公式“=LOOKUP (J6,N6:N9,M6:M9)”，将2组取值域按 F4 键所有变为肯定详细地址。这儿 LOOKUP () 的功效是根据目标，立即到副表中搜索相匹配级别，对比 IF () 函数看起来精减许多。并且那样解决后的报表，也便捷客户随时随地调节考核线。

▲简易的分级考核公式

注：应用 LOOKUP () 函数时，要留意副表考核值务必从小到大排序，不然将造成結果出现异常。

写在最终

之上这种，是人事部门工作人员每日都需要碰到的，许多繁杂的实际操作实际上通常来自日常累积。