Verschillen tussen twee versies van een spreadsheet vinden

Om te zien wat er tussen twee versies van een spreadsheet is veranderd, koppel je elke rij in het oude bestand aan de bijbehorende rij in het nieuwe bestand. Vervolgens vergelijk je de cellen van elk gekoppeld paar. Het koppelen is het lastige deel. Een rij-voor-rij-vergelijking op positie loopt vast zodra er een rij wordt ingevoegd of het bestand opnieuw wordt gesorteerd, want dan wordt elke volgende rij met de verkeerde tegenhanger vergeleken. Koppelen op een sleutelkolom, zoals een ordernummer, houdt elke rij gekoppeld aan zijn eigen record, ook als rijen verschuiven.

Spreadsheets vergelijkenZien wat er is veranderdGratis doen

Waarom de rijvolgorde een gewone vergelijking breekt

Rij 2 met rij 2 vergelijken is snel en makkelijk, maar ook gemakkelijk misleidend. Voeg één rij bovenaan toe en elke volgende rij wordt met de verkeerde gekoppeld. Het resultaat zijn honderden ‘wijzigingen’ die in werkelijkheid één wijziging zijn. Sorteren veroorzaakt hetzelfde probleem. Opnieuw sorteren verandert geen data, maar een vergelijking op positie meldt elke verplaatste rij als gewijzigd. Lijkt een diff veel te groot, controleer dan eerst de volgorde.

Gebruik een sleutelkolom

Een sleutelkolom identificeert een rij en verandert niet voor die rij: een ordernummer, een productcode, een klant-ID. Kies een kolom waarin elke waarde maar één keer voorkomt. Als de sleutel zich herhaalt, kan de vergelijking niet zien welke rij welke is. Zoek dan een betere kolom of combineer er twee.

Met een sleutel valt elke rij in een van vier groepen:

• Toegevoegd. De sleutel bestaat alleen in het nieuwe bestand.
• Verwijderd. De sleutel bestaat alleen in het oude bestand.
• Gewijzigd. De sleutel bestaat in beide bestanden, en minstens één cel verschilt.
• Ongewijzigd. De sleutel bestaat in beide bestanden, en elke vergeleken cel komt overeen.

Vergelijk de twee bestanden om de sleutelkolom te kiezen. Je ziet dan elke groep met het aantal rijen, en per gewijzigde rij welke cellen verschillen.

Gewijzigde cellen en kolommen die altijd veranderen

Een gewijzigde rij kan in één cel van de dertig verschillen, dus kijk naar de cellen. Een prijs die van 12.50 naar 13.00 gaat, is een ander soort wijziging dan een nieuw adres in dezelfde rij. Negeer kolommen die bij elke keer opslaan veranderen, zoals een tijdstip ‘laatst gewijzigd’, zodat ze je lijst niet vullen.

Hoofdletters en spaties tellen ook mee. ‘Berlin’ en ‘berlin ’ zijn voor een computer verschillend. Hoofdletters en spaties aan de randen negeren verwijdert deze valse wijzigingen. Een gecorrigeerde spelling is echter een echte wijziging, dus gebruik die opties alleen als het verschil voor jou niet uitmaakt.

Getallen die er anders uitzien maar gelijk zijn

Twee cellen kunnen verschillende tekst tonen en toch dezelfde waarde bevatten. Dit is de meest voorkomende bron van valse wijzigingen:

• Duizendtalscheidingstekens. 1,000 en 1000 zijn hetzelfde getal.
• Decimalen. 12.5 weergegeven als 12.50 is dezelfde waarde in een andere weergave.
• Valutanotaties. Een cel met de opmaak £5 bevat het getal 5. Een cel die als tekst met het £-teken is ingevoerd, niet.
• Datums. Een spreadsheet slaat een datum op als serienummer en toont die in een gekozen notatie. Daardoor kunnen 03/04/2026 en 2026-04-03 dezelfde dag zijn.

De vergelijkingstool leest getallen zoals een spreadsheet ze schrijft. Hij leest de waarden die Excel heeft opgeslagen, niet de formules die ze hebben gemaakt. Een getal dat als tekst is opgeslagen, wordt als tekst vergeleken. Controleer het datatype van elke kolom daarom voordat je de vergelijking van die kolom vertrouwt.

Computers slaan decimalen ook binair op. Daardoor is 0.1 + 0.2 in de meeste programmeertalen niet precies 0.3. Met de tolerantie-instelling tellen getallen als gelijk wanneer ze niet meer dan een zelfgekozen bedrag verschillen, bijvoorbeeld 0.01 voor een cent.

