Sådan sammenlignes to Excel -ark (for forskelle)

Se video - Sådan sammenlignes to Excel -ark til forskelle

Det kan være svært at sammenligne to Excel -filer (eller sammenligne to ark i den samme fil), da en Excel -projektmappe kun viser et ark ad gangen.

Dette bliver vanskeligere og mere tilbøjeligt til fejl, når du har mange data, der skal sammenlignes.

Heldigvis er der nogle fede funktioner i Excel, der giver dig mulighed for at åbne og let sammenligne to Excel -filer.

I denne Excel -vejledning vil jeg vise dig flere måder at sammenligne to forskellige Excel -filer (eller ark) og tjek for forskelle. Den metode, du vælger, afhænger af, hvordan dine data er struktureret, og hvilken slags sammenligning du leder efter.

Lad os komme igang!

Sammenlign to Excel-ark i separate Excel-filer (side om side)

Hvis du vil sammenligne to separate Excel-filer side om side (eller to ark i den samme projektmappe), er der en indbygget funktion i Excel til at gøre dette.

Det er Se side om side mulighed.

Dette anbefales kun, når du har et lille datasæt, og manuelt vil sammenligne disse filer sandsynligvis være mindre tidskrævende og tilbøjelige til fejl. Hvis du har et stort datasæt, anbefaler jeg at bruge den betingede metode eller formelmetoden, der er dækket senere i denne vejledning.

Lad os se, hvordan du bruger dette, når du skal sammenligne to separate filer eller to ark i den samme fil.

Antag, at du har to filer i to forskellige måneder, og du vil kontrollere, hvilke værdier der er forskellige i disse to filer.

Som standard vil det sandsynligvis optage hele din skærm, når du åbner en fil. Selvom du reducerer størrelsen, ser du altid en Excel -fil øverst.

Med indstillingen visning side om side kan du åbne to filer og derefter arrangere disse vandret eller lodret. Dette giver dig mulighed for let at sammenligne værdierne uden at skifte frem og tilbage.

Nedenfor er trinene til at justere to filer side om side og sammenligne dem:

1. Åbn de filer, du vil sammenligne.
2. I hver fil skal du vælge det ark, du vil sammenligne.
3. Klik på fanen Vis
4. I Windows -gruppen skal du klikke på 'Vis side om side'. Dette bliver kun tilgængeligt, når du har to eller flere Excel -filer åbne.

Så snart du klikker på indstillingen Vis side om side, arrangerer Excel projektmappen vandret. Begge filer vil være synlige, og du er fri til at redigere/sammenligne disse filer, mens de er arrangeret side om side.

Hvis du vil arrangere filerne lodret, skal du klikke på indstillingen Arranger alle (i fanen Vis).

Dette åbner dialogboksen 'Arranger Windows', hvor du kan vælge 'Vertikal'.

På dette tidspunkt, hvis du ruller ned i et af regnearkene, forbliver det andet, som det er. Du kan ændre dette, så når du ruller i det ene ark, ruller det andet også på samme tid. Dette gør det lettere at foretage en linje for linje sammenligning og se eventuelle forskelle.

Men for at gøre dette skal du aktivere Synkron rulning.

For at aktivere synkron rulning skal du klikke på fanen Vis (i en hvilken som helst af projektmapperne) og derefter klikke på indstillingen Synkron rullning. Dette er en vippeknap (så hvis du vil slukke den, skal du blot klikke på den igen).

Sammenligning af flere ark i separate Excel-filer (side om side)

Med indstillingen 'Vis side om side' kan du kun sammenligne to Excel -filer på én gang.

Hvis du har flere Excel -filer åbne, når du klikker på indstillingen Vis side om side, viser den dig en 'Sammenlign side om side' dialogboks, hvor du kan vælge, hvilken fil du vil sammenligne med den aktive projektmappe.

