部门数据整理应用指南.

《部门数据整理应用指南.》由会员分享，可在线阅读，更多相关《部门数据整理应用指南.（9页珍藏版）》请在金锄头文库上搜索。

1、部门数据整理应用指南普查中心宋雪清年9月5日在获得统计、工商、国税、地税、编办、民政、质监等部门数据并将其导入单位清查程序之前，由于各部门数据格式、内容不统一，需要预先通过EXCEL对各部门提供的数据分别进行整理和规范,主要包括以下几个方面的工作： 将详细地址中的省、市、县名称去掉，并将所有门牌号统一为半角字符。尽量获得9位或12位行政区划代码。核实与剔除同一部门重名重码的单位。按统一的顺序排列指标。具体操作过程如下：一、 用EXCEL打开部门提供的数据文件，删除其中不需要的列，以及难以整理成标准代码的中文行业说明，经营范围，单位类型等等信息，重点保留“部门登记注册号、组织机构代码、单位名称、

2、法定代表人、详细地址、联系电话”等指标。有的部门数据数据可能有“注册地址”和“经营地址”等多项地址内容，这时可选择一项作为“详细地址”。二、 通过快速排序键升、降排列来观察详细地址的特点。选中“详细地址”列，通过EXCEL的替换功能将其中包含的省、地、县、市等内容都替换为空。注意，在替换时，一定要选中“详细地址”列，否则可能将单位名称改变。三、 在“详细地址”一列后插入5列空白列，同时插入一个新的Sheet用来放置本区县的行政区划代码，放置时要将行政区划名称放在前列，行政区划代码放在后列，将全部代码区域选中，然后在左上角的名称框处输入“aa”，aa就代表这里选中的行政区划代码区域，在后面的公式

3、中要使用（见图）。本区县的行政区划代码应以这次普查的标准12位代码为准。四、 打开“部门数据整理及行政区划代码赋码模板.xls”，复制选定区域的内容，见图。五、 将上一步复制的内容粘贴到部门文件刚插入的5列的第1、2行中。其中第2行各单元格中对应的其实是公式，其含义见下表，其公式内容需要根据本地的情况进行简单修改。主要是将H2单元格公式中“220104000000”改为本区县的行政区划代码（后6位为0）。六、 选择公式所在的5个单元格，如图，可用三种方式将这些公司扩展到其他所有单位所在的行。一是当鼠标放在选中区域的右下角且其形状变为黑十字时按下鼠标左键向下拖动，二是当鼠标变为黑十字时双击鼠标自

4、动填充，三是选中这5列中单位对应的所有行，通过编辑菜单中的向下填充命令，自动填充每一个单位的情况。1、 点击“去掉街道乡镇名称后的地址（半角）（此列为最后详细地址的内容）”这一列，再单击快速降序排序键，会看到最前面有些空行或地址的首位为数字的情况，这时要将“详细地址”一列中与这些空行或数字对应的内容复制到此处，代替公式的内容。2、 点击“提取的街道、乡镇名称”列，再单击快速降序排序键，然后观察该列的内容，看看街道乡镇名称提取得是否正确，同时观察“补充街道、乡镇级行政区划代码”这一列中的行政区划代码是否达到9位（7、8、9位不都为0）。如果街道乡镇名称明显错误，就要用详细地址中的内容代替，或修改

