别再浪费时间在Power Query上：用AI合并与分析学生数据

问题背景与痛点

想象一下，你是一名课程管理员、企业培训师或教师。你刚刚进行了两次独立的测试或培训，结果存储在两个不同的 Excel 表格中，我们称之为 Test1 和 Test2。

每个表格都包含重要的学生信息：名、姓、出生日期、加入项目的日期以及他们在该特定测试中的分数。

面临的挑战是：你需要创建一个合并报告，但只针对同时参加了两次测试的学生。手动在两个长列表之间交叉核对姓名既繁琐又容易出错。但工作远不止于此。对于最终报告，你需要：

• 将名和姓合并成一个格式统一的“姓, 名”列。
• 根据出生日期计算每个学生当前的年龄。
• 修复一个 Excel 未识别为正确日期的“加入日期”列。
• 合并两次测试的分数以获得“总分”。
• 根据总分是否达到某个阈值，分配“通过”或“未通过”状态。

使用 VLOOKUP 或 INDEX/MATCH 等传统 Excel 函数来完成这项工作，公式很快就会变得一团糟。更高级的用户可能会求助于 Power Query，但这又会带来其自身的复杂性。

传统 Excel 解决方案：Power Query 方法

对于经验丰富的数据分析师来说，Power Query 是处理此类任务的首选工具。它是内置于 Excel 中的强大数据转换引擎，无需编写基于单元格的公式即可处理合并、清理和重塑数据。然而，它远非一个简单的“点击即用”解决方案。

以下是使用 Power Query 解决学生数据问题所需步骤的高级概述：

1. 将数据加载到 Power Query： 首先，需要将两个表格（Test1 和 Test2）导入 Power Query 编辑器。它们通常被加载为“仅连接”查询，以避免在最终结果准备好之前弄乱工作簿。

2. 合并查询： 核心步骤是合并两个查询。你需要选择 Test1 查询，选择“合并查询”，然后选择 Test2 作为要合并的表格。为了找到参加了两次测试的学生，你必须执行内部联接，在“名”和“姓”列上匹配两个表格。

3. 展开和清理列： 合并后，会出现一个包含嵌套表数据的新列。你需要“展开”此列以拉入第二个表格的测试分数。然后，可以删除第二个表格中冗余的姓名列。

4. 为新增数据添加自定义列： 这是在 Power Query 内开始手动公式工作的地方。你需要：

   • 创建全名： 添加一个自定义列，使用类似 [LastName] & ", " & [FirstName] 的公式来创建格式化的姓名。
   • 计算年龄： 添加另一个自定义列。用于计算年龄（以年为单位）的 M 语言公式可能很棘手，类似于 Duration.Days(Date.From(DateTime.LocalNow()) - [Date of Birth]) / 365.25。然后你必须将其格式化为整数。
   • 计算总分： 添加一个简单的列，将 Test1 和 Test2 的分数相加。

5. 转换数据类型： 你必须手动选择“加入日期”列并将其数据类型更改为“日期”以修复格式问题。同样，确保数值列被识别为数字。

6. 添加条件列： 要创建“通过/未通过”状态，你需要使用“条件列”功能。你需要设置一个规则：如果“总分”大于或等于 140，则输出“通过”；否则，输出“未通过”。

7. 加载到工作表： 最后，完成所有这些步骤后，点击“关闭并加载”，将干净、合并的表格输出到一个新的 Excel 工作表中。

传统方法的局限性

虽然有效，但 Power Query 方法对于普通 Excel 用户来说充满了挑战：

• 陡峭的学习曲线： 理解“内部联接”、“左外部联接”和 M 公式语言等概念并不直观。
• 僵化且耗时： 该过程涉及十几次点击和几个手动创建的公式。如果出错，你必须返回编辑器并对“应用的步骤”进行故障排除。
• 灵活性差： 如果你的经理提出后续问题，比如“你能按他们加入的月份显示平均分吗？”你必须返回 Power Query 编辑器并添加更复杂的分组和转换步骤。
• 难以协作： 将此查询交给不懂 Power Query 的同事是行不通的。逻辑隐藏在编辑器中，难以审计或修改。

