如何在Excel中对动态数组按账户分组求和生成Top N列表

方案一：使用GROUPBY函数（Excel 365 2022及以后版本） #

直接利用GROUPBY实现分组求和+过滤，再排序取Top N：

=INDEX(SORT(GROUPBY(EXAMPLE1[ACCOUNT NAME],EXAMPLE1[QTY],SUM,,(EXAMPLE1[DEALER]=$I$1)*(EXAMPLE1[YEAR]=$I$2)*(EXAMPLE1[MONTH]=$I$3)),2,-1),SEQUENCE($I$4),{1,2})

公式拆解：

1. GROUPBY(EXAMPLE1[ACCOUNT NAME],EXAMPLE1[QTY],SUM,,(EXAMPLE1[DEALER]=$I$1)*(EXAMPLE1[YEAR]=$I$2)*(EXAMPLE1[MONTH]=$I$3))：按账户名分组，对QTY列求和，同时只保留符合I1（经销商）、I2（年份）、I3（月份）条件的行。
2. SORT(...,2,-1)：按分组后的求和数量（第2列）降序排序。
3. INDEX(...,SEQUENCE($I$4),{1,2})：提取前I4行的账户名（第1列）和汇总数量（第2列）。

方案二：兼容旧版Excel 365（无GROUPBY函数） #

用LET整合逻辑，分步实现分组求和、排序、取Top N：

=LET(
    filteredAccounts, UNIQUE(FILTER(EXAMPLE1[ACCOUNT NAME],(EXAMPLE1[DEALER]=$I$1)*(EXAMPLE1[YEAR]=$I$2)*(EXAMPLE1[MONTH]=$I$3))),
    summedQTY, BYROW(filteredAccounts, LAMBDA(x, SUMIFS(EXAMPLE1[QTY],EXAMPLE1[DEALER],$I$1,EXAMPLE1[YEAR],$I$2,EXAMPLE1[MONTH],$I$3,EXAMPLE1[ACCOUNT NAME],x))),
    combinedArray, HSTACK(filteredAccounts, summedQTY),
    sortedArray, SORT(combinedArray,2,-1),
    INDEX(sortedArray,SEQUENCE($I$4),{1,2})
)

公式拆解：

1. filteredAccounts：获取符合条件的唯一账户名列表。
2. summedQTY：遍历每个账户名，用SUMIFS计算对应条件下的QTY总和。
3. combinedArray：将账户名和汇总数量合并为二维数组。
4. sortedArray：按汇总数量降序排序。
5. INDEX(...)：提取前I4行的目标列。

效果验证 #

使用上述任一公式后，将得到你需要的结果：

Bob's Boats        116
Marlin Sails Inc   85
Mike's Lemonade Stand   40
Labyrinth Walkways LLC  32
Direct Mailings LLC     16

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