5、详细地址的内容。3、4、 ，及社区村委会名称所在列及赋码效果，有可能有些村、居委会等名称在标准行政区划代码表中没有赋码，或同一个村有两个名，这时可补充一下标准代码表的内容，从而在名录中自动得到相应的行政区划代码。第一个单元格中的公式为：=IF(ISERROR(FIND(办事处,H2)=TRUE,MID(H2,1,FIND(办事处,H2)+2)&IF(ISERROR(FIND(镇,H2)=TRUE,MID(H2,1,FIND(镇,H2)&IF(ISERROR(FIND(镇,H2)=TRUE,IF(ISERROR(FIND(乡,H2)=TRUE,MID(H2,1,FIND(乡,H2),)该函数由结

6、构基本相同的三部分构成，中间由“&”连接。该语句的基本思想是在详细地址中分别寻找“办事处”、“镇”、“乡”等关键字，如果找到该关键字，则从详细地址左边开始截取字符到该关键字，如果没有该关键字，则取空字符串。然后把每一个关键字对应的串通过“&” 联接起来。正常情况前两个关键字一般不会同时出现在同一详细地址中，如果同时出现，可以通过观察计算结果来发现并个别修正。如果同时出现“镇”、“乡”，则只提取“镇”的情况，没有“镇”，才提取“乡”的情况。如果详细地址中使用的都是“街道”而不是“街道办事处”，则需要将以上公式中的“办事处”改为“街道”，并将“&”前的2改为1。第二个单元格中的公式为：=ASC(I

7、F(I2=,H2,IF(ISERROR(FIND(办事处,H2)=TRUE,MID(H2,FIND(办事处,H2)+3,20)&IF(ISERROR(FIND(镇,H2)=TRUE,MID(H2,FIND(镇,H2)+1,20)&IF(ISERROR(FIND(镇,H2)=TRUE,IF(ISERROR(FIND(乡,H2)=TRUE,MID(H2,FIND(乡,H2)+1,20),)该函数也是由三部分通过“&”连接而成，只是在函数外面又加了一个ASC()函数。目的是提取详细地址中去掉街道或乡镇名称后的地址，并将所有门牌号等数字统一变为半角字符。如果将“办事处”改为“街道”，需要将该部分语句后

8、面的3改为2。第三个单元格中的公式为：=IF(ISERROR(VLOOKUP(I2,aa,2,FALSE)=TRUE,220104000000,&VLOOKUP(I2,aa,2,FALSE)将标红的内容改为本区县的6位行政区划代码并补6个0。而aa代表的就是前面建立的行政区划代码区域。目的是根据街道乡镇名称自动赋9位行政区划代码，如果没有正规的街道乡镇名称，则赋本区县的6位行政区划代码。第四个单元格中的公式为：=IF(ISERROR(FIND(社区,J2)=TRUE,MID(J2,1,FIND(社区,J2)+1)&IF(ISERROR(FIND(村,J2)=TRUE,MID(J2,1,FIND

9、(村,J2)&委会)也是由两部分构成，中间由“&”连接。如果本地主要用居委会，则可将“社区”改为“居委会”，并将加红后的这部分语句后面的“1”改为“2”。同时要考虑到行政区划代码表中的表述方式。目的是从地址中提取“村委会”、“社区”的名称。第五个单元格中的公式为：=IF(L2=,K2,IF(ISERROR(VLOOKUP(L2,aa,2,FALSE)=TRUE,K2,VLOOKUP(L2,aa,2,FALSE)&本单元格中的内容不用变。目的是根据社区、村委会名称自动赋12位行政区划代码，如果无法提取社区、村委会名称，则使用前面的6位或9位行政区划代码。5、 选中根据本地情况修改完成后的这五个包

10、含公式的单元格，通过向下拖动或鼠标双击的方式，自动填充每一个单位的情况。6、 点击“去掉街道乡镇名称后的地址（半角）”这一列，再单击快速降序排序键，会看到最前面有些空行或地址的首位为数字的情况，这时可将“详细地址”一列中与这些空行或数字对应的内容复制到此处。再通过快速排序键分别观察街乡镇名称及社区村委会名称所在列及赋码效果，有可能有些村、居委会等名称在标准行政区划代码表中没有赋码，或同一个村有两个名，这时可补充一下标准代码表的内容，从而在名录中自动得到相应的行政区划代码。（一） 查找是否有重复的单位。具体方法是：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位开始连续取9位代码，即为其组织机构代码。税务数据的登记注册号有中文和全角符号，可通过ASC()函数统一替换为半角。（三） 如果组织机构代码不到9位，可能是其前面的“0”丢掉了，可使用函数“=REPT(0,9-LEN(C2)&C2”来补齐。Rept（）函数是用于重复字符“0”的。类似可补充邮政编码、行业代码等。（四） 从单位清查程序下载EXCEL模板文件，该文件中的字段顺序如下：部门登记证号组织机构代码单位名称法定代表人详

13、细地址行政区划代码邮政编码联系电话行业代码单位类别代码备注将部门数据文件中的内容一列一列地复制到该模板文件中，复制时要使用选择性粘贴，可使用快捷方式快速选取要复制的内容，但要注意别漏掉内容（如果中间有空行，快速选择方式容易漏掉空行后面的内容）。然后使用方便识别部门来源的文件名保存。（五） 其他注意事项：1、 统计名录库的地址只需要“街（村）门牌号”，不需要街乡镇及村居委会等字段；将工商登记注册号、编制登记注册号、民政登记注册号合并为一列作为统计的部门登记注册号（不是统计登记的号码）；2、 民政部门数据常常会只有名称，地称等，登记注册号及组织机构代码都没有，这时可将名称复制到登记注册号这一列，目

14、的是为了统一判断登记注册号不重复。3、 如果部门给的数据是扩展名为“.txt”的文本文件，可选择使用EXCEL打开，然后使用EXCEL的“分列”功能，很快将文本数据转换为EXCEL格式。4、 工商、编制、民政登记机关的部门登记证号不能为空且唯一。 5、 统计、质监、国税、地税的组织机构代码和单位名称不能为空且唯一。（六） EXCEL使用中常见问题及使用技巧a) 公式不能自动计算b) 数值和文本混排，如何都变为有左上标的内容c) 行政区划代码显示为科学计数d) 如何查找重复的记录（IF函数）e) 如何提取数据（组织机构代码）（left(),right(),mid()f) 如何排序g) 行业代码、

15、电话、邮政编码、部门组织机构代码前面自动补0（根据长度判断）h) 如何自动赋码：vlookup(要查找的值，查找的范围，在范围中的列号（要赋的代码所在列），false)，IF（）函数i) 如何自动填充内容和公式（相同内容和递增内容）j) 选择性粘贴k) 如何快速选取整行、整列、多行、多列l) 多列合并形成新的一列m) 内容分列n) 自动筛选o) 错误判断函数iserror()的使用p) 字符串查找函数FIND()（七） 进一步补充行政区划代码部门数据整理好之后，就可分别导入“单位清查程序”。导入之后，生成核查数据库，然后进入“行政区划代码划定”界面，自动为没有12位码的单位赋码，赋码原理是：将详细地址中最左边的汉字和第一组数字相同的单位归为一组，用该组中最有代表性的12位行政区划代码统一替换该组所有单位的行政区划代码。通过此步骤，大部分单位都可以解决。之后提取标准路名，对提取的路名排序，以此为线索，可以很方便地继续进行人工行政区划代码划定。程序在生成单位清查底册时，将把单位分成三类。一类是没有任何地