Hoe twee Excel-bladen te vergelijken (voor verschillen)

Bekijk video - Hoe u twee Excel-bladen kunt vergelijken voor verschillen

Het vergelijken van twee Excel-bestanden (of het vergelijken van twee bladen in hetzelfde bestand) kan lastig zijn, omdat een Excel-werkmap slechts één blad tegelijk weergeeft.

Dit wordt moeilijker en foutgevoeliger wanneer u veel gegevens heeft die moeten worden vergeleken.

Gelukkig zijn er enkele coole functies in Excel waarmee je twee Excel-bestanden kunt openen en eenvoudig kunt vergelijken.

In deze Excel-zelfstudie laat ik je meerdere manieren zien om: vergelijk twee verschillende Excel-bestanden (of -bladen) en controleer op verschillen. De methode die u kiest, hangt af van hoe uw gegevens zijn gestructureerd en naar wat voor soort vergelijking u op zoek bent.

Laten we beginnen!

Vergelijk twee Excel-bladen in afzonderlijke Excel-bestanden (naast elkaar)

Als u twee afzonderlijke Excel-bestanden naast elkaar wilt vergelijken (of twee bladen in dezelfde werkmap), is er een ingebouwde functie in Excel om dit te doen.

Het is de Naast elkaar bekijken keuze.

Dit wordt alleen aanbevolen als u een kleine dataset heeft en het handmatig vergelijken van deze bestanden waarschijnlijk minder tijdrovend en foutgevoelig is. Als u een grote gegevensset heeft, raad ik u aan de voorwaardelijke methode of de formulemethode te gebruiken die later in deze zelfstudie wordt behandeld.

Laten we eens kijken hoe u dit kunt gebruiken wanneer u twee afzonderlijke bestanden of twee bladen in hetzelfde bestand moet vergelijken.

Stel je hebt twee bestanden voor twee verschillende maanden en je wilt controleren welke waarden in deze twee bestanden verschillen.

Wanneer u een bestand opent, neemt het standaard waarschijnlijk uw hele scherm in beslag. Zelfs als je de grootte verkleint, zie je altijd één Excel-bestand bovenaan.

Met de optie naast elkaar bekijken kunt u twee bestanden openen en deze vervolgens horizontaal of verticaal rangschikken. Hierdoor kun je eenvoudig de waarden vergelijken zonder heen en weer te hoeven schakelen.

Hieronder vindt u de stappen om twee bestanden naast elkaar uit te lijnen en te vergelijken:

1. Open de bestanden die u wilt vergelijken.
2. Selecteer in elk bestand het blad dat u wilt vergelijken.
3. Klik op het tabblad Weergave
4. Klik in de Windows-groep op de optie 'View Side by Side'. Dit wordt alleen beschikbaar als u twee of meer Excel-bestanden hebt geopend.

Zodra u op de optie Naast elkaar weergeven klikt, zal Excel de werkmap horizontaal rangschikken. Beide bestanden zijn zichtbaar en u bent vrij om deze bestanden te bewerken/vergelijken terwijl ze naast elkaar zijn gerangschikt.

Als u de bestanden verticaal wilt rangschikken, klikt u op de optie Alles rangschikken (op het tabblad Weergave).

Dit opent het dialoogvenster 'Windows rangschikken' waar u 'Verticaal' kunt selecteren.

Als u nu in een van de werkbladen naar beneden scrolt, blijft de andere ongewijzigd. U kunt dit wijzigen zodat wanneer u in het ene blad scrolt, het andere ook tegelijkertijd scrolt. Dit maakt het eenvoudiger om regel voor regel te vergelijken en eventuele verschillen op te sporen.

Maar om dit te doen, moet u inschakelen Synchroon scrollen.

Om Synchroon schuiven in te schakelen, klikt u op het tabblad Weergave (in een van de werkmappen) en vervolgens op de optie Synchroon schuiven. Dit is een schakelknop (dus als u deze wilt uitschakelen, klikt u er gewoon opnieuw op).

Meerdere bladen vergelijken in afzonderlijke Excel-bestanden (naast elkaar)

Met de optie ‘View Side by Side’ kun je maar twee Excel-bestanden tegelijk vergelijken.

Als u meerdere Excel-bestanden hebt geopend, krijgt u bij het klikken op de optie Side by Side weergeven een dialoogvenster 'Side by Side vergelijken', waarin u kunt kiezen welk bestand u wilt vergelijken met de actieve werkmap.

