如何自动化计算大数据集按20行间隔分组的平均值与标准差？

针对你表格「空白行→AvgN→StDevN→两行空白」的循环结构，以及下拉公式范围出错的问题，推荐两种自动化计算方案：

方法1：使用INDEX函数（非易失性，性能更稳定） #

平均值（AvgN）公式 #

假设第一个AvgN在第2行（第1行为空白行），输入公式后直接下拉：

=AVERAGE(INDEX('Series Data'!$Q:$Q, 2 + INT((ROW()-2)/5)*20):INDEX('Series Data'!$Q:$Q, 21 + INT((ROW()-2)/5)*20))

标准差（StDevN）公式 #

假设第一个StDevN在第3行，输入公式后直接下拉：

=STDEV.S(INDEX('Series Data'!$Q:$Q, 2 + INT((ROW()-3)/5)*20):INDEX('Series Data'!$Q:$Q, 21 + INT((ROW()-3)/5)*20))

方法2：使用OFFSET函数（易失性，适合小数据量场景） #

平均值（AvgN）公式 #

=AVERAGE(OFFSET('Series Data'!$Q$2, INT((ROW()-2)/5)*20, 0, 20, 1))

标准差（StDevN）公式 #

=STDEV.S(OFFSET('Series Data'!$Q$2, INT((ROW()-3)/5)*20, 0, 20, 1))

公式逻辑说明 #

• ROW() 获取当前单元格行号，INT((ROW()-x)/5) 计算当前属于第几组（x为第一个AvgN/StDevN的行号，5是表格循环块总行数：1空白+1Avg+1StDev+2空白）
• 每组对应「Series Data」Q列的20行数据，通过*20实现组间行偏移，下拉时自动切换到下一组的目标范围

内容的提问来源于stack exchange，提问作者Jan van oest