如何在Sheets中比较逗号分隔值列表并判断包含关系？

以下几种方法可以实现你的需求：检查「Test lists」的每个逗号分隔集合，是否被「Possible Solutions」中的某一个集合完全包含（允许包含额外值，但不能缺少测试列表的任何值），存在则返回TRUE，否则返回FALSE。

方案1：Excel 365/2021 动态数组公式 #

利用动态数组函数简化逻辑，直接在单元格输入公式后下拉：

=OR(BYROW($B$2:$B$6, LAMBDA(x, AND(ISNUMBER(XMATCH(TEXTSPLIT(A2, ", "), TEXTSPLIT(x, ", ")))))))

• TEXTSPLIT(A2, ", ")：将当前测试列表拆分为单个值的数组
• BYROW($B$2:$B$6, LAMBDA(x, ...))：遍历所有Possible Solutions单元格
• 对每个解决方案，用XMATCH检查测试列表的所有值是否都存在其中，AND确认全部匹配
• OR判断是否存在至少一个符合条件的解决方案，返回最终布尔值

方案2：兼容旧版Excel的公式 #

如果使用无动态数组的旧版Excel，可结合FILTERXML和SUMPRODUCT实现：

=SUMPRODUCT(--(SUMPRODUCT(--ISNUMBER(SEARCH(", "&FILTERXML("<t><s>"&SUBSTITUTE(A2, ", ","</s><s>")&"</s></t>","//s")&", ", ", "&$B$2:$B$6&", ")))=COUNTA(FILTERXML("<t><s>"&SUBSTITUTE(A2, ", ","</s><s>")&"</s></t>","//s"))))>0

• 用", "&值&", " 包裹内容，避免部分匹配（比如避免把"a"误判为包含在"ab"中）
• 统计每个解决方案中匹配测试列表值的数量，当数量等于测试列表的总项数时，说明完全包含
• SUMPRODUCT统计符合条件的解决方案数量，大于0则返回TRUE

方案3：Power Query批量处理 #

适合数据量较大的场景，步骤如下：

1. 选中数据区域，点击「数据」→「从表格/区域」导入Power Query
2. 给表格添加索引列（「添加列」→「索引列」），保留原始行的对应关系
3. 拆分「Test lists」列：选中列→「拆分列」→「按分隔符」，选择逗号加空格，拆成行
4. 同理拆分「Possible Solutions」列并保留索引，然后将两个拆分后的表按值合并
5. 按测试列表索引和解决方案索引分组，统计匹配值的数量；再按测试列表索引分组，判断是否存在解决方案的匹配数等于测试列表的总项数
6. 将结果加载回Excel

核心M代码片段：

// 拆分测试列表并保留索引
TestSplitRows = Table.ExpandListColumn(Table.TransformColumns(Table.AddIndexColumn(Source, "TestIndex"), {{"Test lists", Splitter.SplitTextByDelimiter(", ")}}), "Test lists"),
// 拆分解决方案并保留索引
SolSplitRows = Table.ExpandListColumn(Table.TransformColumns(Table.AddIndexColumn(Source, "SolIndex"), {{"Possible Solutions", Splitter.SplitTextByDelimiter(", ")}}), "Possible Solutions"),
// 匹配并统计
Grouped = Table.Group(Table.NestedJoin(TestSplitRows, {"Test lists"}, SolSplitRows, {"Possible Solutions"}, "Matches"), {"TestIndex", "SolIndex"}, {{"MatchCount", each Table.RowCount(Table.RemoveNulls(_))}}),
// 判断是否存在完全包含的解决方案
FinalResult = Table.Group(Grouped, {"TestIndex"}, {{"Result", each List.AnyTrue([MatchCount] = Table.RowCount(Table.SelectRows(TestSplitRows, (r)=>r[TestIndex]=_[TestIndex]{0})))}})

方案4：VBA自定义函数 #

编写自定义函数，可直接在单元格调用：

Function IsListContained(testList As String, solRange As Range) As Boolean
    Dim testArr As Variant, solArr As Variant
    Dim i As Integer, j As Integer, allFound As Boolean
    
    testArr = Split(testList, ", ")
    If UBound(testArr) = -1 Then ' 处理空值
        IsListContained = False
        Exit Function
    End If
    
    For Each cell In solRange
        If cell.Value <> "" Then
            solArr = Split(cell.Value, ", ")
            allFound = True
            ' 检查测试列表的每个值是否都在当前解决方案中
            For i = LBound(testArr) To UBound(testArr)
                allFound = False
                For j = LBound(solArr) To UBound(solArr)
                    If testArr(i) = solArr(j) Then
                        allFound = True
                        Exit For
                    End If
                Next j
                If Not allFound Then Exit For
            Next i
            If allFound Then
                IsListContained = True
                Exit Function
            End If
        End If
    Next cell
    IsListContained = False
End Function

使用方法：在目标单元格输入=IsListContained(A2, $B$2:$B$6)，下拉填充即可。

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