Formula generica
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Sommario
Per estrarre solo valori univoci da un elenco o da una colonna, puoi utilizzare una formula di matrice basata su INDICE, CONFRONTA e CONTA.SE. Nell'esempio mostrato, la formula in D5, copiata verso il basso, è:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
dove "list" è l'intervallo denominato B5: B11.
Nota: questa è una formula di matrice e deve essere inserita utilizzando CTRL + MAIUSC + INVIO.
Spiegazione
Il nucleo di questa formula è una ricerca di base con INDICE:
=INDEX(list,row)
In altre parole, dai a INDICE l'elenco e un numero di riga, e INDICE recupererà un valore da aggiungere all'elenco univoco.
Il duro lavoro è capire il numero di RIGA da fornire INDICE, in modo da ottenere solo valori univoci. Questo viene fatto con MATCH e COUNTIF, e il trucco principale è qui:
COUNTIF($D$4:D4,list)
Qui, COUNTIF conta quante volte gli elementi già presenti nell'elenco univoco vengono visualizzati nell'elenco principale, utilizzando un riferimento in espansione per l'intervallo, $ D $ 4: D4.
Un riferimento in espansione è assoluto da un lato, relativo dall'altro. In questo caso, quando la formula viene copiata, il riferimento si espanderà per includere più righe nell'elenco univoco.
Nota il riferimento inizia in D4, una riga sopra la prima voce univoca, nell'elenco univoco. Questo è intenzionale: vogliamo contare gli elementi * già * nell'elenco univoco e non possiamo includere la cella corrente senza creare un riferimento circolare. Quindi, iniziamo dalla riga sopra.
Importante: assicurati che l'intestazione per l'elenco univoco non compaia nell'elenco principale.
Per i criteri in CONTA.SE, stiamo utilizzando l'elenco principale stesso. Quando vengono forniti più criteri, CONTA.SE restituirà più risultati in una matrice. Ad ogni nuova riga, abbiamo un array diverso come questo:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Nota: CONTA.SE gestisce più criteri con una relazione "OR" (ovvero CONTA.SE (intervallo, ("rosso", "blu", "verde")) conta il rosso, il blu o il verde.
Ora abbiamo gli array di cui abbiamo bisogno per trovare le posizioni (numeri di riga). Per questo, usiamo MATCH, impostato per la corrispondenza esatta, per trovare valori zero. Se mettiamo gli array creati da COUNTIF sopra in MATCH, ecco cosa otteniamo:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
CONFRONTA individua gli elementi cercando un conteggio pari a zero (ovvero cercando elementi che non sono ancora presenti nell'elenco univoco). Funziona, perché MATCH restituisce sempre la prima corrispondenza quando sono presenti duplicati.
Infine, le posizioni vengono inserite in INDICE come numeri di riga e INDICE restituisce il nome in quella posizione.
Versione non array con LOOKUP
È possibile creare una formula non di matrice per estrarre elementi unici utilizzando la funzione di RICERCA flessibile:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
La costruzione della formula è simile alla formula INDEX MATCH sopra, ma LOOKUP può gestire in modo nativo l'operazione di matrice.
- CONTA.SE restituisce i conteggi di ogni valore da "elenco" nell'intervallo di espansione $ D $ 4: D4
- Il confronto con zero crea una matrice di valori VERO e FALSO
- Il numero 1 viene diviso per l'array, creando un array di 1 e # DIV / 0 errori
- Questo array diventa il lookup_vector all'interno di LOOKUP
- Il valore di ricerca di 2 è maggiore di qualsiasi valore in lookup_vector
- CERCA corrisponderà all'ultimo valore non di errore nell'array di ricerca
- LOOKUP restituisce il valore corrispondente in result_vector, l'intervallo denominato "list"
Estrai gli elementi che appaiono una sola volta
La formula di RICERCA sopra è facile da estendere con la logica booleana. Per estrarre un elenco di elementi univoci che compaiono una sola volta nei dati di origine, puoi utilizzare una formula come questa:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
L'unica aggiunta è la seconda espressione COUNTIF:
COUNTIF(list,list)=1
Qui, CONTA.SE restituisce un array di conteggi di elementi come questo:
(2;2;2;2;2;1;2)
che vengono confrontati con 1, risultando in una matrice di valori VERO / FALSO:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
che agiscono come un "filtro" per limitare l'output agli elementi che si verificano una sola volta nei dati di origine.
