OFFSET函数可以挖掘的空间太大了,之前我们已经写过几篇,同时分享了一篇关于参数数组化的文章,今天我们结合其他函数再来进一步深入学习一下! “跑马圈地”形容OFFSET再形象不过,后四个参数,行列移动-“跑马”,还可以指定移动需要的高度和宽度,这不就是“圈地”吗? 后面的内容略有难度,若看的过程中,有不适,请及时加上收藏夹“吃灰”,来日巩固基础后再战! 我们先从稍微简单的来说,比如逆序内容 案例1 | 逆序内容
解析说明: OFFSET第三参数为偏移行,正数向下,负数向上,A13是12月的位置,13-ROW(A13),下拉会变成,0,-1,-2……-11,相对于从A13内容依次向上取! 本文由“壹伴编辑器”提供技术支持 我们稍微加大点难度,我们上面逆序了内容,那么可以逆序累计吗? 案例2 | 逆序累计求和
解析说明: OFFSET第四参数表示需要的结果的高度,同时正数向下,负数向上, -ROW(A1)下拉,依次表示-1,-2,-3……区域逐步扩大! 逆序求和我们说完,正序大家肯定更没有问题了,但其实没有什么难度,说好的加大难度呢?我们想要的是“内存数组”! 内存数组的逆序累计▼ 数组公式,非365版本,三键录入
解析说明: 1、OFFSET一个参数数组化后形成三维引用,SUBTOTAL可以对多维引用进行求和! 2、SUBTOTAL要求参数是ref(也就是引用类型即可),第一参数9表示求和 具体SUBTOTAL可以看此专题教程:SUBTOTAL专题教程 3、结果是一个内存数组,也就是说,可以二次计算,比如INDEX获取元素! 下面我们就针对上面的内容数组,继续处理,现在要求出最后几个月累计销售达到50的月份? 最近累计销售达到30的月份▼数组请“三键”
解析说明: 1、公式虽然看上去很长,但是其实核心部分我们在上面就分析过了 SUBTOTAL对应的结果是一个内存数组,所以我们可以和其他元素比较 2、如果对应的累计值满足条件,我们就让其返回对应的行号,注意是逆序,所以需要使用13-ROW(1:12)) = {12;11;10;9;8;7;6;5;4;3;2;1} 3、最后使用INDEX获取指定行号对应的月份即可! 本文由“壹伴编辑器”提供技术支持 下面我们来一个有趣点的案例,关于筛选的问题!比如筛选后的最后一个值是什么? 案例3 | 获取筛选后的最后一个内容
使用MATCH函数处理,可以简化一些:
▼ 动画演示效果 解析说明:整体有一点点难! 1、不管是我们第一个公式的LOOKUP还是第二个中的MATCH省略第三参数用法,要求参数升序!不管你怎么处理,他始终认为你是升序排序的! 返回的是小于等于查找值的最大值! 2、我们把第二参使用 0/数组,得到的结果是错误值和0,LOOKUP会过滤掉类型不一致的数组,最后就剩下0,我们使用一个比0大的值1来查找,那么就会一直往下查,值得查找到和他相等的值,如果查找到最后一个还是没有那么返回最后一个值(最大),整体还是遵循“二分法”! 本文由“壹伴编辑器”提供技术支持 今天我们就先到这里,如果你今天的内容都能掌握,那么恭喜你,你至少有函数中级的水平了! 知识星球的老铁们请及时下载本文Excel附件进行练习巩固 |
|