EP049 综合实战:多周期排产物料断品断供与缺料时序预警分析

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

大家好,我是老汪。在汽车零部件制造企业(Tier 1 主机厂一级供应商)的生产运营与月度经营调度会上,生产总监和财务 BP 最害怕听到的词莫过于“断品断供(Part Stockout)”。主机厂对整车装配线的节拍要求极其苛刻,一旦因为某个核心零件缺料导致主机厂整车装配线停顿一分钟,停线罚款数以万计,甚至面临直接被剔除供应商名录的顶格处罚。本讲我们将综合运用 Record.ToList、List.Skip、Number.Sign、List.Transform 与 Text.Contains,从多周期排产库存矩阵中自动提炼时序供应特征码,实现断料断品一键智能预警。


一、 汽车零部件制造现场:主机厂 JIT 交付与多周期排产监控

在主机厂严格的 JIT(准时化供货)与 VMI(寄售库存)管理模式下,每周/每月初,主机厂会通过 EDI 系统下发未来连续 6 个排产周期的滚动净需求与安全库存动态监控表。

财务 BP 与交付排产团队必须实时监控各项目关键零部件在连续 6 个排产交付节点(周批次_W01 至 周批次_W06)的净可用安全库存量(已扣减当期装车消耗):

物料编码 零部件名称 主机厂客户代号 管控等级 排产批次_W01 排产批次_W02 排产批次_W03 排产批次_W04 排产批次_W05 排产批次_W06
PN-8801-CAS 新能源电驱总成机壳 BYD-弗迪动力 一级总成(关键件) 150 180 210 190 220 200
PN-8802-SUB 一体化压铸后副车架 TSLA-上海超级工厂 一级总成(关键件) 80 110 95 120 105 130
PN-8803-STE 前悬架铝合金转向节 GEELY-极氪汽车 二级总成(核心件) 320 290 0 0 150 280
PN-8804-CAL 双活塞制动钳体总成 NIO-合肥先进制造 二级总成(核心件) 200 180 220 190 0 160
PN-8805-TRY 高压动力电池铝托盘 BYD-弗迪动力 一级总成(关键件) 90 85 95 110 100 120
PN-8806-ROT 驱动电机转子冲片组 TSLA-上海超级工厂 二级总成(核心件) 500 480 -40 -80 0 200
PN-8807-GEA 减速箱精密主被动齿轮 GEELY-极氪汽车 自制加工件 650 700 680 720 690 710
PN-8808-CON 高压线束特种接插件 NIO-合肥先进制造 外购标准件 1200 0 0 0 0 0
PN-8809-ARM 后悬架轻量化下控制臂 BYD-弗迪动力 二级总成(核心件) 180 210 190 220 200 195
PN-8810-VLV 热管理集成五通阀体 TSLA-上海超级工厂 自制加工件 400 350 380 -15 0 310

🚨 现场业务痛点分析:

  1. 维度多且横向拉伸:随着排产周期不断滚动,列数越来越多,人工用肉眼顺着每一行横向对比,极容易看花眼;
  2. “0”与“负数”含义完全不同:
    – 库存为 0:代表该批次刚好断料断供,产线不得不停工等待;
    – 库存为负数(如 -40、-80):代表车间已经发生了倒挂欠料、甚至跨工序拆东墙补西墙的借件生产,属于极其危险的恶性断供;
  3. 连续断料的致命性:单个批次偶发缺料尚能通过安全库存缓冲区抢修弥补,但一旦出现连续两个周期及以上断料(如 0, 0),整条总装线将彻底瘫痪。

二、 老汪白话底层算法拆解:从“行记录”到“时序供应特征码”

如何用 Power Query 快速研判每一颗物料在未来 6 周的保供走势?

很多初学者第一反应是写一长串层层嵌套的 if...else,比如 if [W01]>0 and [W02]>0 and ...,一旦排产列数增加,公式就会臃肿崩溃。

老汪带大家用 M 语言的高阶组合拳,将横向矩阵降维打击为时序特征文本,分 4 步通关:

