（14页PPT）Excel和身份证不得不说的事儿.pptxVIP

内容提要：

使用Excel从身份证号码中

读取人员相关信息

weibo/excelbible

EXCEL

BIBLE

二代比一代多两位年份数字和最

后一位校验码

省份

性别

市县

出生日期校验

EXCEL

BIBLE

单元格只能保留15位有效数字

由于Excel单元格只能保留15位有效数字，在直接输入18位身份证

时最后三位会自动变成0

正确输入方法是在数字前加上一个单引号，或是事先将单元格格式设

置为【文本】

EXCEL

BIBLE

通过【数据有效性】验证输入的大致正确性

（不考虑具体内容的正确性）

条件1输入位数为15位或18位

=OR(LEN(A1)=15,LEN(A1)=18)

条件2前17位均为数字

=ISNUMBER(-LEFT(A1,17))

条件3如果不全都是数字，那就只能是18位且末尾是字母X

=OR(ISNUMBER(-A1),AND(LEN(A1)=18,RIGHT(A1)=X))

条件1AND条件2AND条件3

=AND(OR(LEN(A1)=15,LEN(A1)=18),ISNUMBER(-LEFT(A1,17)),

OR(ISNUMBER(-A1),AND(LEN(A1)=18,RIGHT(A1)=X)))

EXCEL

注：根据设置的单元格不同，公式中的A1也需相应变更BIBLE

l将4位或6位出生日期数字转换为真实日期值

l一代身份证的年份均为19XX年

156

1如果是位身份证，取其第7位开始的位数字

188

=MID(A1,7,IF(LEN(A1)=15,6,8))

2如果是15位身份证，数字前补充完整年份19..（否则小于30时会被转成20XX年）

=RIGHT(19MID(A1,7,IF(LEN(A1)=15,6,8)),8)

38位数字转换为真实日期值

=TEXT(RIGHT(19MID(A1,7,IF(LEN(A1)=15,6,8)),8),0-00-00)+0EXCEL

BIBLE

公式使用效果

=MID(A1,7,IF(LEN(A1)=15,6,8))

1

=RIGHT(19MID(A1,7,IF(LEN