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个月的平均值)

效果效果

学会OFFSET函数, 求解动态平均值问题我们先构建一个大致的函数框架 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)

学会OFFSET函数, 求解动态平均值问题

组合函数组合函数

怎么样,你也可以试试看!


 

FIC-Excel 篇十三:SUM系列的终极王牌公式: SUMIFS多条件求和“SUM系列函数的大家庭中,SUM是最简单易懂的,SUMPRODUCT能够让人快速计算乘积的和,而SUMIF,SUMIFS两兄弟能够让人轻松根据条件求和。那么在今天的文章中,我们带大家走进SUMIFS,这个SUM大家庭中的终极王牌公式!”...FuninCode| 16 评论4 收藏199查看详情

 

 

FIC-Excel 篇十二:实现逆向查询很难吗?使用IF,{0,1} 让你的VLookup提升一个层次!“当有同伴问我,Vlookup能不能从右往左进行查询匹配时,我的回答是:不可以,但今天我们使用一种办法,通过IF,{0,1}让你的Vlookup提升一个层次,完成不可能的任务”...FuninCode| 29 评论12 收藏346查看详情

 

 

FIC-Excel 篇十一:你可能无法想象,Excel单元格自定义格式竟然如此强大!"你觉得下图这种数字格式设置是通过什么方式实现的?条件格式功能?还是别的?今天我们教给大家一种魔术般的技巧,据小编观察,身边掌握该技巧的人不足5%,那么让我们一探究竟吧!"...FuninCode| 90 评论14 收藏1k查看详情

 


推荐关注:
每天涨点小知识
话题:每天涨点小知识 +关注
办公软件
分类:办公软件 +关注
FIC-Excel
系列:FIC-Excel +关注

提示

鼠标移到标签上方,

尝试关注标签~

评论2

发表评论请 登录
  • 最新
  • 最热
评论举报

请选择举报理由

151 2

关注数量超出限制,请先删除部分内容再尝试

关注数量超出限制,
请先删除部分内容再尝试

登录
注册
用户名/邮箱
密码
验证码
看不清?点击更换
看不清?点击更换 忘记密码?

已收藏
去我的收藏夹 >

已取消收藏
去我的收藏夹 >

小提示