Il listino di questo mese e quello del mese scorso. L'elenco dei clienti nel gestionale e quello nel programma di fatturazione. Gli iscritti a un evento e i presenti. Confrontare due elenchi è un'operazione quotidiana, e farla a occhio su qualche centinaio di righe è lento e inaffidabile. Con poche formule si ottiene una risposta precisa a tre domande diverse: cosa manca, cosa è nuovo, cosa è cambiato.
Prima di tutto: una chiave comune
Un confronto funziona solo se i due elenchi hanno un campo che identifica in modo univoco ogni riga: un codice cliente, un codice articolo, un indirizzo email, un numero di fattura. Confrontare per nome e cognome è rischioso, perché basta un accento, una doppia o un secondo nome per far risultare diverse due righe uguali.
Se la chiave non esiste, si può costruire unendo più campi, per esempio =B2&"|"&C2&"|"&D2, ma prima va controllato che la combinazione sia davvero univoca.
Pulire prima di confrontare
La maggior parte dei falsi «mancanti» nasce da differenze invisibili:
- spazi all'inizio o alla fine, da eliminare con ANNULLA.SPAZI;
- codici numerici da una parte e testuali dall'altra, per esempio 1050 contro «1050»;
- maiuscole e minuscole, che CONTA.SE e CONFRONTA ignorano ma altri strumenti no;
- zeri iniziali persi in uno dei due elenchi, «00457» contro 457.
Dieci minuti di pulizia evitano un'ora a inseguire differenze che non esistono.
Cosa c'è in A ma non in B
Supponi che l'elenco A sia nel foglio «Vecchio» (codici in A2:A800) e l'elenco B nel foglio «Nuovo» (codici in A2:A850). Nel foglio Vecchio, accanto a ogni codice, scrivi:
=SE(CONTA.SE(Nuovo!$A$2:$A$850; A2)=0; "Mancante"; "")
CONTA.SE conta quante volte il codice compare nel nuovo elenco: se zero, la voce è stata rimossa. Ripetendo la stessa formula nell'altro foglio, con gli intervalli invertiti, trovi le voci nuove.
In alternativa, con CONFRONTA:
=SE(VAL.NON.DISP(CONFRONTA(A2; Nuovo!$A$2:$A$850; 0)); "Mancante"; "")
Il risultato è identico; CONFRONTA è spesso più veloce su elenchi molto lunghi.
Con le matrici dinamiche
Nelle versioni che supportano FILTRO, l'elenco completo delle voci mancanti si ottiene con una sola formula, in un'area libera:
=FILTRO(Vecchio!A2:A800; CONTA.SE(Nuovo!A2:A850; Vecchio!A2:A800)=0; "Nessuna voce mancante")
Il risultato è un elenco pronto da copiare in una email o da usare come base per un controllo.
Cosa è cambiato
Sapere che un codice è presente in entrambi gli elenchi non basta: il prezzo o l'indirizzo potrebbero essere diversi. Per confrontare un campo, recupera il valore dall'altro elenco e confrontalo:
=SE(CERCA.X(A2; Nuovo!$A$2:$A$850; Nuovo!$C$2:$C$850; "")<>C2; "Modificato"; "")
Qui la colonna C contiene il prezzo. Se il codice manca, CERCA.X restituisce una stringa vuota, quindi la riga risulterebbe «Modificato»: per evitare ambiguità conviene prima escludere i mancanti, oppure combinare le due verifiche in una colonna di stato con tre valori: «Mancante», «Modificato», «Invariato».
Per i numeri calcolati, ricorda di arrotondare prima del confronto: due prezzi che differiscono solo nell'ultima cifra decimale non visibile risulterebbero diversi.
Duplicati dentro lo stesso elenco
Un confronto tra elenchi presuppone che ciascuno sia privo di duplicati sulla chiave. Per verificarlo:
=SE(CONTA.SE($A$2:$A$800; A2)>1; "Duplicato"; "")
Se compaiono duplicati, chiarisci prima il loro significato: possono essere errori, oppure il segno che la chiave scelta non è univoca. In entrambi i casi il confronto va rimandato a dopo la correzione.
Il comando Dati, Rimuovi duplicati elimina le righe ripetute, ma lo fa in modo definitivo e senza mostrarti cosa ha eliminato. Prima di usarlo, lavora su una copia o evidenzia i duplicati con la formula qui sopra per vederli.
Evidenziare senza formule aggiuntive
Se vuoi solo vedere le differenze senza aggiungere colonne, usa la formattazione condizionale con una formula. Selezionando i codici del vecchio elenco a partire da A2:
=CONTA.SE(Nuovo!$A$2:$A$850; $A2)=0
colora di rosso le voci mancanti. In alcune versioni di Excel la regola non accetta riferimenti diretti a un altro foglio: in quel caso assegna un nome all'intervallo del nuovo elenco e usa il nome nella regola.
Un riepilogo per chi deve decidere
Alla fine di un confronto è utile una piccola tabella di sintesi:
| Esito | Conteggio |
|---|---|
| Invariati | =CONTA.SE(Stato; "Invariato") |
| Modificati | =CONTA.SE(Stato; "Modificato") |
| Rimossi | =CONTA.SE(Stato; "Mancante") |
| Nuovi | conteggio dall'altro foglio |
Nel mio lavoro questa tabella è la prima cosa che guardo: se i numeri sono molto diversi dalle attese, il problema è quasi sempre nella pulizia dei dati o nella scelta della chiave, non nelle formule.
In sintesi
- Un confronto affidabile richiede una chiave univoca comune ai due elenchi.
- Pulisci prima spazi, formati e zeri iniziali, causa dei falsi mancanti.
- CONTA.SE o CONFRONTA trovano le voci mancanti; invertendo gli elenchi trovi le nuove.
- Per le modifiche recupera il valore con una ricerca e confrontalo, arrotondando i numeri.
- Verifica i duplicati prima del confronto e chiudi con un riepilogo dei risultati.
