Excel 数据透视表不够用时，如何处理来自多个数据源的数据？

当数据透视表感到“吃力”时，我们该怎么办：

1. 数据量巨大：Excel工作表有104万行的限制，当有几十万行数据时，透视表和公式就会变得非常卡顿。
2. 数据来源多样：数据分散在多个Excel文件、CSV文件、数据库甚至网页上，需要手动复制粘贴才能汇总，费时费力且容易出错。
3. 数据清洗复杂：原始数据不规范，有大量合并单元格、空行、错误格式等，每次分析前都要花大量时间“打扫战场”。

面对这些情况，你需要升级你的工具，从“前台”的Excel单元格和数据透视表，转向“后台”的专业数据处理引擎。在Excel生态中，这个引擎就是——Power Query。

---

Power Query：Excel 内置的“数据瑞士军刀”

Power Query (简称PQ) 是一个内置于Excel（2016及以上版本）和Power BI中的数据获取与转换工具。你可以把它想象成一个自动化的数据处理流水线。

它的核心工作流程是“ETL”：

• Extract (提取)：从各种数据源（Excel、CSV、文件夹、数据库、网页等）连接并获取数据。
• Transform (转换)：通过一系列可视化的点击操作（无需写代码），对数据进行清洗、整理、合并、拆分、计算等。你做的每一步操作都会被记录下来。
• Load (加载)：将处理干净的数据加载到Excel工作表、数据透视表，或者一个更高效的“数据模型”中。

与数据透视表的区别：

• 数据透视表：是分析和聚合已经整理好的数据。
• Power Query：是在分析之前，获取和整理原始、混乱的数据。它是数据透视表的“上游工序”。

---

如何使用Power Query解决核心痛点

痛点1：处理海量数据（超过Excel限制）

传统方式：数据超过104万行，Excel直接打不开或无法处理。
Power Query 解决方案：

1. 连接数据，但不加载到工作表：Power Query可以连接到一个几百万行甚至上千万行的CSV或数据库文件。
2. 在PQ编辑器中进行转换：你可以在Power Query编辑器中对这些海量数据进行筛选、分组、聚合，先将数据量“降维”。例如，你可能只需要按月、按产品的汇总数据，而不是每一条明细。
3. 加载到“数据模型”：将处理后的结果加载到数据模型 (Data Model)，而不是Excel工作表。数据模型是Excel内部一个基于列式存储的高效压缩引擎，可以轻松处理数百万行的数据，而且文件体积比存到工作表小得多。
4. 基于数据模型创建数据透视表：最后，你创建的数据透视表的数据源不再是工作表区域，而是这个强大的“数据模型”。操作起来飞快，完全不卡顿。

操作入口：数据 -> 获取数据 -> 从文件 / 从数据库 -> … -> 在加载时选择“仅创建连接”和“将此数据添加到数据模型”。

痛点2：整合多个数据源

传统方式：手动打开12个分公司的月度销售报表，逐个复制粘贴到一个总表中。下个月重复一次。
Power Query 解决方案：从文件夹合并文件。

1. 创建一个文件夹，把所有结构相同的Excel文件（例如，12个分公司的月度报表）都放进去。
2. 在Excel中，选择 数据 -> 获取数据 -> 从文件 -> 从文件夹。
3. 选择你创建的那个文件夹。
4. Power Query会识别出文件夹里的所有文件，点击“合并和转换”。
5. PQ会让你选择一个样板文件，并自动生成一套查询步骤，将所有文件的数据纵向追加在一起。它还会自动添加一列来标注数据来自哪个源文件。
6. 一劳永逸：下个月，你只需要把新的报表文件扔进那个文件夹，然后回到Excel中，点击 数据 -> 全部刷新，总表和基于它的数据透视表就会自动更新，包含新文件的数据！

痛点3：自动化复杂的数据清洗

传统方式：每次拿到报表，都要手动删除前几行、取消合并单元格、拆分列、替换错误值…
Power Query 解决方案：录制清洗步骤。
你在Power Query编辑器中的每一步操作，都会被右侧的“应用的步骤”窗格记录下来。

常见清洗操作（全部通过点击按钮完成）：

• 提升标题行：将数据的第一行用作列标题。
• 删除行/列：删除顶部的空行、底部的总计行，或不需要的列。
• 筛选数据：像在Excel中一样筛选掉不需要的行。
• 拆分列：按分隔符（如逗号、空格）或字符数拆分列。
• 合并列：将多列合并成一列。
• 逆透视列 (Unpivot)：将交叉表（如每个月一列）转换成规范的一维表，这是Power Query的超级必杀技之一，对数据分析至关重要。
• 添加自定义列：用公式创建新的计算列。
• 替换值：批量替换错误或不规范的数据。

当你设置好这一套清洗流程后，它就变成了一个自动化模板。下次拿到同样格式的脏数据，只需刷新查询，所有清洗步骤都会自动重新走一遍。

---

何时使用 Power Query？

当你的内心出现以下声音时，就应该毫不犹豫地使用Power Query：

• “天啊，这个表有一百多万行，Excel要卡死了！” -> 用PQ加载到数据模型。
• “又要合并这几十个分公司的表，我得加个班了。” -> 用PQ从文件夹合并。
• “这个系统导出的报表格式真烂，每次都要手动调半天。” -> 用PQ录制清洗步骤。
• “我需要把Excel里的客户表，和数据库里的订单表关联起来分析。” -> 用PQ分别连接两个源，然后使用“合并查询”（类似VLOOKUP）功能。

学习路径建议：

1. 从最简单的开始：尝试用Power Query连接一个不规范的Excel表，通过点击按钮完成删除空行、提升标题、更改数据类型这三步。
2. 进阶：学习从文件夹合并多个Excel文件。这是最有价值的技能之一。
3. 掌握核心：学习“逆透视”功能，理解它如何将交叉表“拍扁”成一维表。
4. 深入：学习“合并查询”（左连接、内连接等）和“追加查询”，实现多表关联和汇总。

总结：
数据透视表是你的“分析台”，而Power Query是你的“自动化数据处理工厂”。当你的原材料（数据）变得复杂、庞大、多样时，先用Power Query这个工厂把它们加工成高质量的半成品，再送到分析台上去，这样才能高效、准确地得出你的结论。掌握Power Query，你就从一个Excel用户，真正迈向了数据分析师的门槛。