Google Sheets 如何自动定位当前周列获取最大值对应团队成员姓名

前置定位说明 #

先确认4个表格固定区域，写公式时直接对应替换即可：

• 当前周计算单元格：放置=TODAY()-WEEKDAY(TODAY(),3)公式的单元格，记得加绝对引用锁死位置，比如放在A1就写$A$1
• 周表头行范围：存放所有周起始日期的行，比如示例中4/12/21、4/19/21在第7行，从B列开始存表头就写B7:7
• 全量数据范围：覆盖所有成员、所有周可用率的单元格区域，比如成员从第8行开始，预留到Z列存后续周数据就写B8:Z
• 成员名称范围：存放所有团队成员姓名的列，比如成员从A8开始存就写A8:A

可直接套用的核心公式 #

=INDEX(成员名称范围,
 MATCH(
  MAX(INDEX(全量数据范围,0,MATCH(当前周计算单元格,周表头行范围,0))),
  INDEX(全量数据范围,0,MATCH(当前周计算单元格,周表头行范围,0)),
  0
 )
)

对应示例场景的实写公式 #

按照给出的表格结构套用后，实际可用公式为：

=INDEX(A8:A,
 MATCH(
  MAX(INDEX(B8:Z,0,MATCH($A$1,B7:7,0))),
  INDEX(B8:Z,0,MATCH($A$1,B7:7,0)),
  0
 )
)

公式运行逻辑 #

• 第一层MATCH($A$1,B7:7,0)：精准匹配当前周值在周表头行的列位置，返回对应列在全量数据范围里的序号
• INDEX(全量数据范围,0,匹配序号)：自动抓取当前周对应的整列可用率数据，完全替代手动选列的操作
• 剩余逻辑和原有手动公式一致：定位当前周列的最大值位置，匹配对应行的成员姓名

常见场景适配 #

• 并列最高值处理：如果需要返回所有和最大值持平的成员，可替换为过滤型公式，返回结果会用顿号分隔：

=TEXTJOIN("、",TRUE,
 FILTER(A8:A, INDEX(B8:Z,0,MATCH($A$1,B7:7,0)) = MAX(INDEX(B8:Z,0,MATCH($A$1,B7:7,0)))
 )
)

• 新增周列适配：只要把新的周起始日期填到对应表头行的末尾，公式会自动把新列纳入匹配范围，不需要反复修改列范围

注意事项：要确保周表头的日期和当前周单元格计算出的日期格式完全统一（同为日期序列值，不要出现文本格式的日期），否则会出现匹配失败的问题。

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