新解决方案：使用 Excel AI（通过匡优Excel）

与其在菜单和公式的迷宫中导航，不如直接告诉 Excel 你想要什么？这就是像 匡优Excel 这样的 Excel AI 助手的承诺。它用一个简单的对话界面取代了复杂、多步骤的工作流程。

以下是如何在极短时间内完成完全相同任务的方法。

步骤 1：上传你的 Excel 文件

首先，只需将包含 Test1 和 Test2 表格的 Excel 工作簿直接拖放到匡优Excel中。AI 将自动解析文件并识别其中的不同表格。

步骤 2：用简单的语言描述你的目标

这就是神奇之处。你无需执行手动步骤，只需与 AI 对话即可。你可以将请求分解为几个简单的提示。

以下是你与匡优Excel 对话以解决问题的示例：

步骤 3：审查与优化

匡优Excel 将立即显示结果表格。你可以看到所有转换都已正确应用。最棒的是灵活性。如果你有后续请求，无需重新开始。只需继续对话即可。

例如，你可以问：

• “现在，按‘总分’从高到低对这个表格进行排序。”
• “你能创建一个饼图，显示通过和未通过学生的百分比吗？”
• “筛选此列表，仅显示 2023 年加入的学生。”

每个命令都会立即执行，从而实现流畅、探索性的数据分析过程，这是使用 Power Query 等僵化工具无法实现的。

步骤 4：下载你的结果

一旦满意，你可以一键下载最终、格式完美的 Excel 文件。你也可以复制生成的公式或表格，用于现有的工作簿。

传统方法与匡优Excel：快速比较

常见问题

1. 使用匡优Excel 做这个需要是 Excel 专家吗？ 完全不需要。匡优Excel 的全部意义在于赋能那些不了解高级函数或 Power Query 的用户。如果你能用语言描述你的业务逻辑，你就可以使用这个工具。

2. 我将学生数据上传到匡优Excel 时安全吗？ 是的。匡优Excel 在设计时考虑了数据安全，采用行业标准的加密和隐私协议。你的数据仅用于分析处理，不会长期存储或共享。详细信息请始终参考官方隐私政策。

3. 如果我的列名不一致怎么办，比如一个表格是 'First_Name'，另一个是 'FirstName'？ AI 通常足够智能，能够识别这些细微的差异。但是，为了获得最佳效果，你可以在提示中明确说明，例如：“通过匹配 Test1 中的 'First_Name' 和 Test2 中的 'FirstName' 来合并表格。”

4. 匡优Excel 能处理非常混乱的数据吗，比如不一致的日期格式？ 是的，匡优Excel 的 AI 接受了大量数据清理任务的训练。它通常能够自动识别和转换各种非标准的日期和数字格式，而这在传统 Excel 中通常需要特定的公式或步骤。

5. 我能从匡优Excel 获取 Power Query M 代码吗？ 不能，匡优Excel 是一个独立的平台，直接执行分析。它不会为其他工具生成代码。它提供最终输出——干净的数据表、数据透视表、图表或公式——你可以立即下载和使用。

采取行动：立即升级你的 Excel 工作流程

不要再花费宝贵的时间与复杂的界面和晦涩的公式作斗争。合并和分析学生数据的任务是传统方法造成不必要摩擦的完美例子。虽然 Power Query 是一个强大的工具，但对于许多日常业务用户来说，它过于复杂。

通过拥抱 Excel AI 助手，你可以将注意力从如何执行分析转移到想要回答什么问题上。你可以更快地完成报告，减少错误，并以你从未想过的方式灵活地探索数据。

准备好亲身体验了吗？免费试用匡优Excel 并上传你自己的多工作表 Excel 文件。使用本文中的提示作为起点，看着你繁琐的数据任务在几秒钟内完成。