横向数据集连续3周及以上未标注HR员工统计公式适配问题

问题背景 #

我有一份包含员工姓名的横向数据集，HR需要为员工添加备注。原本使用公式从右向左统计连续3周及以上未被HR添加备注的员工，但现在数据集格式变更：最新周的数据插入在性别列之后，所有周数据按降序横向排列，原公式无法适配新格式，求修改后的公式。

原公式：

=let(range_,A3:12,row_,A3:B12,headr_,A2:2, col_,filter(headr_,right(headr_,5)="Notes"),data_,filter(range_,right(headr_,5)="Notes"), Σ,choosecols(makearray(rows(row_),columns(col_),lambda(r,c,if(left(index(col_,,c),3)="HR1",if(or(len(index(data_,r,c)),len(index(data_,r,c+1))),1,),))),sequence(counta(col_)/2,1,1,2)), let(Λ,index((counta(col_)/2)-byrow(Σ,lambda(z_,ifna(xmatch(1,z_,,-1))))),filter({row_,Λ,vlookup(index(row_,,1),{let(Σ,xmatch("HR1*",headr_,2,-1),choosecols(range_,1,Σ-2,Σ-1))},{2,3},)},Λ>2)))

适配新格式的公式 #

针对新格式（最新周数据在性别列后，周数据降序排列），修改后的公式如下：

=LET(
    range_, A3:B12,
    headr_, A2:2,
    // 筛选所有Notes列的表头和对应数据
    col_notes, FILTER(headr_, RIGHT(headr_, 5) = "Notes"),
    data_notes, FILTER(A3:12, RIGHT(headr_, 5) = "Notes"),
    // 按周分组，标记该周是否有HR备注（1=有，0=无）
    week_flags, MAKEARRAY(ROWS(range_), COUNTA(col_notes)/2, LAMBDA(r, w,
        LET(
            col_idx, (w-1)*2 + 1,
            hr1_note, INDEX(data_notes, r, col_idx),
            hr2_note, INDEX(data_notes, r, col_idx+1),
            IF(OR(LEN(hr1_note), LEN(hr2_note)), 1, 0)
        )
    )),
    // 统计从最新周开始的连续未备注周数
    consecutive_blank, BYROW(week_flags, LAMBDA(row,
        LET(
            first_note_pos, XMATCH(1, row, 0, 1),
            IFNA(ROWS(row) - first_note_pos + 1, ROWS(row))
        )
    )),
    // 筛选连续未备注≥3周的员工并整理结果
    result, FILTER(HSTACK(range_, consecutive_blank), consecutive_blank > 2),
    // 添加结果表头
    VSTACK({"姓名", "性别", "连续未备注周数"}, result)
)

公式逻辑说明 #

1. 基础范围定义：range_锁定员工姓名和性别列，headr_提取表头行用于识别备注列。
2. 备注列筛选：自动提取所有后缀为"Notes"的列，对应每个周的HR1、HR2备注数据。
3. 周备注状态标记：将每两周（HR1+HR2）视为一个周单元，只要其中任意一个HR有备注，就标记该周为"已备注（1）"，否则标记为"未备注（0）"。
4. 连续未周数统计：从最新周（最左侧）开始查找第一个已备注的周，计算之后连续未备注的周数；若所有周都无备注，则直接取总周数。
5. 结果筛选与整理：仅保留连续未备注超过2周（即≥3周）的员工，组合成带表头的结果表。

使用方法 #

将公式粘贴到新格式数据集的空白单元格（如D14），即可自动生成符合要求的统计结果。

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