【原】两个怪函数CELL和INDIRECT撞出神的奇：不用辅助行忽略隐藏列求和

  文 前 推 荐   

忽略隐藏列进行求和

用INDIRECT函数跨表求和

无所不能的SUMPRODUCT函数

增删工作表后多表汇总如何自动更新？

编按：

今天小窝介绍不用单元格宽度辅助行也能忽略隐藏列求和的方法。之所以如此，主要是CELL和INDIRECT函数都“怪”，怪到一起变神奇了。

很久前在部落窝官网上曾分享过《如何忽略隐藏列求和》文章。

忽略隐藏值求和，大家第一反应就是用SUBTOTAL函数，但是SUBTOTAL函数只对隐藏行有效。因此文章分享了一个运用CELL函数建立单元格width宽度辅助行，然后再条件求和，实现忽略隐藏列求和的方法。

文章最后提到不可能只用一个公式,如：

=SUMPRODUCT((CELL("width",B1:G1>0)*B2:G2)

实现忽略隐藏列求和，因为CELL函数只返回引用区域，如B1:G1中左上单元格，即B1单元格对应的值。

真的就只能建立辅助行后才能实现忽略隐藏列求和吗？

小窝测试了多种方法，最终发现搭配INDIRECT函数可以不用辅助行。

在H2中输入如下公式并向下填充：

=SUMPRODUCT((CELL("width",INDIRECT("r1c"&COLUMN(B:G),0))>0)*B2:G2)

我们来测试一下。

隐藏C列，然后按F9刷新，合计值忽略了C列。

顺便说说INDIRECT和CELL两个函数的怪处。

INDIRECT的怪处：不能单独输出数组。

它可以用数组实现对多个区域的引用，但它不能单独输出数组。譬如，引用B2:G2的数据，输入下方公式结果是错误的：

=INDIRECT("r2c"&COLUMN(B:G),0)

在编辑栏中选中"r2c"&COLUMN(B:G)按F9，可以看到一组地址：

这些地址单独用于INDIRECT函数，都能正确引用数据：

如果在INDIRECT外嵌套聚合函数，如求和、最大最小值函数等，也能得到正确结果：

CELL的怪处：在新版本中计算单元格width，需嵌套聚合函数。

新版本支持动态数组，CELL("width")会输出两个值：宽度值和是否是默认宽度的逻辑值。

如果要建立单元格宽度辅助行，必须外套MAX函数后才能向右填充：

没想到两怪合一，反而解决了不用辅助行的问题，也是神奇哈。
OK，关于不用宽度辅助行实现忽略隐藏列求和的方法就介绍这么多。