excel 身份证号提取信息.doc

每个单位一般都有“职工花名册”，其中有“身份证号”和“性别”两列数据，有没有想 过让表格根据身份证号自动填写性别呢? 方法如下： 在 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.1.身份证号码的输入身份证号码的输入居民身份证号码分别存在 15 位和 18 位数字组合（除部分身份证号码以 X 结尾），那么身份证 号码的录入也经常使用户产生困惑。

因此提供以下两种方法供用户进行数据录入：以文本方式录入：以文本方式录入：首先，设置单元格格式为“文本”，然后进行身份证号码的录入前置单撇号进行录入：前置单撇号进行录入：在输入身份证号码时，先输入单撇号“'”，再输入身份证号即可，如： 在单元格中输入：'123456199912310123注意：以上两种方法同样可以用于如：帐号、产品编号等长数值的数据录入已经录入数据的修改：已经录入数据的修改：如果用户直接输入身份证号码，可能用户看到的是以科学计数法来显示的数值，因为在 WPS 表 格中，整数数值仅能保留 15 位有效数字，而且当用户输入超过 11 位数值时，系统自动以科学计数 法来进行显示因此对于这种情况，分别有两种办法来解决：1.已经输入的 15 位身份证：用户需要重新在单元格格式中进行自定义设置：0，身份证可以完 整显示2.已经输入的 18 位身份证：由于整数数值仅能保留 15 位有效数字，系统除了显示成科学计数 法以外，原来录入的数据系统只能保留前 15 位有效数字，其他 3 位数字以 0 显示，数据精度已经 丢失因此这种数据只能重新录入2.152.15 位与位与 1818 位身份证的相互转换：位身份证的相互转换：进入 21 世纪后，公安局新颁发的居民身份证都是 18 位。

某些时候用户如果需要将 15 位身份 证和 18 位身份证位数进行转换（或进行校验），可以使用以下两种方法：1515 位转换为位转换为 1818 位位：如：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)”即可1818 位转换为位转换为 1515 位位: :如：A2 单元格为某一 18 位身份证号码，B2 的转换公式为：=REPLACE(LEFT(A2,17),7,2,““)3.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 及以上版本。