Hvis du vil sammenligne mere end to filer på én gang, skal du åbne alle disse filer og derefter klikke på indstillingen Arranger alle (det er på fanen Vis).

Vælg Vertikal/vandret i dialogboksen Arranger Windows, og klik derefter på OK.

Dette vil arrangere alle de åbne Excel -filer i den valgte rækkefølge (lodret eller vandret).

Sammenlign to ark (side om side) i den samme Excel-projektmappe

Hvis du vil sammenligne to separate ark i den samme projektmappe, kan du ikke bruge funktionen Vis side om side (da den kun fungerer for separate Excel -filer).

Men du kan stadig foretage den samme sammenligning side om side.

Dette er muliggjort af 'Ny Windows -funktion i Excel, der giver dig mulighed for at åbne to instanser på den samme projektmappe. Når du har to instanser åbne, kan du arrangere disse side om side og derefter sammenligne disse.

Antag, at du har en Excel -projektmappe, der har to ark i to forskellige måneder (januar og februar), og du vil sammenligne disse side om side for at se, hvordan salget pr. Butik har ændret sig:

Nedenfor er trinene til at sammenligne to ark i Excel:

1. Åbn den projektmappe, der har de ark, du vil sammenligne.
2. Klik på fanen Vis
3. I vinduesgruppen skal du klikke på indstillingen 'Nyt vindue'. Dette åbner den anden forekomst af den samme projektmappe.
4. Klik på 'Arranger alle' på fanen 'Vis'. Dette åbner dialogboksen Arranger Windows
5. Vælg 'Vertikal' for at sammenligne data i kolonner (eller vælg Vandret, hvis du vil sammenligne data i rækker).
6. Klik på OK.

Ovenstående trin ordner begge forekomster af projektmappen lodret.

På dette tidspunkt ville det samme regneark være valgt i begge projektmapper. I en af ​​projektmapperne skal du vælge det andet ark, du vil sammenligne med det aktive ark.

Hvordan virker det?

Når du klikker på nyt vindue, åbner den samme projektmappe igen med et lidt andet navn. For eksempel, hvis dit projektmappes navn er 'Test', og du klikker på nyt vindue, navngiver det den allerede åbne projektmappe 'Test - 1' og den anden forekomst som 'Test - 2'.

Bemærk, at disse stadig er den samme projektmappe. Hvis du foretager ændringer i nogen af ​​disse projektmapper, afspejles det i begge.

Og når du lukker en enkelt forekomst af den åbne fil, vender navnet tilbage til originalen.

Du kan også aktivere synkron rulning, hvis du vil (ved at klikke på 'Synkron rullning' i fanen 'Vis')

Sammenlign to ark og fremhæv forskelle (ved hjælp af betinget formatering)

Selvom du kan bruge ovenstående metode til at justere projektmapperne sammen og manuelt gå gennem dataene linje for linje, er det ikke en god måde, hvis du har mange data.

Også at lave dette sammenligningsniveau manuelt kan føre til mange fejl.

Så i stedet for at gøre dette manuelt kan du bruge kraften i betinget formatering til hurtigt at fremhæve eventuelle forskelle i de to Excel -ark.

Denne metode er virkelig nyttig, hvis du har to versioner i to forskellige ark, og du hurtigt vil kontrollere, hvad der er ændret.

Bemærk, at du KAN IKKE sammenligne to ark i forskellige projektmapper.

Da betinget formatering ikke kan referere til en ekstern Excel -fil, skal de ark, du skal sammenligne, være i den samme Excel -projektmappe. Hvis dette ikke er tilfældet, kan du kopiere et ark fra den anden fil til den aktive projektmappe og derefter foretage denne sammenligning.

Antag i dette eksempel, at du har et datasæt som vist nedenfor i to måneder (januar og februar) i to forskellige ark, og du vil hurtigt sammenligne dataene i disse to ark og kontrollere, om priserne på disse varer er ændret eller ej.

