Vispārēja formula
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
Kopsavilkums
Lai no datu kopas iegūtu unikālu vērtību sarakstu, kas filtrēts pēc skaita vai sastopamības, varat izmantot UNIQUE ar FILTER un lietot kritērijus ar funkciju COUNTIF. Parādītajā piemērā formula D5 ir:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
kas izsniedz 3 unikālās vērtības, kas vairākkārt parādās nosauktajā diapazonā "dati" (B5: B16).
Piezīme: Šajā piemērā mēs iegūstam unikālu vērtību sarakstu, kas parādās vairāk nekā vienu reizi. Citiem vārdiem sakot, mēs veidojam dublikātu sarakstu :) Valoda ir nedaudz mulsinoša.
Paskaidrojums
Šajā piemērā tiek izmantota funkcija UNIQUE kopā ar funkciju FILTER. Šeit jūs varat redzēt vienkāršāku piemēru.
Šajā gadījumā triks ir piemērot kritērijus funkcijai FILTER, lai atļautu tikai vērtības, kuru pamatā ir gadījumu skaits. Strādājot no iekšpuses, tas tiek darīts ar COUNTIF un FILTER funkciju šeit:
FILTER(data,COUNTIF(data,data)>1)
Rezultāts no COUNTIF ir šādu skaitļu masīvs:
(3;1;3;3;2;1;1;3;1;2;3;3)
kas tiek pārbaudīti ar loģisko salīdzinājumu> 1, lai iegūtu masīvu vai TRUE / FALSE vērtības:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Paziņojums TRUE atbilst vērtībām datos, kas parādās vairāk nekā vienu reizi. Šis masīvs tiek atgriezts FILTER kā arguments iekļaut , ko izmanto datu filtrēšanai. Rezultātā FILTER atgriež citu masīvu:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Šis masīvs tiek tieši atgriezts funkcijā UNIKĀLA kā masīva arguments. Paziņojums par 12 sākotnējām vērtībām izdzīvo tikai 8
UNIQUE pēc tam noņem dublikātus un atgriež pēdējo masīvu:
("red";"green";"blue")
Ja vērtības B5: B16 mainās, izeja tiks nekavējoties atjaunināta.
Skaitīt> 2
Formula F5, kurā uzskaitītas krāsas, kas avota datos parādās vismaz 2 reizes, ir šāda:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Dinamiskā avota diapazons
Tā kā dati (B5: B15) ir parasts nosauktais diapazons, to lielums netiks mainīts, ja dati tiks pievienoti vai dzēsti. Lai izmantotu dinamisko diapazonu, kura nepieciešamības gadījumā automātiski mainīs izmērus, varat izmantot Excel tabulu vai izveidot dinamisku nosaukto diapazonu ar formulu.
