sum+offset+match多条件多列求和

我们在做数据表的时候,经常会遇见单条件或是多条件动态区域求和或是求乘积,能够熟练使用offset函数可能会帮助你节省很多时间;

如下图,我们需要在不同月份得到当月的预算数据,并且还要列出从1月到该月的累计数据;

offset动态和求.png

这里共需要用到offsetrowsummatch这几个函数,下面就先了解一下这几个函数用法;

1、row函数:返回所选择的某一个单元格的行数,如row(),则返回当前行数;

2、sum函数:求和函数;

3、match函数:查找引用函数,常规操作中,它是需要和vlookupindex、offset等函数搭配使用;

语法:MATCH(要查找的数据, 查找区域, 查找方式)

4、offset函数:返回以某个单元格基准且距离该单元格指定行数和列数区域的引用,可以在被引用的单元格周围进行上下左右偏移;

语法:OFFSET(引用单元格, 行数, 列数, [高度], [宽度]),第四和第五参数如非必要,可以省略;

实际运用:

例如,今天是2021年6月份,我需要得到6月份的预算数,及1-6月份的累计预算金额;

一、通过偏移引用得到6月份的预算

D5单元格公式:=OFFSET($F$4,ROW()-4,$D$2-1)

解析:

第一参数:$F$4,用绝对引用符号进行定位,即表示公式后面参数的偏移是转绕F4这个单元格进行的;

第二参数:row()-4,计算标所在单元格(即D5)的行数并减4,结果为1,即表示offset函数的第一参数的引用单元格行数往下偏移一行,即得到的是光标所在单元格的行;

第三参数:$D$2-1,D2即是动态条件所在的单元格,如D2条件是1月份时,而结果为1-1=0,表示不进行偏移,如D2条件为6月份时,即6-1=5,从F4开始往右偏移5列;

如上即可得到动态条件的引用数据;

二、动态条件计算多列的和

在E5单元格求出1-6月的累计预算额

E5单元格公式:SUM(OFFSET($F$4,ROW()-4,,,MATCH($D$2,$F$1:$L$1,0)));

解析:

这里的公式第三和第四参数为空,使用第二参数的原因是表格公式是往下拖动的,所以只对行数进行偏移就可以了,如果表格公式是横向拖动,那么第二参数为空,改用第三参数并用column函数进行列的偏移计算就可以了;

第一参数和第二参数原理同上;

第五参数:MATCH($D$2,$F$1:$L$1,0),这个参数的作用是将第一参数作为基准点,向右圈选按条件所需要的宽度,这里使用match函数,查找出D2的动态条件所对应的F:L具体所在的列;

例如D2单元格是6月份时,则可以得到以D2为基准单元格,向下偏移一行,向右偏移到6月所在的单元格这样的一个区域,再嵌套一个sum函数进行求和,从而得出1-6月的累计预算额;

offset动态和求1.png

Please GoUrl:rijifang.Com
打赏
转载请注明出处;
本文地址:https://rijifang.com/index.php/post/14352.html
相关热文

发表评论: