Excel中SWITCH函数通配符失效 如何实现globbing模式匹配值转换

问题说明 #

现有A列存储编码数据，示例值如下：

60BM1
90CM2
120MM3
60CM1
120BM2

原公式使用SWITCH搭配*通配符，尝试将编码按规则转换为指定格式，预期输出结果：

F.1.T.1.1
F.2.T.2.2
F.3.T.3.3
F.1.T.2.1
F.3.T.1.2

SWITCH为精确匹配函数，原生不支持globbing通配符规则，因此带*的匹配条件无法触发，导致公式运行错误。

可落地实现方案 #

方案1：通用通配符匹配写法（全Excel版本兼容） #

使用IFS搭配支持通配符判断的COUNTIF函数实现匹配，完全复用原本设计的通配规则，不需要调整匹配逻辑，修正后公式如下：

="F." &
IFS(COUNTIF(A1,"60*"),"1",COUNTIF(A1,"90*"),"2",COUNTIF(A1,"120*"),"3") &
".T." &
IFS(COUNTIF(A1,"*BM*"),"1",COUNTIF(A1,"*CM*"),"2",COUNTIF(A1,"*MM*"),"3") &
"." &
IFS(COUNTIF(A1,"*1"),"1",COUNTIF(A1,"*2"),"2",COUNTIF(A1,"*3"),"3")

逻辑说明：COUNTIF(目标单元格, 通配规则)在内容匹配规则时返回1，不匹配返回0，可直接作为IFS的判断条件，支持*（匹配任意长度任意字符）、?（匹配单个任意字符）两类标准globbing通配符。

方案2：固定结构简化写法（适合当前编码规则） #

当前编码结构固定为「数字前缀+2位字母标识+1位数字后缀」，可以直接通过文本截取拆分字段做匹配，不需要通配符判断，运行效率更高：

="F." & IF(LEFT(A1,3)="120",3,IF(LEFT(A1,2)="60",1,2)) &
".T." & MATCH(MID(A1,IF(LEFT(A1,3)="120",4,3),2),{"BM","CM","MM"},0) &
"." & RIGHT(A1,1)

公式直接判断前缀长度：120为3位数字，对应编码3；60、90为2位数字，分别对应1、2；中间2位字母通过MATCH匹配对应编码；最后1位后缀直接取单元格最右侧字符即可，输出结果和预期完全一致。

内容的提问来源于stack exchange，提问作者user3310334