Ok, quindi nei miei primi giorni di analisi dei dati, ho avuto la necessità di ottenere automaticamente gli elementi univoci in Excel da un elenco, piuttosto che rimuovere i duplicati ogni volta. E ho trovato un modo per farlo. Dopodiché la mia dashboard di Excel è stata ancora più dinamica di prima. Quindi vediamo come possiamo farlo…
Per estrarre un elenco di valori univoci da un elenco, utilizzeremo INDEX, MATCH e COUNTIF. Userò anche IFERROR, solo per avere risultati puliti, è facoltativo.
E sì, sarà una formula di matrice… Quindi facciamolo…
Formula generica per estrarre valori univoci in Excel
{=INDEX(lista_rif,MATCH(0,COUNTIF(intervallo_output_espandibile,lista_rif),0))}
Rif_lista: L'elenco da cui si desidera estrarre valori univoci
Expanding_output_range: questo è molto importante. Questo è l'intervallo in cui desideri visualizzare l'elenco estratto. Questa gamma deve avere un'intestazione distinta che non fa parte dell'elenco e la formula sarà sotto l'intestazione (se l'intestazione è in E1 la Formula sarà in E2).
Ora espandere significa che quando trascini verso il basso la formula dovrebbe espandersi nell'intervallo di output. Per fare ciò, è necessario fornire il riferimento dell'intestazione come $E$1:E1 (La mia intestazione è in E1). Quando lo trascinerò verso il basso, si espanderà. In E2, sarà $E$1:E1. In E3, sarà $E$1:E2. In E2, sarà $E$1:E3 e così via.
Ora vediamo un esempio. Lo chiarirà.
Estrazione di valori univoci Esempio Excel
Quindi qui ho questo elenco di clienti in Colonna UN nel raggio d'azione A2: A16. Ora nella colonna E, voglio ottenere valori univoci solo dai clienti. Ora anche questo intervallo A2: A16 può aumentare, quindi voglio che la mia formula recuperi qualsiasi nuovo nome cliente dall'elenco, ogni volta che l'elenco aumenta.
Ok, ora per recuperare valori univoci dalla colonna A scrivi questa formula in Cella E2, e colpisci CTRL+MAIUSC+INVIO per renderlo una formula di matrice.
{=INDICE($A$2:A16,MATCH(0,COUNTIF($E$1:E1,$A$2:A16),0))}
A$2:A16: Mi aspetto che l'elenco si espanda e possa avere nuovi valori univoci che mi piacerebbe estrarre. Ecco perché l'ho lasciato aperto dal basso per riferimento non assoluto di A16. Gli permetterà di espandersi ogni volta che copi la formula qui sotto.
Quindi sappiamo come funzionano le funzioni INDICE e CONFRONTA. La parte principale qui è:
CONTA.SE($E$1:E1,$A$2:A16): Questa formula restituirà una matrice di 1 e 0. Ogni volta che un valore nell'intervallo $E$1:E1 si trova nell'elenco dei criteri $A$2:A16, il valore viene convertito in 1 nella sua posizione nell'intervallo $A$2:A16.
Ora usando la funzione CONFRONTA stiamo cercando i valori 0. La corrispondenza restituirà la posizione del primo 0 trovato nell'array restituito dalla funzione CONTA.SE. Di quanto INDEX esaminerà A$2:A16 per restituire il valore trovato nell'indice restituito dalla funzione CONFRONTA.
Forse è un po' difficile da capire ma funziona. Lo 0 alla fine dell'elenco indica che non ci sono più valori univoci. Se non vedi quello 0 alla fine, dovresti copiare la formula nelle celle sottostanti.
Ora da evitare #N / A in puoi usare la funzione IFERROR di excel.
{=IFERROR(INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:$E1,$A$2:A16),0)),"")}
Quindi sì, puoi usare questa formula per ottenere valori univoci da un elenco. In excel 2019 con abbonamento Office 365, Microsoft offre una funzione denominata UNICA. Prende semplicemente un intervallo come argomento e restituisce un array di valori univoci. Non è disponibile in Microsoft Excel 2016 acquisto una tantum.
Download file:
Formula di Excel per estrarre valori univoci da un elencoFormula di Excel per estrarre valori univoci da un elenco
Come contare i valori univoci in Excel
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività
Come utilizzare la funzione CERCA.VERT in Excel
Come utilizzare la funzione CONTA.SE in Excel
Come utilizzare la funzione SOMMA.SE in Excel