Formula generică
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
rezumat
Pentru a extrage o listă de valori unice dintr-un set de date, filtrate după număr sau apariție, puteți utiliza UNIQUE cu FILTRU și puteți aplica criterii cu funcția COUNTIF. În exemplul prezentat, formula din D5 este:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
care afișează cele 3 valori unice care apar de mai multe ori în intervalul numit „date” (B5: B16).
Notă: În acest exemplu, extragem o listă unică de valori care apar de mai multe ori. Cu alte cuvinte, creăm o listă de duplicate :) Limba este oarecum confuză.
Explicaţie
Acest exemplu folosește funcția UNIQUE împreună cu funcția FILTER. Puteți vedea un exemplu mai simplu aici.
Trucul în acest caz este de a aplica criterii funcției FILTER pentru a permite numai valori bazate pe numărul de apariții. Lucrând din interior spre exterior, acest lucru se face cu COUNTIF și funcția FILTER aici:
FILTER(data,COUNTIF(data,data)>1)
Rezultatul de la COUNTIF este o serie de numere ca aceasta:
(3;1;3;3;2;1;1;3;1;2;3;3)
care sunt verificate cu comparația logică> 1 pentru a produce o matrice sau valori TRUE / FALSE:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Notă TRUE corespunde valorilor din date care apar de mai multe ori. Această matrice este returnată la FILTER ca argument include , utilizat pentru a filtra datele. FILTER returnează o altă matrice ca rezultat:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Această matrice este returnată direct la funcția UNIQUE ca argument al matricei . Observați cele 12 valori originale, doar 8 supraviețuiesc.
UNIQUE apoi elimină duplicatele și returnează matricea finală:
("red";"green";"blue")
Dacă valorile din B5: B16 se schimbă, ieșirea se va actualiza imediat.
Număr> 2
Formula din F5, care listează culorile care apar de cel puțin 2 ori în datele sursă, este:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Gama de surse dinamice
Deoarece datele (B5: B15) sunt un interval denumit normal, nu se va redimensiona dacă datele sunt adăugate sau șterse. Pentru a utiliza un interval dinamic care se va redimensiona automat atunci când este necesar, puteți utiliza un tabel Excel sau puteți crea un interval dinamic denumit cu o formulă.
