仅当所有值都是数字时才减去值

仅当所有值都是数字时才减去值

我想从 Excel 中的一个特定单元格中减去四个单元格,但前提是四个单元格中任何一个单元格都有值。更具体地说,在我的例子中,单元格 L2 中的公式目前为

=IF(AND(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2),),"",D2-E2-G2-I2-K2)

但我只希望当单元格 E2、G2、I2、K2 中的任何一个或所有单元格都有值时才计算。除非上述情况属实,否则公式中缺少什么才能让 L2 返回 0?

答案1

你需要做的——也是我们两天前就应该要求你做的——是给出一些例子,说明你希望从哪些输入得到什么结果。是的,我知道你已经给了我们一些例子,但它们应该是編輯进入问题;评论是表格数据和长公式的糟糕地方,它们是错误的了解问题所需的信息。无论如何,我为你创建了一些示例数据。请注意,它们并不都是正确的。我知道它们并不都是正确的,因为它们不一致,但我也很难让它们全部正确,因为我仍然很难理解你想要什么。

        Case       D        E        G        I        K        L (Result)
        fee       40        1                                  39
        fie       40        0.01                               39.99
        foe       40        0                                  40
        fum       40                                           40
        foo       40                                            0

如果案例fee不能fie反映出你想要的,那我们就有严重的沟通问题了。如果你永远也不会在万年内真正拥有一个0E2G2I2或中K2,那么我们实际上不需要担心 的情况foe。因此,价值 40,000 美元的问题是:您想要fum( L2= 40) 还是 foo( L2= 0)?

fum( L2= 40)

这很简单。设置L2为以下任意一项,

  • =D2-E2-G2-I2-K2
  • =D2-(E2+G2+I2+K2)
  • =D2-SUM(E2,G2,I2,K2)

它们是等效的。

fooL2= 0)

稍微长一点,但同样明显:

=IF(OR(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2)), D2-E2-G2-I2-K2, 0)

这可能看起来很熟悉,因为这是 Teylyn 的回答与我的评论的结合(“如果您希望在任意或所有单元格都有值时计算减法,...请使用OR而不是AND。”)

啊,什么?

你原来的配方,

=IF(AND(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2),),"",D2-E2-G2-I2-K2)

就像 Teylyn 指出的那样,调用这个AND()函数有五个参数:

  • ISNUMBER(E2)
  • ISNUMBER(G2)
  • ISNUMBER(I2)
  • ISNUMBER(K2), 和
  • (空白的)。

好吧,如果在 Excel 中将空白值用作数字,则将其视为 0(零)。这就是“ fum”公式起作用的原因:空白单元格被视为包含 0。同样,如果在 Excel 中将空白值用作布尔值(即逻辑值),则将其视为 FALSE。因此,您的公式的作用就像

=如果(与(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2),错误的),“”,D2-E2-G2-I2-K2)

由于是,以上简化为AND(anything, FALSE)FALSE

=IF(FALSE, "", D2-E2-G2-I2-K2)

或者简单地

=D2-E2-G2-I2-K2

这可能看起来很熟悉,因为它是“ fum”公式。


我们到了吗?

如果我遗漏了你问题中一些比较深奥的细节,请编辑你的问题澄清一下;例如,当以上两个答案都无法给出您想要的结果时,添加样本数据。

答案2

当所有条件都满足时,您的公式会返回一个空字符串。我认为您希望在那里进行计算。AND() 函数中有一个多余的逗号。

根据您的口头描述,您可能需要的公式是

=IF(AND(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2)),D2-E2-G2-I2-K2,0)

仅当所有单元格都包含值时才会进行计算。

但我只希望当单元格 E2、G2、I2、K2 中的任何一个或所有单元格都有值时才计算它。

您需要下定决心。如果您想在任何单元格包含值时进行计算,则不需要 IF 语句。在这种情况下,只需进行减法即可。

答案3

我认为您可能需要一个辅助行。您可以使用公式将其放在相关单元格的正下方

=如果(ISNUMBER(E2),E2,0)
然后,您只需在辅助行上进行简单的减法即可。当然,该公式将输入到 E3 中。顺便说一句,AND 语句只有在其所有参数都为真时才为真,因此您的公式只有在其中的所有单元格都包含数字时才有效。如果只有一个单元格包含非数字值,则公式将计算为假,并且不会计算。FWIW False = 0;True = 非 False。

相关内容