EXCEL中运用身份证号码常用操作收集整理.docVIP

EXCEL中运用 身份证号码 常用操作收集整理 （自己运用过） 一、从18位身份证号码中提取出生日期=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、输出格式格式=IF(LEN(A1)=15,19MID(A1,7,2)MID(A1,9,2)MID(A1,11,2),MID(A1,7,4)MID(A1,11,2)MID(A1,13,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是根据身份证自动填充年龄？ 假设A列从A1开始是，在B列中进行转换，则在B1中输入根据 身份证自动填充年龄假设A列从A1开始是身份证号，在B列中进行转换，则在B1中输入=YEAR(TODAY())-IF(LEN(A1)=15,19MID(A1,7,2),MID(A1,7,4)) 然后再向下复制即可。 =DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2)) 2、假设单元格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)-CO