EP050 Table.ToColumns 与 List.Zip:横向多栏并排数据纵向折叠堆叠(多列转两列)

文 / 老汪(汽车零部件财务与商业智能实战派)

大家好,我是老汪。在汽车零部件制造企业(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 (空) (空)

🚨 现场业务痛点分析:

  1. 数据无法汇总:如果想知道“新能源电驱总成机壳(PN-8801-CAS)”全天在各个工位累计完工了多少件,用常规公式需要跨列相加,公式极其容易漏选;
  2. 人工复制极易错行:很多成本会计以前只能用“纯体力活”,把第 3、4 列剪切粘贴到第 1、2 列下方,再把第 5、6 列粘贴到更下方……不仅费时费力,遇到行数参差不齐(有些工位只报了 4 笔,有些报了 5 笔),手动对齐时极易贴错行;
  3. 数据格式不符合范式:标准的数据仓库维度建模要求“一行一明细”,所有零件图号应该在同一列,完工数量在另一列。

二、 老汪白话底层算法拆解:奇偶拉链配对法

面对这种横向重复排布的 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 高手必须掌握的看家本领!

© 版权声明
THE END
喜欢就支持一下吧
点赞15 分享
评论 抢沙发

请登录后发表评论

    暂无评论内容