两个怪函数CELL和INDIRECT撞出神的奇:不用辅助行忽略隐藏列求和
编按:
今天小窝介绍不用单元格宽度辅助行也能忽略隐藏列求和的方法。之所以如此,主要是CELL和INDIRECT函数都“怪”,怪到一起变神奇了。
忽略隐藏值求和,大家第一反应就是用SUBTOTAL函数,但是SUBTOTAL函数只对隐藏行有效。因此文章分享了一个运用CELL函数建立单元格width宽度辅助行,然后再条件求和,实现忽略隐藏列求和的方法。=SUMPRODUCT((CELL("width",B1:G1>0)*B2:G2)实现忽略隐藏列求和,因为CELL函数只返回引用区域,如B1:G1中左上单元格,即B1单元格对应的值。小窝测试了多种方法,最终发现搭配INDIRECT函数可以不用辅助行。=SUMPRODUCT((CELL("width",INDIRECT("r1c"&COLUMN(B:G),0))>0)*B2:G2)顺便说说INDIRECT和CELL两个函数的怪处。它可以用数组实现对多个区域的引用,但它不能单独输出数组。譬如,引用B2:G2的数据,输入下方公式结果是错误的:=INDIRECT("r2c"&COLUMN(B:G),0)在编辑栏中选中"r2c"&COLUMN(B:G)按F9,可以看到一组地址:这些地址单独用于INDIRECT函数,都能正确引用数据:如果在INDIRECT外嵌套聚合函数,如求和、最大最小值函数等,也能得到正确结果:CELL的怪处:在新版本中计算单元格width,需嵌套聚合函数。新版本支持动态数组,CELL("width")会输出两个值:宽度值和是否是默认宽度的逻辑值。如果要建立单元格宽度辅助行,必须外套MAX函数后才能向右填充:没想到两怪合一,反而解决了不用辅助行的问题,也是神奇哈。
OK,关于不用宽度辅助行实现忽略隐藏列求和的方法就介绍这么多。
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。