在Excel中输入身份证号码及提取信息【会计实务经验之谈】.doc

从业二十年的老会计经验之谈，如果觉得有帮助请您打赏支持，谢谢!在Excel中输入身份证号码及提取信息【会计实务经验之谈】一、解决输入身份证号码的问题默认情况下 在Excel当前单元格中输入的数字位数如果超过11位(不含11位)时，系统将以“科学记数”格式显示输入的数字;当输入的数字位数超过15位(不含 15位)时，系统将15位以后的数字全部显示为“0”。这样一来，如果我们要输入身份证号码(15位或18位)，身份证号码就不能正确显示出来了。但我们 有两种办法解决此问题：1、利用数字标签选中需要输入身份证号码的单元格(或区域)，打开“单元格格式”对话框，进入“数字”标签，在“分类”下面选中“文本”选项，确定退出即可。2、在输入身份证号码时，在号码前面加上一个英文单引号“´”，即可让身份证号码完全显示出来(该单引号不显示)。二、让单元格内自动添加身份证前面相同部分身份证号码的前6位，是省、市、区(县)的代号(如资中县：511025)，如果持证人都是资中县的，输入这样的的身份证号码，可以让单元格格式的“数字”标签帮你减轻输入的工作量：选中需要输入身份证号码的单元格区域，单击Excel上面菜单栏的格式打开“单元格”对话框(或者右键点击设置单元格格式)，进入默认的“数字”标签，在“分类”下面选中最下面的“自定义”选项，然后在右侧“类型”下面的方框中输入：”511025”，确定。以后输入身份证号码9位及以后的数字(如“197303246211”)后，系统会自动在前面加上51102519，形成完整的身份证号码(如“511025197303246211”)。其它需要输入数字含相同部分的也可按照这个方法输入。Excel从身份证号码中提取个人信息如18位身份证号码：前6位是公民户口登记地，第7到14位代表出生日期，第17位代表性别，奇数为男，偶数为女。 (15位身份证号码15位数代表性别)比如，某员工的身份证号码(18位)是320521197208170241，那么表示1972年8月17日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错。提取个人信息 需要使用IF、LEN、MOD、 MID、DATE等函数。某表格，其中员工的身份证号码信息已输入到C列，出生年月信息填写在D列，性别信息填写在B列。1. 提取出生年月信息由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15‚MID(C2‚7‚4)‚MID(C2‚9‚4)”，其中：LEN(C2)=15：检查C2单元格中字符串的字符数目是否等于15，意即检查身份证号码的长度是否是15位。MID(C2‚7‚4)：从C2单元格中字符串的第7位开始提取四位数字，即提取15位身份证号码的第7、8、9、10位数字。MID(C2‚9‚4)：从C2单元格中字符串的第9位开始提取四位数字，即提取18位身份证号码的第9、10、11、12位数字。IF(LEN(C2)=15‚MID(C2‚7‚4)‚MID(C2‚9‚4)：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的四位数字，否则就从第9位开始提取四位数字。如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式日期”中进行设置。2. 提取性别信息由于报表中各位员工的序号编排是按照上级核定的编制进行的，因此不可能按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容易出错例如性别信息统一在B列填写，可以在B2单元格中输入公式“=IF(MOD(IF(LEN(C2)=15‚MID(C2‚15‚1)‚MID(C2‚17‚1)‚2)=1‚”男”‚”女”)”，其中：LEN(C2)=15：检查身份证号码的长度是否是15位。MID(C2‚15‚1)：如果身份证号码的长度是15位，那么提取第15位的数字。MID(C2‚17‚1)：如果身份证号码的长度不是15位，即18位身份证号码，那么应该提取第17位的数字。MOD(IF(LEN(C2)=15‚MID(C2‚15‚1)‚MID(C2‚17‚1)‚2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。IF(MOD(IF(LEN(C2)=15‚MID(C2‚15‚1)‚MID(C2̵8;17‚1)‚2)=1‚”男”‚”女”)：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。回车确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。如图2所示，现在这份报表无论是提取信息或是核对，都方便多了!小编寄语：不要指望一张证书就能使你走向人生巅峰。考试只是检测知识掌握的一个手段，不是目的。千万不要再考试通过之后，放松学习。财务人员需要学习的有很多，人际沟通，实务经验，excel等等等等，都要学，都要积累。要正确看待考证，证考出来，可以在工作上助你一臂之力，但是绝对不是说有了证，就有了一切。有证书知识比别人多了一个选择。会计学的学习，必须力求总结和应用相关技巧，使之更加便于理解和掌握。学习时应充分利用知识的关联性，通过分析实质，找出核心要点。