Als je meer dan twee bestanden tegelijk wilt vergelijken, open dan al deze bestanden en klik vervolgens op de optie Alles rangschikken (deze staat op het tabblad Weergave).

Selecteer in het dialoogvenster Vensters schikken de optie Verticaal/Horizontaal en klik vervolgens op OK.

Hiermee worden alle geopende Excel-bestanden in de geselecteerde volgorde (verticaal of horizontaal) gerangschikt.

Vergelijk twee bladen (naast elkaar) in dezelfde Excel-werkmap

Als u twee afzonderlijke bladen in dezelfde werkmap wilt vergelijken, kunt u de functie Naast elkaar weergeven niet gebruiken (omdat deze alleen voor afzonderlijke Excel-bestanden werkt).

Maar u kunt nog steeds dezelfde zij-aan-zij vergelijking maken.

Dit wordt mogelijk gemaakt door de ‘Nieuwe Windows-functie in Excel, waarmee u twee exemplaren in dezelfde werkmap kunt openen. Zodra u twee instanties open heeft staan, kunt u deze naast elkaar rangschikken en vergelijken.

Stel dat je een Excel-werkmap hebt met twee bladen voor twee verschillende maanden (jan en feb) en je wilt deze naast elkaar vergelijken om te zien hoe de verkoop per winkel is veranderd:

Hieronder vindt u de stappen om twee bladen in Excel te vergelijken:

1. Open de werkmap met de bladen die u wilt vergelijken.
2. Klik op het tabblad Weergave
3. Klik in de groep Venster op de optie 'Nieuw venster'. Hiermee wordt het tweede exemplaar van dezelfde werkmap geopend.
4. Klik in het tabblad 'Bekijken' op 'Alles schikken'. Dit opent het dialoogvenster Vensters schikken
5. Selecteer 'Verticaal' om gegevens in kolommen te vergelijken (of selecteer Horizontaal als u gegevens in rijen wilt vergelijken).
6. Klik OK.

De bovenstaande stappen zouden beide instanties van de werkmap verticaal rangschikken.

Op dit moment zouden beide werkmappen hetzelfde werkblad hebben geselecteerd. Selecteer in een van de werkmappen het andere blad dat u wilt vergelijken met het actieve blad.

Hoe werkt dit?

Wanneer u op Nieuw venster klikt, wordt dezelfde werkmap opnieuw geopend met een iets andere naam. Als de naam van uw werkmap bijvoorbeeld 'Test' is en u op Nieuw venster klikt, wordt de reeds geopende werkmap 'Test - 1' genoemd en de tweede instantie als 'Test - 2'.

Merk op dat dit nog steeds dezelfde werkmap is. Als u wijzigingen aanbrengt in een van deze werkmappen, wordt dit in beide weergegeven.

En wanneer u een exemplaar van het geopende bestand sluit, keert de naam terug naar het origineel.

U kunt desgewenst ook synchroon scrollen inschakelen (door op de optie 'Synchroon scrollen' op het tabblad 'Bekijken' te klikken)

Vergelijk twee bladen en markeer verschillen (met voorwaardelijke opmaak)

Hoewel u de bovenstaande methode kunt gebruiken om de werkmappen op elkaar af te stemmen en handmatig regel voor regel door de gegevens te gaan, is dit geen goede manier als u veel gegevens hebt.

Ook kan het handmatig doen van dit niveau van vergelijking tot veel fouten leiden.

Dus in plaats van dit handmatig te doen, kunt u de kracht van voorwaardelijke opmaak gebruiken om snel eventuele verschillen in de twee Excel-bladen te markeren.

Deze methode is erg handig als je twee versies in twee verschillende bladen hebt en je snel wilt controleren wat er is veranderd.

Merk op dat je KAN NIET vergelijk twee bladen in verschillende werkmappen.

Aangezien voorwaardelijke opmaak niet kan verwijzen naar een extern Excel-bestand, moeten de werkbladen die u wilt vergelijken zich in dezelfde Excel-werkmap bevinden. Als dit niet het geval is, kunt u een blad uit het andere bestand naar de actieve werkmap kopiëren en vervolgens deze vergelijking maken.

