利用VBA从身份证号码提取性别

合集下载

在Excel中自动提取身份证中生日和性别

在Excel中自动提取身份证中生日和性别

在Excel中自动提取身份证中生日和性别
1.转换身份证号码格式
我们先将学生的身份证号完整地输入到Excel2003表格中,这时默认为“数字”格式(单元格内显示的是科学记数法的格式),需要更改一下数字格式。

选中该列中的所有身份证号后,右击鼠标,选择“设置单元格格式”。

在弹出对话框中“数字”标签内的“分类”设为“文本”,然后点击确定。

2.“提取出”出生日期
=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"
/判断性别“男女”
=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")
求年龄问题,要看你的数据是哪种格式(日期、文本),举例说明:假定你的A1单元格为日期格式,内容为“1986-2-20”,在B1单元格可
输入如下公式得到年龄:
=INT((TODAY()-A1)/365)。

用Excel从身份证号码中提取信息(年龄、性别、出生地)

用Excel从身份证号码中提取信息(年龄、性别、出生地)

用Excel从身份证号码中提取信息(年龄、性别、出生地)1.出生年月日信息提取:方法一:在记录列中输入公式:=--TEXT(MID(B2,7,6+IF(LEN(B2)=15,0,2)),"#-00-00"),往下复制,无论15位还是18位身份证号码全部搞定,方法最简单。

方法二:在记录列中输入公式:=--IF(LEN(B2)=15,TEXT(MID(B2,7,6),"##-00-00"),TEXT(MID(B2,7,8),"####-00-00")),往下复制,无论15位还是18位身份证号码全部搞定,公式增加了几个字符,原理差不多,结果一致。

原理:使用函数text、if、mid、len。

注意:1、B列存放身份证号码。

存放在其它列,则在公式中作相应调整。

2、计算出错(#V ALUE!),说明身份证号码有错。

3、日期显示格式,可在单元格格式中设置。

性别信息提取:在记录列中输入公式:=IF(LEN(B2)=15,IF(MOD(RIGHT(B2),2)=0,"女","男"),IF(MOD(LEFT(RIGHT(B2,2)),2)=0,"女","男"))无论15位还是18位身份证号码全部轻松完成。

原理:使用函数IF、LEN、MOD、LEFT、RIGHT。

注意:1、B列存放身份证号码。

存放在其它列,则在公式中作相应调整。

2、计算出错(#V ALUE!),说明身份证号码有错。

出生地信息提取:在记录列中输入公式:=LEFT(B2,6),往下复制,然后根据代码用VLOOKUP查询发证地或者是出生地信息。

Excel文件模板:从身份证号码中提取信息使用的模板:使用Excel从身份证号码提取信息.xls点击该图标,打开该EXCEL文件,另存为××文件,即可使用。

谢谢你的使用。

Excel中根据身份证号码自动提取信息

Excel中根据身份证号码自动提取信息

Excel中根据身份证号码自动提取信息在excel表格中,根据身份证号码就可以自动提取出生年月、性别、年龄。

第一,身份证号码的组成当今的身份证号码由18位数字组成:前6位为地址码,第7至14位为出生日期码,第15至17位为顺序码,第18位为校验码。

15~17位为顺序号,其中第17位如果是单数为男性分配码,双数为女性分配码。

即通过第17位数字,可以判断性别,奇数为男,偶数为女。

下面以实例的方式讲解如何根据身份证号码自动提取出生年月、性别、年龄。

首先,在excel中输入以下表格,实例如下面所示。

A BCD E姓名身份证号码出生日期性别年龄张三5103211972050307861972年05月03日女37第二,根据身份证号码自动提取出生年月在目标单元格输入公式:=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日",回车确定,单元格内会出现与身份证号码相关的出生日期。

其它单元格的填充,不必一一输入公式,只需选中该单元格复制,在其它单元格内粘贴即可。

提示:MID函数是提供的一个“从字符串中提取部分字符”的函数命令。

第三,根据身份证号码自动提取性别的方法在目标单元格输入公式:=IF(MOD(RIGHT(LEFT(B2,17)),2),"男","女")提示:IF 函数:根据逻辑表达式测试的结果,返回相应的值。

IF函数允许嵌套。

RIGHT函数:根据所指定的字符数返回文本串中最后一个或多个字符。

第四,根据身份证号码自动提取年龄的方法在目标单元格输入公式:=DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),"#-00-00"),NOW(),"y"),回车确定。

Excel问答集锦-1(87个问答)

Excel问答集锦-1(87个问答)

Excel问答集锦-11、从身份证号码中提取性别Q:A1单元格中是15位的身份证号码,要在B1中显示性别(这里忽略15位和18位身份证号码的判别)B1=if(mod(right(A1,1),2)>0,"male","female")请问这个公式有无问题,我试过没发现问题。

