Как использовать вложенный IF (AND) в формуле массива Excel?

Как заставить работать вложенное «И» внутри «ЕСЛИ» в формуле массива?

Я свел свою проблему к следующему примеру: Таблица Excel, показывающая формулу массива с вложенным ANDПримечание: приведенное выше изображение было обновлено и теперь включает фигурные скобки формулы массива

Вверху справа у нас есть критерии поиска в L3 («цвет») и L4 («форма»). Слева столбец D содержит рабочие формулы соответствия как для цвета, так и для формы в списке элементов. В первой таблице показана правильная работа формулы соответствия без использования формулы массива.

Во второй таблице показана формула массива, соответствующая цвету.

В третьей таблице показана формула массива, соответствующая форме.

Справа - моя попытка использовать оба критерия в формуле массива, комбинируя их с AND.

ЕСЛИ значение в столбце цвета соответствует критериям цвета (L3), а значение в столбце формы соответствует критериям формы (L4), тогда я хочу увидеть «СООТВЕТСТВИЕ!».

Я нашел обходной путь: объедините значения и критерии, а затем сопоставьте их в одном IF. Я чувствую, что должен быть лучший способ ... например, если бы И работал так, как ожидалось!

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

Ключ к ответу на эти вопросы - написать что-то, что работает как формула массива, который вводится нажатием CTRL + SHIFT + ENTER после ввода формулы в ячейку. Excel автоматически добавит фигурные скобки, чтобы указать, что это формула массива.


excel array-formulas excel-formula
person Steven T. Snyder    schedule 11.01.2014    source источник
comment
Ответ @Simoco правильный. Поскольку and принимает массивы в качестве аргументов и возвращает одно значение (все ли они истинны?), Он не применяется к каждому элементу отдельно, как функция (например, if), принимающая только отдельные значения. Разные контексты, разные способы работы, как ожидалось. :-) Умножение массивов (неявно TRUE- ›1, FALSE-› 0) является эквивалентом массива and (возвращает ненулевое значение, если ни один из них не равен нулю), а добавление (неотрицательных) массивов является эквивалентом массива or (возвращает ненулевой, если какой-либо ненулевой).   -  person maybeWeCouldStealAVan    schedule 13.01.2014
comment
@maybeWeCouldStealAVan, ответ simoco работает, но не в формуле массива. Я обновил исходный вопрос, чтобы было понятнее, о чем я прошу. Я заметил, что на изображении не показаны фигурные скобки, поэтому, возможно, это было нечетко. Ответ BernardSaucier действительно работает в фигурных скобках, поэтому он отвечает на вопросы. Могут быть другие / лучшие решения, которые более масштабируемы для более длинных аргументов AND ().   -  person Steven T. Snyder    schedule 13.01.2014
comment
@maybeWeCouldStealAVan Я допустил ошибку в своем листе - на самом деле ответ simoco отлично работает!   -  person Steven T. Snyder    schedule 13.01.2014

Ответы (3)


arrow_upward
13
arrow_downward

Попробуйте использовать следующую формулу массива:

=IF(($J$16:$J$22=$L$3)*($K$16:$K$22=$L$4),"MATCH!","-")

вот ссылка на тестовую книгу.

person Dmitry Pavliv    schedule 11.01.2014
comment
При этом IF не используется в качестве формулы массива, и это решение не работает, если используется формула массива. Я только что понял, что в моем исходном вопросе фигурные скобки формулы массива не отображаются, так что, возможно, это сбивает с толку вопрос. Я починю это. - person Steven T. Snyder; 13.01.2014
comment
Это точно формула массива, и она мне подходит. Если вы выберете диапазон L16: L22 и с выбранным диапазоном введите формулу в строке формул, а затем нажмите CTRL + SHEET + ENTER - вы получите нужный результат (и появятся фигурные скобки). Я тестировал формулу много раз - person Dmitry Pavliv; 13.01.2014
comment
Использование оператора * не превращает его в формулу массива (office.microsoft.com/en-us/excel-help/) - person Steven T. Snyder; 13.01.2014
comment
and this solution breaks if an array formula is used - Как ломается ??? Вы действительно пробовали использовать его как функцию массива? И что вы собираетесь делать, если у вас будет 10 критериев (а не 2) - вы собираетесь использовать 10 вложенных IF ?? - person Dmitry Pavliv; 13.01.2014
comment
Это просто дает результат MATCH для всего. Вы пробовали это как формулу массива? Вот скриншот: imgur.com/QMU41eM - person Steven T. Snyder; 13.01.2014
comment
Хм .. вот мой пример учебного пособия .. попробуйте, пожалуйста. dropbox.com/s/7m1amdjfxo4lzq8/Simoco_Test.xlsx - person Dmitry Pavliv; 13.01.2014
comment
Спасибо. Ваш рабочий лист работает отлично, и формула вводится как формула массива. Введены те же формулы, но в моем тестовом листе они не работают. Странный! - person Steven T. Snyder; 13.01.2014
comment
Получил, чтобы работать; Когда я нажимал SHIFT + CTRL + ENTER, у меня были выделены не все ячейки. Не могли бы вы отредактировать свой ответ, включив ссылку на свою книгу? ТАК не позволяет мне изменить свой голос без редактирования ответа. - person Steven T. Snyder; 13.01.2014
comment
Спасибо! Я изменил свой принятый ответ на этот, так как он масштабируется по большему количеству критериев. - person Steven T. Snyder; 13.01.2014
comment
Действительно, мое решение было очень сложно масштабировать! +1 к этой отличной альтернативе. - person Bernard Saucier; 13.01.2014
comment
Это очень элегантное и отличное решение. а для операций ИЛИ используйте + вместо *. POI: формулы массива на Mac: shift-command-enter - person Paris Char; 14.03.2015

arrow_upward
5
arrow_downward

Вы можете вложить IF в value of true IF, чтобы получить IF something AND somethingelse. Например :

=IF(A1="something", IF(A2="somethingelse", "A1 is something and A2 is somethingelse", "A1 is something but A2 is not something else"), "A1 is nothing and A2 is nothingelse")

Дополнительные сведения о том, как опробовать этот код: напишите «что-то» (без кавычек) в A1 и «somethingelse» в A2, поместите эту формулу в любую другую ячейку этого листа. Поиграйте с этими значениями, чтобы увидеть, где вы попадаете во вложенные IF.

person Bernard Saucier    schedule 11.01.2014
comment
Это прекрасно работает! Конкретное решение в моем вопросе {=IF($J16:$J22=$L$3,IF($K16:$K22=$L$4,"MATCH","-"),"-")} - person Steven T. Snyder; 13.01.2014

arrow_upward
0
arrow_downward

Вы можете использовать один из этих двух способов вместо AND: поставить «*» между условиями, а второй способ - использовать вложенные If. например один из них:

=IF(($J$16:$J$22=$L$3)*($K$16:$K$22=$L$4),"MATCH!","-")

or

=IF($J$16:$J$22=$L$3, if($K$16:$K$22=$L$4,"MATCH!","-"))

Логически для использования OR или XOR вы должны поставить () + () или - соответственно. Я их сейчас не тестировал.

С уважением, M

person Mahhdy    schedule 24.01.2018