Excel微课堂,轻轻松松学会Excel~
在使用Excel汇总数据时,有时候需要提取数据区域中固定间隔n行的数据进行求和,例如每间隔2行有一个小计项,需要计算所有小计项之和等。如图 87‑1所示为一份数据表,现需要求出数据区域中每间隔2行的数据之和,即行号为2、5、8、11、14的数据之和,该如何操作呢?
→ 解决方案:
使用SUMPRODUCT+MOD函数组合实现隔行求和。
→ 操作方法
在H2单元格输入如下公式,按Enter键结束。
=SUMPRODUCT((MOD(ROW(A2:F16),3)=MOD(ROW(A2),3))*A2:F16)
→ 原理分析
MOD函数构建间隔n行与循环1.本例要求出A2:F16单元格区域每隔2行的数据之和,即相当于每3行一个循环,所以使用MOD函数将数据区域的行号除以3求余数,用MOD(ROW(A2:F16),3)返回余数数组:
{2;0;1;2;0;1;2;0;1;2;0;1;2;0;1}
2.其中,第2、5、8、11、14行所得余数均为2,因为从数据区域的首行开始求和,将上述余数数组与数据区域的首行行号余数(即MOD(ROW(A2),3))比较判断是否相同。
3.使用SUMPRODUCT函数求出判断返回逻辑值数组与数据相乘之和。
→ 知识扩展
间隔n行数据求和通用公式
1.本例中的公式还可以进一步简化如下:
=SUMPRODUCT((MOD(ROW(2:16),3)=2)*A2:F16)
2.如果需要求数据区域每间隔3行的数据之和,则可以使用如下公式:
=SUMPRODUCT((MOD(ROW(A2:F16),4)=MOD(ROW(A2),4))*A2:F16)
如果需要求数据区域每间隔4行的数据之和,则可以使用如下公式:
=SUMPRODUCT((MOD(ROW(A2:F16),5)=MOD(ROW(A2),5))*A2:F16)
由此可以得出求数据区域每间隔n行的数据之和的通用公式为:
=SUMPRODUCT((MOD(ROW(数据区域),n+1)=MOD(数据区域起始行号,n+1))*数据区域)
同理,可以得出求数据区域每间隔n列的数据之和的通用公式为:
=SUMPRODUCT((MOD(COLUMN(数据区域),n+1)=MOD(数据区域起始列号,n+1))*数据区域)