Общая формула
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Резюме
Если вы хотите проверить ячейку, чтобы увидеть, содержит ли она все элементы в списке, вы можете сделать это с помощью формулы, в которой используется функция ПОИСК, с помощью функций ЕЧИСЛО, СУММПРОИЗВ и СЧЁТ.
Контекст
Допустим, у вас есть список текстовых строк в диапазоне B5: B8, и вы хотите узнать, содержат ли эти ячейки все слова из другого диапазона, E5: E7.
Вы можете создать формулу, которая использует вложенные операторы IF для проверки каждого элемента, но она не будет хорошо масштабироваться, если вам нужно много чего искать. Каждый раз, когда вы добавляете слово для поиска, вам нужно будет добавить еще один вложенный IF и настроить круглые скобки.
Решение
Решение состоит в том, чтобы создать формулу, учитывающую все совпадения одновременно. Получив это, мы просто сравниваем это количество с количеством элементов, которые мы ищем. Если они совпадают, мы знаем, что ячейка содержит все элементы.
В показанном примере мы используем следующую формулу:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Объяснение
Ключ в этом фрагменте:
ISNUMBER(SEARCH(things,B5)
Это основано на другой формуле (подробно описанной здесь), которая просто проверяет ячейку на наличие единственной подстроки. Если ячейка содержит подстроку, формула возвращает ИСТИНА. Если нет, формула возвращает ЛОЖЬ.
Однако, если мы дадим той же формуле список вещей (в данном случае мы используем именованный диапазон под названием «вещи», E5: E7), он вернет нам список значений ИСТИНА / ЛОЖЬ, по одному для каждого элемента в . В результате получается массив, который выглядит так:
(ИСТИНА; ИСТИНА; ИСТИНА)
Где каждое ИСТИНА представляет найденный элемент, а каждое ЛОЖЬ представляет собой не найденный элемент.
Мы можем принудительно установить значения TRUE / FALSE на 1 и 0 с помощью двойного отрицательного (-, также называемого двойным унарным):
--ISNUMBER(SEARCH(things,B5))
что дает такой массив:
(1; 1; 1)
Затем мы обрабатываем этот массив с помощью SUMPRODUCT, что дает нам общую сумму. Если эта сумма равна количеству элементов в названном диапазоне «вещи», мы знаем, что нашли все, и можем вернуть ИСТИНА. Мы делаем это, сравнивая два числа напрямую. Мы получаем количество непустых ячеек в «вещах» с помощью COUNTA:
=COUNTA(things)
С жестко запрограммированным списком
Не требуется, чтобы вы использовали диапазон для своего списка вещей. Если вы ищете лишь небольшое количество вещей, вы можете использовать список в формате массива, который называется константой массива. Например, если вы просто ищете красный, синий и зеленый цвета, вы можете использовать («красный», «синий», «зеленый») вот так:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
