问题的形成
对于大多数 Excel 用户迟早会面临的一种非常标准的情况，让我们看一个漂亮的解决方案：您需要快速、自动地将大量文件中的数据收集到一个最终表格中。
假设我们有以下文件夹，其中包含几个文件，其中包含来自分支城市的数据：
文件的数量无关紧要，将来可能会发生变化。 每个文件都有一个名为 销售数据表所在的位置：
当然，表中的行数（订单）是不同的，但列的集合在任何地方都是标准的。
任务：将所有文件中的数据收集到一本书中，并在添加或删除城市文件或表中的行时自动更新。 根据最终的合并表，那么就可以构建任何报表、数据透视表、过滤排序数据等。主要是能够收集。
我们选择武器
对于解决方案，我们需要最新版本的 Excel 2016（默认情况下已内置必要的功能）或安装了免费插件的早期版本的 Excel 2010-2013 电源查询 来自 Microsoft（在此处下载）。 Power Query 是一个超级灵活和超级强大的工具，用于将数据从外部加载到 Excel 中，然后对其进行剥离和处理。 Power Query 支持几乎所有现有数据源——从文本文件到 SQL 甚至 Facebook 🙂
如果您没有 Excel 2013 或 2016，那么您将无法进一步阅读（开个玩笑）。 在旧版本的 Excel 中，这样的任务只能通过在 Visual Basic 中编写宏（这对初学者来说非常困难）或单调的手动复制（这需要很长时间并且会产生错误）来完成。
步骤 1. 导入一个文件作为样本
首先，让我们以从一个工作簿中导入数据为例，让 Excel “领悟”。 为此，请创建一个新的空白工作簿并...
- 如果您有 Excel 2016，则打开选项卡 时间 然后 创建查询 - 从文件 - 从书 （数据 - 新查询 - 来自文件 - 来自 Excel）
- 如果您有安装了 Power Query 加载项的 Excel 2010-2013，则打开选项卡 电源查询 并选择它 从文件 - 从书 （来自文件 - 来自 Excel）
然后，在打开的窗口中，转到我们的带有报告的文件夹并选择任何城市文件（哪个都没有关系，因为它们都是典型的）。 几秒钟后，应该会出现 Navigator 窗口，您需要在左侧选择我们需要的工作表（Sales），其内容将显示在右侧：
如果单击此窗口右下角的按钮 下载 （加载），然后表格将立即以其原始形式导入到工作表中。 对于单个文件，这很好，但是我们需要加载很多这样的文件，所以我们会稍微不同，点击按钮 更正 （编辑）. 之后，Power Query 查询编辑器应该显示在一个单独的窗口中，其中包含我们来自书中的数据：
这是一个非常强大的工具，可以让您将表格“完成”到我们需要的视图。 即使是对其所有功能的肤浅描述也需要大约一百页，但是，如果非常简短，使用此窗口您可以：
- 过滤掉不必要的数据、空行、有错误的行
- 按一列或多列对数据进行排序
- 摆脱重复
- 按列划分粘性文本（按分隔符、字符数等）
- 将文本按顺序排列（删除多余的空格、正确的大小写等）
- 以各种可能的方式转换数据类型（将文本等数字转换为普通数字，反之亦然）
- 转置（旋转）表并将二维交叉表扩展为平面表
- 使用 Power Query 中内置的 M 语言向表中添加其他列并使用其中的公式和函数。
- ...
例如，让我们在表格中添加一个带有月份文本名称的列，以便以后更容易构建数据透视表报告。 为此，请右键单击列标题 日期并选择命令 列重复 （重复列），然后右键单击出现的重复列的标题并选择命令 转换 - 月份 - 月份名称:
应形成一个新列，其中包含每一行的月份文本名称。 通过双击列标题，您可以将其重命名为 复制日期 为了更舒适 月，例如。
如果在某些列中程序不能完全正确地识别数据类型，那么您可以通过单击每列左侧的格式图标来帮助它：
您可以使用简单的过滤器排除有错误或空行的行，以及不必要的经理或客户：
此外，所有执行的转换都固定在右侧面板中，它们总是可以回滚（交叉）或更改其参数（齿轮）：
轻盈优雅，不是吗？
步骤 2. 让我们将请求转换为函数
为了随后重复为每本导入的书籍所做的所有数据转换，我们需要将创建的请求转换为一个函数，然后将其依次应用于我们的所有文件。 要做到这一点实际上非常简单。
在查询编辑器中，转到查看选项卡并单击按钮 进阶编辑 （查看 - 高级编辑器）. 应该打开一个窗口，我们之前的所有操作都将以 M 语言的代码形式编写。 请注意，我们为示例导入的文件的路径在代码中是硬编码的：
现在让我们做一些调整：
他们的意思很简单：第一行 （文件路径）=> 把我们的过程变成一个带参数的函数 文件路径，下面我们将固定路径更改为此变量的值。
全部。 点击 完成 应该看到这个：
不要害怕数据消失了——事实上，一切正常，一切都应该是这样的. 仍然要给它一个更易于理解的名称（例如 获取数据) 在字段右侧的面板中 名字 你可以收获 主页 — 关闭并下载 （首页 - 关闭并加载）. 请注意，我们为示例导入的文件的路径在代码中是硬编码的。 您将返回到 Microsoft Excel 主窗口，但右侧应出现一个面板，其中包含已创建的与我们的函数的连接：
步骤 3. 收集所有文件
所有最困难的部分都已过去，愉快和容易的部分仍然存在。 转到选项卡 数据 - 创建查询 - 从文件 - 从文件夹 （数据 - 新查询 - 从文件 - 从文件夹） 或者，如果您有 Excel 2010-2013，则类似于选项卡 电源查询. 在出现的窗口中，指定我们所有源城市文件所在的文件夹，然后单击 OK. 下一步应该打开一个窗口，其中将列出在此文件夹（及其子文件夹）中找到的所有 Excel 文件以及每个文件的详细信息：
点击 更改 （编辑） 我们再次进入熟悉的查询编辑器窗口。
现在我们需要使用我们创建的函数向表中添加另一列，该函数将从每个文件中“提取”数据。 为此，请转到选项卡 添加列 - 自定义列 （添加列 - 添加自定义列） 在出现的窗口中，输入我们的函数 获取数据，为其指定每个文件的完整路径作为参数：
点击后 OK 创建的列应添加到右侧的表中。
现在让我们删除所有不必要的列（如在 Excel 中，使用鼠标右键 - 删除)，只留下添加的列和带有文件名的列，因为这个名称（更准确地说，城市）对于每行的总数据很有用。
现在是“令人惊叹的时刻”——使用我们的功能单击添加列右上角带有自己箭头的图标：
…取消选中 使用原始列名称作为前缀 （使用原始列名作为前缀）并点击 OK. 我们的函数将加载和处理来自每个文件的数据，遵循记录的算法并将所有内容收集在一个公用表中：
为了完整美观，您还可以从带有文件名的第一列中删除 .xlsx 扩展名 - 通过标准替换为“nothing”（右键单击列标题 - 替代) 并将此列重命名为 城市. 并更正日期列中的数据格式。
全部！ 点击 主页 – 关闭并加载 （首页 - 关闭并加载）. 查询收集到的所有城市的所有数据都会以“智能表”格式上传到当前的 Excel 工作表中：
创建的连接和我们的装配函数不需要以任何方式单独保存 - 它们以通常的方式与当前文件一起保存。
将来，文件夹（添加或删除城市）或文件（更改行数）中的任何更改，直接右键单击表格或右侧面板中的查询并选择命令 更新并保存 （刷新） – Power Query 将在几秒钟内再次“重建”所有数据。
PS
修正案。 在 2017 年 XNUMX 月更新后，Power Query 学会了如何自行收集 Excel 工作簿，即不再需要制作单独的函数——它会自动发生。 因此，不再需要本文中的第二步，整个过程变得明显更简单：
- 创建请求 - 从文件 - 从文件夹 - 选择文件夹 - 确定
- 出现文件列表后，按 更改
- 在查询编辑器窗口中，用双箭头展开二进制列，然后选择要从每个文件中获取的工作表名称
就这样！ 歌曲！
- 将交叉表重新设计为适合构建数据透视表的平面表
- 在 Power View 中构建动画气泡图
- 将不同 Excel 文件中的工作表组合成一个的宏