分组求和、间隔取值，你只需要一个公式

我们经常会遇到这样的情况，连续的多列数据，我们需要

• 将他们分组求和

• 每隔几列取值或求和

你是怎么做的呢？还在用最原始的方法吗？

让我来告诉你个方法。只需要输入一个公式，拖动复制就可以全部搞定！省事省力还不易出错。

问题与公式 1

如下图所示，在A1:L6的区域内有每个月的销售数据，我们需要将每行数据按照季度汇总，也就是每三个单元格作为一组进行求和。

我们在M2单元格中输入以下公式：

=SUM(OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,3))

然后向右、向下拖拉复制公式填充，这样就完成了所有的求和了。简单快捷吧？下面让我们来详细了解一下这个公式。

公式详解之Offset 

这个公式里面用到了三个函数，SUM，OFFSET，COLUMN。

OFFSET函数的语法是OFFSET(reference, rows, cols, [height], [width])，它根据指定的参数来返回一个单元格或单元格区域的引用。也就是从一个起始单元格或区域开始，向上或向下，向左或向右移动几行或几列，然后再返回一个设定了高度和宽度的区域。

函数的最后两个参数用来指定返回区域的高度和宽度，如果省略，则返回的区域大小和第一个参数reference的大小一样。

如下图所示，在A1单元格输入公式=OFFSET(A1,4,1)

表示从A1单元格向下移动4个单元格，再向右移动一个单元格，就跳到了B5单元格。这里没有指定最后两个参数，则表示移动后的区域还是1行1列，那么返回的就是对B5单元格的引用。

公式详解之Column 

COLUMN函数是用来返回列号，如果不加参数就表示返回当前单元格的列号。示例中COLUMN($M$1)就是表示返回M1单元格的列号，即13。

COLUMN()-COLUMN($M$1)，当在水平方向上拖动复制公式的时候，就生成一个序列0，1，2，3，乘以3就得到0，3，6，9。

我们这样做，是因为我们需要从A1单元格开始，向右移动0列、3列、6列、9列，从而定位到每个季度的第一个月，然后再设置移动后的区域宽度为3列，这样就得到了一个1行3列的区域，也就是每个季度的区域。

所以随着我们从M1单元格开始向右拖动复制公式，

OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,3)

生成了对每组3个单元格的引用，再加上SUM函数就可以得到每个季度的和了。

公式详解之绝对引用与混合引用 

提醒大家一定要注意公式中的绝对引用和混合引用。

$A2是混合引用，当拖动复制公式的时候，总是固定在A列，行号则根据公式所在的行号变化，这样可以保证我们的Offset总是从A列开始移动；

$M$1是绝对引用，不管将公式复制到哪儿这个地址都不变，这样COLUMN()的结果是变化的,COLUMN($M$1)的结果是不变的，最终才能生成等差序列。

公式中使用COLUMN()-COLUMN($M$1)的用意在于，当L列和M列中间插入其他信息时，我们就不需要更改公式了。否则如果有绝对数字的话，别人一改你的表格，公式计算结果可能就不正确了。

问题与公式 2

同样的例子，假如我们想要取一、四、七、十这几个月的值该怎么做呢？

如果是一次取一个单元格的值的话，只需要OFFSET就可以了，不需要SUM了。

在M1单元格输入以下公式：

=OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,1)

大家注意，最后一个参数变成了1，这个1就表示区域的大小是1列，倒数第二个参数省略了，表示行号跟起始区域的大小一样，都是1行。

好了，公式讲解就结素了。

今天的技巧你学会了吗？快练习一下吧。

本文由公众号 Excel轻松学 友情推荐