Üldine valem
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Kokkuvõte
Kõigi vastete eraldamiseks osalise vaste põhjal saate kasutada massiivivalemit, mis põhineb funktsioonidel INDEX ja AGGREGATE, toetusega ISNUMBER ja SEARCH. Näidatud näites on valem G5-s järgmine:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
järgmiste nimega vahemikega: "otsing" = D5, "ct" = D8, "andmed" = B5: B55.
Märkus: see on massiivivalem, kuid see ei vaja juhtimist + shift + enter, kuna AGGREGATE saab massiive käsitleda loomulikult.
Selgitus
Selle valemi tuumaks on funktsioon INDEX, kusjuures AGGREGATE-i kasutatakse väljavõtte piirkonna iga rea n-nda vaste väljaselgitamiseks:
INDEX(data,nth_match_formula)
Peaaegu kogu töö seisneb selles, et välja selgitada ja aru anda, millised "andmete" read vastavad otsingustringile, ja iga vastava väärtuse positsiooni aruandlus INDEXile. Seda tehakse funktsiooniga AGGREGATE, mis on konfigureeritud järgmiselt:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Esimene argument, 15, käsib AGGREGATE'il käituda nagu VÄIKE ja tagastada n-nda väikseima väärtuse. Teine argument 6 on võimalus vigu eirata. Kolmas argument on avaldis, mis genereerib vastavate tulemuste massiivi (kirjeldatakse allpool). Neljas argument F5 toimib SMALL-is nagu "k", et määrata "n" väärtus.
AGGREGATE töötab massiividel ja allpool olev lause ehitab massiivi AGGREGATE-i kolmanda argumendi jaoks:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Siin kasutatakse funktsiooni ROW suhteliste ridade numbrite massiivi genereerimiseks ning ISNUMBER ja SEARCH kasutatakse koos otsingutulemite vastendamiseks andmetes olevate väärtustega, mis genereerib väärtuste TRUE ja FALSE massiivi.
Nutikas bitt on jagada rea numbrid otsingutulemitega. Sellises matemaatikaoperatsioonis käitub TRUE nagu 1 ja FALSE nagu null. Tulemuseks on positiivse vastega seotud reanumbrite jagamine ühega ja need jäävad toimingu ellu, samas kui mittevastavate väärtustega seotud reanumbrid hävitatakse ja neist saab # DIV / 0 viga. Kuna AGGREGATE on seatud vigu eirama, eirab see # DIV / 0 tõrkeid ja tagastab ülejäänud väärtustes väikseima arvu "n", kasutades veerus F olevat arvu "n".
Toimivuse juhtimine
Nagu kõik massiivivalemid, on ka see valem suure andmekogumiga ressursside osas "kallis". Jõudlusmõjude minimeerimiseks on kogu INDEX ja MATCH valem mähitud IF-i järgmiselt:
=IF(F5>ct,"",formula)
kus nimetatud vahemik "ct" (D8) omab seda valemit:
=COUNTIF(data,"*"&search&"*")
See kontroll peatab valemi INDEX ja AGGREGATE osa töötamise, kui kõik sobivad väärtused on välja tõmmatud.
Massiivvalem koos VÄIKSEGA
Kui teie Exceli versioonil pole funktsiooni AGGREGATE, saate kasutada alternatiivset valemit, mis põhineb VÄIKESEL ja IF:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.
