☰

Excel 中的数据验证-单元格范围内的逗号分隔列表中的所有值

tag-icon • tag-icon microsoft-excel-2010 worksheet-function csv data-validation
Excel 中的数据验证-单元格范围内的逗号分隔列表中的所有值

我有一份备忘录参考表和一些有关备忘录的信息，包括它们引用的其他备忘录以及它们被引用的内容。我想制定一个数据验证规则，如果逗号分隔的参考列表中的任何值不在我的备忘录列表中（换句话说，我们缺少信息或用户可能输入了错误），该规则将显示为红色。可以引用任意数量的其他备忘录。每个条目的备忘录编号在 A 列中，参考资料在 G 列中。标题在 A 行，数据在以下行中，定期添加。

......G

LF100009.......400 <--红色

HS100011.......

RT100012.......LF100009

LA100015.......LF100009，RT100012

ME100020.......HS100011, RT1000012, LA100015 <--红色

KE100050.......LA100011, LA100015 <--红色

工作簿的屏幕截图

答案1

它一点也不优雅，但最多可以用于 3 个引用。您可以根据需要与条件格式结合使用，也可以将其放入辅助列中。

=IF(IF(IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)=TRUE,1,
IF(IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)=1,COUNTIF(A:A,G2),
IF(IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)=2,SUMPRODUCT((COUNTIF(A:A,LEFT(G2,FIND(",",G2)-1)))*(COUNTIF(A:A,MID(G2,FIND(",",G2)+2,LEN(G2))))),
IF(IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)=3,SUMPRODUCT((COUNTIF(A:A,LEFT(G2,FIND(",",G2)-1)))*(COUNTIF(A:A,MID(G2,FIND(",",G2)+2,LEN(G2)-FIND("@",SUBSTITUTE(G2,",","@",IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)-1))-1)))*(COUNTIF(A:A,MID(G2,FIND("@",SUBSTITUTE(G2,",","@",IF(G2="",TRUE,LEN(G2)-LEN(SUBSTITUTE(G2,",",""))+1)-1))+2,LEN(G2))))),
FALSE
))))=1,TRUE)

答案2

下面是一个 UDF，它将 TRUE / FALSE 值放入辅助单元格中。公式如下所示=testReferences(G2,A:A,", ")

以下代码粘贴到 VBA 编辑器中。它可用于无限次引用。

Function testReferences(text As Range, list As Range, delimiter As String)
    Dim arr() As String
    Dim found As Boolean

    If text = "" Then
        found = True
    Else
        arr = Split(text, delimiter)            
        For i = LBound(arr) To UBound(arr)
            If Application.WorksheetFunction.CountIf(list, arr(i)) = 1 Then
                found = True
            Else
                found = False
                Exit For
            End If
        Next
    End If

    testReferences = found
End Function

相关内容