文 / 老汪（汽车零部件财务与商业智能实战派）
专栏：【Excel BI 财务建模实战课讲义（第2阶段）】 · 期数：EP044
核心函数：Text.Start/Text.Middle/Text.End/Text.Range/Text.Length
📌 老汪开篇导读：复合条码背后的“密码锁”与财务清洗之痛
各位财务同仁、成本会计、车间统计员与商业智能分析师：
在咱们汽车零部件制造企业（Tier 1 一级供应商）的日常业务流转中，从原材料进厂、压铸机加注塑、装配入库，直到发往主机厂（如比亚迪、吉利、理想），每一个零部件与批次都会被打上一串长长的“复合工单条码”。例如：
DC01-20261108-A01-OTS、CNC02-20261109-B01-SOP。
很多初入工厂的财务BP或分析新手，看到这一长串密密麻麻的字符往往觉得眼晕，甚至直接当成一串普通编码放在那落灰。但老法师一眼看过去，这串条码里藏着极其宝贵的四大财务与运营管理维度：
1. 前导段（DC01 / CNC02）：所属生产车间与核心机台产线代号（压铸车间01线 / CNC机加02线）；
2. 中间日期段（20261108）：工单实际开工完工的标准归属日期，核算当期制造成本必不可少；
3. 流水批次段（A01 / B01）：当天班组批次流水，用于追溯具体带班班组与质检判定；
4. 尾部标识段（OTS / PPAP / SOP）：研发与生产所处的生命周期阶段（是属于模具样件试制、小批量验证，还是大批量量产？）。
💡 老汪的人间清醒：
如果这串条码无法在底层被秒级拆解为结构化的“车间代号”、“生产日期”与“研发阶段”，财务在做月末成本差异分析与按主机厂毛利对账时，就必须手工一遍遍用 Excel 的LEFT、MID、RIGHT去割裂处理，不仅容易因为中英文字符、下划线横杠混乱导致公式错位，一旦换个账期更新几万行数据，Excel 更是直接卡死！
今天老汪就带大家吃透 Power Query M 语言中的“文本截取三剑客”——Text.Start、Text.Middle 与 Text.End，彻底拆解它们的物理机制、底层索引规则与容错死穴，并为大家展示如何构建一条全自动拆解车间复合条码的工业级 M 语言清洗流水线！
🛠️ 第一部分：老法师拆解底层——文本截取核心函数物理机理
很多同学学过 Excel 的工作表函数，习惯了 LEFT(text, num)、RIGHT(text, num) 和 MID(text, start, num)。在 Power Query 的 M 语言世界里，它们分别对应着更加严谨规范的工业级函数：
Text.Start(text as nullable text, count as number) as nullable text
Text.End(text as nullable text, count as number) as nullable text
Text.Middle(text as nullable text, start as number, optional count as nullable number) as nullable text
Text.Range(text as nullable text, offset as number, optional count as nullable number) as nullable text
Text.Length(text as nullable text) as nullable number
1. Text.Start 与 Text.End：左右两端的纯净提取
Text.Start(文本, 截取长度)：从左往右（从头部开始）截取指定数量的字符。- 例如：
Text.Start("DC01-20261108-A01-OTS", 4)➔ 返回"DC01"； Text.End(文本, 截取长度)：从右往左（从尾部开始）倒数截取指定数量的字符。- 例如：
Text.End("DC01-20261108-A01-OTS", 3)➔ 返回"OTS"； - 容错机制：如果请求截取的长度大于文本本身的实际长度，函数不会报错，而是安全返回整段完整文本；若输入为
null，安全返回null。
2. 🚨 Text.Middle：中间截取的灵魂——0基下标（Zero-based Indexing）
这是让 90% 从传统 Excel 转过来的同学反复栽跟头的“重灾区”：
– Excel 的 MID 函数：第一个字符的起始位置是 1；
– Power Query M 语言的 Text.Middle：起始位置是 从 0 开始计数的下标（Offset/Index）！
字符位置对照：
字 符： D C 0 1 - 2 0 2 6 1 1 0 8 - A 0 1
M语言下标: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Excel位置: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
如果我们想从上面的字符串中截取 8 位的生产日期 "20261108"：
– 起始位置在第 6 个字符，即下标为 5！
– 截取长度为 8；
– 标准写法：Text.Middle("DC01-20261108-A01-OTS", 5, 8) ➔ 稳定输出 "20261108"！
3. Text.Middle 与 Text.Range 的孪生对决：宽容 vs 严苛
很多资深工程师在翻看 M 语言官方文档时会发现还有一个叫 Text.Range 的函数，它的入参与功能看起来和 Text.Middle 如出一辙，二者究竟有何本质区别？
– Text.Middle（宽容容错派 · 强烈推荐）：
如果第三参数给定的长度超出了文本尾部的实际剩余字符数（比如后面只剩 3 个字符，你写了要取 10 个），Text.Middle 绝不报错，而是把后面仅剩的字符全量取完即安全结束！
– Text.Range（严苛校验派）：
如果截取的 offset + count 超出了字符串总边界，Text.Range 会毫不留情地抛出 Expression.Error（下标越界错误）！
– 💡 老汪推荐：在面对企业真实数据源格式经常微小变异的场景下，优先选用 Text.Middle，保障你的 ETL 自动化数据刷新流水线固若金汤！
4. Text.Length：字符计数基石
测量字符串的总字符长度，常用于校验条码规范性、或配合动态计算尾部截取长度：
– Text.Length("DC01-20261108-A01-OTS") ➔ 返回数值 21。
– 中英文混合说明：在 M 语言中，一个中文字符与一个英文字符、一个数字或符号，长度均计量为 1！
🏭 第二部分：汽车零部件制造企业工单条码自动化解析实战
1. 真实业务场景画像
在某大型底盘系统部件制造基地（包含精密压铸、CNC机加、冲压焊接、总成装配四大车间），MES 生产制造执行系统每天清晨会导出全厂各产线的工单流水台账。
导出的原始台账中有一列核心字段：【复合工单条码】。车间编码规则如下：
– 车间机台代号（如 DC01、CNC02、W03、ASY01，长度 3~5 位不定，后跟横杠 -）；
– 8位工单日期（如 20261108，标准 YYYYMMDD，后跟横杠 -）；
– 班组流水编号（如 A01、B02，后跟横杠 -）；
– 项目阶段代码（如 OTS、PPAP、SOP，位于末尾）。
财务总监与生产 BP 核心痛点：
条码中包含各种不同车间与长短不齐的代码，无法直接用于按车间汇总产能、按生产日期核算工单完工工时差异、以及按研发试制（OTS/PPAP）与量产（SOP）进行成本精准分摊。
2. 核心架构设计思路
我们要利用 Power Query 构建一套动态弹性解析流水线：
1. 测定字符总长（Text.Length）：建立基础校验列，快速发现打印缺损的异常短条码；
2. 动态定位与弹性前缀截取（Text.PositionOf + Text.Start）：车间代号长短不一（W03 3位，CNC02 5位），动态找到首个横杠位置，从头部截取；
3. 安全提取中间日期并标准格式化（Text.Middle）：从首横杠后一位起取 8 位字符，并用拼接重构为标准 YYYY-MM-DD 财务对账日期；
4. 末尾阶段标识弹性提取（Text.End）：根据最后一个横杠位置计算尾部实际长度，精准提取 OTS/PPAP/SOP；
5. 重排结构化标准看板：清洗为立即可用于 Power BI 矩阵建模的清爽事实表！
💻 第三部分：随堂课件完整 M 脚本工程实现
💡 老汪敲黑板（免改路径直接刷新秘籍）：
平时做财务分析最怕换台电脑一刷新就报错“找不到文件路径”。老汪在给各位学员配套的实操课件【配置】表 A1 单元格中预置了自适应公式，大家把本课案例包解压后直接点击 Excel 的【全部刷新】即可秒级重现，绝不报红字！大家可以把这套自适应架构无缝迁移到公司的日常模型中！ 实操指南点此篇文章：《实操指南》
// =========================================================================
// 专栏：【Excel BI 财务建模实战课讲义（第2阶段）】· EP044
// 模块：Text.Start、Text.Middle 与 Text.End 文本位置截取三剑客
// 讲师：老汪（汽车零部件财务与商业智能实战派）
// =========================================================================
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」中 MES 导出的工单台账
// ---------------------------------------------------------------------
数据文件路径 = 当前目录 & "原始业务数据表.xlsx",
源文件 = Excel.Workbook(File.Contents(数据文件路径), null, true),
原始明细表 = 源文件{[Item="MES系统导出_工单条码与成本台账", Kind="Sheet"]}[Data],
提升的标题 = Table.PromoteHeaders(原始明细表, [PromoteAllScalars=true]),
更改的数据类型 = Table.TransformColumnTypes(提升的标题, {
{"复合工单条码", type text},
{"所属产线", type text},
{"核心零件名称", type text},
{"主机厂客户", type text},
{"完工入库量(件)", Int64.Type},
{"单件标准工时(秒)", Int64.Type},
{"实际总工时(小时)", type number},
{"单件合同价(元)", type number},
{"工单完工金额(元)", type number},
{"质检判定", type text}
}),
// ---------------------------------------------------------------------
// 步骤 3：核心技法 1 ——【Text.Length】字符长度测量（条码规范性校验）
// ---------------------------------------------------------------------
添加条码总长 = Table.AddColumn(更改的数据类型, "条码总字符数", each Text.Length([复合工单条码]), Int64.Type),
// ---------------------------------------------------------------------
// 步骤 4：核心技法 2 ——【Text.Start】从左侧截取固定长度的车间代号
// 业务说明：复合条码最前导固定为车间与线体代号，如 DC01-..., CNC02-...
// 结合 Text.PositionOf 动态定位首个分隔符横杠"-"的下标，实现精准弹性截取
// ---------------------------------------------------------------------
添加车间代号 = Table.AddColumn(添加条码总长, "车间代号", each
let
横杠位置 = Text.PositionOf([复合工单条码], "-"),
车间代码 = Text.Start([复合工单条码], 横杠位置)
in
车间代码,
type text
),
// ---------------------------------------------------------------------
// 步骤 5：核心技法 3 ——【Text.Middle】从指定起始下标截取工单生产日期
// 底层机制：M 语言下标严格从 0 开始计数！
// 日期紧跟在第一个横杠后，起始下标为 横杠位置 + 1，固定截取 8 位 YYYYMMDD
// 容错特性：Text.Middle 即使截取长度超出字符总长也不会崩溃报红！
// ---------------------------------------------------------------------
添加生产日期 = Table.AddColumn(添加车间代号, "工单生产日期", each
let
首横杠 = Text.PositionOf([复合工单条码], "-"),
原生日期串 = Text.Middle([复合工单条码], 首横杠 + 1, 8),
// 格式化为标准日期文本 YYYY-MM-DD
格式化日期 = Text.Start(原生日期串, 4) & "-" & Text.Middle(原生日期串, 4, 2) & "-" & Text.End(原生日期串, 2)
in
格式化日期,
type text
),
// ---------------------------------------------------------------------
// 步骤 6：核心技法 4 ——【Text.End】从右侧截取项目研发/量产阶段标识
// 业务说明：条码末尾为批次状态（OTS样件 / PPAP小批 / SOP量产）
// ---------------------------------------------------------------------
添加项目阶段 = Table.AddColumn(添加生产日期, "项目阶段", each
let
末尾横杠 = Text.PositionOf([复合工单条码], "-", Occurrence.Last),
总长 = Text.Length([复合工单条码]),
截取长度 = 总长 - 末尾横杠 - 1,
阶段标识 = Text.End([复合工单条码], 截取长度)
in
阶段标识,
type text
),
// ---------------------------------------------------------------------
// 步骤 7：重新排序列，输出干净利索的结构化业务台账
// ---------------------------------------------------------------------
重排的列 = Table.SelectColumns(添加项目阶段, {
"复合工单条码", "条码总字符数", "车间代号", "工单生产日期",
"项目阶段", "所属产线", "核心零件名称", "主机厂客户",
"完工入库量(件)", "工单完工金额(元)"
})
in
重排的列
📊 第四部分：清洗前后数据效果对照表
通过 Text.Start、Text.Middle 与 Text.End 的组合拳清洗，原本粘连在一起的非结构化长串条码，被瞬间抽离为颗粒度精细的财务分析维度表：
🚦 第五部分：老汪实战避坑指南（三大血泪经验）
1. 切勿直接把字符串当成列表用索引 {0} 去取字符！
在 Python 或某些高级语言中，你可以直接写 条码[0] 来取第一个字母。但是在 Power Query M 语言中：
– 字符串（Text）是标量基元类型，而不是列表（List）！
– 如果你对文本直接写 [复合工单条码]{0}，M 引擎会当场抛出 Expression.Error: 无法对此类型应用字段访问或索引！
– 正确姿势：必须规规矩矩调用 Text.Start(text, 1) 或者用 Text.Middle(text, 0, 1)！
2. 空值 null 与空字符串 "" 的安全防护
车间手工补录的某些异常报表中，条码列可能会出现空单元格（在 PQ 中表现为 null）：
– Text.Length(null) 会安全返回 null，不会中断；
– 但如果你在嵌套计算时进行了数值加减或与其他文本用 & 强制拼接，null & "文本" 会导致整行直接变成 null！
– 💡 老汪防御代码：
powerquery
安全条码 = [复合工单条码] ?? "" // 空值合并运算符，null 则转为空文本
3. 固定截取 vs 动态分隔符截取的选择原则
- 优先固定截取场景：字符结构具有严格国家或行业标准，如 18 位公民身份证号（前 6 位行政区划、中间 8 位出生日期、倒数第 2 位性别），此时用
Text.Start/Text.Middle/Text.End速度最快、性能最高； - 动态长度场景：像本文案例中的车间代号（
DC014位、W033位、ASY015位），切忌写死固定数值！必须配合Text.PositionOf定位分隔符横杠-，实现弹性自适应截取！
暂无评论内容