|
凤凰山写生 前言二代身份证号码由18位数字组成，大家都知道每个数字的含义吗，我们能从身份证号码上获取什么信息呢？今天老崔就给大家讲一下有关身份证的几个秘密？ 1身份证号码的输入身份证为18位数，当直接在单元格里输入时后3位会自动变成0,这是因为EXCEL的数字精度为15位，超过15位数显示0，且无法显示原数字 超过12位的数字，系统会自动将其转换为科学记数法。 超过15位的数字，系统会自动将15位以后的数字转换成0。 解决方法： 方法一：输入前，将单元格格式设置为文本格式 方法二：先输入英文状态下的单引号“’”,然后接着输入身份证号，系统将强制将数值转化成文本。此方法不影响显示和打印。 2身份证号码编码规则前6位为地址代码，表示省、市、区，这里不做具体分析 第7~14位为8位数出生年月日，年4位，月2位，日2位 第15~17位为顺序码，奇数为男性，偶数为女性 第18位，也就是最后一位为校验码，我们重点讲一下检验码。
校验码是算出来的，以刚才输入的身份证为例：370613201704011231 我们看一下校验码如何计算： 第一步：首先计算身份证前17位数字各个数字与因数的成绩之和： E21=SUMPRODUCT(D2:D18,E2:E18) 其中因数为固定值，区域为E2:E18,用身份证的每个数字乘以各自的因数，再加起来求和,如同下列运算： 第二步：计算余数 用刚才的计算的数除以11，计算余数 E22=MOD(E21,11)，结果为8 第三步：根据余数查找对应的校验码 E23=VLOOKUP(E22,H2:I12,2,0)，结果为4 余数与校验码的对应关系见H1:I12的范围 最后我们算出来的校验码为4，而刚才输入的身份证最后一位为1，说明此身份证为假的！ 还等什么？快快拿出你的身份证算算吧！ 3提取生日B2=MID(A2,7,8) 用MID 函数从第七位开始取8位数，就是出生年月日，但是是文本格式 如果想变成日期格式可以这样写公式 用MID 函数分别把年月日取出来，再用DATE(年,月,日)函数整合起来形成日期格式 4提取性别可以根据第17位是奇数或偶数判断 先用MID(A1,17,1)把第17位数字取出来 用MOD(MID(A2,17,1),2)，计算能否被2整除 再用IF判断，如果能被2整除，说明为偶数，则性别为女，否则为男。 总结mid(字符串，开始位置，长度)：取中间字符 mod(被除数，除数）：计算余数 date(年，月，日)：日期函数 vlookup（查找值，查找区域，返回第几列，0/1）:查询函数 sumproduct(数组1，数组2，数组3)：数组成绩之和 END |
|
|
