Google Sheets中如何按序列位置计算排序筛选值的分组平均值？

数据集 #

数据生成逻辑 #

Col2的数据由以下公式生成：

=sort(FILTER(paste!M2:M, paste!$A$2:$A = A2, paste!$B$2:$B = B2, paste!$C$2:$C = C2, paste!$F$2:$F > 0))

分组计算需求 #

需在三个独立单元格中计算三组数据的平均值，分组规则基于非空条目数自动计算：

• 第一、三组条目数：通过=QUOTIENT(AH2, 3)得到（AH2为总条目数31，结果为10）
• 中间组条目数：通过=QUOTIENT(COUNT(B1:B31), 3) + MOD(COUNT(B1:B31), 3)计算得11（修正原公式语法错误）

具体计算要求：

• 第一组：最小的10个值的平均值
• 第二组：第11至第21小的值的平均值
• 第三组：第22至第31小（最大）的值的平均值

要求使用动态引用公式，禁止使用绝对数值。此前尝试=SUBTOTAL(101,paste!I$2:I)无法实现排序序列的部分数据统计，现寻求Google Sheets中的可行公式方案。

Google Sheets 实现方案 #

1. 先定义总条目数（示例放在AH2单元格）

=COUNTA(B2:B32) // 根据实际数据范围调整，确保统计所有非空值

2. 第一组平均值（最小N个值，N=QUOTIENT(AH2,3)）

如果Col2未预先排序：

=AVERAGE(INDEX(SORT(B2:B32), SEQUENCE(QUOTIENT(AH2, 3))))

如果Col2已通过原公式升序排序，可简化为：

=AVERAGE(INDEX(B2:B32, SEQUENCE(QUOTIENT(AH2, 3))))

3. 第二组平均值（中间段数据）

如果Col2未预先排序：

=AVERAGE(INDEX(SORT(B2:B32), SEQUENCE(QUOTIENT(AH2,3)+MOD(AH2,3), 1, QUOTIENT(AH2,3)+1)))

如果Col2已升序排序，可简化为：

=AVERAGE(INDEX(B2:B32, SEQUENCE(QUOTIENT(AH2,3)+MOD(AH2,3), 1, QUOTIENT(AH2,3)+1)))

4. 第三组平均值（最大N个值，N=QUOTIENT(AH2,3)）

如果Col2未预先排序：

=AVERAGE(INDEX(SORT(B2:B32,1,FALSE), SEQUENCE(QUOTIENT(AH2, 3))))

如果Col2已升序排序，可简化为：

=AVERAGE(INDEX(B2:B32, SEQUENCE(QUOTIENT(AH2, 3), 1, AH2-QUOTIENT(AH2,3)+1)))

公式说明 #

• SORT(B2:B32)：对目标数据列升序排序，确保数据从小到大排列（若Col2已排序可省略）
• INDEX(..., SEQUENCE(...))：动态提取指定范围的排序后数据，其中：
  • SEQUENCE(数量, 步长, 起始位置)：生成连续的行号序列，实现动态范围引用
• AVERAGE()：对提取的分组数据计算平均值

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