Excel在污染源普查工作中數(shù)據(jù)查重的應(yīng)用

姚 磊 付立蘋

（昆明市環(huán)境科學(xué)研究院 云南昆明 650032）

引言

污染源普查是重大的國情調(diào)查，是環(huán)境保護(hù)的基礎(chǔ)性工作,對掌握各類污染源的數(shù)量、行業(yè)和地區(qū)分布情況，了解主要污染物產(chǎn)排和處理情況，建立健全重點(diǎn)污染源檔案，準(zhǔn)確判斷我國當(dāng)前環(huán)境形勢，制定實(shí)施有針對性的經(jīng)濟(jì)社會發(fā)展和環(huán)境保護(hù)政策、規(guī)劃，加快推進(jìn)生態(tài)文明建設(shè)具有重要意義[1]。

污染源普查工作中，數(shù)據(jù)的采集和對數(shù)據(jù)的處理貫通整個(gè)工作的所有環(huán)節(jié)，是該項(xiàng)工作的重中之重。其中，在數(shù)據(jù)處理的過程中，會多次涉及到數(shù)據(jù)查重的問題。例如，獲得來自于不同職能部門工業(yè)企業(yè)原始名錄后，需要進(jìn)行匯總并查重比對，剔除重復(fù)的企業(yè)形成基礎(chǔ)名錄。查重工作處理得當(dāng)，能夠?yàn)楹罄m(xù)的數(shù)據(jù)處理帶來便利，避免重復(fù)調(diào)查和重復(fù)統(tǒng)計(jì)，節(jié)約有限的人力資源。

本文根據(jù)Excel 2016（Excel 2007或以上版本均可）提供的功能，提出幾種數(shù)據(jù)查重處理的方法，并對幾種方法的優(yōu)劣進(jìn)行探討，以期污普工作者在處理數(shù)據(jù)查重問題時(shí)能夠找到一種適合自己的最佳方法。

現(xiàn)以第二次污染源普查在建立工業(yè)企業(yè)基礎(chǔ)名錄中的查重比對的技術(shù)要求為例，詳細(xì)介紹各種方法。根據(jù)要求，用來比對的字段包括統(tǒng)一社會信用代碼，組織機(jī)構(gòu)代碼以及企業(yè)名稱。不同來源的數(shù)據(jù)查重比對，只要其中一數(shù)據(jù)源的某條記錄中以上3個(gè)字段中有一項(xiàng)的值和另一數(shù)據(jù)源的對應(yīng)字段的某條記錄值是相同的（不含空值），則認(rèn)為分別來自兩個(gè)數(shù)據(jù)源中的這兩條記錄是重復(fù)的，即這兩條記錄都是指向同一工業(yè)企業(yè)。

1 Excel查重方法

下述所有的方法在應(yīng)用前均須把兩組不同來源的數(shù)據(jù)根據(jù)字段對應(yīng)的原則復(fù)制到同一工作表中，并新建一列字段標(biāo)注數(shù)據(jù)來源以方便比對。本文實(shí)例中使用了12條記錄。

1.1 快速刪除重復(fù)值法[2]

選中表格區(qū)域中的任意一個(gè)單元格，如A7單元格。單擊“數(shù)據(jù)”選項(xiàng)卡中的“刪除重復(fù)值”按鈕，打開“刪除重復(fù)值”對話框。根據(jù)污普查重比對技術(shù)要求，只單獨(dú)勾選“企業(yè)名稱”字段，同時(shí)勾選“數(shù)據(jù)包含標(biāo)題”復(fù)選框，單擊“確定”后根據(jù)提示完成操作。再次執(zhí)行“刪除重復(fù)值”的操作，將“刪除重復(fù)值”對話框中的字段單獨(dú)勾選為“統(tǒng)一社會信用代碼”，操作完畢后，接著對“組織機(jī)構(gòu)代碼”字段進(jìn)行“刪除重復(fù)值”操作。

1.2 高級篩選法[2]

選中表格區(qū)域中的任意一個(gè)單元格，如A7單元格。單擊“數(shù)據(jù)”選項(xiàng)卡中的“高級”按鈕，彈出“高級篩選”對話框。點(diǎn)選“在原有區(qū)域顯示篩選結(jié)果”，將光標(biāo)定位到“列表區(qū)域”框內(nèi)，將原有內(nèi)容修改為“$C$1:$C$13”（即企業(yè)名稱字段對應(yīng)的所有數(shù)據(jù)集），然后勾選“選擇不重復(fù)的記錄”復(fù)選框，最后單擊“確定”按鈕。將篩選結(jié)果復(fù)制拷貝到另一新建的工作表中，接著對“統(tǒng)一社會信用代碼”應(yīng)用高級篩選，完成后再將二次篩選結(jié)果復(fù)制到另一新建工作表中，然后對“組織機(jī)構(gòu)代碼”也應(yīng)用高級篩選。

