|
在Excel中，利用函数的嵌套可以实现很多功能，而offset函数就是比较难掌握的函数之一，这个函数因为参数比较多，可以有5个参数，所以与其他函数结合使用可以有非常多的用法，本文就给大家介绍一下offset函数的几种用法，希望能够对您提供帮助。 一：基本含义。 offset函数有5个参数，即=offset（起始区域，向下偏移行数，向右偏移列数，返回的行数，返回的列数），如果最后2个参数省略，则返回的区域与第一个参数相同。而第一个参数表示的起始区域不仅可以是单元格，也可以表示从一个区域开始进行偏移。 如下图所示，在F2单元格输入函数=OFFSET(A1,5,3,4,2)，表示从A1单元格开始偏移，向下偏移5行，向右偏移3列，然后返回4行2列的区域，即下图中D6到E9单元格的区域。因为此处返回的是一个区域，所以输入函数直接按回车会返回错误值，按下Ctrl+shift+enter键会返回区域的第一个值。
二：offset函数的基本运算 既然上图中offset函数经过偏移后返回的是一个区域，所以我们可以对这个区域求和、平均值、计数、最大最小值等。比如对下面的返回的区域求和，那么在offset函数前加上sum函数，然后就可以计算出结果是36。
三：offset与一个match函数结合求和。 在下图中，要计算1月到某月的销量和，如果切换月份时，累计销量也会变化。该如何操作呢？ 首先，在F2单元格添加数据验证。选择【数据】——【数据验证】——允许选择【序列】——来源选择A2到A13单元格的数据——【确定】。这时我们就在F2单元格建立好月份的下拉菜单选项。 然后在G2单元格输入函数=SUM(OFFSET(B2,0,0,MATCH(F2,A2:A13,0)))，此时再F2单元格选择相应的月份，G2单元格的累计销量就会随着选择的月变化而变化。 这个函数有三个公式，最里面的MATCH(F2,A2:A13,0)，表示查找F2位于A2到A13单元格的第几行，比如F2单元格是十月，十月在A2到A13单元格的第10行，所以match函数返回的结果是10。OFFSET(B2,0,0,10）中省略了第五个参数，返回的结果是B2到B11单元格，最后利用sum函数对B2到B11单元格进行求和。
四：offset函数与多个match函数进行求和。 仍然是上面一组数据，那么可不可以求任意两个月份之间的累计销量呢？我们考虑到既然match函数返回的值是所选单元格在区域中的位置，那么就可以利用match函数嵌套来编制公式。 我们在D2和F2单元格分别设置月份的下拉菜单选项，然后在G2单元格中输入函数=SUM(OFFSET(B2,MATCH(D2,A2:A13,0)-1,0,MATCH(F2,A2:A13,0)-MATCH(D2,A2:A13,0)+1),0)。此时我们在D2和F2中选择相应的月份，就可以求出两个月份之间的累计销量了。 这个函数看上去比较长，实际在编写函数的时候比较容易想到，也容易理解，因为match函数可以返回行数，所以在以B2单元格为起始单元格的前提下，向下偏移的行数要根据D2单元格的变化而变化，第二个参数用了MATCH(D2,A2:A13,0)-1，表示如果D2单元格选择九月，那么这个match函数返回的是8，即9月对应的销量在B2开始数的第9-1=8行数据。第4个参数用到了两个match函数相减，因为F2单元格所在的行数减D2单元格所在的行数，需要对计算结果加1进行调整。最后利用sum函数求和即可。 但是这种情况如果选择时D2单元格大于F2单元格时，计算的就不是正确结果怎么办？此时只要把offset函数第四个参数嵌套一个if函数，即如果F2的月份大于D2，那么match函数相减后加1，如果F2的月份小于D2，那么等于match函数相减后减1。这样设置后无论如何选择月份都会计算出正确结果。
五：与count函数组合求最近几个累计数 如下图所示，我想要计算最近3个月的累计销量，当增加月份时，销量也会变动，函数如何写呢？ 此时输入的函数是=SUM(OFFSET(B1,COUNT(B:B),0,-3))。在下面继续添加月份和销售时，累计销量始终是最近3个月的销售累计。 这个函数offset函数第二个参数COUNT(B:B)表示对B列数据进行计算，因为count函数对文本、空白单元格都不会进行计数，所以B列有多少有数据的单元格，count函数就返回多少。而第四个参数-3，表示从B1单元格偏移到最后一个单元格后，往回折了3个单元格。所以可以表示计算最近三笔销量之和。
六，综合运用：与match、countif、vlookup函数，定义名称结合制作二级下拉菜单并动态查找数据 下图左侧是我国34个省级行政区，300多个市级行政区及对应销量，我们根据右侧的下拉箭头选择省级行政区后，就可以在后面的下拉箭头选择当前省级行政区下的市及对应销量。因为步骤比较多，此处不再对具体操作进行演示，简单说一下操作步骤。以后在介绍动态图表制作的时候会进行详细介绍。 首先把A列的数据复制到E列中(此处为了能看清楚动图，E列已隐藏)。然后选择【数据】选项卡——【删除重复值】，把E列中的每个省份名称只留下一个值。 然后打开【公式】选项卡——【定义名称】，输入函数=5'!$E$2:$E$35，前面这个5'!是引用的工作表名称。名称输入“省”。继续定义名称，输入函数=OFFSET('5'!$B$1,MATCH('5'!$G$2,'5'!$A$2:$A$342,0),0,COUNTIF('5'!$A$2:$A$342,'5'!$G$2),1)，名称输入“市”。 接着在G2单元格中，添加【数据验证】——【序列】——【来源】=省。在H2单元格中，【数据验证】——【序列】——【来源】=市。 最后在I2单元格中输入函数=VLOOKUP(H2,$B:$C,2,0)，就可以实现动态查找了。
这就是本文介绍的offset函数的应用，试着操作一下吧。 |
|
|
