EP051 Text.Split 与 List.PositionOf:末次锚点定位与紧邻前置元素动态提取

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

大家好,我是老汪。在汽车零部件制造企业(Tier 1 一级供应商)月末召开质量成本分析与废品损失追责会时,车间主任与质量部最容易扯皮的就是“多轮返工后的报废责任到底归哪道工序”。MES 系统导出的不良品工序流转轨迹往往挤在一个单元格里,且同一批次零件可能经历初检、返修、再加工、复检多次循环。本讲我们将综合运用 Text.Split、List.PositionOf(Occurrence.Last 末次定位)与容错下标偏移技术,从复杂流转链条中一键精准揪出末次质检拦截前的紧邻责任工序。


一、 汽车零部件制造现场:多轮返工流转下的“责任工序罗生门”

在精密压铸、锻造与 CNC 机加工车间,一批零部件从毛坯投料到成品入库往往要流转 5~8 道工序。按照工厂《内部质量损失核算制度》规定:
– 当批次零件最终判定报废时,以该批次最后一次进入 质检拦截 节点紧邻的前一道加工工序作为“首要责任工序”,承担 70% 的内部工料报废损失;
– 若零件入厂首站即被 质检拦截(前置无任何车间加工工序),则判定为外购件供应商来料不良,转入采购 IQC 索赔流程,生产车间免责。

以下是从车间 MES 系统导出的原始不良品评审流转台账:

不良评审单号 零部件名称与图号 主机厂配套项目 MES工序流转完整轨迹 批次报废总成本(元)
NCR-202611-01 800V碳化硅电驱壳体(ED-12) 比亚迪仰望U8 高压压铸,去毛刺,质检拦截,补焊返修,CNC精加工,质检拦截,气密封存 42,000.00
NCR-202611-02 一体化压铸后副车架(SF-09) 特斯拉Model Y 熔炼除气,万吨压铸,T6热处理,激光切边,CNC钻攻,质检拦截 68,000.00
NCR-202611-03 前悬架铝合金转向节(ST-08) 吉利极氪001 精密模锻,质检拦截,抛丸清理,数控车削,动平衡测试,质检拦截,防锈包装 25,000.00
NCR-202611-04 高压线束镀银接插件(HT-03) 蔚来ET7 质检拦截 18,000.00
NCR-202611-05 双活塞电子驻车卡钳(BC-15) 小米SU7 重力浇铸,质检拦截,粗镗缸孔,返工珩磨,阳极氧化,质检拦截,总成试压,质检拦截 54,000.00
NCR-202611-06 减速器输入花键齿轮轴(GS-22) 理想L9 下料锻造,正火处理,数控滚齿,渗碳淬火,精密磨齿,质检拦截 36,000.00
NCR-202611-07 动力电池液冷铝合金托盘(BT-06) 比亚迪海豹 型材挤压,质检拦截,搅拌摩擦焊,焊缝打磨,涂胶密封,质检拦截 49,000.00
NCR-202611-08 进口耐高温氟橡胶油封(OS-88) 特斯拉Model 3 质检拦截,隔离退货仓 15,000.00
NCR-202611-09 空气悬架锻铝上控制臂(CA-19) 问界M9 铝合金锻造,固溶时效,五轴CNC精铣,球头压装,质检拦截,包装待发 31,000.00
NCR-202611-10 热管理集成五通阀座(TV-04) 吉利银河E8 精密注塑,质检拦截,超声波焊接,气密初检,质检拦截,激光打标,质检拦截 22,000.00

🚨 现场业务与数据清洗三大痛点:

  1. 流转节点不定长且位置不固定:有的零件在第 5 站终检报废,有的在第 7 站拦截,拦截后还可能挂着“气密封存”、“包装待发”等收尾动作,无法直接取倒数第 2 个词;
  2. 同一轨迹多次重复出现锚点:如 NCR-202611-01 和 NCR-202611-05 经历了 2~3 次 质检拦截。如果用常规默认查找,只能抓到第一次初检前的“去毛刺”或“重力浇铸”,造成严重的责任错判;
  3. 首站拦截引发负下标越界报错:如 NCR-202611-04(外购接插件)和 NCR-202611-08(进口油封),入厂第一站就是 质检拦截(位置下标为 0),若直接用 0 - 1 取列表第 -1 项,Power Query 会立刻抛出越界异常(Expression.Error)!

