在excel中匹配查询单元格中的多个值

Question

我有一个列，列有一组特殊的基因组。还有另一列基因组，其中一些细胞有多个条目，逗号分隔.我想要的是检查第二列的每个单元格是否是第一列中的任何条目。如果第二列中的单元格只有一个元素，我可以使用=NOT(ISERROR(MATCH(B2,$A$2:$A$120,0)))来完成，但是由于有些查询单元格有多个元素，所以这些元素不适用。有什么建议吗？

​

​

Answer 1

试试SUMPRODUCT和SEARCH

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(","&$A$2:$A$5&",",","&B2&","))),"Y","N")

​

​

解释：

我将在我的示例中解释单元格C2的逻辑。

1. 在第一步中，公式将范围和结果的值输入： =IF(SUMPRODUCT(--ISNUMBER(SEARCH({",AA,",",CC,",",EE,",",Gg,"},",AA,BB,"))),"Y","N")
2. SEARCH函数返回找到的字符串或错误的位置数组(如果没有找到)： =IF(SUMPRODUCT(--ISNUMBER({1,#VALUE,#VALUE,#VALUE})),"Y","N")
3. ISNUMBER返回布尔值数组： =IF(SUMPRODUCT(--{TRUE,FALSE,FALSE,FALSE}),"Y","N")
4. --将布尔值转换为整数： =IF(SUMPRODUCT({1,0,0,0}),"Y","N")
5. SUMPRODUCT总结了一下： =IF(1,"Y","N")
6. 如果第一个参数为非零值，IF将返回TRUE部件。

Answer 2

如果需要保留基因组列中的行，则将基因组列分离为3列，并在新的B、C和D列中进行匹配。

=NOT(ISERROR(MATCH(A2,$B$2:$D$120,0)))

编辑:或者您可以尝试使用VB宏和正则表达式：

Public Function RegexExecute(str As String, reg As String, _
                             Optional matchIndex As Long, _
                             Optional subMatchIndex As Long) As String
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = reg
    regex.Global = Not (matchIndex = 0 And subMatchIndex = 0) 'For efficiency
    If regex.test(str) Then
        Set matches = regex.Execute(str)
        RegexExecute = matches(matchIndex).SubMatches(subMatchIndex)
        Exit Function
    End If
ErrHandl:
    RegexExecute = CVErr(xlErrValue)
End Function

参考文献