Excel动态提取Top10账户：基于MATCH确定SORTBY查找数组的问题

核心思路 #

通过MATCH定位目标数据列，用INDEX获取对应列的数值区域，结合SORTBY+TAKE（或旧版兼容公式）实现动态Top10提取，无需拼接地址字符串。

差异不利Top10（数值降序取前10） #

适用于Excel 365/2021及以上版本：

=TAKE(SORTBY('Sheet 2'!$D$14:$D$119,INDEX('Sheet 2'!$G$14:$AN$119,,MATCH(C5&D7&C4&E8,'Sheet 2'!$G$4:$AN$4,0)),-1),10)

公式说明 #

• MATCH(C5&D7&C4&E8,'Sheet 2'!$G$4:$AN$4,0)：根据拼接的条件值，定位目标数据列在第4行的位置
• INDEX('Sheet 2'!$G$14:$AN$119,,定位结果)：提取目标列中14-119行的数值区域
• SORTBY('Sheet 2'!$D$14:$D$119, 数值区域, -1)：按目标列数值降序排序账户名称（D列）
• TAKE(...,10)：截取排序后的前10条结果

差异有利Top10（数值升序取前10） #

适用于Excel 365/2021及以上版本：

=TAKE(SORTBY('Sheet 2'!$D$14:$D$119,INDEX('Sheet 2'!$G$14:$AN$119,,MATCH(C5&D7&C4&E8,'Sheet 2'!$G$4:$AN$4,0)),1),10)

公式说明 #

仅将排序参数改为1（升序），其余逻辑与不利Top10一致。

旧版Excel兼容方案（无TAKE/SORTBY） #

差异不利Top10 #

=INDEX('Sheet 2'!$D$14:$D$119,SMALL(IF(INDEX('Sheet 2'!$G$14:$AN$119,,MATCH(C5&D7&C4&E8,'Sheet 2'!$G$4:$AN$4,0))=LARGE(INDEX('Sheet 2'!$G$14:$AN$119,,MATCH(C5&D7&C4&E8,'Sheet 2'!$G$4:$AN$4,0)),ROW(1:10)),ROW($1:$106),""),ROW(1:10)))

注：此为数组公式，需按Ctrl+Shift+Enter确认生效（Excel 2019及更早版本）

差异有利Top10 #

=INDEX('Sheet 2'!$D$14:$D$119,SMALL(IF(INDEX('Sheet 2'!$G$14:$AN$119,,MATCH(C5&D7&C4&E8,'Sheet 2'!$G$4:$AN$4,0))=SMALL(INDEX('Sheet 2'!$G$14:$AN$119,,MATCH(C5&D7&C4&E8,'Sheet 2'!$G$4:$AN$4,0)),ROW(1:10)),ROW($1:$106),""),ROW(1:10)))

注：同样需按Ctrl+Shift+Enter确认生效

原公式错误原因 #

• 第一个公式用字符串拼接单元格地址，SORTBY无法识别字符串形式的区域引用，必须使用INDEX这类返回实际单元格区域的函数
• 第二个公式的FILTER参数逻辑错误，MATCH返回的是列位置，不能直接作为筛选条件

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