Google Sheets跨表获取指定F2值对应B49值及求和需求

1. 获取F2=1时B49的值 #

假设选手原始数据存储在RawData工作表中（A列为学校编号，B-E列对应「SchoolWiseList」表的B-E选手信息），可在「ContactPerson」工作表中使用以下公式计算目标值：

=LET(
  target_school, 1,
  // 筛选目标学校的选手数据
  school_data, FILTER(RawData!B:E, RawData!A=target_school),
  // 模拟「SchoolWiseList」表A列的编号生成逻辑
  generate_ids, LAMBDA(data,
    SCAN("", SEQUENCE(ROWS(data)), LAMBDA(acc, row,
      LET(
        current_row, INDEX(data, row, ),
        IF(COUNTA(current_row)=0, "",
          IFERROR(
            XLOOKUP(1, (INDEX(data, SEQUENCE(row-1), 1)=INDEX(current_row,1))*(INDEX(data, SEQUENCE(row-1), 3)=INDEX(current_row,3)), acc, , 0),
            IFERROR(MAX(acc), 0)+1
          )
        )
      )
    )
  ),
  // 取编号最大值（对应B49的结果）
  IFERROR(MAX(generate_ids(school_data)), 0)
)

如果「SchoolWiseList」表的B-E列数据来自其他筛选逻辑，请将school_data部分替换为对应的QUERY或FILTER公式。

2. 计算F2从1到11时所有B49值的总和 #

使用BYROW遍历1-11的学校编号，逐个计算对应B49值后求和：

=SUM(BYROW(SEQUENCE(11), LAMBDA(x,
  LET(
    school_data, FILTER(RawData!B:E, RawData!A=x),
    generate_ids, LAMBDA(data,
      SCAN("", SEQUENCE(ROWS(data)), LAMBDA(acc, row,
        LET(
          current_row, INDEX(data, row, ),
          IF(COUNTA(current_row)=0, "",
            IFERROR(
              XLOOKUP(1, (INDEX(data, SEQUENCE(row-1), 1)=INDEX(current_row,1))*(INDEX(data, SEQUENCE(row-1), 3)=INDEX(current_row,3)), acc, , 0),
              IFERROR(MAX(acc), 0)+1
            )
          )
        )
      )
    ),
    IFERROR(MAX(generate_ids(school_data)), 0)
  )
)))

简化场景说明 #

若「SchoolWiseList」表A列的公式是生成选手唯一编号（而非按学校+类别分组的编号），可将generate_ids逻辑简化为：

generate_ids, LAMBDA(data,
  SCAN(0, SEQUENCE(ROWS(data)), LAMBDA(acc, row,
    IF(COUNTA(INDEX(data, row, ))>0, acc+1, "")
  ))
)

内容的提问来源于stack exchange，提问作者Kurumba Daspara F P School