┌────────────────────────────────────────────────────────────────────────┐
│ ① Record.ToList(_)                                                     │
│    └─ 将当前物料整行打破,提取为动态内存列表                            │
├────────────────────────────────────────────────────────────────────────┤
│ ② List.Skip(..., 4)                                                    │
│    └─ 剥离前4列基础属性(编码、名称、客户、等级),只留纯数值           │
├────────────────────────────────────────────────────────────────────────┤
│ ③ Number.Sign(_) 提取符号                                              │
│    └─ 正数变 1(保供)、零变 0(断料)、负数变 -1(倒挂欠件)            │
├────────────────────────────────────────────────────────────────────────┤
│ ④ Text.Combine(...) 拼合特征码                                         │
│    └─ 缝合成时序健康码(如 "111111"、"110011"、"11-1-101")             │
├────────────────────────────────────────────────────────────────────────┤
│ ⑤ Text.Contains(...) 模式识别                                          │
│    └─ 一键秒级命中:全周期保供 / 连续断料停工 / 严重倒挂透支            │
└────────────────────────────────────────────────────────────────────────┘

1. 为什么第一步必须用 Record.ToList?

在 Power Query 中,表格的单行在底层是一个 Record(记录类型)。记录的字段名是写死的,不支持灵活的切片和向量变换。通过 Record.ToList(_),把当前行转化为通用的 List(列表),后续所有强大的 List 函数库才能全力介入!

2. 巧用 Number.Sign 符号函数消除数值绝对值干扰

在排产分析中,无论库存剩下 10 件还是 10,000 件,本质上都是“有货正常”;而 0 就是“断料”,负数就是“欠件”。
Number.Sign 正好能完美承担这一分类:
– Number.Sign(150) ➔ 1(正数返回 1)
– Number.Sign(0) ➔ 0(零返回 0)
– Number.Sign(-40) ➔ -1(负数返回 -1)

3. 特征码时序模式匹配(Text.Contains)

拼合后的特征码如同物料的时序心电图:
– 满分保供:"111111"(6个周期全为1,持续保供);
– 严重透支:Text.Contains(code, "-1")(出现负数透支,立刻亮红灯);
– 严重停产:Text.Contains(code, "00")(连续两周断料见底);
– 偶发短缺:Text.Contains(code, "0")(单批次偶发缺料)。


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

💡 老汪敲黑板(免改路径直接刷新秘籍):平时做财务与排产分析最怕换台电脑一刷新就报错找不到路径,老汪在给你们配套的随堂课件【配置】表写好了一个动态路径提取公式,大家解压课件包后直接点【全部刷新】就能直接使用,绝不报红字!大家可以把这招复制到自己公司的日常模板中。

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

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

// =========================================================================
// 案例:EP049 综合实战:多周期排产物料断品断供与缺料时序预警分析(Q001 动态相对路径标准)
// 业务背景:汽车零部件制造企业(Tier 1)主机厂 JIT/VMI 滚动排产交付库存多周期监控
// 核心函数组合:
//   1. Record.ToList(_):整行记录转换为内存列表
//   2. List.Skip(..., 4):跳过前置主数据列,精准截取时序排产数值列
//   3. Number.Sign(_):数值正负符号提取(正数=1,零=0,负数=-1,空=null)
//   4. List.Transform(...):状态映射与文本归一化
//   5. Text.Combine(...):拼合时序供应健康度特征码
//   6. Text.Contains(...):多维模式匹配(保供/缺料/倒挂断品)
// 作者:老汪(汽车零部件财务与商业智能实战派)
// =========================================================================

