我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
我推过一期跨表公式合集,其中有一个是利用sum进行多表求和
【例】如下图所示,需要在汇总表中统计1~30日的各个商品销量合计(日报表和汇总表格式、位置完全一样)
在汇总表B2中输入公式:
=sum(‘*’!b2)
输入后会自动替换为多表引用方式
=SUM(‘1日:30日 ‘!B2)
有同学提问:如果各个表中商品的位置(所在行数)不一样,该怎么求和?我今天要分享一个更强大的支持行数不同的求和公式。
分析及公式设置过程:
如果对单个表(比如1日)进行对A商品进行求和,可以直接用sumif函数搞定:
1日表
在汇总表中设置求和公式:
=SUMIF(‘1日’!A:A,A2,’1日’!B:B)
依此类推,如果对30天求和,公式应为:
=SUMIF(‘1日’!A:A,A2,’1日’!B:B)+SUMIF(‘2日’!A:A,A2,’2日’!B:B)
+…….+SUMIF(’30日’!A:A,A2,’30日’!B:B)
这公式也太长了吧……
细心的同学会发现,公式虽然,但还是有规律的:对各个表的求和除了表名外,其他公式部分都相同。
利用这个特点,我们可以用row函数自动生成对1~30天的引用。
=Row(1:30) 的结果为
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}
为证明这一点,可以在单元格中输入公式后,选中row(1:30)按F9键
连接成对各个表A列和B列的引用
=ROW(1:30)&”日!A:A”
=ROW(1:30)&”日!B:B”
连接成的只是字符串,并不能代表1:30日的A列和B列。把字符串地址转换成真正的引用,这是indirect函数的特长:
=Inidrect(ROW(1:30)&”日!A:A”)
=Indirect(ROW(1:30)&”日!B:B”)
有地址了,把它套进sumif函数中会怎么样?
=SUMIF(Inidrect(ROW(1:30)&”日!A:A”),A2,Indirect(ROW(1:30)&”日!B:B”))
结果是会把各个表中的A产品销量分别进行求和,查看结果按F9。
最后用sumproduct函数进行求和(这里不用sum的原因是:sum无法直接支持数组运算,本公式中同时对多数组进行运算属数组运算)
最终的公式为:
=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$30)&”日!a:a”),A2,INDIRECT(ROW($1:$30)&”日!b:b”)))
由于公式复制后row(1:30)中的行数会发生变化,所以这里必须要添加绝对引用符号$
注:如果是多表多条件求和,可以用sumifs函数,原理相同。
相关文章
我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
在工作中,可能许多朋友都会碰到一个情况,那就是工作簿和工作表数据的合并操作。如何将上百个工作簿快速合并到一个表格中,许多朋友可能会觉得不可思议。今天我们就来教大家学习一......
2023-01-08
今天在这里为你分享5个Excel文本函数,这些拆分和组合函数,你一定会用上的。①LEFT函数公式:=LEFT(A2,1)在Excel表格中,需要想要拆分汉字,想从哪里开始就从那哪里开始。首先选定单元格......
2023-01-08
相信大家也和我一样,才开始看到Excel可以当做翻译软件的时候会很好奇,这究竟是怎样做到的?其实,这个方法并不是很难,它是由一个函数公式而制作出来的,好了,首先我们一起来看看成......
2023-01-08
函数可以说是所用快捷方法中最为简单的一种方法,为什么很多人认为函数用起来很难了?主要是因为它拥有很长的函数公式,记不住。其实不管是学Excel函数,还是学习其他的一些快捷方法......
2023-01-08