论文部分内容阅读
【摘 要】本文通过用Excel处理人事系统中的身份证号，提取出籍贯、年龄、性别等特定的个人信息，丰富了Office办公软件在日常工作和生活中的应用。
【关键词】Excel2013；人事系统；身份证号
一.引言
无论是企事业单位，还是公司，在人事工作方面，经常会遇到身份证信息提取的相关问题。目前中国大陆已停止使用一代身份证，广泛使用二代身份证。二代身份证共18位号码，比较于一代身份证，二代身份证补足了四位的年号日期，并在末尾添加了一位校验字符。
二.身份证号的主要信息特征
二代身份证号码格式如下：前两位表示证件所属省份代码，如“13”代表河北省。3至6位表示证件所属城市、区（县）代码，如“0283”代表迁安市。7至14表示证件所有人的出生日期，格式为“yyyymmdd”，如19860118。15至17位是个人分配顺序码，其中第17位标识性别，奇数表示男性，偶数表示女性。第18位是校验码，由计算机根据一定规则的运算产生，目的是检测身份证号码正确与否。
三.Excel2013在处理身份证号中的应用
（一）.正确输入身份证号码
Excel单元格最多只支持15位有效数字，当输入18位的身份证号时，若不设置单元格格式，会默认按数值处理，后三位会自动转换成0，这样会导致身份证号码输入错误。有两种方法可以避免这种错误。
方法一：在输入号码前，先输入一个半角的单引号，再输入身份证号码，这样身份证号会按文本型数据处理。
方法二：先将要输入身份证的单元格区域的格式设置为文本类型，再输入身份证号码。但如果先完成输入再改单元格格式，仍会造成后三位自动转换成0。接下来对身份证号码信息的提取，都默认是按第二种方法输入的。
（二）.提取省份区县
步骤1：使用LEFT函数提取身份证号的前6位。
= LEFT（A2，6）
步骤2：使用VLOOKUP函数，在“身份证号前6位编码表”中找出前6位编码所对应的省市区县，将结果显示在B2单元格。
=VLOOKUP（LEFT（A2，6），身份证前6位编码表！$A$1：$B$1988，2，FALSE）
敲回车完成输入，可以看到身份证号为130283198601183592的人，籍贯是河北省迁安市。
（三）.提取出生日期
在身份证数据信息提取时，经常会用到从身份证号里提取出生日期。出生日期位于18位身份证号码的7至14位，其中，7～10位表示年份，11、12位表示月份，13、14位表示日。
方法一：
步骤1：用MID函数分别提取7～10位、11-12位、13-14位。=MID（A2，7，4） =MID（A2，11，2） =MID（A2，13，2）步骤2：用date函数将年、月、日组成日期，即得出生日期。
操作如下：
选定C2单元格，在公式编辑栏输入“=DATE（MID（A2，7，4），MID（A2，11，2），MID（A2，13，2））”，敲回车完成输入。还可以选定C2单元格，右键“设置单元格格式”，更改日期类型的显示格式。如改成“1986年1月18日”。
方法二：
步骤1：应用MID函数提取身份证的7～14位。
=MID（A2，7，8）
步骤2：应用TEXT函数，将取出的8位字符转换为日期格式。
=TEXT（MID（A2，7，8），"0-00-00"）+0
（四）.计算年龄
在实际应用中还经常会用到根据身份证号求员工的年龄。如对上面身份证号计算年龄，有两种方法：
方法一：
用当前的年份减去身份证中提取的年份。公式如下：
=YEAR（TODAY（））-MID（A2，7，4）
结果为28。
但是这样计算年龄并不总是精确，比如，一个2013年12月31日出生的小朋友，在2014年1月1日出生仅两天，应用上述公式计算年龄却为1周岁。所以，为了更精确的计算年龄（周岁），应使用如下方法。
方法二：
用当前日期减去图3提取的“出生日期”，因为在Excel中，两个日期相减得出是两个日期间差的天数，所以，再除以365，最后应用INT函数对结果做向下取整，即得出实际年龄（周岁）。所用公式如下：
=INT（（TODAY（）-C2）/365）
（五）.提取性别
18位身份证号码第17位奇数表示性别为“男”，偶数表示性别为“女”。所以要提取性别，可采用如下步骤。
步骤1：应用MID函数提取第17位。
= MID（A2，17，1）），2）
步骤2：应用MOD函数将步骤1取出的第17位除以2取余数。
= MOD（MID（A2，17，1），2）
步骤3：应用IF函数，对上述结果进行判断，余数是1输出“男”，0输出“女”。
=IF（MOD（MID（A2，17，1），2），”男"，"女"）
或者，通过以-1为底的指数来进行判断，如果结果为-1，说明其指数是奇数，为1说明指数是偶数。所用公式如下：
=IF（-1^MOD（MID（A2，17，1），2）=-1，”男"，"女"）
四.结论
Excel2013是微软最新的Office办公套装软件的一个重要的组成，它能够进行除了身份证号之外各种复杂数据的处理、分析统计等操作，在金融管理、财经统计、日常生活等众多领域都有应用。
【关键词】Excel2013；人事系统；身份证号
一.引言
无论是企事业单位，还是公司，在人事工作方面，经常会遇到身份证信息提取的相关问题。目前中国大陆已停止使用一代身份证，广泛使用二代身份证。二代身份证共18位号码，比较于一代身份证，二代身份证补足了四位的年号日期，并在末尾添加了一位校验字符。
二.身份证号的主要信息特征
二代身份证号码格式如下：前两位表示证件所属省份代码，如“13”代表河北省。3至6位表示证件所属城市、区（县）代码，如“0283”代表迁安市。7至14表示证件所有人的出生日期，格式为“yyyymmdd”，如19860118。15至17位是个人分配顺序码，其中第17位标识性别，奇数表示男性，偶数表示女性。第18位是校验码，由计算机根据一定规则的运算产生，目的是检测身份证号码正确与否。
三.Excel2013在处理身份证号中的应用
（一）.正确输入身份证号码
Excel单元格最多只支持15位有效数字，当输入18位的身份证号时，若不设置单元格格式，会默认按数值处理，后三位会自动转换成0，这样会导致身份证号码输入错误。有两种方法可以避免这种错误。
方法一：在输入号码前，先输入一个半角的单引号，再输入身份证号码，这样身份证号会按文本型数据处理。
方法二：先将要输入身份证的单元格区域的格式设置为文本类型，再输入身份证号码。但如果先完成输入再改单元格格式，仍会造成后三位自动转换成0。接下来对身份证号码信息的提取，都默认是按第二种方法输入的。
（二）.提取省份区县
步骤1：使用LEFT函数提取身份证号的前6位。
= LEFT（A2，6）
步骤2：使用VLOOKUP函数，在“身份证号前6位编码表”中找出前6位编码所对应的省市区县，将结果显示在B2单元格。
=VLOOKUP（LEFT（A2，6），身份证前6位编码表！$A$1：$B$1988，2，FALSE）
敲回车完成输入，可以看到身份证号为130283198601183592的人，籍贯是河北省迁安市。
（三）.提取出生日期
在身份证数据信息提取时，经常会用到从身份证号里提取出生日期。出生日期位于18位身份证号码的7至14位，其中，7～10位表示年份，11、12位表示月份，13、14位表示日。
方法一：
步骤1：用MID函数分别提取7～10位、11-12位、13-14位。=MID（A2，7，4） =MID（A2，11，2） =MID（A2，13，2）步骤2：用date函数将年、月、日组成日期，即得出生日期。
操作如下：
选定C2单元格，在公式编辑栏输入“=DATE（MID（A2，7，4），MID（A2，11，2），MID（A2，13，2））”，敲回车完成输入。还可以选定C2单元格，右键“设置单元格格式”，更改日期类型的显示格式。如改成“1986年1月18日”。
方法二：
步骤1：应用MID函数提取身份证的7～14位。
=MID（A2，7，8）
步骤2：应用TEXT函数，将取出的8位字符转换为日期格式。
=TEXT（MID（A2，7，8），"0-00-00"）+0
（四）.计算年龄
在实际应用中还经常会用到根据身份证号求员工的年龄。如对上面身份证号计算年龄，有两种方法：
方法一：
用当前的年份减去身份证中提取的年份。公式如下：
=YEAR（TODAY（））-MID（A2，7，4）
结果为28。
但是这样计算年龄并不总是精确，比如，一个2013年12月31日出生的小朋友，在2014年1月1日出生仅两天，应用上述公式计算年龄却为1周岁。所以，为了更精确的计算年龄（周岁），应使用如下方法。
方法二：
用当前日期减去图3提取的“出生日期”，因为在Excel中，两个日期相减得出是两个日期间差的天数，所以，再除以365，最后应用INT函数对结果做向下取整，即得出实际年龄（周岁）。所用公式如下：
=INT（（TODAY（）-C2）/365）
（五）.提取性别
18位身份证号码第17位奇数表示性别为“男”，偶数表示性别为“女”。所以要提取性别，可采用如下步骤。
步骤1：应用MID函数提取第17位。
= MID（A2，17，1）），2）
步骤2：应用MOD函数将步骤1取出的第17位除以2取余数。
= MOD（MID（A2，17，1），2）
步骤3：应用IF函数，对上述结果进行判断，余数是1输出“男”，0输出“女”。
=IF（MOD（MID（A2，17，1），2），”男"，"女"）
或者，通过以-1为底的指数来进行判断，如果结果为-1，说明其指数是奇数，为1说明指数是偶数。所用公式如下：
=IF（-1^MOD（MID（A2，17，1），2）=-1，”男"，"女"）
四.结论
Excel2013是微软最新的Office办公套装软件的一个重要的组成，它能够进行除了身份证号之外各种复杂数据的处理、分析统计等操作，在金融管理、财经统计、日常生活等众多领域都有应用。