从Excel数据清洗到Power Query：自动化报表的入门进阶指南

简介面向Excel报表自动化初学者的Power Query入门手册帮助读者摆脱重复性复制粘贴系统掌握数据导入、清洗、转换与组合技能。资源共1个PDF文件大小约3.13MB内容从入门案例到高级查询覆盖获取文本文件、更改数据类型、将数据返回Excel、M函数导入、修改与删除应用步骤、添加自定义列与条件列、逆透视与分组依据、追加与合并查询、模糊查询等关键操作并附数据清洗十招可按需查阅。已有4554人学习下载适合希望提升Excel数据处理效率的办公人员、数据分析初学者及需要整合多源数据的报表使用者。手册目录结构清晰每一步配有操作说明可边看边练快速上手。1. 为什么我劝你先从 Power Query 入门而不是直接学 DAX你可能在 Excel 里被一堆清洗数据的活逼疯过把全省各分店发来的销售表合并、去掉重复值、把身份证号里混进来的空格清掉、把2024/1/1和2024-01-01统一成同一种日期。传统做法是录制宏或者手写 VLOOKUP但录宏在数据结构变一点就翻车VLOOKUP 面对多表合并更是黑匣子。Power Query 直接把这类重复劳动变成一次性的数据流构建你把清洗规则录一遍以后每次刷新数据源它自动重放。对绝大多数非 IT 出身、但天天和表格打交道的人来说Power Query 是比 DAX 更值得先学的第一个技能——它不要求你懂公式嵌套所有操作都能点出来生成的后台代码M 语言你暂时看不懂也不影响使用。这篇手册就是按我自己的使用路径写的先搞清楚它解决什么问题再跑通最小清洗案例接着啃几个关键 M 函数最后把刷新不稳的那些坑填平。2. Power Query 究竟是什么从复制粘贴式清洗到可刷新数据流2.1 它站在 Excel 和数据库之间的哪个位置很多人第一次打开数据 获取数据时会以为这只是个加强版导入外部数据。实际上 Power Query 是一个完整的 ETL 工具只是被嵌在 Excel2016 之后内置Office 365 更强和 Power BI 里。ETL 的意思是 Extract提取、Transform转换、Load加载它先把数据从各种源头拉进来在内存里做一系列清洗和整形最后把结果加载回 Excel 工作表或 Power BI 的数据模型。这个位置很关键。它不像函数那样在单元格里实时计算也不像透视表那样只能改变数据呈现方式而是发生在你看到工作表上的结果之前的中间层。也就是说你在 Power Query 编辑器里做的每一步操作都没有真正改动原始文件只是在记录一套数据手术流程。原始数据是脏的、乱的、几十个字段混在一起的它负责把它们切成你接下来分析要用的干净形状。我一般会这样跟团队解释想象你招了一个只能按固定流程做事的数据实习生。你把脏表丢给他告诉他先删掉前两行把 B 列拆成两列单价改成数字然后按月份分组求和他按步骤做完把结果交给你。Power Query 就是这个实习生唯一的区别是它能把流程重放无数次而且每次都会严格按相同的步骤执行——不会因为心情不好漏一步。2.2 一个查询的完整生命周期加载、转换、上载用 Power Query 处理数据通常会走完这么一段路选择数据源Excel 表、CSV、文件夹、数据库、网页等。在查询编辑器里预览数据通过右键菜单、功能区按钮或写 M 函数进行转换。每一步操作会被记录在右侧查询设置的应用的步骤里按顺序迭加。处理完点击关闭并上载把结果作为一张新表放进工作表或者只保留连接供透视表引用。下面这张表概括了三种出口的适用场景上载方式数据去哪什么时候选它表Excel 工作表里显示结果需要被其他公式直接引用仅创建连接不显示只存在于工作簿数据模型结果很大或者只是透视表的中间源数据模型Power Pivot进入 Power BI 或旧版 Power Pivot要做跨表关系、DAX 度量值这里最容易被忽略的是应用的步骤面板。它不仅是日志还是一个可回退的版本控制。你可以在任意步骤上右键查看编辑设置也可以直接删掉最后的几步重新设计——这是比手动改公式更安全的地方。我见过不少人直接用单元格公式清洗一旦公式错了改一处整表联动崩掉。Power Query 里你只需要回到出错那一步重新调整后面步骤会尽力跟着重算至少你有后悔药可以吃.2.3 为什么说它比 VBA 宏更适合做数据清洗提到自动化老手第一反应是 VBA 宏。VBA 确实是 Excel 里的全能原子弹但它有几个问题一是录制宏时会把鼠标点击、滚动、选择都记进去一旦目标表结构微变宏就失控二是 VBA 代码写起来没有可视化验证出错了找半天也找不到是哪行三是宏文件容易被禁用换个电脑还要调安全设置。Power Query 的优点是每一步操作都能立即看到结果预览出错时屏幕上的数据变化会提示你这步做坏了。它生成的 M 代码是函数式语言大部分时间你不需要理解它只要会看它把哪一列变成了什么样。相比 VBA它的学习曲线更平缓你从点击操作学起进阶时才需要阅读和手写 M 函数。而且相同逻辑在 Power BI 中也能复用也就是说你学会的是微软数据平台共通的底层技能而不是只盯着 Excel 宏。但要注意它的边界Power Query 不适合做实时动态计算因为结果加载到表之后就是静态快照除非你手动刷新或设置自动刷新。需要实时联动单元格、需要触发事件的地方VBA 仍然不可替代。我用 Power Query 的准则是凡是一次性建立规则、后续只是换数据源重跑的工作优先交给 PQ凡是打开文件马上要看到最新结果的场景才考虑宏或动态公式。3. 用 Power Query 跑通第一个清洗流程从脏表到可分析表的最小可复现步骤3.1 准备数据什么样的表格适合直接丢给 PQPower Query 不是万能的它对输入数据有比较好的容忍度但最好先做成标准的一维表每一列是一个字段每一行是一条记录。如果你手头是一个带合并单元格、多层表头、小计行的报表PQ 也能处理但步骤会多一些。我建议第一个练习案例就用一份典型的销售明细脏表包含以下特征第一行是标题但标题里有空格和重复名。某列数值与文本混在一起比如单价里出现15元。日期列格式不统一有2024/1/1和2024.01.01。有一些全空行或只有某几列有值的行。总共有 1000 行左右方便看出操作效果。这样的表才是大多数商务 Excel 的真实样子。你不需要专门去造数据打开任意一份历史报表都能练。不过要注意不要把数据源放在正在编辑的同一个工作簿里否则刷新时可能发生循环依赖。我一般会新建一个数据源.xlsx放原始数据再在另一个工作簿里做 Power Query 处理。3.2 分步操作拆分列、填充、替换、删除错误值假设你已经把 Excel 打开菜单栏点击数据 获取数据 自文件 从 Excel 工作簿选择你的数据源在导航器中选择那张脏表点转换数据进入查询编辑器。接下来按下面几步操作每步对应一个常见需求。第一清洗标题。选中标题行旁边的一个列右键重命名去掉多余空格。有重复的列名PQ 会自动加数字后缀你可以右键重命名成有业务含义的名字。第二拆分列。比如有一列日期商品名是合并的选中该列点击开始 拆分列 按分隔符选择自定义输入|拆成两列。拆完后记得把老的合并列删除不然它就是冗余数据。 Table.SplitColumn(最终改的列, 库存信息, Splitter.SplitTextByDelimiter(|, QuoteStyle.Csv), {日期, 商品名称})这段 M 代码是点击拆分列后自动生成的你不需要手写但要知道逻辑Table.SplitColumn接受要拆的列名、拆分函数和输出的新列名。如果你在代码里改了新列名表头会立刻更新。参数里的QuoteStyle.Csv表示遇到引号时不拆分这个默认值保留即可。第三填充空值。有些行区域列是空的但上面一行有值说明是合并单元格产生的结构。选中区域列点击转换 填充 向下。PQ 会用上一个非空值填充空格。这在处理父分类字段时非常常用。第四替换错误值。当某列是数字类型时文本15元会变成错误字样。选中该列点击列标题左边的类型图标先改成文本然后使用替换值把元替换成空字符串再把它改回整数。很多人忽略的是必须按这个顺序来如果直接在数字类型下尝试替换文本PQ 会直接报错。第五删除错误值。如果只是少数几行有问题不需要替换直接筛选掉。点击列标题的筛选箭头取消勾选错误然后右键删除行。筛选的动作同样会被记录成步骤刷新时会再次执行。3.3 保存并上载什么时候选仅创建连接什么时候选表在查询编辑器点击关闭并上载是第一个坑。默认情况下它会作为一张新表加载进工作簿。但如果你只是要用这份数据做透视表没必要在工作表里显示几百 MB 的明细。更合理的方式是先点关闭并上载至——弹出的对话框里选仅创建连接然后勾选将此数据添加到数据模型。之后你插入透视表时直接从数据模型里选这张表。反过来如果清洗后的结果需要被 VLOOKUP 或其他单元格公式引用那就必须上载为表。注意上载为表后工作表的表会和 Power Query 查询保持关联但你不应该直接改这张表的单元格因为下次刷新会把你的手改动覆盖。以下是两种方式的对比上载模式是否占用工作表空间能否被 VLOOKUP 引用能否被透视表使用适合场景表占用可以可以但会占用行数结果要交给其他公式仅创建连接不占用不行除非用 CUBE 函数可以且性能更好只是中间清洗层、最终呈现靠透视表我自己的习惯是所有中间查询一律仅创建连接只有最终要给人看的表格才上载为表。这样工作簿不会泄露你清洗过程的半成品刷新也更快。说到底Power Query 是一条水线前面每一级都应该是隐藏的只有最后一个出水口是可见的。4. 进阶必会的几个 M 函数从点击操作到可复用逻辑4.1 用 M 函数代替菜单点击让清洗逻辑可复用界面操作会生成 M 代码这是学习 M 语言的最佳入口。打开任意一个查询点击功能区视图 高级编辑器你就能看到完整脚本。M 语言的执行方式是从let到in逐行定义变量每一行给一个名字引用前一行的结果。它不像 VBA 那样按顺序改单元格而是更像函数式管道每一步返回一张新的表。举一个我用得很频繁的例子你要筛选出金额大于 1000 且区域不是空白的行。鼠标点击需要两步先筛选金额列再筛选区域列。用 M 函数可以直接写成一行:let 源 Excel.CurrentWorkbook(){[Name销售明细]}[Content], 筛选行 Table.SelectRows(源, each [金额] 1000 and [区域] null and [区域] ) in 筛选行逻辑说明第一行Excel.CurrentWorkbook读取当前工作簿内的销售明细表第二行Table.SelectRows接受两个参数——要筛选的表源以及一个条件函数each ...。each表示对每一行执行右侧条件[金额]是取当前行的金额列。注意 M 语言对空白值的判断有两个null和空字符串如果你只判断 null空字符串还是会被漏过去.参数说明Table.SelectRows的条件里多个条件用and连接而且each后面的表达式必须返回逻辑值。如果你把金额大于 1000写成[金额] 1000会得到错误——因为 M 语言区分数字和文本1000 是整数而1000是文本。这也是为什么我建议先用界面操作改类型再写条件表达式。4.2 把多个 Excel 文件合并成一个查询Folder.Files 的实际用法做月度汇总时最难受的是每周或每天都有一个新 Excel 文件表结构相同但散落在不同文件夹。用传统方法是一个个复制粘贴Power Query 有一个官方方案从文件夹获取数据。点击数据 获取数据 自文件 从文件夹选择文件夹PQ 会把所有文件列出然后点击合并和转换数据。它自动生成类似下面的逻辑let 源 Folder.Files(C:\销售数据\2024), 按名称筛选 Table.SelectRows(源, each Text.EndsWith([Name], .xlsx)), 合并文件 Table.Combine(Table.TransformColumns(按名称筛选, {{Content, each Excel.Workbook(_, true)}})[Content]) in 合并文件逻辑说明Folder.Files返回一个列出文件信息的表包含Name、Extension、Content二进制内容等列。Table.SelectRows在这里过滤掉非 Excel 文件。最关键的是Table.TransformColumns它把每一行的Content二进制转换成Excel.Workbook结构然后抽取其中的Content列最后Table.Combine把所有表纵向合并。参数说明Excel.Workbook(_, true)中的第二个参数true表示使用第一行作为标题。如果每个文件的第一行都是 商品、金额 这样的标题那true是安全的。但如果第一个文件恰好第一行是数据你就要改成false然后再手动提升标题。另一个容易踩的坑是文件扩展名大小写Text.EndsWith([Name], .xlsx)对文件名大小写敏感如果你有.XLSX文件最好改成Text.EndsWith(Text.Lower([Name]), .xlsx)。这个方法的优点是当新增文件时你只要把文件丢进文件夹然后点一下刷新新文件会自动被吸入。它还有一个隐藏参数Folder.Files是递归读取子文件夹的也就是说文件夹下再建子文件夹也能被扫到。想只读顶层文件夹用Folder.Contents替代。4.3 自定义列和条件列处理同一列里混着三种数据类型商务数据最让人头疼的就是省市区三列挤在一列里或者金额列里既有整数又有文本备注。如果你打算用 Excel 函数解决公式会很绕。Power Query 里可以用添加列 自定义列写一段 M 逻辑做判断。比如金额备注列里的值是这样的1500元、1200、需复核:800。你希望提取出真正的数字金额同时保留需复核标记。let 源 Excel.CurrentWorkbook(){[Name数据表]}[Content], 提取金额 Table.AddColumn(源, 金额, each try Number.From(Text.Select([金额备注], {0..9, .})) otherwise null) in 提取金额逻辑说明Text.Select会从字符串中只保留数字和小数点然后Number.From把它转成数值。try ... otherwise是 M 语言里的异常捕获机制如果某行的Text.Select结果为空比如备注里没有数字Number.From会报错otherwise null就把该行金额置为空。参数说明{0..9, .}是一个字符集合列表你也可以加入 ,- 等符号。但如果原始数据有负数这个写法会把负号丢掉需要额外处理。更稳妥的做法是使用Table.SplitColumn按空格拆成多列再用条件列判断。我的建议是当值格式混乱时不要试图用一个万能函数吞下所有情况先拆分列让每个字段变成同一种类型再写判断。另一个常见需求是条件列新列根据某列的值填充高/中/低。你可以在添加列 条件列里配置生成的 M 代码为:Table.AddColumn(源, 等级, each if [金额] 1000 then 高 else if [金额] 500 then 中 else 低)不要手动去写这种嵌套 if用界面操作更安全因为一个括号错位就找半天。但你要理解生成的代码中each和if的对应关系这样出错了才改得动。5. Power Query 避坑指南刷新慢、合并出错、数据源路径失效这些坑我替你踩过5.1 隐私级别警告导致刷新失败现象从 Excel 表合并另一个 Excel 表时第一次点击刷新弹出您尝试合并的数据源是不同隐私级别的组合这可能会导致数据泄露的警告然后刷新停止。原因Power Query 会为每个数据源分配隐私级别公共、组织、机密。当不同隐私级别数据源进行合并、聚合时为了安全它会拒绝执行除非你明确设置忽略级别。解决在功能区的数据 选项 隐私里勾选忽略 Power Query 的隐私级别并可能提高性能。这实际上是在告诉 PQ我自己的数据我清楚不用你管。然后重新刷新。注意如果你是在企业环境发布 Power BI 报告这个设置未必能保留需要管理员配置。个人 Excel 场景下勾选这个是最省事的方案。另外如果你用的数据源是同一个文件夹下的多个文件弹出隐私警告建议先使用从文件夹合并而不是单独导入每个文件再合并——后者的隐私边界更多警告更容易触发。5.2 日期列变成数字或乱码地区格式和步骤顺序的锅现象明明 Excel 里显示2024/1/1的日期导入 Power Query 后变成了像45292这样的数字或者文本格式的 45292。原因Power Query 会把类似2024/1/1的字符串自动识别为日期。但是当它无法确认时会当作文本而 Excel 的日期本质上是数字距 1900 年 1 月 1 日的天数如果你把日期列类型误设为整数就会变成45292。解决在查询编辑器中选中日期列点击类型图标改为日期或使用区域设置。如果原始列是文本格式 2024/1/1先确认是否为文本再改类型。关键原则是修改类型必须在清洗其他步骤之前或者在拆分列、删除行之后但不要在替换值之后立即改类型——因为替换值可能把列类型强制改为文本后面的类型步骤会覆盖你之前的工作。我遇到过最折腾的一次一个从 SAP 导出的 .csv 文件日期列前半部分是文本20240101后半部分是数字45293两者混在同一列。Power Query 无法自动统一类型。我是这样处理的先在该列上替换值将文本 20240101 替换成2024-01-01格式的字符串然后整列改为日期类型再替换所有错误值。顺序错一个都会失败这没什么玄学就是按类型转换的前因后果去排步骤。5.3 大文件刷新慢不是 PQ 慢是你的步骤设计慢现象一个 10 万行的 Excel 文件刷新要一两分钟有时候一分钟多感觉 Power Query 和朋友的秒开差距很大。原因Power Query 的每个步骤都会在内存中生成一份表副本。如果你先加载了全表然后做了很多次删除其他列之前的筛选但最终只需要 5 列那么前面每一步都会把巨大的多余列带着跑。另一个常见坑是使用 Table.AddColumn 里的each反复调用Text.Upper之类的函数每一行都重新计算成本很高。解决把删除其他列这步尽量前移只保留有用列。同理尽量在源头做筛选如果数据源是 SQL 数据库尽可能用筛选行把数据量压下来如果源是 Excel 表先删除不需要的列。刷新时打开查询编辑器 视图 查询依赖查看哪些步骤拖慢了整个链但实际优化靠经验和逐步注释。我还会使用启用数据预览的限制调整。右键查询名称 查询属性勾选启用快速加载如果可用。另外如果你不需要行级权限处理检查是否在查询中使用了Table.Buffer。Table.Buffer是会把表缓存到内存的 M 函数有时能提升后续计算速度但如果你在大表上用它反而会导致内存暴涨。只有在你之后要对同一张表执行多次分离操作时才值得用。5.4 文件路径改变后所有查询报错用参数解决而不是硬编码现象把 Excel 工作簿从项目A目录移到项目B目录后所有从外部文件夹获取的数据都报错无法找到文件。原因Power Query 生成的 M 代码默认把路径写死为绝对路径比如C:\项目A\数据\销售.xlsx。移动文件夹后这个路径不存在了。解决使用参数表管理路径。点击查询设置 管理参数新建一个参数命名为数据源路径并赋值为当前文件夹路径。然后在高级编辑器中把所有硬编码路径替换为数据源路径变量。更稳妥的方式是让用户只在第一个单元格输入路径甚至使用 VBA 把路径写进工作簿命名区域再用Excel.CurrentWorkbook读取该命名区域作为参数值。一个好习惯是把数据源文件统一放到工作簿的数据子文件夹里然后通过Excel.CurrentWorkbook获取工作簿自身路径再用Text.Combine拼接子路径。这样整个主文件夹被拷贝到别的电脑后只要相对结构不变刷新就能继续工作。不过这个方法在 Excel 中比较复杂大部分情况下用管理参数手动改一次就够了。对我个人来说遇到路径问题先检查参数别急着改每个查询否则几十个查询改起来太痛苦。5.5 合并查询时索引列错乱别忘了在合并前添加索引现象用追加查询把两个表上下拼接时第二个表的第一行莫名其妙和第一个表的最后一行合并到了一起或者分组后行顺序不对。原因Power Query 的每一步操作都不保证行的原始顺序。如果你的原始表没有唯一键PQ 在处理时可能按不同顺序并行计算导致后来引用的上一行不是你想的上一行。解决在清洗早期阶段给表添加一列索引列。点击添加列 索引列从 0 开始或从 1 开始。之后做任何合并、分组操作索引列会保持你的行身份。如果需要恢复原始顺序最后按索引列排序再删除索引列。这个操作生成的 M 代码是Table.AddIndexColumn参数只有列名和起始值。但我提醒一句索引列不是数据库主键它只能保证在你当前的查询上下文中的稳定顺序。如果之前有删除行操作索引仍会保留原值不会自动重排。所以在做任何筛选操作之前加索引列才能真正追踪到原始文件中的位置。6. 把 Power Query 用成一键刷新全家桶三个可以直接抄的进阶技巧6.1 用参数表控制今天处理哪个月做月度报表时与其每个月改一把查询不如在 Excel 里隐藏一个参数表放一个单元格写2024-06。Power Query 读取这个单元格作为参数然后所有查询都根据这个月份进行筛选。这样每个月你只需要改一个单元格再点刷新全部报表跟着走。具体做法在工作表里建一个名为参数设置的表A1 写月份B1 写2024-06。然后进入查询编辑器数据 从其他源 从 Excel 当前工作簿加载这张表并提取 B1 的值。用 M 代码实现let 参数表 Excel.CurrentWorkbook(){[Name参数设置]}[Content], 月份 参数表{0}[月份] in 月份然后在你的数据查询中筛选日期列时引用这个月份变量即可。注意{0}是 M 语言中取第一行记录的语法行索引从 0 开始。我这里直接命名了列名月份如果你的参数表列名不同要同步修改。6.2 在查询外部加错误处理try otherwise 帮你保住整个刷新流程当 Power Query 有多个查询时其中一个查询刷新失败默认会导致整个刷新流程中断。为了避免一个文件漏传导致全部报表白等我习惯给每个容易出错的查询外面包一层try otherwise。比如读取特定的文件时let 尝试读取 try Folder.Files(C:\Data\榜) otherwise null, 空表 Table.FromColumns({}, {文件路径, 日期}), 结果 if 尝试读取 null then 空表 else Table.SelectRows(尝试读取, each true) in 结果逻辑说明如果Folder.Files报错比如路径不存在try otherwise返回null然后if判断返回一张空表保证后续其他查询能继续执行。在 Power BI 里应用时你还需要在数据加载设置中勾选自定义错误处理这里更常见的是仅用于本地 Excel 场景。这种做法不能彻底掩盖错误——你应该在刷新后检查原查询的日志或备注——但它至少避免了连锁崩溃。6.3 验证刷新结果行数对比和内建的数据分析最后一步也是我用 Power Query 多年养成的习惯每次刷新完先看结果的尾巴再去看总量。具体做法是新建一个单独查询引用最终表用Table.RowCount统计行数和上个月或上个周期的行数做对比。如果今天突然少了 20%那很可能源文件里漏了一个分店。let 最终数据 上载表, 行数 Table.RowCount(最终数据), 说明 if 行数 1000 then 数据量异常偏少 else 数据量正常 in 说明你还可以配合条件格式让异常行数高亮出来。比起肉眼扫上万行数据做抽查这种自动化检查才真正发挥 Power Query可重放的优势。我自己在实际项目中会把这类检查逻辑放在每个关键查询的最后两步确保刷出来时一眼看到警示。说到这我想起最初用 Power Query 时总是急着把所有东西一把塞进同一个查询结果刷新慢、定位难。后来习惯拆成基础查询 检查查询 结果查询三层维护成本降了一大截。这个方向值不值得投入如果你日常工作里每周要花两三个小时做重复的数据整理Power Query 至少能帮你省掉一半时间而且它打磨的是你面对任何结构化数据的思维方式。希望这篇手册能让你少走几趟弯路把更多时间留给真正需要判断力的事情上。本文还有配套的精品资源点击获取