Povzetek
Če želite v ločenih celicah izločiti več zadetkov v ločenih celicah, lahko uporabite matrično formulo, ki temelji na INDEX in SMALL. V prikazanem primeru je formula v F5:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),""))
To je formula matrike in jo je treba vnesti s tipkama Control + Shift + Enter.
Ko vnesete formulo v prvo celico, jo povlecite navzdol in navzgor, da zapolnite druge celice.
Pojasnilo
Opomba: ta formula uporablja dva imenovana obsega: "imena" se nanaša na C5: C11 in "skupine" se nanaša na B5: B11. Ta imena so opredeljena tudi na zgornjem posnetku zaslona.
Bistvo te formule je naslednje: s funkcijo SMALL ustvarimo številko vrstice, ki ustreza "n-ti ujemanju". Ko dobimo številko vrstice, jo preprosto prenesemo v funkcijo funkcije INDEX, ki vrne vrednost v tej vrstici.
Trik je v tem, da SMALL deluje z matriko, ki jo v tem bitu dinamično zgradi IF:
IF(groups=$E5,ROW(names)-MIN(ROW(names))+1)
Ta delček preizkuša imenovane "skupine" obsega za vrednost v E5. Če ga najdemo, vrne številko vrstice iz polja relativnih številk vrstic, ustvarjenega z:
ROW(names)-MIN(ROW(names))+1
Končni rezultat je matrika, ki vsebuje številke, kjer obstaja ujemanje, in FALSE, kjer ne:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Ta matrika gre v SMALL. Vrednost k za SMALL (nth) prihaja iz naraščajočega obsega:
COLUMNS($E$5:E5)
Ob kopiranju v tabelo rezultatov se obseg razširi, zaradi česar se k (nth) poveča. Funkcija SMALL vrne vsako ujemajočo se številko vrstice, ki je funkcija INDEX podana kot številka_vrstice, z matriko imenovanega obsega.
Napake pri ravnanju
Ko stolpec COLUMNS vrne vrednost za k, ki ne obstaja, SMALL vrne napako #NUM. To se zgodi po vseh tekmah. Za zatiranje napake zavijemo formulo v funkcijo IFERROR, da zajamemo napake in vrnemo prazen niz ("").