Nedenfor er trinene for at gøre dette:

1. Vælg de data på arket, hvor du vil fremhæve ændringerne. Da jeg vil kontrollere, hvordan priserne har ændret sig fra jan til feb, har jeg valgt dataene i feb -arket.
2. Klik på fanen Startside
3. I gruppen Styles skal du klikke på 'Betinget formatering'
4. I de indstillinger, der vises, skal du klikke på 'Ny regel'
5. I dialogboksen 'Ny formateringsregel' skal du klikke på 'Brug en formel til at bestemme, hvilke celler der skal formateres'
6. I formelfeltet skal du indtaste følgende formel: = B2Jan! B2
7. Klik på knappen Format
8. I dialogboksen Formater celler, der vises, skal du klikke på fanen 'Udfyld' og vælge den farve, som du vil fremhæve de data, der ikke matcher.
9. Klik på OK
10. Klik på OK

Ovenstående trin vil øjeblikkeligt fremhæve eventuelle ændringer i datasættet i begge ark.

Hvordan virker det?

Betinget formatering fremhæver en celle, når den givne formel for den celle returnerer en SAND. I dette eksempel sammenligner vi hver celle i et ark med den tilsvarende celle i det andet ark (udført ved hjælp af operatoren ikke lig med formlen).

Når betinget formatering finder nogen forskel i dataene, fremhæver det det i Jan -arket (det, hvor vi har anvendt den betingede formatering.

Bemærk, at jeg har brugt relativ reference i dette eksempel (A1 og ikke $ A $ 1 eller $ A1 eller A $ 1).

Når du bruger denne metode til at sammenligne to ark i Excel, skal du huske følgende;

• Denne metode er god til hurtigt at identificere forskelle, men du kan ikke bruge den løbende. For eksempel, hvis jeg indtaster en ny række i et af datasættene (eller sletter en række), ville det give mig forkerte resultater. Så snart jeg indsætter/sletter rækken, betragtes alle efterfølgende rækker som forskellige og fremhæves i overensstemmelse hermed.
• Du kan kun sammenligne to ark i den samme Excel -fil
• Du kan kun sammenligne værdien (ikke forskellen i formel eller formatering).

Sammenlign to Excel -filer/ark og få forskellene ved hjælp af formel

Hvis du kun er interesseret i hurtigt at sammenligne og identificere forskellene mellem to ark, kan du bruge en formel til kun at hente de værdier, der er forskellige.

For denne metode skal du have et separat regneark, hvor du kan hente forskellene.

Denne metode ville fungere, hvis du vil sammenligne to separate Excel -projektmapper eller regneark i den samme projektmappe.

Lad mig vise dig et eksempel, hvor jeg sammenligner to datasæt i to ark (i den samme projektmappe).

Antag, at du har datasættet som vist nedenfor i et ark kaldet Jan (og lignende data i et ark kaldet Feb), og du vil vide, hvilke værdier der er forskellige.

For at sammenligne de to ark skal du først indsætte et nyt regneark (lad os kalde dette ark 'Forskel').

Indtast følgende formel i celle A1:

= HVIS (Jan! A1Feb! A1, "Jan Value:" & Jan! A1 & CHAR (10) & "Feb Value:" & Feb! A1, "")

Kopier og indsæt denne formel for et område, så det dækker hele datasættet i begge ark. Da jeg har et lille datasæt, vil jeg kun kopiere og indsætte denne formel i A1: B10 -område.

Ovenstående formel bruger en HVIS -betingelse til at kontrollere for forskelle. Hvis der ikke er nogen forskel i værdierne, returnerer den blank, og hvis der er en forskel, returnerer den værdierne fra begge arkene i separate linjer i den samme celle.

Det gode ved denne metode er, at den kun giver dig forskellene og viser dig præcis, hvad forskellen er. I dette eksempel kan jeg let se, at prisen i celle B4 og B8 er forskellige (samt de nøjagtige værdier i disse celler).

Sammenlign to Excel -filer/ark og få forskellene ved hjælp af VBA

Hvis du skal sammenligne Excel -filer eller -ark ganske ofte, er det en god idé at have en klar Excel -makro -VBA -kode og bruge den, når du skal sammenligne.

Du kan også tilføje makroen til værktøjslinjen Hurtig adgang, så du kan få adgang med en enkelt knap og med det samme vide, hvilke celler der er forskellige i forskellige filer/ark.

Antag, at du har to ark Jan og Feb, og du vil sammenligne og fremhæve forskelle i Jan -arket, kan du bruge nedenstående VBA -kode:

Sub CompareSheets () Dim rngCell som område for hver rngCell i regneark ("Jan"). UsedRange If Not rngCell = Worksheets ("Feb"). Celler (rngCell.Row, rngCell.Column) Herefter rngCell.Interior.Color = vbYellow End Hvis Næste rngCell End Sub

Ovenstående kode bruger For Next -løkken til at gå gennem hver celle i Jan -arket (hele det anvendte område) og sammenligner den med den tilsvarende celle i Feb -arket. Hvis den finder en forskel (som kontrolleres ved hjælp af If-Then-sætningen), fremhæver den disse celler i gult.

Du kan bruge denne kode i et almindeligt modul i VB Editor.

Og hvis du har brug for at gøre dette ofte, er det bedre at gemme denne kode i den personlige makro -projektmappe og derefter tilføje den til værktøjslinjen Hurtig adgang. På disse måder vil du være i stand til at foretage denne sammenligning med et klik på en knap.

Her er trinene til at få den personlige makro -projektmappe i Excel (den er ikke tilgængelig som standard, så du skal aktivere den).

Her er trinene til at gemme denne kode i Personal Macro Workbook.

Og her finder du trinene til at tilføje denne makrokode til QAT.

Brug af et tredjepartsværktøj - XL -sammenligning

En anden hurtig måde at sammenligne to Excel-filer og kontrollere for match og forskelle er ved at bruge et gratis tredjepartsværktøj som XL Comparator.

Dette er et webbaseret værktøj, hvor du kan uploade to Excel-filer, og det vil oprette en sammenligningsfil, der har de data, der er almindelige (eller forskellige data baseret på hvilken mulighed du har valgt.

Antag, at du har to filer, der har kundedatasæt (f.eks. Navn og e -mail -adresse), og du vil hurtigt kontrollere, hvilke kunder der er fil 1 og ikke i fil 2.

Nedenfor er hvordan du sammenligner to Excel -filer og opretter en sammenligningsrapport:

1. Åbn https://www.xlcomparator.net/
2. Brug indstillingen Vælg fil til at uploade to filer (maksimal størrelse for hver fil kan være 5 MB)
3. Klik på knappen Næste.
4. Vælg den fælles kolonne i begge disse filer. Værktøjet vil bruge denne fælles kolonne til at lede efter match og forskelle
5. Vælg en af ​​de fire muligheder, uanset om du vil have matchende data eller forskellige data (baseret på File 1 eller File 2)
6. Klik på Næste
7. Download sammenligningsfilen, som vil have dataene (baseret på hvilken mulighed du valgte i trin 5)

Nedenfor er en video, der viser, hvordan XL Comparator -værktøjet fungerer.

En bekymring, du kan have, når du bruger et tredjepartsværktøj til at sammenligne Excel-filer, handler om privatliv. Hvis du har fortrolige data, og privatliv er virkelig vigtigt for det, er det bedre at bruge andre metoder vist ovenfor. Bemærk, at XL Comparator -webstedet nævner, at de sletter alle filerne efter 1 times sammenligning.

Dette er nogle af de metoder, du kan bruge til at sammenligne to forskellige Excel -filer (eller regneark i den samme Excel -fil). Håber du fandt denne Excel -tutorial nyttig.