文 / 老汪(汽车零部件财务与商业智能实战派)
大家好,我是老汪。在汽车零部件制造企业(Tier 1/Tier 2 一级/二级供应商)月末做工序成本核算与工时稽核时,成本会计最头疼的就是车间统计员发来的“多栏式排版日报”。车间为了在一页纸或者同一屏幕上能看全各个工段的生产进度,往往把压铸1线、压铸2线、CNC1线、CNC2线、总装线的完工数据横向一字排开,做成两两并排的 10 列并列大宽表。这种表给人眼看很直观,但在商业智能(BI)建模里简直是“数据灾难”——无法做透视、无法 VLOOKUP、更无法进行产线料工费分配!今天老汪就带大家用 Table.ToColumns 结合 List.Zip 拉链算法,教你如何用 M 语言全自动将横向多栏并排报表瞬间折叠堆叠为标准的明细事实表。
一、 汽车零部件制造现场:车间“多栏并排报工”的数据灾难
在汽车零部件制造工厂,各工位每天都会产出完工与报工记录。车间统计员为了方便车间主任在工控机大屏上一览无余,制作的原始日报表通常长成这样:
| 零件图号_压铸01线 | 合格数_压铸01线 | 零件图号_压铸02线 | 合格数_压铸02线 | 零件图号_CNC01线 | 合格数_CNC01线 | 零件图号_CNC02线 | 合格数_CNC02线 | 零件图号_总装01线 | 合格数_总装01线 |
|---|---|---|---|---|---|---|---|---|---|
| PN-8801-CAS | 450 | PN-8805-TRY | 320 | PN-8803-STE | 280 | PN-8806-ROT | 600 | PN-8809-ARM | 350 |
| PN-8802-SUB | 380 | PN-8807-GEA | 540 | PN-8804-CAL | 310 | PN-8808-CON | 850 | PN-8810-VLV | 420 |
| PN-8803-STE | 290 | PN-8801-CAS | 410 | PN-8806-ROT | 580 | PN-8802-SUB | 360 | PN-8805-TRY | 310 |
| PN-8804-CAL | 330 | PN-8809-ARM | 360 | PN-8807-GEA | 520 | PN-8810-VLV | 390 | PN-8801-CAS | 430 |
| PN-8805-TRY | 300 | (空) | (空) | PN-8802-SUB | 350 | PN-8804-CAL | 320 | (空) | (空) |
🚨 现场业务痛点分析:
- 数据无法汇总:如果想知道“新能源电驱总成机壳(PN-8801-CAS)”全天在各个工位累计完工了多少件,用常规公式需要跨列相加,公式极其容易漏选;
- 人工复制极易错行:很多成本会计以前只能用“纯体力活”,把第 3、4 列剪切粘贴到第 1、2 列下方,再把第 5、6 列粘贴到更下方……不仅费时费力,遇到行数参差不齐(有些工位只报了 4 笔,有些报了 5 笔),手动对齐时极易贴错行;
- 数据格式不符合范式:标准的数据仓库维度建模要求“一行一明细”,所有零件图号应该在同一列,完工数量在另一列。
二、 老汪白话底层算法拆解:奇偶拉链配对法
面对这种横向重复排布的 N 组两列表格,核心思路是:将横向的列按每两列切成一组,再将所有切片子表纵向堆叠合并。
老汪为大家白话拆解这套经典的 M 语言“列切片拉链算法”:
┌────────────────────────────────────────────────────────────────────────┐
│ ① Table.ToColumns(原始表) │
│ └─ 整张表按列打散,变成 10 个列列表组成的集合 (List of Lists) │
├────────────────────────────────────────────────────────────────────────┤
│ ② List.Positions(列集合) │
│ └─ 生成列下标索引序列:{0, 1, 2, 3, 4, 5, 6, 7, 8, 9} │
├────────────────────────────────────────────────────────────────────────┤
│ ③ 奇偶下标分离提取 │
│ ├─ 偶数下标(左列·图号):List.Select(..., Number.IsEven) ➔ {0,2,4,6,8}│
│ └─ 奇数下标(右列·数量):List.Select(..., Number.IsOdd) ➔ {1,3,5,7,9}│
├────────────────────────────────────────────────────────────────────────┤
│ ④ List.Zip 拉链配对 │
│ └─ 压缩为坐标对:{{0, 1}, {2, 3}, {4, 5}, {6, 7}, {8, 9}} │
├────────────────────────────────────────────────────────────────────────┤
│ ⑤ List.Transform + Table.FromColumns │
│ └─ 批量抓取对应列,重构成 5 张标准的 2 列表格 │
├────────────────────────────────────────────────────────────────────────┤
│ ⑥ Table.Combine + Table.SelectRows │
│ └─ 纵向追加合并并防御性剔除尾部空行,输出终极事实表 │
└────────────────────────────────────────────────────────────────────────┘
1. 为什么用 Table.ToColumns 而不是 Table.ToRows?
Table.ToRows是按行切割;- 但我们当前的业务痛点是列与列之间的并排重复。通过
Table.ToColumns,第 0 个元素就是压铸1线的图号列,第 1 个元素就是压铸1线的数量列……每列作为一个独立的向量,极大方便了后续按列重组。
2. 什么是 List.Zip“拉链配对”?
就像衣服上的拉链,左边一排齿 {0, 2, 4, 6, 8},右边一排齿 {1, 3, 5, 7, 9}。
List.Zip({偶数列表, 奇数列表}) 一拉合拢,自动咬合成配对的坐标集:
{{0, 1}, {2, 3}, {4, 5}, {6, 7}, {8, 9}}!
每一个子列表 {a, b},正好就是对应产线左列与右列在内存中的真实索引!
3. _{0} 和 _{1} 是什么意思?
在 List.Transform(配对列表, each ...) 中:
– _(下划线)代表当前的坐标对,比如第三次循环时 _ 就是 {4, 5};
– _{0} 代表取坐标对里的第 0 个元素,即 4;
– _{1} 代表取坐标对里的第 1 个元素,即 5;
– Table.FromColumns({列列表集合{4}, 列列表集合{5}}, {"零件图号", "合格完工数"}),瞬间就组装好了一张标准的产线子表!
三、 Power Query 高级编辑器完整 M 语言实操代码
💡 老汪敲黑板(免改路径直接刷新秘籍):平时做财务与排产分析最怕换台电脑一刷新就报错找不到路径,老汪在给你们配套的随堂课件【配置】表写好了一个动态路径提取公式,大家解压课件包后直接点【全部刷新】就能直接使用,绝不报红字!大家可以把这招复制到自己公司的日常模板中。
实操指南点此篇文章:《实操指南》
打开 Power Query 编辑器,新建空查询,进入【高级编辑器】,粘贴以下完整实战脚本:
// =========================================================================
// 案例:EP050 Table.ToColumns 与 List.Zip:横向多栏并排数据纵向折叠堆叠(多列转两列)
// 业务背景:汽车零部件制造企业(Tier 1)车间多工位完工报工日报横向多栏合并
// 核心函数组合:
// 1. Table.ToColumns(表) :整表转为多列内嵌列表(List of Lists)
// 2. List.Positions(列表):动态生成全部列的索引下标序列 {0..N-1}
// 3. List.Select + Number.IsEven / Number.IsOdd:奇偶下标分类提取
// 4. List.Zip({偶数下标, 奇数下标}):将左右并排配对列压缩为拉链索引元组
// 5. List.Transform + Table.FromColumns:批量切片重构成标准两列表
// 6. Table.Combine(表列表):纵向追加合并所有切片子表
// 7. Table.SelectRows:防御性剔除尾部空值行(null)
// 作者:老汪(汽车零部件财务与商业智能实战派)
// =========================================================================
let
// 1. 【Q001自适应规范】:动态获取当前工作簿所在文件夹相对路径(免疫跨电脑拷贝报错)
当前文件夹路径 = try
let
rawPath = Text.From(Excel.CurrentWorkbook(){[Name="当前路径"]}[Content]{0}[Column1]),
cleanPath = Text.Trim(rawPath),
fixedPath = if Text.EndsWith(cleanPath, "\") then cleanPath else cleanPath & "\"
in
fixedPath
otherwise
"./",
// 2. 动态拼接并加载【原始业务数据表.xlsx】
目标数据文件 = 当前文件夹路径 & "原始业务数据表.xlsx",
文件二进制 = File.Contents(目标数据文件),
打开Excel = Excel.Workbook(文件二进制, null, true),
多栏报工原始表 = 打开Excel{[Item="车间多工位完工报工日报", Kind="Sheet"]}[Data],
// 3. 提升首行作为标题
提升标题 = Table.PromoteHeaders(多栏报工原始表, [PromoteAllScalars=true]),
// 4. 【核心第一步】:表转列列表(Table.ToColumns)
// 将整张 10 列表格转化为一个包含 10 个列列表的大 List,每个元素代表一整列的数据
列列表集合 = Table.ToColumns(提升标题),
// 5. 【核心第二步】:获取所有列的下标序列
// 得到动态索引序列:{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}
列下标列表 = List.Positions(列列表集合),
// 6. 【核心第三步】:奇偶下标分离
// 左列(零件图号):均为偶数下标 -> {0, 2, 4, 6, 8}
// 右列(完工数量):均为奇数下标 -> {1, 3, 5, 7, 9}
偶数下标 = List.Select(列下标列表, each Number.IsEven(_)),
奇数下标 = List.Select(列下标列表, each Number.IsOdd(_)),
// 7. 【核心第四步】:拉链配对(List.Zip)
// 压缩配对成每两列一组的坐标列表:{{0, 1}, {2, 3}, {4, 5}, {6, 7}, {8, 9}}
配对下标列表 = List.Zip({偶数下标, 奇数下标}),
// 8. 【核心第五步】:遍历重构为标准两列表列表
// 针对每一个下标对 {leftIndex, rightIndex}:
// - 取出对应的列数据:列列表集合{leftIndex} 和 列列表集合{rightIndex}
// - 构造成标准两列表 Table.FromColumns,统一赋予规范表头 {"零件图号", "合格完工数(件)"}
子表列表 = List.Transform(配对下标列表, each
Table.FromColumns(
{列列表集合{_{0}}, 列列表集合{_{1}}},
{"零件图号", "合格完工数(件)"}
)
),
// 9. 【核心第六步】:纵向追加合并所有切片子表(Table.Combine)
合并标准表 = Table.Combine(子表列表),
// 10. 【防御性清洗】:剔除因各工位行数不一致导致的尾部空值行(null)
过滤空值行 = Table.SelectRows(合并标准表, each [零件图号] <> null and [零件图号] <> ""),
// 11. 规范最终报表字段数据类型
规范数据类型 = Table.TransformColumnTypes(过滤空值行, {
{"零件图号", type text},
{"合格完工数(件)", Int64.Type}
})
in
规范数据类型
四、 最终清洗与纵向堆叠成果展现
原本横向拉伸、各线错位的 10 列宽表,经过上述代码处理后,瞬间化身为符合一维数据库范式的标准事实表(共计 23 条干净记录):
| 行号 | 零件图号 | 合格完工数(件) | 归属来源工位(业务还原) |
|---|---|---|---|
| 1 | PN-8801-CAS | 450 | 来自 压铸01线 |
| 2 | PN-8802-SUB | 380 | 来自 压铸01线 |
| 3 | PN-8803-STE | 290 | 来自 压铸01线 |
| 4 | PN-8804-CAL | 330 | 来自 压铸01线 |
| 5 | PN-8805-TRY | 300 | 来自 压铸01线 |
| 6 | PN-8805-TRY | 320 | 来自 压铸02线 |
| 7 | PN-8807-GEA | 540 | 来自 压铸02线 |
| 8 | PN-8801-CAS | 410 | 来自 压铸02线 |
| 9 | PN-8809-ARM | 360 | 来自 压铸02线 |
| 10 | PN-8803-STE | 280 | 来自 CNC01线 |
| … | … | … | … |
| 21 | PN-8810-VLV | 420 | 来自 总装01线 |
| 22 | PN-8805-TRY | 310 | 来自 总装01线 |
| 23 | PN-8801-CAS | 430 | 来自 总装01线 |
转换成这张表之后,后续无论是用数据透视表做“按零件图号汇总全厂完工总数”,还是用 Table.Group 做加权工时计算,都变得轻而易举!
五、 避坑指南与老汪进阶神技
1. 为什么必须加 Table.SelectRows 过滤空行?
在车间现场,各个工位的生产节奏不一致。比如压铸01线当天报工了 5 个零件,而压铸02线当天只产出了 4 个零件,底表第 5 行就会留下空值。
如果不加过滤,合并后的事实表中就会混入无意义的 null 行,污染后续财务统计,必须加上 [零件图号] <> null 的防御清洗。
2. 如果每一组是 3 列(如:图号、完工数、报废数)怎么办?
很多初学者会问:“老汪,奇偶数只能分 2 列,如果车间并排的是 3 列甚至 4 列,奇偶法不就失效了吗?”
非常敏锐的思考!
– 进阶思路 A:用模运算 Number.Mod(_, 3),将余数为 0、1、2 的列分别提取;
– 老汪独家神技 B(强烈推荐):用 List.Split 2 行代码秒杀!
powerquery
// 老汪极简神技:直接把列集合按每 N 列切成一组!
切片列组 = List.Split(Table.ToColumns(提升标题), 2),
合并表 = Table.Combine(
List.Transform(切片列组, each Table.FromColumns(_, {"零件图号", "合格完工数(件)"}))
)
List.Split 是 M 语言中极少人知道的“宝藏函数”,它能直接把一个大列表按固定步长切开,无论是 2 列一组、3 列一组还是 5 列一组,一行代码全部搞定!
六、 老汪结语
在制造业财务数字化转型中,财务人员面对的大量原始报表往往并非规范的 IT 系统导出表,而是车间统计员为了肉眼好看而设计的“并排多栏表”。
如果我们只会写手工复制粘贴宏,或者一层层用 PQ “逆透视列再拆分”,往往会陷入繁琐的列名匹配泥潭。而今天我们学习的这套 Table.ToColumns + List.Zip / List.Split 切片合并法,从内存列向量的底层视角直接对表格进行重塑,逻辑清晰、运行极速,是每一位 Excel BI 高手必须掌握的看家本领!













暂无评论内容