如何在Google Sheets分数数据库中查找最接近突破5000分的最优分数

规则对齐 #

• 筛选范围：仅保留C2:C937中数值<5000的条目
• 排序优先级：第一优先级为突破5000后的富余空间（即D列值-5000）从大到小排序；第二优先级为C列当前分数从大到小排序，保证同富余空间下更接近5000分的条目靠前

公式方案 #

Excel 365/2021 及以上版本（支持动态数组） #

直接在空白单元格输入以下公式，会自动溢出所有符合要求的C/D/E三列内容：

=SORT(FILTER(C2:E937,C2:C937<5000),{2,1},{-1,-1})

如果只需要返回对应条目的D列记录，使用以下公式：

=INDEX(SORT(FILTER(C2:E937,C2:C937<5000),{2,1},{-1,-1}),0,2)

公式解释

• FILTER(C2:E937,C2:C937<5000)：先过滤掉所有C列≥5000的无效条目
• SORT(...,{2,1},{-1,-1})：第一排序键为D列（突破后分数）降序，保证富余空间更大的条目靠前；第二排序键为C列（当前分数）降序，保证同富余空间下更接近5000分的条目靠前

Excel 2019及以下旧版本（不支持动态数组） #

需要使用数组公式，输入完成后按Ctrl+Shift+Enter确认生效。假设你需要在G列输出排序后的D列记录，在G2单元格输入以下公式，下拉填充即可：

=IFERROR(INDEX(D:D,MODE(IF(C$2:C$937<5000,IF(COUNTIF(G$1:G1,D$2:D$937)=0,MATCH(D$2:D$937+0.000001*C$2:C$937,D$2:D$937+0.000001*C$2:C$937,0))))),"")

公式说明

通过D列值+0.000001*C列值构造唯一权重值，同时满足D越大优先级越高、D相同C越大优先级越高的排序规则，避免重复取值。 #

原有公式问题说明 #

1. 硬编码取LARGE函数的前3个值，没有覆盖所有<5000的C列条目，符合条件的条目超过3个时会直接漏判
2. INDIRECT+MATCH匹配E列的逻辑未考虑E列重复值场景，大概率出现匹配错误

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