Office 2021多条件匹配查找：全匹配商品返回对应订单号

针对你的需求（在Excel 2021中，找出包含所有指定商品的订单号），以下提供两种可行方案：函数公式法和VBA自定义函数法，同时附上搜索关键词供你拓展学习。

一、函数公式方案（动态数组支持） #

假设你的数据表范围是A2:A9（订单号）、B2:B9（商品），指定的目标商品放在G2:G4（比如要找包含Apple、Banana、Pear的订单），可以使用以下公式：

=FILTER(UNIQUE(A2:A9),BYROW(UNIQUE(A2:A9),LAMBDA(x,SUMPRODUCT(--(COUNTIFS(A:A,x,B:B,G2:G4)>0))=COUNTA(G2:G4))))

公式解析： #

1. UNIQUE(A2:A9)：提取所有唯一的订单号，避免重复检查同一订单
2. BYROW(..., LAMBDA(x, ...))：遍历每个唯一订单号x，执行后续验证逻辑
3. SUMPRODUCT(--(COUNTIFS(A:A,x,B:B,G2:G4)>0))：计算当前订单x包含的目标商品数量（每个目标商品只要出现一次就算数）
4. =COUNTA(G2:G4)：判断当前订单包含的目标商品数量是否等于所有指定商品的总数，满足则保留该订单号
5. FILTER：最终筛选出符合条件的所有订单号

二、VBA自定义函数方案 #

如果需要更灵活的逻辑（比如处理大量数据或复杂规则），可以编写自定义函数：

1. 按Alt+F11打开VBA编辑器，插入新模块，粘贴以下代码：

Function GetOrdersWithAllProducts(orderRange As Range, productRange As Range, targetProducts As Range) As Variant
    Dim orderDict As Object
    Dim cell As Range
    Dim targetArr As Variant
    Dim i As Integer, j As Integer
    Dim hasAll As Boolean
    
    Set orderDict = CreateObject("Scripting.Dictionary")
    targetArr = targetProducts.Value
    
    ' 构建每个订单对应的商品集合
    For Each cell In orderRange
        If Not orderDict.Exists(cell.Value) Then
            orderDict(cell.Value) = CreateObject("Scripting.Dictionary")
        End If
        orderDict(cell.Value)(productRange(cell.Row - orderRange.Row + 1).Value) = True
    Next cell
    
    ' 筛选包含所有目标商品的订单
    Dim resultArr() As String
    ReDim resultArr(0 To orderDict.Count - 1)
    i = 0
    For Each key In orderDict.Keys
        hasAll = True
        For j = 1 To UBound(targetArr, 1)
            If Not orderDict(key).Exists(targetArr(j, 1)) Then
                hasAll = False
                Exit For
            End If
        Next j
        If hasAll Then
            resultArr(i) = key
            i = i + 1
        End If
    Next key
    
    ReDim Preserve resultArr(0 To i - 1)
    GetOrdersWithAllProducts = resultArr
End Function

2. 返回Excel界面，在任意单元格输入公式（替换对应范围）：

=GetOrdersWithAllProducts(A2:A9,B2:B9,G2:G4)

按回车即可得到包含所有指定商品的订单号列表。

三、搜索关键词（用于拓展学习） #

• 英文关键词：Excel find orders containing all specified products、Excel FILTER BYROW multiple criteria、Excel VBA dictionary match all items
• 中文关键词：Excel 筛选包含全部指定商品的订单号、Excel 多条件匹配所有项、Excel VBA 字典 商品匹配

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