​工作案例

HR 新人小宇，要将一批新入职员工的信息录入Excel【职工档案表】中。

勤奋的小宇拿起入职登记表就开始干活：

第1条，姓名、性别、手机、学历、身份证、出生年月、生日、年龄...约两分钟录完了第1条，检查一下有没有错误，好，继续...

第2条，姓名...

第3条...

...

十余条信息录完，半个小时过去了，小宇终于舒了一口气〜

案例讨论

打开小宇的这张表格：

表格比较简单，信息都是档案中最基础的。表格总共有16个字段（16列），和身份证相关的字段有4-5个。小宇非常认真负责地输入了每一行信息。

毋庸置疑，身份证号是档案必需的。

我们都知道身份证号中包括「出生日期」。

那么是不是可以从身份证号中提取职工的出生日期，而无需录入？

提取到出生日期，我们不就知道了「生日」？

「年龄」是唯一一个需要稍做计算的数值。

甚至我们还可以提取员工的「户籍地」！

也就是说，小宇只要输入一次身份证号，「出生年月」、「生日」、「年龄」「户籍地」就都有了，一下子可以节省4个字段的录入时间啊， 也就是光这一项就省下4/16=1/4的录入时间哦！！

小宇得知还有这波操作，笑的可开心了〜

优化实战

讨论出了优化思路，那就开始干吧：

从上面的分析过程我们能看出：

提取身份证号中的出生日期是非常关键的一步。「生日」和「年龄」的计算都依赖于这一步。

身份证号属于「文本字符串」，字符串长度18位，其中出生日期占8位，从身份证的第7位开始至第14位结束。

01 提取出生日期

从文本中截取一段特定长度的字符串所使用的最经典的函数是MID()。

MID()函数语法如下：

MID(text, start_num, num_chars)

text  包含要提取字符的字符串（或单元格）

start_num  文本中要提取的第一个字符的位置 

num_chars  希望从文本中返回字符的个数 

以本例第4行（第一位员工高圆圆）为例：

身份证号所在的列是 G 列，那么text = G4 ，

出生日期是从第 7 个字符开始，那么start_num = 7，

出生日期的字符长度是8位，那么num_chars = 8

「出生日期」位于 H 列，我们在 H4 单元格中直接输入：

=MID(G4,7,8)

回车，即可得到返回值“19980101”，这就是从身份证中提取的出年日期的字符了。

细心的同学会发现，我们得到的“19980101”与表格中“1998-01-01”长的不一样（格式不一致）。

是的，MID()函数返回的是一段纯文本，不带任何格式。要让他们长的一样（格式一致），我们需要进行一步“格式转换”。

这里我们使用文本格式函数TEXT()，你可以把它理解成一个格式化函数，几乎可以按需格式任何类型的数值。

TEXT() 函数语法如下：

TEXT(value, format_text)

value  要转换格式的数值

format_text  要转换的格式（文本）

在本例中，

我们要转换格式的文本是来自于MID()函数的返回值，

也就是说 value = MID(G4,7,8),

而要显示的样式是“年-月-日”，令 format_text = "0-00-00"（记住用法）

稍微修改一下H4单元格中的函数：

=TEXT(MID(G4,7,8),"0-00-00")

回车，即可得到“1998-01-01”我们常用的格式了。

至此，我们已经完成了最关键的一步。

02 提取生日

有了出生年月，就等于有了生日信息，

只是我们通常在说一个人的生日时不会涉及年份，

格式一般显示为“**月**日”。

也就是说，我们只需要提取月、日信息，显示为“**月**日”的样式即可。

月和日的长度一共是4位，从身份证的第11位开始。

我们直接复制 H4 单元格中的公式，粘贴至 J4 单元格中，做如下修改：

=TEXT(MID(G4,11,4),"00月00日")

回车后，即可得到“01月01日”样式的生日信息了。

03 计算年龄

年龄是本例中唯一需要计算的一步。档案中的年龄一般是指当前年份与出生年份的差，还要考虑当前的日期是在生日前还是在生日后，在生日前就要再减掉1岁。比如，1990年3月1日出生至2010年3月1日，是20周岁，但如果当前是2010年2月份，还未到3月1日，那么年龄就应该是19周岁而不是20周岁。

所以，在Excel中计算年龄时，一定是以当前日期为基准，计算 与出生日期的年份差。

我们可以使用日期函数DATEDIF()来实现年龄计算。

DATEDIF() 函数语法如下：

DATEDIF（Start_Date,End_Date,Unit）

Start_Date  代表时间段内的第一个日期或起始日期；

End_Date  代表时间段内的最后一个日期或结束日期；

Unit  所需信息的返回类型。

在本例中，

开始日期是身份证中的出生日期，即

Start_Date = TEXT(MID(G4,7,8),"0-00-00") 

结束日期就是当前日期，也就是系统当前日期，可以用函数TODAY()提取到，即

End_Date = TODAY()

而我们想要的是两个日期的间隔年数，在Excel中用字母“Y”来表示年份差，即

Unit = "Y"

我们在I4单元格中，输入完整的公式 

=DATEDIF(TEXT(MID(G4,7,8),"0-00-00"),TODAY(),"Y")

回车，即可得到职工高圆圆的年龄了。

04 提取户籍地

从身份证中提取户籍地，需要使用《全国行政区域身份证代码表》作为数据源，借助Excel的搜索查询函数来实现。受篇幅所限，这一功能的具体的实现方法我们将在单独的文章中介绍。

总  结

通过小宇的案例，我们学习到以下4个函数的用法：

TODAY( )  

 获取系统前日期

MID( 文本字符串,开始提取的位置, 

 要提取的字符数  )  

  从一段文本字符串中提取一部分字符

TEXT( 要格式化的数值, 格式化后的形式  )  

  格式化金额、日期、文本、数字

DATEDIF( 开始日期,结束日期,天/月/年  )  

 计算两个日期之间间隔的天数、月数或年数

为获取最佳体验，给亲们两点建议，请收下：

在函数参数中引用单元格时对「列」使用「绝对引用」，以防止在其他单元格中复制引用公式时出错。示例中为强调优化思路，所有引用都简单的使用了「相对引用」。

注意DATEDIF( )函数的异常处理。在「年龄」一列往下的空白单元格中填充该公式时，会出现“#VALUE”的错误，原因是当身份证所在的单元格为空时，第一个参数的值为空，导致该函数返回值错误。为避免该情况出现，应该在DATEDIF( )函数外面再嵌套IFERROR( )错误处理函数：

=IFERROR(DATEDIF(TEXT(MID(G4,7,8),"0-00-00"),TODAY(),"Y"),"") 。这时再往下填充公式就不会显示错误了。

精心为您准备了本文的「知识卡片」，长按图片保存至相册 OR 分享给朋友

（精雕细琢才敢呈现，感谢您的支持）〜

  觉得好看，请点这里↓↓↓