Excel Formula per estrarre valori unici da un elenco
Ok, quindi nei miei primi giorni di analisi dei dati, avevo la necessità di ottenere automaticamente gli elementi univoci in Excel da un elenco, piuttosto che rimuovere ogni volta duplicati. E ho trovato un modo per farlo. Dopo di che 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, useremo link: / lookup-formulas-excel-index-function [INDEX], link: / lookup-formulas-excel-match-function [MATCH]
e link: / tips-countif-in-microsoft-excel [COUNTIF].
Userò anche link: / logical-formas-excel-iferror-function [SE.ERRORE], solo per avere risultati puliti, è opzionale.
E sì, sarà una formula di matrice … Quindi facciamolo … ===== Formula generica per estrarre valori univoci in Excel
{=INDEX(ref_list,MATCH(0,COUNTIF(expanding_output_range,ref_list),0))}
Ref_list: L’elenco da cui si desidera estrarre valori univoci link: / excel-range-name-expanding-references-in-excel [Expanding_output_range:]
Questo è molto importante. Questo è l’intervallo in cui desideri visualizzare l’elenco estratto. Questo intervallo 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 l’espansione 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. Nell’E3, sarà $ E $ 1: E2. In E2, sarà $ E $ 1: E3 e così via.
Ora vediamo un esempio. Lo renderà chiaro.
Esempio di estrazione di valori univoci Excel Quindi qui ho questo elenco di clienti nella colonna A nell’intervallo A2: A16.
Ora nella colonna E, desidero ottenere valori univoci solo dai clienti. Ora anche questo intervallo A2: A16 può aumentare, quindi voglio che la mia formula recuperi qualsiasi nuovo nome di cliente dall’elenco, ogni volta che l’elenco aumenta.
Ok, ora per recuperare valori univoci dalla colonna A scrivi questa formula nella cella E2 e premi CTRL + MAIUSC + INVIO per renderla una formula di matrice.
{=INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:E1,$A$2:A16),0))}
A $ 2: A16: Mi aspetto che l’elenco si espanderà e potrebbe avere nuovi valori univoci che mi piacerebbe estrarre. Questo è il motivo per cui l’ho lasciato aperto dal basso per riferimento non assoluto di A16. Gli consentirà di espandersi ogni volta che copi la formula qui sotto.
Quindi sappiamo link: / lookup-formas-retrieving-matching-values-from-not-adiacente-list [come funzionano le funzioni INDICE e CONFRONTA]. La parte principale qui è:
CONTA.SE ($ E $ 1: E1, $ A $ 2: A16): questa formula restituirà un array di 1 e 0. Ogni volta che viene trovato un valore nell’intervallo $ E $ 1: E1 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 MATCH stiamo cercando i valori 0. Match restituirà la posizione del primo 0 trovato nell’array restituito dalla funzione COUNTIF. Than INDEX esaminerà A $ 2: A16 per restituire il valore trovato all’indice restituito dalla funzione MATCH.
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 per evitare #NA in puoi usare la funzione SE.ERRORE 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 a Office 365, Microsoft offre una funzione denominata UNIQUE. Accetta semplicemente un intervallo come argomento e restituisce un array di valori univoci. Non è disponibile in Microsoft Excel 2016 acquisto una tantum.