Come confrontare due fogli Excel per trovare le differenze
Confronta i fogli Excel tramite un identificativo univoco per trovare valori modificati, righe aggiunte e record mancanti. Un esempio completo, controlli sui duplicati e una richiesta mirata per l’IA.
Due esportazioni di ordini possono contenere lo stesso numero di righe e rappresentare comunque ordini diversi. Confrontare A2 con A2 non basta se qualcuno ha ordinato uno dei fogli o inserito un record.
Per i dati aziendali, abbina prima i record mediante un identificativo stabile, quindi confronta i campi che gli appartengono. Mantieni separati tre risultati: record modificati, record presenti soltanto nel vecchio foglio e record presenti soltanto nel nuovo. Conserva entrambe le fonti e crea il report su un foglio separato.
Decidi che cosa confrontare
| Domanda | Approccio adatto |
|---|---|
| L’importo di un ordine è cambiato? | Abbina l’ID dell’ordine, poi confronta l’importo |
| Quali ordini sono stati aggiunti o rimossi? | Verifica la presenza degli ID in entrambe le direzioni |
| È cambiata una formula o la formattazione di una cella? | Confronta le versioni della cartella di lavoro, non soltanto i valori restituiti |
| Due fogli piccoli sembrano diversi? | Affiancali e poi esamina le differenze |
La visualizzazione affiancata dei fogli di Microsoft agevola l’ispezione visiva. Non allinea però i record tramite i loro identificativi aziendali. L’esempio seguente confronta i valori, non la formattazione o il testo delle formule.
Parti da una chiave univoca
Usa un ID ordine, una matricola dipendente o un altro identificativo che dovrebbe comparire una sola volta in ciascuna fonte. I nomi da soli spesso non sono adatti. Se un ordine ha più righe, il suo ID non è univoco: usa una combinazione documentata, come ID ordine e numero di riga.
Prima dell’abbinamento, controlla ID vuoti, duplicati, zeri iniziali e differenze tra testo e numeri. Conserva l’ID originale accanto a qualsiasi versione ripulita. Non eliminare spazi significativi o zeri iniziali solo per forzare una corrispondenza. La nostra guida all’abbinamento delle colonne tratta il compito correlato di aggiungere campi a una tabella esistente.
Le formule seguenti usano nomi di funzione inglesi e virgole. Una versione localizzata di Excel potrebbe richiedere nomi tradotti o punti e virgola. L’esempio usa ID semplici senza caratteri jolly e importi numerici non vuoti.
Segui un piccolo esempio di confronto
In una copia della cartella di lavoro, crea due fogli chiamati Old e New. Inserisci questi record fittizi nelle colonne A e B, con le intestazioni nella riga 1. L’ordine delle righe è volutamente diverso.
| ID in Old | Importo in Old | ID in New | Importo in New |
|---|---|---|---|
| A101 | 120 | A103 | 75 |
| A102 | 80 | A101 | 120 |
| A103 | 75 | A105 | 60 |
| A104 | 50 | A102 | 95 |
Le prime due colonne appartengono a Old, le ultime due a New. Sono dati illustrativi, non un risultato ottenuto da un cliente né un test di velocità.
Per prima cosa, in ciascun foglio usa una colonna di appoggio per contare quante volte l’ID della riga compare nella propria fonte:
=COUNTIF($A$2:$A$5,A2)
Ogni ID non vuoto dell’esempio dovrebbe restituire 1. Esamina i valori superiori a 1 prima di eseguire una ricerca. COUNTIF non distingue maiuscole e minuscole e riconosce i caratteri jolly nei criteri. Se la distinzione tra maiuscole e minuscole è significativa per gli ID, oppure gli ID contengono *, ? o ~, queste formule semplici richiedono una regola di abbinamento diversa: non usarle senza adattarle.
Successivamente, in Old!D2, conta le corrispondenze nella nuova fonte e copia la formula verso il basso:
=COUNTIF(New!$A$2:$A$5,A2)
0 indica che l’ID compare solo in Old; 1 indica un candidato univoco; un valore maggiore di 1 indica un abbinamento ambiguo. In Old!E2, recupera il nuovo importo:
=XLOOKUP(A2,New!$A$2:$A$5,New!$B$2:$B$5,"Not found",0)
Confronta E con B soltanto se D è uguale a 1 e i due importi sono numeri validi. Un record mancante, un importo vuoto e uno zero effettivo non sono equivalenti. Per gli importi calcolati, stabilisci una regola di arrotondamento o tolleranza appropriata prima di classificare le differenze.
XLOOKUP restituisce il primo elemento corrispondente e non risolve i duplicati. Inoltre, non è disponibile in Excel 2016 e 2019. In queste versioni, dopo gli stessi controlli di unicità, usa questa alternativa con corrispondenza esatta in E2:
=IFNA(INDEX(New!$B$2:$B$5,MATCH(A2,New!$A$2:$A$5,0)),"Not found")
Per comportamento e compatibilità delle funzioni, consulta la documentazione di XLOOKUP e la guida a INDEX e MATCH di Microsoft.
Infine, su New, conta ogni ID rispetto a Old per individuare le aggiunte:
=COUNTIF(Old!$A$2:$A$5,A2)
Filtrando questa colonna per 0 troverai A105. Un controllo soltanto da Old verso New non lo rileverebbe.
Crea un report che spieghi ogni differenza
Per l’esempio, un report separato dovrebbe contenere:
| ID | Importo in Old | Importo in New | Stato |
|---|---|---|---|
| A101 | 120 | 120 | Invariato |
| A102 | 80 | 95 | Modificato |
| A103 | 75 | 75 | Invariato |
| A104 | 50 | — | Solo in Old |
| A105 | — | 60 | Solo in New |
Ci sono cinque ID distinti: due invariati, uno modificato, uno solo in Old e uno solo in New. Entrambe le fonti contengono quattro righe. Un confronto basato sulla posizione delle righe darebbe un quadro fuorviante.
Se confronti più campi, registra per ogni modifica il nome del campo, il vecchio valore e quello nuovo. Conserva anche i riferimenti alle righe di origine, oltre agli ID. Inserisci le chiavi vuote o duplicate in un elenco di verifica separato invece di eliminarle senza segnalarlo. Prima di modificare le fonti, leggi come rimuovere i duplicati in sicurezza.
Chiedi a GetSheetAI un confronto ben definito
Nella barra laterale di GetSheetAI per Excel, specifica chiave, campi, destinazione del risultato e regole, invece di chiedere soltanto «confronta questi fogli». Sostituisci i nomi dei campi di questa richiesta con le intestazioni effettive e ometti Stato se la fonte contiene soltanto una colonna di importo:
Confronta Old e New tramite l’ID ordine, usando gli intervalli effettivamente popolati. Segnala prima gli ID vuoti e duplicati in ciascuna fonte. Se gli ID sono ambigui, fermati e chiedimi come abbinarli. Confronta solo Importo e Stato. Distingui i record mancanti, i valori vuoti e gli zeri. Crea un nuovo foglio Comparison con ID, campo modificato, vecchio valore, nuovo valore, categoria del risultato e riferimenti alle righe di origine. Includi i record presenti soltanto su un lato. Non modificare nessuno dei fogli di origine. Riepiloga i conteggi e mostra le righe non risolte.
Controlla la regola di abbinamento proposta prima di accettare modifiche. L’IA può aiutare a organizzare il confronto, ma non può decidere se due identificativi diversi rappresentano lo stesso ordine senza una regola fornita da te.
Controlla il report prima di utilizzarlo
- Verifica un record invariato, uno modificato e un record presente soltanto in ciascuna fonte.
- Fai tornare il numero degli ID distinti tra tutte le categorie e conta separatamente gli ID non risolti.
- Conferma che ordinare una delle fonti non cambi la classificazione.
- Controlla che siano stati inclusi tutti gli intervalli popolati, non soltanto le righe visibili o filtrate.
- Conserva le esportazioni originali, così un’altra persona potrà riprodurre il confronto.
Se il passo successivo è abbinare fatture e pagamenti anziché confrontare versioni, usa il flusso di riconciliazione delle fatture. Più pagamenti per fattura richiedono regole diverse da quelle di questo esempio, che prevede un record per ID.
