souhrn
Chcete-li extrahovat více shod do samostatných buněk, můžete v samostatných sloupcích použít maticový vzorec založený na INDEXU a MALÉM. V zobrazeném příkladu je vzorec v F5:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),""))
Toto je vzorec pole a musí být zadán pomocí kláves Control + Shift + Enter.
Poté, co zadáte vzorec do první buňky, přetáhněte jej dolů a napříč a vyplňte ostatní buňky.
Vysvětlení
Poznámka: Tento vzorec používá dva pojmenované rozsahy: „names“ odkazuje na C5: C11 a „groups“ označuje B5: B11. Tyto názvy jsou také definovány na snímku obrazovky výše.
Podstata tohoto vzorce je tato: pomocí funkce MALÉ generujeme číslo řádku odpovídající „n-té shodě“. Jakmile máme číslo řádku, jednoduše ho předáme do funkce funkce INDEX, která vrací hodnotu v daném řádku.
Trik spočívá v tom, že SMALL pracuje s polem, které je v tomto bitu dynamicky konstruováno IF:
IF(groups=$E5,ROW(names)-MIN(ROW(names))+1)
Tento úryvek testuje pojmenované „skupiny“ rozsahu pro hodnotu v E5. Pokud je nalezen, vrátí číslo řádku z pole relativních čísel řádků vytvořených pomocí:
ROW(names)-MIN(ROW(names))+1
Konečným výsledkem je pole, které obsahuje čísla, kde existuje shoda, a FALSE, pokud ne:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Toto pole jde do MALÉHO. Hodnota k pro SMALL (n-tý) pochází z rozšiřujícího se rozsahu:
COLUMNS($E$5:E5)
Při kopírování přes tabulku výsledků se rozsah rozšíří, což způsobí přírůstek k (n-té). Funkce SMALL vrací každé odpovídající číslo řádku, které je dodáváno funkci INDEX jako row_num, s pojmenovaným rozsahem „names“ jako maticí.
Zpracování chyb
Když COLUMNS vrátí hodnotu pro k, která neexistuje, hodí SMALL chybu # ČÍSLO. K tomu dochází poté, co proběhly všechny zápasy. Abychom chybu potlačili, zabalíme vzorec do funkce IFERROR, abychom zachytili chyby a vrátili prázdný řetězec ("").
