Excel跨工作表匹配斜杠分隔多值单元格并关联短名输出方法

场景与需求梳理 #

• 现有数据存储结构
  • TestConfigs工作表B3:B11区域为多值列，每个单元格内的多个字符串以/分隔
  • Commun工作表存储待搜索字符串列表，G列为待匹配关键词，同时每个关键词对应关联Shortname字段
• 核心规则
  • 遍历待搜索列表的每一个关键词，在多值列中做精确匹配
  • 每匹配成功1次，就在结果列写入1次对应匹配项+关联Shortname
  • 所有以GPRF_开头的关键词，严格按照实际匹配次数重复输出：例如GPRF_TxChPower共匹配到3次，就连续输出3次带对应Shortname的结果，再处理下一个待搜索项
• 原有方案的问题
  • 原有F列判断公式仅能识别关键词是否存在，无法统计实际匹配次数，且SEARCH函数为模糊匹配，容易出现子串误匹配
  • 原有结果列公式仅能单次输出匹配到的关键词，无法实现按次数重复输出、关联Shortname的需求

实现方案 #

方案1：适用于Excel 365/2021及以上版本 纯公式无宏方案 #

不需要额外辅助列，直接在结果列起始单元格输入以下动态数组公式，回车后会自动溢出所有符合要求的结果：

=LET(
  searchRng, Commun!$G$35:INDEX(Commun!G:G,COUNTA(Commun!G:G)),
  snRng, Commun!$E$35:INDEX(Commun!E:E,COUNTA(Commun!E:E)),
  sourceStr, "|"&SUBSTITUTE(TEXTJOIN("/",TRUE,TestConfigs!$B$3:$B$11),"/","|")&"|",
  cntList, MAP(searchRng,LAMBDA(k,SUMPRODUCT(LEN(sourceStr)-LEN(SUBSTITUTE(sourceStr,"|"&k&"|","")))/LEN("|"&k&"|"))),
  rawRes, REDUCE("",SEQUENCE(ROWS(searchRng)),LAMBDA(acc,i,
    LET(
      letK, INDEX(searchRng,i),
      letCnt, INDEX(cntList,i),
      letSn, INDEX(snRng,i),
      IF(letCnt=0, acc,
        IF(LEFT(letK,5)="GPRF_",
          VSTACK(acc,REPT(letK&" - "&letSn&CHAR(10),letCnt)),
          VSTACK(acc,letK&" - "&letSn&CHAR(10))
        )
      )
    )
  )),
  resArr, TEXTSPLIT(TEXTJOIN("",TRUE,rawRes),CHAR(10)),
  FILTER(resArr,resArr<>"")
)

注意：公式中snRng参数对应Shortname所在列，示例写的是E列，实际使用时替换成你表格里Shortname的实际列号即可；输出的连接符-可以根据需要自行修改

方案2：适用于所有Excel版本 VBA自定义函数方案 #

1. 打开Excel文件后按Alt+F11调出VBA编辑器，右键点击当前工作簿名称选择「插入」-「模块」
2. 在模块代码窗口粘贴以下代码：

Function MatchAndExpand(searchRng As Range, snRng As Range, sourceRng As Range) As Variant
    Dim sourceText As String, cell As Range, sourceArr() As String
    Dim i As Long, j As Long, matchCnt As Long, resIdx As Long
    Dim key As String, sn As String, resArr() As String
    
    ' 拼接所有多值列内容，按/拆分为独立值数组
    sourceText = ""
    For Each cell In sourceRng
        If Trim(cell.Value) <> "" Then
            sourceText = sourceText & "/" & Trim(cell.Value)
        End If
    Next
    sourceArr = Split(Mid(sourceText, 2), "/")
    
    ' 预分配结果数组空间
    resIdx = 0
    ReDim resArr(1 To UBound(sourceArr) + 10, 1 To 1)
    
    ' 遍历每个待搜索关键词
    For i = 1 To searchRng.Cells.Count
        key = Trim(searchRng.Cells(i).Value)
        sn = Trim(snRng.Cells(i).Value)
        If key <> "" Then
            matchCnt = 0
            ' 统计精确匹配次数
            For j = 0 To UBound(sourceArr)
                If Trim(sourceArr(j)) = key Then
                    matchCnt = matchCnt + 1
                End If
            Next
            
            ' 按规则输出结果
            If matchCnt > 0 Then
                If Left(key, 5) = "GPRF_" Then
                    ' GPRF开头项按实际匹配次数重复输出
                    For j = 1 To matchCnt
                        resIdx = resIdx + 1
                        resArr(resIdx, 1) = key & " - " & sn
                    Next
                Else
                    ' 非GPRF开头项匹配即输出1次，需要按次数输出的话把下面1改成matchCnt即可
                    resIdx = resIdx + 1
                    resArr(resIdx, 1) = key & " - " & sn
                End If
            End If
        End If
    Next
    
    MatchAndExpand = resArr
End Function

3. 关闭VBA编辑器回到Excel界面，选中结果列的起始单元格，输入公式：

=MatchAndExpand(Commun!G35:G100, Commun!E35:E100, TestConfigs!B3:B11)

公式参数说明：第一个参数是待搜索关键词所在区域，第二个参数是对应Shortname所在区域，第三个参数是多值列数据源区域，根据实际表格范围修改即可。Excel 2019及更早版本输入完成后按Ctrl+Shift+Enter作为数组公式确认，365/2021版本直接回车即可自动输出所有结果。

使用提示 #

• 两个方案均采用精确匹配逻辑，不会出现原有SEARCH函数模糊匹配导致的误判问题，比如搜索a不会误匹配到ab
• 如果非GPRF_开头的项也需要按实际匹配次数重复输出，直接修改对应分支的输出次数参数即可
• 原有F列的判断公式可以直接删除，新方案已经内置匹配判断、次数统计逻辑，不需要额外辅助列

内容的提问来源于stack exchange，提问作者Mateus Santos Valença