wps2013表格技巧：通过函数提取身份证信息（15位、18位通吃）

 本帖最后由 你猜？ 于 2013-7-25 20:53 编辑

WPS2013表格技巧：通过函数提取身份证信息（15位、18位通吃）

<籍贯、性别、出生年月日、农历年、属相、生日、星座、出生至今年数、出生至今天数和判断证件有效性>

通过身份证提取籍贯、农历年、属相、星座和出生至今天数是独家报道哦~

等我研究透了，给你们计算生辰八字。{:soso_e151:}
[d1000]
文件中公式说明：

本帖隐藏的内容

原籍贯：

1. =IFERROR(IF(LENB(A2)=15,IFERROR(vlookup(VALUE(MID(A2,1,6)),Sheet4!$A:$B,2,0),VLOOKUP(VALUE(MID(A2,1,4)&"00"),Sheet4!$A:$B,2,0)),IFERROR(IFERROR(VLOOKUP(VALUE(MID(A2,1,6)),Sheet3!$A:$E,5,0),VLOOKUP(VALUE(MID(A2,1,4)&"00"),Sheet3!$A:$E,5,0)),VLOOKUP(VALUE(MID(A2,1,2)&"0000"),Sheet3!$A:$E,5,0))),"证件无效")

复制代码

运算说明：
此运算运用了iferror逻辑函数，主要判断后面的函数计算结果，如果经过后面所有函数的判断都无法得到结果的，就在此单元格显示"证件无效"。（此函数在后面的单元格（除判断身份证有效单元格外）都用到此函数，同理，后面就不一一解释此函数的用法。
if函数主要是判断a2单元格的字符数（len函数）是否满足15位，如果是15位的话，则从a2单元格里提取（mid函数）前6位数字（从第一位提取，6个字符）在数据库中查找（vlookup函数）满足此条件的原籍贯所在地，此单元中后面的vlookup函数也是此用途。
中间运用了value函数，主要是因为数据库的数据都是数值型数字，函数中提取的是文本型数字，value函数就是把文本型数值激活为数值型函数。

性别：

1. =IFERROR(IF(LENB(A2)=15,IF(MOD(MID(A2,15,1),2)=1,"男","女"),IF(MOD(MID(A2,17,1),2)=1,"男","女")),"证件无效")

复制代码

运算说明：
此运算中主要运用了循环余数（mod）函数，将a2单元格中从第15位数字起提取1位，并除以2，如果得到1，就是男性，否则就是女性。<mod函数也是判断数字是否为奇偶数，或被某是否整除，此单元格中就运用了判断奇偶数，农历年和属相中运用了被60整除，在我的视频教程：wps2013视频教程：wps表格制作工资条的四种方法和技巧：http://bbs.wps.cn/thread-22377957-1-1.html>

出生年月日：

1. =IFERROR(IF(LENB(A2)=15,TEXT(MID(A2,7,6),"19"&"00年00月00日"),TEXT(MID(A2,7,8),"0年00月00日")),"证件无效")

复制代码

运算说明：
此运算中运用了转换文本函数（text），指定提取（mid函数）的信息转化为指定的格式。

本帖隐藏的内容

农历年：

1. =IFERROR(VLOOKUP(MOD(IF(LEN(A2)=15,VALUE("19"&MID(A2,7,2)),VALUE(MID(A2,7,4))),60),Sheet2!$S$1:$U$61,2,0),"证件无效")

复制代码

运算说明：
此运算中遇到的所有函数在前面都已经提到了，在这里就不一一详解，在此只解释下思路，在15位身份证号码在提取(mid)后在前面加上"19"，先用用value强制转化为数值型数字参与计算，然后运用循环余数函数（mod）和查找引用函数结合在数据中，查找对应的农历年份。

属相：

1. =IFERROR(VLOOKUP(MOD(IF(LEN(A2)=15,VALUE("19"&MID(A2,7,2)),VALUE(MID(A2,7,4))),60),Sheet2!$S$1:$U$61,3,0),"证件无效")

复制代码

运算说明：
此运算与农历年的运算类似，在数据库中查找相对应的属相。

生日：

1. =IFERROR(IF(LENB(A2)=15,TEXT(MID(A2,9,4),"00月00日"),TEXT(MID(A2,11,4),"00月00日")),"证件无效")

复制代码

运算说明：
此运算与提取出生年月日类似，在这里只是提取（mid）月份和年份，然后用文本函数（text)强制转化为日期格式。

本帖隐藏的内容

星座：

1. =IFERROR(IF(LENB(A2)=15,INDEX(Sheet2!$M$1:$M$13,MATCH(VALUE(TEXT(MID(A2,9,4),"0月00日")),Sheet2!$N$1:$N$13)),INDEX(Sheet2!$M$1:$M$13,MATCH(VALUE(TEXT(MID(A2,11,4),"0月00日")),Sheet2!$N$1:$N$13))),"证件无效")

