Formula generica
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
Sommario
Per estrarre un elenco di valori univoci da un set di dati, filtrato per conteggio o occorrenza, è possibile utilizzare UNIQUE con FILTER e applicare criteri con la funzione COUNTIF. Nell'esempio mostrato, la formula in D5 è:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
che restituisce i 3 valori univoci che appaiono più di una volta nell'intervallo denominato "dati" (B5: B16).
Nota: in questo esempio, estraiamo un elenco univoco di valori che vengono visualizzati più di una volta. In altre parole, stiamo creando un elenco di duplicati :) La lingua è un po 'confusa.
Spiegazione
Questo esempio utilizza la funzione UNIQUE insieme alla funzione FILTER. Puoi vedere un esempio più semplice qui.
Il trucco in questo caso è applicare criteri alla funzione FILTER per consentire solo i valori basati sul conteggio delle occorrenze. Lavorando dall'interno verso l'esterno, questo viene fatto con COUNTIF e la funzione FILTER qui:
FILTER(data,COUNTIF(data,data)>1)
Il risultato di COUNTIF è un array di conteggi come questo:
(3;1;3;3;2;1;1;3;1;2;3;3)
che vengono controllati con il confronto logico> 1 per produrre un array o valori VERO / FALSO:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Si noti che TRUE corrisponde ai valori nei dati che appaiono più di una volta. Questo array viene restituito a FILTER come argomento include , utilizzato per filtrare i dati. FILTER restituisce un altro array come risultato:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Questa matrice viene restituita direttamente alla funzione UNIQUE come argomento della matrice . Avviso dei 12 valori originali, solo 8 sopravvivono.
UNIQUE quindi rimuove i duplicati e restituisce l'array finale:
("red";"green";"blue")
Se i valori in B5: B16 cambiano, l'output verrà aggiornato immediatamente.
Conteggio> 2
La formula in F5, che elenca i colori che compaiono almeno 2 volte nei dati di origine, è:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Gamma dinamica della sorgente
Poiché i dati (B5: B15) sono un intervallo denominato normale, non verranno ridimensionati se i dati vengono aggiunti o eliminati. Per utilizzare un intervallo dinamico che verrà ridimensionato automaticamente quando necessario, puoi utilizzare una tabella di Excel o creare un intervallo denominato dinamico con una formula.
