EXCEL 身份证号提取信息

 

每个单位一般都有“职工花名册”，其中有“身份证号”和“性别”两列数据，有没有想过让表格根据身份证号自动填写性别呢?
方法如下：
在C4单元格中输入“=IF(MOD(RIGHT(D4),2)=0,"女","男")”，以下单元格复制即可。
说明：根据目前我国居民身份证的编号规则，男的末尾数是奇数，女的末尾数是偶数。Mod()是求余数函数，RIGHT()是截取右侧字符串函数，上述Right(D4)也可写为Right(d4,1)，即截取D4单元格中数据的最右侧一位。

 

A1存放身份证号码.
则在B1获取性别的公式为：=IF(MOD(LEFT(RIGHT(A1,(LEN(A1)=18)+1)),2),"男","女")这个18、15位的通用。

 

 

 

居民身份证号码是从事人事管理方面的人员经常接触到的一种特殊数据，原为15位，在21世纪以后都统一升级为18位，其编码规则按排列顺序从左至右依次为：
　　15位：6位数字地址码，6位数字出生日期码，3位数字顺序码。
　　18位：6位数字地址码，8位数字出生日期码，3位数字顺序码和1位数字校验码。
　　下面将向用户介绍在WPS表格中如何进行身份证数据的录入和利用身份证号码进行相关操作，包括以下内容：

　　1.身份证数据的录入；　　　2.身份证号码位数的转换；
　　3.从身份证号码中提取生日；4.从身份证号码中提取性别；
　　1.身份证号码的输入

　　居民身份证号码分别存在15位和18位数字组合（除部分身份证号码以X结尾），那么身份证号码的录入也经常使用户产生困惑。因此提供以下两种方法供用户进行数据录入：

　　以文本方式录入：首先，设置单元格格式为“文本”，然后进行身份证号码的录入。
　　前置单撇号进行录入：在输入身份证号码时，先输入单撇号“'”，再输入身份证号即可，如：在单元格中输入：'123456199912310123。

　　注意：以上两种方法同样可以用于如：账号、产品编号等长数值的数据录入。
　　已经录入数据的修改：
　　如果用户直接输入身份证号码，可能用户看到的是以科学计数法来显示的数值，因为在WPS表格中，整数数值仅能保留15位有效数字，而且当用户输入超过11位数值时，系统自动以科学计数法来进行显示。
　　因此对于这种情况，分别有两种办法来解决：
　　1.已经输入的15位身份证：用户需要重新在单元格格式中进行自定义设置：0，身份证可以完整显示。
　　2.已经输入的18位身份证：由于整数数值仅能保留15位有效数字，系统除了显示成科学计数法以外，原来录入的数据系统只能保留前15位有效数字，其他3位数字以0显示，数据精度已经丢失。因此这种数据只能重新录入。

　　2.15位与18位身份证的相互转换：

　　进入21世纪后，公安局新颁发的居民身份证都是18位。某些时候用户如果需要将15位身份证和18位身份证位数进行转换（或进行校验），可以使用以下两种方法：

　　15位转换为18位：

　　如：A2单元格为某一15位身份证号码，B2的转换的数组公式为：

　　{=REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)}

　　利用这个公式的原理，用户还可以对18位身份证进行校验，只需要将公式中“REPLACE(A2,7,,19)”修改为“LEFT(A2,17)”即可。

　　18位转换为15位:

　　如：A2单元格为某一18位身份证号码，B2的转换公式为：

　　=REPLACE(LEFT(A2,17),7,2,"")
　　3.从身份证号码中提取信息

　　人事部门经常需要从身份证号码中提取相关的人员信息，如：出生日期、性别等信息。下面分别对这两种信息的提取进行介绍：

　　提取出生日期:

　　如：A2单元格为某一身份证号码（18位或15位），下面的两个公式将可以提取出文本型日期：

　　=TEXT(MID(A2,7,IF(LEN(A2)=15,6,8)),"0-00-00")
　　=TEXT(MID(A2,7,6+(LEN(A2)=15)*2),"0-00-00")

　　如果用户希望将提取出来的数据转换为真正的日期，则需要对数据进行转换，常用的方法是：

　　=--TEXT(MID(A2,7,6+(LEN(A2)=15)*2),"0-00-00")
　　该公式的最前面加上“两个负号”，在WPS表格中通常称为“减负运算”，目的是将文本型数据转换为数值（由于日期型数据为特殊的数值，用户只需要设置单元格格式为日期格式即可）。

　　提取人员性别:

　　在身份证号码中，身份证顺序码的最后一位数字的奇偶性可以识别用户的性别，下面的公式将通过MOD函数取得用户的性别。

　　如：A2单元格为某一身份证号码（18位或15位），提取性别的公式为：

　　=IF(MOD(MID(A1,IF(LEN(A1)=15,15,17),1),2),"男","女")
　　=IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女")
　　小结：
　　1.身份证号码的输入技巧，该技巧对录入账号、产品编号等长数值的数据同样适用。
　　　　方法1：先设置单元格格式为“文本”格式，再进行输入；
　　　　方法2：在输入时，先输入单撇号，再输入身份证号码；
　　2.用户可以通过函数公式对15位和18位身份证号码进行转换和校验。

　　3.用户可以通过函数公式从身份证号码中提取人员出生日期、人员性别等信息。

　　适用范围:

　　适用于WPS Office 2005及以上版本。