程序员经验分享-hwhale

Excel：使用powerquery进行多表合并

2023-05-16

​注：本文原创为：https://www.cnblogs.com/fanyu2019/p/11175827.html,本文在原创的基础上添加修改了一点内容

目录

  • 一. 单工作簿多工作表合并
  • 二. 多工作簿单工作表合并
  • 三. 多工作簿多工作表合并
  • 注意事项：

工作中常遇到需汇总多张表进行分析的情况，本文以某公司销售数据（数据为虚构数据）为例介绍使用powerquery合并excel表的方法。

本文中所使用数据格式相同，且工作表中第一行为标题行，数据不规范可能使合并汇总数据存在问题或合并不成功，注意事项请移至文末进行查看。同时本文操作工具为office365。

多表合并存在多种情况：
在这里插入图片描述

一. 单工作簿多工作表合并

原始数据中存在多sheet页，进行单工作簿的多工作表合并，先看原始数据及处理之后的数据：

原始数据▼
在这里插入图片描述

最终数据▼
在这里插入图片描述

第一步：新建查询（进入powerquery编辑器）
点击【数据】>【获取数据】>【来自文件】>【从工作簿】，选择原始数据，导入
在这里插入图片描述

第二步：追加查询
点击【主页】>【追加查询】>【追加查询】，选择“三个或更多表”，依次添加工作表，得到合并后的数据
在这里插入图片描述

第三步：关闭并上载

点击【关闭并上载】>【关闭并上载】，可在excel中查看汇总后的数据
在这里插入图片描述

第四步：excel中对数据进行最后处理

在excel中对数据进行简单处理，得到最终汇总数据
在这里插入图片描述

二. 多工作簿单工作表合并

原始数据含有多个工作簿，但每个工作簿中只有一个工作表**（注意：这里的单工作表是指每个工作簿中的工作表只有一个，且工作表的名称需要一致！！如果每个工作簿中只有一个工作表，但是表名不一致，需要按照本文稍后提供的第三种方法即多工作簿多工作表的方法进行合并）**，进行多工作簿的单工作表合并，先看原始数据及处理之后的数据：

原始数据▼
在这里插入图片描述

最终数据▼
在这里插入图片描述

第一步：新建查询（进入powerquery编辑器），合并
点击【数据】>【获取数据】>【来自文件】>【从文件夹】，选择原始数据，导入，点击【组合】>【合并和编辑】，选择工作表，点击【确定】，在powerquery中可查看到已合并的数据

在这里插入图片描述

第二步：关闭并上载
查看合并后的数据，点击【关闭并上载】

注：由于第三个工作表中存在编辑过的空行，合并时空行也进入到合并的数据中，可在合并之前对工作表数据进行处理
在这里插入图片描述

第三步：excel中对数据进行最后处理
在excel中对数据进行简单处理，得到最终汇总数据
在这里插入图片描述

三. 多工作簿多工作表合并

实际工作中常常存在需要合并文件夹中的excel数据，且每个工作簿中含有多张工作表，进行多工作簿的多工作表合并，先看原始数据及处理之后的数据：

原始数据▼

在这里插入图片描述

最终数据▼
在这里插入图片描述

第一步：新建查询（进入powerquery编辑器）

点击【数据】>【获取数据】>【来自文件】>【从文件夹】，选择原始数据，导入，点击【转换数据】
在这里插入图片描述

第二步：删除其他信息，保留content及name列

保留content及name列，点击【主页】>【删除列】>【删除其他列】。这些删除的列一般我们都是用不到的，所以我们直接删除提高效率

在这里插入图片描述

第三步：自定义列，返回工作表记录

点击【添加列】>【自定义列】，输入新列名及公式：Excel.Workbook([Content],true)

注：
公式Excel.Workbook([Content],true)需区分大小写

在这里插入图片描述

公式解析：

  • 功能：从Excel工作簿返回工作表的记录
  • 参数：Excel.Workbook( workbook as binary, optional useHeaders as nullable logical, optional delayTypes as nullable logical) as table
    第一个参数是要解析的字段，返回一个table，第二个是可选参数逻辑值，参数使用true，就是指定数据使用第一行做为标题。

