私信回复关键词【插件】~
获取Excel高手都在用的“插件合集+插件使用小技巧”!
Q:拉登老师,有个动态求和效果,你帮忙看看怎么实现的?
先看下数据:
第 1 页,是销售总和汇总,后面是每天的销售统计。
第 1 页整体的汇总倒是没什么特别。
有意思的是,移动「开始」和「结束」页,前面的汇总数据就变了,自动计算「开始」和「结束」的销售总和,这是怎么回事?
01
跨工作表求和
这里考了一个知识点:单元格引用的设置。
在单元格引用当中,冒号指的是起始和结束单元格位置。
这个位置当中,可以包含工作表的名称,也可以单元格的地址。
有了起始和结束位置,中间所有的区域的都会被算进来求和。
所以这个公式当中,「开始」和「结束」代表的就是把这两个工作表中间的所有工作表,全部都进行求和。
所以,当我们移动开始和结束工作表标签的时候,中间位置发生了变化,那么公式也会动态的更新。
对于动态求和还有几种方法,给大家再普及一下。
02
区域自动求和
表格新增数据的时候,动态求和,这是比较普遍的一个需求。
SUM 函数求和的区域往往都是固定的,「怎样把它构建成一个动态的区域呢?」
这里需要借助另外 2 个函数叫做:OFFSET 和 COUNTA。
上图效果对应的公式是这样的:
<code>=SUM(OFFSET(B3,,,COUNTA(B3:B27)))/<code>
公式复杂了一点,我们从内往外拆解公式。
❶ COUNT 函数。
首先用 COUNT 函数,统计要求和的单元格数量:
<code>= COUNTA(B3:B27)/<code>
❷ OFFSET 函数。
然后用 OFFSET 函数,动态更新求和区域:
<code>=OFFSET(B3,,,COUNTA(B3:B27))/<code>
OFFSET 的几个参数含义如下:
❶ 参数 1:选区的起始位置,这里写的是 B3;
❷ 参数 2:要向下偏移几行,不偏移就写 0 或空着;
❸ 参数 3:要向右偏移几列,不偏移就写 0 或空着;
❹ 参数 4:选区包含几行,这里写的是 COUNTA 计算出来的行数;
❺ 参数 5:选区要包含几列,不写就是和参数 1 一致。
❸ SUM 函数。
最后,外面套上 SUM 函数进行求和:
<code>=SUM(OFFSET(B3,,,COUNTA(B3:B27)))/<code>
如果你觉得这个公式太复杂,学不会!
想找更简单的方法?
很好,因为偷懒是人类进步的动力。
这个时候,你需要知道另外一个很好用的功能叫做:「智能表格」。
来看一下效果:
非常简单,把数据转成智能表格。
新增数据的时候,智能表格会自动扩展区域(看样式就看出来了),SUM 函数的求和区也会自动拓展。
整个过程不需要外加任何的函数,就可以实现动态求和。
私信回复关键词【插件】~
获取Excel高手都在用的“插件合集+插件使用小技巧”!
閱讀更多 秋葉Excel 的文章