Generička formula
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
Sažetak
Za izdvajanje popisa jedinstvenih vrijednosti iz skupa podataka, filtriranih brojanjem ili pojavljivanjem, možete upotrijebiti UNIQUE s FILTER i primijeniti kriterije s funkcijom COUNTIF. U prikazanom primjeru formula u D5 je:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
koji daje 3 jedinstvene vrijednosti koje se više puta pojavljuju u imenovanom rasponu "podaci" (B5: B16).
Napomena: U ovom primjeru izdvajamo jedinstveni popis vrijednosti koji se pojavljuju više puta. Drugim riječima, stvaramo popis duplikata :) Jezik je pomalo zbunjujući.
Obrazloženje
Ovaj primjer koristi funkciju UNIQUE zajedno s funkcijom FILTER. Osnovniji primjer možete vidjeti ovdje.
Trik je u ovom slučaju primijeniti kriterije na funkciju FILTER kako bi se dopustile samo vrijednosti na temelju broja pojavljivanja. Radeći iznutra prema van, to se ovdje radi s COUNTIF i funkcijom FILTER:
FILTER(data,COUNTIF(data,data)>1)
Rezultat iz COUNTIF-a niz je brojanja poput ovog:
(3;1;3;3;2;1;1;3;1;2;3;3)
koji se provjeravaju logičkom usporedbom> 1 da bi se dobio niz ili vrijednosti TRUE / FALSE:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Obavijest TRUE odgovara vrijednostima u podacima koje se pojavljuju više puta. Ovaj se niz vraća u FILTER kao argument uključivanja koji se koristi za filtriranje podataka. FILTER kao rezultat vraća drugi niz:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Ovaj se niz vraća izravno funkciji UNIQUE kao argument niza . Obavijest o 12 izvornih vrijednosti, preživjelo je samo 8.
UNIQUE zatim uklanja duplikate i vraća konačni niz:
("red";"green";"blue")
Ako se vrijednosti u B5: B16 promijene, izlaz će se odmah ažurirati.
Broj> 2
Formula u F5, koja navodi boje koje se pojavljuju najmanje 2 puta u izvornim podacima, je:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Dinamički opseg izvora
Budući da su podaci (B5: B15) uobičajeni imenovani raspon, neće im se promijeniti veličina ako se podaci dodaju ili izbrišu. Da biste koristili dinamički raspon koji će automatski promijeniti veličinu po potrebi, možete upotrijebiti Excel tablicu ili stvoriti dinamički imenovani raspon s formulom.
