Excel如何修改现有IF公式实现仅返回前9%匹配行并隐藏其他行

核心逻辑 #

在原有等级匹配规则的基础上，新增符合条件行的排名判定，仅前270行（总数据的9%）返回A/B/C标签，其余行返回空值，配合列筛选即可直接隐藏不符合要求的行，无需使用数据透视表。

修改后公式（默认按O列分值从高到低排序，可自行替换排序依据） #

=IF(AND(OR(AND(H2=1,O2>=58),AND(H2=2,O2>=58),AND(H2=5,O2>=55)),RANK.EQ(O2,IF(OR(AND(H:H=1,O:O>=58),AND(H:H=2,O:O>=58),AND(H:H=5,O:O>=55)),O:O,""),0)<=270),IF(AND(H2=1,O2>=58),"A",IF(AND(H2=2,O2>=58),"B",IF(AND(H2=5,O2>=55),"C",""))),"")

操作步骤 #

• 将上述公式替换你原有等级列的第一个单元格公式（如原公式在P2则粘贴到P2）
• 下拉公式填充至所有3000行对应单元格
• 点击等级列的筛选按钮，仅勾选A、B、C三个选项后确认，即可自动隐藏其余行，保留符合要求的前270行数据

调整说明 #

• 若你排序用的参考列不是O列，将公式中RANK.EQ的第一个参数、以及IF判断里的O:O替换为实际排序列即可
• 若后续数据总量变化，可将公式中的270替换为ROUND(COUNTA(A:A)*0.09,0)，即可自动按9%比例计算匹配行数，无需手动修改数值
• 2019及更早版本的Excel输入公式后，需要按Ctrl+Shift+Enter触发数组计算，否则会返回错误值

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