www.valterborsato.it

Come confrontare due tabelle di Excel. Applicare la Funzione CONFRONTA e la Formattazione condizionale

Capita frequentemente di avere in Excel tabelle che contengono valori (prodotti, anagrafiche etc) che fanno riferimento a periodi o situazioni diverse, e doverle confrontare fra di loro. Questo processo di confronto spesso viene indicato con il termine Mecciare o Matchare.
- Per risolvere questa necessità è possibile intervenire applicando Funzioni, Formattazione condizionale oppure per i più esperti programmando una macro con VBA.
- In questa sezione, con il fine di effettuare un confronto fra due tabelle dati, viene proposta l'applicazione della Funzione CONFRONTA e di una specifica formattazione condizionale (per evidenziare quali valori sono presenti o assenti in una delle due tabelle).

Confrontare due tabelle con la Funzione CONFRONTA oppure CONFRONTA.X

Seppure precedentemente considerata in un'altra sezione del sito: Le Funzioni Confronta e Confronta.x
prima di procedere con l'esempio di un confronto fra tabelle dati, conviene considerare con attenzione la sintassi della Funzione CONFRONTA

Sintassi della Funzione CONFRONTA

=CONFRONTA(valore; matrice; [corrisp])
Nella quale:
valore
, rappresenta il Valore che si vuole cercare all'interno della matrice.
matrice, rappresenta l'intervallo di celle all'interno delle quali si vuole rilevare la presenza del valore.
corrisp, (argomento facoltativo) definisce il modo in cui Excel deve confrontare il valore con i dati contenuti in matrice.
0 = corrispondenza esatta. Trova il primo dato esattamente uguale al valore.
1 = la Funzione trova il valore più grande che è inferiore o uguale al valore.
-1 = la Funzione trova il valore più piccolo che è maggiore o uguale al valore.
Attenzione. corrisp, è un argomento facoltativo. Qualora non venisse impostato per default la ricerca sarà uguale a 1 (trova il valore più grande che è inferiore o uguale a valore)

Esempio su come confrontare due diverse tabelle

Nell'esempio raffigurato nella sottostante immagine, vengono messe a confronto due tabelle contenenti l'elenco dei dipendenti di due diversi anni.
Si vuole evidenziare nella seconda tabella (2020) i valori che non erano presenti nel precedente anno.
Allo scopo viene applicata la Funzione CONFRONTA.

Nella Cella H3 è inserita la seguente Funzione =CONFRONTA(E3;$A$3:$A$30;0).
Nella Quale E3 rappresenta il primo valore da confrontare nella tabella "dipendenti 2019" (Per un corretto confronto si fa riferimento al numero di matricola del dipendente e non al nome del dipendente);
$A$3:$A$30 rappresenta la matrice nella quale cercare il valore E3; ed infine 0 imposta la ricerca come esatta.
La Funzione CONFRONTA restituisce come risultato la posizione dei valori della colonna E nella matrice $A$3:$A$30
Nel caso specifico dell'esempio, il primo nominativo (Vighini) si trova in posizione 2 e i successivi (Merlini e Crespelli) nella posizione 3 e 5.
Nel caso del quarto nominativo la Funzione restituisce il codice di errore #N/D poichè questo nominativo non è presente nella prima tabella.

Come confrontare due tabelle di Excel. Applicare la Funzione CONFRONTA per confrontare due tabelle.

Nel confronto fra due tabelle, è ovviamente possibile migliorare la restituzione del risultato, sostituendo codici di errore e numeri con delle stringhe testo che notificano la presenza o meno del valore nella prima tabella.
Nella successiva immagine viene proposta la correzione nidificando la Funzione CONFRONTA, all'interno di una Funzione logica SE e di una Funzione SE.ERRORE
La Funzione SE, come test logico, verifica che i valori restituiti (relativi alla posizione nella matrice) siano superiori a zero. Qualora questo test risultasse vero, il numero viene sostituito dalla stringa "valore presente".
Inoltre, alla formula, viene anteposta la Funzione SE.ERRORE per eliminare il codice di errore #N/D che veniva restituito se nel confronto fra le due tabelle i codici di matricola presenti in tabella dipendenti 2020 non erano presente in tabella dipendenti 2019.
La Formula inserita nella cella H3 è la seguente:
=SE.ERRORE(SE(CONFRONTA(E3;$A$3:$A$30;0)>0;"valore presente");"NON PRESENTE").
:: Nella colonna H, è stata impostata una formattazione condizione per verificare se il valore è uguale a "NON PRESENTE"
se questa condizione risultasse vera la cella viene colorata in testo rosso scuro si sfondo rosso chiaro.

In maniera del tutto analoga, l'esercizio di confronto fra le due tabelle dati, poteva essere risolto anche applicando la nuova Funzione CONFRONTA.X
Questa nuova Funzione può a tutti gli effetti essere considerata uno sviluppo della "vecchia" Funzione CONFRONTA.
Anche la Funzione CONFRONTA.X ricerca un elemento specifico in una matrice e restituisce la posizione relativa dell'elemento.
L'elemento innovativo di CONFRONTA.X è che se non viene definito il terzo argomento della funzione (facoltativo) la modalità di comparazione che viene applicata è una ricerca di corrispondenza esatta! Inoltre, nel caso di una ricerca approssimativa, CONFRONTA.X supporta anche valori non disposti in ordine crescente o decrescente.


 Come confrontare due tabelle di Excel. Applicare la Funzione CONFRONTA per confrontare due tabelle. 

Supportare il confronto di valori fra due tabelle con la formattazione condizionale 

Con la Formattazione condizionale è possibile evidenziare quali valori presenti nella tabella "dipendenti 2020" sono, o non sono, presenti nella tabella "dipendenti 2019".
Ovviamente questo "match" di confronto dati potrebbe essere impostato anche dalla prima tabella alla seconda tabella.
In questo caso è necessario impostare una nuova regola di formattazione condizionale, attraverso l'impostazione di una una Funzione logica CONTA.SE 

Per procedere con la formattazione:
- selezionare l'intervallo di celle da E3:E30 (i numeri matricola tabella anno 2020)
- Scheda Principale HOME cliccare sul pulsante Formattazione condizionale e scegliere l'opzione Nuova Regola...
-
Si apre la Finestra di dialogo Modifica regola di formattazione cliccare su Utilizza una formula per determinare le celle da formattare
-
Nella seconda parte della finestra di dialogo inserire la Funzione =CONTA.SE($A$3:$A$30;E3)=0
-
Ultimo passaggio, nel pulsante "Nessun formato impostato" cliccare e scegliere quale colore sfondo o colore testo attribuire alla formattazione condizionale qualora il risultato della Funzione fosse uguale a VERO.
Una volta impostata la formattazione (come nell'immagine sottostante) comparirà nel pulsante il formato colore che è stato scelto e che verrà applicato.
Cliccare ok per confermare l'inserimento della formattazione.
Quale risultato dell'impostazione di questa formattazione condizionale tutte i valori nella colonna E che non trovano riscontro nella colonna A vengono colorati in giallo.

Come confrontare due tabelle di Excel. Applicare la formattazione condizionale per confrontare due tabelle.

:: APPROFONDIMENTI di Funzioni e comandi trattati in questa sezione
- Applicare la Formattazione condizionale di Excel
- Applicare la formattazione condizionale con una regola espressa da una Formula
- La Funzione CONFRONTA e la Funzione CONFRONTA.X
- La Funzione SE.ERRORE
- La Funzione logica SE

 

Indice Generale Argomenti: EXCEL