Google Sheets中MAP嵌套FILTER内的OFFSET条件失效问题排查与解决

已实现功能 #

1. 提取所有每日表中的唯一姓名（列B公式）：

=let(_getDate,lambda(d,TEXT(DAY(d),"00")&"/"&TEXT(MONTH(d),"00")&"/"&YEAR(d)),Γ,tocol(map(SEQUENCE(J3-I3,1,1),lambda(d,torow(iferror(indirect(_getDate(d+I3)&"!"&"B3:B"),""),1))),1),Λ,filter(Γ,not(Γ="")),unique(Λ))

2. 统计姓名总出现次数（列C公式）：

=let(_getDate,lambda(d,TEXT(DAY(d),"00")&"/"&TEXT(MONTH(d),"00")&"/"&YEAR(d)),Γ,tocol(map(SEQUENCE(J3-I3,1,0,1),lambda(d,torow(iferror(indirect(_getDate(d+I3)&"!B3:C"),""),1))),1),Λ,filter(Γ,not(Γ="")),map(B3:B,lambda(name,if(name="",,COUNTA(filter(Λ,Λ=name))))))

问题描述 #

需要统计所有每日工作表（表名格式如"11/05/2023"）中，对应值为TRUE的姓名出现次数。尝试在列D中修改FILTER条件为filter(Λ,Λ=name,offset(Λ,1,0)=true)，但OFFSET条件不生效，未达到预期效果。

失效原因 #

1. Λ是扁平化的单列数组：原公式通过TOCOL将多列数据（B3:C）转为单列数组，此时OFFSET(Λ,1,0)指向的是数组下一行元素，而非原数据中对应姓名右侧的C列值，逻辑完全错误。
2. OFFSET在数组运算中的局限性：OFFSET属于引用类函数，在数组环境中无法正确对应原二维数据的位置关系，扁平化后的数组已丢失原B、C列的配对关联。

解决方案 #

保留姓名与对应TRUE/FALSE的配对关系，改用二维数组处理：

列D公式（统计对应TRUE的姓名次数） #

=LET(
    _getDate, LAMBDA(d, TEXT(DAY(d),"00")&"/"&TEXT(MONTH(d),"00")&"/"&YEAR(d)),
    // 读取所有每日表的B3:C区域，保留二维结构
    Γ, REDUCE("", SEQUENCE(J3-I3,1,0), LAMBDA(a,d, VSTACK(a, IFERROR(INDIRECT(_getDate(d+I3)&"!B3:C"), "")))),
    // 过滤空行
    Λ, FILTER(Γ, INDEX(Γ,,1)<>""),
    // 对每个姓名统计对应C列为TRUE的次数
    MAP(B3:B, LAMBDA(name, IF(name="",, COUNTA(FILTER(INDEX(Λ,,1), INDEX(Λ,,1)=name, INDEX(Λ,,2)=TRUE)))))
)

优化说明 #

• 使用REDUCE+VSTACK替代MAP+TOROW+TOCOL，保留了原数据中姓名（B列）与状态（C列）的二维配对关系，避免扁平化导致的关联丢失。
• 通过INDEX(Λ,,1)和INDEX(Λ,,2)分别提取姓名列和状态列，精准匹配姓名对应的TRUE条件。
• 逻辑更清晰，彻底避免OFFSET在数组中位置错乱的问题。

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