- 问题 - Excel里面怎么用VBA控制Power Query查询的批量刷新？...比如，根据查询的名称特征控制部分查询一起刷新，即纪要自动刷新多个，又不是全部刷新。 - 解答 - 我们知道，通过VBA刷新查询，最简单的是通过刷新查询的连接。...所以，如果能获得所有查询的连接名称，就可以通过连接名称进行规则化批量控制。...实际上，在VBA中要获得所有连接名称很简单，遍历工作簿的Connections对象即可，唯一有点特别的是，定义变量时，要用WorkbookConnection，而循环时遍历的对象是Connections
我们把数据通过Power Query进行导入，然后在导入一个关键词查询表。 ? 2个表导入后，我们可以在关键词表这里进行添加列的操作，把找到所对应的的目录表都找出来，然后展开即可。...在查询数据表的旁边插入数据透视图，随后我们做一个VBA的按钮进行针对透视表的刷新。这样一个简单的查询功能就做出来了。 查询功能可以参考之前的文章。
一、数据透视3步搞定工作表拆分 用数据透视表对表格进行拆分非常简单，只需要3个简单的步骤即可，具体如下： Step01、插入数据透视表 Step02、将分拆条件拖入筛选框（如果拆分结果表需要保留该列，...，如下图所示： 二、Power Query实现固定分类的工作表一键刷新式拆分 使用数据透视的方式进行工作表的拆分操作很简单，但是存在2个问题： 1、拆分后的工作表仍然是数据透视表 2、拆分后的每个表均包含了所有数据...所有需要的表格生成后，即可将结果返回Excel中，形成不同的分表，如下图所示： 这样，以后只要单击全部刷新即可得到最新的拆分结果，如下图所示： 三、VBA实现终极动态拆分 对于第二种Power Query...的拆分方法，也存在一个问题：即如果需要拆分出来的表格个数并不是固定的，那就无法实现了——这是目前Power Query的一个弱点（汇总处理数据很强大，但不能动态拆分表），对于这种情况，只能通过VBA或者一些插件来完成来完成...关于这方面的代码网上有很多，搜索一下就能找到，实际工作中我用得不多，也懒得写了： 以上介绍了数据透视、Power Query及VBA三种批量拆分工作表的方法，各有优缺点，在我的实际工作过程中，按固定分类拆分的情况比较多
这个时候，不要忘了还有咱们的老朋友VBA嘛！通过Power Query与VBA的强强联合，咱们就可以刷新对带密码Excel工作簿数据的自动刷新。...里处理好数据源的引用路径，既方便Power Query的引用，也方便在VBA里引用： 同时将这个表接入到Power Query里： 这样，就可以在获取数据源时直接引用已经处理好的路径...- 3 - VBA实现解密刷新 通过VBA，我们可以用密码打开数据源工作簿，清除密码，然后刷新查询，刷新完毕后再对数据源工作簿重新加密……是不是很像“把大象放进冰箱里”的三个步骤？...ThisWorkbook.Connections("查询 - 表2").Refresh '3、重新打开工作簿，设置密码并保存关闭 Set wb = Workbooks.Open(path...： 这样，我们就又可以一键刷新了： 注意，这里数据刷新后，查询上仍然会提示如文章开头所说的错误信息，但这并不影响数据的更新和使用。
前期，我用VBA写了个小工具，用于解决一个问题：表格很宽，有许多列，一眼看不到头，我们只想看自己需要的数据怎么办？...但是，这个工具还非常初级，比方我们只想看张三丰"精通"什么工具，以前写的那个VBA就实现不了，需要费神重新改代码。...此时，微软的Power BI系列组件之一Power Query现身了，我们无需任何代码与公式，这个表格30秒之内可以快速转化。...动图展示如下： 转换完成之后，表格就成了这个样子，你可以随意筛选了： Power query除了快速还有两个好处： 可以在数据源之外单独生成表格，不影响原表结构。...生成的表格保持和原表链接，如原表数据更新，此处只需要刷新即可。 Power Query的这种快速逆透视功能不仅适用于方便筛选，还可用于服装业尺码快速横排竖排转化等工作。
普通的透视表能提供常用度量值的快速显示。 ? ? 3. 使用VBA上的不同 Power Pivot不能使用VBA进行创建，只能利用VBA很小的功能。 普通透视表则可以利用VBA进行灵活处理。 4....更改数据的不同 Power Pivot如果要更改数据，必须从数据源去更改 普通透视表的数据一般使用的是表，只需要在表格数据上进行更改即可。 5....透视表列名更改为其他列时的反应不同 Power Pivot把透视表列名更改为其他列后不会发生变化 ? 普通透射比把列名更改为其他列后对应数据则会相应换位 ? 6....报表筛选页的不同 在Power Pivot透视表中，无法使用报表筛选页。 ? 普通透视表则可以使用报表筛选页生成筛选值的工作表。 ? 9....分组功能使用不同 Power Pivot透视表中，只有日期格式能实现自动分组功能，数字格式无法生成。 ? 普通透视表中，数字格式则可以实现自动分组功能。 ?
然而，【数据透视表】并没有改变，如图 8-11 所示。 图 8-11 “Transaction” 表已经更新，但【数据透视表】却没有更新 这不是什么大问题，只是一个小小的不便和提醒。...如果用户把数据加载到一个 Excel 表中，然后把它放入到一个 【数据透视表】中，是需要刷新【数据透视表】，以便让更新的数据流入【数据透视表】。 右击【数据透视表】【刷新】。...此时，【数据透视表】确实更新了，如图 8-12 所示。...图 8-12 一月到三月的记录现在显示在一个【数据透视表】中 【注意】 记住，如果查询被加载到 Excel 或 Power BI 的数据模型中，点击一次【刷新】就可以更新数据源和任何透视或可视化对象。...而当用户想刷新这个解决方案时，只需要单击【全部刷新】按钮就可以更新它。Power Query 将启动对 “Transactions” 表的刷新，这将启动对三个单独的数据表的刷新，为它提供数据。
如果是多个 Excel 工作表呢？我特意在网上搜索了一下，知乎有两篇文章，一篇是基于 VBA 的，另一篇是基于数据透视表多重合并计算区域的。 如何快速的合并多个 Excel 工作簿成为一个工作簿？...excel多表操作：如何快速完成多工作表汇总求和 VBA 对于普通 Excel 用户来说，掌握的人并不多。有了 Power Query 之后呢，合并工作表就变得轻而易举了。...在这个面板中，有一个名为 Jan 的查询，双击这个查询，进入 Power Query 编辑器 界面。...如果源数据有变化，比如某一条记录发生变化，新增一个工作表，都可以通过刷新按钮更新数据。...Query 的初步印象：Power Query 的核心是查询对象，通过查询对象连接不同的数据源获取数据，并对数据进行处理和转换，得到处理后的结果。
excelperfect 标签：VBA 当执行VBA过程代码后，如果想反悔，像在Excel中操作一样，使用Excel的撤销功能或者按Ctrl+Z来撤销VBA代码对工作表的改变，不会起作用。...这里，在jkp-ads.com中找到了代码，可以用来撤销VBA对工作表的操作。代码中，关键是两个类模块，创建了一个通用撤消处理程序，可以将其导入到任何项目。...类模块：clsExecAndUndo 这个类将保存clsUndoObject类的所有实例的集合（简单地说：它将保存所有已更改的对象的列表，并知道如何撤消这些更改）。...这里的程序可以撤消的操作通常仅限于对对象属性的更改，无法撤消插入或删除工作表、刷新查询表、更新数据透视表等操作。...有兴趣的朋友可以查看： https://jkp-ads.com/articles/undowithvba00.asp 研阅原文并下载示例工作簿。
有时候，我们希望只将Power Query相关的查询结果给用户，又或者需要将查询的结果固化下来（不随新数据的加入而刷新），而被其他查询引用（比如有些工作中需要做不同阶段的数据检查、校验等），...就需要对Power Query生成的查询或查询连接进行删除。...，又能利用VBA对Power Query的执行过程实现自动化！...---- 用VBA删除Power Query生成的查询，有以下2种情况。 一、删除连接，但不删除查询 即仅删除查询和结果数据表间的连接，使数据表不能刷新。...比如有如下查询“表1”： 其连接情况如下（特别要注意的是，查询名称为“表1”，查询连接的名称为“查询 - 表1”）： 那么，要删除查询连接，VBA语句如下：
几年以前就有人问我：如何在 Excel 中学习并应用商业智能？不久后 Power BI 来了。它们是可以完美融合的。如下： ? 我们提出了在 Excel 中 Excel BI 概念。...我们回归于简单，用 Excel 的点，线，面，条，块来快速基于数据模型的透视表构建可视化。...建议使用 PowerBI 构建数据模型，这样你既可以使用 PowerBI 进行高端大气的可视化，也可以用 Excel 连接之，进行中规中规的经典透视表。...而在下由于第一不精通VBA，第二精力有限，根本没时间去维护管理更新一个完全免费的工具。所以，就推出了纯净版的 Excel BI。...由于缺失了 VBA 的能力，所以无法使用一键连接 Power BI 的功能。但整体还是体现了商业智能的理念的。 我特地设置了一个小机关，让它到 2021年的7月过期，并希望看看是否会有多少人在使用。
Microsoft Excel发布于1985年，本质上是一种具有计算，绘图工具，数据透视表和宏编程语言等功能的工具，可以在Windows，Mac，Android，iOS等多种操作系统上运行。...Excel，Tableau和Power BI允许用户合并API并利用数据创建醒目的可视化效果。 5. Dashboards 仪表盘 Excel提供了有限的创建仪表板功能，刷新过程繁琐。...Power BI 用户界面非常易学，也因此被用户偏爱。 7. 语言依赖 Excel以及其他Microsoft Office程序的编程语言是VBA。...另外，当你从一个透视表选取项目时，Excel使用MDX来检索字段和数值。从Analysis Services多维数据集中导入数据时，也可以手动生成MDX查询。 但什么是MDX？...它的数据可视化和自助服务功能允许决策者打开一个报告或仪表板，并自由地向下钻取到细粒度的信息。这一解决方案可能很昂贵，但是当你需要创建非常多的重复报告并迅速更新的时候，这些钱花得就很值得。
当然，可以使用VBA来解决，但前提是你必须懂VBA。这里有一个简单快速的方法，使用Power Query。...找到“Extension”列并单击其右侧的下拉箭头，选择“.csv”文件类型，如下图4所示。 图4 此时，将只列出该文件夹中所有CSV文件列表。...然后，找到“Content”列并单击其右侧的合并按钮，如下图5所示。 图5 出现“合并文件”对话框，单击“确定”，如下图6所示。...图6 在Power Query编辑器中，单击“关闭并上载”按钮，如下图7所示。 图7 此时，这些CSV文件中的信息已合并至工作表中，如下图8所示。...图8 以后，当你更新了这些CSV文件的信息或者在该文件夹中添加了更多的CSV文件，只需简单地刷新查询即可实现信息更新。 当然，以上合并操作也适用于Excel文件，即快速合并多个工作簿中的工作表。
下面我们来逐一详解本月的更新吧。...可视化对象的关于信息 微软本次还给出了可视化对象的关于信息，例如： 可以看出矩阵的内置叫法其实就是：PivotTable（透视表）。...值得强调的是，这里的透视表比Excel中的透视表更加强大，参考罗叔此前文章：最复杂超级中国式报表。里面详细阐述了打造极致的矩阵（透视表）的详细方法，首创。...继续保持了和 Excel 以及 VBA 的一致性，非常好。...可以导出数据质量数据 在查询编辑器中，有几个地方可以监测数据质量，如下： 导出为： 导出为： 导出为： 导出为： Google 分析模板 如果你在使用Google分析，那可以接入数据并直接使用内置模板，
摘要：三维地图、预测工作表、引用外部数据查询、数据透视表更强大的功能改进、将Excel 表格发布到Office 365 Power BI实现数据的商业智能分析……Excel 2016在数据智能分析与展示上亮点多多...3.预测结果在新的工作表中呈现。 ? 03引用外部数据查询（新） 通过 Excel 2016 的内置查询功能，轻松快速地获取和转换数据。示例中以“从Web”插入数据源。...即可导入到表中，选中任一数据单元格，单击【设计】-【刷新】，表中数据同步实时更新。 ? 04数据透视表增强功能（新） Excel 以其灵活且功能强大的分析体验而闻名。...1.将光标定位在数据区域内，单击【插入】-【数据透视表】，勾选“将此数据添加到数据模型”并确定。 ? 2.单击“全部”，搜索框中输入“地区”然后拖到“列”字段中。 ?...7.单击【转至Power BI】。 ? 8.在【数据集】中单击导入的表格名称，在【可视化】中选择要插入的图表类型，例如【饼图】，并设置【图例】的值为【城市】，【值】为【营业面积】。 ?
摘要 三维地图、预测工作表、引用外部数据查询、数据透视表更强大的功能改进、将Excel 表格发布到Office 365 Power BI实现数据的商业智能分析……Excel 2016在数据智能分析与展示上亮点多多...3.预测结果在新的工作表中呈现。 ? 03引用外部数据查询（新） 通过 Excel 2016 的内置查询功能，轻松快速地获取和转换数据。示例中以“从Web”插入数据源。...即可导入到表中，选中任一数据单元格，单击【设计】-【刷新】，表中数据同步实时更新。 ? 04数据透视表增强功能（新） Excel 以其灵活且功能强大的分析体验而闻名。...1.将光标定位在数据区域内，单击【插入】-【数据透视表】，勾选“将此数据添加到数据模型”并确定。 ? 2.单击“全部”，搜索框中输入“地区”然后拖到“列”字段中。 ?...7.单击【转至Power BI】。 ? 8.在【数据集】中单击导入的表格名称，在【可视化】中选择要插入的图表类型，例如【饼图】，并设置【图例】的值为【城市】，【值】为【营业面积】。 ?
vlookup函数除了适用于两列对比，还可以用于表间的数据对比，如下图所示： 三、使用数据透视进行数据对比 对于大规模的数据对比来说，数据透视法非常好用，具体使用方法也很简单，即将2列数据合并后...，构造成明细表，然后进行数据透视——这种方法适用于多表数据对比，甚至可以在一些数据不太规范的场合下，减少数据对比的工作量，如下例子： 表间数据不规范统一，用数据透视递进巧比对 比如很多公司的盘点数据对比问题...Query进行的数据对比，可以随着数据源的更新而达到一键更新对比结果的效果。...比如，有两个表的数据要天天做对比，找到差异的地方，原来用Excel做虽然也不复杂，但要频繁对比，就很麻烦了，因此，可以考虑使用Power Query来实现直接刷新的自动对比。...1、将需要对比的2个表的数据加载到Power Query 2、以完全外部的方式合并查询 3、展开合并的数据 4、添加差异比对列 5、按需要筛选去掉无差异部分 6、按需要调整相应的列就可以将差异结果返回
此时，数据加载到 Power Query 中并创建两个查询步骤：“Source” 和 “Changed Type”，如图 7-3 所示。...图 7-3 该查询自动添加了一个 “Changed Type” 步骤 在构建任何解决方案时，首先的是要考虑将来更新这些数据时会发生什么。在构建一个 “逆透视” 解决方案时，这一点至关重要。...图 7-6 两个数据透视表由一个未透视的数据集生成 7.1.4 应对变化 此时，保存文件并把它发送回给用户，让用户继续更新它，数据分析师可能会感到相当舒服。...毕竟，Power Query 解决方案可以在任何时候刷新。 当然，数据分析师会这样做，用户进行了更新，然后将更新的文件发回给数据分析师。...问题是，在这些变化的情况下，刷新将如何进行？来找出答案，转到 “Sales” 工作表，分别单击【全部刷新】【刷新】按钮（第一个用于刷新查询，第二用于刷新【数据透视表】）。
一旦阅读并掌握了整本书的内容，就会意识到，用一个查询来处理这样的透视表结构罗列的数据集其实也是可能的。话虽如此，但这样做太过于复杂。...9.8 更新解决方案 随着数据的加载，现在可以构建一些可重复使用的商业智能。 9.8.1 使用数据 为了演示从导入到刷新的完整周期，需要使用“矩阵”或“数据透视表”建立一个快速报告。...图9-25 现在是时候向解决方案添加一些新的数据了 移动文件夹后，返回解决方案并点击【刷新】。 Power BI：转到【主页】【刷新】。 Excel：转到【数据】【全部刷新】。...图9-26数据已更新 这是多么令人难以置信，不仅可以很容易地【追加】多个文件，而且刚刚创建了一个可【刷新】的商业智能报表，当加入新的数据时，只需单击几下就可以更新文件，这就是现在的解决方案。...特别是考虑到 Power Query 不能被配置为只更新新的或数据发生改变的文件。每次用户单击【刷新】按钮时，Power Query 都会重新加载文件夹中所有文件的所有数据。
实现该功能有三个核心要点： 首先，Power Query单条件查询检索数据，即按照输入的单一货号从产品资料中找到该货号的所有信息。...最后，Power Query无法自动识别货号信息变更，因此使用VBA自动刷新。下面进行详细说明。...1.设立单条件检索功能 ---- （1）将准备好的产品资料表导入Power Query （2）新建一个表，如下图所示，取名为“查询条件”，也导入Power Query （3）在Power Query...中： 3.自动刷新 ---- 默认情况下，每次变更货号，需要如下图方式手动刷新产品资料信息，非常不方便。...在查询界面工作表输入以下VBA代码，即可变更货号自动刷新： 代码来源：施阳老师 https://pqfans.com/2402.html 以上，我们即完成了产品信息查询工具。
领取专属 10元无门槛券
手把手带您无忧上云
云服务器
ICP备案
实时音视频
即时通信 IM
对象存储
