今天主要是两个函数，和一个能干事的大公式。大致内容如下：

• datedif函数好吃吗？能干啥？
• text函数究竟是干什么的？
• 如何通过Excel检验身份证号的真假。

好了，进入正题吧。

1、datedif 函数

Excel中，DATEDIF是一个传说的隐藏函数，在帮助和插入之中，你是找不到的。它的作用呢，就是返回两个日期之间“年”、“月”、“日”之间的间隔数。

先来看参数。

=DATEDIF("开始日期","结束日期","返回值的类型")

开始和结束日期，只有一个要求，就是结束日期必须大于开始日期。同时，开始日期必须在1900年后。

返回的类型，可以选择以下的：

Y ： 年
M ： 月
D ： 日

当然，还有别的玩法。

MD ： 返回起始日期和结束日期的同月间隔天数，忽略月份和年份。
YD ： 返回起始日期和结束日期的同年间隔天数，忽略年份。
YM ： 返回起始日期和结束日期的间隔月数，忽略日期中的年份。

举个例子，计算我到2020年9月19日的周岁。

=datedif("1997-09-26","2020-09-19","Y")

直接返回多少呢？

答案是18（22），毕竟姜辰今年18（22）岁。

2、text函数

text函数是Excel中最有用的函数之一。

也是我最喜欢的。

主要作用就是把数值转换为按指定格式表示的文本。

用起来，很舒服的。

用法：

=TEXT("数值","你要转换出来的格式")

举个例子，我要把20200808变成2020年08月08日。

=text("20200808","0000年00月00日")

很简单，那么能转换哪些格式呢？

可以转换的格式	数值	转换出来的结果	说明
G/通用格式	10	10	没变化
000.0	10.25	010.3	小数点前面不够三位，用0补齐，保留1位小数。
####	10.0000	10	没用的0一律消失
00.##	1.2340	01.23	小数点前面不够2位，用0补齐；后面多余的，只保留2位小数
0000-00-00	12345678	1234-56-78	用来表示日期的
0000年00月00日	87654321	8765年43月21日	还是用来表示日期
aaaa	2020/09/19	星期六	显示中文星期几的全称
aaa	2020/09/19	六	显示中文星期几的简称

除了这些，还能用来干什么？

还可以用来判断条件哟。

举个例子：

=text("85","[>=90]优秀;[>=60]合格;不及格")

输出的结果是什么？

合格

这是什么意思呢？输入的值大于等于90，则显示优秀；大于等于60，并且小于90，则显示合格，小于60，显示不合格。

可以用来干什么？判断成绩打等级？

就这样吗？用text函数能不能把123变成一二三？

可以。

=text("123","[DBNum1][$-804]G/通用格式")

输出结果：

一百二十三

那我要大写的呢？

=text("123","[DBNum2][$-804]G/通用格式")

输出结果：

壹佰贰拾叁

强大的TEXT函数，不打算多练习一下吗？

接下来，就是我们的重头戏了，

如何通过Excel检验身份证号的真假。

众所周知，身份证号只有18位，每一位究竟是干什么的呢？

来，一起研究下。

Excel 校验身份证号

身份证号前6位是地区码。以北京为例：110000，第一、二位表示省，三四位标识市，五六位标识县。

第7到14位，表示出生年月日。这也就是为什么Excel中从身份证计算年龄都是从第7位开始，提取8位了。

紧接着，15到17位是顺序码。用来表示在同一个地方出生的人，进行编号。其中，17位奇数给男生，偶数给女生。

18位是校验码，用来检测身份证是否合法，如果第18位是10，则用X代替。据说是按照ISO7064:1983.MOD 11-2校验码来计算出来的。

正常情况怎么判断身份证号是否正确呢？

1、将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为：7－9－10－5－8－4－2－1－6－3－7－9－10－5－8－4－2。
2、将这17位数字和系数相乘的结果相加。
3、用加出来和除以11，看余数是多少？
4、余数只可能有0－1－2－3－4－5－6－7－8－9－10这11个数字。其分别对应的最后一位身份证的号码为1－0－X －9－8－7－6－5－4－3－2。（即余数0对应1，余数1对应0，余数2对应X...）
5、通过上面得知如果余数是3，就会在身份证的第18位数字上出现的是9。如果对应的数字是2，身份证的最后一位号码就是罗马数字X。
例如：某男性的身份证号码为【53010219200508011X】， 我们看看这个身份证是不是符合计算规则的身份证。
首先我们得出前17位的乘积和【(5*7)+(3*9)+(0*10)+(1*5)+(0*8)+(2*4)+(1*2)+(9*1)+(2*6)+(0*3)+(0*7)+(5*9)+(0*10)+(8*5)+(0*8)+(1*4)+(1*2)】是189，然后用189除以11得出的结果是189÷11=17余下2，187÷11=17，还剩下2不能被除尽，也就是说其余数是2。最后通过对应规则就可以知道余数2对应的检验码是X。所以，可以判定这是一个正确的身份证号码。

那么，用Excel怎么来检测呢？

假设A2是身份证号，并且是现在18位的二代身份证，那么我们就在B2来校验吧。

# 在B2填写：
=IF(LEN(A2)<>18,"非二代身份证",IF(MID("10X98765432",(MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1),1)=RIGHT(A2),"对","错"))

这个公式很长对吗？

是不是需要一个一个解析一下？

if函数、mod函数、mid函数，我们都在《Excel中一些好玩的小公式（2）》中讲到了，而之中陌生的，是SUMPRODUCT函数和INDIRECT函数以及RIGHT函数。

那么，明天见。