|
最近一直在做表格，经常被如何从两个表格中筛选出同样一部分人的问题所困扰。刚开始的时候，我是把两个表格排序、并且并排比较得出相同的、差异的人，然后作出颜色标记。可随着表格行数的增多，让我头昏眼花，而且每到此时差错率就会直线上升。经过朋友的提醒，我开始研究excel中vlookup的用法。经过反复验证，终于成功了。于是顿悟：懒惰产生创新。下面就把vlookup的用法记录下来，既可以提醒自己也可以方便他人。
例子：表1为薪酬表（其中包括，姓名、身份证号码等）；表2为参加医疗保险人员名单（其中包括，姓名、身份证号码等）。我需要做的工作，一是找出哪些人在领工资，但是却没有参加医疗保险；二是找出哪些人参加了医疗保险，但是却没有在本单位领工资。说明：两张表均是人数过千，并且数量不等。 表的基本结构： 薪酬表：A序号、B员工编号、C姓名、D责任中心名称、E身份证编号、F18位身份证（共1139行） 参保表：A姓名、B身份证号码、C类别、D18位身份证 步骤： 1． 利用公式将身份证号码统一为18位（15位身份证号码升为18位，18位的不变） 为了便于比较，将身份证号码统一省位为18位 方法： 假设E列自E2起是身份证号（15位或18位）。 =IF(LEN(E2)=15,REPLACE(E2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(E2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),E2) 注意：数组公式输入方法：输入公式后不要按回车，而是按Ctrl+Shift+Enter。 说明：15位升18位是有固定的算法的，如果结果和你要的不一样那很可能是公安局或者填表人员在填写原身份证号码时误填了。
2．在参加医保人员名单中查找与薪酬表中姓名一样的人员，并显示该名员工在参加医疗保险名单中所列示的18位身份证号码 =VLOOKUP(C2:C1139,参保表!A:D,4,0)
注： “C2:C1139”代表薪酬表中的全部人员姓名； “参保表!A:D”代表在参保表的A至D列搜索是否有人与薪酬表中的人员姓名完全吻合； “4”代表显示参保表 中A至D列的第4列单元格中的内容，即与在参保表中“18位身份证号码”一列中，与薪酬表姓名吻合的人员的18位身份证号码； “0”代表模糊判断（这个我也没太搞清楚，不过一般都要写“0”或“false”）
3． 判断薪酬表中的18位身份证号码是否与步骤2中查到的18位身份证号码相等 =IF(F7=G7,1)
4． 检查步骤3中没有显示数字“1”的人员 可能有三种情况 1） 原始的两个表中记录的身份证号码至少有一个错误 2） 原始的两个表中记录的人员可能存在重名 3） 原始的两个表中记录的人员姓名可能书写错误 注：到这步剩余的人数已经非常少了
5． 根据步骤4的结果手工判断是哪种可能性 |
|
|
