如果值为零，则显示空白单元格的最佳方法 [英] Best way to show blank cell if value if zero

问题描述

=COUNTIFS(Orders!$T:$T,$B4)

是给出0或+ ve结果的代码

is a code that gives 0 or a +ve result

我在1500个单元格中使用它，使工作表充满0s

I use this across 1500 cells which makes the sheet gets filled with 0s

我想使用以下公式删除零点

I'd like to remove the Zeros by using the following formula

if(COUNTIFS(Orders!$T:$T,$B3,Orders!$F:$F,""&P$1&"*")=0,
"",
COUNTIFS(Orders!$T:$T,$B3,Orders!$F:$F,""&P$1&"*"))

这将两次计算每个公式并增加计算时间。

This calculates every formula twice and increases the calculation time.

如果值是0，如何在1个公式中执行此操作-保留为空-否则显示答案

How can we do this in 1 formula where if the value is 0 - keep empty - otherwise display the answer

推荐答案

我建议使用此单元格功能：

I suggest this cell-function:

=IFERROR(1/(1/COUNTIFS(Orders!$T:$T,$B4)))

编辑：
我不确定要添加什么作为解释。基本上用空单元格代替复杂计算的结果（如果结果为0），则可以将复杂函数包装在

I'm not sure what to add as explanation. Basically to replace the result of a complex calculation with blank cells if it results in 0, you can wrap the complex function in

IFERROR(1/(1/  ComplexFunction()   ))

它通过两次取反（1 / X）的结果，因此在除DIV0错误产生的0以外的所有情况下都返回原始结果。然后，此错误将被IFERROR捕获，从而导致一个空白单元格。

It works by twice taking the inverse (1/X) of the result, thus returning the original result in all cases except 0 where a DIV0 error is generated. This error is then caught by IFERROR to result in a blank cell.

此方法的优点是它不需要两次计算复杂函数，因此可以可以显着提高速度/可读性，并且不会像自定义数字格式那样愚弄输出，如果在其他功能中使用此单元格，则可能会很重要。

The advantage of this method is that it doesn't need to calculate the complex function twice, so can give a significant speed/readability increase, and doesn't fool the output like a custom number format which can be important if this cell is used in further functions.

这篇关于如果值为零，则显示空白单元格的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！