Excel中身份证号码的秘密

一、号码的结构

公民身份号码是特征组合码,由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。

Excel中身份证号码的秘密

1、地址码

表示编码对象常住户口所在县(市、旗、区)的行政区划代码,按GB/T2260的规定执行。

Excel中身份证号码的秘密

2、出生日期码

表示编码对象出生的年、月、日,按GB/T7408的规定执行,年、月、日代码之间不用分隔符。

Excel中身份证号码的秘密

3、顺序码

表示在同一地址码所标识的区域范围内,对同年、同月、同日出生的人编定的顺序号,顺序码的奇数分配给男性,偶数分配给女性。

Excel中身份证号码的秘密

4、校验码

根据前面十七位数字码,按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。

Excel中身份证号码的秘密

二、Excel中号码的秘密

1、通过6位地址码我们可以找出地址信息。

2、通过8位出生日期码我们可以知道出生日期。

3、通过8位出生日期码可以计算出还剩多少天过生日。

4、通过8位出生日期码可以计算出年龄。

5、通过3位顺序码可以知道性别。

Excel中身份证号码的秘密

下面,我们就在Excel中来实现这些吧!

Excel中身份证号码的秘密

准备工作

①下表中列出了我们本次教程所要实现的内容,随便写了3个符合规定的身份证号码。这是表1,表名为“身份证号码信息表”。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

②在网上可以搜索全国各省份身份证号码前6位对照表,这里我使用了陕西省份的数据进行教程,因为我找到的对照表中地址码和地址信息是连在一起的,通过简单的裁剪函数将其分开。这是表2,表名为“身份证号码前6位对照表”。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

按号码结构裁剪号码

Excel中身份证号码的秘密

6位地址码,即号码的前6位,使用LEFT函数。

含义 从左边第一个字符开始截取,截取指定的长度
语法格式 left(text,num_chars) 
text 代表用来截取的单元格内容
num_chars 代表截取多少个字符数
Excel中身份证号码的秘密

在B3单元格输入:=LEFT(A3,6),回车,向下拖动格式刷,把其它两个地址码也剪裁出来。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

8位出生日期码,即号码的第7位到第14位,使用MID函数。

含义 从一个文本字符串的指定位置开始,截取指定数目的字符。
语法格式 MID(text,start_num,num_chars)
text 代表用来截取的单元格内容
start_num 表示从第几位开始截取
num_chars 代表截取几位字符数
Excel中身份证号码的秘密

在C3单元格输入:=MID(A3,7,8),回车,向下拖动格式刷,把其它两个出生日期码也剪裁出来。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

3位顺序码,即号码的第15位到第17位,也使用MID函数。

Excel中身份证号码的秘密

在D3单元格输入:=MID(A3,15,3),回车,向下拖动格式刷,把其它两个顺序码也剪裁出来。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

1位校验码,即号码的第18位,使用RIGHT函数。

含义 是用于从一个文本字符串的最后一个字符开始返回指定个数的字符,即截取倒数指定个数的字符
语法格式 RIGHT(text,num_chars)
text 代表用来截取的单元格内容
num_chars 代表截取几位字符数

与LEFT函数刚好相反,一个从左边第一位开始截取,一个从右边第一位开始截取。

Excel中身份证号码的秘密

在E3单元格输入:=RIGHT(A3,1),回车,向下拖动格式刷,把其它两个检验码也剪裁出来。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

获取地址信息

使用VLOOKUP函数。

含义 按列查找,最终返回该列所需查询列序所对应的值
语法格式 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
lookup_value 要查找的值
table_array 要查找的区域
col_index_num

返回数据在查找区域的第几列(即你需要返回的那个值在查找区域的第几列,并不是整个表的第几列)

range_lookup

模糊匹配或者精确匹配,值为TRUE或FALSE

Excel中身份证号码的秘密

在F3单元格输入:=VLOOKUP(B3,身份证号码前6位对照表!$B$2:$C$33,2,FALSE),回车,向下拖动格式刷,把其它两个地址信息也获取到。

“身份证号码前6位对照表”是表名(因为地址码数据在表2中,所以这里需要使用表2)。

“$B$2:$C$33”带$是绝对引用的意思。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

获取出生日期

使用DATE函数。

含义 返回代表特定日期的序列号
语法格式 DATE(year,month,day)
year 表示年份
month 表示月份
day 表示天数
Excel中身份证号码的秘密

设置G3单元格格式为,日期,“yyyy-mm-dd”。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

在G3单元格输入:=DATE(LEFT(C3,4),MID(C3,5,2),RIGHT(C3,2)),回车,向下拖动格式刷,把其它两个出生日期也获取到。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

计算生日剩余天数

使用YEAR、MONTH、DAY、TODAY、NOW函数。

YEAR函数 返回年份
MONTH函数 返回月份
DAY函数 返回天数
TODAY函数 返回当前年月日日期
NOW函数 返回当前的日期和时间
Excel中身份证号码的秘密

设置H3单元格格式为,数值,0位小数。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

第一种,在H3单元格输入:=DATE(YEAR(NOW()),MONTH(G3),DAY(G3))-TODAY(),回车,向下拖动格式刷,把其它两个生日剩余天数也获取到。

红色的表示生日已过多少天;

黑色的表示生日还有多少天;

0表示生日为当天。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

第二种,在H3单元格输入:=IF(DATE(YEAR(NOW()),MONTH(G3),DAY(G3))>TODAY(),DATE(YEAR(NOW()),MONTH(G3),DAY(G3))-TODAY(),IF(DATE(YEAR(NOW()),MONTH(G3),DAY(G3))=TODAY(),”今天生日”,”生日已过”)),回车,向下拖动格式刷,把其它两个生日剩余天数也获取到。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

获取年龄(周岁)

使用DATEDIF函数。

含义 主要用于计算两个日期之间的天数、月数或年数
语法格式 DATEDIF(start_date,end_date,unit)
start_date 表示起始日期
end_date 表示结束日期
unit 为所需信息的返回时间单位代码
Excel中身份证号码的秘密

unit代码含义如下:

y 返回时间段中的整年数
m 返回时间段中的整月数
d 返回时间段中的天数
md 参数1和2的天数之差,忽略年和月
ym 参数1和2的月数之差,忽略年和日
yd 参数1和2的天数之差,忽略年。按照月、日计算天数
Excel中身份证号码的秘密

设置I3单元格格式为,数值,0位小数。

Excel中身份证号码的秘密

在I3单元格输入:=DATEDIF(G3,TODAY(),”y”),回车,向下拖动格式刷,把其它两个年龄也获取到。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

获取性别

使用IF、MOD函数。

含义

判断一个条件是否满足,如果满

足返回一个值,如果不满足则返

回另一个值

语法格式

IF(logical_test,value_if_true,

value_if_false) 

logical_test 条件判断
value_if_true 条件成立返回的值
value_if_false 条件不成立返回的值
含义 两个数值作除法运算后的余数
语法格式 MOD(nExp1,nExp2)
nExp1 为被除数
nExp2 为除数
Excel中身份证号码的秘密

在J3单元格输入:=IF(MOD(D3,2)=0,”女”,IF(MOD(D3,2)=1,”男”)),回车,向下拖动格式刷,把其它两个性别也获取到。

Excel中身份证号码的秘密
Excel中身份证号码的秘密

好了,今天的所有教程结束了,你有收获吗?

Excel中身份证号码的秘密

案例文档下载:

链接:

https://pan.baidu.com/s/1CfoIOwb-vh3KaTHQXHzK7g

密码:qux1

来源:IT刘伟东,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/124113

发表评论

登录后才能评论
侵权联系
返回顶部