Excel技巧-重名檢索

1、*EXCEL: 重名檢索與姓氏頻率統(tǒng)計(例如500 人左右)的機關、團體、單位的人事管理部門，或者戶籍管理部門，都會遇到重名的問題。例如筆者所在單位783人就有 12人 6對重名。在用 EXCEL 電子表格制作各類管理文件時重名會帶來很多問題 (例如以姓名作參數(shù)用 VLOOKUP 函數(shù)，來查找該人的信息時就會出錯) 。因此有一個方便快速的重名檢索辦法就十分必要。(筆者根據(jù)經(jīng)驗建議凡用EXCEL 電子表格進行辦公業(yè)務自動化管理的單位， 應給每個人設立一個代碼， 像居民身份證號碼一樣是終身的唯一的，不要把調離、退休等人員的代碼用于新增人員)。方法如下：先制作空表格：把本文所附的只有2 行的表頭打開

2、，下拉菜單 “編輯 ”、點擊 誼位”在引用位置欄輸入“B2:H1001；按 確定”、再下拉菜單 編輯”、點擊“填充 ” 、 “向下填充 ”空表格制作完成。(這是大量填充單元格的最快方法)。然后把姓名清單從A2 單元格開始拷貝至A 列。 這樣檢索程序操作就完成了。 用該檢索程序，在奔III 733 機器上 1 秒鐘內便完成了 783人的重名檢索。下 面 簡 單 介 紹 B2 至 H2 單 元 格 的 公 式 ， B2 單 元 格“ =IF(A2=0,0,SUBSTITUTE(A2," ","") 中 SU” BSTITUTE 函數(shù)是去掉A2 單元格中 的

3、名 字 的 前 、 后 、 中 間 的 空 格 ，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個姓名開始計數(shù)的行數(shù)。意即 B3 往下有重名時標明行數(shù)，否則為零。 D2 單元格“ =LARGE(C:C,ROW(A1”) 就是把重名所在行的行數(shù)從大到小進行排列。E2單元格“=IF(D2=0,0,IND

4、EX(B$2:B$1001,D2)就是在B列根據(jù)D2單元格標明 的 行 數(shù) 查 找 重 名 的 姓 名 。 F2 單 元 格 “=IF(E2=0,0,IF(ISERROR(VLOOKUP(E2,E3:E$1001,1,FALSE)=TRUE,ROW(A1),0) ”與C2單元格的公式相似，只是根據(jù)條件取舍相反。即讓已檢出的 重 名 只 出 現(xiàn) 一 次 。 G2 單 元 格 的 公 式 “=IF(ROW(A1)>COUNTIF(F:F,">0"),0,INDEX(E$2:E$1001,LARGE(F:F,ROW(A1) ”就是對 F 列標明的行數(shù)， 按大到小進行排

5、列并在E 列查找重名的姓名。H2單元格的公式“=IF(G2=0,0,COUNTIF(B:B,G2)就是對B列在G列列出的 重名進行計數(shù)。此程序稍作改變便能用來統(tǒng)計姓氏的頻數(shù)與頻率。? ont color="先制作空表格：把本文所附的只有4 行的表頭打開，用上述方法填充B4:l1002 單元格.再把姓名清單從A3 單元格開始拷貝至 A 列。這樣姓氏的頻數(shù)與頻率統(tǒng)計程序操作就完成了。用該程序對筆者所在單位 783人統(tǒng)計有 160個姓氏，張姓最多有95人出現(xiàn)頻率為 12.1%。 樣本太少不具全國姓氏的頻數(shù)與頻率統(tǒng)計上的意義， 但似乎張姓為中國第一大姓。 B 到 F 列的公式與重名檢索工作表的公式極相似， G 到K 列的公式在筆者的 “排序與篩選”一文中有詳細說明。筆者在奔 III 733計算機上制作一張統(tǒng)計10000人姓氏頻數(shù)與頻率的空表需時 6 分 37 秒，復制這樣一張空表瞬時就能完成，在空表上填充 10000 人的姓名后統(tǒng)計姓氏頻數(shù)與頻率的時間為 2 分 42 秒。 填充完后文件大小為 4996k。筆者所以測試以上時間是筆者有一個強烈的愿望： 把程序用于全國千分之一到萬分之一抽樣人口即 12 萬到 120 萬