Google表格公式未根据使用范围显示0 [英] Google Sheet formula not presenting 0 according to the Used range

问题描述

使用下面的公式可以很好地工作，但是我在所附工作表中突出显示的地方并没有提到0.

Using below formula that is working perfectly but it is not mentioning 0 where as i highlighted in attached sheet.

我只希望E:E范围结束时该公式也应结束，它也应适用于E:E范围.

I just want that where E:E range ends this formula should end too it should work with E:E range.

例如:E5有值，公式将给出结果，否则，如果E6为空，则公式为0，那么公式也为空.

for Example: E5 has values, formula will give result accordingly otherwise it would be 0 if E6 is empty then formula will also be empty.

原始公式:

=ArrayFormula(IFERROR(1/(1/(IF(E3:E="",,LEN(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE({Jan!C3:AG, Feb!C3:AG, Mar!C3:AG, Apr!C3:AG, May!C3:AG, Jun!C3:AG, Jul!C3:AG, Aug!C3:AG, Sep!C3:AG, Oct!C3:AG, Nov!C3:AG,Dec!C3:AG}),,9^9))), " ", )))))))

https://docs.google.com/spreadsheets/d/1pgVGJxkw39uelgPU9ceZLXflnP8KaFGou4rJfSsfVXc/edit#gid = 0

推荐答案

尝试一下:

=ArrayFormula(IF(LEN(E3:E),IF(E3:E="",,LEN(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE({Jan!C3:AG, Feb!C3:AG, Mar!C3:AG, Apr!C3:AG, May!C3:AG, Jun!C3:AG, Jul!C3:AG, Aug!C3:AG, Sep!C3:AG, Oct!C3:AG, Nov!C3:AG,Dec!C3:AG}),,9^9))), " ", ))),""))

这篇关于Google表格公式未根据使用范围显示0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！