Загальна формула
=SUMPRODUCT(--(rng=values))>0
Резюме
Щоб перевірити, чи існує одне з кількох значень у діапазоні комірок, можна скористатися формулою, заснованою на функції SUMPRODUCT. У наведеному прикладі формула в комірці F5 має вигляд:
=SUMPRODUCT(--(rng=B5:D5))>0
де "rng" - названий діапазон H4: H10 і містить значення, які потрібно шукати.
Пояснення
Кожен елемент у rng порівнюється з кожним елементом у значеннях, і результатом є масив значень TRUE або FALSE.
Подвійний від’ємник змусить значення TRUE та FALSE до 1 та 0 відповідно. Оскільки SUMPRODUCT отримує лише один масив, він просто додає елементи в масиві і повертає результат.
Логічно, будь-який результат, більший за нуль, означає, що в діапазоні існує принаймні одне значення. Отже, останній крок - це оцінка результату SUMPRODUCT, щоб побачити, чи більше воно за нуль. Будь-який результат, більший за нуль, повертає TRUE, а будь-який результат, рівний нулю, повертає FALSE.
З жорстко закодованими значеннями
Ви також можете жорстко закодувати значення пошуку у формулу, використовуючи те, що відоме як "константа масиву". Наприклад, якщо ви хочете знайти 3 значення: червоний, блакитний та пурпуровий всередині діапазону H2: H8, ви можете використовувати:
=SUMPRODUCT(--(H2:H8=("red","cyan","magenta")))>0
У наведеному вище прикладі ("червоний", "блакитний", "пурпуровий") - константа масиву, що є одним із способів подати кілька значень в одному аргументі.
Часткові збіги або підрядки
Наведена вище формула перевіряє лише еквівалентність і не знайде часткових збігів чи підрядків у діапазоні. Якщо вам потрібно шукати підрядки, ви можете використовувати цю формулу замість цього.
