Общая формула
=FILTER(data,ISNUMBER(MATCH(rng1,rng2,0)),"No data")
Резюме
Чтобы отфильтровать данные для включения только тех записей, в которых столбец равен одному из многих значений, вы можете использовать функцию ФИЛЬТР вместе с функциями ЕЧИСЛО и функцией ПОИСКПОЗ. В показанном примере формула в F5:
=FILTER(data,ISNUMBER(MATCH(color,list,0)),"No data")
где «данные» (B5: D15), «цвет» (C5: C15) и «список» (J5: J7) являются именованными диапазонами.
Объяснение
Функция ФИЛЬТР может фильтровать данные, используя логическое выражение, предоставленное в качестве аргумента «включить». В этом примере этот аргумент создается с помощью выражения, которое использует функции ЕЧИСЛО и ПОИСКПОЗ:
=ISNUMBER(MATCH(color,list,0))
ПОИСКПОЗ настроен на поиск каждого цвета в C5: C15 в меньшем диапазоне J5: J7. Функция ПОИСКПОЗ возвращает такой массив:
(1;#N/A;#N/A;#N/A;2;3;2;#N/A;#N/A;#N/A;3)
Номера уведомлений соответствуют положению «найденных» цветов («красный», «синий» или «черный»), а ошибки соответствуют строкам, в которых целевой цвет не был найден. Чтобы получить результат ИСТИНА или ЛОЖЬ, этот массив передается в функцию ЕЧИСЛО, которая возвращает:
(TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Приведенный выше массив передается в функцию FLITER в качестве аргумента включения, а FILTER возвращает только строки, соответствующие значению TRUE.
С жестко запрограммированными значениями
Приведенный выше пример создан со ссылками на ячейки, где целевые цвета введены в диапазоне J5: J7. Однако, используя константу массива, вы можете жестко закодировать значения в формулу, подобную этой, с тем же результатом:
=FILTER(data,ISNUMBER(MATCH(color,("red","blue","black"),0)),"No data")
