INDICE CONFRONTA Excel: estrarre elementi univoci da elenco 

 Maggio 17, 2019

By  Damiano Causale

  • Home
  • Blog
  • 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))}

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.

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

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

  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.

  5. Non riesco a farla funzionare neppure copiando pedissequamente l'esempio.

    Non riesco a capire dove sia l'errore

    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.

  6. 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"

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

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

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

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

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

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

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

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

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