Общая формула
=SUMPRODUCT(--(A1=things))>0
Резюме
Если вы хотите проверить ячейку, чтобы узнать, соответствует ли она одному из нескольких значений, вы можете сделать это с помощью формулы, в которой используется функция СУММПРОИЗВ.
Контекст
Предположим, у вас есть список текстовых строк в диапазоне B5: B11, и вы хотите проверить каждую ячейку на соответствие другому списку элементов в диапазоне E5: E9. Другими словами, для каждой ячейки в B5: B11 вы хотите знать: совпадает ли эта ячейка с чем-либо из элементов в E5: E9?
Вы можете начать построение большой формулы на основе вложенных операторов IF, но формула массива на основе SUMPRODUCT - более простой и понятный подход.
Решение
Решение состоит в том, чтобы создать формулу, которая будет проверять несколько значений и возвращать список значений ИСТИНА / ЛОЖЬ. Как только у нас есть это, мы можем обработать этот список (фактически, массив) с помощью SUMPRODUCT.
Формула, которую мы используем, выглядит так:
=SUMPRODUCT(--(B5=things))>0
Объяснение
Ключ в этом фрагменте:
--(B5=things)
который просто сравнивает значение в B5 с каждым значением в именованном диапазоне "things". Поскольку мы сравниваем B5 с массивом (т. Е. Именованным диапазоном «вещей», E5: E11), результатом будет массив значений ИСТИНА / ЛОЖЬ, например:
(ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ)
Если у нас есть хотя бы одно ИСТИНА в массиве, мы знаем, что B5 равно по крайней мере одному элементу в списке, поэтому, чтобы принудительно установить значения ИСТИНА / ЛОЖЬ до 1 и 0, мы используем двойной отрицательный (-, также называемый двойным унарный). После этого принуждения мы имеем следующее:
(1; 0; 0; 0; 0)
Теперь мы обрабатываем результат с помощью SUMPRODUCT, который складывает элементы в массив. Если мы получим любой ненулевой результат, у нас есть по крайней мере одно совпадение, поэтому мы используем> 1, чтобы получить окончательный результат TRUE или FALSE.
С жестко запрограммированным списком
Не требуется, чтобы вы использовали диапазон для своего списка вещей. Если вы ищете лишь небольшое количество вещей, вы можете использовать список в формате массива, который называется константой массива. Например, если вы просто ищете красный, синий и зеленый цвета, вы можете использовать («красный», «синий», «зеленый») вот так:
--(B5=("red","blue","green"))
Работа с лишним пространством
Если в тестируемых ячейках есть лишнее пространство, они не будут соответствовать друг другу. Чтобы удалить все лишнее пространство, вы можете изменить формулу для использования функции TRIM следующим образом:
=SUMPRODUCT(--(TRIM(A1)=things))>0
