.

如何用Excel在一分钟内获取国家最新行

我曾在简书上写过两篇帖子,介绍了用PowerBIDesktop如何从国家民政部网站获取最新行政区划信息。其中第二篇帖子在第一篇基础上封装了一个函数,使得获取全国最新行政区划信息不需要任何多余的操作,只需要刷新就行了。

然而,由于Excel不支持Web.BrowserContents()函数,所以在Excel中做不到全自动获取最新行政区划信息(这是个遗憾)。

但是我们还是可以通过手动方式在30秒至一分钟内获取全国最新行政区划信息并整理成我们想要的形式(如区分省、市、县三级,国家民政部网站没有区分)。

下面用Excel来演示下具体的操作流程。

01建立一个参数表格

首先我们在一个新的Excel文件中,建立一个参数表格。这个表格为我们后面的操作提供URL。

表格很简单,加上表格列字段,总共只有两行。

把其中的汉字换成民政部网站上的行政区划信息链接地址(抱歉我在这里没法给出,因为百家号限制)

02把参数表格引入PowerQuery查询

比较简单,直接引入一个来自于表格/区域的数据源。

具体操作如下:

在Excel及以上版本(其他低版本需要下载安装并PowerQuery插件,百度一下就知道怎么操作了)中,鼠标放在参数表格的数据区域,然后点击选项卡上的:

然后PowerQuery会自动启动查询编辑器,并进入查询编辑页面。我们在第一行右键点击,选择,这一步是直接提取出参数表格中的URL(记得要在参数表格中把汉字换成实际的URL),备用。

03新建一个空白查询并复制PowerQuery代码

仍然保持在查询编辑界面,这时我们要进入获取最新行政区划的关键操作部分。

在查询编辑窗口左侧的查询导航栏空白处,右键点击,新建一个空白查询:

然后点击菜单栏的:

清空里边的所有代码:

然后把下面的代码粘贴进去

let源=Web.Page(Web.Contents(url)),Data0=源{0}[Data],删除的顶端行=Table.Skip(Data0,1),#PromotedHeaders=Table.PromoteHeaders(删除的顶端行,[PromoteAllScalars=true]),#RemovedOtherColumns=Table.SelectColumns(#PromotedHeaders,{行政区划代码,单位名称}),#ChangedType=Table.TransformColumnTypes(#RemovedOtherColumns,{{行政区划代码,Int64.Type}}),#RemovedErrors=Table.RemoveRowsWithErrors(#ChangedType,{行政区划代码}),#ChangedType1=Table.TransformColumnTypes(#RemovedErrors,{{行政区划代码,typetext}}),#AddedCustom=Table.AddColumn(#ChangedType1,省级,eachifText.End([行政区划代码],4)=then[单位名称]elsenull),#AddedCustom1=Table.AddColumn(#AddedCustom,地级,eachifText.End([行政区划代码],2)=00then[单位名称]elsenull),#FilledDown=Table.FillDown(#AddedCustom1,{省级,地级}),#RenamedColumns=Table.RenameColumns(#FilledDown,{{单位名称,县级}}),#ReorderedColumns=Table.ReorderColumns(#RenamedColumns,{行政区划代码,县级,地级,省级})in#ReorderedColumns

这样我们的操作就完成了。上面代码可以根据自己需要进行修改。我是提取了省市级三个行政级别放置到不同列。你得到的结果应该类似于:

04把结果加载到Excel表格

最后一步是把结果加载到Excel表格,操作也比较简单,还是在查询编辑器窗口,左上角点击并选择,选择:

如果你像我一样设置了默认加载链接的话,就会看到是灰色不可用,这个没关系,那就直接点好了,我们到Excel中去加载。

关闭并加载后,你应该在Excel中看到如下的界面:

我们点击,这时会弹出操作窗口,让选择加载方式及目的地:

你可以选择加载到现有工作表,也可以做其他选择,比如加载成数据透视表之类,或者添加到数据模型,和其他表格建立关联,进行下一步处理,随你喜欢。

这就是处理结果:

05怎么刷新数据?

假设现在你从民政部网站获得了最新的全国行政区划代码链接,你只需要将其填入我们的参数表格,然后点击选项卡上的,这样在10-30秒内,全国最新行政区划就从民政局网站上获取下来,并根据你在PowerQuery中做的调整结果,显示在Excel表格中了。

是不是很方便?

总结下,你需要做的就是:

进入民政部


转载请注明:http://www.abachildren.com/xgyy/1534.html