Come confrontare due elenchi in Excel e trovare le differenze

Hai due esportazioni, per esempio l'elenco dei dipendenti dal programma delle paghe e quello dalle risorse umane, oppure i pagamenti ricevuti e quelli registrati. Devi capire cosa manca da una parte, cosa è in più e dove i dati non coincidono. Farlo a occhio con migliaia di righe è impossibile.

La buona notizia è che Excel lo fa in pochi minuti, se i dati sono preparati bene. Ecco il metodo classico e le insidie in cui si cade più spesso.

Il principio: una chiave comune

Per confrontare due elenchi serve una colonna che identifichi in modo univoco ogni riga: un codice fiscale, un numero di fattura, un codice articolo. Si chiama chiave. Tutto il confronto è una domanda: "questa chiave esiste anche nell'altro elenco?".

Esempio, con l'Elenco A e l'Elenco B:

Da qui si ricavano tre risultati: presente in entrambi con lo stesso valore (F-101), presente in entrambi ma con valore diverso (F-102), presente solo in uno (F-103 e F-104).

Passo 1: pulire le chiavi

Metà dei problemi nasce da chiavi che sembrano uguali e non lo sono: spazi in coda, maiuscole diverse, caratteri simili. Crea una colonna di appoggio con la chiave normalizzata:

=MAIUSC(ANNULLA.SPAZI(A2))

ANNULLA.SPAZI toglie gli spazi in eccesso, MAIUSC porta tutto in maiuscolo. Per sostituire caratteri indesiderati (per esempio le lettere accentate) si usa SOSTITUISCI. Se le chiavi sono numeri in un elenco e testo nell'altro, uniformali.

Passo 2: verificare la presenza

Nell'Elenco A, accanto alla chiave pulita:

=SE(CONTA.SE('Elenco B'!$D:$D; D2)>0; "Presente in B"; "Solo in A")

E specularmente nell'Elenco B. CONTA.SE conta quante volte la chiave compare nell'altro elenco: se è zero, la riga manca. Con migliaia di righe funziona bene, anche se è più lenta di altri metodi.

Passo 3: confrontare i valori

Per le chiavi presenti in entrambi, recupera il valore dell'altro elenco con CERCA.VERT e confrontalo:

=SE.ERRORE(SE(CERCA.VERT(D2; 'Elenco B'!$D:$F; 2; FALSO)=B2; "Uguale"; "Valori diversi"); "Solo in A")

Con la stessa formula puoi gestire presenza e differenza in una sola colonna. Per i numeri decimali fai attenzione agli arrotondamenti: confronta con ARROTONDA(valore; 2) per non segnalare differenze di un millesimo.

Passo 4: i duplicati

Se una chiave compare due volte nello stesso elenco, CERCA.VERT trova solo la prima corrispondenza e il confronto può trarre in inganno. Segnalali prima:

=SE(CONTA.SE($D$2:$D$5000; D2)>1; "Duplicato"; "")

Nell'elenco dei duplicati decidi caso per caso: errore di inserimento o ripetizione legittima.

Il riepilogo

Alla fine ti serve un quadro: quante righe totali, quante coincidenti, quante solo in A, solo in B, con differenze. Con CONTA.SE sulle colonne dei risultati lo ottieni in un attimo. Per estrarre gli elenchi pronti da inviare a chi deve correggere, usa un filtro sulla colonna del risultato.

Errori da evitare

• Confrontare senza pulire: spazi e maiuscole creano falsi "solo in A".
• Usare descrizioni come chiave: "Rossi M." e "Rossi Mario" non sono la stessa cosa per Excel; meglio un codice.
• Ignorare i duplicati: falsano i totali.
• Formule su colonne intere con troppi dati: possono rallentare il file; limita l'intervallo.
• Sovrascrivere gli originali: lavora sempre su copie.

Il modello Excel gratuito

Se non vuoi costruire le formule ogni volta, il modello "Confronto di due elenchi" è gratuito. Incolli i due elenchi e il foglio fa il resto. Senza macro né collegamenti esterni.

• Elenco A e Elenco B: chiave e due valori aggiuntivi, fino a 5000 righe ciascuno.
• Pulizia automatica della chiave: spazi in eccesso, maiuscole e minuscole, È/E.
• Risultato per ogni riga: «Presente nell'altro elenco», «Solo qui», «Presente, ma con valori diversi», con segnalazione delle chiavi ripetute.
• Riepilogo: totale, coincidenti, solo in A, solo in B, differenze, duplicati.
• Solo in A, Solo in B, Differenze: elenchi pronti con i numeri di riga.

Il confronto avviene per corrispondenza completa dopo la pulizia, senza ricerca approssimata: «Rossi M.» e «Rossi Mario» non vengono abbinati. Se una chiave si ripete, vale la prima corrispondenza trovata nell'altro elenco. Funziona con Excel 2007 e successivi e con LibreOffice.

Domande frequenti

Posso confrontare più di due colonne?

Con il metodo manuale sì: concatena le colonne in una chiave composta, ad esempio data e importo, oppure confronta ogni colonna con una formula separata.

Che differenza c'è tra CERCA.VERT e CONTA.SE per questo scopo?

CONTA.SE dice se la chiave esiste; CERCA.VERT permette anche di leggere i valori associati e confrontarli.

Come evidenzio i valori diversi con i colori?

Usa la formattazione condizionale con una formula, per esempio =$E2="Valori diversi", applicata all'intera riga.

Va bene per elenchi molto grandi?

Fino a qualche migliaio di righe sì. Per centinaia di migliaia servono strumenti specifici, come Power Query o un database.