Excel在污染源普查工作中數據查重的應用

姚 磊 付立蘋

（昆明市環境科學研究院 云南昆明 650032）

引言

污染源普查是重大的國情調查，是環境保護的基礎性工作,對掌握各類污染源的數量、行業和地區分布情況，了解主要污染物產排和處理情況，建立健全重點污染源檔案，準確判斷我國當前環境形勢，制定實施有針對性的經濟社會發展和環境保護政策、規劃，加快推進生態文明建設具有重要意義[1]。

污染源普查工作中，數據的采集和對數據的處理貫通整個工作的所有環節，是該項工作的重中之重。其中，在數據處理的過程中，會多次涉及到數據查重的問題。例如，獲得來自于不同職能部門工業企業原始名錄后，需要進行匯總并查重比對，剔除重復的企業形成基礎名錄。查重工作處理得當，能夠為后續的數據處理帶來便利，避免重復調查和重復統計，節約有限的人力資源。

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

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

1 Excel查重方法

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

1.1 快速刪除重復值法[2]

選中表格區域中的任意一個單元格，如A7單元格。單擊“數據”選項卡中的“刪除重復值”按鈕，打開“刪除重復值”對話框。根據污普查重比對技術要求，只單獨勾選“企業名稱”字段，同時勾選“數據包含標題”復選框，單擊“確定”后根據提示完成操作。再次執行“刪除重復值”的操作，將“刪除重復值”對話框中的字段單獨勾選為“統一社會信用代碼”，操作完畢后，接著對“組織機構代碼”字段進行“刪除重復值”操作。

1.2 高級篩選法[2]

選中表格區域中的任意一個單元格，如A7單元格。單擊“數據”選項卡中的“高級”按鈕，彈出“高級篩選”對話框。點選“在原有區域顯示篩選結果”，將光標定位到“列表區域”框內，將原有內容修改為“$C$1:$C$13”（即企業名稱字段對應的所有數據集），然后勾選“選擇不重復的記錄”復選框，最后單擊“確定”按鈕。將篩選結果復制拷貝到另一新建的工作表中，接著對“統一社會信用代碼”應用高級篩選，完成后再將二次篩選結果復制到另一新建工作表中，然后對“組織機構代碼”也應用高級篩選。

1.3 函數公式法

1.3.1 COUNTIF函數法[3]

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

1.3.2 SUM函數數組求和法

分別選中E8、F8、G8單元格，然后分別輸入函數公式：{=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 單元格內輸入=IF((E8＞0)+(F8＞0)+(G8＞0),"重復","")，選定 E8:H8,向下拖動至第 13 行，則H列內顯示“重復”值其所對應的條目屬于重復企業條目。

1.3.3 IF函數綜合排序法

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

2 各種查重方法的比較

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

3 討論

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

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

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

結語

Excel軟件可以實現將多種數據查重方法應用于污染源普查工作中，通過選取合適的查重方法讓查重工作效率最大化。