检查单元格是否包含动态列表中的任何单词

Question 1

您可以使用数组公式来执行此操作。对于固定范围，公式C2如下

=IF(MAX(IFERROR(SEARCH($A$2:$A$5,$B2),0))>0,"yes","no")

确保使用Ctrl+ Shift+将其作为数组公式输入Enter。当公式两端都有花括号 { } 时，您就会知道它起作用了。

如果您希望范围是动态的，一个好方法是定义一个命名范围。如果您尝试使用所有列，A:A那么所有内容都会返回，yes因为如果您要查找的字符串为空，SEARCH和FIND函数都会返回。由于列表下会包含空白，因此所有结果都将为空白，这没有帮助。相反，我们需要定义一个动态命名范围。有几种方法可以做到这一点，但我喜欢打开“名称管理器”（它位于“公式”功能区中间的“定义名称”部分）。打开它并单击左上角的“新建”。给它一个名字，如和一个“引用”公式TRUEA:AyesKeywords

=OFFSET(Sheet1!$A$1,1,0,MATCH(TRUE,INDEX(ISBLANK(Sheet1!$A:$A),0,0),0)-2)

现在，您可以将公式更改为如下形式（仍然使用++C1输入）CtrlShiftEnter

=IF(MAX(IFERROR(SEARCH(Keywords,$B2),0))>0,"yes","no")

警告 #1

SEARCH不区分大小写。如果您想要区分大小写的搜索，请将其替换为FIND。

警告 #2

动态命名范围的公式假设关键字列表中没有任何空白。它将引用从A2到第一个空白之前的单元格的所有单元格。当然，这也假设有一个空白某处这意味着A:A你的关键字列表必须少于 1,048,575 个项目。

警告 #3

您可以在不使用命名范围的情况下执行此操作，但公式会变得很长，更难理解。但是，它确实将所有内容打包成一个公式。如果您的数据非常大，我建议使用命名范围，因为它会加快计算速度。要将所有这些结合起来，公式将C2是

=IF(MAX(IFERROR(SEARCH(OFFSET(Sheet1!$A$1,1,0,MATCH(TRUE,INDEX(ISBLANK(Sheet1!$A:$A),0,0),0)-2),$B2),0))>0,"yes","no")

Answer

您可以使用数组公式来执行此操作。对于固定范围，公式C2如下

=IF(MAX(IFERROR(SEARCH($A$2:$A$5,$B2),0))>0,"yes","no")

确保使用Ctrl+ Shift+将其作为数组公式输入Enter。当公式两端都有花括号 { } 时，您就会知道它起作用了。

如果您希望范围是动态的，一个好方法是定义一个命名范围。如果您尝试使用所有列，A:A那么所有内容都会返回，yes因为如果您要查找的字符串为空，SEARCH和FIND函数都会返回。由于列表下会包含空白，因此所有结果都将为空白，这没有帮助。相反，我们需要定义一个动态命名范围。有几种方法可以做到这一点，但我喜欢打开“名称管理器”（它位于“公式”功能区中间的“定义名称”部分）。打开它并单击左上角的“新建”。给它一个名字，如和一个“引用”公式TRUEA:AyesKeywords

=OFFSET(Sheet1!$A$1,1,0,MATCH(TRUE,INDEX(ISBLANK(Sheet1!$A:$A),0,0),0)-2)

现在，您可以将公式更改为如下形式（仍然使用++C1输入）CtrlShiftEnter

=IF(MAX(IFERROR(SEARCH(Keywords,$B2),0))>0,"yes","no")

警告 #1

SEARCH不区分大小写。如果您想要区分大小写的搜索，请将其替换为FIND。

警告 #2

动态命名范围的公式假设关键字列表中没有任何空白。它将引用从A2到第一个空白之前的单元格的所有单元格。当然，这也假设有一个空白某处这意味着A:A你的关键字列表必须少于 1,048,575 个项目。

警告 #3

您可以在不使用命名范围的情况下执行此操作，但公式会变得很长，更难理解。但是，它确实将所有内容打包成一个公式。如果您的数据非常大，我建议使用命名范围，因为它会加快计算速度。要将所有这些结合起来，公式将C2是

=IF(MAX(IFERROR(SEARCH(OFFSET(Sheet1!$A$1,1,0,MATCH(TRUE,INDEX(ISBLANK(Sheet1!$A:$A),0,0),0)-2),$B2),0))>0,"yes","no")

Question 2

尝试一下这个小用户定义函数（UDF）：

Public Function MultiMatch(sIN As String, rng As Range) As String
    Dim r As Range
    MultiMatch = "no"

    For Each r In rng
        If InStr(1, sIN, r.Text) > 0 Then
            MultiMatch = "yes"
            Exit Function
        End If
    Next r
End Function

用户定义函数 (UDF) 非常容易安装和使用：

ALT-F11 打开 VBE 窗口
ALT-I ALT-M 打开新模块
粘贴内容并关闭 VBE 窗口

如果您保存工作簿，UDF 将随之保存。如果您使用的是 2003 之后的 Excel 版本，则必须将文件保存为 .xlsm 而不是 .xlsx

要删除 UDF：

调出如上所示的 VBE 窗口
清除代码
关闭 VBE 窗口

要从 Excel 使用 UDF：

=MultiMatch(B1,$A$1:$A$4)

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

有关 UDF 的详细信息，请参阅：

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

必须启用宏才能使其工作！

在此处输入图片描述

Answer