如何使用QUERY函数提取求和Top5数值及其对应求和原因Top3

云原生阿岚

2026-10-6

你原有查询的逻辑是对D（条目）、Q（原因）的组合做聚合后直接取总和前5，和你要的「先提取条目总求和前5，再匹配每个条目下原因求和前3」的需求逻辑不匹配，需要调整分组排序的层级。

调整方案

1. 先获取总求和排名前5的条目列表

先运行如下公式拿到符合条件的前5个条目（可放在空白列比如S列作为辅助数据）：
=QUERY('BD3'!A:Q, "SELECT D, SUM(M) WHERE I='W34' AND M>0 AND L='Huacho' GROUP BY D ORDER BY SUM(M) DESC LIMIT 5",-1)
这一步输出的是总求和最高的5个条目及对应总金额，作为后续过滤的基准。

2. 查询每个条目下排名前3的原因

用嵌套逻辑筛选出属于前5条目的数据，再按条目分组后每组取前3个原因，单公式实现如下：

=ARRAYFORMULA(
  LET(
    // 定义变量：总求和前5的条目列表
    top5_d, QUERY('BD3'!A:Q, "SELECT D WHERE I='W34' AND M>0 AND L='Huacho' GROUP BY D ORDER BY SUM(M) DESC LIMIT 5",-1),
    // 定义变量：过滤出仅属于前5条目的符合条件的行
    filtered_data, FILTER('BD3'!A:Q, COUNTIF(top5_d, 'BD3'!D:D)>0, 'BD3'!I='W34', 'BD3'!M>0, 'BD3'!L='Huacho'),
    // 定义变量：按条目、原因分组求和，先按条目排序、再按求和值降序排序
    grouped, QUERY(filtered_data, "SELECT D,Q,SUM(M) GROUP BY D,Q ORDER BY D, SUM(M) DESC",-1),
    // 按条目分组，每组取前3条
    SORTN(grouped, 9^9, 2, INDEX(grouped,,1), 1)
  )
)

3. 格式适配（可选）

如果需要和你期望的输出格式一致，即同一个条目只在第一行显示名称，后续行留空，可以在上述公式外再加一层判断：

=ARRAYFORMULA(
  LET(
    res, 【此处替换为上面第二步的完整公式】,
    d_col, INDEX(res,,1),
    map_d, BYROW(SEQUENCE(ROWS(d_col)), LAMBDA(r, IF(COUNTIF(OFFSET(d_col,0,0,r), INDEX(d_col,r))>1, "", INDEX(d_col,r)))),
    {map_d, INDEX(res,,2), INDEX(res,,3)}
  )
)