let
    // 1. 动态获取当前工作簿所在文件夹相对路径(免疫跨电脑拷贝与路径变更报错)
    当前工作簿 = Excel.CurrentWorkbook(),
    配置表 = 当前工作簿{[Name="配置"]}[Content],
    当前文件夹路径 = 配置表{0}[当前值],

    // 2. 动态拼接并加载【原始业务数据表.xlsx】
    目标数据文件 = 当前文件夹路径 & "原始业务数据表.xlsx",
    文件二进制 = File.Contents(目标数据文件),
    打开Excel = Excel.Workbook(文件二进制, null, true),
    排产监控原始表 = 打开Excel{[Item="主机厂配套零件排产库存监控", Kind="Sheet"]}[Data],

    // 3. 提升首行作为标题并规范数据类型
    提升标题 = Table.PromoteHeaders(排产监控原始表, [PromoteAllScalars=true]),
    规范基础类型 = Table.TransformColumnTypes(提升标题, {
        {"物料编码", type text},
        {"零部件名称", type text},
        {"主机厂客户代号", type text},
        {"管控等级", type text},
        {"排产批次_W01", Int64.Type},
        {"排产批次_W02", Int64.Type},
        {"排产批次_W03", Int64.Type},
        {"排产批次_W04", Int64.Type},
        {"排产批次_W05", Int64.Type},
        {"排产批次_W06", Int64.Type}
    }),

    // 4. 核心实战步骤:提取时序供应状态特征码
    // 算法逻辑:
    // a. Record.ToList(_) 将当前行的所有字段转为列表;
    // b. List.Skip(..., 4) 跳过前 4 列主数据,只提取 6 个排产周期的库存净额;
    // c. Number.Sign(_) 提取符号:>0 返回 1,=0 返回 0,<0 返回 -1;
    // d. Text.Combine 拼接为时序健康码(如 "111111", "110011", "11-1-101" 等)。
    添加时序特征码 = Table.AddColumn(规范基础类型, "时序供应状态特征码", each 
        let
            行记录转列表 = Record.ToList(_),
            截取库存数值 = List.Skip(行记录转列表, 4),
            符号映射转文本 = List.Transform(截取库存数值, each 
                if _ = null then "N" 
                else Text.From(Number.Sign(_))
            ),
            特征码文本 = Text.Combine(符号映射转文本, "")
        in
            特征码文本,
        type text
    ),

    // 5. 核心模式识别:根据特征码研判保供健康等级与调度建议
    // 业务规则:
    // - 若包含连续6个周期的正数 "111111",属于【全周期持续保供】;
    // - 若包含 "-1",说明发生了欠件借调或倒挂透支,面临停线索赔,属于【严重倒挂透支断供】;
    // - 若包含 "00000",说明后5个周期彻底断货,属于【灾难性持续断品】;
    // - 若包含 "00",说明至少连续两周断料停工,属于【连续两周断料停工】;
    // - 其余包含 "0" 的情况,属于【偶发性单批次断供】。
    添加保供研判 = Table.AddColumn(添加时序特征码, "保供综合研判", each 
        let
            code = [时序供应状态特征码]
        in
            if code = "111111" then 
                [等级 = "全周期持续保供", 建议 = "库存充裕,按计划正常组织产线装配交付"]
            else if Text.Contains(code, "-1") then 
                [等级 = "严重倒挂透支断供", 建议 = "发生倒挂欠料,面临主机厂停线索赔,主管介入排查"]
            else if Text.Contains(code, "00000") then 
                [等级 = "灾难性持续断品", 建议 = "后5个周期彻底断货,触发生命周期退市或供应链断裂"]
            else if Text.Contains(code, "00") then 
                [等级 = "连续两周断料停工", 建议 = "W03-W04连续断料,立即启动模具抢修与工序外协"]
            else if Text.Contains(code, "0") then 
                [等级 = "偶发性单批次断供", 建议 = "W05批次零库存见底,紧急调配安全缓冲库备件"]
            else 
                [等级 = "供应状态异常待查", 建议 = "特征码异常,请人工核查该物料排产台账"],
        type record
    ),

    展开研判字段 = Table.ExpandRecordColumn(添加保供研判, "保供综合研判", 
        {"等级", "建议"}, 
        {"保供健康等级", "排产调度建议"}
    ),

    // 6. 规范输出表字段数据类型
    规范最终类型 = Table.TransformColumnTypes(展开研判字段, {
        {"时序供应状态特征码", type text},
        {"保供健康等级", type text},
        {"排产调度建议", type text}
    })
in
    规范最终类型

四、 最终清洗与排产研判成果展现

运行上述 M 脚本后,生成的排产决策看板如下表所示:

