如何结合日期筛选，用INDEX/MATCH/LARGE提取指定周的Top3人名

假设你的表格满足以下前提：

• 表格首列（示例为A列）为周次对应的标识（如周次编号、日期区间），指定日期输入在单元格A1
• G3:L3为人员姓名列标题，G4:Lxx区域为各周次对应的数值行

方案1：Excel 365/2021 动态数组版 #

若你的Excel支持动态数组函数，可实现自动提取Top3所有姓名（无需下拉填充）：

=TAKE(SORTBY(G3:L3,XLOOKUP(WEEKNUM(A1),A4:Axx,G4:Lxx,""),-1),3)

如果需要按B8指定的位次单独提取（如B8=2时取第2名）：

=INDEX(SORTBY(G3:L3,XLOOKUP(WEEKNUM(A1),A4:Axx,G4:Lxx,""),-1),B8)

参数说明 #

• WEEKNUM(A1)：将指定日期转换为对应周次，可通过添加第2参数调整周起始规则（如WEEKNUM(A1,2)代表周一为周起始）
• XLOOKUP(WEEKNUM(A1),A4:Axx,G4:Lxx,"")：根据周次匹配到对应行的数值区域
• SORTBY(G3:L3,...,-1)：按数值降序排列姓名
• TAKE(...,3)：提取前3个结果；INDEX(...,B8)：提取指定位次的结果

方案2：兼容旧版Excel的公式 #

若你的Excel不支持动态数组，可结合传统函数实现需求：

=INDEX(G3:L3,MATCH(LARGE(INDEX(G4:Lxx,MATCH(WEEKNUM(A1),A4:Axx,0),0),B8),INDEX(G4:Lxx,MATCH(WEEKNUM(A1),A4:Axx,0),0),0))

参数说明 #

• MATCH(WEEKNUM(A1),A4:Axx,0)：定位指定日期对应周次所在的行号
• INDEX(G4:Lxx,行号,0)：提取该行的所有数值
• LARGE(...,B8)：获取该行第B8大的数值
• 外层INDEX(G3:L3,MATCH(...))：匹配对应姓名

注意事项 #

• 若存在相同数值的并列情况，上述公式会返回第一个匹配到的姓名；如需显示所有并列人员，需额外调整逻辑
• 确保周次列（A4:Axx）的周次格式与WEEKNUM返回值一致，若A列为日期区间文本（如"2024/1/1-2024/1/7"），可改用MATCH(TRUE,ISNUMBER(SEARCH(TEXT(A1,"yyyy/mm/dd"),A4:Axx)),0)定位行号

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