二、 老汪白话底层算法拆解:四步逆向锁定首要责任方

要在一条包含重复节点的文本链条中,稳准狠地抠出“最后一次目标锚点前面的那个值”,核心只有 4 个步骤:

┌────────────────────────────────────────────────────────────────────────┐
│ ① Text.Split([轨迹], ",")                                              │
│    └─ 将整串逗号分隔文本拆解为有序工序列表 List                        │
├────────────────────────────────────────────────────────────────────────┤
│ ② List.PositionOf(工序列表, "质检拦截", Occurrence.Last)               │
│    └─ 开启末次搜索模式,精准获取最后一个“质检拦截”的 0 基下标 pos      │
├────────────────────────────────────────────────────────────────────────┤
│ ③ 坐标前移偏移:{pos - 1}                                              │
│    └─ 将末次质检下标减 1,换算成紧邻上道加工工序的列表索引             │
├────────────────────────────────────────────────────────────────────────┤
│ ④ 边界防爆护盾:if pos > 0 + try ... otherwise null                    │
│    └─ 首站即为质检(pos=0)或无质检(pos=-1)时安全返回 null 不崩溃    │
└────────────────────────────────────────────────────────────────────────┘

1. List.PositionOf 第三参数(出现次序控制)全解析

很多同学平时用 List.PositionOf(list, value) 只写前两个参数,此时引擎默认只找第一次出现的位置。当列表存在重复值时,第三参数才是真正的杀手锏:

标准枚举写法(老汪强烈推荐) 等价简写数字 底层检索行为与返回结果
Occurrence.First (缺省默认) 0 从左往右找第一次出现的位置下标(整数)
Occurrence.Last (本讲核心) 1 从右往左锁定最后一次出现的位置下标(整数)
Occurrence.All 2 找出所有匹配位置,返回一个下标列表(如 {2, 5, 7})

以 NCR-202611-01 为例:
– 拆分后的列表为:{"高压压铸", "去毛刺", "质检拦截", "补焊返修", "CNC精加工", "质检拦截", "气密封存"}(下标 0 ~ 6);
– List.PositionOf(工序列表, "质检拦截", Occurrence.Last) 直接跳过下标 2,精准返回最后一个质检的下标 5;
– 紧邻前置工序即 工序列表{5 - 1} = 工序列表{4} ➔ "CNC精加工"!

2. 为什么要用 let 缓存列表变量?

很多初学者写自定义列时,会把 Text.Split([轨迹], ",") 复制粘贴写两遍:外层取元素拆一遍,内层算下标又拆一遍。用 let 工序列表 = ... in ... 把拆分结果存进内存变量,不仅代码清爽易读,处理十万行 MES 流水时还能直接节省一半的字符串切分开销!


三、 Power Query 高级编辑器完整 M 语言实操代码

💡 老汪敲黑板(免改路径直接刷新秘籍):平时做财务与质量成本核算最怕换台电脑一刷新就报错找不到数据源路径,老汪在给你们配套的随堂课件【配置】表预置了 当前路径 动态自适应公式,大家解压课件包后按指南操作即可免改路径一键刷新,绝不报红字!

实操指南点此篇文章:《实操指南》

打开 Power Query 编辑器,新建空查询,进入【高级编辑器】,粘贴以下完整实战脚本:

