您现在的位置: 首页 > 网站导航收录 > 百科知识百科知识

新一代Excel Power Query真的这么厉害？

行政区划,代码,单位名称新一代Excel Power Query真的这么厉害？

发布时间：2016-12-08加入收藏来源：互联网点击：

新一代Excel Power Query真的这么厉害？

回答于 2019-09-11 08:43:50

回答于 2019-09-11 08:43:50

《教你如何在一分钟内获取最新最全的全国行政区划信息》

在工作中，我们经常会用到全国行政区划的信息，但我们往往只能从百度文库或其他网站去下载别人整理过的二手资料。这些资料不一定准确，而且最重要的是不一定保持最新，有时候会给我们工作带来意想不到的麻烦。

那怎么办呢？其实国家民政部的网站上就提供了最新的行政区划信息。比如我们可以得到2018年10月全国最新的行政区划信息：

点开后，显示的是这样的页面：

可以看到，这个列表虽然是全国最新的行政区划，但我们并没法直接使用——因为没有显示出省市县的层级来。

我们需要的是一个有层级的行政区划表：

也就是说，我们对行政区划产生了两个需求：

一是能够即时获得最新信息；二是这些信息已组织好能够直接使用。

怎么实现这两个需求呢？

我还是祭出微软的个人自助式BI神器——PowerBI。

建立一个来自于Web的查询

打开PowerBI Desktop（excel也行，操作逻辑都是一样的），新建一个基于Web的查询：

比如2018年10月份最新的行政区划信息所在的网页链接是：

http://www.mca.gov.cn/article/sj/xzqh/2018/201804-12/20181011221630.html，如下图所示，将这个链接填入URL下的方框内，点击确定：

民政部的网站很贴心，给出的行政区划信息是一个表格，PowerBI Desktop能完整识别这类html表格：

上图中那个Table0就是我们需要的信息。点击确定后加载，对信息进行处理。

数据清洗和处理

上面步骤获得的行政区划信息包含不相干数据，我们需要将这些不相干数据过滤掉。

首先第一行的“2018年10月中华人民共和国…”我们不需要，利用“删除最前面几行”命令删除第一行：

第二步是提升标题：

第三步是删除其他不需要的列，只保留“行政区划代码”和“单位名称”两列：

处理完后的表格只剩两列：

这时我们要进一步检测表格是不是还有其他没剔除的噪音信息——主要是不需要的文本信息。

第四步，将“行政区划代码”的格式改为整数，这样如果这一列有不需要的文本信息，那么就会报错，我们再把出错的行删除就ok了。

第五步，确认噪音信息被剔除后，再把“行政区划代码”格式改回为文本。这样做的目的是避免后面统计时这些代码被当成数字自动进行求和运算。我们不需要求和。

第六步，分别添加省市两级的列。我是根据行政区划代码最后几位的数字来判断省市的。如果最后四位都是0，那么是省一级；如果最后两位都是0，那么是市一级别：

= Table.AddColumn(#"Changed Type1", "省级", each if Text.End([行政区划代码],4)="0000" then [单位名称] else null) = Table.AddColumn(#"Added Custom", "地级", each if Text.End([行政区划代码],2)="00" then [单位名称] else null)

第七步：向下填充。在上一步分别添加了省市两个级别后，我们需要把null值用对应的省或市来填充掉，因此，选择省市两列，右键，选择“填充”——“向下”：

下面是填充后的结果：

到这一步基本就结束了。

不过我个人是强迫症，我还想统计各级单位的个数。于是有了下面的内容。

统计汇总

这个动作其实是多此一举，因为民政局的网站上已经有了现成的统计结果，而且细致到乡、街道。

但这毕竟是2017年的数据，没有2018年最新的数据，不是么？所以，我想自己统计下截止到现在，中国有多少个市、多少个县。

观察之前处理的结果，我们可以发现“单位名称”里边包含了省级单位名称和市级单位名称，但我们后面两列已经有省市两级的名称了，所以，我们需要从“单位名称”里剔除省市名称，只保留县级单位名称即可。

于是我写了个公式：

= Table.SelectRows(#"Sorted Rows", each not Text.EndsWith([行政区划代码], "0000") and not Text.EndsWith([行政区划代码], "00"))

但这个公式有一个大问题：它会把没有下级单位的省级单位也过滤了，这就会导致计算省级单位的时候出错（变成31个）。

所以我又写了第二个公式：

= Table.SelectRows(#"Sorted Rows", each not Text.EndsWith([行政区划代码], "0000") and not Text.EndsWith([行政区划代码], "00")or Text.StartsWith([行政区划代码], "7") or Text.StartsWith([行政区划代码], "8"))

这样没下级单位的省级单位就保留了。但这样带来的后遗症是它们依然会出现在”单位名称“列和”市“一级单位名称里。那对市县进行计数的时候怎么剔除这类特殊区域呢？

办法是留到统计时候筛选。这时又有两种办法。

最简单最偷懒的办法是直接在视觉筛选器里单独筛选这类特殊区域：

但这样的法子很容易导致问题：在将来的计算中忘记剔除这些特殊区域了。

所以，最保险的法子是建立度量值，在度量值里边进行筛选：

县计数 = CALCULATE(COUNTA('行政区划'[行政区划代码]),FILTER('行政区划',[县级]<>"台湾省"&&[县级]<>"香港特别行政区"&&[县级]<>"澳门特别行政区")) 市计数 = CALCULATE(DISTINCTCOUNT('行政区划'[地级]),FILTER('行政区划',[地级]<>"台湾省"&&[地级]<>"香港特别行政区"&&[地级]<>"澳门特别行政区"))

最后获得的统计结果如下：

全国目前有34个省级行政区，337个地级单位，2852个县级单位。

四川的地级单位最多，有21个；县级单位也最多，有183个。原来海南省只有3个地级单位，宁夏有5个，西藏有7个，青海有8个，吉林、福建和贵州有9个地级单位。这些我都还是第一次才知道，看来上学时候地理没学好。

结语

以后要获取全国最新的行政区划信息，只需要打开这个pbix文件，刷新即可，所有需要的信息会在一分钟以内更新完毕。excel同理。

有兴趣的小伙伴可以发私信消息”行政区划“，我将我做的这个pbix文件发您。其实比较简单，建议自己动手试试看。

帖子有点长，主要是步骤写得比较详细，谢谢阅读