Na extrahovanie viacerých zhôd do oddelených buniek môžete v oddelených stĺpcoch použiť vzorec poľa na základe INDEX a SMALL. V uvedenom príklade vzorec v F5 je:
{= IFERROR ( INDEX (names, SMALL ( IF (groups=$E5, ROW (names)- MIN ( ROW (names))+1), COLUMNS ($E:E5))),'')}
Toto je vzorec poľa a musíte ho zadať kombináciou klávesov Ctrl + Shift + Enter.
čo je logický test v programe Excel
Potom, čo zadáte vzorec do prvej bunky, potiahnite ju nadol a naprieč, aby ste vyplnili ostatné bunky.
Vysvetlenie
Poznámka: tento vzorec používa dva pomenované rozsahy : „názvy“ označujú C5: C11 a „skupiny“ označujú B5: B11. Tieto názvy sú definované aj na snímke obrazovky vyššie.
Podstata tohto vzorca je nasledovná: pomocou funkcie SMALL generujeme číslo riadka zodpovedajúce „n -tej zhode“. Akonáhle máme číslo riadka, jednoducho ho odovzdáme do funkcie funkcie INDEX, ktorá vráti hodnotu v tomto riadku.
Ide o to, že SMALL pracuje s poľom, ktoré v tomto bite dynamicky skonštruuje IF:
IF (groups=$E5, ROW (names)- MIN ( ROW (names))+1)
Tento úryvok testuje pomenované skupiny „skupín“ na hodnotu v E5. Ak je nájdený, vráti číslo riadku z poľa relatívnych čísel riadkov vytvoreného pomocou:
ROW (names)- MIN ( ROW (names))+1
Konečným výsledkom je pole, ktoré obsahuje čísla, kde je zhoda, a FALSE, ak nie:
ak bunka obsahuje nejaký text
{1FALSEFALSEFALSEFALSE6FALSE}
Toto pole ide do SMALL. Hodnota k pre SMALL (n -tý) pochádza z an rozšírenie sortimentu :
COLUMNS ($E:E5)
Pri kopírovaní do tabuľky výsledkov sa rozsah rozšíri, čo spôsobí k (n -tý) prírastok. Funkcia SMALL vracia každé zodpovedajúce číslo riadka, ktoré je dodané funkcii INDEX ako riadok_číslo, pričom pomenovaný rozsah „názvy“ ako pole.
Manipulačné chyby
Keď COLUMNS vráti hodnotu pre k, ktorá neexistuje, SMALL vyvolá chybu #ČÍSLO. Stáva sa to po odohraní všetkých zápasov. Na potlačenie chyby zabalíme vzorec do funkcie IFERROR, aby zachytil chyby a vrátil znak prázdny reťazec ('').
Autor Dave Bruns