Общая формула
=SUMPRODUCT(--(ISNA(MATCH(rngA,rngB,0))))>0
Резюме
Чтобы проверить, содержит ли диапазон какие-либо значения (т.е. хотя бы одно значение), не входящие в другой диапазон, вы можете использовать функцию СУММПРОИЗВ с ПОИСКПОЗ и ISNA.
В показанном примере формула в F6:
=SUMPRODUCT(--(ISNA(MATCH(lista,listb,0))))>0
Объяснение
Обычно функция ПОИСКПОЗ получает одно поисковое значение и возвращает одно совпадение, если оно есть. В этом случае, однако, мы даем MATCH массив для значения поиска, поэтому он вернет массив результатов, по одному на элемент в массиве поиска. МАТЧ настроен на «точное совпадение». Если совпадение не найдено, ПОИСКПОЗ вернет ошибку # Н / Д. После выполнения матча он возвращается примерно так:
=SUMPRODUCT(--(ISNA((3;5;6;2;#N/A;4))))>0
Мы используем это преимущество, используя функцию ISNA для проверки любых ошибок # N / A.
После ISNA у нас есть:
=SUMPRODUCT(--((FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)))>0
Мы используем двойной отрицательный (двойной унарный) оператор для преобразования значений ИСТИНА ЛОЖЬ в единицы и нули, что дает нам следующее:
=SUMPRODUCT((0;0;0;0;1;0))>0
SUMPRODUCT затем суммирует элементы в массиве, и результат сравнивается с нулем, чтобы получить результат ИСТИНА или ЛОЖЬ.
