在日常数据处理中，我们经常会遇到这样的需求：在特定分组内，按照某个指标对数据进行排序。比如，在同一年份、同一地市的路段中，根据 PQI（路面技术状况指数）进行排名。这种 “有限制条件下的排序”，用简单的 RANK 函数往往力不从心，而今天我们要拆解的 SUMPRODUCT 公式，正是解决这类问题的一把利器。
一、场景痛点：为什么简单公式不够用？
先看我们的示例数据：这是一份公路路况检测表，包含 地市（B 列）、年份（J 列）、PQI 指数（K 列）等关键信息。我们的目标是：在同一地市、同一年份的所有路段中，根据 PQI 值从低到高进行排序。
如果直接使用=RANK(K2, $K$2:$K$717)，会得到一个全局排名，这显然不符合我们 “分组内排名” 的要求。而如果使用RANK.EQ配合条件筛选，又会遇到重复值处理和公式扩展性差的问题。
这时候，我们就需要一个更强大的工具 —SUMPRODUCT 函数，它能灵活地组合多个条件，实现精准的分组排序。
二、请豆包编辑公式，如下所示：
=SUMPRODUCT(($B$2:$B$717=B2)*($J$2:$J$717=J2)*($K$2:$K$717<=K2)/COUNTIFS($B$2:$B$717,$B$2:$B$717,$J$2:$J$717,$J$2:$J$717,$K$2:$K$717,$K$2:$K$717))
三、公式拆解：看懂每一部分的逻辑
1. 条件筛选：锁定分组范围
($B$2:$B$717=B2)*($J$2:$J$717=J2)
$B$2:$B$717=B2：筛选出与当前行（B2）地市相同的所有行。
$J$2:$J$717=J2：筛选出与当前行（J2）年份相同的所有行。
*：在 Excel 中，TRUE 代表 1，FALSE 代表 0。两个条件相乘，就相当于逻辑 “与”，只有同时满足 “同地市” 和 “同年份” 的行，结果才为 1，否则为 0。
2. 排名依据：统计符合条件的数量
($K$2:$K$717<=K2)
这部分筛选出在上述分组内，**PQI 值小于或等于当前行（K2）** 的所有行。结合前面的条件，这三部分相乘的结果，就是在 “同地市、同年份” 这个分组内，PQI 值小于或等于当前行的记录总数。
3. 去重处理：解决重复值问题
/COUNTIFS($B$2:$B$717,$B$2:$B$717,$J$2:$J$717,$J$2:$J$717,$K$2:$K$717,$K$2:$K$717)
这是公式中最精妙的一步。COUNTIFS函数在这里的作用是，统计每一行在 “同地市、同年份、同 PQI” 这个组合下出现的次数。
用前面统计的总数，除以这个出现次数，就能巧妙地处理重复值。例如，如果有 3 条记录的 PQI 值都是 88.79，它们的排名就会是相同的，而不是依次递增。
四、实战演示：从数据到结果
让我们用截图中的数据来验证一下公式的效果：
行 2-3：地市为 “地市 1”，年份为 “2027”，PQI 值为 88.79。
公式会先锁定 “地市 1+2027” 这个分组。
在这个分组内，PQI 值小于或等于 88.79 的记录有 3 条（85.01、87.94、88.79）。
由于 88.79 出现了多次，通过除以重复次数，最终排名为3。
行 5-6：地市为 “地市 1”，年份为 “2027”，PQI 值为 85.01。
在 “地市 1+2027” 分组内，PQI 值小于或等于 85.01 的记录只有 1 条。
因此，这些行的排名都是1，符合我们的预期。
可以看到，这个公式完美地实现了 “在同一地市、同一年份内，根据 PQI 值进行排名” 的需求，并且优雅地处理了重复值的问题。
最终结果如下图所示。
提醒一下，输入公式后要按数组形式进行计算，即按住ctrl+shift+回车。
五、举一反三：公式的灵活应用
这个公式的强大之处在于它的可扩展性。我们可以根据实际需求，轻松修改条件，实现不同的排序逻辑：
增加分组条件：如果需要在 “地市 + 年份 + 技术等级” 的分组内排序，只需在条件部分增加($G$2:$G$717=G2)即可。
改变排序方向：如果要从高到低排名，只需将($K$2:$K$717<=K2)改为($K$2:$K$717>=K2)。
处理其他数据类型：这个公式不仅适用于数字，也可以用于文本或日期的排序，只需将 K 列替换为相应的列即可。
六、注意事项：让公式跑得又快又稳
在使用这个公式时，有几点需要特别注意：
绝对引用：公式中的$B$2:$B$717使用了绝对引用，这是为了确保在向下填充公式时，引用的范围不会发生变化。如果忘记加$，公式就会出错。
数据范围：确保公式中的数据范围（如$B$2:$B$717）覆盖了所有需要处理的数据。如果后续添加了新行，需要手动更新范围，或者使用动态数组（如B:B），但要注意性能问题。
性能优化：SUMPRODUCT 是一个数组函数，在处理大量数据时可能会比较慢。如果数据量非常大（超过 1 万行），可以考虑使用 Power Query 或数据透视表来实现类似的功能。
七、总结：掌握核心逻辑，告别公式恐惧
Excel 公式看似复杂，但其背后的逻辑往往是清晰的。今天我们拆解的这个 SUMPRODUCT 公式，核心思想就是：先锁定分组，再统计符合条件的记录数，最后处理重复值。
掌握了这个思路，你就不再是只会复制粘贴公式的 “工具人”，而是能够根据自己的需求，灵活构建解决方案的数据处理高手，这种 “多条件分组排序” 思维，会让你事半功倍。
