admin 管理员组文章数量: 1086019
2024年3月21日发(作者：在线抽签工具)
EXCEL中运用 身份证号码
常用操作收集整理
（自己运用过）
一、从18位身份证号码中提取出生日期
假设身份证号在A列，则第一个身份证号所在的位置为A1;可在A1所在的那一行任
意单元格中编辑公式，即B1、C1、D1上均可（根据实际情况更改公式中A1项）
例：按照下面的表格所示，若要计算出生年月，则可在E3一栏输入以下任意公式，
而公式里的A1需改为A3，E3计算出出生年月以后，E3以下的出生年月，向下拖就可自
行进行填充。
公式为：等号以后的内容，包括等号都属于公式的一部分，任意一种公式均可。
1、输出格式为 2011-1-1 ，日与月都为单数，不会在单数前自动补0，单数即为单位数。
=DATE(mid(A1,7,4),mid(A1,11,2),mid(A1,13,2))
2、输出格式为 2011-01-01 ，日与月都为双数，可在单数前自动补0，成为两位数。
=MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)
3、输出格式为 20110101 格式
=IF(LEN(A1)=15,"19"&MID(A1,7,2)&MID(A1,9,2)&MID(A1,11,2),MID(A1,7,4)&MID(A1
,11,2)&MID(A1,13,2))
注意：
1、身份证号前不能有空格，如果有空格，就将身份证号复制到文本文档中（txt），
在有空格的身份证号前会有小黑块，将这个小黑块复制，用于“替换”，粘贴于“查找
内容”一栏，然后确认“替换为”一栏无任何内容，然后替换，就可发现，小黑块都不
见了，然后再将这些身份证号码复制，粘贴于原身份证号码一栏，就可以了运用以上公
式了。
2、如果要用“2012-02-19”这类月和日为两位数的格式，选定已弄好的出生日期
——右键，设置单元格格式——数字——自定义——可选定相似的一类进行修改或自行
输入小写的yyyy-mm-dd.，然后确定，就可以了。
3、运用公式后，如果“出生年月”一栏没有可以对应的“身份证号码”，那么，这
一栏出生日期里会显示“#VALUE!”这一标记，删除或清空即可，不会有影响。
二、确定“性别”：
每个人的性别可以利用“身份证号码”进行判断，18位身份证号码中，
第15～17位为顺序号，奇数为男，偶数为女, 最后一位为X也可以用此公
式。
在单元格中输入函数公式：
=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),"女","男")
三、如何让excel中两列数据互相找相同字段自动对齐？？
例：
1、把 列二 的文本字段按照 列一 文本字段的顺序排列， 列二 或 列一 中没
有的字段可以排在最后）；
2、同时 列二 和 分数 的数据仍然要对应。
之前的效果： 对齐后的效果：
列一 列二 分数 列一 列二 分数
甲 乙 90 甲 甲 79
乙 甲 79 乙 乙 90
丙 戊 86 丙 丙 97
丁 丙 97 丁 丁 69
戊 丁 69 戊 戊 86
己 己
1、选中第一列——工具——选项——自定义序列——导入——确定（excel 2003版）
2、选中第二、第三列——数据——排序——选项——在自定义排序序列里选择刚才添
加的序列——“排序选项”确定——“排序”确定
3、注意：最后一个要确保“主要关键字”下拉菜单里是名字，而不是数字，不然不能
运行成功，如果是列一第一个名字，那么在运行第一个就是列一第一个名字，如果不是，
在运行后，删除第一个，以后顺序就会正确如果数据较多，特别是身份证号较多的，应
注意重名的，将会向下多占位置，需自行检查选出正确的删除错误的；若列一、列二对
照后名字有出入的，列一不变，列二上的位置由下一个数据顶替，需自行插入数据。
4、注意：自定义序列最多可弄300个左右，因此，如果数据太多，没有全部自动运用
本公式，可先标记最后正确的一栏，然后从错误的那一栏开始，重复1、及2、，列一需
选剩余的300个左右，列二需选剩余的全部，运行完成后，可检查对照一下是否运行成
功。
四、在excel是根据出生日期、身份证自动填充年龄？
1、根据 出生日期 自动填充年龄，本篇中的三种出生日期格式都可用此公式。
假设A列从A1开始是出生日期，在B列中进行转换，则在B1中输入
=INT((TODAY()-A1)/365)
2、根据 身份证号码 自动填充年龄。
假设A列从A1开始是身份证号，在B列中进行转换，则在B1中输入
=YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4))
然后再向下复制即可。
六、数字转为日期
1、假设：20110216在单元格A1，运用下面公式可得 2011-2-16
=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))
2、假设：20110216在单元格A1，运用下面公式可得 2011-02-16
=MID(A1,1,4)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2)
第二步，将此列复制—选择性粘贴—数值，就可以了，如果会出现“
#VALUE!
”，则复制
—粘贴—选择性粘贴－数值，应该就可以了。
第三步，将中间的短横线替换一次，替换时还是替换成短横线“-”，或者替换成斜线“/”
也行，不管你在第一步中是英文状态输入还是中文状态输入都要替换一次。
第四步，设置为日期格式，将它设置为你所需要的格式，并且可以进行日期计算。
四、年龄段筛选
H2 为图中第一个年龄所在的位置
H5895为图中最后一个年龄所在的位置
一、在运用时，需自行更改公式中的F2及F8。（需要知道年龄）
1、
计算 35岁及以下 的人数，单元格中输入
=COUNTIF(F2:F8,"<=35")
2、
计算 36至45岁 的人数，在单元格中输入（大的数字在前）
=COUNTIF(F2:F8,"<=45")-COUNTIF(F2:F8,"<36")
3、
计算 46至60岁 的人数，在单元格中输入（大的数字在前）
=COUNTIF(F2:F8,"<=60")-COUNTIF(F2:F8,"<46")
4、
计算 60岁以上 的人数，在单元格中输入
=COUNTIF(F2:F8,">60")
二、运用身份证号码编年龄段等（如成年人、未成年人等）
1、对身份证号码编个序号。
2、数据—排序—“主要关键字” 身份证号 —确定，数据将按照身份证号大小排序，
按照身份证里的出生年月即可分出年龄段，做好需要设置的标记。
3、标记号以后，按照数据—排序—“主要关键字” 序号 —确定，可恢复原有顺序，
最后将序号删除即可。
版权声明：本文标题：EXCEL中运用身份证号码常用操作收集整理 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/b/1711003586a583698.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论