如何用Excel公式（无VBA）按规则列表分类账号？

核心公式方案（动态适配账号列表） #

假设表1账号在A列（从A2开始，支持动态扩展），表2分类在C列、匹配规则在D列（从C2:D5开始，可动态扩展），在B2单元格输入以下公式（Excel 365/2021直接回车，旧版本按Ctrl+Shift+Enter执行数组运算）：

=XLOOKUP(TRUE,BYROW(D$2:D$5,LAMBDA(r,OR(ISNUMBER(SEARCH(TEXTSPLIT(r,";"),A2))))),C$2:C$5,"无匹配",0,1)

公式逻辑说明 #

• TEXTSPLIT(r,";")：拆分单条规则中的多个匹配项（比如把102;103拆成{"102","103"}）
• SEARCH(...,A2)：检查当前账号是否包含拆分后的任一匹配内容
• ISNUMBER+OR：判断当前规则是否有至少一个匹配项命中账号
• BYROW：遍历所有规则，返回每条规则的命中结果数组
• XLOOKUP：按规则列表的顺序查找第一个命中的分类，保证规则优先级；最后一个参数1确保匹配到第一个符合条件的结果

如果需要区分前缀匹配和包含匹配（比如规则要求前4位匹配，而非任意位置包含），可以给规则加标识（比如前缀规则写1001，包含规则写*1003），公式调整为：

=XLOOKUP(TRUE,BYROW(D$2:D$5,LAMBDA(r,OR(LET(s,TEXTSPLIT(r,";"),IF(LEFT(r,1)="*",ISNUMBER(SEARCH(RIGHT(r,LEN(r)-1),A2)),LEFT(A2,LEN(s))=s))))),C$2:C$5,"无匹配",0,1)

数据结构优化建议（可选） #

如果允许调整规则存储格式，建议把多选项规则拆分为多行（比如原规则102;103拆成两行，分类都标注为「分类3」），公式可简化为：

=XLOOKUP(TRUE,ISNUMBER(SEARCH(D$2:D$6,A2)),C$2:C$6,"无匹配",0,1)

这种结构更直观，降低公式复杂度，同时不影响动态扩展能力。

动态范围适配 #

要让公式自动适配账号列表和规则列表的新增内容，可将固定范围替换为动态范围：

• 账号动态范围：A2:INDEX(A:A,COUNTA(A:A))
• 规则动态范围：D2:INDEX(D:D,COUNTA(D:D))，对应分类范围：C2:INDEX(C:C,COUNTA(C:C))

内容的提问来源于stack exchange，提问作者Dr.Rosik