// =========================================================================
// 案例:EP051 Text.Split 与 List.PositionOf:末次锚点定位与紧邻前置元素动态提取
// 业务背景:汽车零部件制造企业(Tier 1)MES 不良品多轮返工工序轨迹拆解与首要责任工序定损
// 核心函数组合:
//   1. Text.Split(文本, ","):将工序流转轨迹字符串拆解为有序列表
//   2. List.PositionOf(列表, "质检拦截", Occurrence.Last):精准定位末次质检拦截下标
//   3. 列表下标偏移 {末次下标 - 1} + try ... otherwise 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="MES不良品工序流转拦截台账", Kind="Sheet"]}[Data],

    // 3. 提升首行作为标题并规范数据类型
    提升标题 = Table.PromoteHeaders(轨迹原始表, [PromoteAllScalars=true]),
    规范基础类型 = Table.TransformColumnTypes(提升标题, {
        {"不良评审单号", type text},
        {"零部件名称与图号", type text},
        {"主机厂配套项目", type text},
        {"MES工序流转完整轨迹", type text},
        {"批次报废总成本(元)", type number}
    }),

    // 4. 【核心实战步骤】:拆解流转列表 -> 定位末次【质检拦截】下标 -> 提取紧邻前置责任工序
    // 避坑要点:
    //   a. 用 let 变量缓存 工序列表,避免重复调用 Text.Split 浪费一倍算力;
    //   b. 第三参数使用标准枚举常量 Occurrence.Last(等价于数字 1),锁定最后一次质检;
    //   c. 当首站即为"质检拦截"(下标为 0)时,0 - 1 = -1 会引发列表负下标越界报错,
    //      使用 if 末次下标 > 0 配合 try ... otherwise null 实现双重防爆返回 null!
    添加责任工序解析 = Table.AddColumn(规范基础类型, "轨迹追溯结果", each 
        let
            工序列表 = List.Transform(Text.Split([MES工序流转完整轨迹], ","), Text.Trim),
            总站数 = List.Count(工序列表),
            末次下标 = List.PositionOf(工序列表, "质检拦截", Occurrence.Last),
            前置责任工序 = if 末次下标 > 0 then (try 工序列表{末次下标 - 1} otherwise null) else null
        in
            [
                流转总站数 = 总站数,
                末次质检下标 = 末次下标,
                首要责任工序 = 前置责任工序
            ],
        type record
    ),

    展开追溯结果 = Table.ExpandRecordColumn(添加责任工序解析, "轨迹追溯结果", 
        {"流转总站数", "末次质检下标", "首要责任工序"}, 
        {"流转总站数", "末次质检下标", "首要责任工序"}
    ),

    // 5. 根据首要责任工序是否为空,自动判定定损归属并核算 70% 内部质量损失承担额
    // (若首要责任工序为 null 且末次下标为 0,说明入厂首站即被 IQC 拦截,属供应商来料索赔,车间免责)
    添加定损类别 = Table.AddColumn(展开追溯结果, "定损归属类别", each 
        if [首要责任工序] <> null then "内部工序追责(承担70%)" 
        else "供应商来料索赔(车间免责)", 
        type text
    ),

    添加工序承担损失 = Table.AddColumn(添加定损类别, "工序承担损失_70%(元)", each 
        if [首要责任工序] <> null then Number.Round([#"批次报废总成本(元)"] * 0.7, 2) 
        else 0.0, 
        type number
    ),

    // 6. 整理输出标准报表列并规范字段类型
    选择输出列 = Table.SelectColumns(添加工序承担损失, {
        "不良评审单号", "零部件名称与图号", "主机厂配套项目", "流转总站数",
        "末次质检下标", "首要责任工序", "定损归属类别",
        "批次报废总成本(元)", "工序承担损失_70%(元)"
    }),

    规范最终类型 = Table.TransformColumnTypes(选择输出列, {
        {"流转总站数", Int64.Type},
        {"末次质检下标", Int64.Type},
        {"首要责任工序", type text},
        {"定损归属类别", type text},
        {"批次报废总成本(元)", type number},
        {"工序承担损失_70%(元)", type number}
    })
in
    规范最终类型

四、 最终清洗与责任工序定损成果展现

运行上述 M 脚本后,原本杂乱无章的多轮返工工序链条瞬间被精准定责定损,车间主任心服口服:

不良评审单号 零部件名称与图号 主机厂项目 总站数 末次质检下标 首要责任工序 定损归属类别 报废总成本(元) 工序承担_70%(元)
NCR-202611-01 800V碳化硅电驱壳体(ED-12) 比亚迪仰望U8 7 5 CNC精加工 内部工序追责(承担70%) 42,000.00 29,400.00
NCR-202611-02 一体化压铸后副车架(SF-09) 特斯拉Model Y 6 5 CNC钻攻 内部工序追责(承担70%) 68,000.00 47,600.00
NCR-202611-03 前悬架铝合金转向节(ST-08) 吉利极氪001 7 5 动平衡测试 内部工序追责(承担70%) 25,000.00 17,500.00
NCR-202611-04 高压线束镀银接插件(HT-03) 蔚来ET7 1 0 (null) 供应商来料索赔(车间免责) 18,000.00 0.00
NCR-202611-05 双活塞电子驻车卡钳(BC-15) 小米SU7 8 7 总成试压 内部工序追责(承担70%) 54,000.00 37,800.00
NCR-202611-06 减速器输入花键齿轮轴(GS-22) 理想L9 6 5 精密磨齿 内部工序追责(承担70%) 36,000.00 25,200.00
NCR-202611-07 动力电池液冷铝合金托盘(BT-06) 比亚迪海豹 6 5 涂胶密封 内部工序追责(承担70%) 49,000.00 34,300.00
NCR-202611-08 进口耐高温氟橡胶油封(OS-88) 特斯拉Model 3 2 0 (null) 供应商来料索赔(车间免责) 15,000.00 0.00
NCR-202611-09 空气悬架锻铝上控制臂(CA-19) 问界M9 6 4 球头压装 内部工序追责(承担70%) 31,000.00 21,700.00
NCR-202611-10 热管理集成五通阀座(TV-04) 吉利银河E8 7 6 激光打标 内部工序追责(承担70%) 22,000.00 15,400.00

五、 避坑指南:老汪从业多年的现场实战秘籍

1. 致命隐蔽陷阱:为什么不能无脑只靠 try 列表{下标 + 1} otherwise null?

在今天取前置元素 {下标 - 1} 的场景里:
– 如果目标词在第 0 位,0 - 1 = -1(列表没有 -1 项,越界报错,被 try 拦截返回 null);
– 如果目标词根本不存在,List.PositionOf 返回 -1,-1 - 1 = -2(同样越界报错返回 null)。

但请千万警惕反向延伸场景! 如果下次老板让你提取“最后一次质检拦截后面紧跟的那道处置工序”(即 {下标 + 1}):
– 一旦某行数据里根本没有出现“质检拦截”,List.PositionOf 会返回 -1;
– 此时 -1 + 1 = 0!try 列表{0} 完全不会报错,而是静默把第 0 号首道工序张冠李戴抓了出来,造成极其隐蔽的财务核算重大事故!
– 老汪黄金防呆准则:凡是基于 List.PositionOf 做加减偏移,必须先判断 if 下标 >= 0(取前置元素则直接写 if 下标 > 0),绝不给 -1 参与加减运算的机会!

2. 随手清洗元素首尾空格(Text.Trim)

车间 MES 或 OA 审批流导出的逗号分隔字符串,经常混有 "高压压铸, 去毛刺, 质检拦截" 这样带空格的写法。拆分后如果不套一层 List.Transform(..., Text.Trim),元素实际是 " 质检拦截",List.PositionOf 严格匹配时会全部返回 -1 找不到!

3. 特殊符号列名记得加 [#"字段名(元)"]

在 M 语言词法规则中,包含括号 ()、百分号 % 的列名(如 批次报废总成本(元)),在引用时必须写成 [#"批次报废总成本(元)"],否则高级编辑器会把括号误判为函数调用而报语法红字错误。


六、 老汪结语

在企业日常经营分析中,像OA 多级审批流抓最终复核人前一位经办人、物流轨迹抓末次中转站上一站发货仓、车间多轮返修抓末次终检前责任工序,本质上全都是同一个数学模型:Text.Split 列表化 ➔ List.PositionOf(..., Occurrence.Last) 锚点定位 ➔ 边界校验与坐标偏移 {pos - 1}。

掌握这套干净利落的锚点偏移组合拳,以后再复杂的流转链条追溯,对咱们财务 BP 来说都是几行代码秒级收工的小菜一碟!

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

请登录后发表评论

    暂无评论内容