Excel 2016：按组筛选后用单公式计算区域求和排名

现有一张7000多行的Excel表格，结构如下：
| Group 1 | Group 2 | Group 3 | Group 4 | Region | Value |

针对指定筛选组合（示例：Region=1、Group1=3、Group2=2），已算出两个关键值：

• 目标区域求和值(A)：
  (A) = SUMIFS(Table[Value];Table[Region];1;Table[Group1];3;Table[Group2];2)
• 权重值(B)：
  (B) = (A) / SUMIFS(Table[Value];Table[Group1];3;Table[Group2];2)

需求是在Excel 2016中用单个公式，计算该区域在相同组筛选条件（Group1=3、Group2=2）下的求和排名。

由于Excel 2016无动态数组函数支持，可通过SUMPRODUCT结合SUMIFS实现单公式排名，针对示例条件的公式如下：

=SUMPRODUCT(--(SUMIFS(Table[Value],Table[Group1],3,Table[Group2],2,Table[Region],UNIQUE(Table[Region]))>SUMIFS(Table[Value],Table[Region],1,Table[Group1],3,Table[Group2],2)))+1

公式解析 #

1. UNIQUE(Table[Region])：提取表格中所有不重复的Region值（注：若你的Excel 2016未启用UNIQUE函数，可使用下方兼容版公式）
2. 内层SUMIFS：计算每个符合Group1=3、Group2=2条件的Region对应的Value总和
3. --(...)>...：将“求和值大于目标区域总和”的判断结果转为1或0，统计这类Region的数量
4. 最后加1，得到目标区域的排名（规则：求和值越大，排名越靠前）

兼容无UNIQUE函数的Excel 2016版本

如果你的Excel 2016不支持UNIQUE，可用以下公式避免重复统计：

=SUMPRODUCT(--(SUMIFS(Table[Value],Table[Group1],3,Table[Group2],2,Table[Region],Table[Region])/COUNTIFS(Table[Region],Table[Region],Table[Group1],3,Table[Group2],2)>SUMIFS(Table[Value],Table[Region],1,Table[Group1],3,Table[Group2],2)))+1

这里通过COUNTIFS对重复Region的求和值做去重处理，确保每个Region只被计算一次。

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