如何在Google Sheets查询中添加总计行？Top5筛选时总计异常求助

问题说明 #

当前使用的公式在触发Top5（Limit 5）筛选时，总计行仍计算全量数据的总和，而非仅统计筛选出的Top5条目：

SE(E(B1="Leads";C1="Crescente";D1="Top5";E1="Tudo");{QUERY(Dados!G2:N;"Select Col5,Sum(Col6),(Sum(Col8)/Sum(Col6)),Sum(Col8) Group By Col5 Order By Sum(Col6) ASC Limit 5 Label (Sum(Col8)/Sum(Col6)) 'CPL', Sum(Col6) 'Leads', Sum(Col8) 'Valor Investido'";1);
{"TOTAL"\QUERY(Dados!G2:N;"Select Sum(Col6), (Sum(Col8)/Sum(Col6)), Sum(Col8) Order By Sum(Col6) DESC Limit 5 Label (Sum(Col8)/Sum(Col6)) '', Sum(Col6)'', Sum(Col8)''";1)}}

解决思路 #

原公式的总计部分直接查询全量Dados!G2:N数据，未关联前面筛选出的Top5结果。需先获取Top5的分组数据集，再基于该子集计算总计。

修改后的公式 #

SE(E(B1="Leads";C1="Crescente";D1="Top5";E1="Tudo");
  LET(
    top5_data; QUERY(Dados!G2:N;"Select Col5,Sum(Col6),(Sum(Col8)/Sum(Col6)),Sum(Col8) Group By Col5 Order By Sum(Col6) ASC Limit 5 Label (Sum(Col8)/Sum(Col6)) 'CPL', Sum(Col6) 'Leads', Sum(Col8) 'Valor Investido'";1);
    total_row; {"TOTAL"; INDEX(QUERY(top5_data;"Select Sum(Col2), (Sum(Col4)/Sum(Col2)), Sum(Col4) Label Sum(Col2) '', (Sum(Col4)/Sum(Col2)) '', Sum(Col4) ''";1);2)};
    {top5_data; total_row}
  )
)

公式解释 #

• 用LET函数定义中间变量top5_data，存储筛选后的Top5分组结果，避免重复查询全量数据
• total_row基于top5_data计算总计：
  • 对top5_data的第2列（Leads）、第4列（Valor Investido）求和
  • 计算总计CPL（总计Valor Investido / 总计Leads）
• 最后将top5_data和total_row合并为最终输出数组

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