如何在表格中实现按键限制数量的前N条高值记录展示？

我需要从大量键值对数据中提取前N个最高值，但要限制每个键最多显示其前M个高值。当前用LARGE和INDEX/MATCH的方法会导致单个键占满所有结果，无法满足每个键最多M条的限制。

比如当N=5、M=2时，原始数据：

期望得到：

解决方案（Excel 365/2021 及以后版本，推荐） #

利用动态数组函数可以一步生成结果，无需下拉填充，公式更简洁易维护：

假设原始数据在Values工作表的L3:L999（Key列）和K3:K999（Value列），在目标单元格输入以下公式：

=LET(
    key_range, Values!$L$3:$L$999,
    val_range, Values!$K$3:$K$999,
    N, 20,    -- 要提取的总条目数
    M, 5,     -- 每个键最多显示的条目数
    -- 计算每个值在对应Key中的降序排名
    ranks, BYROW(val_range, LAMBDA(v, COUNTIFS(key_range, INDEX(key_range, ROW()-ROW(key_range)+1), val_range, ">"&v)+1)),
    -- 筛选出排名≤M的条目，合并Key和Value
    filtered, FILTER(HSTACK(key_range, val_range), ranks<=M),
    -- 按Value降序排序
    sorted, SORT(filtered, 2, -1),
    -- 提取前N个结果
    TAKE(sorted, N)
)

公式说明： #

1. LET函数：定义变量，让公式结构更清晰，方便修改N和M的值（也可以改成引用单元格，比如N=A1）。
2. ranks计算：通过COUNTIFS统计同一Key中比当前值大的数量，加1得到该值在对应Key中的降序排名。
3. filtered筛选：保留所有排名不超过M的键值对。
4. sorted排序：将筛选后的结果按Value从高到低排序。
5. TAKE提取：取排序后的前N个条目，直接生成结果表格。

解决方案（旧版Excel，无动态数组） #

如果使用的是不支持动态数组的Excel版本，需要用数组公式分两步实现：

1. 提取符合条件的Value列 #

在目标Value列的第一个单元格（比如J4）输入以下数组公式，按Ctrl+Shift+Enter确认，然后下拉填充到第N行：

=LARGE(IF(COUNTIFS(Values!$L$3:$L$999, Values!$L$3:$L$999, Values!$K$3:$K$999, ">"&Values!$K$3:$K$999)+1<=5, Values!$K$3:$K$999), ROW(J4)-ROW(J$3))

2. 匹配对应的Key列 #

在目标Key列的第一个单元格（比如K4）输入以下数组公式，按Ctrl+Shift+Enter确认，然后下拉填充：

=INDEX(Values!$L$3:$L$999, MATCH(1, (Values!$K$3:$K$999=J4)*(COUNTIFS(Values!$L$3:$L$999, Values!$L$3:$L$999, Values!$K$3:$K$999, ">"&Values!$K$3:$K$999)+1<=5)*(COUNTIF($K$3:K3, Values!$L$3:$L$999)<5), 0))

公式说明： #

• Value列公式：先用IF筛选出同一Key中排名≤M的所有值，再用LARGE依次提取第1到第N个高值。
• Key列公式：通过MATCH同时匹配三个条件：值等于当前Value、该值在对应Key中的排名≤M、该Key在已生成的结果中出现次数小于M，确保每个Key最多显示M次。

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