第四步：展开数据并删除多余数据

  1. 选中第三步中返回的table列及name列，点击【主页】>【删除列】>【删除其他列】

    注：保留name列为保留数据来源，若不需要可以删除

  2. 点击table列，进行展开

  3. 展开的数据中，选中table列及数据源列，点击【主页】>【删除列】>【删除其他列】

  4. 展开table列

在这里插入图片描述

注：若需保留数据来源（工作簿名、工作表名），在数据处理中对数据来源列进行保留即可

第五步：关闭并上载

点击【关闭并上载】>【关闭并上载】，可在excel中查看到已合并的数据

在这里插入图片描述

第六步：excel中对数据进行最后处理

在excel中对数据进行简单处理，得到最终汇总数据

在这里插入图片描述

注：当文件夹中的数据改变中，可右键进行【刷新】即可更新数据

注意事项：

  1. 数据格式：此方法仅适用于数据格式相同的数据表合并

  2. 合并单元格：工作表中应避免出现合并单元格

  3. 空行：若工作表中存在编辑过或有格式的空行，合并时空行也会保留，需在合并之前对工作表数据进行处理：Ctrl+shift+↓选中所有空行，Ctrl±删除所有空行

  4. 筛选很多的下拉值时只会显示前1000个值，如果想要筛选1000后的值可以考虑在原始数据中改变一下值的位置

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

Excel

powerquery

进行多表合并