Stel dat u voor dit voorbeeld een dataset hebt zoals hieronder weergegeven voor twee maanden (jan en feb) in twee verschillende bladen en u wilt snel de gegevens in deze twee bladen vergelijken en controleren of de prijzen van deze items zijn veranderd of niet.

Hieronder staan ​​de stappen om dit te doen:

1. Selecteer de gegevens in het blad waar u de wijzigingen wilt markeren. Omdat ik wil zien hoe de prijzen zijn veranderd van januari tot februari, heb ik de gegevens in het blad van februari geselecteerd.
2. Klik op het tabblad Start
3. Klik in de groep Stijlen op 'Voorwaardelijke opmaak'
4. Klik in de opties die verschijnen op 'Nieuwe regel'
5. Klik in het dialoogvenster 'Nieuwe opmaakregel' op 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'
6. Voer in het formuleveld de volgende formule in: =B2Jan!B2
7. Klik op de knop Opmaak
8. Klik in het dialoogvenster Cellen opmaken dat verschijnt op het tabblad 'Vullen' en selecteer de kleur waarin u de niet-overeenkomende gegevens wilt markeren.
9. Klik OK
10. Klik OK

De bovenstaande stappen markeren onmiddellijk eventuele wijzigingen in de gegevensset in beide bladen.

Hoe werkt dit?

Voorwaardelijke opmaak markeert een cel wanneer de gegeven formule voor die cel een WAAR retourneert. In dit voorbeeld vergelijken we elke cel in het ene blad met de overeenkomstige cel in het andere blad (gedaan met de operator niet gelijk aan in de formule).