但在某个网站看到作者所用的是如下公式:B1=if(mid(A1,15,1)/2=trunc(mid(A1,15,1)/2),"female","male")A:leaf道理都是一样的,不过你的公式比那个公式优质提取性别(无论是15位还是18位)=IF(LEN(A1)=15,IF(MOD(MID(A1,15,1),2)=1,"男","女"),IF(MOD(MID(A1,17,1),2)=1,"男","女"如果身份证号的输入已是15或18位,用公式=IF(MOD(LEFT(RIGHT(A1,(LEN(A1)=18)+1)),2),"男","女"2、xls--->exe可以么?A:Kevin如果只是简单的转换成EXE,当然可以。

如果你指的是脱离Excel也可以运行,好像没听说过可以。

当然,通过DDE,是可以不运行Excel但调用它的所有功能的,但前提仍然是你的计算机上已经安装了Excel3、列的跳跃求和Q:若有20列(只有一行),需没间隔3列求和,该公式如何做?前面行跳跃求和的公式不管用。

A:roof假设a1至t1为数据(共有20列),在任意单元格中输入公式:=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1)) 按ctrl+shift+enter结束即可求出每隔三行之和。

跳行设置:如有12行,需每隔3行求和=SUM(IF(MOD((ROW(1:12)),3)=0,(A1:A12)))4、能否象打支票软件那样输入一串数字它自动给拆分成单个数字?Q:如我输入123456.52它自动给拆成¥1 2 3 4 5 6 5 2 的形式并且随我输入的长度改变而改变?A:Chiu我所知函数不多,我是这样做的,如有更方便的方法,请指点例如:在A1输入小写金额,则:千万:B1=IF(A1>=10000000,MID(RIGHTB(A1*100,10),1,1),IF(A1>=1000000,"¥",0))百万:C1=IF(A1>=1000000,MID(RIGHTB(A1*100,9),1,1),IF(A1>=100000,"¥",0))十万:D1=IF(A1>=100000,MID(RIGHTB(A1*100,8),1,1),IF(A1>=10000,"¥",0))万:E1=IF(A1>=10000,MID(RIGHTB(A1*100,7),1,1),IF(A1>=1000,"¥",0))千:F1=IF(A1>=1000,MID(RIGHTB(A1*100,6),1,1),IF(A1>=100,"¥",0))百:G1=IF(A1>=100,MID(RIGHTB(A1*100,5),1,1),IF(A1>=10,"¥",0))十:H1=IF(A1>=10,MID(RIGHTB(A1*100,4),1,1),IF(A1>=1,"¥",0))元:I1=IF(A1>=1,MID(RIGHTB(A1*100,3),1,1),IF(A1>=0.1,"¥",0))角:J1=IF(A1>=0.1,MID(RIGHTB(A1*100,2),1,1),IF(A1>=0.01,"¥",0))分:K1=IF(A1>=0.01,RIGHTB(A1*100,1),0)网客公式中最后一个0改为""5、如何编这个公式Q:我想编的公式是: a/[84 - (b×4)]其中a是一个数值,小于或等于84;b是包含字符C的单元格的个数;C是一个符号。

从身份证号提取信息、编号、自动插入照片公式及VBA编程

从身份证号提取信息、编号、自动插入照片公式及VBA编程
6 12 女 牛 双鱼座 贵州省 2021/4/3 6 3.5 6 1 2 3 60% 潘金莲 点此返回 贰佰贰拾壹元零伍分 3 65 105
公式集锦
31
蜻蜓 蝴蝶 青蛙 78 80 95 35
41
42
150 14 6 2
中 良 优 差
利用VBA编写宏代码 根据数据的有效性 弹出的姓名在指定 单元格插入照片。代码
序号
公式
1
2
3
4 522428200903182 522428260205903182 522428260205903182 522428260205903182 522428260205903182 522428260205903182 522428260205903182 522428260205903182 522428260205903182 522428260205903182 522428260205903182
加班
利用身份证号提取出生日期6
70
利用身份证号提取出生日期7 利用身份证号提取出生日期8 利用身份证号计算年龄
65 B26 60 B27 90 用
年龄实岁(到计算当天)
80
利用身份证号提取男女
121.00
利用身份证号提取生肖 利用身份证号提取星座 利用身份证号提取省份
30.00
50.00
B31 用
20.05
插入当前日期(将该单元格设置为自己所需的日期格式)
221.05
将一组数据排名次
30 区间
去掉一个最高,一个低分计算平均分(所需数据如D3-D8)
35 40 65
返回一组数的最大值(所需数据如D3-D8)
53
返回一组数的最小值(所需数据如D3-D8)

身份证号自动生成年龄、出生年月、性别公式

身份证号自动生成年龄、出生年月、性别公式

身份证号自动生成年龄、出生年月、性别公式=MID(E4,8,4)&"/"&MID(E4,12,2)&"/"&MID(E4,14,2)(2011-07-28 17:54:56)转载▼身份证号自动生成年龄、出生年月、性别公式:=IF(LEN(C2)=15,"19"&MID(C2,7,2)&"-"&MID(C2,9,2)&"-"&MID(C2,11,2),MID (C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2))=DATE(MID(I4,IF(LEN(I4)=18,7,7),IF(LEN(I4)=18,4,2)),MID(I4,IF(LEN(I4 )=18,11,9),IF(LEN(I4)=18,2,2)),MID(I4,IF(LEN(I4)=18,13,11),IF(LEN(I4 )=18,2,2)))1、生成出生年月日公式一(18位号和15位号都可自动生成 19**-*-*):=DATE(MID(I4,IF(LEN(I4)=18,7,7),IF(LEN(I4)=18,4,2)),MID(I4,IF(LEN(I4)=18, 11,9),IF(LEN(I4)=18,2,2)),MID(I4,IF(LEN(I4)=18,13,11),IF(LEN(I4)=18,2,2)))生成出生年月日公式二(18位或15位号生成19**-**-**):=IF(LEN(C2)=15,"19"&MID(C2,7,2)&"-"&MID(C2,9,2)&"-"&MID(C2,11,2),MID (C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2))说明:自动生成出生年月就把以上公式复制粘贴到需自动生成“出生年月日”的那一栏第一格,然后把公式一中的所有“I4”或公式二中的所有“C2”改成身份证号码单元格的栏代码如A、B、C、D……和行数如1、2、3、4……。

从身份证号码中自动生成性别和生日

从身份证号码中自动生成性别和生日

从身份证号码中自动生成性别和生日生成性别:(其中B2是身份证号码所在列)一性别双击性别所在列的第二行,然后输入下面公式,然后按ENTER键;再利用下拉方式将公式复制到该列的其他行中即可1=CHOOSE(MOD(IF(LEN(B2)=18,MID(B2,17,1),IF(LEN(B2)=15,RIGHT(B2,1),"")),2)+1,"女","男")2=IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女")3=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女"))二出生日期提取出生日期:(其中B2是身份证号码所在列)双击出生日期所在列的第二行,然后输入下面公式,然后按ENTER键;再利用下拉方式将公式复制到该列的其他行中即可=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))三计算年龄:(其中C3是出生日期所在列)双击年龄所在列的第二行,然后输入下面公式,然后按ENTER键;再利用下拉方式将公式复制到该列的其他行中即可=YEAR(NOW())-YEAR(C3)Excel自动从身份证中提取生日性别出处:天空软件作者:佚名日期:2009-09-16每年新入学的一年级学生,都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格,以备建立全省统一的电子学籍档案。

