小课堂:跨表格求和引发的,4个求和骚操作
今日目标:
学习4种求和姿势
拉登老师,有个动态求和效果,你帮忙看看怎么实现的?
先看下数据。
第1页,是销售总和汇总。
后面是每天的销售统计。
第1页整体的汇总倒是没什么特别。
有意思的是,移动「开始」和「结束」页,前面的汇总数据就变了,自动计算「开始」和「结束」的销售总和,这是怎么回事?
媳妇:拉登!吃饭啦!
拉登:等会,我把这篇文章写完再吃!马上就好!
1- 跨工作表求和
这里考了一个知识点:单元格引用的设置。
在单元格引用当中,冒号指的是起始和结束单元格位置。
这个位置当中,可以包含工作表的名称,也可以单元格的地址。
有了起始和结束位置,中间所有的区域的都会被算进来求和。
所以这个公式当中,「开始」和「结束」代表的就是把这两个工作表中间的所有工作表,全部都进行求和。
所以显然,当我们移动开始和结束工作表标签的时候,中间位置发生了变化,那么公式也会动态的更新。
对于动态求和还有几种方法,给大家再普及一下。
2- 区域自动求和
表格新增数据的时候,动态求和,这是比较普遍的一个需求。
SUM函数求和的区域往往都是固定的,怎么样把它构建成一个动态的区域呢?
这里需要借助另外2个函数叫做:OFFSET和COUNTA。
上图效果对应的公式是这样的。
=SUM(OFFSET(B3,,,COUNTA(B3:B27)))
公式复杂了一点,我们从内往外拆解公式。
1- COUNT函数
首先用COUNT函数,统计要求和的单元格数量
= COUNTA(B3:B27)
2- OFFSET函数
然后用OFFSET函数,动态更新求和区域。
=OFFSET(B3,,,COUNTA(B3:B27))
OFFSET的几个参数含义如下:
- 参数1。选区的起始位置,这里写的是B3。
- 参数2。要向下偏移几行,不偏移就写0或空着。
- 参数3。要向右偏移几列,不偏移就写0或空着。
- 参数4。选区包含几行,这里写的是COUNTA计算出来的行数。
- 参数5。选区要包含几列,不写就是和参数1一致。
3- SUM函数
最后,外面套上SUM函数进行求和。
=SUM(OFFSET(B3,,,COUNTA(B3:B27)))
如果你觉得这个公式太复杂,学不会!想找更简单地方法?
很好,因为偷懒是人类进步的动力。
这个时候,你需要知道另外一个很好用的功能叫做:智能表格,来看一下效果先。
非常简单,把数据转成智能表格,新增数据的时候,智能表格会自动扩展区域(看样式就看出来了),SUM函数的求和区也会自动拓展。
整个过程不需要外加任何的函数,就可以实现动态求和。
3- 筛选自动求和
还有一种情况,是筛选或者隐藏数据之后,只对可见的单元格求和。
这显然已经完全超出了SUM能力,这个时候请出SUM的大表哥「SUBTOTAL」来实现这个效果啦!
上面的效果,对应SUBTOTAL的公式如下:
=SUBTOTAL(9,C3:C12)
结合这个公式,我们看一下SUBTOTAL的参数用法:
1- 参数1。计算的方式,用不同的数字代表不同的计算方式。公式中选择的9 - SUM。
2- 参数2。计算的区域。公式中的C3:C12
这里的关键是参数1,有很多的选项:
按照office官方帮助解释是这样的:
数字 1-11 或 101-111,用于指定要为分类汇总使用的函数。如果使用 1-11,将包括手动隐藏的行,如果使用 101-111,则排除手动隐藏的行;始终排除已筛选掉的单元格。
不管了,你只要记住求和用9或者109就够了。
有了前面一次偷懒的经验,我猜你肯定在想更简单的方法吧?
没错,方法是有的,还是用「智能表格」,轻松地解决这个问题。
把区域转成「智能表格」,在「设计」选项卡里面勾选「汇总行」。
注意!一定要注意!
睁开你的双眼,这个时候奇迹发生了!
表格中自动出现一个汇总行,每个单元格都会有一个下拉菜单,点击就可以选择汇总的方式,包括求和。
而这个求和默认就是对可见单元格求和。
整个过程,我们只是鼠标点了两下,选择了求和,就这么简单。
4- 条件求和
还有一种自动求和的方式,叫做:条件求和。
比如,现在我们只希望针对下面的正数求和。
求你把计算器拿远一点,行吗?「智能表格」也帮不上忙。
要用SUMIF函数,萨姆姨夫,懂吗?直接看效果:
对应公式如下:
=SUMIF(B2:B13,">0")
SUMIF的作用,就是根据条件进行求和,它有这么几个参数。
- 参数1。要求和的区域。
- 参数2。求和的条件,这里写的是">0",即大于0的数字才求和。
简单的不得了。而且数据变化后,求和结果也自动更新。
5- 总结
好了,简单总结一下今天的自动求和知识点。
1- 跨表自动求和,SUM + 单元格引用
2- 扩展区域求和,SUM+OFFSET
3- 隐藏区域求和,SUBTOTAL
4- 根据条件求和,SUMIF
什么是高手,高手就是可以把简单的事情做到极致;可以吃个泡面,都能吃出230万的播放量!
刻意练习嘛!
考考你
好啦,那么最后考考你。
要计算每个产品的营业额,应该用什么函数呢?
评论区等你的答案,记得点「在看」。
好了,我去吃饭了。
= = 推荐文章 = =
小课堂:34岁的VLOOKUP要光荣退休了?这个新函数好用到爆哭!!