Excel中，OFFSET函数的使用方法( 二 ) _Excel

文章插图

OFFSET与count函数组合求最近几个累计数
如下图所示，我想要计算最近3个月的累计销量，当增加月份时，销量也会变动，函数如何写呢？
此时输入的函数是=SUM(OFFSET(B1,COUNT(B:B),0,-3)) 。在下面继续添加月份和销售时，累计销量始终是最近3个月的销售累计。
这个函数offset函数第二个参数COUNT(B:B)表示对B列数据进行计算，因为count函数对文本、空白单元格都不会进行计数，所以B列有多少有数据的单元格，count函数就返回多少。而第四个参数-3，表示从B1单元格偏移到最后一个单元格后，往回折了3个单元格。所以可以表示计算最近三笔销量之和。

文章插图

综合运用：OFFSET与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)，就可以实现动态查找了。