FIC-Excel 篇十四:学会OFFSET函数, 求解动态平均值问题
我们经常会遇到需要对动态区域进行运算/制图的情况,那么下面这个公式就非常重要了,他就是OFFSET函数!那么到底有哪些情形会用到这个公式呢?
当你的源数据不断更新增加时,你需要返回最新的3天/周/月的和/均值等等,根据用户的选择/偏好,对数据集合的特定部分进行操作 (比如用户在国家中选择中国,那么计算结果就是中国的结果)
计算逻辑非常复杂,涉及动态区域等等
=OFFSET(reference, rows, cols, [height], [width])
offset函数有5个参数
Reference, 指的是起始单元格/区域
Rows, 指明需要从起始点/区域向下移动多少行
Cols, 指明需要从起始点/区域向右移动多少列
[height], [width] 分别为新的区域的行列数量
那么我们就用一个具体的例子看看OFFSET函数的用法吧!
A,B列分别为月份和当月的销量,我们在F25内可以选择任意一个月份,然后需要在E26内返回根据所选月份之后3个月的平均值 (这里选择了2016年2月,我们要计算,2月,3月,4月,3个月的平均值)
我们先构建一个大致的函数框架 Average(OFFSET(起始点,移动行数,移动列数,区域行数,区域列数)),因为我们要对B列进行计算,因此起始点可以设为B25, 移动行数未知,移动列数可以设置为0 (因为就对B列计算,不需要移动),区域行数设置为3 (因为要对3个月的数据进行计算),区域列数为1(仅有1列需要计算),因此这里唯一需要求解的就是移动行数了!
接下来我们使用Match函数返回所选择月份在数据集合中的行坐标
这里结果为3,也就是说2016年2月在整个A25:A51的集合中排在第3行, 而它正是我们从起始点到该位置所需的移动行数,因此我们可以将其替代OFFSET中的移动行数参数。
=AVERAGE(OFFSET(B25,MATCH(F25,A26:A51,0),0,3,1)
怎么样,你也可以试试看!
云雾海潮
校验提示文案
Tony9999
校验提示文案
Tony9999
校验提示文案
云雾海潮
校验提示文案