OFFSET进阶案例

OFFSET函数可以挖掘的空间太大了，之前我们已经写过几篇，同时分享了一篇关于参数数组化的文章，今天我们结合其他函数再来进一步深入学习一下！

“跑马圈地”形容OFFSET再形象不过，后四个参数，行列移动-“跑马”，还可以指定移动需要的高度和宽度，这不就是“圈地”吗？

后面的内容略有难度，若看的过程中，有不适，请及时加上收藏夹“吃灰”，来日巩固基础后再战！

我们先从稍微简单的来说，比如逆序内容

案例1 | 逆序内容

=OFFSET(A$13,13-ROW(A13),)

解析说明：

OFFSET第三参数为偏移行，正数向下，负数向上，A13是12月的位置，13-ROW(A13）,下拉会变成，0，-1，-2……-11，相对于从A13内容依次向上取！

本文由“壹伴编辑器”提供技术支持

我们稍微加大点难度，我们上面逆序了内容，那么可以逆序累计吗？

案例2 | 逆序累计求和

=SUM(OFFSET($B$13,,,-ROW(A1)))

解析说明：

OFFSET第四参数表示需要的结果的高度，同时正数向下，负数向上，

-ROW(A1)下拉，依次表示-1，-2，-3……区域逐步扩大！

逆序求和我们说完，正序大家肯定更没有问题了，但其实没有什么难度，说好的加大难度呢？我们想要的是“内存数组”！

内存数组的逆序累计

▼ 数组公式，非365版本，三键录入

=SUBTOTAL(9,OFFSET($B$13,,,-ROW($1:$12)))

解析说明：

1、OFFSET一个参数数组化后形成三维引用，SUBTOTAL可以对多维引用进行求和！

2、SUBTOTAL要求参数是ref（也就是引用类型即可），第一参数9表示求和

具体SUBTOTAL可以看此专题教程：SUBTOTAL专题教程

3、结果是一个内存数组，也就是说，可以二次计算，比如INDEX获取元素！

下面我们就针对上面的内容数组，继续处理，现在要求出最后几个月累计销售达到50的月份？

最近累计销售达到30的月份

▼数组请“三键”

=INDEX($A$2:$A$13,MAX(IF(SUBTOTAL(9,OFFSET($B$13,,,-ROW($1:$12)))>30,13-ROW(1:12))))

解析说明：

1、公式虽然看上去很长，但是其实核心部分我们在上面就分析过了

SUBTOTAL对应的结果是一个内存数组，所以我们可以和其他元素比较

2、如果对应的累计值满足条件，我们就让其返回对应的行号，注意是逆序，所以需要使用13-ROW(1:12)) = {12;11;10;9;8;7;6;5;4;3;2;1}

3、最后使用INDEX获取指定行号对应的月份即可！

本文由“壹伴编辑器”提供技术支持

下面我们来一个有趣点的案例，关于筛选的问题！比如筛选后的最后一个值是什么？

案例3 | 获取筛选后的最后一个内容

=INDEX(A:A,LOOKUP(1,0/(SUBTOTAL(3,OFFSET(A1,ROW(1:12),))=1),ROW(1:12))+1)

使用MATCH函数处理，可以简化一些：

=INDEX(A:A,MATCH(1,0/(SUBTOTAL(3,OFFSET(A1,ROW(1:12),))=1))+1)

▼ 动画演示效果

解析说明：整体有一点点难！

1、不管是我们第一个公式的LOOKUP还是第二个中的MATCH省略第三参数用法，要求参数升序！不管你怎么处理，他始终认为你是升序排序的！

返回的是小于等于查找值的最大值！

2、我们把第二参使用 0/数组，得到的结果是错误值和0，LOOKUP会过滤掉类型不一致的数组，最后就剩下0，我们使用一个比0大的值1来查找，那么就会一直往下查，值得查找到和他相等的值，如果查找到最后一个还是没有那么返回最后一个值（最大），整体还是遵循“二分法”！

本文由“壹伴编辑器”提供技术支持

今天我们就先到这里，如果你今天的内容都能掌握，那么恭喜你，你至少有函数中级的水平了！

知识星球的老铁们请及时下载本文Excel附件进行练习巩固