Come confrontare due elenchi in Excel
Confronta due elenchi in Excel con CONTA.SE, CERCA.X, formattazione condizionale o Power Query e individua corrispondenze e valori mancanti.
Metodo 1: confrontare l'appartenenza con CONTA.SE
Quando l'ordine non conta e vuoi solo sapere se un elemento esiste, COUNTIF (in italiano CONTA.SE) è la via diretta. Se l'elenco 1 è in A2:A100 e l'elenco 2 in B2:B100, scrivi in C2 =COUNTIF($B$2:$B$100,A2)>0 e trascina verso il basso: TRUE significa che A2 compare almeno una volta nell'elenco 2. Avvolgila in IF quando etichette come Match e Missing sono più comode da filtrare.
Blocca l'intervallo di ricerca con il simbolo del dollaro, così non scorre quando copi la formula. Questo metodo considera presenti anche i valori ripetuti, ma non dimostra che i due elenchi contengano lo stesso numero di copie. Se la quantità conta, confronta =COUNTIF($A$2:$A$100,A2) con =COUNTIF($B$2:$B$100,A2) su un elenco di chiavi uniche.
Metodo 2: restituire i dati collegati con CERCA.X
Usa XLOOKUP (CERCA.X) quando una corrispondenza deve restituire uno stato, un importo o una descrizione. Per esempio =XLOOKUP(A2,$D$2:$D$100,$E$2:$E$100,"Missing") cerca A2 nella colonna D e restituisce il valore allineato della colonna E. La corrispondenza esatta è predefinita e il valore esplicito per «non trovato» rende visibili le chiavi mancanti senza nascondere altri errori. XLOOKUP è disponibile in Microsoft 365 e in Excel 2021 o successivo.
Le cartelle di lavoro più vecchie possono usare =IFNA(VLOOKUP(A2,$D$2:$E$100,2,FALSE),"Missing"). VLOOKUP richiede che la chiave sia nella prima colonna della tabella, mentre XLOOKUP può restituire dati da entrambi i lati. In ogni caso verifica che la chiave abbia una sola riga prevista: altrimenti la prima corrispondenza può nascondere record duplicati nell'origine.
Metodo 3: evidenziare o unire le differenze
Per un controllo visivo seleziona il primo elenco e scegli Home > Formattazione condizionale > Nuova regola > Utilizza una formula per determinare le celle da formattare. Con =COUNTIF($B$2:$B$100,A2)=0 formatti gli elementi assenti dall'elenco 2. Crea la regola inversa sull'elenco 2 per vedere le omissioni in entrambe le direzioni e controlla l'intervallo «Si applica a» prima di confermare.
Per confronti ricorrenti tra grandi esportazioni Power Query è più facile da verificare. Carica ogni intervallo come tabella, unisci le query sulla chiave e scegli un join di tipo Left Anti, che restituisce le righe presenti solo nella prima tabella. Ripeti nell'altra direzione per un report completo delle differenze ed esamina le chiavi duplicate prima dell'unione, perché possono moltiplicare le righe.
Dove il confronto va storto
Una formula riga per riga come =A2=B2 confronta le posizioni, non l'appartenenza all'elenco: basta un elemento inserito perché tutte le righe seguenti sembrino diverse. Anche le chiavi di testo possono dare problemi a causa di spazi invisibili, quindi prova corrispondenze e mancanze note.
I tipi di dato si comportano in modo diverso a seconda della funzione. XLOOKUP, MATCH e VLOOKUP trattano il numero 100 e il testo "100" come valori diversi, quindi una corrispondenza visibile può restituire #N/D. COUNTIF è più tollerante: converte il criterio, perciò può segnalare una corrispondenza che XLOOKUP non vedrebbe. Inoltre COUNTIF legge *, ? e ~ dentro una chiave come caratteri jolly, per cui una chiave come "P*" corrisponde a ogni codice che inizia con P. Confronta le due direzioni prima di dichiarare identici gli elenchi.
Tocca a te
In C2 verifica se lo SKU di A2 compare in B2:B5.
| A | B | C | |
|---|---|---|---|
| 1 | List 1 | List 2 | Matches |
| 2 | P-20 | P-10 | |
| 3 | P-30 | P-20 | |
| 4 | P-50 | P-40 | |
| 5 | P-50 |
Soluzione: =COUNTIF(B2:B5,A2)
Esempio verificato
L'elenco 1 è in A2:A4, l'elenco 2 in C2:C5. «Match» dice solo che l'elemento esiste; il conteggio mostra che l'elenco 2 contiene due copie di Pens.
| A | B | C | |
|---|---|---|---|
| 1 | List 1 | List 2 | |
| 2 | Pens | Ink | |
| 3 | Ink | Glue | |
| 4 | Tape | Pens | |
| 5 | Pens |
| Formula | Risultato |
|---|---|
=COUNTIF($C$2:$C$5,A2)>0 | TRUE |
=IF(COUNTIF($C$2:$C$5,A4)>0,"Match","Missing") | "Missing" |
=COUNTIF($C$2:$C$5,A2) | 2 |
=IFNA(VLOOKUP(A3,$C$2:$C$5,1,FALSE),"Missing") | "Ink" |
=COUNTIF($C$2:$C$5,"P*") | 2 |
Errori frequenti
- Confrontare le posizioni delle righe quando gli elenchi hanno un ordinamento diverso.
- Controllare solo A rispetto a B e non B rispetto ad A.
- Ignorare il numero di duplicati o i tipi di testo e numero incoerenti.
Domande frequenti
Come trovo i valori di A che non sono in B?
Usa =COUNTIF($B$2:$B$100,A2)=0, trascina verso il basso e filtra su TRUE (in Excel italiano VERO).
Posso confrontare elenchi che stanno in fogli diversi?
Sì. Qualifica l'intervallo con il foglio, per esempio =COUNTIF(Sheet2!$A$2:$A$100,A2)>0.
Perché una corrispondenza visibile restituisce falso?
Con XLOOKUP, MATCH o VLOOKUP un valore può essere testo e l'altro numerico, oppure il testo può contenere spazi in più o caratteri nascosti.
Che cosa conviene per un confronto mensile ripetuto?
Spesso Power Query, perché un'unione documentata si può aggiornare sostituendo le tabelle di origine.
Metti in pratica
Continua con una lezione verificata di FormulaCamp in inglese: XLOOKUP (in inglese) Gratis
Apri la lezione gratuita (in inglese)