Excel中如何计算每第n行对应列块数据的平均值

针对10万行、191列的大体积数据集，以下方法计算效率高，不会触发软件卡顿，可直接套用。 #

方法1：辅助列+条件平均（全Excel版本兼容，性能最优） #

核心逻辑是先给每行打标记，筛选出需要计算的目标行后按列求平均，步骤如下：

• 在数据集最右侧插入1列空白辅助列，从第一行数据所在行开始（如果有表头就从表头下第一行数据开始），输入标记公式：=MOD(ROW() - 行偏移量, 间隔数n)=0
  • 行偏移量根据你要取的起始行调整：比如要取第10、20、30…行（每10行取最后一个）、数据从第1行开始，偏移量设为0，公式为=MOD(ROW(),10)=0；如果要取第1、11、21…行，偏移量设为1，公式为=MOD(ROW()-1,10)=0
  • 对应你给出的两行重复样例：取第1、3、5…行时公式为=MOD(ROW()-1,2)=0，取第2、4、6…行时公式为=MOD(ROW(),2)=0
• 选中辅助列填了公式的单元格，双击单元格右下角的黑色填充十字，公式会自动批量填充到所有数据行，10万行填充耗时不超过2秒
• 在结果区域对应第一列的单元格输入平均值公式：=AVERAGEIF($辅助列整列数据范围, TRUE, 对应列整列数据范围)
  • 举个实际套用例子：数据范围是A2:GQ100001（共10万行191列，第1行为表头），辅助列为GR列，要计算每第10行的平均值，A列对应的平均值公式为=AVERAGEIF($GR$2:$GR$100001, TRUE, A$2:A$100001)
  • 注意公式中辅助列范围要加$锁死绝对引用，列标前不要加$，保证右拉填充时自动匹配对应列的数据范围
• 选中写好公式的平均值单元格，向右拖动填充到全部191列，即可一次性得到所有列的间隔行平均值
  *如果需要频繁调整间隔数，可以把间隔值n存在单独的空白单元格（比如GS1），公式里直接引用$GS$1替换固定数字，调整间隔时只需要修改单元格数值即可自动重算。

方法2：动态数组公式（无需辅助列，仅适配Excel 365/2021及以上版本） #

如果你的Excel版本支持动态数组函数，不需要插辅助列，直接在结果区域第一个单元格输入单条公式，即可自动溢出所有列的计算结果：

=BYCOL(数据范围,LAMBDA(col,AVERAGE(FILTER(col,MOD(SEQUENCE(ROWS(数据范围))-行偏移量,间隔数n)=0))))

套用例子：数据范围为A2:GQ100001，计算每第10行（从第10行开始取）的平均值，公式为：

=BYCOL(A2:GQ100001,LAMBDA(col,AVERAGE(FILTER(col,MOD(SEQUENCE(ROWS(A2:GQ100001)),10)=0))))

输入后按回车即可自动返回191列的计算结果，无需手动填充。 #

样例校验 #

用你给出的两行重复测试数据验证：5列数据循环重复[1,2,5,6,7]、[3,4,10,9,8]两组值，间隔数n=2时：

• 取奇数行（1、3、5…）计算平均值，结果为1,2,5,6,7，和第一组数值完全一致
• 取偶数行（2、4、6…）计算平均值，结果为3,4,10,9,8，和第二组数值完全一致
  计算逻辑准确。
  *注意：10万行级数据集不要使用逐行点选、OFFSET类易失性函数嵌套的公式，会触发Excel全表反复重算导致卡顿，上述两种方法单次计算耗时均在3秒以内。

内容的提问来源于stack exchange，提问作者Mustafa Köse