Excel 2021无TEXTSPLIT：如何检查逗号分隔列值是否在另一表格

针对你的Excel 2021环境（无TEXTSPLIT等新函数），以下是几种高效处理20万+行数据的方法：

方法一：SUMPRODUCT函数法（无需数组输入，相对高效） #

假设表1位于Sheet1，IDs列是A列（数据从A2开始）；表2位于Sheet2，ID列是A列（数据从A2到A200001）。在表1的C2单元格输入以下公式，下拉填充即可：

=SUMPRODUCT(--ISNUMBER(SEARCH(","&Sheet2!$A$2:$A$200001&",", ","&Sheet1!A2&",")))>0

• 原理：通过在ID和IDs内容前后添加逗号，避免部分匹配（比如区分"one"和"onex"）；SEARCH查找是否存在匹配，ISNUMBER转换为布尔值，--将布尔值转为1/0，SUMPRODUCT求和后判断是否大于0，得出是否有匹配。
• 优化提示：将表2的ID列转为Excel表（按Ctrl+T），公式会自动适配动态行范围，且计算效率更高；关闭自动重算（公式选项卡→计算选项→手动），输入完公式后按F9手动重算，避免卡顿。

方法二：数组公式法（利用FILTERXML拆分多值） #

若需要明确拆分IDs内容后匹配，可使用数组公式（输入后按Ctrl+Shift+Enter确认，Excel 2021也支持直接回车）：

=NOT(ISERROR(MATCH(TRUE,ISNUMBER(MATCH(FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s"),Sheet2!$A$2:$A$200001,0)),0)))

• 原理：用SUBSTITUTE和FILTERXML拆分逗号分隔的IDs内容，再通过两层MATCH检查是否有值存在于表2的ID列，最终返回是否匹配的结果。
• 注意：该方法在20万行数据下可能比SUMPRODUCT稍慢，适合数据量相对小一些的场景。

方法三：VBA宏法（大数据量最优选择） #

对于20万+行的超大数据量，VBA的处理速度远快于函数，以下是实现代码：

Sub CheckIDMatch()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim dict As Object
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, arrIDs As Variant, id As Variant
    
    '指定工作表，根据实际情况修改
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    Set dict = CreateObject("Scripting.Dictionary")
    
    '将表2的ID存入字典，实现O(1)快速查找
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow2
        id = Trim(ws2.Cells(i, "A").Value)
        If id <> "" And Not dict.Exists(id) Then
            dict.Add id, True
        End If
    Next i
    
    '遍历表1，检查每个IDs单元格是否有匹配
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False '关闭屏幕刷新，加速运行
    For i = 2 To lastRow1
        arrIDs = Split(Trim(ws1.Cells(i, "A").Value), ", ")
        ws1.Cells(i, "C").Value = False
        For Each id In arrIDs
            If dict.Exists(Trim(id)) Then
                ws1.Cells(i, "C").Value = True
                Exit For '找到匹配立即跳出循环，节省时间
            End If
        Next id
    Next i
    Application.ScreenUpdating = True
    
    Set dict = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
    MsgBox "处理完成"
End Sub

• 使用步骤：按Alt+F11打开VBA编辑器，插入模块，粘贴代码，修改工作表名称后运行即可。
• 优势：字典查找的效率极高，20万行数据可在数秒内完成处理，且避免函数计算的卡顿问题。

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