我推过一期跨表公式合集 , 其中有一个是利用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 。
【瞬间搞定一月数据汇总!这个Excel求和公式太牛了】
文章插图
最后用sumproduct函数进行求和(这里不用sum的原因是:sum无法直接支持数组运算 , 本公式中同时对多数组进行运算属数组运算)
最终的公式为:
=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$30)&”日!a:a”),A2,INDIRECT(ROW($1:$30)&”日!b:b”)))
由于公式复制后row(1:30)中的行数会发生变化 , 所以这里必须要添加绝对引用符号$
文章插图
注:如果是多表多条件求和 , 可以用sumifs函数 , 原理相同 。
推荐阅读
- 一群高手折腾了一个晚上都搞不定的库存表,结果居然一个简单的SUMIFS函数就搞定...
- 因为不知道SUMIF函数还能横着用,她一上午的工作被别人一分钟搞定
- 这些Excel函数公式5分钟搞定5小时的工作
- 一个rank函数一分钟搞定多表排名
- 使用RANK函数轻松搞定并列排名
- 3分钟,快速搞定excel排序毛病!
- 8个Excel常用函数,轻松帮你搞定日常办公!
- 31个单函数+7种复合函数,公式太多记不住?一招搞定!
- 对于排名,很多时候并不是一个RANK能搞定
- 教你搞定RANK函数都没辙的排名方法