技术博客

超实用 Excel 函数你知多少：身份证号提取性别 / 生日，手机号码自动加“-”

admin2021-05-10 05:30 883人已围观

简介超实用 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 ()

首先建立“状态”列，输入公式“=IF (G7-TODAY ()

▲合同到期前自动提醒

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),"迟到","")”，再建立一个“早退”列，输入公式“=IF (C2

▲自动标记迟到、早退

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)”，将两组取值域按 F4 键全部转为绝对地址。这里 LOOKUP () 的作用是通过目标值，直接到副表中查找对应等级，相比 IF () 函数显得精简很多。而且这样处理后的表格，也方便用户随时调整考核线。

▲简单的分级考核公式

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

写在最后

以上这些，是人事工作者每天都要遇到的，很多复杂的操作其实往往来源于日常积累。

  • 微信公众号

很赞哦！(0)

上一篇：Wine 6.8 版本发布：已支持 JavaScript 中的 Map 对象

下一篇：大幅提高日常工作效率，Word 制表小技巧

相关文章

文章评论


评论0

    站点信息

    • 微信公众号：扫描二维码，关注我们

    点击排行

    三种 PS 的套索工具使用小技巧

    本栏推荐

    信达证券：化工行业周报：三氯蔗糖、安赛蜜等价格上扬

    标签云

    猜你喜欢