Excel技术问询：提取指定表标识后筛选目标表符合条件的记录

需求说明 #

现有两个Excel表格：

• TableOne：字段包括tName（唯一标识）、Category、startDate
• TableTwo：字段包括tName、dDate、tStatus（注：示例数据中字段名误写为tStaus，实际以tStatus为准）

需实现：

1. 从TableOne中提取Category等于cat1的tName列表
2. 针对该列表中的每个tName，在TableTwo中筛选出对应tName的最大dDate记录，且该记录的tStatus不等于Accepted

示例数据 #

TableOne #

TableTwo #

预期结果 #

错误尝试及问题分析 #

Attempt 1 — 输出：#N/A #

=LET(
    tNames, FILTER(TableOne[tName],(TableOne[Category]="cat1"),"NoAsset"),
    dDates, TableTwo[dDate],
    tStatuses, TableTwo[tStatus],
    maxDates, MAXIFS(TableTwo[dDate], TableTwo[tName], tNames),
    result, IF((TableTwo[dDate]=maxDates)*(TableTwo[tStatus]<>&"Accepted"), TableTwo[tName], ""),
    FILTER(result, result<>&"")
)

问题：MAXIFS传入tNames数组时，会返回每个tName对应的最大日期数组，但后续TableTwo[dDate]=maxDates是将整个dDate列与该数组逐一匹配，逻辑不成立，无法正确筛选目标记录。

Attempt 2 — 输出：#Value! #

=LET(tNames,FILTER(TableOne[tName],(TableOne[Category]="cat1"),"NoAsset"),
cntr, SEQUENCE(ROWS(tNames)),
rngTwo,FILTER(tNames,(TableTwo[tName]=INDEX(tNames,cntr))*(TableTwo[dDate]=MAXIFS(TableTwo[dDate],TableTwo[tName],INDEX(tNames,cntr)))*(TableTwo[tStatus]<>&"Accepted")),
r,SEQUENCE(ROWS(rngTwo)),
output,INDEX(rngTwo,r),
output)

问题：INDEX(tNames,cntr)返回的是数组，而FILTER的条件参数要求维度与TableTwo的行数一致，两者维度不匹配，导致返回#VALUE!错误。

正确公式及解释 #

使用LET结合BYROW遍历每个目标tName，逐一筛选符合条件的记录，最后合并结果：

=LET(
    tNames, FILTER(TableOne[tName], TableOne[Category]="cat1"),
    filteredRecords, BYROW(tNames, LAMBDA(name, 
        LET(
            nameRecords, FILTER(TableTwo, TableTwo[tName]=name),
            maxDate, MAX(nameRecords[dDate]),
            result, FILTER(nameRecords, (nameRecords[dDate]=maxDate)*(nameRecords[tStatus]<>"Accepted")),
            IFERROR(result, "")
        )
    )),
    finalResult, FILTER(VSTACK(filteredRecords), NOT(ISBLANK(INDEX(VSTACK(filteredRecords),,1))))
)

步骤说明 #

1. 提取目标tName列表：用FILTER从TableOne中筛选出Category=cat1的tName
2. 遍历筛选单条记录：
   • 用BYROW遍历每个tName，先筛选TableTwo中该tName的所有记录
   • 计算该tName对应的最大dDate
   • 筛选出最大日期且tStatus≠Accepted的记录，无符合条件记录则返回空
3. 合并并清理结果：用VSTACK合并所有筛选结果，再用FILTER去掉空行，得到最终表格

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