Общая формула
=SUMPRODUCT(--(rng=values))>0
Резюме
Чтобы проверить, существует ли одно из нескольких значений в диапазоне ячеек, вы можете использовать формулу, основанную на функции СУММПРОИЗВ. В показанном примере формула в ячейке F5:
=SUMPRODUCT(--(rng=B5:D5))>0
где «rng» - именованный диапазон H4: H10, содержащий значения для поиска.
Объяснение
Каждый элемент в rng сравнивается с каждым элементом в values, и результатом является массив значений TRUE или FALSE.
Двойное отрицание приведет к тому, что значения ИСТИНА и ЛОЖЬ будут равны 1 и 0 соответственно. Поскольку SUMPRODUCT получает только один массив, он просто складывает элементы в массиве и возвращает результат.
Логически любой результат больше нуля означает, что в диапазоне существует по крайней мере одно значение. Итак, последний шаг - оценить результат SUMPRODUCT, чтобы убедиться, что он больше нуля. Любой результат больше нуля возвращает ИСТИНА, а любой результат, равный нулю, возвращает ЛОЖЬ.
С жестко запрограммированными значениями
Вы также можете жестко закодировать значения поиска в формулу, используя так называемую «константу массива». Например, если вы хотите найти 3 значения: красный, голубой и пурпурный внутри диапазона H2: H8, вы можете использовать:
=SUMPRODUCT(--(H2:H8=("red","cyan","magenta")))>0
В приведенном выше примере («красный», «голубой», «пурпурный») - это константа массива, которая является одним из способов предоставить несколько значений в одном аргументе.
Частичные совпадения или подстроки
Приведенная выше формула проверяет только эквивалентность и не находит частичных совпадений или подстрок в диапазоне. Если вам нужно искать подстроки, вы можете использовать эту формулу.
