Twee Excel-werkbladen vergelijken op verschillen
Vergelijk Excel-werkbladen op basis van een unieke ID om gewijzigde waarden, nieuwe rijen en ontbrekende records te vinden. Met een uitgewerkt voorbeeld, controles op duplicaten en een gerichte AI-opdracht.
Twee exports van bestellingen kunnen evenveel rijen bevatten en toch over verschillende bestellingen gaan. A2 met A2 vergelijken is niet voldoende als iemand één werkblad heeft gesorteerd of een record heeft ingevoegd.
Vergelijk zakelijke gegevens eerst op basis van een vaste identificatiecode en daarna op de velden die bij die code horen. Houd drie uitkomsten uit elkaar: gewijzigde records, records die alleen in het oude werkblad staan en records die alleen in het nieuwe werkblad staan. Bewaar beide bronnen en plaats het rapport op een apart werkblad.
Bepaal wat u wilt vergelijken
| Vraag | Geschikte aanpak |
|---|---|
| Is het bedrag van een bestelling veranderd? | Zoek dezelfde bestel-ID en vergelijk daarna het bedrag |
| Welke bestellingen zijn toegevoegd of verwijderd? | Controleer in beide richtingen of de ID voorkomt |
| Is een formule of celopmaak veranderd? | Vergelijk versies van de werkmap, niet alleen de weergegeven waarden |
| Zien twee kleine werkbladen er verschillend uit? | Bekijk ze naast elkaar en onderzoek daarna de verschillen |
De functie van Microsoft om werkbladen naast elkaar weer te geven helpt bij een visuele controle. Deze functie koppelt records niet op basis van hun zakelijke identificatiecodes. Het onderstaande voorbeeld vergelijkt waarden, geen opmaak of formuletekst.
Begin met een unieke sleutel
Gebruik een bestel-ID, personeelsnummer of andere identificatiecode die in elke bron maar één keer hoort voor te komen. Alleen namen zijn vaak ongeschikt. Als een bestelling meerdere regels heeft, is de bestel-ID op zichzelf niet uniek: gebruik dan een vastgelegde combinatie, zoals bestel-ID en regelnummer.
Controleer vóór het koppelen op lege ID's, duplicaten, voorloopnullen en verschillen tussen tekst en getallen. Bewaar de oorspronkelijke ID naast een eventueel opgeschoonde versie. Verwijder geen betekenisvolle spaties of voorloopnullen alleen om een overeenkomst af te dwingen. Onze handleiding voor het koppelen van kolommen behandelt de verwante taak om velden aan een bestaande tabel toe te voegen.
De volgende formules gebruiken Engelse functienamen en komma's. Een gelokaliseerde versie van Excel kan vertaalde functienamen of puntkomma's vereisen. Het voorbeeld gebruikt eenvoudige ID's zonder jokertekens en numerieke bedragen zonder lege waarden.
Werk een kleine vergelijking uit
Maak in een kopie van de werkmap twee werkbladen met de namen Old en New. Zet deze fictieve records in kolom A en B, met de kopteksten op rij 1. De rijvolgorde is bewust verschillend.
| ID in Old | Bedrag in Old | ID in New | Bedrag in New |
|---|---|---|---|
| A101 | 120 | A103 | 75 |
| A102 | 80 | A101 | 120 |
| A103 | 75 | A105 | 60 |
| A104 | 50 | A102 | 95 |
De eerste twee kolommen horen op Old; de laatste twee op New. Dit zijn voorbeeldgegevens, geen klantresultaat of snelheidsmeting.
Gebruik eerst op elk werkblad een hulpkolom om te tellen hoe vaak de ID van die rij in de eigen bron voorkomt:
=COUNTIF($A$2:$A$5,A2)
Elke niet-lege ID in dit voorbeeld moet 1 opleveren. Onderzoek waarden boven 1 voordat u een zoekfunctie gebruikt. COUNTIF maakt geen onderscheid tussen hoofdletters en kleine letters en herkent jokertekens in criteria. Als hoofdletters voor uw ID's van belang zijn, of als ID's *, ? of ~ bevatten, is een andere koppelregel nodig; gebruik deze eenvoudige formules dan niet ongewijzigd.
Tel vervolgens in Old!D2 de overeenkomsten in de nieuwe bron en vul de formule naar beneden door:
=COUNTIF(New!$A$2:$A$5,A2)
0 betekent dat de ID alleen in Old voorkomt; 1 betekent dat er één kandidaat is; meer dan 1 betekent dat de koppeling niet eenduidig is. Haal in Old!E2 het nieuwe bedrag op:
=XLOOKUP(A2,New!$A$2:$A$5,New!$B$2:$B$5,"Not found",0)
Vergelijk E alleen met B als D gelijk is aan 1 en beide bedragen geldige getallen zijn. Behandel een ontbrekend record, een leeg bedrag en een echte nul niet als hetzelfde. Spreek voor berekende bedragen eerst een passende afrondingsregel of tolerantie af voordat u verschillen indeelt.
XLOOKUP retourneert de eerste overeenkomst en lost duplicaten niet op. De functie is bovendien niet beschikbaar in Excel 2016 en 2019. Gebruik in die versies, na dezelfde controles op uniciteit, dit alternatief met exacte overeenkomst in E2:
=IFNA(INDEX(New!$B$2:$B$5,MATCH(A2,New!$A$2:$A$5,0)),"Not found")
Raadpleeg de XLOOKUP-documentatie en de uitleg over INDEX en MATCH van Microsoft voor het gedrag en de beschikbaarheid van de functies.
Tel tot slot op New hoe vaak elke ID in Old voorkomt om toevoegingen te vinden:
=COUNTIF(Old!$A$2:$A$5,A2)
Als u deze kolom op 0 filtert, vindt u A105. Een controle die alleen van Old naar New loopt, zou die missen.
Maak een rapport dat elk verschil verklaart
Voor dit voorbeeld hoort een apart rapport het volgende te bevatten:
| ID | Bedrag in Old | Bedrag in New | Status |
|---|---|---|---|
| A101 | 120 | 120 | Ongewijzigd |
| A102 | 80 | 95 | Gewijzigd |
| A103 | 75 | 75 | Ongewijzigd |
| A104 | 50 | — | Alleen in Old |
| A105 | — | 60 | Alleen in New |
Er zijn vijf verschillende ID's: twee ongewijzigd, één gewijzigd, één alleen in Old en één alleen in New. Beide bronnen bevatten vier rijen. Een vergelijking per rijpositie zou een misleidend beeld geven.
Noteer bij meerdere velden voor elke wijziging de veldnaam en de oude en nieuwe waarde. Bewaar naast de ID's ook verwijzingen naar de bronrijen. Zet lege of dubbele sleutels op een aparte controlelijst in plaats van ze ongemerkt te verwijderen. Lees hoe u duplicaten veilig verwijdert voordat u een van de bronnen aanpast.
Vraag GetSheetAI om een afgebakende vergelijking
Geef in de GetSheetAI-zijbalk voor Excel de sleutel, velden, uitvoerlocatie en regels op, in plaats van alleen te vragen om “deze werkbladen te vergelijken”. Vervang de veldnamen in deze opdracht door uw eigen kolomkoppen; laat Status weg als uw bron alleen een bedragkolom bevat:
Vergelijk Old en New op Order ID en gebruik de daadwerkelijk gevulde bereiken. Rapporteer eerst lege en dubbele ID's in elke bron. Stop bij onduidelijke ID's en vraag mij hoe ze gekoppeld moeten worden. Vergelijk alleen Amount en Status. Houd ontbrekende records, lege waarden en nulwaarden uit elkaar. Maak een nieuw werkblad Comparison met de ID, het gewijzigde veld, de oude waarde, de nieuwe waarde, de resultaatcategorie en verwijzingen naar de bronrijen. Neem ook records op die maar aan één kant voorkomen. Pas geen van beide bronwerkbladen aan. Vat de aantallen samen en toon alle onopgeloste rijen.
Controleer de voorgestelde koppelregel voordat u wijzigingen accepteert. AI kan helpen om de vergelijking te organiseren, maar kan zonder een regel van u niet bepalen of twee verschillende identificatiecodes dezelfde bestelling vertegenwoordigen.
Controleer het rapport voordat u het gebruikt
- Controleer één ongewijzigd record, één gewijzigd record en één record dat uitsluitend in elke bron voorkomt.
- Controleer of het aantal verschillende ID's overeenkomt met de som van alle resultaatcategorieën; tel onopgeloste ID's apart.
- Ga na of het sorteren van een van de bronnen de indeling niet verandert.
- Controleer of de volledige gevulde bereiken zijn meegenomen, niet alleen de zichtbare of gefilterde rijen.
- Bewaar de oorspronkelijke exports zodat iemand anders de vergelijking kan herhalen.
Wilt u hierna facturen aan betalingen koppelen in plaats van versies vergelijken, gebruik dan de werkwijze voor factuurafstemming. Meerdere betalingen per factuur vragen om andere regels dan dit voorbeeld met één record per ID.
