当前位置：首页>Excel>驾驭Excel隐藏的“编程语言”:Power Query M语言,让数据处理自动化

驾驭Excel隐藏的“编程语言”:Power Query M语言,让数据处理自动化

2026-04-07 23:00:48

驾驭Excel隐藏的“编程语言”：Power Query M语言，让数据处理自动化

各位【Excel数据分析BI】的家人们！今天我们不聊透视表，也不讲VLOOKUP，咱们玩点更“硬核”的——直接驾驭Excel里隐藏的数据处理“编程语言”：Power Query的M语言。

你是不是还在为这些事头疼？

• 每月合并几十张表，复制粘贴到手抽筋？
• 数据格式乱七八糟，手动调整到眼花？
• 想从身份证号里提取生日、性别，却要写一长串复杂函数？

如果你的答案是“是”，那今天这篇文章就是为你准备的“效率解药”。M语言，就是Power Query背后真正的“大脑”，它能将你的重复性数据清洗工作，变成一键刷新的自动化流水线。掌握了它，你就不再是表格的“搬运工”，而是数据的“指挥官”。

本文核心价值：用5段核心M代码，带你彻底搞懂如何像编程一样，对表格进行“增、删、改、查”，实现数据处理质的飞跃。

你将学到：

• 表的诞生：从零到一，如何把杂乱文件变成规整表格？
• 列的精修：如何批量给列“动手术”，只留有用的？
• 行的筛选：如何快速找到你关心的那几行数据？
• 列的衍生：如何用简单公式，让数据自己“说出”新信息？
• 表的融合：如何让多个表格像“社交”一样自动关联？

第一部分：表的诞生——从“文件堆”到“数据表”的魔法

场景：每月初，你要汇总十几个部门的Excel报表。手动打开、复制、粘贴，一上午就没了，还容易出错漏。

M语言解法：用Folder.Files和Excel.Workbook函数，构建一个自动化的数据收集流水线。

let    // 1. 获取文件夹下所有文件    源 = Folder.Files("C:\销售数据\2025"),    // 2. 筛选出Excel文件（非隐藏）    筛选文件 = Table.SelectRows(源, each ([Extension] = ".xlsx")),    // 3. 对每个文件，调用自定义函数读取内容    调用函数 = Table.AddColumn(筛选文件, "数据", each Excel.Workbook([Content], null, true)),    // 4. 展开读取到的数据表    展开表 = Table.ExpandTableColumn(调用函数, "数据", {"Data"}, {"Data"}),    // 5. 提升第一行为标题    最终表 = Table.PromoteHeaders(展开表{0}[Data], [PromoteAllScalars=true])in    最终表

原理点睛：这段代码构建了一个自动化流程。Folder.Files像侦察兵，把指定文件夹里所有文件信息列出来。Excel.Workbook是翻译官，把二进制的Excel文件“翻译”成Power Query能懂的表格结构。后续步骤就像流水线上的工人，把每个文件里的数据表展开、合并，最后统一格式。设置好后，下次只需把新文件扔进文件夹，点击“刷新”，所有数据自动规整完毕。

金句：真正的效率，不是手速快，而是让机器记住你的操作流程。

第二部分：列的精修——给数据表“瘦身塑形”

场景：原始数据表有几十列，但真正用到的就五六列。其他无用的“备注”、“临时编号”列不仅碍眼，还拖慢处理速度。

M语言解法：使用Table.SelectColumns和Table.RemoveColumns，像剪刀一样精准修剪列。

let    // 假设这是上一步得到的表    源表 = ...,    // 1. 方法A：精确选择需要的列（推荐）    精选列 = Table.SelectColumns(源表, {"订单号", "客户名称", "产品", "销售额", "日期"}),    // 2. 方法B：删除明确不需要的列    删除列 = Table.RemoveColumns(源表, {"备注", "临时编号", "审核人"}),    // 3. 重命名列，让名称更易懂    重命名 = Table.RenameColumns(删除列, {{"SalesAmt", "销售额"}, {"OrderDate", "下单日期"}}),    // 4. 调整列顺序，让逻辑更清晰    调顺序 = Table.ReorderColumns(重命名, {"订单号", "下单日期", "客户名称", "产品", "销售额"}),    // 5. 更改列数据类型，确保能正确计算    改类型 = Table.TransformColumnTypes(调顺序,{{"销售额", Currency.Type}, {"下单日期", Date.Type}})in    改类型

原理点睛：Table.SelectColumns是“点名入伍”，只保留你指定的列，是最干净的做法。Table.RemoveColumns则是“开除冗员”，把确定不要的列踢出去。Table.TransformColumnTypes至关重要，它确保“销售额”被识别为数字（能求和），而不是文本（只能计数）。顺序调整则让报表更符合阅读习惯。

金句：干净的数据表，是做好分析的地基。列不是越多越好，而是越精越好。

第三部分：行的筛选——快速定位“关键先生”

