当前位置:首页 > 科技 > 正文

@所有人,你的身份证号隐藏了超多Excel公式,知道几个?

哈喽,大家好,今天来给大家系统地讲解一下身份证号里隐藏了哪些Excel知识,赶紧来对照自查,看看你还有没有遗漏的吧!

1、如何正确地输入身份证号

D2单元格输入18位身份证号码:345321198010030876,显示出来的是“3.45321E+17”,而且Excel还擅自做主把最后三位数字换成“000”。

简直晕了,这不是我想要的啊啊啊啊!

@所有人,你的身份证号隐藏了超多Excel公式,知道几个?

输入大于15位的数字的正确操作:

方法一:先输入单引号,然后再输入数字。

方法二:设置单元格格式为文本再输入。

2、身份证号是否重复

我们在输入身份证号,用COUNTIFS判断号码是否重复时,明明没有重复,但用函数得到的结果却是2(指出现2次),这是怎么回事呢?

而COUNTIFS在常规计算时,只能计算前15位。图中的两个身份证号码,前15位相同,导致COUNTIFS认为它们是一致的。

所以这里应在英文双引号里面加一个星号“*”,使其从数值强行变为文本统计,从而进行完整的判断。

@所有人,你的身份证号隐藏了超多Excel公式,知道几个?

3、身份证号加密

如果我们需要给身份证号进行加密,可以在C2单元格输入公式=REPLACE(B2,LEN(B2)-5,4,"****")

REPLACE函数语法(要替换字符串,从哪个位置开始换,替换长度,新的字符)

@所有人,你的身份证号隐藏了超多Excel公式,知道几个?

4、根据身份证号提取出生年月

如下图所示,要根据C列的身份证号码,来提取出生年月,D2单元格公式为:

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

@所有人,你的身份证号隐藏了超多Excel公式,知道几个?

先用MID函数将C2单元格中身份证号从第7位开始,提取8个字符,然后使用TEXT函数将其变成具有日期样式的文本“1993-5-8”,最后加上两个负号,也就是计算负数的负数,通过这样一个数学计算,把文本型的日期变成了真正的日期序列值。

如果单元格中显示的是五位数值,设置成日期格式就好。

5、根据出生年月计算年龄

需要根据C列提取出来的出生年月来计算年龄,在C2单元格输入以下公式:

=DATEDIF(B2,TODAY(),"y")

@所有人,你的身份证号隐藏了超多Excel公式,知道几个?

TODAY函数返回当前的日期。

DATEDIF函数以C2的出生年月作为开始日期,以当前日期作为结束日期,第三参数使用“Y”,表示计算两个日期之间的整年数。

6、根据身份证号码提取性别

如下图所示,要根据C列的身份证号码,判断性别。E2单元格公式为:

=IF(MOD(MID(C2,17,1),2),"男","女")

@所有人,你的身份证号隐藏了超多Excel公式,知道几个?

先使用MID函数,从C2单元格的第17位开始提取1个字符,这个字符就是性别码。

然后使用MOD函数,计算这个性别码与2相除的余数。

如果IF函数的第一个参数等于0,IF函数将其按FALSE处理,返回第三参数指定的内容“女”。如果不等于0,则按TRUE处理,返回第二参数指定的内容“男”。

好啦,以上就是今天要给大家分享的Excel身份证号码那些事儿,你是不是全部了然于心呢?

有话要说...

取消
扫码支持 支付码