Összegzés
Ha több egyezést szeretne külön cellákba külön oszlopokba gyűjteni, használhatja az INDEX és a SMALL tömbképletet. A bemutatott példában az F5 képlete a következő:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),""))
Ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni.
Miután megadta a képletet az első cellában, húzza lefelé és keresztbe a többi cellának kitöltéséhez.
Magyarázat
Megjegyzés: ez a képlet két megnevezett tartományt használ: a "nevek" a C5: C11-re, a "csoportok" pedig a B5: B11-re utal. Ezeket a neveket a fenti képernyőkép is meghatározza.
Ennek a képletnek a lényege a következő: a SMALL funkciót használjuk az "n-edik egyezésnek" megfelelő sorszám előállításához. Miután megvan a sorszám, egyszerűen átadjuk az INDEX függvény függvénynek, amely visszaadja az adott sor értékét.
A trükk az, hogy a SMALL egy tömbmel dolgozik, amelyet az IF dinamikusan konstruál ebben a bitben:
IF(groups=$E5,ROW(names)-MIN(ROW(names))+1)
Ez a kódrészlet a nevezett tartomány "csoportokat" teszteli az E5 értékére. Ha megtalálható, akkor egy sorszámot ad vissza a relatív sorszámok tömbjéből, amelyet a következőkkel hoztak létre:
ROW(names)-MIN(ROW(names))+1
A végeredmény egy tömb, amely számokat tartalmaz, ahol van egyezés, és FALSE, ha nem:
(1; HAMIS; HAMIS; HAMIS; HAMIS; 6; HAMIS)
Ez a tömb KIS-be megy. A kicsi (n) k értéke táguló tartományból származik:
COLUMNS($E$5:E5)
Ha az eredménytáblára másolja, a tartomány kibővül, ami k (n-edik) növekedését okozza. A SMALL függvény minden egyes egyező sorszámot visszaad, amelyet az INDEX függvényhez sor_számként szállítunk, tömbként a megnevezett tartományt "names".
Kezelési hibák
Amikor a COLUMNS visszaadja a k értékét, amely nem létezik, a SMALL #NUM hibát dob. Ez akkor következik be, amikor minden mérkőzés megtörtént. A hiba kiküszöbölése érdekében a képletet beillesztjük az IFERROR függvénybe, hogy elkapjuk a hibákat és egy üres karakterláncot adjunk vissza ("").
