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
attendo il prossimo post !!! urgentemente !!!
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 ?
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
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.
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
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
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
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
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
Buongiorno Leonardo,
non ho capito bene cosa vorreste fare. Può farmi un esempio concreto del tipo di dati e del tipo di risultato?
Grazie 🙂
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
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.
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!
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
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
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 FunctionUna 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
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à
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
Buona sera,
ho testato la macro con la relativa funzione e tutto funziona egregiamente!
Grazie 1000 per il preziosissimo supporto!!
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
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.
salve, pagina interessantissima. complimenti. io cerco una formula che mi faccia indiviudare il valore massimo di una serie ma solo entro un certo periodo limitato. Cioè se o n1, n2, n3…., nx io vorrei il massimo tra n e nx. Esiste qualcosa del genere? Grazie Giancarlo