☰

Вычитать значения только если все они являются числами

tag-icon • tag-icon microsoft-excel-2010 worksheet-function
Вычитать значения только если все они являются числами

Я хочу вычесть четыре ячейки в 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не отражают то, что вы хотите, у нас серьезная проблема с коммуникацией. Если вы никогда, никогда, за миллион лет не будете иметь фактического0в E2, G2, I2, или 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)

которые эквивалентны.

foo( L2= 0)

Немного длиннее, но столь же очевидно:

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

Это может показаться знакомым, так как это ответ Тейлин в сочетании с моим комментарием («Если вы хотите, чтобы вычитание вычислялось, если какая-либо или все ячейки имеют значение, … используйте ORвместо AND.»)

А? Что?

Ваша оригинальная формула,

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

(как указал Тейлин) вызывает AND()функцию с пятью параметрами:

  • ISNUMBER(E2),
  • ISNUMBER(G2),
  • ISNUMBER(I2),
  • ISNUMBER(K2), и
  • (пустой).

Ну, если пустое значение используется как число в Excel, оно обрабатывается так, как будто это 0 (ноль). Вот почему формулы « fum» работают: пустые ячейки обрабатываются так, как будто они содержат 0. Аналогично, если пустое значение используется как логическое значение (т. е. логическое значение) в Excel, оно обрабатывается так, как будто это ЛОЖЬ. Поэтому ваша формула действует как

=ЕСЛИ(И(ЕСТЬЧИСЛО(E2),ЕСТЬЧИСЛО(G2),ЕСТЬЧИСЛО(I2),ЕСТЬЧИСЛО(K2),ЛОЖЬ),"",Д2-Е2-Г2-И2-К2)

Так как is , то вышесказанное сводится к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

Я думаю, вам может понадобиться вспомогательная строка. Вы можете поместить ее прямо под ячейками, о которых идет речь, используя формулу

=ЕСЛИ(ЕЧИСЛО(E2),E2,0)
. Затем вы можете просто выполнить простое вычитание в строке помощника. Конечно, эта формула будет введена в E3. И, кстати, оператор AND истинен только в том случае, если все его аргументы истинны, поэтому ваша формула будет работать только тогда, когда все ее ячейки содержат числа. Если только одна ячейка содержит нечисловое значение, формула будет оценена как ложная и не будет вычислена. FWIW False = 0; True = Not False.

Связанный контент