Обща формула
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Обобщение
За да извлечете всички съвпадения въз основа на частично съвпадение, можете да използвате формула на масив, базирана на функциите INDEX и AGGREGATE, с поддръжка от ISNUMBER и SEARCH. В показания пример формулата в G5 е:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
със следните именувани диапазони: "търсене" = D5, "ct" = D8, "данни" = B5: B55.
Забележка: това е формула на масив, но тя не изисква управление + shift + enter, тъй като AGGREGATE може да обработва масиви от самото начало.
Обяснение
Сърцевината на тази формула е функцията INDEX, с AGREGATE, използвана за определяне на „n-тото съвпадение“ за всеки ред в областта за извличане:
INDEX(data,nth_match_formula)
Почти цялата работа е в намирането и отчитането на това кои редове в „данни“ съответстват на низа за търсене и отчитане на позицията на за всяка съвпадаща стойност с INDEX. Това се прави с функцията AGGREGATE, конфигурирана по следния начин:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Първият аргумент, 15, казва на AGGREGATE да се държи като МАЛКИ и връща n-тите най-малки стойности. Вторият аргумент, 6, е опция за игнориране на грешки. Третият аргумент е израз, който генерира масив от съвпадащи резултати (описани по-долу). Четвъртият аргумент, F5, действа като "k" в МАЛКО, за да посочи стойността "n-та".
AGGREGATE работи върху масиви и изразът по-долу изгражда масив за третия аргумент вътре в AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Тук функцията ROW се използва за генериране на масив от относителни номера на редове, а ISNUMBER и SEARCH се използват заедно за съвпадение на низа за търсене със стойности в данните, което генерира масив от TRUE и FALSE стойности.
Умният бит е да разделите номерата на редовете на резултатите от търсенето. При математическа операция като тази TRUE се държи като 1, а FALSE се държи като нула. Резултатът е, че номера на редове, свързани с положително съвпадение, се делят на 1 и оцеляват в операцията, докато номерата на редове, свързани с несъответстващи стойности, се унищожават и стават # DIV / 0 грешки. Тъй като AGGREGATE е настроен да игнорира грешки, той игнорира грешките # DIV / 0 и връща "n-то" най-малкото число в останалите стойности, като използва числото в колона F за "nth".
Управление на изпълнението
Както всички формули на масиви, тази формула е „скъпа“ по отношение на ресурси с голям набор от данни. За да се сведе до минимум въздействието върху производителността, цялата формула INDEX и MATCH е обвита в IF по следния начин:
=IF(F5>ct,"",formula)
където посоченият диапазон "ct" (D8) съдържа тази формула:
=COUNTIF(data,"*"&search&"*")
Тази проверка спира изпълнението на частта INDEX и AGREGATE на формулата, след като бъдат извлечени всички съвпадащи стойности.
Формула на масив с МАЛКИ
Ако вашата версия на Excel няма функцията AGGREGATE, можете да използвате алтернативна формула, базирана на SMALL и IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.