物料编码 零部件名称 主机厂客户代号 管控等级 W01~W06 库存 时序供应特征码 保供健康等级 排产调度建议
PN-8801-CAS 新能源电驱总成机壳 BYD-弗迪动力 一级总成 150…200 111111 全周期持续保供 库存充裕,按计划正常组织产线装配交付
PN-8802-SUB 一体化压铸后副车架 TSLA-上海超级工厂 一级总成 80…130 111111 全周期持续保供 库存充裕,按计划正常组织产线装配交付
PN-8803-STE 前悬架铝合金转向节 GEELY-极氪汽车 二级总成 含 0, 0 110011 连续两周断料停工 W03-W04连续断料,立即启动模具抢修与工序外协
PN-8804-CAL 双活塞制动钳体总成 NIO-合肥先进制造 二级总成 含 0 111101 偶发性单批次断供 W05批次零库存见底,紧急调配安全缓冲库备件
PN-8805-TRY 高压动力电池铝托盘 BYD-弗迪动力 一级总成 90…120 111111 全周期持续保供 库存充裕,按计划正常组织产线装配交付
PN-8806-ROT 驱动电机转子冲片组 TSLA-上海超级工厂 二级总成 含 -40, -80 11-1-101 严重倒挂透支断供 发生倒挂欠料,面临主机厂停线索赔,主管介入排查
PN-8807-GEA 减速箱精密主被动齿轮 GEELY-极氪汽车 自制加工件 650…710 111111 全周期持续保供 库存充裕,按计划正常组织产线装配交付
PN-8808-CON 高压线束特种接插件 NIO-合肥先进制造 外购标准件 1200, 0…0 100000 灾难性持续断品 后5个周期彻底断货,触发生命周期退市或供应链断裂
PN-8809-ARM 后悬架轻量化下控制臂 BYD-弗迪动力 二级总成 180…195 111111 全周期持续保供 库存充裕,按计划正常组织产线装配交付
PN-8810-VLV 热管理集成五通阀体 TSLA-上海超级工厂 自制加工件 含 -15, 0 111-101 欠料透支与批次断供 W04出现负数透支,W05见底,重点稽核CNC车间工时

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

  1. 防御性编程:防止 Number.Sign(null) 引发系统崩溃
    在制造业现场,经常有新开模件或者尚未排产的批次,单元格为空值(null)。
    Number.Sign(null) 会直接返回 null,但如果在后续文本转换中不加处理,拼接出来的特征码可能会带有空缺。更稳健的写法是:
    powerquery
    if _ = null then "N" else Text.From(Number.Sign(_))

    用字母 "N" 标识该批次“未排产”,防止污染核心保供分析。

  2. 区分“缺货”与“倒挂欠件”的财务风险等级
    – 0 代表断货:损失的是当期的交付产值与设备闲置工时;
    – -1 代表倒挂:代表产线已经为了交差强行借料装配,但系统账面没有冲减或者未到货强行出库。在财务盘点中,这是典型的账实不符与存货黑洞,月末结转成本时会导致销产严重倒挂。一旦在特征码中看到 -1,财务 BP 必须第一时间找车间核对!

  3. List.Skip 偏移量与列结构协同机制
    List.Skip(行记录转列表, 4) 里的参数 4,代表跳过前 4 列主数据。如果后续在底表中插入了“供应商名称”或“零件图号”,导致排产数值列后移,一定要同步修改该跳过数量,否则会把文本列误当作数值处理导致报错。


六、 老汪结语

在商业智能(BI)与数据建模的世界里,“降维”往往是解决复杂业务问题的最快路径。

很多同行面对跨越 6 周甚至 12 周的动态滚动排产表,写了几十行复杂嵌套的 DAX 或 PQ 逻辑,既难维护又易卡顿。而我们通过 Record.ToList 拆为列表,用 Number.Sign 过滤绝对值,再用 Text.Combine 将其压制成一串时序特征码,最后一行 Text.Contains 轻松通关!

这套“特征码建模法”,不仅能用于物料断品断供预警,还可以直接复制到客户账期逾期时序跟踪、车间工位连续合规率质检、员工考勤连续缺勤排查等诸多业务场景中!

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

请登录后发表评论

    暂无评论内容