Wanneer voorwaardelijke opmaak enig verschil in de gegevens vindt, wordt aangegeven dat in het Jan-blad (degene waarin we de voorwaardelijke opmaak hebben toegepast.

Merk op dat ik in dit voorbeeld relatieve referentie heb gebruikt (A1 en niet $A$1 of $A1 of A$1).

Wanneer u deze methode gebruikt om twee bladen in Excel te vergelijken, onthoud dan het volgende;

• Deze methode is goed om snel verschillen te identificeren, maar je kunt hem niet continu gebruiken. Als ik bijvoorbeeld een nieuwe rij in een van de datasets invoer (of een rij verwijder), krijg ik onjuiste resultaten. Zodra ik de rij invoeg/verwijder, worden alle volgende rijen als verschillend beschouwd en dienovereenkomstig gemarkeerd.
• U kunt slechts twee bladen in hetzelfde Excel-bestand vergelijken
• U kunt alleen de waarde vergelijken (niet het verschil in formule of opmaak).

Vergelijk twee Excel-bestanden/-bladen en ontdek de verschillen met behulp van de formule

Als u alleen geïnteresseerd bent in het snel vergelijken en identificeren van de verschillen tussen twee bladen, kunt u een formule gebruiken om alleen die waarden op te halen die anders zijn.

Voor deze methode heeft u een apart werkblad nodig waar u de verschillen kunt ophalen.

Deze methode zou werken als u twee afzonderlijke Excel-werkmappen of -werkbladen in dezelfde werkmap wilt vergelijken.

Ik zal u een voorbeeld laten zien waarbij ik twee datasets in twee bladen (in dezelfde werkmap) vergelijk.

Stel dat u de onderstaande gegevensset hebt in een blad met de naam Jan (en vergelijkbare gegevens in een blad met de naam Feb), en u wilt weten welke waarden verschillen.

Om de twee bladen te vergelijken, voegt u eerst een nieuw werkblad in (laten we dit blad 'Verschil' noemen).

Voer in cel A1 de volgende formule in:

=IF(Jan!A1Feb!A1,"Jan Waarde:"&Jan!A1&CHAR(10)&"Feb Waarde:"&Feb!A1,"")

Kopieer en plak deze formule voor een bereik zodat deze de volledige gegevensset in beide bladen dekt. Omdat ik een kleine dataset heb, zal ik deze formule alleen in het bereik A1:B10 kopiëren en plakken.

De bovenstaande formule gebruikt een IF-voorwaarde om te controleren op verschillen. Als er geen verschil is in de waarden, wordt blanco geretourneerd en als er een verschil is, worden de waarden van beide bladen in afzonderlijke regels in dezelfde cel geretourneerd.

Het mooie van deze methode is dat het je alleen de verschillen geeft en je precies laat zien wat het verschil is. In dit voorbeeld kan ik gemakkelijk zien dat de prijs in cel B4 en B8 verschillend is (evenals de exacte waarden in deze cellen).

Vergelijk twee Excel-bestanden/-bladen en ontdek de verschillen met VBA

Als u Excel-bestanden of -bladen vrij vaak moet vergelijken, is het een goed idee om een ​​kant-en-klare Excel-macro VBA-code te hebben en deze te gebruiken wanneer u de vergelijking wilt maken.

U kunt de macro ook toevoegen aan de werkbalk Snelle toegang, zodat u met een enkele knop toegang hebt en direct weet welke cellen verschillen in verschillende bestanden/bladen.

Stel dat je twee bladen Jan en Feb hebt en je wilt de verschillen in het Jan-blad vergelijken en markeren, dan kun je de onderstaande VBA-code gebruiken:

Sub CompareSheets() Dim rngCell As Range voor elke rngCell In Worksheets ("Jan").UsedRange If Not rngCell = Worksheets("Feb").Cells(rngCell.Row, rngCell.Column) Dan rngCell.Interior.Color = vbYellow End Als volgende rngCell End Sub

De bovenstaande code gebruikt de For Next-lus om door elke cel in het Jan-blad (het volledige gebruikte bereik) te gaan en deze te vergelijken met de overeenkomstige cel in het blad Feb. In het geval dat het een verschil vindt (wat wordt gecontroleerd met de If-Then-instructie), markeert het die cellen in het geel.

Je kunt deze code gebruiken in een reguliere module in de VB Editor.

En als u dit vaak moet doen, is het beter om deze code op te slaan in de persoonlijke macro-werkmap en deze vervolgens toe te voegen aan de werkbalk Snelle toegang. Op die manier kunt u deze vergelijking met een klik op de knop maken.

Hier zijn de stappen om de persoonlijke macrowerkmap in Excel te krijgen (deze is standaard niet beschikbaar, dus u moet deze inschakelen).

Hier zijn de stappen om deze code op te slaan in de persoonlijke macrowerkmap.

En hier vindt u de stappen om deze macrocode toe te voegen aan de QAT.

Een tool van derden gebruiken - XL-vergelijker

Een andere snelle manier om twee Excel-bestanden te vergelijken en te controleren op overeenkomsten en verschillen, is door een gratis tool van derden te gebruiken, zoals XL Comparator.

Dit is een webgebaseerde tool waarmee u twee Excel-bestanden kunt uploaden en een vergelijkingsbestand kunt maken met de gegevens die algemeen zijn (of verschillende gegevens op basis van de optie die u hebt geselecteerd.

Stel je hebt twee bestanden met klantdatasets (zoals naam en e-mailadres), en je wilt snel zien welke klanten er zijn, er staat bestand 1 en niet in bestand 2.

Hieronder ziet u hoe u twee Excel-bestanden vergelijkt en een vergelijkingsrapport maakt:

1. Open https://www.xlcomparator.net/
2. Gebruik de optie Bestand kiezen om twee bestanden te uploaden (de maximale grootte van elk bestand kan 5 MB zijn)
3. Klik op de knop Volgende.
4. Selecteer de gemeenschappelijke kolom in beide bestanden. De tool gebruikt deze algemene kolom om overeenkomsten en verschillen te zoeken
5. Selecteer een van de vier opties, of u overeenkomende gegevens of andere gegevens wilt krijgen (gebaseerd op Bestand 1 of Bestand 2)
6. Klik op Volgende
7. Download het vergelijkingsbestand met de gegevens (op basis van de optie die u in stap 5 hebt geselecteerd)

Hieronder vindt u een video die laat zien hoe de XL Comparator-tool werkt.

Een zorg die u mogelijk heeft bij het gebruik van een hulpprogramma van derden om Excel-bestanden te vergelijken, betreft privacy. Als je vertrouwelijke gegevens hebt en privacy daar erg belangrijk voor is, is het beter om andere hierboven getoonde methoden te gebruiken. Merk op dat de XL Comparator-website vermeldt dat ze alle bestanden verwijderen na 1 uur na het doen van de vergelijking.

Dit zijn enkele van de methoden die u kunt gebruiken om twee verschillende Excel-bestanden (of werkbladen in hetzelfde Excel-bestand) te vergelijken. Ik hoop dat je deze Excel-zelfstudie nuttig vond.