☰

Subtraia valores apenas se todos forem números

tag-icon • tag-icon microsoft-excel-2010 worksheet-function
Subtraia valores apenas se todos forem números

Quero subtrair quatro células no Excel de uma célula específica, mas apenas se alguma das quatro células tiver um valor. Mais especificamente no meu caso, a fórmula na célula L2 está atualmente

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

mas só quero que isso seja calculado quando alguma ou todas as células E2,G2,I2,K2 tiverem um valor. O que estou faltando na fórmula para fazer L2 retornar 0, a menos que o acima seja verdadeiro?

Responder1

O que você precisa fazer – e o que deveríamos ter pedido que você fizesse há dois dias – é dar alguns exemplos de quais resultados você deseja para quais insumos. Sim, eu sei que você nos deu alguns, mas deveriam ter sidoeditadona questão; comentários são um péssimo lugar para dados tabulares e fórmulas longas, e são oerradolocal para informações necessárias para entender a questão. De qualquer forma, criei alguns dados de amostra para você. Observe que nem todos estão corretos. Eu sei que não estão todos corretos, porque são inconsistentes, mas também teria dificuldade em corrigi-los todos porque ainda estou lutando para entender o que você quer.

        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

Se os casos feenão fierefletirem o que você deseja, temos um sério problema de comunicação. Se você nunca, jamais, em um zilhão de anos, terá uma experiência real0in E2, G2, I2, ou K2, então não precisamos realmente nos preocupar com case foe. Então, a pergunta de US$ 40.000 é: você quer fum( L2= 40) ou  foo( L2= 0)?

fum( L2= 40)

Isto é simples. Defina L2qualquer um dos seguintes,

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

que são equivalentes.

foo( L2= 0)

Um pouco mais longo, mas igualmente óbvio:

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

Isso pode parecer familiar, pois é a resposta de Teylyn combinada com meu comentário (“Se você deseja que a subtração seja calculada se alguma ou todas as células tiverem um valor,… use ORem vez de AND.”)

Huh? O que?

Sua fórmula original,

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

está (como Teylyn apontou) chamando a AND()função com cinco parâmetros:

  • ISNUMBER(E2),
  • ISNUMBER(G2),
  • ISNUMBER(I2),
  • ISNUMBER(K2), e
  • (em branco).

Pois bem, se um valor em branco for usado como número no Excel, ele será tratado como se fosse 0 (zero). É por isso que as fumfórmulas “ ” funcionam: as células que estão em branco são tratadas como se contivessem 0. Da mesma forma, se um valor em branco for usado como booleano (ou seja, valor lógico) no Excel, ele será tratado como se fosse FALSO. Então sua fórmula funciona como

=SE(E(ÉNÚMERO(E2),ÉNÚMERO(G2),ÉNÚMERO(I2),ÉNÚMERO(K2),FALSO),"",D2-E2-G2-I2-K2)

Como é , o acima se reduz aAND(anything, FALSE)FALSE

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

ou simplesmente

=D2-E2-G2-I2-K2

Isso pode parecer familiar, pois é a fumfórmula “”.

Já estamos lá?

Se eu perdi algum aspecto misterioso da sua pergunta, por favoredite sua perguntapara esclarecê-lo; por exemplo, adicione dados de amostra onde nenhuma das respostas acima fornece os resultados desejados.

Responder2

Sua fórmula retorna uma string vazia quando todas as condições são atendidas. Acho que você quer o cálculo naquele lugar. Há uma vírgula perdida na função AND().

De acordo com sua descrição verbal, a fórmula que você provavelmente precisa é

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

Isso só fará o cálculo se todas as células contiverem um valor.

mas só quero que isso seja calculado quando alguma ou todas as células E2,G2,I2,K2 tiverem um valor.

Você precisa se decidir. Se quiser fazer o cálculo quando qualquer célula contém um valor, você não precisa de uma instrução IF. Nesse caso, basta fazer a subtração.

Responder3

Estou pensando que você pode precisar de uma linha auxiliar. Você poderia colocá-lo logo abaixo das células em questão usando a fórmula

=SE(ÉNÚMERO(E2),E2,0)
. Então você poderia simplesmente fazer uma subtração simples na linha auxiliar. Claro, essa fórmula seria inserida no E3. E, aliás, uma instrução AND é verdadeira apenas se todos os seus argumentos forem verdadeiros, portanto, sua fórmula só funcionaria quando todas as células nela contivessem números. Se apenas uma célula contivesse um valor não numérico, a fórmula seria avaliada como falsa e não seria calculada. FWIW Falso = 0; Verdadeiro = Não falso.

informação relacionada