Power Query：像专业人士一样导入与转换数据

如果您每周都要花费数小时下载 CSV 文件、删除空行、格式化日期，并编写复杂的嵌套公式，仅仅是为了让数据满足分析需求，那么您所付出的努力可能已经超出了必要的范围。欢迎了解 Power Query——这是直接内置于 Microsoft Excel 中最强大的数据自动化工具。

Power Query 常被称为“获取和转换数据”，它允许您连接到几乎任何数据源，清理和重塑信息，并将其加载到您的电子表格中。最棒的一点是什么？它会记录您的所有步骤。下次收到新数据时，您无需重复手动操作；只需点击刷新即可。

在这份全面指南中，我们将探索 Power Query 是什么、如何使用它的界面，并通过一个实际案例，演示如何将杂乱的数据集转换为干净、可用于分析的信息。

什么是 Power Query？

Power Query 是一个数据连接和准备引擎。在数据库管理领域，这个过程被称为 ETL：提取（Extract）、转换（Transform）和加载（Load）。

• 提取：从各种来源（Excel 工作簿、CSV 和文本文件、网页、SQL 数据库，甚至是云服务）获取数据。
• 转换：清理和重塑数据。这包括筛选行、更改数据类型、拆分文本、合并表以及创建计算列。
• 加载：将处理好的数据直接输出到 Excel 表格、数据透视表或 Power Pivot 数据模型中。

传统上，Excel 用户依靠 TRIM、PROPER、SUBSTITUTE 和 VLOOKUP 等函数的组合，再加上手动复制粘贴来处理这些任务。Power Query 通过一个可视化的、用户友好的界面取代了这种繁琐的工作流程。

为什么应该学习 Power Query

如果您还在犹豫是否要学习这款新的 Excel 工具，以下是掌握 Power Query 能为您的工作效率带来质的飞跃的原因：

• 零代码要求：虽然 Power Query 运行在一种名为“M”的公式语言上，但您可以仅使用功能区按钮和右键菜单完成 95% 的数据转换工作。
• 无需 VBA 即可实现自动化：您不需要知道如何编写宏来自动执行重复性任务。Power Query 会记住您“应用的步骤”，并自动将其应用于新数据。
• 处理大数据：Excel 的物理限制是 1,048,576 行。Power Query 可以连接到包含数百万行的数据集，在后台对数据进行聚合，并仅将汇总结果加载到您的电子表格中。
• 非破坏性编辑：Power Query 从不更改您的原始数据源。它创建连接，读取数据，并输出一份清理干净的副本。

快速入门：Power Query 编辑器

要访问 Power Query，请打开一个空白的 Excel 工作簿，并导航到功能区上的数据选项卡。在最左侧找到获取和转换数据组。

在这里，您可以点击获取数据来查看可用数据源的下拉菜单。选择文件并点击“转换数据”后，Excel 会在一个新窗口中打开 Power Query 编辑器。此界面主要由四个区域组成：

1. 功能区：位于顶部，包含主页、转换、添加列和视图等选项卡。您所有的工具都在这里。
2. 查询窗格：位于左侧，列出工作簿中所有活动的数据连接。
3. 数据预览：中央网格，实时预览数据在当前选定步骤下的外观。
4. 查询设置窗格（应用的步骤）：位于右侧。这是编辑器最重要的部分。每次您进行更改时，Power Query 都会将其作为一个连续步骤记录在此处。您可以随时删除、重新排序或编辑这些步骤。

分步演练：清理杂乱的销售数据

让我们看一个实际的真实案例。假设您从公司的 CRM 系统导出一份每周销售报告。原始导出数据非常杂乱，包含不必要的标题、组合在一起的文本字符串以及不一致的格式。

以下是我们杂乱原始数据的示例：

系统导出：第三季度销售报告	Column2	Column3
生成时间：10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

如果使用传统公式，我们将不得不使用 LEFT、RIGHT、FIND 和 VALUE 函数来提取销售代表的姓名并修正数字。让我们改用 Power Query 来实现。

第 1 步：导入数据

将杂乱的数据保存为 CSV 或 Excel 文件。打开一个新的 Excel 工作簿，转到数据 > 获取数据 > 来自文件，然后选择您的文件。当预览窗口出现时，点击转换数据。随即会打开 Power Query 编辑器。

第 2 步：删除前几行并提升标题

数据的前两行是系统导出的元数据，而不是实际的数据记录。我们需要将它们删除。

• 在“主页”选项卡上，点击删除行 > 删除前几行。输入“2”并点击确定。
• 现在，我们实际的列标题位于第 1 行。点击“主页”选项卡上的将第一行用作标题按钮。瞬间，“Rep_ID_Name”、“Order_Date”和“Revenue”就成了正式的列标题。

第 3 步：拆分销售代表列

“Rep_ID_Name”列同时包含了 ID 号和员工姓名，中间由连字符分隔。

• 右键点击“Rep_ID_Name”列标题。
• 选择拆分列 > 按分隔符。
• 选择“自定义”并输入连字符 ( - )。点击确定。
• Power Query 会将数据拆分为两列。您现在可以双击新的列标题，将它们重命名为“Rep_ID”和“Rep_Name”。

要清理 Bob 姓名中的下划线 (Bob_Jones)，请右键点击 Rep_Name 列，选择替换值，在“要查找的值”框中输入下划线 (_)，将“替换为”留空或添加一个空格。点击确定。

第 4 步：标准化数据类型