复制代码

运算说明：
此运算中运用了查找引用函数（index函数嵌套match函数），与前面的vlookup函数类似，只不过vlookup函数如果要逆向查找需要嵌套if函数和数组公式：
=IFERROR(IF(LEN(A2)=15,VLOOKUP(VALUE(TEXT(MID(A2,9,4),"0月00日")),IF({1,0},Sheet2!$N$1:$N$13,Sheet2!$M$1:$M$13),2,1),VLOOKUP(VALUE(TEXT(MID(A2,11,4),"0月00日")),IF({1,10},Sheet2!$N$1:$N$13,Sheet2!$M$1:$M$13),2,1)),"证件无效")
用index函数嵌套match函数就不用这些了此法是vlookup函数的弊端。在此运用index函数嵌套match函数，主要是让大家更多的了解其他的查找引用函数。（其实此数据库中的日期，是可以放在星座前面的，然后用vlookup函数就简单多了）
[此处运用了查找引用函数的模糊匹配，绝对匹配：用index函数嵌套match函数<match第三参数更改为0（false)，或默认不写>或vlookup函数<第四参数更为0（false）>；如果是模糊匹配，在match函数中的第三个参数更改为1(true)(第三参数为1升序排列，-1为降序排列），vlookup第四个参数为1（true)]
如图：

出生至今年数：

1. =IFERROR(IF(LENB(A2)=15,YEAR((TODAY()))-YEAR((TEXT(MID(A2,7,6),"19"&"0年00月00日"))),YEAR((TODAY()))-YEAR((TEXT(MID(A2,7,8),"0年00月00日")))),"证件无效")

复制代码

运算说明：
此运算运用了日期函数（year、today函数），year函数是提取日期函数中的年份，然后用今天（today函数）所在的年份减去a2身份证日期中提取（mid)日期中的年份。（在15位身份证中是从第7位提取6位，并在前面加上"19"，然后将其强制转化为数字（text函数）参与计算。

出生至今天数：

1. =IFERROR(IF(LENB(A2)=15,TODAY()-TEXT(MID(A2,7,6),"19"&"00-00-00"),TODAY()-TEXT(MID(A2,7,8),"00-00-00")),"证件无效")

复制代码

运算说明：
此运算与出生至今年数类似，只不过此函数是将其强制转换（text)为天数然后参与计算的。<提示：时间日期函数有个特征：时间函数是小数（把当前时间除以24得到的），日期函数是整数（是从1900年1月1日算起的天数），所以许多网友在在表格中经常遇到，明明输入时间，却显示小数，明明输入日期，却显示数字，遇到此种情况，只要设置单元格就行，如果在函数运算中出现的话，就用text转化。>

判断证件有效性：

1. =IF(OR(B2="证件无效",C2="证件无效",D2="证件无效",E2="证件无效",F2="证件无效",G2="证件无效",H2="证件无效",I2="证件无效",J2="证件无效"),"证件无效","证件有效")

复制代码

运算说明：
此运算是运用（逆向思维）逻辑函数（if和or）判断前面单元格（b2,c2,d2,e2,f2,g2,h2,i2和j2）如果有一个显示为"证件无效"，则说明此身份证证件无效，否则此身份证就有效。
此法也可以使用函数（顺向思维）：

1. =IF(AND(B2="证件无效",C2="证件无效",D2="证件无效",E2="证件无效",F2="证件无效",G2="证件无效",H2="证件无效",I2="证件无效",J2="证件无效"),"证件有效","证件无效")

复制代码

同样如果此单元格显示证件无效让其显示红色（也可以设置字体、字号、底纹等），使用条件格式。

本帖隐藏的内容

如图：

总结：
运用函数类型：
逻辑函数：iferrror,if,and,or,
2. 文本字节函数：mid，len，text，value
3. 查找引用函数：vlookup，index，match
4. 时间日期函数：year，today
5. 数学函数：mod
特别强调下：
如果大家在今后遇到将文本型数数字强制转化为数字值型数字用value函数。
如果大家在今后遇到格式中有限考虑用单元格设置，如果在运算中，首选text函数。
如果大家在统计单元格字符，优先选择len函数，统计单元格字节就用lenb函数。
如果是遇到需要循环处理数据，就用mod函数。
如果大家要绝对查找数据，用index函数嵌套match函数<match第三参数更改为0（false)，或默认不写>或vlookup函数<第四参数更为0（false）>；如果是模糊匹配，在match函数中的第三个参数更改为1(true)(第三参数为1升序排列，-1为降序排列），vlookup第四个参数为1（true)。

文件撤销保护密码：123

通过身份证提取信息.xls(868 KB, 下载次数: 211)