Všeobecný vzorec
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
Zhrnutie
Ak chcete extrahovať zoznam jedinečných hodnôt zo sady údajov filtrovaných podľa počtu alebo výskytu, môžete použiť položku UNIQUE s funkciou FILTER a použiť kritériá pomocou funkcie COUNTIF. V zobrazenom príklade je vzorec v D5:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
ktorá vydáva 3 jedinečné hodnoty, ktoré sa v pomenovanom rozsahu „data“ (B5: B16) objavia viackrát.
Poznámka: V tomto príklade extrahujeme jedinečný zoznam hodnôt, ktoré sa vyskytujú viackrát. Inými slovami, vytvárame zoznam duplikátov :) Jazyk je trochu mätúci.
Vysvetlenie
V tomto príklade sa používa funkcia UNIQUE spolu s funkciou FILTER. Tu si môžete pozrieť základnejší príklad.
Trik v tomto prípade spočíva v použití kritérií na funkciu FILTER tak, aby boli hodnoty povolené iba na základe počtu výskytov. Pri práci zvnútra von sa to deje pomocou COUNTIF a funkcie FILTER tu:
FILTER(data,COUNTIF(data,data)>1)
Výsledok z COUNTIF je pole týchto počtov:
(3;1;3;3;2;1;1;3;1;2;3;3)
ktoré sú kontrolované logickým porovnaním> 1, aby sa získalo pole alebo TRUE / FALSE hodnoty:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Všimnite si, že TRUE zodpovedá hodnotám v dátach, ktoré sa objavia viackrát. Toto pole sa vráti do FILTERA ako argument zahrnutia , ktorý sa používa na filtrovanie údajov. FILTER ako výsledok vráti ďalšie pole:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Toto pole sa vracia priamo do funkcie UNIQUE ako argument poľa . Všimnite si 12 pôvodných hodnôt, iba 8 prežije.
UNIQUE potom odstráni duplikáty a vráti konečné pole:
("red";"green";"blue")
Ak sa hodnoty v B5: B16 zmenia, výstup sa okamžite aktualizuje.
Počet> 2
Vzorec v F5, ktorý uvádza farby, ktoré sa v zdrojových údajoch objavujú najmenej dvakrát, je:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Dynamický rozsah zdrojov
Pretože údaje (B5: B15) sú normálnym pomenovaným rozsahom, pri pridaní alebo odstránení údajov sa ich veľkosť nezmení. Ak chcete použiť dynamický rozsah, ktorý v prípade potreby automaticky zmení veľkosť, môžete použiť tabuľku Excel alebo vytvoriť dynamický pomenovaný rozsah pomocou vzorca.
