分组求和、间隔取值,你只需要一个公式-Excel之家ExcelHome
我们经常会遇到这样的情况,连续的多列数据,我们需要
将他们分组求和
每隔几列取值或求和
你是怎么做的呢?还在用最原始的方法吗?
让我来告诉你个方法。只需要输入一个公式,拖动复制就可以全部搞定!省事省力还不易出错。
问题与公式 1
如下图所示,在A1:L6的区域内有每个月的销售数据,我们需要将每行数据按照季度汇总,也就是每三个单元格作为一组进行求和。
我们在M2单元格中输入以下公式:
=SUM(OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,3))
然后向右、向下拖拉复制公式填充,这样就完成了所有的求和了。简单快捷吧?下面让我们来详细了解一下这个公式。
公式详解之Offset
这个公式里面用到了三个函数,SUM,OFFSET,COLUMN。
OFFSET函数的语法是OFFSET(reference, rows谢羽亿, cols, [height], [width]),它根据指定的参数来返回一个单元格或单元格区域的引用。也就是从一个起始单元格或区域开始,向上或向下,向左或向右移动几行或几列,然后再返回一个设定了高度和宽度的区域聚u惠。
函数的最后两个参数用来指定返回区域的高度和宽度,如果省略,则返回的区域大小和第一个参数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轻松学友情推荐
长按二维码关注