Als er geen gedeelde sleutel is

Gebruik het dichtstbijzijnde stabiele paar dat je hebt, zoals naam plus geboortedatum, en controleer een steekproef met de hand. Vergelijk je toch op positie, lees het resultaat dan als ‘deze rijen verschillen op dezelfde plek’. Het is geen bewijs van wat er precies is veranderd.

Samenvoegen en matchen: de verwante taken

Een vergelijking vraagt wat er verschilt tussen twee versies van één tabel. Een join vraagt hoe twee verschillende tabellen op elkaar aansluiten. VLOOKUP is de eenvoudigste join: het zoekt een overeenkomende sleutel in een andere tabel en geeft er een waarde uit terug. Twee valkuilen veroorzaken de meeste verrassingen. Ten eerste kijkt VLOOKUP alleen naar de eerste kolom van het bereik, dus de sleutel moet links staan. Ten tweede geeft het de eerste overeenkomst die het vindt. Een sleutel die twee keer voorkomt, wordt daardoor stilletjes teruggebracht tot één rij. Het laatste argument weglaten schakelt bovendien over naar benaderend zoeken. XLOOKUP heeft de sleutel niet links nodig, maar geeft per zoekopdracht nog steeds één overeenkomst terug.

Voeg twee spreadsheets samen op een gedeelde kolom doet hetzelfde werk zonder formules. Kies de sleutel in elke tabel en een jointype:

• Alle linkerrijen behouden houdt elke rij van de eerste tabel en voegt overeenkomende kolommen toe waar die bestaan.
• Alleen overeenkomsten houdt alleen de rijen die in beide tabellen voorkomen.
• Linkerrijen zonder overeenkomst toont wat in de tweede tabel ontbreekt. Dit is vaak het nuttigste resultaat.

Bij dubbele sleutels gedraagt Eerste overeenkomst zich als VLOOKUP. Alle overeenkomsten voegt voor elke overeenkomst een rij toe. Een klant die rechts drie keer voorkomt, komt dus ook drie keer in het resultaat voor. Tel de rijen vóór en na de join, en controleer eerst de dubbele sleutels.

Namen die anders gespeld zijn

Exacte sleutels falen als hetzelfde op twee manieren is geschreven, zoals ‘Jon Smith’ en ‘John Smith’, of ‘Müller’ en ‘Mueller’. Fuzzy matching koppelt elke naam in de ene lijst aan de dichtstbijzijnde naam in de andere, met een score. De score is de hoogste van twee maten. De ene vergelijkt letters in volgorde en beloont een gedeeld begin, wat past bij ‘Smith’ en ‘Smyth’. De andere vergelijkt woorden. Een naam waarvan alle woorden in de andere naam voorkomen, scoort daarom 100. Let op een 100 als één van de namen extra woorden heeft. De tool negeert accenten en leestekens en kan bedrijfswoorden zoals Inc of Ltd weglaten. Een score blijft een schatting, dus lees de paren na voordat je ze accepteert.

Randgevallen

• Onzichtbare tekens maken sleutels die er hetzelfde uitzien ongelijk. Verwijder verborgen tekens als sleutels die er identiek uitzien niet willen matchen.
• Voorloopnullen verdwijnen wanneer een bestand in een spreadsheet wordt geopend. Daardoor komt ‘00421’ niet meer overeen met de tekstversie ervan. Vink in de join Voorloopnullen negeren aan, zodat beide schrijfwijzen als dezelfde sleutel tellen.
• Verborgen kolommen tellen in een vergelijking nog steeds mee, tenzij je ze negeert.

De korte methode

1. Kies een sleutelkolom in beide bestanden en controleer dat die uniek is.
2. Vergelijk op sleutel. Zet de regels voor hoofdletters en spaties alleen aan waar het verschil er niet toe doet.
3. Negeer kolommen die bij elke keer opslaan veranderen.
4. Controleer de totalen. Bij unieke sleutels is ongewijzigd plus gewijzigd plus verwijderd gelijk aan het aantal oude rijen, en ongewijzigd plus gewijzigd plus toegevoegd gelijk aan het aantal nieuwe rijen.

Snelle antwoorden

Waarom markeert één ingevoegde rij alles als gewijzigd?

Een vergelijking op positie koppelt elke rij aan de rij met hetzelfde nummer. De invoeging verschuift echter alle volgende rijen. Vergelijk daarom op een sleutelkolom.

Waarom toont een vergelijking van twee bestanden met dezelfde getallen toch een wijziging?

De waarden zijn waarschijnlijk als tekst opgeslagen of anders opgemaakt. Controleer het kolomtype.