.st0{fill:#FFFFFF;}

Excel Avanzato, Funzioni e Formule

INDICE CONFRONTA Excel: estrarre elementi univoci da elenco

 Maggio 17, 2019

By  Damiano Causale

  • Home
  • INDICE CONFRONTA Excel: estrarre elementi univoci da elenco

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.

indice confronta excel

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.

excel indice e confronta

NOTA

La formula specificata in precedenza è una formula di matrice pertanto, dopo aver digitato l’intera formula all’interno cella, è necessario premere la seguente combinazione di tasti:

CTRL + MAIUSC + INVIO

Successivamente vedrai comparire le parentesi graffe nella barra della formula.

{=INDICE(Elenco;CONFRONTA(0;CONTA.SE($C$1:C1;Elenco);0))}

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.

funzione INDICE Excel

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.

excel CONFRONTA | Excel CONTA SE

Estrarre elementi univoci da un elenco di dati: la funzione CONTA.SE

Il segreto è contenuto all’interno della seguente porzione di formula.

formula excel conta se

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.

excel indice confronta | elenco in excel

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.

matrice excel

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.

excel indice confronta | matrice excel

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:

formula matrice excel

matrici excel | elenco excel | indice confronta excel 

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.

funzione confronta excel

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:

excel matrice

elenco excel

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.

formula indice excel

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:

matrici in excel | indice confronta excel 

Di seguito vediamo i pro e i contro di questa formula.

matrice excel | indice confronta excel | pro e contro

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.

funzione se errore

Potrai riscrivere la formula nel modo seguente:

indice confronta excel | funzione se errore

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.

matrice in excel

Trucchi e suggerimenti

Qualora volessi un elenco ordinato di valori univoci puoi usare la seguente formula:

indice confronta excel | matrici con excel

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.

Damiano Causale


Mi chiamo Damiano Causale, istruttore Microsoft certificato, per lavoro aiuto aziende e privati nell'utilizzo di Excel.

Damiano Causale

Your email address will not be published. Required fields are marked

  1. 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?

    1. 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;)

  2. 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.

    1. 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:)

  3. 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

  4. 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

    1. 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.

    1. 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.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}