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.
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.
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.
:: 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
[Valter Borsato: marzo - 2020 | Ultimo aggiornamento 24/06/2023]
Indice Generale Argomenti: EXCEL