Excel表格中根据身份证号码自动填出生日期、计算年龄

Excel表格中根据身份证号码自动填出生日期、计算年龄，利用WPS的函数功能，只需要设置一下相应的参数，把一个个隐藏在身份证号码 里的信息提取出来，完成上面所有操作用时也不到半小时，将所得到的表格美化一下，就可以向领导交差了，呵呵。 在Excel或WPS表格中，处理身份证号码是一项常见的任务，特别是在进行人事管理或者数据分析时。以下是如何根据身份证号码自动填出生日期和计算年龄的详细步骤和相关知识点： 1. **出生日期的提取**： - 对于18位身份证号码，出生日期位于第7位到第14位，可以通过`MIDB`函数来提取。例如，`DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))`这个公式将提取出年（4位）、月（2位）和日（2位），然后组合成一个日期格式。 - 如果身份证号码只有15位，出生日期在第7位到第12位，转换公式为`TEXT(MID(E2,7,6),"#-00-00")`，需要手动添加'19'作为年份前缀，因为15位身份证号码的出生年份默认为1900年代。 2. **自动录入性别**： - 性别的判断基于身份证号码的最后一位（18位时为倒数第二位）。奇数表示男性，偶数表示女性。可以使用`MOD`函数结合`IF`函数实现。例如，`IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))),2)=0,"女","男")`，这个公式会先判断身份证长度，然后提取性别位，再根据奇偶性确定性别。 3. **计算年龄**： - 年龄的计算通常基于当前年份减去出生年份，但要考虑是否已经过生日。这里使用了简单的公式`IF(MIDB(E2,7,2)="19",107- MIDB(E2,9,2),107-MIDB(E2,7,2))`，这里的107代表当年的年份，如果是18位身份证，从第9位开始取两位作为出生年份，如果是15位，从第7位开始取两位。 4. **身份证号位数判断**： - 使用`LEN`函数可以获取身份证号码的长度，从而区分15位和18位身份证。例如，`=LEN($A2)`，如果长度为18，那么是18位身份证，否则是15位。 5. **性别判断**： - 使用`MID`和`RIGHT`函数提取性别标识数字，结合`IF`函数进行条件判断。例如，`=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))`，根据身份证长度判断提取性别位。 6. **性别转换**： - 使用`ISEVEN`函数检查性别标识数字的偶偶性，`=IF(ISEVEN($C2)=TRUE,"女","男")`，如果为偶数（即性别位为0），则表示女性，反之为男性。 7. **出生日期格式化**： - 提取出数字型出生日期后，可以进一步使用`"&"`连接符和`TEXT`函数将其转换为标准日期格式，例如`"19"&MID($A2,7,2)&"-"`和`MID($A2,11,2)&"-"&MID($A2,13,2)`，将年、月、日组合成`YYYY-MM-DD`格式。 通过以上方法，可以高效地处理大量包含身份证号码的数据，自动填充出生日期、计算年龄并标识性别，大大提高了工作效率。在实际操作中，确保数据的准确性非常重要，同时也可以结合条件格式、数据验证等其他Excel功能进行数据清洗和验证。