多工作表匹配ID与日期，返回百分比>0的工作表名称问题

我有一个含多个工作表（会不时新增）的Excel文件，每个工作表某列是唯一ID，顶部是日期，日期下方是0%-100%的百分比值，部分ID跨表存在。需要在汇总工作表中，按ID和日期查找对应百分比值，若值>0%则返回对应的工作表名称。

目前有一个跨表求和公式可正常使用，其中Tab_List是包含所有工作表名称的命名区域，B$1为目标月份：

=LET(
    x, SUBSTITUTE(ADDRESS(1,MATCH(B$1,$B$1:$G$1,0),4),"1",""),
    SUMPRODUCT(SUMIFS(INDIRECT("'"&Tab_List&"'!"&x&"$1:"&x&"$1000"), INDIRECT("'"&Tab_List&"'!a$1:a$1000"),$A2))
)

我尝试修改公式以实现需求，但返回#VALUE!错误：

=LET(
    col, SUBSTITUTE(ADDRESS(1, MATCH(B$15,$B$1:$G$1,0), 4), "1", ""),
    tabindx, ROW(INDIRECT("1:" & COUNTA(Tab_List))),
    found, IF(SUMPRODUCT((INDIRECT("'" & Tab_List  & "'!" & col & "$1:" & col & "$1000")>0)*(INDIRECT("'"&Tab_List&"'!B$1:B$1000")=$A15))>0, Tab_List, ""),
    TEXTJOIN(", ",TRUE, FILTER(found, found <> ""))
)

汇总工作表和各子工作表结构参考对应截图。

原公式报错的核心原因：SUMPRODUCT无法直接处理INDIRECT("'"&Tab_List&"'!xxx")返回的多工作表独立区域数组，逻辑运算时会出现维度不匹配的问题。

修正后的公式 #

改用BYROW遍历每个工作表名称，逐个验证条件，再收集符合要求的工作表名称：

=LET(
    targetDate, B$15,
    targetID, $A15,
    col, SUBSTITUTE(ADDRESS(1, MATCH(targetDate, $B$1:$G$1, 0), 4), "1", ""),
    // 遍历每个工作表，判断是否存在匹配ID且对应百分比>0
    result, BYROW(Tab_List, LAMBDA(tab,
        LET(
            idRange, INDIRECT("'"&tab&"'!B$1:B$1000"),
            valRange, INDIRECT("'"&tab&"'!"&col&"$1:"&col&"$1000"),
            matchCount, COUNTIFS(idRange, targetID, valRange, ">0%"),
            IF(matchCount>0, tab, "")
        )
    )),
    // 拼接非空结果
    TEXTJOIN(", ", TRUE, FILTER(result, result<>""))
)

优化版公式（适用于支持TOCOL/HSTACK的Excel版本） #

如果你的Excel版本支持这些函数，可以进一步简化，同时降低INDIRECT因工作表名称含特殊字符出错的概率：

=LET(
    targetDate, B$15,
    targetID, $A15,
    colNum, MATCH(targetDate, $B$1:$G$1, 0),
    // 批量获取所有工作表的ID列、目标值列和对应工作表名称
    allIDs, TOCOL(HSTACK(INDIRECT("'"&Tab_List&"'!B$1:B$1000")), 2),
    allVals, TOCOL(HSTACK(INDIRECT("'"&Tab_List&"'!R1C"&colNum&":R1000C"&colNum, FALSE)), 2),
    allTabs, TOCOL(HSTACK(REPT(Tab_List, 1000)), 2),
    // 筛选符合条件的工作表名称并去重
    filteredTabs, UNIQUE(FILTER(allTabs, (allIDs=targetID)*(allVals>0%))),
    TEXTJOIN(", ", TRUE, filteredTabs)
)

额外提示 #

• 新增工作表后，需更新Tab_List命名区域；若要自动更新，可结合GET.WORKBOOK与FILTER动态获取所有工作表名称。
• 若工作表名称包含空格或特殊字符，INDIRECT中的单引号已做处理，无需额外调整。

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