Transcription
今天，让我们来看看一个你可能已经遇到过的常见任务，那就是将多个 Excel 文件合并到一个文件中。例如，假设你向同事发送了一个模板来收集一些数据。你收到了分开的文件。现在你想将它们合并。基本上，你想在一个文件中整合或追加数据。一个解决方案一直是 VBA，但这次我们将使用一种更简单的方法。我们将使用“获取和转换”（Get & Transform），也称为 Power Query，来自数据选项卡。（欢快的音乐）（嗖嗖声）（气泡破裂声）我的目标是通过直接连接到文件夹来合并这些文件中的数据。现在，有一些要求。我不想包含任何不包含“_Data”的文件，并且我还想确保排除任何非 Excel 文件。让我们快速看一下文件的内容。我有一个特定月份的单元格信息。数据不在 Excel 表格中。但是，文件的结构是相同的。它们都有相同的标题。现在，我想确保排除这个文件。现在，我最终想要的结果是一个将这些文件中的数据合并的 PivotTable。我想按公司和客户获取汇总销售报告。让我们打开一个空白工作簿。转到数据 + 获取数据 + 从文件 + 从文件夹。浏览文件夹。我的文件就在这里。确定和确定。现在，Power Query 会继续，创建一个到文件夹的连接，并检查文件夹中的内容。如果所有数据都格式正确，如果它只包含我需要的文件，我就可以直接将数据合并并加载到工作簿中，但我需要对其进行一些转换。所以让我们继续转换数据。在我合并这些文件（我们可以在这里看到）之前，我想做的是过滤掉我不需要的文件。所以我设置了任何不包含“_Data”的都不需要。所以对于文本过滤器，我想包含所有包含“_Data”的内容，然后确定。我还想确保我将所有内容限制为 Excel 文件，以“.xls”开头，然后确定。这是我的两个步骤。现在我准备好合并内容了。我所要做的就是点击这个双向下箭头，合并文件，Power Query 将会弹出导航器，询问我想要合并哪个选项卡或哪个数据。我在这里做出的决定是基于一个示例文件。所以它选择了第一个文件，也就是那个文件夹里的 Bere Kleid。从这个第一个文件中，我可以选择我想要的选项卡，然后确定。现在，Power Query 将会尝试弄清楚它应该对该示例文件应用什么转换，将该转换应用于所有其他文件并追加数据。这就是它为我所做的工作。现在，不幸的是，在这一点上，它并不完美，因为我的数据不是完美的 Excel 表格。所以我需要做一些调整，但它已经添加了所有这些步骤。在我点击这个按钮之前的最后一个步骤是这个步骤。它所做的是添加了另一个过滤器。它过滤掉了隐藏文件。所以如果我碰巧有一个文件在后台打开，我不会遇到问题。它将被自动过滤。然后它继续创建了所有这些转换，这里并不多。它试图弄清楚它应该对该示例文件做什么，使其格式正确，以便它可以将其应用于所有其他文件，然后再将它们追加在一起。这里的函数将此转换应用到其他文件，然后再将其合并。这就是这个步骤的意思。然后它继续并将此列重命名为 source，然后删除了关于文件的所有其他信息，然后展开了我们的数据并应用了更改类型。所以现在，我们知道在加载之前需要清理它，但是我们在哪里进行清理呢？我们是清理追加的版本，还是清理示例的版本？现在，这取决于你，但尽可能地，尝试在 Power Query 将它们合并在一起之前，在示例文件级别进行清理。但是，有些步骤需要在追加级别上进行，因为只有在追加级别上，我才有文件名，我实际上可以用它来获取公司名称。所以如果我回溯一步，在我们展开表格之前，我实际上可以在此之后立即处理它。我也可以在这里最后处理它。在我的情况下，我将在那里处理它，所以我将把它重命名为 Company + 插入步骤，并且我还将从文件名中提取公司名称。转换 + 提取 + 分隔符之前的文本。我的分隔符是下划线，但我只是要在此基础上添加“_Data”，然后确定。现在，一旦它展开，我就有了正确的公司名称展开了。现在，因为我的标题已更改，“更改类型”步骤不再适用。我实际上将退出，因为它没有意义将“更改类型”应用于这些。我应该先清理这些。让我们快速看一下我们需要进行的清理。首先，列标题重复了，其次，在一些数据集的底部有一些空行。让我们通过清理示例来清理它。在示例文件中，我不需要“提升的标题”。我实际上需要做的是删除前两行。删除顶部行，输入两个，然后确定。现在，我想在这里提升标题，所以使用第一行作为标题，接下来，我想处理这些空行。所以删除行 + 删除空白行。现在，当我提升标题时，它自动做的另一件事是它决定更改类型。但这对我没有帮助，因为我仍然需要在那里进行类型设置。所以我更喜欢从这里删除“更改类型”步骤，并在最后一步正确地进行。所以我将按 Ctrl + A 选择所有内容，转换 + 检测数据类型，然后仔细检查所有内容是否正确。好了，我的数据干净了。让我们将其加载为 PivotTable。所以关闭并加载 + 关闭并加载到 + PivotTable 报告。我们将其放在现有工作表中，然后确定。现在，让我们按客户和公司分析销售额。我只是更新一下设计，以表格格式显示。这样，我可以看到每个公司对客户的总销售额，并看到哪些公司在向同一客户销售。现在，假设我收到了新数据。Lucas Basics 决定发送他们的数据，所以我将获取文件并将其放在这里。他们输入了数据，只是将列的顺序放错了。所以销售价值不再在最后，它在 E 列，而不是其他列所在的 F 列。这会给我们带来问题吗？让我们检查一下。右键单击并刷新。Lucas Basics 的数据就在这里。列的顺序无关紧要。重要的是拥有相同的列标题，因为看看这个。如果我打开 Lucas Basics_Data，而不是将“销售价值”的“S”大写，而是小写“s”，我将保存它，然后返回这里并刷新，数据将不会显示出来。现在，对于这类问题，你需要决定在哪里进行清理。你是想清理源文件，还是这是一个常见问题，有些标题可以大写，有些不能？这是你想在 Power Query 端处理的事情吗？如果你决定在 Power Query 端处理它，你可以添加一个新步骤，在合并它们之前将所有标题大写。但要做到这一点，你需要熟悉 M 函数。这是我们将在课程的高级部分涵盖的内容。如果你有兴趣了解更多关于我的 Power Query 课程的信息，请查看此视频下方的描述。现在，如果你想要的最终结果是一个表格而不是 PivotTable 呢？嗯，你所要做的就是更改加载目标。右键单击最终查询 + 加载到，选择表格而不是 PivotTable。现有工作表 $A$1 就可以了，然后单击确定。它正在询问我们是否真的要删除这个 PivotTable。我们确实要，这就是我们的表格。所有数据都堆叠在一起。这就是你如何使用 Excel 的“获取和转换”功能将多个文件的数据合并到一个文件中。我希望你喜欢这个视频。如果你喜欢，请给它一个赞，（气泡破裂声）如果你喜欢你看到的内容，请考虑（鼠标点击）订阅此频道（铃声响起），如果你还没有这样做的话。并且不要忘记点击那个铃铛，这样你就可以在有新视频发布时收到更新，我将在下一个视频中见到你。（欢快的音乐）