Hai due elenchi in Excel, per esempio i codici prodotto del magazzino e quelli presenti nel catalogo online, e devi sapere quali sono in entrambi, quali mancano in uno e quali sono in più. Controllare a occhio funziona con dieci righe; con cinquecento, è facile che qualcosa sfugga.
Ecco i metodi più utili per confrontare due colonne, dal confronto riga per riga all’estrazione automatica delle differenze, con formule che puoi copiare così come sono.
I dati di esempio
Useremo questo foglio: nella colonna A, i codici del magazzino; nella B, quelli del catalogo online. Le intestazioni sono nella riga 1 e i dati vanno dalla riga 2 alla 7.
| Riga | A: Magazzino | B: Catalogo online |
|---|---|---|
| 2 | P-101 | P-101 |
| 3 | P-102 | P-104 |
| 4 | P-103 | P-103 |
| 5 | P-104 | P-107 |
| 6 | P-105 | P-102 |
| 7 | P-106 | P-108 |
Prima di scegliere un metodo, chiediti: la posizione conta? Se ogni riga rappresenta la stessa cosa in entrambe le colonne (per esempio il prezzo vecchio e quello nuovo di un prodotto), confronta riga per riga. Se sono due elenchi indipendenti in ordine diverso, devi cercare ogni valore nell’altra colonna.
Confrontare riga per riga
Il modo più semplice è scrivere un’uguaglianza in C2 e copiarla verso il basso:
=A2=B2
Restituisce VERO o FALSO. Se preferisci un testo più chiaro:
=SE(A2=B2;"Uguale";"Diverso")
Con i dati di esempio, solo le righe 2 e 4 risultano «Uguale». Nota che P-102 e P-104 compaiono in entrambe le colonne, ma su righe diverse, quindi questo metodo li segna come diversi. Per questo serve solo quando le righe sono allineate.
Distinguere maiuscole e minuscole
L’operatore = non distingue «abc» da «ABC». Se devono contare come diversi, usa IDENTICO:
=SE(IDENTICO(A2;B2);"Uguale";"Diverso")
Sapere se un valore è nell’altra colonna
Quando l’ordine non conta, lo strumento chiave è CONTA.SE. Scrivi in C2:
=SE(CONTA.SE($B$2:$B$7;A2)>0;"Nel catalogo";"Manca nel catalogo")
La formula conta quante volte A2 compare nell’intervallo B2:B7. Se è più di zero, il codice c’è. I simboli $ bloccano l’intervallo perché non si sposti quando copi la formula. Risultato: P-101, P-102, P-103 e P-104 sono nel catalogo; P-105 e P-106 mancano.
Per la direzione opposta (cosa c’è nel catalogo ma non in magazzino), scrivi in D2 e copia verso il basso:
=SE(CONTA.SE($A$2:$A$7;B2)=0;"Non in magazzino";"")
Così scopri P-107 e P-108.
Alternativa con CONFRONTA
Se preferisci le funzioni di ricerca, questa formula dà lo stesso risultato:
=SE(VAL.NUMERO(CONFRONTA(A2;$B$2:$B$7;0));"Sì";"No")
CONFRONTA restituisce la posizione del valore o #N/D se non lo trova, e VAL.NUMERO trasforma il risultato in VERO o FALSO. Se vuoi anche recuperare un dato dall’altro elenco, come il prezzo pubblicato in una colonna C del catalogo, usa CERCA.VERT o, in Excel 365 e 2021, CERCA.X.
Evidenziare corrispondenze e differenze con la formattazione condizionale
Se preferisci vedere i colori invece di una colonna di appoggio, la formattazione condizionale è perfetta.
Segnare i valori presenti in entrambe le colonne
- Seleziona A2:B7.
- Vai su Home › Formattazione condizionale › Regole evidenziazione celle › Valori duplicati.
- Scegli il colore e fai clic su OK.
Excel colora ogni valore che compare più di una volta nella selezione. Attenzione: se una stessa colonna ha valori ripetuti, verranno segnati anche quelli, pur non essendo nell’altra.
Segnare solo ciò che manca nell’altra colonna
- Seleziona A2:A7, partendo da A2.
- Vai su Home › Formattazione condizionale › Nuova regola.
- Scegli Utilizza una formula per determinare le celle da formattare.
- Scrivi la formula qui sotto, fai clic su Formato, scegli un riempimento e conferma.
=CONTA.SE($B$2:$B$7;A2)=0
Si coloreranno solo P-105 e P-106. Ripeti il procedimento su B2:B7 con =CONTA.SE($A$2:$A$7;B2)=0 per segnare P-107 e P-108.
Estrarre le differenze in un elenco separato
In Excel 365 ed Excel 2021, la funzione FILTRO restituisce direttamente l’elenco dei valori che soddisfano una condizione. Per ottenere i codici del magazzino che non sono nel catalogo, scrivi in una cella vuota:
=FILTRO(A2:A7;CONTA.SE(B2:B7;A2:A7)=0;"Nessuno")
Il risultato si espande verso il basso con P-105 e P-106. Per le corrispondenze, cambia la condizione:
=FILTRO(A2:A7;CONTA.SE(B2:B7;A2:A7)>0;"Nessuno")
Se i dati crescono, trasforma l’intervallo in una tabella da Inserisci › Tabella o amplia gli intervalli. Evita di includere righe vuote: CONTA.SE confronta anche le celle vuote e potrebbe aggiungere risultati vuoti. Nelle versioni precedenti, usa la colonna di appoggio con CONTA.SE e applica un filtro da Dati › Filtro.
Errori comuni nel confronto tra colonne
- Spazi invisibili. «P-101 » con uno spazio finale non è uguale a «P-101». Pulisci prima con
=ANNULLA.SPAZI(A2)in una colonna di appoggio. - Numeri memorizzati come testo. 101 come numero e «101» come testo non coincidono. Se vedi un triangolino verde nell’angolo della cella, convertili con l’opzione Converti in numero.
- Caratteri jolly nei dati.
CONTA.SEinterpreta*e?come caratteri jolly. Se i tuoi codici li contengono, usaCONFRONTAcon un’uguaglianza oIDENTICO. - Dimenticare i simboli $. Senza riferimenti assoluti, l’intervallo si sposta quando copi la formula e i risultati non sono più affidabili.
- Duplicati nello stesso elenco. Falsano i conteggi e la regola dei valori duplicati. Se una colonna ha ripetizioni, eliminale prima con il nostro strumento per rimuovere i duplicati.
Quando devi confrontare file interi
Le formule funzionano benissimo con due colonne dello stesso foglio. Ma se hai due versioni di un file, per esempio il listino prezzi del mese scorso e quello attuale, con molte colonne e righe, costruire formule per ogni campo diventa faticoso. In quel caso puoi usare il nostro strumento gratuito per confrontare file Excel: carichi il file originale e quello modificato e ottieni un nuovo file con le modifiche tra i due, senza scrivere alcuna formula.
Conclusione
Per confrontare due colonne, per prima cosa decidi se la posizione conta. Se le righe sono allineate, bastano =A2=B2 o SE. Se sono elenchi indipendenti, CONTA.SE ti dice cosa c’è in entrambi e cosa manca, la formattazione condizionale lo rende visibile e FILTRO ti dà le differenze in un elenco pulito. E prima di tutto, controlla spazi, tipi di dato e duplicati: sono la causa della maggior parte dei risultati strani.
Domande frequenti
Come confronto due colonne che si trovano in fogli diversi?
Allo stesso modo, ma includendo il nome del foglio nell’intervallo. Per esempio: =CONTA.SE(Foglio2!$A$2:$A$500;A2). Se il nome del foglio contiene spazi, va tra apici singoli: 'Lista web'!$A$2:$A$500.
Perché due valori che sembrano uguali risultano diversi?
Quasi sempre per spazi all’inizio o alla fine, caratteri non stampabili o perché uno è un numero e l’altro un testo. Prova =LUNGHEZZA(A2) per vedere quanti caratteri contiene davvero ogni cella.
CONTA.SE distingue tra maiuscole e minuscole?
No. «cliente a» e «Cliente A» contano come uguali. Se devi distinguerli, usa IDENTICO in un confronto riga per riga.
Posso confrontare più colonne alla volta, come nome ed email?
Sì. Usa CONTA.PIÙ.SE con una coppia di condizioni, per esempio =CONTA.PIÙ.SE($E$2:$E$500;A2;$F$2:$F$500;B2), che conta le righe in cui i due campi coincidono contemporaneamente.
Prova i nostri strumenti gratuiti
Confronta, converti, unisci e pulisci i tuoi fogli di calcolo dal browser, senza installare nulla.
