Power Query 让数据处理回归简单与智能

**Power Query(简称"PQ")**是Excel中的新技术。Excel 2016及以上版本默认内置Power Query功能。Power Query在数据的清洗、整理、整合等方面表现出色：比工作表函数更强大、比SQL更灵活、比VBA更简洁。

Power Query的真正优势：易于使⽤，易于重复使⽤。 只要把新的⽂本⽂件保存并覆盖旧的⽂件，然后在 Excel中单击“数据”“刷新”就可以完成所有⼯作。Power Query强大优势如下：

• 它可以连接到各种数据源。
• 它记录⽤户采取的每个操作，并建⽴相关步骤。
• 它永远不会改变源数据，允许⽤户尝试不同的命令，如删除或重新设置⽣成的步骤。
• 可以在将来数据改变时进⾏刷新，这种价值是巨⼤的。考虑这样⼀个解决⽅案，构建⼀个Power Query 查询来执⾏⼀些重要的数据清理，将结果放到Excel⼯作表中。然后，根据该数据表创建⼀堆图表和报告。过去，当收到⼀个更新的数据⽂件时，需要⼿动重新执⾏所有的数据清理步骤，然后将清理后的数据复制并粘贴到数据表中。 有了Power Query，所有的重复⼯作都不复存在了，只需单击“刷新”按钮，就完成了，就是这么简单。它不仅速度更快，⽽且保证操作步骤每次都能⼀致地应⽤，消除了可能导致错误的⼈为因素。Power Query为⽤户提供了各种各样的操作数据的能⼒，使数据清理更容易、更快。

Power Query功能强⼤和便利性主要体现在以下⼏个⽅⾯。⼀次构建，循环使⽤。使⽤Power Query将数据处理完毕，后续每次使⽤只需刷新数据源即可。⽅便修改，有迹可循。

识PQ战斗力

数据汇总合并

可以实现多表数据源变化后自动更新。

数据清洗

对于不规范数据，可借助PQ将其转换为结构化数据，便于后续分析。

实时外汇牌价

建立好数据链接后，需要时刷新表格即可获取当天外汇牌价。

制作文件夹目录

HYPERLINK(D2&B2,”打开文件”)

通过PQ制作动态目录，方便管理和查找文件。

第1章 Power Query基础

1.审计人员为什么要学习PQ

处理步骤留痕，满足审计可追溯要求 PQ 会完整记录每一步数据清洗、转换操作，每一步处理都有据可查，符合审计底稿可追溯的工作要求；不像手工操作，做完之后无法复原数据处理过程。

• 数据更新一键刷新，重复工作复用 当客户提供更新后的原始数据，不需要重新做一遍整理工作，直接点击刷新，全部清洗、合并、计算逻辑自动重新执行。同一类项目可以复用查询逻辑，大幅减少重复劳动，降低手工操作带来的人为录入错误。

• 突破普通 Excel 性能限制，实现多表勾稽比对 面对几十万行的大台账，普通 Excel 容易卡顿。PQ 后台处理数据，性能更强；方便做多表匹配、追加、账龄计算、拆分整理，完成往来、收入、费用类底稿的数据勾稽核对。

• 上手门槛低，兼顾灵活拓展 大部分功能可视化点击操作，不需要掌握 VBA；后台自动生成 M 语言，简单场景点点鼠标就能完成，复杂场景还可以自定义公式，比 SQL 更易上手。把人员从机械的数据整理中解放，把更多精力放在风险判断、职业判断上。

2.查询编辑器的启动

通过导入数据启动查询编辑器，就是将获取的数据导入【查询编辑器】中进行后续编辑。数据获取方式有很多，以常见的【从表格】方式为例：首先选择工作表内要导入的区域，单击【来自表格/区域】→【创建表】，确定后即自动进入Power Query查询编辑器界面，如图所示。

3.导入数据

2. 从Excel文件导入数据

Excel文件是存储数据最常用的格式之一，读取Excel文件中的数据也是较为频繁的操作。接下来演示使用Power Query读取并导入Excel文件的方法。

2. 从文本文件导入数据

3. 从网站导入数据

联赛网址

4.PQ编辑器

数据加载到PQ编辑器后，即可进行数据清理、数据汇总与分析等操作，处理完成后将结果上载至Excel表。PQ编辑器整体界面如下：