1.3 函數(shù)公式法

1.3.1 COUNTIF函數(shù)法[3]

分別選中E2、F2、G2單元格，然后分別輸入函數(shù)公式：=COUNTIF(A$2:A2,A2&"*")、=COUNTIF(B$2:B2,B2)、=COUNTIF(C$2:C2,C2)，在 H2 單元格內(nèi)輸入=IF((E2＞1)+(F2＞1)+(G2＞1),"重復(fù)","")，選定E2:H2,向下拖動至第13行，則H列內(nèi)顯示“重復(fù)”值其所對應(yīng)的條目屬于重復(fù)企業(yè)條目。

1.3.2 SUM函數(shù)數(shù)組求和法

分別選中E8、F8、G8單元格，然后分別輸入函數(shù)公式：{=IF(A8="",0,SUM((A8=$A$2:$A$7)*1))}、{=IF(B8="",0,SUM((B8=$B$2:$B$7)*1))}、{=IF(C8="",0,SUM((C8=$C$2:$C$7)*1))}，在 H8 單元格內(nèi)輸入=IF((E8＞0)+(F8＞0)+(G8＞0),"重復(fù)","")，選定 E8:H8,向下拖動至第 13 行，則H列內(nèi)顯示“重復(fù)”值其所對應(yīng)的條目屬于重復(fù)企業(yè)條目。

1.3.3 IF函數(shù)綜合排序法

選中C1單元格，對字段“企業(yè)名稱”進(jìn)行“降序”排序。在E3單元格處輸入：=If(C3=C2,row(),0),向下拖動至第13行，則E列內(nèi)顯示值大于0的單元格其所對應(yīng)的條目屬于重復(fù)企業(yè)條目。該方法的進(jìn)階：可將不同數(shù)據(jù)源的重復(fù)數(shù)據(jù)并排顯示，不同重復(fù)項(xiàng)對應(yīng)不同的篩選值，操作如下：完成E列賦值操作后，選擇E3:E13，復(fù)制，原有位置選擇性粘貼→數(shù)值，再次選擇E3:E13，右擊單元格“F2”→粘貼，將值粘貼于F列內(nèi)，E、F列兩列數(shù)據(jù)挫位排列。在G1單元格內(nèi)輸入字段“篩選值”，G2單元格內(nèi)輸入公式：=E2+F2，并向下拖動至G13，選擇G2:G13,復(fù)制，右擊單元格“G2”，點(diǎn)擊選擇性粘貼→數(shù)值，將值粘貼于G列內(nèi)，然后對G列進(jìn)行降序排序，則可以將來自不同數(shù)據(jù)源的重復(fù)數(shù)據(jù)并排顯示，方便數(shù)據(jù)間的比對。在比對完“企業(yè)名稱”刪除重復(fù)數(shù)據(jù)后，可重復(fù)以上步驟分別對“統(tǒng)一社會信用代碼”和“組織機(jī)構(gòu)代碼”進(jìn)行重復(fù)值比對剔除。

2 各種查重方法的比較

