Power Query实现Excel中下拉框值的动态刷新，分发出去的模板下拉框值自动同步

速六财易

在数据管理中，为了维持数据的一致性和规范性，使用Excel下拉列表是普遍的选择，这种方式提供了一个预定义的可选项清单，有效地防止了非规范化输入，在维护数据统一性上起到了重要的作用。

然而，这种做法有的问题在于，一旦预定义的选项需要更新调整，那么更新的任务就变得繁重而复杂。每一个已经分发出去的模板都需要更新，同时还需要确保所有用户都使用最新的模板版本。而这个过程中总会存在更新不及时，或错过更新的情况，尤其是在选项值频繁变动的条件下。结果就是，虽然目标是数据统一化，但实际效果往往不尽人意，反而可能导致信息的凌乱，无法解决数据碎片化的问题。



幸好我们有了Power Query，采用Power Query作为动态数据管理工具，我们能够显著优化现有的数据更新过程。

只需一次性在Excel工作簿中建立与数据源的连接并配置为自动刷新，我们就可以实现一个持续的、自主的数据更新机制。

这意味着每个下拉列表的值将与数据源保持同步，且无需人工干预，我们在数据源更改下拉列表的值，所有分发出去的工作簿的下拉框值将会自动刷新。这种自动化的解决方案，降低了由于人为因素所产生的错误，而且还能以一种高效且准确的方式促进企业数据的统一管理。 我们通过使用Power Query的功能，提供了一种专业、高效的方法来解决动态数据更新和企业数据管理的问题。

具体来说，按以下步骤，我们就能将以上方案实现：

第一步：建立一个集中的数据源，最简单的方式是通过web页面来提供数据。我们使用速六网的通用选项集功能，在后台可以设定我们自己的选项集合：



第二步：通过power query，从web页面获取我们的选项集数据，按如下信息填写即可。注意http请求标头需要更改为您自己的。当然通过其他方法集中管理选项集数据也可以。



第三步：我们获取到自己设定的选项集数据，这个时候数据格式如下表：



第四步：我们把行中相同项目转化为独立的一列（方法可以看我上一篇帖子 【新提醒】实现行中相同项目转化为独立的一列-Excel基础应用-ExcelHome技术论坛 -），把我们的数据转变为如下格式，这个时候我们的数据已经可以作为下拉框的选项值来使用了：




第五步：但是这个时候数据值改变可以捕获，但是项数量变化还不能自动变化，例如如果我们增加了深圳分公司，数据验证里的序列仍然是a2-a4单元格，这样值无法更新。


为解决这个问题，我们在公式选项卡下，点击名称管理器，为公司设置一个新的名称，点击公式-名称管理器-新建


为公司列新建一个名称，大家注意看，这个时候，引用位置不是$A2-$A4这样的样子，而是 查询名+列名的形式：fun_getset[公司]。这种方式的引用位置，会始终保证引用的是公司这一列的所有值，未来这里不管是1行还是100行，下拉框都会准确的引用这里的所有值。


第六步：现在，为了保证每次使用，这里的数据都是最新的，我们需要把查询设置为每次打开工作簿就刷新：


把打开文件时刷新数据前面的勾勾上。


万事大吉，现在，我们实现了一个可以自动同步可选值的下拉框列表。改变下拉框数据，只要登录速六网页面更新就行，再也不用通知所有使用者更新，或者发新模板了。

内容比较多，文章只能简单介绍步骤，在B站有从零开始逐步制作的视频：https://www.bilibili.com/video/BV1fD421J792/

jany1312

我们公司还在用2010，有的电脑还是2003

世界客都

谢谢分享，做的不错

adfs945

没说的 ，一句话，厉害！

adfs945

谢谢分享，学无止境！o(∩_∩)o...哈哈