Zhrnutie
Ak chcete extrahovať viac zhôd do samostatných buniek, v samostatných riadkoch môžete použiť maticový vzorec založený na INDEXE a MALOM. V zobrazenom príklade je vzorec v E5:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
Toto je vzorec poľa a musí sa zadať pomocou kombinácie klávesov Control + Shift + Enter.
Po zadaní vzorca do prvej bunky potiahnite ho smerom dole a naprieč a vyplňte ďalšie bunky.
Vysvetlenie
Poznámka: tento vzorec používa dva pomenované rozsahy: „names“ odkazuje na C4: C11 a „groups“ označuje B4: B11. Tieto názvy sú definované aj na snímke obrazovky vyššie.
Podstata tohto vzorca je táto: používame funkciu MALÉ na získanie čísla riadku, ktorý zodpovedá „n-tej zhode“. Keď už máme číslo riadku, jednoducho ho odovzdáme do funkčnej funkcie INDEX, ktorá vráti hodnotu v danom riadku.
Trik spočíva v tom, že SMALL pracuje s poľom, ktoré je v tomto bite dynamicky konštruované IF:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
Tento úryvok testuje pomenované rozsahové skupiny pre hodnotu v E4. Ak sa nájde, vráti „normalizované“ číslo riadku z poľa čísel riadkov vytvorených pomocou tejto časti vzorca:
ROW(names)-MIN(ROW(names))+1
Výsledkom je pole, ktoré obsahuje čísla riadkov, kde existuje zhoda, a FALSE, ak nie. Pole vyzerá asi takto:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Toto pole ide do MALÉHO. Hodnota k pre MALÉ (n-té) pochádza z rozširujúceho sa rozsahu:
ROWS($E$5:E5)
Pri kopírovaní do tabuľky s výsledkami sa rozsah rozširuje, čo spôsobí prírastok k (n.). Funkcia SMALL vráti každé zodpovedajúce číslo riadku, ktoré sa dodáva funkcii INDEX ako row_num, s pomenovaným rozsahom „names“ ako poľom.
Zaobchádzanie s chybami
Keď ROWS vráti hodnotu pre k, ktorá neexistuje, SMALL hodí chybu # ČÍSLO. Stáva sa to po odohraní všetkých zápasov. Na potlačenie chyby používame funkciu IFERROR na zachytenie chyby a vrátenie prázdneho reťazca („“).