上述各種查重方法均有其優(yōu)缺點(diǎn)。快速刪除重復(fù)值法操作步驟少，在所有的查重方法中最為快捷，非常適合對Excel不熟練的新手操作，但因?yàn)槭侵苯觿h除數(shù)據(jù)，刪除的數(shù)據(jù)是哪些并不能一目了然的呈現(xiàn)出來，同時(shí)還須做好原始數(shù)據(jù)的備份；高級篩選法操作步驟相對繁瑣，但操作淺顯易懂，也適合新手應(yīng)用，而且在篩選出不重復(fù)值的同時(shí)還保留了原始數(shù)據(jù)，但不能直觀顯示重復(fù)值是哪些條記錄；函數(shù)公式法的應(yīng)用需要對Excel函數(shù)有一定的了解，清楚函數(shù)公式的邏輯所在，并能夠通過公式生成的值來辨別對應(yīng)的條目是否屬于重復(fù)值。但函數(shù)公式法能夠把原始記錄中的重復(fù)與非重復(fù)數(shù)據(jù)非常清晰的呈現(xiàn)出來。函數(shù)公式法中(1)、(2)相對來說步驟較少，可以對“企業(yè)名稱”、“統(tǒng)一社會信用代碼”、“組織機(jī)構(gòu)代碼”3個(gè)字段同時(shí)進(jìn)行查重，且篩選出的重復(fù)值僅屬于其中一個(gè)數(shù)據(jù)源，剔除重復(fù)值相對便捷；但要注意COUNTIF函數(shù)在計(jì)算長字符串時(shí)，會將15位后面出現(xiàn)的任何數(shù)字都視為0，若兩個(gè)字符數(shù)相等的長字符串字符數(shù)多于15位且前面15位也都相等，COUNTIF會視為等值，由于統(tǒng)一社會信用代碼為18位字符串，故以COUNTIF進(jìn)行查重時(shí)須在公式中添加“&"*"”,使COUNTIF函數(shù)識別超過15位的字符串;函數(shù)公式法(3)操作步驟相對較多，且排序后應(yīng)用“IF”函數(shù)篩選出的重復(fù)值可能來源于兩個(gè)數(shù)據(jù)源中，若要篩選出某個(gè)數(shù)據(jù)源中的重復(fù)值，則要通過“進(jìn)階”操作得出篩選值，再通過自定義排序（“數(shù)據(jù)來源”字段作為主要關(guān)鍵字、“篩選值”字段作為次要關(guān)鍵字）顯示出來。但該方法的優(yōu)點(diǎn)是可以通過“進(jìn)階”操作將來自兩個(gè)數(shù)據(jù)源的重復(fù)值并排顯示，且不同重復(fù)項(xiàng)其對應(yīng)篩選值也不一樣，在后期清查建庫過程中需要精確比對剔除重復(fù)數(shù)據(jù)時(shí)，此法反而相對便捷。在應(yīng)用函數(shù)公式法時(shí)須注意，公式不能直接用來排序或篩選，必須將公式進(jìn)行選擇性粘貼生成數(shù)值后才能進(jìn)行排序或篩選。

3 討論

在污染源普查實(shí)際工作中，從不同部門獲取的名錄數(shù)據(jù)是相當(dāng)繁雜的，一是數(shù)據(jù)的條目數(shù)量通常上千，所列的字段不局限于企業(yè)名稱、統(tǒng)代碼及組代碼，還有諸如聯(lián)系地址、行業(yè)類別等字段，且來自不同部門的數(shù)據(jù)所列字段也不一樣；二是數(shù)據(jù)的錄入不規(guī)范，例如原始企業(yè)名錄庫中存在極少數(shù)重復(fù)企業(yè)，部分企業(yè)缺少統(tǒng)代碼和組代碼信息，部分?jǐn)?shù)據(jù)錄入時(shí)存在首/尾空格錄入問題，在查重比對分析中會引起漏查，另外部分統(tǒng)代碼和組代碼是純數(shù)字，在錄入時(shí)格式若不設(shè)置好會以科學(xué)計(jì)數(shù)法顯示，還有企業(yè)名稱錄入時(shí)，全稱中帶有XX省、XX市、“有限責(zé)任公司”的，“省”、“市”、“責(zé)任”等字眼被省略，從而在比對查重時(shí)同一企業(yè)會被計(jì)算機(jī)識別為不同企業(yè)。因此，當(dāng)拿到企業(yè)名錄數(shù)據(jù)后進(jìn)行查重比對前，需要對數(shù)據(jù)進(jìn)行前處理。

本文在此建議數(shù)據(jù)前處理按以下步驟進(jìn)行：①規(guī)范表格樣式，確定表格所需字段及排列順序；②空格替換，將所有單元格內(nèi)的空格替換為空值，但數(shù)據(jù)本身就需要空格的不能替換；③將科學(xué)計(jì)數(shù)法顯示的數(shù)據(jù)變更成數(shù)字串顯示；④填充空值，填充值須簡單、識別度高且與所在字段對應(yīng)的其他數(shù)據(jù)不重復(fù)，如a1、a2…a3…；⑤剔除各原始數(shù)據(jù)源內(nèi)的重復(fù)值。

按以上步驟執(zhí)行完數(shù)據(jù)前處理就可以進(jìn)行查重比對分析了。針對不同數(shù)據(jù)源企業(yè)名稱錄入不統(tǒng)一的問題，可以在執(zhí)行完第一次重復(fù)值剔除后，復(fù)制并拷貝企業(yè)名稱字段列，在拷貝列中將“XX 省”、“YY 市”、“XX”、“YY”等替換為空值，將“有限責(zé)任公司”、“有限公司”替換為“公司”，并按IF函數(shù)綜合排序(進(jìn)階)法進(jìn)行查重，查重結(jié)果須結(jié)合原始企業(yè)名錄進(jìn)行比對，逐一剔除。

結(jié)語

Excel軟件可以實(shí)現(xiàn)將多種數(shù)據(jù)查重方法應(yīng)用于污染源普查工作中，通過選取合適的查重方法讓查重工作效率最大化。