Excel - как отфильтровать список на основе другого списка частичных значений
Как извлечь/отфильтровать инвентарный список (в диапазоне B3:C7) на основе списка поиска с частичными строками (в диапазоне F2:F3)?
Я знаю, что если бы строки фильтрации были полными (не частичными), формула была бы =FILTER(B3:C7, COUNTIF(F2:F3, B3:B7)).
С одной частичной строкой (например, в ячейке F2) формула будет =FILTER(B3:C7,ISNUMBER(SEARCH(F2,B3:B7))).
Но как объединить их, то есть иметь частичные и множественные строки фильтрации?
Ответы 2
Один из вариантов:
Формула в E7:
=FILTER(B3:C7,BYROW(B3:B7&"-",LAMBDA(a,SUM(--(IFERROR(FIND(F2:F3&"-",a),0)=1)))))
@Пётр, ты уверен? Вы получили FILTER(). Какая у вас версия Экселя? Если 365, попробуй обновить.
Да, я. Я использую MS Excel для MS 365 MSO (16.0.13801.21072). Версия самого Excel 2102 (Build 13801.21106 Click-to-Run). Он поддерживает FILTER() и использует разлив, но не распознает LAMBDA() и связанные с ним функции (BYROW() и т. д.).
@Piotr, тогда просто обновите свою версию в главном меню.
Обновлениями управляет мой системный администратор, поэтому это займет некоторое время.
Хотя JvdV Сэр, уже предоставил правильное решение для вашего запроса, однако здесь есть несколько других альтернатив, которые вы также можете попробовать.
• Формула, используемая в ячейке E7
=FILTER(B3:C7,MMULT(--ISNUMBER(SEARCH(TRANSPOSE(F2:F3),B3:B7)),ROW(F2:F3)^0)=1)
• Формула, используемая в ячейке H7
=LET(list,B3:C7,
c,B3:B7,
i,F2:F3,
x,--BYROW(c,
LAMBDA(a,(SUM(COUNTIF(a,"*"&i&"*"))>0))),
FILTER(list,x=1))
• Формула, используемая в ячейке K7
=LET(list,B3:C7,
p,B3:B7,
q,F2:F3,
x,MAP(p,LAMBDA(a,IF(SUM(COUNTIF(a,"*"&q&"*"))>0,a,""))),
FILTER(list,x<>""))
Вы также можете указать, что этот запрос уже был решен ранее в StackOverflow вот ссылка, на которую вы также можете сослаться,
Как искать несколько имен из диапазона и возвращать несколько записей в Excel?
Редактировать
• Формула, используемая в ячейке E7
=FILTER(B3:C7,MMULT(--ISNUMBER(SEARCH("*"&TRANSPOSE(F2:F3)&"*",B3:B7)),ROW(F2:F3)^0))
Однако две другие альтернативы, которыми я поделился, работают без каких-либо изменений, обратите внимание!
Еще одно изменение, чтобы показать, что формула работает, когда значения поиска являются частичными, такими как Пт для Франция и Ge для Германия,
Мне подходит первый вариант (поскольку моя текущая версия MS Excel не поддерживает LAMBDA). Большое спасибо и извините за дублирование вопроса.
Только что обнаружил ограничение - как только появляются дублирующиеся или похожие значения поиска (т. Е. Одно образует частичную строку другого), формула не работает должным образом. Попробуйте ввести Ита вместо Германия в ячейку F2, и вы поймете, что я имею в виду.
В F2 в настоящее время отображается Франция, а не Германия, однако, когда я изменил его на ita, а в ячейке F3 он показывает Italy, он работал с настройками, позвольте мне снова обновить решение для вас!
Отлично, теперь все работает именно так, как я хотел :-). Большое спасибо за вашу помощь!
Хорошо, я отредактировал, чтобы показать все аспекты.
Спасибо, но моя версия Excel 365 не поддерживает функции LAMBDA. Есть ли альтернативный способ?