注意到我们的日期和收入格式完全不同了吗？Power Query 让标准化这一过程变得非常简单。

• 点击“Order_Date”列标题左侧的小图标（它可能显示为“ABC”或“Any”）。将其更改为日期。Power Query 会自动将不同的日期格式转换为统一的标准日期。
• 点击“Revenue”旁边的图标，将其更改为货币（或小数）。它会去掉美元符号，并将所有内容格式化得干干净净。

第 5 步：添加条件列

假设我们希望将超过 1,000 美元的销售额归类为“高价值”。与其在 Excel 中编写像 =IF(C2>=1000, "High Value", "Standard") 这样复杂的 IF 函数，我们不如使用 Power Query 的用户界面。

转到添加列选项卡，然后点击条件列。设置规则：如果 [Revenue] 大于或等于 1000，则输出“High Value”，否则输出“Standard”。在后台，Power Query 会为此步骤生成以下 M 代码：

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

合并查询：VLOOKUP 的现代替代方案

数据分析中最常见的任务之一是合并表格。如果您有一个包含每位销售代表所在区域的单独表格，您通常可能会求助于我们的 VLOOKUP 完整指南 来引入该数据。

然而，运行数千个 VLOOKUP 或 INDEX 和 MATCH 公式会极大地拖慢您的工作簿。在 Power Query 中，您使用的是合并查询功能。

只需将两张表都导入到 Power Query 中，选择您的主销售表，然后在主页选项卡上点击合并查询。选择第二张表（区域表），点击两张表中相匹配的列（例如“Rep_ID”），然后点击确定。Power Query 会在几秒钟内执行相当于超高速 VLOOKUP 的操作，无论您有十行还是上千万行数据。

逆透视数据：Power Query 的秘密武器

通常，您会收到已经按类似透视表结构分组的数据（例如，按列排列的月份：1月、2月、3月、4月）。虽然这便于人类阅读，但对于创建图表或数据透视表来说却非常糟糕。

选择您的标识列（例如销售代表姓名），右键点击标题，然后选择逆透视其他列。Power Query 瞬间就会将您宽阔的交叉表格数据转化为一个扁平的表格布局，并带有新的“属性”（月份）和“值”（销售额）列。使用标准 Excel 公式几乎不可能做到这一点，这也使得逆透视成为 Power Query 最受赞誉的功能之一。

加载转换后的数据

一旦您的数据变得完美无瑕，就可以将其发送回 Excel 了。

在“主页”选项卡上，点击关闭并上载。默认情况下，这会将转换后的数据加载到新工作表上一个全新的绿色 Excel 表格中。如果您更喜欢将数据直接发送到分析阶段，您可以点击下拉箭头，选择关闭并上载至...，然后选择数据透视表报告。如果您需要回顾如何构建这些汇总，请查看我们的数据透视表初学者创建指南。

“刷新”按钮的魔力

下周当您收到一份新的销售导出原始数据时，Power Query 的真正威力就会显现出来。请不要重复上述步骤！

只需用新的 CSV 文件覆盖旧文件即可（保持完全相同的文件名和文件夹路径）。然后，打开您的 Excel 工作簿，在清理后的数据表中的任意位置点击右键，然后点击刷新。

Power Query 会连接到该文件，重新应用每一个步骤——删除行、提升标题、拆分列、替换文本、检查条件并合并表格——并在瞬间更新您的最终输出。这是 Excel 自动化工作流程中的核心组件。

借助 AI 简化您的数据之旅

虽然 Power Query 能出色地处理结构化转换，但有时您需要特定的条件逻辑或复杂的文本解析，这就需要高级 Excel 公式或自定义 M 代码。与其在论坛上苦苦搜寻答案，不如利用人工智能。

如果您发现自己正在为编写完美的自定义列计算而苦恼，ExcelGPT 将是您的得力助手。只需用自然语言描述您想要实现的目标——例如，“我需要一个公式，从混合的文本字符串中仅提取数字”——ExcelGPT 就能瞬间生成正确的公式或 M 代码。将 Power Query 与 AI 数据清理工具结合使用，您将获得一套无懈可击的数据分析工具包。

常见问题解答

Power Query 会更改我的原始数据文件吗？

不会。Power Query 会创建与您的源数据的单向连接。它读取数据，在内存中应用转换，并在 Excel 中输出新结果。您的原始 CSV、数据库或工作簿保持完全原封不动且安全。

我可以在 Mac 上使用 Power Query 吗？

可以，Microsoft 已经显著改善了 Excel for Mac 中对 Power Query 的支持。虽然 Mac 版本传统上缺乏 Windows 上可用的一些高级连接器和 UI 功能，但在现代版本的 Microsoft 365 中，您现在可以流畅地连接本地文件、数据库并刷新现有查询。

Power Query 中的“合并”和“追加”有什么区别？

合并（Merge）等同于 VLOOKUP 或 INDEX/MATCH。您可以使用它通过匹配两个表之间的共同 ID 来添加新的数据列。追加（Append）就像在工作表底部复制并粘贴数据。您使用它将多个表格上下堆叠在一起，添加新的行（例如，将一月份和二月份的销售额合并）。

为什么我的 Power Query 刷新失败了？

查询刷新失败的最常见原因是源文件被移动、重命名或删除了。另一个常见问题是原始数据中的列标题发生了改变（例如，系统将“Revenue”改为了“Total Revenue”）。要解决此问题，您可以打开 Power Query 编辑器，转到“应用的步骤”窗格，更新“源”步骤或在您的步骤逻辑中重命名该列。