Google Sheets公式需求：筛选指定条件的日班操作员名单

需求梳理 #

• Operator Qty工作表里，数据按每5列一组横向无限排列，每组对应一个操作员
• Dashboard的A1用=TEXT(EDATE(today(),-11),"MMM YYY")显示目标年月（比如May 2023）
• 要在Dashboard的A2写公式，筛选出同时满足两个条件的操作员：
  • 属于Day班次（班次定义在Operator Qty的第2行）
  • 该操作员的5列组里，目标年月对应的那一行至少有一个大于0的数值

问题根源 #

原来的公式只检查了操作员组内第一列的非零值，现在要改成覆盖整个5列组的检查。

公式解决办法 #

默认数据结构说明（结构不同可看后面的调整方法） #

默认Operator Qty的布局是：

• 第1行：每组的第一列是操作员名字（比如A1是操作员1，F1是操作员2，以此类推）
• 第2行：每组的第一列是班次（比如A2是Day，F2是Night，同组5列班次一致）
• A列：每一行对应一个年月（比如A2是May 2023，A3是Jun 2023）
• 目标年月那一行的横向单元格：对应各个操作员组的5个数值

最终公式（直接粘贴到Dashboard的A2） #

=LET(
    target_month, A1,
    target_row, IFERROR(MATCH(target_month, OperatorQty!$A:$A, 0), 0),
    group_start_cols, SEQUENCE(ROUNDUP(COLUMNS(OperatorQty!$1:$1)/5), 1, 1, 5),
    operator_names, INDEX(OperatorQty!$1:$1, group_start_cols),
    operator_shifts, INDEX(OperatorQty!$2:$2, group_start_cols),
    group_max_values, BYROW(group_start_cols, LAMBDA(col, MAX(INDEX(OperatorQty!$target_row:$target_row, col):INDEX(OperatorQty!$target_row:$target_row, col+4)))),
    FILTER(operator_names, operator_shifts="Day", group_max_values>0, target_row<>0)
)

公式逻辑说明 #

1. target_month：直接引用Dashboard A1的目标年月文本
2. target_row：在Operator Qty的A列查找目标年月对应的行号，加IFERROR避免无匹配时公式报错
3. group_start_cols：自动生成所有操作员组的起始列号（从第1列开始，每5列一组，适配横向无限延伸的结构）
4. operator_names：提取每组起始列的操作员名称
5. operator_shifts：提取每组起始列的班次（同组班次一致，无需检查全部5列）
6. group_max_values：对每个操作员组，计算目标年月行中5列的最大值——只要最大值大于0，就说明组内存在非零值
7. FILTER：最终筛选出符合条件的操作员：班次为Day、组内有非零值、且目标年月存在

特殊场景调整 #

• 如果Operator Qty的年月不在A列：把MATCH(target_month, OperatorQty!$A:$A, 0)里的$A:$A改成对应列（比如$B:$B）
• 如果操作员组从B列开始（A列为年月列）：把group_start_cols行改成SEQUENCE(ROUNDUP((COLUMNS(OperatorQty!$1:$1)-1)/5), 1, 2, 5)
• 如果Operator Qty里的年月是日期格式：把target_month改成DATEVALUE(A1&" 1")，确保匹配格式一致

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