如何匹配多值范围并按另一值筛选？Excel函数技术求助

表格结构 #

• Table1：A列=产品名，B列=客户名，记录客户缺失的产品
• Table2：A列=产品名，B列=Bundle（父产品）名，记录产品所属Bundle
• Table3：A列=Bundle名，B列=客户名，记录客户已购的Bundle

需求 #

判断Table1中每个产品对应的客户是否拥有包含该产品的任意Bundle，返回首个匹配的Bundle名（无匹配可返回指定提示）

解法1：Excel公式实现 #

针对Table1第2行（A2=产品名，B2=客户名），使用以下公式后下拉填充即可：

=IFERROR(INDEX(FILTER(Table3[Bundle名], (Table3[客户名]=B2)*ISNUMBER(MATCH(Table3[Bundle名], FILTER(Table2[Bundle名], Table2[产品名]=A2), 0))), 1), "无匹配")

公式逻辑：

1. FILTER(Table2[Bundle名], Table2[产品名]=A2)：提取当前产品所属的所有Bundle
2. MATCH(Table3[Bundle名], ..., 0)：判断客户已购的Bundle是否在上述列表中
3. FILTER(Table3[Bundle名], ...)：筛选出该客户已购且包含当前产品的Bundle
4. INDEX(...,1)：取首个匹配结果，IFERROR处理无匹配的情况

也可以用XLOOKUP实现：

=XLOOKUP(TRUE, ISNUMBER(MATCH(Table3[Bundle名], FILTER(Table2[Bundle名], Table2[产品名]=A2), 0))*(Table3[客户名]=B2), Table3[Bundle名], "无匹配")

解法2：VBA脚本实现 #

适合数据量较大的场景，运行后结果会写入Table1的C列：

Sub CheckBundleOwnership()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long, lastRow3 As Long
    Dim i As Long, j As Long, k As Long
    Dim product As String, customer As String
    Dim bundleList As Collection
    Dim foundBundle As String
    
    ' 替换为实际工作表名称
    Set ws1 = ThisWorkbook.Worksheets("Table1")
    Set ws2 = ThisWorkbook.Worksheets("Table2")
    Set ws3 = ThisWorkbook.Worksheets("Table3")
    
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    lastRow3 = ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历Table1每一行
    For i = 2 To lastRow1
        product = ws1.Cells(i, "A").Value
        customer = ws1.Cells(i, "B").Value
        Set bundleList = New Collection
        
        ' 收集当前产品所属的所有Bundle（去重）
        On Error Resume Next
        For j = 2 To lastRow2
            If ws2.Cells(j, "A").Value = product Then
                bundleList.Add ws2.Cells(j, "B").Value, Key:=CStr(ws2.Cells(j, "B").Value)
            End If
        Next j
        On Error GoTo 0
        
        ' 查找客户已购的匹配Bundle
        foundBundle = "无匹配"
        For k = 2 To lastRow3
            If ws3.Cells(k, "B").Value = customer Then
                On Error Resume Next
                bundleList.Item(ws3.Cells(k, "A").Value)
                If Err.Number = 0 Then
                    foundBundle = ws3.Cells(k, "A").Value
                    Exit For ' 找到首个匹配即停止
                End If
                On Error GoTo 0
            End If
        Next k
        
        ws1.Cells(i, "C").Value = foundBundle
    Next i
    
    ' 释放对象
    Set bundleList = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
    Set ws3 = Nothing
End Sub

解法3：Python脚本实现（Pandas） #

适合批量处理或自动化场景：

import pandas as pd

# 读取Excel文件，替换为实际文件路径
df1 = pd.read_excel("your_file.xlsx", sheet_name="Table1")
df2 = pd.read_excel("your_file.xlsx", sheet_name="Table2")
df3 = pd.read_excel("your_file.xlsx", sheet_name="Table3")

# 建立产品与对应Bundle的映射（去重）
product_to_bundles = df2.groupby("产品名")["Bundle名"].unique().reset_index()
# 建立客户与已购Bundle的映射
customer_to_bundles = df3.groupby("客户名")["Bundle名"].unique().reset_index()

# 合并数据
merged_df = pd.merge(df1, product_to_bundles, on="产品名", how="left")
merged_df = pd.merge(merged_df, customer_to_bundles, on="客户名", how="left")

# 定义函数判断是否有匹配的Bundle
def find_matching_bundle(row):
    if pd.isna(row["Bundle名_x"]) or pd.isna(row["Bundle名_y"]):
        return "无匹配"
    # 计算两个Bundle列表的交集
    common_bundles = set(row["Bundle名_x"]) & set(row["Bundle名_y"])
    # 返回首个匹配的Bundle，无则返回提示
    return next(iter(common_bundles), "无匹配")

# 应用函数生成结果列
merged_df["匹配的Bundle"] = merged_df.apply(find_matching_bundle, axis=1)

# 输出结果到控制台
print(merged_df[["产品名", "客户名", "匹配的Bundle"]])
# 保存结果到新Excel文件
merged_df.to_excel("bundle_check_result.xlsx", index=False)

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