EUCOOKIELAW_BANNER_TITLE

La funzione CONFRONTA di Excel

Ciao a tutti, eccoci qui con un’altra funzione sconosciuta ai più, CONFRONTA. Questa funzione ci permette di confrontare un dato valore con altri presenti in una lista e ne restituisce la posizione nella lista. Vediamo quali sono i parametri richiesti da questa funzione

=CONFRONTA(Valore;Matrice;[Corrispondenza])

  • Valore: rappresenta il valore che vogliamo cercare, può essere una cella, una data, un numero, un testo, ecc.
  • Matrice: l’elenco dei valori all’interno dei quali vogliamo effettuare la ricerca
  • [Corrispondenza]: un parametro opzionale che indica che tipo di corrispondenza deve esserci nel confronto e può assumere uno di questi valori:
    • 1: vuol dire “Minore di”, cerca un valore uguale a quello che abbiamo indicato e, se non viene trovato, viene restituito il valore immediatamente inferiore, se non esiste un valore inferiore verrà restituito l’errore di tipo #N/D
    • 0: vuol dire “Corrispondenza esatta”, se il valore non viene trovato restituisce un errore di tipo #N/D
    • -1: vuol dire “Maggiore di”, cerca un valore uguale a quello che abbiamo indicato e, se non viene trovato, viene restituito il valore immediatamente maggiore, se non esiste un valore maggiore verrà restituito l’errore di tipo #N/D

    Se non viene indicato nessuno di questi valori Excel selezionerà automaticamente il tipo di corrispondenza 0.

Poniamo quindi di avere la sequente lista: A1 : b A2 : c A3 : e A4 : f A5 : g E ora vediamo cosa restituisce la funzione a seconda dei casi:

=CONFRONTA("c"; A1:A5;0)

il valore restituito è 2 perché la lettera “c” si trova in seconda posizione

=CONFRONTA("d"; A1:A5;0)

il valore restituito in questo caso è #N/D perché la lettera “d” non è presente

  =CONFRONTA("d"; A1:A5;1)

il valore restituito è 2, perché la lettera “d” non è in elenco ed Excel restituisce la posizione del  valore immediatamente inferiore quindi”c”

=CONFRONTA("d"; A1:A5;-1)

il valore restituito è 3, perché la lettera “d” non è in elenco ed Excel restituisce la posizione del  valore immediatamente inferiore quindi”e”

=CONFRONTA("a"; A1:A5;1)

il valore restituito è #N/D perché la lettera “a” non è presente e non ci sono valore inferiori ad essa

=CONFRONTA("h"; A1:A5;-1)

il valore restituito è #N/D perché la lettera “h” non è presente e non ci sono valore superiori ad essa.

Come accade per la funzione INDICE anche CONFRONTA, da sola, è poco utile ma usata in combinazione con altre funzioni diventa veramente utile. Come? Lo scoprirete nel prossimo post 🙂 Ed anche questa volta vi invito ad usare i commenti per chiedere chiarimenti o per qualunque altra cosa vi passi per la testa 🙂 A presto


MESSAGGIO DALL'AUTORE
Aiutami a far conoscere questo blog ad altre persone perché possa aiutare anche loro, condividi questo articolo su Facebook, Twitter, Google+ o Linkedin, non ti costa nulla e mi aiuterà a diffonderlo dandomi l'ispirazione per scrivere altri articoli.

gianfranco

Appassionato di informatica, fotografia e di tecnologia in generale. Esperto nell'uso di Excel e con tanta voglia di condividere le sue conoscenza con il mondo, sempre pronto a dare una mano (ma non più di due volte, altrimenti rimango senza).

Potrebbero interessarti anche...

