Obecný vzorec
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
souhrn
Chcete-li extrahovat seznam jedinečných hodnot ze sady dat filtrovaných podle počtu nebo výskytu, můžete použít UNIQUE s FILTER a použít kritéria s funkcí COUNTIF. V zobrazeném příkladu je vzorec v D5:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
který vydává 3 jedinečné hodnoty, které se v pojmenovaném rozsahu „data“ (B5: B16) objeví více než jednou.
Poznámka: V tomto příkladu extrahujeme jedinečný seznam hodnot, které se objevují více než jednou. Jinými slovy, vytváříme seznam duplikátů :) Jazyk je poněkud matoucí.
Vysvětlení
Tento příklad používá funkci UNIQUE společně s funkcí FILTER. Zde vidíte základní příklad.
Trik v tomto případě spočívá v použití kritérií na funkci FILTER tak, aby byly povoleny pouze hodnoty založené na počtu výskytů. Při práci zevnitř ven se to provádí pomocí funkce COUNTIF a funkce FILTER zde:
FILTER(data,COUNTIF(data,data)>1)
Výsledek z COUNTIF je pole počtů takto:
(3;1;3;3;2;1;1;3;1;2;3;3)
které jsou kontrolovány logickým porovnáním> 1, čímž se získá pole nebo TRUE / FALSE hodnoty:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Všimněte si, že PRAVDA odpovídá hodnotám v datech, které se objevují více než jednou. Toto pole je vráceno FILTRU jako argument zahrnutí , který se používá k filtrování dat. FILTER jako výsledek vrátí další pole:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Toto pole se vrací přímo do UNIQUE funkce jako argument pole . Všimněte si 12 původních hodnot, pouze 8 přežije.
UNIQUE poté odstraní duplikáty a vrátí konečné pole:
("red";"green";"blue")
Pokud se hodnoty v B5: B16 změní, výstup se okamžitě aktualizuje.
Počet> 2
Vzorec v F5, který uvádí barvy, které se ve zdrojových datech objevují alespoň dvakrát, je:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Dynamický rozsah zdroje
Protože data (B5: B15) jsou normální pojmenovaný rozsah, velikost se nezmění, pokud budou data přidána nebo odstraněna. Chcete-li použít dynamický rozsah, který v případě potřeby automaticky změní velikost, můžete použít tabulku Excel nebo vytvořit dynamický pojmenovaný rozsah pomocí vzorce.
