部門數據整理應用指南.

1、部門數據整理應用指南普查中心宋雪清年9月5日在獲得統(tǒng)計、工商、國稅、地稅、編辦、民政、質監(jiān)等部門數據并將其導入單位清查程序之前，由于各部門數據格式、內容不統(tǒng)一，需要預先通過EXCEL對各部門提供的數據分別進行整理和規(guī)范,主要包括以下幾個方面的工作： 將詳細地址中的省、市、縣名稱去掉，并將所有門牌號統(tǒng)一為半角字符。盡量獲得9位或12位行政區(qū)劃代碼。核實與剔除同一部門重名重碼的單位。按統(tǒng)一的順序排列指標。具體操作過程如下：一、 用EXCEL打開部門提供的數據文件，刪除其中不需要的列，以及難以整理成標準代碼的中文行業(yè)說明，經營范圍，單位類型等等信息，重點保留“部門登記注冊號、組織機構代碼、單位名稱、

2、法定代表人、詳細地址、聯系電話”等指標。有的部門數據數據可能有“注冊地址”和“經營地址”等多項地址內容，這時可選擇一項作為“詳細地址”。二、 通過快速排序鍵升、降排列來觀察詳細地址的特點。選中“詳細地址”列，通過EXCEL的替換功能將其中包含的省、地、縣、市等內容都替換為空。注意，在替換時，一定要選中“詳細地址”列，否則可能將單位名稱改變。三、 在“詳細地址”一列后插入5列空白列，同時插入一個新的Sheet用來放置本區(qū)縣的行政區(qū)劃代碼，放置時要將行政區(qū)劃名稱放在前列，行政區(qū)劃代碼放在后列，將全部代碼區(qū)域選中，然后在左上角的名稱框處輸入“aa”，aa就代表這里選中的行政區(qū)劃代碼區(qū)域，在后面的公式

3、中要使用（見圖）。本區(qū)縣的行政區(qū)劃代碼應以這次普查的標準12位代碼為準。四、 打開“部門數據整理及行政區(qū)劃代碼賦碼模板.xls”，復制選定區(qū)域的內容，見圖。五、 將上一步復制的內容粘貼到部門文件剛插入的5列的第1、2行中。其中第2行各單元格中對應的其實是公式，其含義見下表，其公式內容需要根據本地的情況進行簡單修改。主要是將H2單元格公式中“220104000000”改為本區(qū)縣的行政區(qū)劃代碼（后6位為0）。六、 選擇公式所在的5個單元格，如圖，可用三種方式將這些公司擴展到其他所有單位所在的行。一是當鼠標放在選中區(qū)域的右下角且其形狀變?yōu)楹谑謺r按下鼠標左鍵向下拖動，二是當鼠標變?yōu)楹谑謺r雙擊鼠標自

4、動填充，三是選中這5列中單位對應的所有行，通過編輯菜單中的向下填充命令，自動填充每一個單位的情況。1、 點擊“去掉街道鄉(xiāng)鎮(zhèn)名稱后的地址（半角）（此列為最后詳細地址的內容）”這一列，再單擊快速降序排序鍵，會看到最前面有些空行或地址的首位為數字的情況，這時要將“詳細地址”一列中與這些空行或數字對應的內容復制到此處，代替公式的內容。2、 點擊“提取的街道、鄉(xiāng)鎮(zhèn)名稱”列，再單擊快速降序排序鍵，然后觀察該列的內容，看看街道鄉(xiāng)鎮(zhèn)名稱提取得是否正確，同時觀察“補充街道、鄉(xiāng)鎮(zhèn)級行政區(qū)劃代碼”這一列中的行政區(qū)劃代碼是否達到9位（7、8、9位不都為0）。如果街道鄉(xiāng)鎮(zhèn)名稱明顯錯誤，就要用詳細地址中的內容代替，或修改

