[原创]电子表格中与身份证有关的公式计算与查询

liuzequan

“格式”-“工作表”-“取消隐藏”即可！

风浪

谢谢分享!

zhanhuolin

下不了啊

liuzequan

能够下！

luowanjian

谢谢，给我很多帮助，让我完成了很多工作。

123木头人

号码有效性验证的公式是怎样来的，我看不太懂，把公式复制到别的工作表中去应用却得出FALSE,为什么呀

liuzequan

又到开学报名了，请广大班主任、教导处工作人员根据具体情况使用本文件。

wshcw

也把我的公式从"我的空间"贴过来,给大家分享:

有效性公式(适用于新输入身份证号):
=AND(OR(LEN(A1)={15,18}),ISNUMBER(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")),--TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"#-00-00")>1828,--TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"#-00-00")<TODAY())
性别(最短31字符):
=IF(ISODD(MID(A1,9,9)),"男","女")
=IF(ISEVEN(MID(A1,9,9)),"女","男")
=IF(MOD(MID(A2,15,3),2),"男","女")
出生日期:
=--TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"#-00-00")

年龄:

=MOD(YEAR(NOW())-MID(A1,7,LEN(A1)/2-5),190)

或者:

=DATEDIF(TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"#-00-00"),NOW(),"Y")
已有身份证的情况下,公式应加合法性,有效性判断,所以公式改为:
出生日期:
=IF(A1="","",IF(AND(LEN(A1)<>{15,18}),"错误",IF(ISERR(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")),"错误",IF(OR(1*TEXT

(MID(A1,7,LEN(A1)/2-1),"#-00-00")<1828,1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")>TODAY()),"错误",--TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"#-00-00")))))
年龄:
=IF(A1="","",IF(AND(LEN(A1)<>{15,18}),"错误",IF(ISERR(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")),"错误",IF(OR(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")<1828,1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")>TODAY()),"错误",MOD(YEAR(NOW())-MID(A1,7,LEN(A1)/2-5),190)))))
折算为天数:
=IF(A1="","",IF(AND(LEN(A1)<>{15,18}),"错误",IF(ISERROR(1*(TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00"))),"错误",IF(OR

(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")<1828,1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")>TODAY()),"错误",DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"D")))))

身份证第18位（校验码）的计算方法

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。
5、通过上面得知如果余数是2，就会在身份证的第18位数字上出现罗马数字的Ⅹ。如果余数是10，身份证的最后一位号码就是2。
例如：某男性的身份证号码是53292719650626003X。我们要看看这个身份证是不是合法的身份证。
首先我们得出前17位的乘积和是189，然后用189除以11得出的结果是17+2/11，也就是说其余数是2。最后通过对应规则就可以知道余数2对应的数字是x。所以，可以判定这是一个合格的身份证号码。

=IF(LEN(A1)=15,REPLACE(A1,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A1,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A1)

icongmu

谢谢WSHCW老师分享

有一问题不明:

年龄计算公式

=MOD(YEAR(NOW())-MID(A1,7,LEN(A1)/2-5),190)

改成=YEAR(NOW())-MID(A1,7,LEN(A1)/2-5),这样不是更简吗?

为什么还要以190岁为划分线?

cnsfjlp

提取出生日期中，有的公式应该简化，比如：

=IF(LEN(AD2)=15,"19"&MID(AD2,7,6),IF(LEN(AD2)=18,MID(AD2,7,8)))

就可以得出19660101这样的日期结果，没必要用

=IF(R2="","",DATE(LEFT(IF(LEN(R2)=15,"19","")&MID(R2,7,6+2*(LEN(R2)=18)),4),MID(IF(LEN(R2)=15,"19","")&MID(R2,7,6+2*(LEN(R2)=18)),5,2),MID(IF(LEN(R2)=15,"19","")&MID(R2,7,6+2*(LEN(R2)=18)),7,2)))

这么复杂。