有效地用NA替换0 | 有效地用NA替换

本文介绍了有效地用NA替换0的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试寻找一种在Excel公式中有效地用NA()替换零的方法.我知道以下作品:

I am trying to find a way to efficiently replace zero with NA() in an Excel formula. I know the following works:

=IF(FORMULA = 0, NA(), FORMULA)

但是我的问题是，这将导致FORMULA执行两次.在某些情况下，在大型表中=SUMIFS()的时间可能更长.

But my problem is that this will cause FORMULA to execute twice. I have cases where this may be a longer =SUMIFS() in a giant table.

所以我想要:

没有VBA
只有基数FORMULA计算一次

No VBA
Only have the base FORMULA calculate once

我以为首先尝试使用SUBSTITUTE()将"0"替换为会触发值错误的内容，然后将所有内容包装在IFERROR()中.这显然失败了，因为据我所知，不能强制SUBSTITUTE()检查完整的单词匹配(因此100会触发错误).

I thought at first to try to use SUBSTITUTE() to replace "0" with something that would trigger a value error, and then just wrap all of that within IFERROR(). That obviously fails since SUBSTITUTE() cannot be forced (to my knowledge) to check for full word match (so 100 would trigger the error).

这可能吗?我已经想了好多年，但是决定重新考虑一下.

Is this possible? I have thought for years it was not, but decided to put some thought back into it.

推荐答案

通常的答案是

=IFERROR(f'(f(FORMULA)), AlternateValue)

其中，f(FORMUALA)对于要为其替代值的FORMULA值返回错误(任何错误都会发生).

where f(FORMUALA) returns an error (any error will do) for values of FORMULA that you want an alternate value for.

f'(...)是f(...)的倒数，因此f'(f(FORMULA))返回FORMULA以获得其他值.

And f'(...) is the inverse of f(...), so f'(f(FORMULA)) returns FORMULA for other values.

确保将第一个功能应用于整个FORMULA.将其包含在()中可以保证这一点.

Ensure the first function is applied to the whole of FORMULA. Enclosing it in () guarantees that.

第二，确保以正确的顺序应用这两个功能，这也是使用()实现的.

Secondly, ensure the two functions are applied in the correct order, also achieved using ().

在这种情况下，您需要0的替代值，以便可以使用

In this case you want an alternate value for 0 so you can use

=IFERROR(1/(1/(FORMULA)), NA())

这篇关于有效地用NA替换0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！