Excel：使用powerquery进行多表合并 的相关文章

  • 通过文本自动创建到另一个工作表的超链接

    我想知道如何基于各自工作表中两个单元格具有的相同文本值 通过脚本自动创建从一个 Excel 工作表到另一个 Excel 工作表的超链接 如果这可以在没有脚本的情况下完成 使用某种公式 如 VLOOKUP 这将是更好的选择 谢谢你的时间 使用

  • Excel VBA - 循环文件夹中的文件、复制范围、粘贴到此工作簿中

    我有 500 个包含数据的 Excel 文件 我会将所有这些数据合并到一个文件中 实现此目标的任务列表 我想循环遍历文件夹中的所有文件 打开文件 复制此范围 B3 I102 将其粘贴到活动工作簿的第一张工作表中 重复但在下面粘贴新数据 我已

  • 检查所选单元格是否在特定范围内

    我正在使用 C 创建 Excel 加载项 如何检查选定的 或代码中范围表示的单元格 是否在特定范围内 例如如何检查单元格 P 5 是否在 A 1 Z 10 范围内 Use Application Intersect 像这样 在VBA中 Su

  • VBA rand 如何使用上限和下限生成随机数？

    所以也许这是多余的 也许这就像问为什么大多数人生来就有 5 个手指 最后的简短答案总是 因为事情就是这样 而且它就是这样工作的 但我讨厌这个答案 该死的我想知道怎么做VBA 中的 Rnd 函数有效 Ms Office Excel 的 MSD

  • 如何粘贴到Excel B列的最后一行？

    我需要将单元格从 H2 L2 一直向下剪切并将其粘贴到 B 列的最后一行 数据每次都会不同 所以我无法对任何范围进行硬编码 VBA 代码会很好 从 H2 L2 向下剪切并粘贴 插入到 B 列的最后一行 到目前为止我得到了 Range H2

  • 列表框：添加组合框作为项目？

    是否可以将列表框的每个项目都作为组合框 我需要这个 因为我将列表框设置为可检查 然后我需要让用户从列表中每个元素的不同选项中进行选择 Thanks 如果您不打算分发您的应用程序 那么您还可以查看 TreeView 控件 请参阅此示例 COD

  • 如何每次使用按钮将数据添加到 MATLAB 中的现有 XLSX 文件？

    我有一个函数可以生成一些变量 例如分数 对 错 未回答 使用按钮调用此功能 问题是如何每次将函数生成的这些值添加 附加到 XLSX 文件中 或者 如何创建 MAT 文件以便可以添加它 可能的解决方案是什么 附加到 xls 文件所涉及的挑战是

  • 使用 Cucumber Scenario Outline 处理 Excel 电子表格

    如果可能的话 我试图找到一种更优雅的方法来处理从与 Excel 电子表格行 第 n 个 相关的 Cucumber Scenario Outline 中调用第 n 个数字 目前 我正在使用迭代编号来定义要从中提取数据的 Excel 电子表格的

  • 在VBA中将字符串文本拆分为单独的行

    我的 excel 或 csv 文件 中有 2 个文本框 如下所示 文本框 1 包含 11111 22222 33333 文本框 2 包含 55555 11111 22222 33333 55555 我希望 之间的文本位于 3 个不同的行上

  • Windows Azure 远程站点“Microsoft.ACE.OLEDB.12.0”提供程序未在本地计算机上注册[重复]

    这个问题在这里已经有答案了 以下代码在我的本地开发计算机上可以正常运行 但是当我部署它时 我收到以下错误消息Azure 远程网站 我已经查看了SO答案和谷歌搜索结果 但我仍然不清楚我必须在本地计算机上安装什么 这样当我将代码推送到Azure

  • 你将如何开始自动化我的工作？ - 第2部分

    后续这个问题 https stackoverflow com questions 2796128 how would you start automating my job 在经历了第一波进货 9 小时的复制 粘贴 后 我现在相信我已经满足

  • 如果 FIND 函数在 vba 中找不到任何内容，那么[重复]

    这个问题在这里已经有答案了 我目前正在自动化执行以下步骤的手动流程 1 提示用户打开一个数据文件并打开文件 2 插入4列 3 使用文件中已有的数据创建格式为 DD MM YYYY TEXT 的唯一字符串 其中文本是变量 4 使用 if 语句

  • 通过 PHP 检测 excel .xlsx 文件 mimetype

    我无法通过 PHP 检测 xlsx Excel 文件的 mimetype 因为它是 zip 存档 文件实用程序 file file xlsx file xlsx Zip archive data at least v2 0 to extra

  • MS Access 中的舍入

    VBA Access 中舍入的最佳方法是什么 我目前的方法是利用Excel方法 Excel WorksheetFunction Round 但我正在寻找一种不依赖Excel的方法 请注意 VBA Round 函数使用 Banker 舍入 将

  • 我需要代码在两行之间复制并粘贴到另一张表中，并给出任何值？

    例如 我有 50 行数据 第一行有学生的名字 我需要代码将数据从 RAM 复制到 RAMESH 在这之间我有 20 行 我需要代码来复制行并将其粘贴到另一张纸中 它不应该问我名字 默认情况下 它必须采用 RAM 和 RAMESH 名称 好的

  • 使用 VBA 通过简单命令从非连续范围的并集获取值到数组中（无循环）

    我有以下任务 表面上很简单 使用 VBA 将电子表格上多个列的值复制到二维数组中 为了让生活更有趣 这些柱子并不相邻 但它们的长度都相同 显然 可以通过依次循环每个元素来做到这一点 但这看起来非常不优雅 我希望有一个更紧凑的解决方案 但我很

  • 如何禁用 openpyxl 表中的自动过滤器？

    当我使用 openpyxl 创建表时 它默认在所有列上添加自动过滤器 使用中提供的示例可以重现该行为文档 https openpyxl readthedocs io en stable worksheet tables html 我想显示没

  • 如何在字符串vba中包含引号

    我想存储以下文本 Test1 Monday Test Abcdef 全部在字符串中包含引号 我知道要在字符串中包含引号 我必须包含 之前 但在这里这不是一个很好的解决方案 因为我在文本中有太多这样的解决方案 知道如何一次完成这一切吗 您有两

  • 字典、集合和数组的比较

    我正在尝试找出字典与集合和数组相比的相对优点和功能 我发现了一篇很棒的文章here http www experts exchange com articles 3391 Using the Dictionary Class in VBA

  • Confluence：使用 VBA 更新现有页面

    我尝试使用 VBA 更新 Confluence 页面 我的想法是使用REST API加载页面内容 修改内容然后上传修改后的版本 这是我的代码 Private Sub TestRESTApi Dim uname As String uname

随机推荐

热门标签