INDICE CONFRONTA Excel: scopri come estrarre elementi univoci da un elenco di dati di Excel passo dopo passo.
In questo articolo ti mostrerò come estrarre elementi univoci da un elenco di dati di Excel attraverso due potenti funzioni: INDICE CONFRONTA Excel. Per fare ciò dovremmo fare ricorso a un particolare tipo di formule, le cosiddette formule di matrice (in inglese, Array).
Tratterò in seguito il discorso delle matrici in Excel, per il momento procederò spedito ad illustrarti un esempio su come poter estrarre una lista di dati univoci da un elenco. Ne parlo in modo approfondita anche nel mio corso Excel online!
Per estrarre dei valori univoci da un elenco o da una colonna di dati, utilizziamo una formula di matrice basata sulle funzioni INDICE e CONFRONTA e CONTA.SE. Nell’esempio mostrato di seguito, ho inserito la seguente formula nella cella C2.
La voce Elenco presente nella formula, indica l’intervallo denominato A2:A18 ovvero la nostra matrice.
Per fare ciò sarà sufficiente inserire una tabella e modificare il nome.
NOTA
CTRL + MAIUSC + INVIO
Successivamente vedrai comparire le parentesi graffe nella barra della formula.
{=INDICE(Elenco;CONFRONTA(0;CONTA.SE($C$1:C1;Elenco);0))}
Letture consigliate:
INDICE CONFRONTA Excel: come funziona una formula di matrice … passo dopo passo
Per estrarre elementi univoci da un elenco di dati di Excel abbiamo bisogno di un insieme di elementi. Questi elementi sono rappresentati da funzioni che andranno a comporre la nostra formula. Ma andiamo per gradi e cerchiamo di capire come funziona la formula passo dopo passo. Scomponiamo la formula in vari passaggi mostrando per ciascuno il risultato.
Estrarre elementi univoci da un elenco di dati: la funzione INDICE
Il cuore della formula è rappresentato dalla funzione INDICE.
La funzione INDICE ha due argomenti: l’argomento matrice e l’argomento riga. In altre parole, è necessario fornire a questa funzione un elenco (intervallo di dati) e un numero di riga ed essa ci restituirà un valore da aggiungere all’elenco univoco.
La parte più impegnativa è far capire alla funzione INDICE il numero di RIGA da assegnare, in modo da ottenere i valori univoci. Per fare questo chiamiamo in causa altre due funzioni: CONFRONTA e CONTA.SE. Queste ultime ci consentiranno di determinare il numero di RIGA.
Estrarre elementi univoci da un elenco di dati: la funzione CONTA.SE
Il segreto è contenuto all’interno della seguente porzione di formula.
La funzione CONTA.SE conta quante volte gli elementi presenti nell’elenco univoco vengono visualizzati nell’elenco principale, utilizzando un riferimento espandibile ($C$1:C1).
Un riferimento espandibile è assoluto da un lato ($C$1) e relativo dall’altro (C1). Ciò significa che quando la formula viene copiata nelle celle sottostanti, il riferimento si espanderà a partire dalla cella C1 per includere le righe successive nell’elenco univoco.
È importante notare che il riferimento espandibile inizia a partire dalla cella C1, ovvero una riga sopra la prima voce presente nell’elenco univoco. In questo modo possiamo contare gli elementi presenti nell’elenco univoco senza creare un riferimento circolare.
Occorre, inoltre, assicurarsi che l’intestazione dell’elenco univoco NON compaia nell’elenco principale. Infine, è necessario verificare che i dati presenti nell’elenco principale NON contengano celle vuote.
Estrarre elementi univoci da un elenco di dati: la funzione CONFRONTA
La funzione CONTA.SE deve essere nidificata all’interno della funzione CONFRONTA. Ciò significa che la funzione CONTA.SE diventa un argomento della funzione CONFRONTA ovvero viene inserita al suo interno.
Ogni volta che andiamo a nidificare una formula ci risulta difficile comprendere il risultato finale in quanto sono presenti al suo interno numerosi passaggi intermedi. Per agevolare la lettura possiamo utilizzare il comando Valuta formula presente nel gruppo Verifica formule della scheda Formule. In alternativa possiamo selezionare una porzione della formula e premere il tasto F9.
Attraverso la valutazione della formula è possibile visualizzare il risultato intermedio delle diverse parti di una formula nidificata.
Come argomento Criterio della funzione CONTA.SE, utilizziamo l’intervallo denominato Elenco che, nell’esempio, fa riferimento all’intervallo di celle A2:A18. Quando vengono forniti più criteri, la funzione CONTA.SE restituirà più risultati in una matrice (Array). Per ciascuna riga, avremo una matrice diversa come mostrato nella tabella di seguito:
Ora che abbiamo le matrici (Array) andiamo al passaggio successivo necessario per trovare i numeri di riga.
Per far questo, usiamo la funzione CONFRONTA, impostata per la corrispondenza esatta, per trovare valori uguali a zero, all’interno di ciascuna matrice.
Dopo aver inserito la funzione CONTA.SE nella funzione CONFRONTA e aver eseguito una parte della formula attraverso il tasto F9, otteniamo il seguente risultato:
La funzione CONFRONTA localizza gli oggetti cercando quelli con un conteggio pari a zero ovvero gli elementi che non sono ancora presenti nell’elenco univoco. Essa restituisce sempre la prima corrispondenza quando ci sono duplicati. Infine, grazie ai numeri di riga ottenuti e alla funzione INDICE possiamo ottenere il nome presente nell’elenco rispetto a quel numero.
INDICE CONFRONTA Excel: estrarre elementi univoci da un elenco di dati di Excel … in sintesi …
Riassumiamo i vari passaggi per estrarre elementi univoci da un elenco di dati di Excel attraverso la seguente tabella:
Di seguito vediamo i pro e i contro di questa formula.
INDICE CONFRONTA Excel e la funzione SE ERRORE
Se non desideri visualizzare i messaggi di errore quando trascini verso il basso la formula, puoi utilizzare la funzione SE.ERRORE.
Potrai riscrivere la formula nel modo seguente:
Ricordati di utilizzare di utilizzare la combinazione di tasti CTRL + MAIUSC + INVIO dopo aver digitato la formula. Questo ti consentirà di visualizzare le parentesi graffe all’interno della barra della formula.
Trucchi e suggerimenti
Qualora volessi un elenco ordinato di valori univoci puoi usare la seguente formula:
Infine, se vuoi risparmiare tempo mentre lavori con le formule ti suggerisco di leggere l’articolo su come lavorare in modo efficiente con le formule di Excel.
Ho provato ad utilizzare la formula proposta, ma excel mi ritorna un errore... non capisco perchè sinceramente. La tabella è davvero piccola e ho seguito alla lettera la formula. Ha per caso suggerimenti?
Salve Marco, utilizza le tabelle per entrambe gli intervalli. Inserisci nella cella C2 la formula seguente (sostituisci il NomeDellaTabella con quello da te assegnato)
=INDICE(NomeDellaTabella;CONFRONTA(0;CONTA.SE($C$1:C1;NomeDellaTabella);0))
Infine utilizza la combinazione di tasti CTRL + MAIUSC + INVIO. Successivamente potrai estendere la formula verso il basso. Potrebbe mostrarti un errore del tipo #N/D qualora succedesse riduci l'estensione delle seconda tabella in quanto ha individuato tutti i valori univoci. Fammi sapere se hai risolto;)
Complimenti per la spiegazione. Ho imparato (la sintassi) e usato questa formula ma senza mai averne veramente capito il funzionamento.
In quella che ho imparato io però la porzione CONTA.SE($C$1:c1;Elenco) è inserita in una funzione INDICE, così: INDICE(CONTA.SE($C$1:c1;Elenco);) in modo da non rendere necessaria la formula matriciale.
Comunque di nuovo complimenti per la spiegazione.
Ciao Eugenio
è una grande soddisfazione sapere che ora ti sia chiaro il funzionamento.
Le funzioni nidificate sono abbastanza articolate perciò per rendere la spiegazione più chiara ho cercato di scomporre la formula in più passaggi per facilitarne la comprensione.
Grazie:)
grazie mille, molto utile.
solo una domanda:
volendo tenere dinamico l'elenco dei valori univoci, ho tenuto il range dell' elenco principale più ampio, comprendendo celle non compilate per il momento ma che lo potranno essere in futuro; facendo cosi però l'elenco dei valori univoci, come ultimo valore mi restituisce 0, c'è modo di eliminare lo 0 dall' elenco o eventualmente non far inserire un valore del tipo ""?
grazie
Grazie per aver apprezzato l'articolo;)
Per nascondere gli zeri applica il seguente formato celle personalizzato:
0;-0;;@
In questo modo potrai nascondere i valori pari a zero.
Spero ti sia utile.
In un vecchio foglio ho trovato la seguente formula, molto simile, ma che:
-non ha bisogno di essere inserita come matrice (niente CTRL+ALT+INVIO)
-non si "offende" se trova celle vuote.
=SE.ERRORE(INDICE(Elenco;CONFRONTA(0;INDICE(CONTA.SE($C$1:C1;Elenco&""););0));"")
Nello specifico non capisco:
-come l'ulteriore annidamento della funzione INDICE mi permetta di evitare di inserire la formula come matrice, e se questo porti a degli effettivi benefici in termini di pesantezza di calcolo
-l'esatta funzione dell'argomento &""
Al momento sto riciclando ancora la formula come sopra invece della tua, perché in alcuni frangenti mi pare più robusta, ma detesto utilizzare strumenti di cui non conosco la funzione, e che magari possano nascondere brutte sorprese.
Grazie
Salve Saverio,
grazie per l'interessante suggerimento.
L'ulteriore annidamento della funzione INDICE probabilmente evita di inserire la formula come matrice in quanto la stessa funzione restituisce una matrice.
Le formule di matrice possono rallentare la cartella di lavoro pertanto questa modifica porta degli effettivi benefici in termini di efficienza di calcolo.
L'argomento &"" può essere omesso.
Puoi utilizzare la tua funzione senza brutte sorprese.
Non riesco a farla funzionare neppure copiando pedissequamente l'esempio.
Non riesco a capire dove sia l'errore
Salve Alberto,
assicurati di aver assegnato alla tabella il nome Elenco
inserisci gli stessi valori mostrati nell'esempio dell'articolo
premi la combinazione di tasti CTRL + MAIUSC + INVIO
Qualora avessi ancora difficoltà inviami pure il file.
Buongiorno, ma se io avessi la colonna dell'esempio sopra descritto + un'altra colonna nella quale all'interno avessi valori diversi, esempio: "formazione" "2015" , "formazione" "2016", "formazione" "2017" ecc. come farei per avere "formazione" "2015 2016 2017"
Salve Loris,
potresti utilizzare il comando "Raggruppa per" di Power Query.
Attendo un tuo commento;)
Buonasera, ho letto ed applicato alla lettera l' esempio indicato, ottimo, funziona tutto, quindi complimenti per la spiegazione, ma l' appetito vien mangiando e da qua la domanda: il vettore di ingabbiato nella funzione CONTA.SE fatto di "1" e di "0" è possibile esplicitarlo in una colonna? La domanda sottintesa è posso estrarre in una colonna a parte tutti i "record" della matrice cui corrisponde un certo valore dell' ELENCO?
Spero di essermi spiegato
Marco
Ciao Marco,
grazie per aver apprezzato la spiegazione.
Per quanto riguarda la tua domanda ... intendi se è possibile fare una trasposizione dei dati, da righe in colonne?
No, non è la trasposizione che cerco, riducendo al minimo la richiesta vorrei da una ordinaria matrice estrarre, in un foglio a parte, tutti i "record" che hanno uguali le prime due colonne (es) , se fosse un DataBase parlerei di una query su una tabella in cui :
Col A = x and Col B = y
Grazie della collaborazione
MarcoR
Salve Marco,
potresti fare ricorso direttamente a Power Query.
Ti permetterebbe di bypassare la costruzione di formule complesse.
Buongiorno, la spiegazione è ottima.
Non riesco a capire se e come posso interpretarla per adattarla a questo mio caso:
Io sto cercando di utilizzare INDICE e CONFRONTA in un ARRAYFORMULA e la difficoltà che riscontro è la seguente.
=ARRAYFORMULA(SE(ARRAYFORMULA(RIF.RIGA(D:D)=1);”IDONEO”;SE(LUNGHEZZA(D:D)=0;””;SE(LUNGHEZZA(D:D)>0;(INDICE(Punti!A1:E25;CONFRONTA(1;(Punti!D1:D25=FM16)*(Punti!A1:A25>=FP13)*(Punti!B1:B25<=FP13);0);3));"errore"))))
In realtà la formula come scritta sopra descritta funziona perfettamente.
ho però l'esigenza che ad esempio in questa parte
(Punti!D1:D25=FM16)
FM16 non sia fisso bensì variabile, in base alla riga in cui deve essere restituito il valore.
es nella riga 1 sarà FM1 e dovrà restituire quindi "valore1"
nella riga 2 FM2 e dovrà restituire quindi "valore2"
ecc
valori che saranno così confrontati con l'intervallo Punti!D1:D25
se utilizzo (FM:FM) non funziona
ho provato un CONCATENA("FP";RIF.RIGA())
ma non ne vengo a capo. qualsiasi cosa inserisco di diverso da un riferimento esatto mi da #N/D.
Avete suggerimenti?
spero di essermi spiegato bene, la mia esigenza è di fare un unica formula array nella prima riga di una colonna e che vada a popolarmi tutte le righe sottostanti facendo dei confronti tra il valore presente nella colonna FM a riga variabile , utilizzando lo stesso numero riga in cui la formula dovrebbe popolare il risultato (si colonna diversa, es FN)
ripeto con riferimento fisso la formula che ho inserito ( es FM1) sopra funziona benissimo dovendolo rendere variabile (FM:FM) non funziona
Grazie
Ciao Jack,
innanzitutto, grazie:)
La formula matrice che stai usando è di tipo statico. Ciò non permette l'inserimento di parti variabili.
Per risolvere il tutto servirebbero le nuove funzioni a matrice dinamica introdotte nella versione 365 di Excel.
ciao! ho provato la tua formula con un gruppo di nomi, quando inizializzo la formula mi restituisce un nome presnte nella matrice!! non capisco.
Mi puoi aiutare?
Salve Roberto,
prova con questa formula:
INDICE(Elenco;CONFRONTA(0;INDICE(CONTA.SE($C$1:C1;Elenco););0))
Dove la voce Elenco è il nome della tabella
Ciao, ho seguito alla lettera i passaggi e ho raggiunto il risultato del tutorial, tuttavia avrei bisogno di un'ulteriore layer per questa formula. Il mio obiettivo è quello di estrarre dei dati unici frutto di una combinazione da una tabella, quindi ho una serie di dati che possono essere: AA, BB, CC, AC, CA... Il problema è che mettendo la corrispondenza esatta il filtro non funziona perché i dati sono tutti diversi, se invece metto la corrispondenza =1 allora i dati vengono filtrati, ma combinazioni come CA e AC vengono escluse a priori quando quello di cui avrei bisogno sarebbe di contare almeno uno dei 2 e scartare l'altra perché si assomiglia, mi potresti aiutare?
Salve Andrea,
potrei suggeristi un approccio differente utilizzando le tabelle pivot ed i filtri.
Salve Damiano,
Ho visto la tua chiara spiegazione della formula matriciale. Questo tipo di formula finora mi sono limitato a usarla copiandola semplicemente da chi le aveva fatte e cambiando i riferimenti. Le tue spiegazioni sono molto chiare e vorrei cimentarmi.
Posso chiederti un pillo aiuto?
Vorrei modificare la tua formula per estrarre elementi univoci da un elenco di dati aggiungendo però che i dati da prendere in considerazione sono solo quelli nella cui corrispondente cella nella colonna a sinistra contengono un certo valore (esempio "key 1").
Come posso modificare?
La formula funziona anche se nell'intervallo ci sono celle vuote?
Grazie del tuo aiuto
Ciao Giuseppe,
grazie per aver apprezzato le mie spiegazioni.
Per estrarre i dati della colonna a sinistra questa formula non è adatta.
Posso suggerirti una variante della formula precedente che può essere utilizzata anche con le celle vuote nell'Elenco
=SE.ERRORE(INDICE(Elenco;CONFRONTA(0;INDICE(CONTA.SE($C$1:C1;Elenco););0));"")
Grazie Damiano. Davvero gentile.