21 Risposte

  1. Anonimo ha detto:

    attendo il prossimo post !!! urgentemente !!!

    • GIANNI ha detto:

      Buon giorno, ho questo problema che non riesco a risolvere:
      in un foglio excell ho 2 colonne con una serie di numeri, mi aiuta a trovare la formula che mi dica quando sono presenti sia nella colonna A che nella B ?

      • gianfranco ha detto:

        Buongiorno Gianni,
        semplicemente, nella colonna C, partendo dalla prima riga (es C1) può usare


        =SE(CONTA.SE(B:B;A1)>0;"X";"")

        e poi trascini giù la formula. Se il numero della colonna A è presente nella colonna B verrà mostrata una X nella colonna C alla riga corrispondente

  2. Anonimo ha detto:

    Buongiorno,
    io ho il seguente problema:
    ho una pagina "Dati" con una lista di nomi di agenzie nella colonna B mentre nella colonna A ho i codici associati a ciascuna di esse.

    Nella pagina "Jan 15" ho anche qui una lista di nomi di agenzie ma in ordine diverso rispetto a quello della pagina "Dati" e quello che voglio fare e' scrivere una funzione nella colonna A di "Jan 15" che mi restituisca il codice come in colonna A "Dati" associato all'agenzia. Ossia mi mancano i codici nella pagina "Jan 15" e vorrei inserirli nella colonna A.
    Ringrazio anticipatamente dell'aiuto.

  3. gianfranco ha detto:

    Buongiorno,
    per risolvere il suo problema può usare la seguente formula:

    =SCARTO(Dati!$A$1;CONFRONTA('Jan 15'!A2;Dati!$B:$B);0;1;1)

    La formula è scritta in modo da essere inserita nella cella B2 del foglio Jan 15, se deve inserirla in una riga diversa (es. B15) cambi il riferimento A2 della formula di conseguenza (es A15). I nomi delle agenzie in Jan 15 devono essere scritti esattamente come sono scritti in Dati, altrimenti non verranno trovati dalla formula e restituiranno errore.

    Per informazioni sul funzionamento della formula può fare riferimento a questo articolo per la funzione CONFRONTA e a questa goo.gl/C2VOUH per SCARTO

    Spero di esserle stato utile, mi contatti pure se qualcosa non le è chiaro, buona giornata

    Gianfranco

  4. Anonimo ha detto:

    Ho un file con probabili problemi di formattazione perché non riesco a far funzionare nessuna formula correttamente, come posso risolverlo?
    ho già provato a copiare e incollare, a salvare nuova versione, a copiare e incollare solo i valori, ma fin'ora non ho avuto successo.
    il mio scopo in pratica è di confrontare i nomi di due elenchi per evitare di fare doppie spedizioni.
    grazie
    LA

    • gianfranco ha detto:

      Buongiorno,
      puoi spiegarmi esattamente cosa intendi con formule che non funzionano? Senza vedere il file mi vien difficile capire quale possa essere il problema. Se le formule vengono mostrate nella cella, invece che eseguite, devi cambiare la formattazione di quelle celle, cliccaci sopra con il tasto destro, scegli formattazione e quindi applica il formato "Generale", quindi reinserisci la formula. Altrimenti usa il modulo di contatto che trovi in alto a destra in questa pagina in modo da mandarmi una mail, ti rispondo in privato e cerchiamo di capire quale è il problema

  5. SoloneName ha detto:

    Ciao,ho un'agenda fatta in excel dentro la quale scrivo numeri di telefono e testo su ogni appuntamento che prendo .
    non c'è una colonna specifica per i numeri,possono essere scritti nella stessa cella del testo,oppure in una cella sotto il testo,da soli .
    Vorrei sapere se esiste una formula che mi permetta,ogni volta che digito un numero,di farsì che questo si colori,esempio di rosso,se il numero è nuovo e non è già scritto in altre celle .

    Grazie Nobili Paolo

  6. leonardo ha detto:

    Salve, in una colonna dove sono riportati valori da 10000 ho bisogno di estrarre in ulteriori quattro distinte colonne di report il n. di valori (10000).
    Potete aiutarmi? Grazie

  7. Luca ha detto:

    Buongiorno,
    Esiste un modo in excel per poter fare un confronto tra più celle x verificare che le stringhe all’interno siano presenti a discapito anche dell’ordine di come sono scritte?
    Ho provato con la funzione identico ma funziona solo se l’ordine dei valori é appunto identico qualora fosse diverso non va
    Mi spiego meglio:
    Nella colonna A ho ad esempio A01 B02 C03 e nella B A01 B02 C03 (entrambi separati da spazi) qua la funzione identico funziona bene restituendomi il valore vero nella terza colonna perché effettivamente il contenuto delle celle é identico,ma qualora ad esempio nella colonna B abbia B02 C03 A01 la funzione mi restituisce un falso. A me servirebbe che anche con ordine diverso se sono presenti gli stessi valori (A01 B02 ecc..) abbia come risultato un vero. Chiaramente non è questa la funzione che mi serve ma non riesco a trovare un modo x risolvere questo problema 🙁
    Spero di essermi spiegato bene, ringrazio anticipatamente x ogni eventuale aiuto.
    Saluti

    • gianfranco ha detto:

      Buongiorno Luca,
      Excel non dispone di una funzione proprietaria per fare quanto chiedi, tuttavia, conoscendo bene le condizioni dei paragoni (es. sono sempre 3 valori? sempre separati da spazi?) si può studiare una formula od una macro che risolva il problema.

      Se sei interessato puoi scrivermi qui nei commenti 2 o 3 casistiche o mandarmi privatamente un file (trovi il mio indirizzo email nella sezione “Contattami”) e mi metterò all’opera.

      • Luca ha detto:

        Salve,
        come prima cosa vorrei ringraziare per la pronta risposta ricevuta,in merito alle domande fatte:
        Si i valori sono sempre 3 e sempre separati da 1 spazio nello specifico possono essere i seguenti:

        A01 A02 A03 A04 A05 A06 A07 A08 A09 A15 A20 A22 A25 A26 A27 A28 A30 A31 A32 A33 A34 A35 S01 S02 S03 S04 S05 S06 S07 S09 S12 R01 R02 R03 R04 R05 R06 R07 R10

        la loro presenza ed il loro numero nella singole celle invece è casuale, ci sono delle volte che ci sono tutti e altre volte solo 1 (almeno 1 c’è sempre )
        Grazie ancora per il supporto
        Buona serata!

        • gianfranco ha detto:

          Buonasera Luca,
          ora che ho tutte le informazioni ho stilato la formula che fa quanto chiedi. Poniamo che le 2 stringhe siano in A1 e B1, la formula è:


          =E(NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI(B1;1;3);A1)));NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI(B1;5;3);A1)));NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI(B1;9;3);A1))))

          Praticamente scompongo, con STRINGA.ESTRAI i 3 valori (sapendo che sono sempre 3, separati da spazio e lunghi 3 caratteri cadauno è abbastanza facile). A questo punto, per ognuno dei 3 valori presenti in B1 effettuo una ricerca e passo il risultato alla funzione VAL.ERRORE, se TROVA non trova la stringa cercata restituisce un errore viceversa un numero, questo viene intercettato dal VAL.ERRORE che da come risultato VERO se è un errore, altrimenti falso. La funzione NON inverte il valore FALSO in VERO e viceversa. E analizza i tre valori, se sono tutti e 3 veri restituisce vero, altrimenti falso.

          Semplifichiamo con un esempio, poniamo che le 2 celle contengano “A01 B02 C03” e la seconda “A01 C03 B04”

          La formula si sviluppa in questo modo:


          =E(NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI(B1;1;3);A1)));NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI(B1;5;3);A1)));NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI(B1;9;3);A1))))

          =E(NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI("A01 C03 B04";1;3);A1)));NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI("A01 C03 B04";5;3);A1)));NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI("A01 C03 B04";9;3);A1))))

          =E(NON(VAL.ERRORE(TROVA("A01";A1)));NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI("C03";A1)));NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI("B04");A1))))

          =E(NON(VAL.ERRORE(TROVA("A01";"A01 B02 C03")));NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI("C03";"A01 B02 C03")));NON(VAL.ERRORE(TROVA(STRINGA.ESTRAI("B04");"A01 B02 C03"))))

          =E(NON(VAL.ERRORE(1));NON(VAL.ERRORE(9));NON(VAL.ERRORE(#VALORE)))

          =E(NON(FALSO);NON(FALSO);NON(VERO))

          =E(VERO;VERO;FALSO)

          =FALSO

          Nel caso esistano tutte e tre le stringhe, invece, ottieni VERO.

          Spero che sia abbastanza chiara la spiegazione, altrimenti chiedi pure 🙂

          A presto

          • Luca ha detto:

            Buongiorno
            ho provato la formula:
            funziona mettendo in A1 A01 C03 B04 e in B1 A01 B04 C03 ad esempio (quindi con ordine diverso) mi da VERO
            ma se ad esempio in una delle celle ho un altro valore in più ad esempio in B1 A01 B04 C03 A02 mi da sempre VERO quando invece in realtà il contenuto è diverso 🙁

            Grazie per il preziosissimo supporto

            • gianfranco ha detto:

              Buongiorno Luca,
              leggendo il commento precedente avevo capito che i valori sono “sempre 3”, quindi la formula prova con i primi 3 valori, se sono presenti tutti e 3 ritorna vero. In questo caso c’è un quarto valore che non viene testato e quindi il risultato è comunque VERO.

              Se serve che controlli anche un numero variabile di casistiche devo riscrivere la formula in modo diverso (e un po’ più complesso) o addirittura una macro in VBA.

              C’è un limite massimo di variabili?

              Un esempio in VBA

              Function ConfrontaValori(ByVal rng1 As String, rng2 As String) As Boolean
                  Dim Array1() As String
                  Dim Array2() As String
                  Dim Val As String
                  Dim TempResult As Boolean
                  
                  Array1 = Split(rng1, " ")
                  Array2 = Split(rng2, " ")
                  
                  If UBound(Array1) <> UBound(Array2) Then
                      ConfrontaValori = False
                      Exit Function
                  End If
                  
                  TempResult = True
                  
                  For i = 0 To UBound(Array2)
                      Val = Array2(i)
                      
                      For n = 0 To UBound(Array1)
                          If Array1(n) = Val Then TempResult = True
                      Next
                      
                      If Not TempResult Then
                          ConfrontaValori = False
                          Exit Function
                      Else
                          TempResult = False
                      End If
                  Next
                  
                  ConfrontaValori = True
              End Function

              Una volta inserita questa macro in un modulo basta scrivere, in una cella, =ConfrontaValori(A1;B1)

              P.S.: trovi come creare un modulo in questo tutorial

              • Luca ha detto:

                Salve,
                mi scuso se mi sono spiegato male praticamente:
                nelle singole celle posso avere un minimo di una stringa fino ad un massimo di 39 (quelle specificate nella mia seconda risposta)
                Grazie per la disponibilità

  8. gianfranco ha detto:

    A questo punto la soluzione migliore è la funzione in VBA che ti ho scritto sopra, non ha limiti, funziona da 1 a x, se hai problemi puoi inviarmi il file e ti inserisco io la funzione all’interno

    • Luca ha detto:

      Buona sera,
      ho testato la macro con la relativa funzione e tutto funziona egregiamente!
      Grazie 1000 per il preziosissimo supporto!!

  9. Matteo D'Annunzio ha detto:

    Buongiorno,

    ho un problema che non riesco a risolvere. Ho due colonne con dati differenti e una terza colonna con un valore. Dovrei fare una sorta di cerca.vert nelle due colonne e riportare in un’altra tabella il valore della colonna C. Come potrei fare?
    A B C
    1 5 X
    2 6 Y
    3 7 X
    4 8 Y

  10. Vittorio ha detto:

    Buongiorno
    spero sia la giusta sezione del forum.
    ho una lista di magazzino che contiene codici articolo, relativa descrizione, nome produttore, codice produttore, ecc..
    Stiamo depurando la lista da codici duplicati che, negli anni, sono stati creati.
    Una parte di lavoro l’abbiamo già fatta cercando corrispondenze per “codice produttore” ma ci siamo accorti che vi sono alcuni codici duplicati che condividono solo una parte del campo descrizione. Ecco un esempio:

    CODICE DESCRIZIONE
    AAA123 CILINDRO X456 L=50 PIPPO
    ACA1244 CURVA
    AAB125 CILIN. 50 X456

    In questo caso il codice produttore del cilindro “X456” è stato inserito nella descrizione di due dei tre articoli.
    Quello che avrei bisogno di fare è cercare in tutte le porzioni di testo di una colonna di descrizioni composta da 15000 articoli, per verificare se ne esistono di doppie, ed identificarle.
    Nel caso di sopra, ad esempio, ho bisogno di una formula che cerchi le porzioni “CILINDRO” “X456” “L=50” “PIPPO” in tutte le celle descrizione, trovando quindi il risultato nella riga 3.
    Ho pensato anche di fare prima un “testo in colonne” e separare per spazi per avere una sola cella con ogni porzione di testo da confrontare e usare il confronta, ma non mi viene.

    Ringrazio in anticipo.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.

%d blogger hanno fatto clic su Mi Piace per questo: