Hvordan sammenligne to Excel -ark (for forskjeller)

Se video - Hvordan sammenligne to Excel -ark for forskjeller

Det kan være vanskelig å sammenligne to Excel -filer (eller sammenligne to ark i samme fil), ettersom en Excel -arbeidsbok bare viser ett ark om gangen.

Dette blir vanskeligere og feilutsatt når du har mye data som må sammenlignes.

Heldigvis er det noen kule funksjoner i Excel som lar deg åpne og enkelt sammenligne to Excel -filer.

I denne Excel -opplæringen vil jeg vise deg flere måter å sammenligne to forskjellige Excel -filer (eller ark) og sjekk for forskjeller. Metoden du velger vil avhenge av hvordan dataene dine er strukturert og hva slags sammenligning du leter etter.

La oss komme i gang!

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 samme arbeidsbok), er det en innebygd funksjon i Excel for å gjøre dette.

Det er Se side om side alternativ.

Dette anbefales bare når du har et lite datasett, og manuelt vil sammenligne disse filene sannsynligvis være mindre tidkrevende og utsatt for feil. Hvis du har et stort datasett, anbefaler jeg å bruke den betingede metoden eller formelmetoden som dekkes senere i denne opplæringen.

La oss se hvordan du bruker dette når du må sammenligne to separate filer eller to ark i samme fil.

Anta at du har to filer i to forskjellige måneder, og du vil sjekke hvilke verdier som er forskjellige i disse to filene.

Som standard, når du åpner en fil, vil det sannsynligvis ta hele skjermen. Selv om du reduserer størrelsen, ser du alltid en Excel -fil øverst.

Med alternativet visning side om side kan du åpne to filer og deretter ordne disse horisontalt eller vertikalt. Dette lar deg enkelt sammenligne verdiene uten å bytte frem og tilbake.

Nedenfor er trinnene for å justere to filer side om side og sammenligne dem:

1. Åpne filene du vil sammenligne.
2. Velg arket du vil sammenligne i hver fil.
3. Klikk på fanen Vis
4. I Windows -gruppen klikker du på alternativet "Vis side om side". Dette blir bare tilgjengelig når du har to eller flere Excel -filer åpne.

Så snart du klikker på alternativet Vis side om side, ordner Excel arbeidsboken horisontalt. Begge filene vil være synlige, og du står fritt til å redigere/sammenligne disse filene mens de er ordnet side om side.

Hvis du vil ordne filene vertikalt, klikker du på Arranger alle (i kategorien Vis).

Dette åpner "Arranger Windows" -dialogboksen der du kan velge "Vertikal".

På dette tidspunktet, hvis du ruller ned i et av regnearkene, forblir det andre som det er. Du kan endre dette slik at når du blar i det ene arket, ruller det andre også samtidig. Dette gjør det lettere å sammenligne en linje for linje og se eventuelle forskjeller.

Men for å gjøre dette må du aktivere Synkron rulling.

For å aktivere synkron rulling, klikk på kategorien Vis (i hvilken som helst av arbeidsbøkene) og klikk deretter på alternativet Synkron rulling. Dette er en vippeknapp (så hvis du vil slå den av, klikker du bare på den igjen).

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

Med alternativet "Vis side om side" kan du bare sammenligne to Excel -filer på en gang.

Hvis du har flere Excel -filer åpne, når du klikker på alternativet Vis side om side, viser det deg en 'Sammenlign side om side' dialogboks, der du kan velge hvilken fil du vil sammenligne med den aktive arbeidsboken.

Hvis du vil sammenligne mer enn to filer på en gang, åpner du alle disse filene og klikker deretter på alternativet Ordne alle (det er i kategorien Vis).

Velg Vertikal/Horisontal i dialogboksen Ordne Windows, og klikk deretter OK.

Dette vil ordne alle åpne Excel -filer i den valgte rekkefølgen (vertikal eller horisontal).

Sammenlign to ark (side om side) i samme Excel-arbeidsbok

Hvis du vil sammenligne to separate ark i den samme arbeidsboken, kan du ikke bruke funksjonen Vis side om side (ettersom den fungerer bare for separate Excel -filer).

Men du kan fortsatt gjøre den samme sammenligningen side om side.

Dette er muliggjort av 'Ny Windows -funksjon i Excel, som lar deg åpne to forekomster på samme arbeidsbok. Når du har to forekomster åpne, kan du ordne disse side om side og deretter sammenligne disse.

Anta at du har en Excel -arbeidsbok som har to ark i to forskjellige måneder (januar og februar), og du vil sammenligne disse side om side for å se hvordan salget per butikk har endret seg:

Nedenfor er trinnene for å sammenligne to ark i Excel:

1. Åpne arbeidsboken som har arkene du vil sammenligne.
2. Klikk på fanen Vis
3. I vindusgruppen klikker du på alternativet ‘Nytt vindu’. Dette åpner den andre forekomsten av den samme arbeidsboken.
4. Klikk på "Arranger alle" i kategorien "Vis". Dette åpner dialogboksen Ordne Windows
5. Velg "Vertikal" for å sammenligne data i kolonner (eller velg Horisontal hvis du vil sammenligne data i rader).
6. Klikk OK.

De ovennevnte trinnene vil ordne begge forekomstene av arbeidsboken vertikalt.

På dette tidspunktet ville begge arbeidsbøkene ha valgt det samme regnearket. I en av arbeidsbøkene velger du det andre arket du vil sammenligne med det aktive arket.

Hvordan virker dette?

Når du klikker på nytt vindu, åpnes den samme arbeidsboken igjen med et litt annet navn. For eksempel, hvis arbeidsboknavnet ditt er "Test" og du klikker på Nytt vindu, vil det gi den allerede åpne arbeidsboken navnet "Test - 1" og den andre forekomsten som "Test - 2".

Vær oppmerksom på at disse fremdeles er den samme arbeidsboken. Hvis du gjør noen endringer i noen av disse arbeidsbøkene, vil det gjenspeiles i begge deler.

Og når du lukker en forekomst av den åpne filen, vil navnet gå tilbake til originalen.

Du kan også aktivere synkron rulling hvis du vil (ved å klikke på alternativet ‘Synkron rulling’ i kategorien ‘Vis’)

Sammenlign to ark og fremhev forskjeller (ved bruk av betinget formatering)

Selv om du kan bruke metoden ovenfor til å justere arbeidsbøkene sammen og manuelt gå gjennom dataene linje for linje, er det ikke en god måte hvis du har mye data.

Også å gjøre dette sammenligningsnivået manuelt kan føre til mange feil.

Så i stedet for å gjøre dette manuelt, kan du bruke kraften i betinget formatering til raskt å markere eventuelle forskjeller i de to Excel -arkene.

Denne metoden er veldig nyttig hvis du har to versjoner i to forskjellige ark og du raskt vil sjekke hva som har endret seg.

Legg merke til at du KAN IKKE sammenligne to ark i forskjellige arbeidsbøker.

Siden betinget formatering ikke kan referere til en ekstern Excel -fil, må arkene du må sammenligne være i den samme Excel -arbeidsboken. Hvis dette ikke er tilfelle, kan du kopiere et ark fra den andre filen til den aktive arbeidsboken og deretter gjøre denne sammenligningen.

For dette eksempelet, anta at du har et datasett som vist nedenfor i to måneder (januar og februar) i to forskjellige ark, og du vil raskt sammenligne dataene i disse to arkene og sjekke om prisene på disse elementene har endret seg eller ikke.

Nedenfor er trinnene for å gjøre dette:

1. Velg dataene i arket der du vil markere endringene. Siden jeg vil sjekke hvordan prisene har endret seg fra januar til februar, har jeg valgt dataene i februar -arket.
2. Klikk på kategorien Hjem
3. Klikk på "Betinget formatering" i gruppen Stiler.
4. Klikk på "Ny regel" i alternativene som vises
5. I dialogboksen "Ny formateringsregel" klikker du på "Bruk en formel for å bestemme hvilke celler som skal formateres"
6. Skriv inn følgende formel i formelfeltet: = B2Jan! B2
7. Klikk på Format -knappen
8. I dialogboksen Formatceller som vises, klikker du på "Fyll -fanen" og velger fargen der du vil markere dataene som ikke samsvarer.
9. Klikk OK
10. Klikk OK

De ovennevnte trinnene vil umiddelbart markere eventuelle endringer i datasettet i begge arkene.

Hvordan virker dette?

Betinget formatering fremhever en celle når den gitte formelen for den cellen returnerer en TRUE. I dette eksemplet sammenligner vi hver celle i ett ark med den tilsvarende cellen i det andre arket (gjort ved å bruke ikke -lik operatøren i formelen).

Når betinget formatering finner noen forskjell i dataene, fremhever det det i Jan -arket (det der vi har brukt den betingede formateringen.

Vær oppmerksom på at jeg har brukt relativ referanse i dette eksemplet (A1 og ikke $ A $ 1 eller $ A1 eller A $ 1).

Når du bruker denne metoden til å sammenligne to ark i Excel, må du huske følgende;

• Denne metoden er god for raskt å identifisere forskjeller, men du kan ikke bruke den fortløpende. For eksempel, hvis jeg skriver inn en ny rad i noen av datasettene (eller sletter en rad), vil det gi meg feil resultater. Så snart jeg setter inn/sletter raden, blir alle påfølgende rader betraktet som forskjellige og uthevet tilsvarende.
• Du kan bare sammenligne to ark i den samme Excel -filen
• Du kan bare sammenligne verdien (ikke forskjellen i formel eller formatering).

Sammenlign to Excel -filer/ark og få forskjellene ved hjelp av formelen

Hvis du bare er interessert i å raskt sammenligne og identifisere forskjellene mellom to ark, kan du bruke en formel for å hente bare de verdiene som er forskjellige.

For denne metoden må du ha et eget regneark der du kan hente forskjellene.

Denne metoden vil fungere hvis du vil sammenligne to separate Excel -arbeidsbøker eller regneark i samme arbeidsbok.

La meg vise deg et eksempel der jeg sammenligner to datasett i to ark (i samme arbeidsbok).

Anta at du har datasettet som vist nedenfor i et ark som heter Jan (og lignende data i et ark som heter Feb), og du vil vite hvilke verdier som er forskjellige.

For å sammenligne de to arkene, må du først sette inn et nytt regneark (la oss kalle dette arket "Forskjell").

Skriv inn følgende formel i celle A1:

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

Kopier og lim inn denne formelen for et område slik at det dekker hele datasettet i begge arkene. Siden jeg har et lite datasett, vil jeg bare kopiere og lime inn denne formelen i A1: B10 -området.

Formelen ovenfor bruker en IF -betingelse for å se etter forskjeller. Hvis det ikke er noen forskjell i verdiene, vil det returnere tomt, og i tilfelle det er en forskjell, vil det returnere verdiene fra begge arkene i separate linjer i samme celle.

Det gode med denne metoden er at den bare gir deg forskjellene og viser deg nøyaktig hva forskjellen er. I dette eksemplet kan jeg enkelt se at prisen i celle B4 og B8 er forskjellig (så vel som de eksakte verdiene i disse cellene).

Sammenlign to Excel -filer/ark og få forskjellene ved hjelp av VBA

Hvis du trenger å sammenligne Excel -filer eller ark ganske ofte, er det en god idé å ha en klar Excel -makro -VBA -kode og bruke den når du trenger å gjøre sammenligningen.

Du kan også legge til makroen på verktøylinjen for rask tilgang, slik at du kan få tilgang med en enkelt knapp og umiddelbart vite hvilke celler som er forskjellige i forskjellige filer/ark.

Anta at du har to ark Jan og Feb, og du vil sammenligne og markere forskjeller i Jan -arket, du kan bruke VBA -koden nedenfor:

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

Koden ovenfor bruker For Next -løkken for å gå gjennom hver celle i Jan -arket (hele det brukte området) og sammenligner den med den tilsvarende cellen i Feb -arket. Hvis den finner en forskjell (som kontrolleres ved hjelp av If-Then-setningen), markerer den cellene i gult.

Du kan bruke denne koden i en vanlig modul i VB Editor.

Og hvis du trenger å gjøre dette ofte, er det bedre å lagre denne koden i Personal Macro -arbeidsboken og deretter legge den til på hurtiglinjen. På disse måtene vil du kunne gjøre denne sammenligningen med et klikk på en knapp.

Her er trinnene for å få Personal Macro Workbook i Excel (den er ikke tilgjengelig som standard, så du må aktivere den).

Her er trinnene for å lagre denne koden i Personal Macro Workbook.

Og her finner du trinnene for å legge denne makrokoden til QAT.

Bruke et tredjepartsverktøy - XL -sammenligning

En annen rask måte å sammenligne to Excel-filer og se etter treff og forskjeller er ved å bruke et gratis tredjepartsverktøy som XL Comparator.

Dette er et nettbasert verktøy hvor du kan laste opp to Excel-filer, og det vil opprette en sammenligningsfil som vil ha data som er vanlige (eller forskjellige data basert på hvilket alternativ du valgte.

Anta at du har to filer som har kundedatasett (for eksempel navn og e -postadresse), og du vil raskt kontrollere hvilke kunder det er, fil 1 og ikke fil 2.

Nedenfor ser du hvordan du sammenligner to Excel -filer og lager en sammenligningsrapport:

1. Åpne https://www.xlcomparator.net/
2. Bruk alternativet Velg fil for å laste opp to filer (maksimal størrelse på hver fil kan være 5 MB)
3. Klikk på Neste -knappen.
4. Velg den vanlige kolonnen i begge disse filene. Verktøyet vil bruke denne vanlige kolonnen til å se etter treff og forskjeller
5. Velg ett av de fire alternativene, enten du vil få matchende data eller forskjellige data (basert på fil 1 eller fil 2)
6. Klikk på Neste
7. Last ned sammenligningsfilen som vil ha dataene (basert på hvilket alternativ du valgte i trinn 5)

Nedenfor er en video som viser hvordan XL Comparator -verktøyet fungerer.

En bekymring du kan ha når du bruker et tredjepartsverktøy for å sammenligne Excel-filer, handler om personvern. Hvis du har konfidensielle data og personvern er virkelig viktig for det, er det bedre å bruke andre metoder vist ovenfor. Vær oppmerksom på at XL Comparator -nettstedet nevner at de sletter alle filene etter 1 time med sammenligningen.

Dette er noen av metodene du kan bruke til å sammenligne to forskjellige Excel -filer (eller regneark i samme Excel -fil). Håper du fant denne Excel -opplæringen nyttig.