Общая формула
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Резюме
Чтобы извлечь все совпадения на основе частичного совпадения, вы можете использовать формулу массива, основанную на функциях ИНДЕКС и АГРЕГАТ, при поддержке ISNUMBER и SEARCH. В показанном примере формула в G5:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
со следующими именованными диапазонами: "search" = D5, "ct" = D8, "data" = B5: B55.
Примечание: это формула массива, но она не требует Ctrl + Shift + Enter, поскольку AGGREGATE может обрабатывать массивы изначально.
Объяснение
Ядром этой формулы является функция ИНДЕКС, в которой АГРЕГАТ используется для определения "n-го совпадения" для каждой строки в области извлечения:
INDEX(data,nth_match_formula)
Почти вся работа заключается в выяснении и сообщении того, какие строки в «данных» соответствуют строке поиска, а также в сообщении позиции для каждого совпадающего значения в INDEX. Это делается с помощью функции AGGREGATE, настроенной следующим образом:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Первый аргумент, 15, сообщает AGGREGATE вести себя как SMALL и возвращать n-е наименьшее значение. Второй аргумент, 6, позволяет игнорировать ошибки. Третий аргумент - это выражение, которое генерирует массив совпадающих результатов (описанных ниже). Четвертый аргумент, F5, действует как «k» в SMALL, чтобы указать «n-е» значение.
AGGREGATE работает с массивами, а приведенное ниже выражение создает массив для третьего аргумента внутри AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Здесь функция ROW используется для генерации массива относительных номеров строк, а ISNUMBER и SEARCH используются вместе для сопоставления строки поиска со значениями в данных, что создает массив значений TRUE и FALSE.
Хитрый бит - разделить номера строк на результаты поиска. В такой математической операции ИСТИНА ведет себя как 1, а ЛОЖЬ - как ноль. В результате номера строк, связанные с положительным совпадением, делятся на 1 и остаются в силе, в то время как номера строк, связанные с несовпадающими значениями, уничтожаются и становятся ошибками # DIV / 0. Поскольку параметр AGGREGATE настроен на игнорирование ошибок, он игнорирует ошибки # DIV / 0 и возвращает "n-е" наименьшее число из оставшихся значений, используя число в столбце F для "n-го".
Управление производительностью
Как и все формулы массива, эта формула «дорогая» с точки зрения ресурсов с большим набором данных. Чтобы минимизировать влияние на производительность, вся формула INDEX и MATCH заключена в IF следующим образом:
=IF(F5>ct,"",formula)
где именованный диапазон "ct" (D8) содержит эту формулу:
=COUNTIF(data,"*"&search&"*")
Эта проверка останавливает выполнение частей формулы INDEX и AGGREGATE после извлечения всех совпадающих значений.
Формула массива с МАЛЕНЬКИМ
Если в вашей версии Excel нет функции АГРЕГАТ, вы можете использовать альтернативную формулу, основанную на МАЛЕНЬКИЙ и ЕСЛИ:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
