如何用Conditional Formatting或其他方法批量识别Excel目标列关键词匹配单元格？

方法一：条件格式数组公式（操作简单，适合中等数据量） #

假设关键词存于B1:B3000，目标句子在A1:A100000：

1. 选中A列所有需要处理的单元格（从A1拖至A列最后一行）
2. 点击「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
3. 输入以下公式，再设置你需要的高亮格式（比如填充色）：

=SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$3000,A1)))>0

• 细节调整：
  • 需区分大小写匹配？把SEARCH替换为FIND
  • 要精准匹配完整单词（避免"apple"匹配"apples"）？改用这个公式：

    =SUMPRODUCT(--ISNUMBER(SEARCH(" " & $B$1:$B$3000 & " ", " " & A1 & " ")))>0
    

• 注意：10万行+3000关键词的组合会拖慢公式计算，建议先开启Excel「手动重算」（文件→选项→公式→手动重算），设置完条件格式后再切回自动重算。

方法二：VBA脚本（效率拉满，适合超大数据量） #

公式计算大数量级数据易卡顿，VBA直接操作数组能大幅提升速度：

1. 按Alt+F11打开VBA编辑器
2. 右键当前工作表→「插入」→「模块」，粘贴以下代码：

Sub HighlightKeywords()
    Dim ws As Worksheet
    Dim keyRng As Range, targetRng As Range
    Dim keyArr As Variant, targetArr As Variant
    Dim i As Long, j As Long
    
    Set ws = ActiveSheet
    ' 读取B列所有非空关键词
    Set keyRng = ws.Range("B1", ws.Cells(ws.Rows.Count, "B").End(xlUp))
    keyArr = keyRng.Value
    ' 读取A列所有非空句子
    Set targetRng = ws.Range("A1", ws.Cells(ws.Rows.Count, "A").End(xlUp))
    targetArr = targetRng.Value
    
    ' 清除原有条件格式
    targetRng.FormatConditions.Delete
    
    ' 批量遍历匹配
    For i = 1 To UBound(targetArr)
        If Not IsEmpty(targetArr(i, 1)) Then
            For j = 1 To UBound(keyArr)
                If Not IsEmpty(keyArr(j, 1)) Then
                    ' vbTextCompare=不区分大小写；vbBinaryCompare=区分大小写
                    If InStr(1, targetArr(i, 1), keyArr(j, 1), vbTextCompare) > 0 Then
                        targetRng.Cells(i).Interior.ColorIndex = 6 ' 黄色填充，可改ColorIndex值换颜色
                        Exit For ' 匹配到即跳出，减少计算量
                    End If
                End If
            Next j
        End If
    Next i
End Sub

3. 点击编辑器工具栏的「运行」按钮（绿色三角），等待脚本执行完成。

• 自定义调整：
  • 修改高亮颜色：把ColorIndex = 6换成其他值（比如3=红色，10=绿色）
  • 精准匹配完整单词：可添加正则表达式逻辑，替换内层判断为：

    Dim reg As Object
    Set reg = CreateObject("VBScript.RegExp")
    reg.Pattern = "\b" & keyArr(j, 1) & "\b"
    reg.IgnoreCase = True ' 不区分大小写
    If reg.Test(targetArr(i, 1)) Then
        targetRng.Cells(i).Interior.ColorIndex = 6
        Exit For
    End If
    

内容的提问来源于stack exchange，提问作者H-M