|
在知晓如何使用Excel此技巧前，我们需要了解一下身份证的编码原理 1．号码的结构 公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。 2．地址码 表示编码对象常住户口所在县（市、旗、区）的行政区划代码，按GB/T2260的规定执行。 3．出生日期码 表示编码对象出生的年、月、日，按GB/T7408的规定执行，年、月、日代码之间不用分隔符。 4．顺序码 表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。 5．校验码 作为尾号的校验码，是由号码编制单位按统一的公式计算出来的，如果某人的尾号是0-9，都不会出现X，但如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位，而19位的号码违反了国家标准，并且中国的计算机应用系统也不承认19位的身份证号码。Ⅹ是罗马数字的10，用X来代替10，可以保证公民的身份证符合国家标准。 作为一名HR，常常会遇到只提供了人员姓名和身份证号码的原始信息，我们可以根据身份证号码的编码原理，从而在简单原始信息中整理出丰富的人员信息表。接下来我们便从函数概念理解，单个运用，具体嵌套运用来学习这一实用的技巧。 常用的函数概念及示例 MID MID(字符串,开始位置,字符个数) 它是一个字符串函数，作用是从一个字符串中截取出指定数量的字符。这个函数的3个参数在Excel中“字符串”参数不可“开始位置”参数小于1时，函数返回一个错误；当“开始位置”值大于字符串长度，返回空值；若截取长度超出字符串末尾，或字符个数参数被省略，则返回开始位置到字符串结尾的所有字符。 示例 mid('abcde12345',3,4) ='cde1' MOD MOD(数值,除数) 这是一个求余函数，其格式为mod(数值,除数)，即是两个数值表达式作除法运算后的余数。特别注意：在EXCEL中，MOD函数是用于返回两数相除的余数，返回结果的符号与除数的符号相同。 示例 mod(17,3)=2 即数学式 17/3=5mod(2) TEXT TEXT(值,数值格式) 此函数较为繁杂，简单的描述即为将某一值由一种格式，转为我们需要显示的格式 示例 Text(19920201,'00000年00月00日0000年00月00日0000年00月00日0000年00月00日0000年00月00日0000年00月00日0000年00月00日0000年00月00日') =1992年02月01日 DATEDIF DATEDIF(开始日期,终止日期,比较单位) 返回两个日期之间的年月日间隔数。常使用DATEDIF函数计算两日期之差。比较单位一般为Y ,M,D,即为年，月，日的比较。 示例 datedif('1993/2/1', '1994/2/1','m')=12 EDATE EDATE(开始日期,月数) 返回表示某个日期的序列号，该日期与指定日期相隔(之前或之后)，月数为正，表示未来日期,月数为负表示过去日期。 示例 edate('1993/2/1',8) =1993/10/1 LEN LEN(字符串) 该函数比较简单，计算字符串的个数 示例 len('abcde12345')=10 IF IF(测试条件,真值,假值) IF函数异常强大灵活，内容丰富，是嵌套函数的常用函数，详细用法可参考笔者另一篇文章《IF判断语句的用法》 示例 if(3>4,正确，错误)=错误 “人员信息表”操作实例 第一步 将原始数据中的错误身份证号码信息，用LEN函数筛选出来 身份证检验公式 IF(LEN(B2)=18,'号码正确','号码不为18位') 第二步 利用函数公式嵌套，得到相关的信息 性别公式 IF(MOD(MID(B2,17,1),2)=1,'男','女') 出生年月 00月00日TEXT(MID(00月00日00月00日00月00日00月00日),'0年00月00日') 年龄 DATEDIF(D2,TODAY(),'Y') 退休日期 TEXT(EDATE(D2,12*(IF(C2='男',60,55))),'yyyy/mm/dd aaaa') 第三步 整理图表 |
|
|