5、詳細地址的內容。3、4、 ，及社區(qū)村委會名稱所在列及賦碼效果，有可能有些村、居委會等名稱在標準行政區(qū)劃代碼表中沒有賦碼，或同一個村有兩個名，這時可補充一下標準代碼表的內容，從而在名錄中自動得到相應的行政區(qū)劃代碼。第一個單元格中的公式為：=IF(ISERROR(FIND(辦事處,H2)=TRUE,MID(H2,1,FIND(辦事處,H2)+2)&IF(ISERROR(FIND(鎮(zhèn),H2)=TRUE,MID(H2,1,FIND(鎮(zhèn),H2)&IF(ISERROR(FIND(鎮(zhèn),H2)=TRUE,IF(ISERROR(FIND(鄉(xiāng),H2)=TRUE,MID(H2,1,FIND(鄉(xiāng),H2),)該函數由結

6、構基本相同的三部分構成，中間由“&”連接。該語句的基本思想是在詳細地址中分別尋找“辦事處”、“鎮(zhèn)”、“鄉(xiāng)”等關鍵字，如果找到該關鍵字，則從詳細地址左邊開始截取字符到該關鍵字，如果沒有該關鍵字，則取空字符串。然后把每一個關鍵字對應的串通過“&” 聯接起來。正常情況前兩個關鍵字一般不會同時出現在同一詳細地址中，如果同時出現，可以通過觀察計算結果來發(fā)現并個別修正。如果同時出現“鎮(zhèn)”、“鄉(xiāng)”，則只提取“鎮(zhèn)”的情況，沒有“鎮(zhèn)”，才提取“鄉(xiāng)”的情況。如果詳細地址中使用的都是“街道”而不是“街道辦事處”，則需要將以上公式中的“辦事處”改為“街道”，并將“&”前的2改為1。第二個單元格中的公式為：=ASC(I

7、F(I2=,H2,IF(ISERROR(FIND(辦事處,H2)=TRUE,MID(H2,FIND(辦事處,H2)+3,20)&IF(ISERROR(FIND(鎮(zhèn),H2)=TRUE,MID(H2,FIND(鎮(zhèn),H2)+1,20)&IF(ISERROR(FIND(鎮(zhèn),H2)=TRUE,IF(ISERROR(FIND(鄉(xiāng),H2)=TRUE,MID(H2,FIND(鄉(xiāng),H2)+1,20),)該函數也是由三部分通過“&”連接而成，只是在函數外面又加了一個ASC()函數。目的是提取詳細地址中去掉街道或鄉(xiāng)鎮(zhèn)名稱后的地址，并將所有門牌號等數字統(tǒng)一變?yōu)榘虢亲址?。如果將“辦事處”改為“街道”，需要將該部分語句后

8、面的3改為2。第三個單元格中的公式為：=IF(ISERROR(VLOOKUP(I2,aa,2,FALSE)=TRUE,220104000000,&VLOOKUP(I2,aa,2,FALSE)將標紅的內容改為本區(qū)縣的6位行政區(qū)劃代碼并補6個0。而aa代表的就是前面建立的行政區(qū)劃代碼區(qū)域。目的是根據街道鄉(xiāng)鎮(zhèn)名稱自動賦9位行政區(qū)劃代碼，如果沒有正規(guī)的街道鄉(xiāng)鎮(zhèn)名稱，則賦本區(qū)縣的6位行政區(qū)劃代碼。第四個單元格中的公式為：=IF(ISERROR(FIND(社區(qū),J2)=TRUE,MID(J2,1,FIND(社區(qū),J2)+1)&IF(ISERROR(FIND(村,J2)=TRUE,MID(J2,1,FIND

9、(村,J2)&委會)也是由兩部分構成，中間由“&”連接。如果本地主要用居委會，則可將“社區(qū)”改為“居委會”，并將加紅后的這部分語句后面的“1”改為“2”。同時要考慮到行政區(qū)劃代碼表中的表述方式。目的是從地址中提取“村委會”、“社區(qū)”的名稱。第五個單元格中的公式為：=IF(L2=,K2,IF(ISERROR(VLOOKUP(L2,aa,2,FALSE)=TRUE,K2,VLOOKUP(L2,aa,2,FALSE)&本單元格中的內容不用變。目的是根據社區(qū)、村委會名稱自動賦12位行政區(qū)劃代碼，如果無法提取社區(qū)、村委會名稱，則使用前面的6位或9位行政區(qū)劃代碼。5、 選中根據本地情況修改完成后的這五個包

10、含公式的單元格，通過向下拖動或鼠標雙擊的方式，自動填充每一個單位的情況。6、 點擊“去掉街道鄉(xiāng)鎮(zhèn)名稱后的地址（半角）”這一列，再單擊快速降序排序鍵，會看到最前面有些空行或地址的首位為數字的情況，這時可將“詳細地址”一列中與這些空行或數字對應的內容復制到此處。再通過快速排序鍵分別觀察街鄉(xiāng)鎮(zhèn)名稱及社區(qū)村委會名稱所在列及賦碼效果，有可能有些村、居委會等名稱在標準行政區(qū)劃代碼表中沒有賦碼，或同一個村有兩個名，這時可補充一下標準代碼表的內容，從而在名錄中自動得到相應的行政區(qū)劃代碼。（一） 查找是否有重復的單位。具體方法是：7、 在文件前插入一空白列，任意賦一個列名。點擊“單位名稱”列，點擊快速排序鍵，升

11、降序都可以8、 在第一列第二行輸入公式“=IF(E2=E1,1,0)”，它表示如果本行的單位名稱和上一行的單位名稱一樣，則單元格取值為1，否則取為0，這樣很快就可知道有多少個單位是同名的，也可通過IF（）函數快速判斷代碼和名稱同時相同的單位有哪些，并根據第一列的結果排序，方便地將重復的單位排在一起，以便一次刪除。1、9、 對組織機構代碼、登記注冊代碼等的重碼查詢都可以類似操作。10、 如果需要把重復的單位放在一起觀察，以便確定是否是真正的重復單位，可通過語句“=IF(OR(E2=E1,E2=E3),1,0)”來標識重復的單位，然后將該數據復制再選擇性用“數值”選項粘貼回來，再快速排序，即可達到

12、目的。（二） 如果是稅務部門數據，而且組織機構代碼為空，則可從其部門登記證號中抽取組織機構代碼。具體方法是插入一空白列，使用函數“=MID(B2,7,9)”從登記注冊號的第7位開始連續(xù)取9位代碼，即為其組織機構代碼。稅務數據的登記注冊號有中文和全角符號，可通過ASC()函數統(tǒng)一替換為半角。（三） 如果組織機構代碼不到9位，可能是其前面的“0”丟掉了，可使用函數“=REPT(0,9-LEN(C2)&C2”來補齊。Rept（）函數是用于重復字符“0”的。類似可補充郵政編碼、行業(yè)代碼等。（四） 從單位清查程序下載EXCEL模板文件，該文件中的字段順序如下：部門登記證號組織機構代碼單位名稱法定代表人詳

13、細地址行政區(qū)劃代碼郵政編碼聯系電話行業(yè)代碼單位類別代碼備注將部門數據文件中的內容一列一列地復制到該模板文件中，復制時要使用選擇性粘貼，可使用快捷方式快速選取要復制的內容，但要注意別漏掉內容（如果中間有空行，快速選擇方式容易漏掉空行后面的內容）。然后使用方便識別部門來源的文件名保存。（五） 其他注意事項：1、 統(tǒng)計名錄庫的地址只需要“街（村）門牌號”，不需要街鄉(xiāng)鎮(zhèn)及村居委會等字段；將工商登記注冊號、編制登記注冊號、民政登記注冊號合并為一列作為統(tǒng)計的部門登記注冊號（不是統(tǒng)計登記的號碼）；2、 民政部門數據常常會只有名稱，地稱等，登記注冊號及組織機構代碼都沒有，這時可將名稱復制到登記注冊號這一列，目

14、的是為了統(tǒng)一判斷登記注冊號不重復。3、 如果部門給的數據是擴展名為“.txt”的文本文件，可選擇使用EXCEL打開，然后使用EXCEL的“分列”功能，很快將文本數據轉換為EXCEL格式。4、 工商、編制、民政登記機關的部門登記證號不能為空且唯一。 5、 統(tǒng)計、質監(jiān)、國稅、地稅的組織機構代碼和單位名稱不能為空且唯一。（六） EXCEL使用中常見問題及使用技巧a) 公式不能自動計算b) 數值和文本混排，如何都變?yōu)橛凶笊蠘说膬热輈) 行政區(qū)劃代碼顯示為科學計數d) 如何查找重復的記錄（IF函數）e) 如何提取數據（組織機構代碼）（left(),right(),mid()f) 如何排序g) 行業(yè)代碼、

15、電話、郵政編碼、部門組織機構代碼前面自動補0（根據長度判斷）h) 如何自動賦碼：vlookup(要查找的值，查找的范圍，在范圍中的列號（要賦的代碼所在列），false)，IF（）函數i) 如何自動填充內容和公式（相同內容和遞增內容）j) 選擇性粘貼k) 如何快速選取整行、整列、多行、多列l(wèi)) 多列合并形成新的一列m) 內容分列n) 自動篩選o) 錯誤判斷函數iserror()的使用p) 字符串查找函數FIND()（七） 進一步補充行政區(qū)劃代碼部門數據整理好之后，就可分別導入“單位清查程序”。導入之后，生成核查數據庫，然后進入“行政區(qū)劃代碼劃定”界面，自動為沒有12位碼的單位賦碼，賦碼原理是：將詳細地址中最左邊的漢字和第一組數字相同的單位歸為一組，用該組中最有代表性的12位行政區(qū)劃代碼統(tǒng)一替換該組所有單位的行政區(qū)劃代碼。通過此步驟，大部分單位都可以解決。之后提取標準路名，對提取的路名排序，以此為線索，可以很方便地繼續(xù)進行人工行政區(qū)劃代碼劃定。程序在生成單位清查底冊時，將把單位分成三類。一類