场景：老板让你立刻找出2025年第一季度、销售额超过10万的大客户订单。你难道要手动筛选再复制吗？

M语言解法：用Table.SelectRows配合条件表达式，实现智能筛选。

let    源表 = ...,    // 1. 多条件“且”关系筛选：2025年Q1且销售额>100000    筛选行 = Table.SelectRows(源表, each [下单日期] >= #date(2025,1,1) and [下单日期] <= #date(2025,3,31) and [销售额] > 100000),    // 2. “或”关系筛选：产品是“手机”或“电脑”    筛选产品 = Table.SelectRows(源表, each [产品] = "手机" or [产品] = "电脑"),    // 3. 模糊筛选：客户名包含“科技”二字    模糊筛选 = Table.SelectRows(源表, each Text.Contains([客户名称], "科技")),    // 4. 删除空值行（如客户名称为空）    删空值 = Table.SelectRows(源表, each not ([客户名称] = null or [客户名称] = "")),    // 5. 删除重复行，确保数据唯一性    删重复 = Table.Distinct(源表, {"订单号"}) // 根据订单号去重in    删重复

原理点睛：Table.SelectRows是M语言里的“过滤器”。each代表对每一行进行判断，后面的条件表达式就是过滤规则。你可以用and（且）、or（或）组合复杂条件。Text.Contains能进行模糊匹配，非常实用。Table.Distinct则是数据清洗的利器，一键删除完全重复的行。

金句：数据筛选不是大海捞针，而是用精确的指令，让关键信息自动“浮出水面”。

第四部分：列的衍生——让数据自己“讲故事”

场景：你有一列“身份证号”，想自动得出“出生年月”和“性别”；有一列“销售额”，想立刻知道它属于哪个“业绩等级”。

M语言解法：使用Table.AddColumn和each函数，为每一行数据计算新信息。

let    源表 = ...,    // 1. 从身份证号提取出生日期（假设为18位）    加出生日期 = Table.AddColumn(源表, "出生日期", each Date.FromText(Text.Middle([身份证号], 6, 8)), type date),    // 2. 从身份证号判断性别（第17位，奇数为男）    加性别 = Table.AddColumn(加出生日期, "性别", each if Number.Mod(Number.FromText(Text.Middle([身份证号], 16, 1)), 2) = 1 then "男" else "女"),    // 3. 根据销售额添加业绩等级    加业绩等级 = Table.AddColumn(加性别, "业绩等级", each if [销售额] >= 50000 then "A" else if [销售额] >= 20000 then "B" else "C"),    // 4. 计算订单金额（单价*数量）    加订单金额 = Table.AddColumn(源表, "订单金额", each [单价] * [数量]),    // 5. 生成数据唯一标识（行号）    加行号 = Table.AddIndexColumn(源表, "行号", 1, 1)in    加行号

原理点睛：Table.AddColumn是M语言的“创造力引擎”。它不改变原数据，而是基于原数据生成新列。Text.Middle、Date.FromText等函数用于文本和日期处理。if...then...else是条件判断，实现业务逻辑。Table.AddIndexColumn能轻松添加序号列，常用于标记或排序。

金句：原始数据是沉默的矿石，M语言就是你的炼金术，能从中提炼出更有价值的黄金信息。

第五部分：表的融合——让数据表“牵手成功”

场景：销售记录里只有“产品ID”，你想看到“产品名称”；客户信息在另一张表里。用VLOOKUP？数据量大就卡死。

M语言解法：使用Table.NestedJoin或图形化“合并查询”，建立表间关系，实现智能关联。

let    销售表 = ...,    产品表 = ...,    // 1. 合并查询（类似SQL的LEFT JOIN）    合并 = Table.NestedJoin(销售表, {"产品ID"}, 产品表, {"产品ID"}, "产品详情", JoinKind.LeftOuter),    // 2. 展开合并过来的新列（产品名称、类别）    展开 = Table.ExpandTableColumn(合并, "产品详情", {"产品名称", "类别"}, {"产品名称", "类别"}),    // 3. 同理，可以关联客户表    客户表 = ...,    合并客户 = Table.NestedJoin(展开, {"客户ID"}, 客户表, {"客户ID"}, "客户详情", JoinKind.LeftOuter),    // 4. 展开客户信息    最终表 = Table.ExpandTableColumn(合并客户, "客户详情", {"客户姓名", "区域"}, {"客户姓名", "区域"})in    最终表

原理点睛：这步操作在Power Query编辑器里点几下鼠标就能完成，背后就是这段M代码。Table.NestedJoin是“红娘”，根据共同的键（如“产品ID”）把两张表关联起来。JoinKind.LeftOuter表示保留左表（销售表）所有行，右表（产品表）没有匹配上的就显示空值。Table.ExpandTableColumn则是把关联过来的“小表”（产品详情）展开成具体的几列。从此，分析维度随心切换，告别VLOOKUP到死的噩梦。