获取不同细分项Top5成本值的Google Sheets公式

问题场景 #

现有Google Sheets表格包含4列（A、B、ID、Cost），数据按A列（如France、India）分组，需要提取每组中Cost列的前5个最大值对应的整行数据。使用QUERY+LIMIT无法实现分组分段限制，以下是可行解决方案。

原始数据 #

**A**  **B**  **ID**    **Cost**   
France  101 1   2,038.67
France  101 2   1,067.87
France  101 3   884.08
France  101 4   872.92
France  101 5   842.53
France  101 6   717.75
France  101 7   409.96
France  101 8   374.00
France  101 9   280.97
France  101 10  232.74
France  101 11  217.16
France  101 12  5.09
India   102 13  52,113.35
India   102 14  34,213.21
India   102 15  24,332.05
India   102 16  23,824.82
India   102 17  21,239.98
India   102 18  18,528.92
India   102 19  12,207.84
India   102 20  11,992.45
India   102 21  10,705.70
India   102 22  6,799.04
India   102 23  6,625.35
India   102 24  6,495.47
India   102 25  6,410.21

预期输出 #

France  101 1   2,038.67
France  101 2   1,067.87
France  101 3   884.08
France  101 4   872.92
France  101 5   842.53
India   102 13  52,113.35
India   102 14  34,213.21
India   102 15  24,332.05
India   102 16  23,824.82
India   102 17  21,239.98

解决方案 #

方案1：使用FILTER+COUNTIFS实现 #

假设数据区域为A2:D26（表头在A1:D1），输入以下公式：

=FILTER(A2:D26, COUNTIFS(A2:A26, A2:A26, D2:D26, ">="&D2:D26) <= 5)

逻辑说明：

• COUNTIFS(A2:A26, A2:A26, D2:D26, ">="&D2:D26)：统计当前行所属A组中，Cost大于等于当前行Cost的行数，即当前行在组内的降序排名。
• 筛选排名≤5的行，即可得到每组Cost前5大的记录。

方案2：使用QUERY+BYROW+UNIQUE实现 #

如果需要更灵活的分组控制，可使用LET函数整合逻辑：

=LET(
  分组列表, UNIQUE(A2:A26),
  分组结果, BYROW(分组列表, LAMBDA(组, QUERY(A2:D26, "SELECT * WHERE A='"&组&"' ORDER BY D DESC LIMIT 5"))),
  VSTACK(IFERROR(INDEX(分组结果,1),""), TOCOL(分组结果,1))
)

逻辑说明：

1. UNIQUE(A2:A26)：提取A列所有唯一分组值。
2. BYROW遍历每个分组，用QUERY筛选该组数据，按Cost降序排序后取前5行。
3. VSTACK+TOCOL将所有分组的结果合并为一个连续区域。

注意事项 #

• 确保Cost列为数值格式，若当前是带逗号的文本格式，可先用VALUE(SUBSTITUTE(D2:D26,",",""))转换为数值后再进行排名筛选。

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