**区域1：**功能选项卡区域，主要有：主页+转换+添加列+视图

区域2：展示连接的数据源

区域3：展示选中的数据源对应的明细

**区域4：**展示选中单元格后，对应的数值

区域5：数据表的查询属性设置，一般只用来修改数据表表名

**区域6：**记录数据处理步骤。

第2章 功能区介绍

功能区包括【开始】、【转换】、【添加列】、【视图】几个选项卡。不同选项卡下，部分命令是重复的，但使用效果可能不一样。

1. 主页区

【开始】选项卡下汇集了Power Query中较常用的功能命令，通过观察命令所在的组名称，即可大致了解其作用。

案例引入：

2. 转换区

【转换】选项卡下的命令主要用于对原有数据进行转换处理，可对任意列执行数据检测、替换、填充、透视等操作。

案例引入：

3. 添加列

执行【添加列】选项卡下的命令会产生新列。其中部分命令与【转换】选项卡相同，区别正如各自的选项卡名称所示。

案例引入：

4. 视图区

【视图】选项卡主要用于Power Query查询编辑器的显示设置，如显示或隐藏【编辑栏】、【高级编辑器】、【查询设置】等。

第3章 案例演示

1.数据清洗

痛点：日常工作中获取的不规范销售数据会带来大量重复整理劳动，有时甲方或同事不会配合配合调整数据格式，每天都要重复执行相同的整理步骤。

• 三类数据问题：A列订购日期使用中文句号分隔，无法直接识别为标准日期；所属区域列存在合并单元格，下方单元格全部为空；金额列数值后附带中文"元"单位，无法直接累计计算。
• 传统手动处理方式：以往需通过替换功能将中文句号替换为短横线得到标准日期，使用定位工具处理合并单元格，再替换掉金额后的"元"单位，三步完成数据整理。

2.追加与合并

本案例综合运用追加与合并功能，以南京、苏州、无锡三地销售数据为基础，演示从数据导入、多表追加、双条件匹配合并到生成最终销售额统计报表的完整流程，实现数据更新后一键刷新的自动化效果。

原始数据情况：本次案例使用南京、苏州、无锡三个城市的销售数据，仅有销售数量，没有价格和金额字段；另配套一张价格表，标注不同地区对应各类商品的专属售价，如南京和苏州地区的彩盒售价存在差异。

核心任务：统计三个城市的总销售额，需结合地区和产品类别两个维度查询对应价格，以价格乘以销售数量算出单条数据的金额，最终汇总得到全地区总销售额。

3.拆分数据

本案例演示使用Power Query处理线下活动兼职工资台账的方法，解决原始台账无法直接透视统计的痛点，覆盖从数据抓取、拆分列到动态刷新的全流程操作，兼顾财务人员记录习惯与数据统计需求。

4.账龄分析

本案例围绕不等长值分段的通用数据处理方法展开，以财务场景中的账龄计算为实操案例，完整演示从数据导入、间隔天数计算、条件分段到透视统计的全流程操作。

5.透视与逆透视

本案例围绕透视与逆透视的核心概念差异展开，结合带合并单元格、双行表头的月度业务数据，完整演示从数据载入到最终生成合规透视表的全流程操作。

6.不同结构多表追加合并

本案例演示结构不同的多表格合并追加操作，对比南京、苏州两地表格的列结构差异。Power Query的追加操作按列标题名称进行匹配，不会出现按列顺序错位串列的情况：南京文件缺失的发票号列会自动生成，且所有行填充为空值。此外，南京文件的成本列名为"成本额"，苏州文件的成本列名为"成本"，直接追加后苏州文件的"成本额"列、南京文件的"成本"列均为空，导致成本统计结果错误，统一列标题后方可正确汇总。

7.合并文件夹中所有文件

$$=Excel.Workbook([Content],true)$$

这是需要记住的唯一公式

本案例演示以文件夹为单位的多文件自动合并方法，解决传统手动追加分公司数据的痛点，覆盖从数据筛选、公式编写到自动刷新的全流程操作，验证新增符合规则的文件可被自动抓取合并。传统手动链接分公司文件再追加的方式存在两个明显缺陷：一是后续新增其他分公司数据时需重新手动操作，无法自动纳入合并范围；二是面对几百个表格时逐一手动追加效率极低，实用性很差。