Генеричка формула
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Резиме
Да бисте издвојили сва подударања на основу делимичног подударања, можете користити формулу низа засновану на функцијама ИНДЕКС и АГГРЕГАТЕ, уз подршку од ИСУМБЕР и СЕАРЦХ. У приказаном примеру, формула у Г5 је:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
са следећим именованим опсезима: „претрага“ = Д5, „цт“ = Д8, „подаци“ = Б5: Б55.
Напомена: ово је формула низа, али не захтева контролу + схифт + ентер, јер АГГРЕГАТЕ може нативно да обрађује низове.
Објашњење
Језгро ове формуле је функција ИНДЕКС, са АГРЕГАТЕ-ом који се користи за откривање „н-тог подударања“ за сваки ред у области издвајања:
INDEX(data,nth_match_formula)
Готово сав посао састоји се у откривању и извештавању који се редови у „подацима“ подударају са низом за претрагу, и извештавању о положају за сваку подударну вредност у ИНДЕКС. То се ради са АГГРЕГАТЕ функцијом конфигурисаном овако:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Први аргумент, 15, говори АГРЕГАТЕ-у да се понаша МАЛО и враћа н-те најмање вредности. Други аргумент, 6, је опција за занемаривање грешака. Трећи аргумент је израз који генерише низ резултата који се подударају (описано у наставку). Четврти аргумент, Ф5, делује као „к“ у МАЛОМ како би се одредила „н-та“ вредност.
АГГРЕГАТЕ делује на низовима, а доњи израз гради низ за трећи аргумент унутар АГГРЕГАТЕ:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Овде се функција РЕД користи за генерисање низа релативних бројева редова, а ИСУМБЕР и СЕАРЦХ се користе заједно за подударање низа за претрагу са вредностима у подацима, што генерише низ вредности ТРУЕ и ФАЛСЕ.
Паметна бит је подела бројева редова према резултатима претраге. У оваквој математичкој операцији, ТРУЕ се понаша као 1, а ФАЛСЕ као нула. Резултат су бројеви редова повезани са позитивним подударањем подељени са 1 и преживе операцију, док су бројеви редова повезани са неподударајућим вредностима уништени и постали # ДИВ / 0 грешке. Будући да је АГГРЕГАТЕ подешен да игнорише грешке, он занемарује грешке # ДИВ / 0 и враћа "н-ти" најмањи број у преосталим вредностима, користећи број у колони Ф за "нтх".
Управљање учинком
Као и све формуле низа, и ова формула је „скупа“ у погледу ресурса са великим скупом података. Да би се утицаји на перформансе свели на минимум, цела формула ИНДЕКС и МАТЦХ умотана је у АКО овако:
=IF(F5>ct,"",formula)
где именовани опсег „цт“ (Д8) садржи ову формулу:
=COUNTIF(data,"*"&search&"*")
Ова провјера зауставља покретање ИНДЕКС и АГРЕГАТЕ дијела формуле након што се изваде све одговарајуће вриједности.
Формула низа са МАЛИМ
Ако ваша верзија Екцел-а нема функцију АГРЕГАТЕ, можете користити алтернативну формулу засновану на СМАЛЛ и ИФ:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер.
