Excel 2019如何通过公式按通配符多条件提取排名前N结果

已查阅所有可获取的相关问题资源仍未找到解决方案，因此向社区求助：
需要对规模超15000行的数据集生成报表，提取按指定列数值降序排列的前25个最大值，提取时除数值列排序规则外，还需满足多类附加筛选条件。目前已实现带部分筛选条件的Top N取值数组公式，具体如下：

{=LARGE(IF('IMPORTED DATA'!$X$4:$X$1048576 = IF('Data Cleanup'!$AX$3 = 1, "Gaming Designed", "Not Gaming Designed"), 'IMPORTED DATA'!$BH$4:$BH$1048576), ROW(A2) - ROW(A$1))}

现存问题 #

新增通配符筛选条件时公式失效：尝试通过COUNTIF方法规避IF语句不支持通配符匹配的问题，但编写的公式中COUNTIF部分始终返回真值，附加筛选条件未生效，当前测试公式如下：

{=LARGE(IF(COUNTIF('IMPORTED DATA'!$P$6:$P$1048576, IF('Data Cleanup'!$AX$3 = 1, "?????", "????")) * ('IMPORTED DATA'!$X$6:$X$1048576 = IF('Data Cleanup'!$AX$3 = 1, "Gaming Designed", "Not Gaming Designed")) * ('IMPORTED DATA'!$E$6:$E$1048576 <> "All Other (Suppressed)"), 'IMPORTED DATA'!$BH$6:$BH$1048576), ROW(A2) - ROW(A$1))}

已知约束 #

• 使用环境为Excel 2019，该版本不支持FILTER函数，无法使用该函数简化实现
• 使用通配符的原因是通过字符长度区分分类：低于100Hz的对应内容为4个字符，高于100Hz的对应内容为5个字符
• 提取Top N数值后还需要获取对应行的其他列数据，因此方案需要兼容MATCH匹配逻辑，支持相同筛选条件下的行信息查找

1、公式失效根因 #

原有COUNTIF写法未实现逐行匹配：COUNTIF第一参数直接传入整列范围时，会返回整个范围内匹配通配符的单元格总个数，只要范围内存在符合长度要求的内容，返回结果就为大于0的数值，在数组运算中会被判定为逻辑真，导致筛选条件完全失效。

2、修正后的Top N取值公式（数组公式，输入后按Ctrl+Shift+Enter确认） #

无需使用COUNTIF做通配符匹配，既然筛选逻辑为判断字符长度，直接用LEN函数逐行计算P列内容长度即可，运算效率高于通配符匹配，也不会出现整列统计的逻辑错误：

{=LARGE(IF(
(LEN('IMPORTED DATA'!$P$6:$P$16000)=IF('Data Cleanup'!$AX$3=1,5,4))
*('IMPORTED DATA'!$X$6:$X$16000=IF('Data Cleanup'!$AX$3=1,"Gaming Designed","Not Gaming Designed"))
*('IMPORTED DATA'!$E$6:$E$16000<>"All Other (Suppressed)"),
'IMPORTED DATA'!$BH$6:$BH$16000),
ROW(A2)-ROW(A$1))}

注意：将公式中的行上限从1048576调整为数据集实际最大行即可（示例中写为16000，可按实际数据量调整），全列引用会大幅降低数组公式运算速度，15000行数据量下缩小引用范围后计算效率会有明显提升。

3、匹配对应行其他列数据的公式（兼容MATCH逻辑，数组公式） #

提取到Top N数值后，通过INDEX+MATCH多条件匹配即可取到对应行的其他字段，额外增加计数逻辑处理数值重复场景，避免匹配到错误行：

{=INDEX('IMPORTED DATA'!B:B,MATCH(1,
(LEN('IMPORTED DATA'!$P$6:$P$16000)=IF('Data Cleanup'!$AX$3=1,5,4))
*('IMPORTED DATA'!$X$6:$X$16000=IF('Data Cleanup'!$AX$3=1,"Gaming Designed","Not Gaming Designed"))
*('IMPORTED DATA'!$E$6:$E$16000<>"All Other (Suppressed)")
*('IMPORTED DATA'!$BH$6:$BH$16000=B2)
*(COUNTIF(B$2:B2,B2)=COUNTIFS($B$2:B2,B2,'IMPORTED DATA'!$BH$6:$BH$16000,B2)),
0))}

公式中B2为已提取到的Top N数值所在单元格，'IMPORTED DATA'!B:B替换为需要提取的目标列即可。

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