Excel技巧–重名检索.doc

EXCEL:重名检索与姓氏频率统计(例如500人左右)的机关、团体、单位的人事管理部门，或者户籍管理部门，都会遇到重名的问题。例如笔者所在单位783人就有12人6对重名。在用EXCEL电子表格制作各类管理文件时重名会带来很多问题(例如以姓名作参数用VLOOKUP函数，来查找该人的信息时就会出错)。因此有一个方便快速的重名检索办法就十分必要。(笔者根据经验建议凡用EXCEL电子表格进行办公业务自动化管理的单位，应给每个人设立一个代码，像居民身份证号码一样是终身的唯一的，不要把调离、退休等人员的代码用于新增人员)。 　　方法如下： 　　先制作空表格：把本文所附的只有2行的表头打开，下拉菜单“编辑”、点击“定位”在引用位置栏输入“B2:H1001”、按“确定”、再下拉菜单“编辑”、点击“填充”、“向下填充”空表格制作完成。(这是大量填充单元格的最快方法)。然后把姓名清单从A2单元格开始拷贝至A列。这样检索程序操作就完成了。用该检索程序，在奔III 733机器上1秒钟内便完成了783人的重名检索。 　　下面简单介绍B2至H2单元格的公式，B2单元格“=IF(A2=0,0,SUBSTITUTE(A2, ,))”中SUBSTITUTE函数是去掉A2单元格中的名字的前、后、中间的空格，C2单元格“=IF(B2=0,0,IF(ISERROR(VLOOKUP(B2,B3:B$1001,1,FALSE))=TRUE,0,ROW(A1)))” 中ISERROR(VLOOKUP(B2,B3:B$1001,1,FALSE))=TRUE,0,ROW(A1))即如在B3到B1001单元格中找不到与B2相同的姓名时为零，否则为从第1个姓名开始计数的行数。意即B3往下有重名时标明行数，否则为零。D2单元格“=LARGE(C:C,ROW(A1))”就是把重名所在行的行数从大到小进行排列。E2单元格“=IF(D2=0,0,INDEX(B$2:B$1001,D2))”就是在B列根据D2单元格标明的行数查找重名的姓名。F2单元格“=IF(E2=0,0,IF(ISERROR(VLOOKUP(E2,E3:E$1001,1,FALSE))=TRUE, ROW(A1),0))”与C2单元格的公式相似，只是根据条件取舍相反。即让已检出的重名只出现一次。G2单元格的公式“=IF(ROW(A1)COUNTIF(F:F,0),0,INDEX(E$2:E$1001,LARGE(F:F,ROW(A1))))” 就是对F列标明的行数，按大到小进行排列并在E列查找重名的姓名。H2单元格的公式“=IF(G2=0,0,COUNTIF(B:B,G2))”就是对B列在G列列出的重名进行计数。　　　　　　　　　　　　 　　此程序稍作改变便能用来统计姓氏的频数与频率。 　? ont color= 　　先制作空表格：把本文所附的只有4行的表头打开，用上述方法填充B4:l1002单元格.再把姓名清单从A3单元格开始拷贝至A列。这样姓氏的频数与频率统计程序操作就完成了。 　　用该程序对笔者所在单位783人统计有160个姓氏，张姓最多有95人出现频率为12.1%。样本太少不具全国姓氏的频数与频率统计上的意义，但似乎张姓为中国第一大姓。B到F列的公式与重名检索工作表的公式极相似，G到K列的公式在笔者的“排序与筛选”一文中有详细说明。 　　笔者在奔III 733计算机上制作一张统计10000人姓氏频数与频率的空表需时6分37秒，复制这样一张空表瞬时就能完成，在空表上填充10000人的姓名后统计姓氏频数与频率的时间为2分42秒。填充完后文件大小为4996k。笔者所以测试以上时间是笔者有一个强烈的愿望：把程序用于全国千分之一到万分之一抽样人口即12万到120万人的姓氏频数与频率的统计。笔者在此请求网友支持，提供你能到的某一群体人员的姓氏或姓名样本，和所在省市。笔者每收集到1万个样本便在网站公布一次姓氏频数与频率的统计结果。