数百个新生,就得输入数百行相应数据,这可不是个轻松活儿。

有没有什么办法能减轻一下输入工作量、提高一下效率呢?其实,我们只需在Excel2003中将学生的身份证号完整地输入后,它就可以帮我们自动填好出生日期和性别。

EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)

EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)

EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考:说明:公式中的B2是身份证号1、根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),"身份证错"))2、根据身份证号码求出生年月日:=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2, 9,2)),IF(LEN( B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11,2) ),"身份证错"))3、根据身份证号码求年龄:=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year (now())-VALUE(MID(B2,7,4)),"身份证错"))其他通用公式:(新旧身份证号通用)(1)、根据“身份证号”获得“年龄”A1为身份证号,B1中输入公式=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=1 8)*2),"#-00-00"), TODAY(),"y")(2)、根据出生“年-月-日”获得“年龄”B1为出生日期,C1中输入公式=INT((TODAY()-B1)/365)或者=DATEDIF(B1,TODAY(),"Y")判断身份证格式是否正确:B1为身份证号,C1中输入公式=IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MON TH(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1* (MID(B1,11,2)),DAY(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1 * (MID(B1,13,2))))<>1*(MID(B1,13,2))),"月日错误",""),IF(LEN(B1)=15,IF(OR(MONTH(DATE(1*(MID(B1,7,2))+19 00,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,9,2)),DAY(DATE(1* (MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1* (MID(B1,11,2))),"月日错误",""))),"身份证位数不对")&IF(AND(LEN(B1)=18,1*MID(B1,7,4)>YEAR(TODAY())-6),"年份错误","")。

  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
相关文档
最新文档