在EXCEL中怎样用身份证号计算年龄,性别及出生年月、查重

1、在EXCEL中怎样用身份证号计算年龄假设身份证号码在A1单元格 B1单元格输入公式 =datedif(text(mid(a1,7,len(a2)/2-1),"0-00-00"),now(),"y") 此公式适用于15位和18位号码假设身份证号在A1单元格 都是18位的 在B1单元格输入公式 按月份=year(today()-year(-text(mid(A1,7,8),"0-00-00") 或 按年=datedif(-text(mid(A1,7,8),"0-00-00"),today(),"Y"

2、)在Excel中自动推测出生年月日及性别的技巧 大家都知道，身份证号码已经包含了每个人的出生年月日及性别等方面的信息（对于老式的15位身份证而言，7-12位即个人的出生年月日，而最后一位奇数或偶数则分别表示男性或女性。如某人的身份证号码为420400700101001，它的7-12位为700101，这就表示该人是1970年元月1日出生的，身份证的最后一位为奇数1，这就表示该人为男性；对于新式的18位身份证而言，7-14位代表个人的出身年月日，而倒数第二位的奇数或偶数则分别表示男性或女性）。根据身份证号码的这些排列规律，结合Excel的有关函数，我们就能实现利用身份证号码自动输入出生年月日及性别

3、等信息的目的，减轻日常输入的工作量。 Excel中提供了一个名为MID的函数，其作用就是返回文本串中从指定位置开始特定数目的字符，该数目由用户指定（另有一个名为MIDB的函数，其作用与MID完全一样，不过MID仅适用于单字节文字，而MIDB函数则可用于汉字等双字节字符），利用该功能我们就能从身份证号码中分别取出个人的出生年份、月份及日期，然后再加以适当的合并处理即可得出个人的出生年月日信息。 提示：MID函数的格式为MID(text,start_num,num_chars)或MIDB(text,start_num,num_bytes)，其中Text是包含要提取字符的文本串；Start_num是

4、文本中要提取的第一个字符的位置（文本中第一个字符的start_num为1，第二个为2以此类推）；至于Num_chars则是指定希望MID从文本中返回字符的个数。 假定某单位人员管理希望通过身份证号码自动得出员工的出生年月日及性别，现以此为例，将有关步骤向广大用户作一简要介绍： 1. 选中整个B列，然后执行“格式”菜单中的“单元格”命令，打开“单元格格式”窗口。 2. 选择“数字”选项卡。 3. 在“分类”栏中选择“文本”选项，然后单击“确定”按钮，关闭“单元格格式”窗口，将所有包含有身份证号码的单元格设置为文本格式。 4.将光标移至C3单元格中，然后输入“="19"&

5、;MID(B3,7,2)&"年"&MID(B3,9,2)&"月"&MID(B3,11,2)&"日"”内容。其中MID(B3,7,2)就是从身份证号码的第7位开始取2位数，得出该员工的出生年份，MID(B3,9,2)就是得出该员工的出生月份，而MID(B3,11,2)则是该员工的出生日期，这些信息再加上年、月、日等文字就会组成该员工的准确出生年月日“1970年1月1日”。 5. 接下来我们应将光标移至D3单元格中，然后输入“=IF(MID(B3,15,1)/2=TRUNC(MID(B3,15,1)

6、/2),"女","男")”。这就表示取身份证号码的第15位数，若能被2整除，这表明该员工为女性，否则为男性。 6. 最后我们只需利用自动填充功能对其他各个员工的出生年月日、性别进行填充即可。 上面都是以15位身份证为例进行介绍的，18位身份证的操作方法与此类似，广大用户若使用的是18位身份证，只需对有关函数的取值位置进行适当调整即可（如将“="19"&MID(B3,7,2)&"年"&MID(B3,9,2)&"月"&MID(B3,11,2)&&quo

7、t;日"”修改为“=MID(B3,7,4)&"年"&MID(B3,11,2)&"月"&MID(B3,13,2)&"日"”）。 这样就实现了由Excel自动填充员工出生年月日、性别的功能，从而极大地减轻了用户录入数据时的工作量，有兴趣的读者不妨一试。出生年月18位=DATE(MID(a2,7,4),MID(a2,11,2),MID(a2,13,2)15位=DATE(MID(e13,7,2),MID(e13,9,2),MID(e13,11,2) 或：=IF(LEN(TRIM(a2)=18,

8、(DATE(MID(a2,7,4),MID(a2,11,2),MID(a2,13,2),(DATE(MID(a2,7,2),MID(a2,9,2),MID(a2,11,2)同时，设置一下单元格格式为日期格式就正常了性别=if(mod(if(len(trim(a2)=15,mid(a2,15,1),mid(a2,17,1),2)=1,"男","女")年龄=DATEDIF(TEXT(LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00"),TODAY(),"y")

9、A2为身份证,A3输入 =DATEDIF(DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2),TODAY(),"y")根据第一列的不同类型让第二列每个类型自动从1编号的办法B2=countif($A$2:$A2,$A2)Excel四舍五入、不四舍五入或取整的相关函数ROUND函数1、四舍五入：ROUND(目标单元格，0)2、入：ROUNDUP(目标单元格，0) 得出来的结果是向上的整数，即：3.35=43、舍：ROUNDDOWN(目标单元格，0)得出来的结果是向下的整数，即：3.65=3*公式中后面那个零表示取整，如果是1，表示小数点后1位

10、，依次类推。TRUNC：数字截尾取整函数TRUNC(目标单元格，0)例：3.6=3 3.1=3*公式中后面那个零表示取整，如果是1，表示小数点后1位，依次类推。INT：取整函数(数字向下舍入到最接近的整数。这里的“向下”就是得到的结果比这个函数的参数要小，并且是整数。 )INT(目标单元格)如：INT(8.9)=8INT(-8.9)=-9因为8比8.9要小-9比-8.9要小。注：函数 TRUNC 和函数 INT 类似，都返回整数。函数 TRUNC 直接去除数字的小数部分，而函数 INT 则是依照给定数的小数部分的值，将其向下舍入到最接近的整数。函数 INT 和函数 TRUNC 在处理负数时有所不同：如：trunc:-1.7=-1-1.1=-1int:-1.7=-2-1.1=-2找同一列重复数据：输入公式：=IF(COUNTIF(A:A,A2)>1,"重复","")通配符查重公式（解决身份证号位数多不准确