当前位置:首页 > 办公 > Excel教程 > 正文内容

瞬间搞定一月数据汇总!这个Excel求和公式太牛了

酷网1个月前 (10-21)Excel教程28

我推过一期跨表公式合集,其中有一个是利用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函数,原理相同。

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/40266.html

分享给朋友:

相关文章

玩转Lookup函数,看这一篇就够了……

玩转Lookup函数,看这一篇就够了……

今天分享一个Excel中非常强大的查找函数Lookup。在日常工作中,我们都会遇到在数据表中查询数据的需求,Excel中对数据查询给出了很多的方式,其中速度最快的查询当属LOOKUP函数,下面就让一点IT技术带你深入了解...

EXCEL随机排监考老师?没有系统也能实现!

EXCEL随机排监考老师?没有系统也能实现!

某天湖北武汉的廖老师求助小编,希望在EXCEL中实现随机安排监考老师; 源数据表长这样,一共有近300场次需要安排:   图 1:源数据 可供监考的教师却只有50多人:   图 2:监考老师表 廖老师...

excel自动填满单元格

excel自动填满单元格

excel自动填满单元格要如何做?在Excel中,可以使用你已经输入到单元格中的数据填满整个单元格,并且会随着单元格尺寸的调整而自动填充,如下图1所示。 图1 实现起来也很简单,选择要实现自动填满数据的单元格,单击功能...

怎么在EXCEL中创建库存清单

怎么在EXCEL中创建库存清单

本文将教你如何在Windows或Mac电脑中用Excel电子表格来管理企业库存。你可以使用预制的库存清单模板,也可以手动创建工作表。 步骤 方法 1使用模板 打开Excel软件。软件是深绿色的图标,上面有白色的“X”。...

N多人分组完成M个项目,excel表格统计每个人参与了哪些项目

N多人分组完成M个项目,excel表格统计每个人参与了哪些项目

一位朋友留言,说他们项目部所有的人,每五人为一小组,完成了很多项目。现在,要论功行赏,按分组名单,统计每人参与了哪些项目。 他问有没有公式,一次完成统计。 为了好述,将数据简化如下: 最终要完成:按照表一项目分组,完成...

怎么在Excel中创建日历

怎么在Excel中创建日历

虽然Excel不是专用的日历程序,但可以使用Excel创建和管理日历。你可以根据自己的喜好自定义各种日历模板,这比自己从头开始创建日历格式要快得多。还可以从电子表格中获取日历事件列表,并将其导入Outlook日历。 步骤...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。