现在有免费的精英会计交流群,每天前150名进群免费,与众多优秀财务人一起讨论业务、交朋友!进群加微信号kjsc022
打败我们的从来不是函数,而是信心。来,今天卢子就给你信心学好这个OFFSET函数。
OFFSET函数动态图解,起点为B2供应商ID,根据这个起点向下几行,向右几列。先用心的看3遍动画!
函数语法:
=OFFSET(起点,向下几行,向右几列)
1、统计前几个月的总金额。
正常如果统计前4个月我们都是用SUM函数直接求和。
=SUM(B2:B5)
但很多时候我们前几个月的数字是变动的,这次是4个月,下次是6个月,也就是说D3这个单元格不断改变。
我们知道求和的起始单元格,求和的终点单元格是什么?这时就要用到OFFSET函数确定。
=OFFSET(B1,D3,0)
这里OFFSET函数的起点是B1,向下几行由D3决定,因为是获取B列的值,所以不向右,也就是为0。
组合起来就是:
=SUM(B2:OFFSET(B1,D3,0))
2、对公式B2:OFFSET(B1,D3,0)这一部分很好奇,为什么这样能求和?
你可以在编辑栏选择这一部分区域,按F9键。
其实就是B2:B5这个区域的值,这也是嵌套SUM函数能够自动求和的原因。
不要单独看OFFSET(B1,D3,0)得出来是300,这个怎么能求和?而是要看整体B2:OFFSET(B1,D3,0),300是和前面B2组成的一个新区域。
举一个最简单的例子,现在要对B2:B5进行求和,你选择B5用F9键按出来的是300,然后问B2:300怎么求和,这样会一脸懵逼。
你要一次性选择B2:B5按F9键才能看明白,我们学公式要学会看整体。
3、查询每个季度的金额。
通过观察,我们发现第一季度为B1向下4行。
=OFFSET($B$1,4,0)
第二季度为B1向下8行。
=OFFSET($B$1,8,0)
第三季度为B1向下12行。
=OFFSET($B$1,12,0)
第四季度为B1向下16行。
=OFFSET($B$1,16,0)
而4、8、12和16分别是4乘以1、2、3和4得到的结果。
我们知道ROW函数可以获取1~N的序号,综合起来,就是:
=OFFSET($B$1,4*ROW(A1),0)
4、如何获取偶数月份的金额?
前面说过隔行获取对应值,这个是隔列获取对应值。行号用ROW函数,列号用COLUMN函数。
偶数月份金额,也就是A2这个单元格向右2、4、6、8、10和12列获取的值,也就是1、2、3、4、5和6乘以2就可以,而数字换成COLUMN函数就是。
综合起来就是:
=OFFSET($A$2,0,COLUMN(A1)*2)
5、大家最关心的OFFSET函数第4、第5参数如何运用的问题。
OFFSET函数有一种是3个参数的用法:
=OFFSET(起点,向下几行,向右几列)
有一种是5个参数的用法:
=OFFSET(起点,向下几行,向右几列,多少行,多少列)
这种5个参数的用法一般用在获取动态区域上,像这种明细表很常见,就是行数不断增加,但是列数固定为5列。
起点:A1
向下几行:0
向右几列:0
多少行:不确定
多少列:5
现在除了第4参数多少行不确定,其他都是确定的,直接输入进去即可。怎么确定这个第4参数?
因为供应商这一列每个单元格都会输入内容,所以判断这一列有多少个非空单元格即可,非空单元格用COUNTA函数统计。
综合起来,动态区域公式就是:
=OFFSET($A$1,0,0,COUNTA($A:$A),5)
不过这个公式不能直接用在单元格,一个单元格只能放一个值,现在是一个区域,一个单元格容纳不了。就比如说,你平常一顿饭吃一碗饭,现在要给你吃100碗,肯定是吃不下,直接就撑死,在Excel中这种叫出错。
这时就涉及到一个新功能,定义名称。单击公式,定义名称,名称改成动态,引用位置将公式复制粘贴过去,确定。
定义名称一般都是跟数据透视表一起用。
函数很难吗?嵌套函数很难吗?不是的,要懂得方法!
现在有免费的精英会计交流群,每天前150名进群免费,与众多优秀财务人一起讨论业务、交朋友!进群加微信号kjsc022
来源:excel不加班,作者:卢子