Hur man jämför två Excel -ark (för skillnader)

Titta på video - Hur man jämför två Excel -ark för skillnader

Att jämföra två Excel -filer (eller jämföra två ark i samma fil) kan vara svårt eftersom en Excel -arbetsbok bara visar ett ark i taget.

Detta blir svårare och felbenäget när du har mycket data som behöver jämföras.

Lyckligtvis finns det några häftiga funktioner i Excel som låter dig öppna och enkelt jämföra två Excel -filer.

I denna Excel -handledning kommer jag att visa dig flera sätt att jämför två olika Excel -filer (eller ark) och kolla efter skillnader. Vilken metod du väljer beror på hur din data är uppbyggd och vilken typ av jämförelse du letar efter.

Låt oss börja!

Jämför två Excel-ark i separata Excel-filer (sida vid sida)

Om du vill jämföra två separata Excel-filer sida vid sida (eller två ark i samma arbetsbok) finns det en inbyggd funktion i Excel för att göra detta.

Det är Se sida vid sida alternativ.

Detta rekommenderas endast när du har en liten datamängd och manuellt jämför dessa filer sannolikt mindre tidskrävande och felbenägen. Om du har en stor datamängd rekommenderar jag att du använder den villkorade metoden eller formelmetoden som täcks senare i den här självstudien.

Låt oss se hur du använder detta när du måste jämföra två separata filer eller två ark i samma fil.

Anta att du har två filer i två olika månader och du vill kontrollera vilka värden som är olika i dessa två filer.

Som standard, när du öppnar en fil, kommer det troligtvis att ta upp hela skärmen. Även om du minskar storleken ser du alltid en Excel -fil högst upp.

Med alternativet vy sida vid sida kan du öppna två filer och sedan ordna dem horisontellt eller vertikalt. Detta gör att du enkelt kan jämföra värdena utan att växla fram och tillbaka.

Nedan följer stegen för att justera två filer sida vid sida och jämföra dem:

1. Öppna filerna som du vill jämföra.
2. Markera det ark du vill jämföra i varje fil.
3. Klicka på fliken Visa
4. I Windows -gruppen klickar du på alternativet "Visa sida vid sida". Detta blir endast tillgängligt när du har två eller flera Excel -filer öppna.

Så snart du klickar på alternativet Visa sida vid sida ordnar Excel arbetsboken horisontellt. Båda filerna kommer att vara synliga, och du är fri att redigera/jämföra dessa filer medan de är ordnade sida vid sida.

Om du vill ordna filerna vertikalt klickar du på alternativet Ordna alla (på fliken Visa).

Detta öppnar dialogrutan "Ordna Windows" där du kan välja "Vertikal".

Vid den här tiden, om du rullar ner i ett av kalkylbladet, skulle det andra förbli som det är. Du kan ändra detta så att när du bläddrar i ett ark, rullar det andra samtidigt samtidigt. Detta gör det lättare att göra en rad för rad jämförelse och upptäcka eventuella skillnader.

Men för att göra detta måste du aktivera Synkron rullning.

För att aktivera synkron rullning, klicka på fliken Visa (i någon av arbetsböckerna) och klicka sedan på alternativet Synkron rullning. Detta är en växlingsknapp (så om du vill stänga av den, klicka helt enkelt på den igen).

Jämföra flera ark i separata Excel-filer (sida vid sida)

Med alternativet "Visa sida vid sida" kan du bara jämföra två Excel -filer samtidigt.

Om du har flera Excel -filer öppna, när du klickar på alternativet Visa sida vid sida, kommer det att visa dig en 'Jämför sida vid sida' dialogruta, där du kan välja vilken fil du vill jämföra med den aktiva arbetsboken.

Om du vill jämföra mer än två filer samtidigt, öppna alla dessa filer och klicka sedan på alternativet Ordna alla (det finns på fliken Visa).

Välj Vertikal/Horisontell i dialogrutan Ordna Windows och klicka sedan på OK.

Detta ordnar alla öppna Excel -filer i vald ordning (vertikal eller horisontell).

Jämför två ark (sida vid sida) i samma Excel-arbetsbok

Om du vill jämföra två separata blad i samma arbetsbok kan du inte använda funktionen Visa sida vid sida (eftersom det bara fungerar för separata Excel -filer).

Men du kan fortfarande göra samma jämförelse sida vid sida.

Detta möjliggörs av 'Ny Windows -funktion i Excel, som låter dig öppna två instanser i samma arbetsbok. När du har två instanser öppna kan du ordna dessa sida vid sida och sedan jämföra dessa.

Anta att du har en Excel -arbetsbok som har två ark under två olika månader (januari och februari) och du vill jämföra dessa sida vid sida för att se hur försäljningen per butik har förändrats:

Nedan följer stegen för att jämföra två ark i Excel:

1. Öppna arbetsboken som har de blad som du vill jämföra.
2. Klicka på fliken Visa
3. Klicka på alternativet "Nytt fönster" i fönstergruppen. Detta öppnar den andra förekomsten av samma arbetsbok.
4. Klicka på "Ordna alla" på fliken "Visa". Detta öppnar dialogrutan Ordna Windows
5. Välj "Vertikal" för att jämföra data i kolumner (eller välj Horisontell om du vill jämföra data i rader).
6. Klicka på OK.

Stegen ovan skulle ordna båda instanserna av arbetsboken vertikalt.

Vid denna tidpunkt skulle båda arbetsböckerna ha samma arbetsblad valt. I en av arbetsböckerna väljer du det andra bladet som du vill jämföra med det aktiva bladet.

Hur fungerar detta?

När du klickar på Nytt fönster öppnas samma arbetsbok igen med ett något annat namn. Till exempel, om ditt arbetsboknamn är "Test" och du klickar på Nytt fönster, kommer det att öppna den redan öppna arbetsboken "Test - 1" och den andra förekomsten som "Test - 2".

Observera att dessa fortfarande är samma arbetsbok. Om du gör några ändringar i någon av dessa arbetsböcker återspeglas det i båda.

Och när du stänger någon instans av den öppna filen, skulle namnet återgå till originalet.

Du kan också aktivera synkron rullning om du vill (genom att klicka på alternativet 'Synkron rullning' på fliken 'Visa')

Jämför två ark och markera skillnader (med villkorlig formatering)

Även om du kan använda ovanstående metod för att anpassa arbetsböckerna tillsammans och manuellt gå igenom datan rad för rad, är det inte ett bra sätt om du har mycket data.

Att göra denna jämförelse manuellt kan också leda till många fel.

Så istället för att göra detta manuellt kan du använda kraften i villkorlig formatering för att snabbt markera eventuella skillnader i de två Excel -arken.

Denna metod är verkligen användbar om du har två versioner i två olika ark och du snabbt vill kontrollera vad som har ändrats.

Observera att du KAN INTE jämför två blad i olika arbetsböcker.

Eftersom villkorlig formatering inte kan hänvisa till en extern Excel -fil måste de ark du behöver jämföra finnas i samma Excel -arbetsbok. Om dessa inte är det kan du kopiera ett blad från den andra filen till den aktiva arbetsboken och sedan göra den här jämförelsen.

För det här exemplet, anta att du har en dataset som visas nedan i två månader (jan och feb) i två olika blad och du vill snabbt jämföra data i dessa två blad och kontrollera om priserna på dessa artiklar har ändrats eller inte.

Nedan följer stegen för att göra detta:

1. Välj data i arket där du vill markera ändringarna. Eftersom jag vill kontrollera hur priserna har ändrats från januari till februari har jag valt data i febbladet.
2. Klicka på fliken Hem
3. Klicka på "Villkorlig formatering" i gruppen Stilar
4. I alternativen som visas klickar du på "Ny regel"
5. I dialogrutan "Ny formateringsregel" klickar du på "Använd en formel för att avgöra vilka celler som ska formateras"
6. I formelfältet anger du följande formel: = B2Jan! B2
7. Klicka på knappen Format
8. I dialogrutan Formatceller som visas klickar du på fliken "Fyll" och väljer den färg där du vill markera data som inte matchar.
9. Klicka på OK
10. Klicka på OK

Stegen ovan skulle omedelbart markera eventuella ändringar i datamängden i båda arken.

Hur fungerar detta?

Villkorlig formatering markerar en cell när den angivna formeln för den cellen returnerar en TRUE. I det här exemplet jämför vi varje cell i ett ark med motsvarande cell i det andra arket (gjort med operatorn inte lika med formeln).

När villkorlig formatering hittar någon skillnad i data markerar det det i Jan -bladet (det där vi har tillämpat den villkorade formateringen.

Observera att jag har använt relativ referens i detta exempel (A1 och inte $ A $ 1 eller $ A1 eller A $ 1).

Kom ihåg följande när du använder denna metod för att jämföra två ark i Excel;

• Denna metod är bra för att snabbt identifiera skillnader, men du kan inte använda den fortlöpande. Till exempel, om jag anger en ny rad i någon av datamängderna (eller tar bort en rad), skulle det ge mig felaktiga resultat. Så snart jag sätter in/tar bort raden betraktas alla efterföljande rader som olika och markeras i enlighet därmed.
• Du kan bara jämföra två ark i samma Excel -fil
• Du kan bara jämföra värdet (inte skillnaden i formel eller formatering).

Jämför två Excel -filer/ark och få skillnaderna med hjälp av formel

Om du bara är intresserad av att snabbt jämföra och identifiera skillnaderna mellan två ark kan du använda en formel för att hämta endast de värden som är olika.

För den här metoden måste du ha ett separat kalkylblad där du kan hämta skillnaderna.

Denna metod skulle fungera om du vill jämföra två separata Excel -arbetsbok eller kalkylblad i samma arbetsbok.

Låt mig visa dig ett exempel där jag jämför två datamängder i två blad (i samma arbetsbok).

Anta att du har datauppsättningen som visas nedan i ett blad som heter Jan (och liknande data i ett blad som heter Feb), och du vill veta vilka värden som är olika.

För att jämföra de två arken, sätt först in ett nytt kalkylblad (låt oss kalla det här bladet för 'skillnad').

I cell A1 anger du följande formel:

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

Kopiera och klistra in denna formel för ett intervall så att den täcker hela datamängden i båda arken. Eftersom jag har en liten datamängd kommer jag bara att kopiera och klistra in denna formel i A1: B10 -intervall.

Ovanstående formel använder ett IF -villkor för att kontrollera om det finns skillnader. Om det inte finns någon skillnad i värdena, kommer det att returnera tomt, och om det finns en skillnad, kommer det att returnera värdena från båda arken i separata rader i samma cell.

Det som är bra med denna metod är att den bara ger dig skillnaderna och visar dig exakt vad skillnaden är. I det här exemplet kan jag enkelt se att priset i cell B4 och B8 är olika (liksom de exakta värdena i dessa celler).

Jämför två Excel -filer/ark och få skillnaderna med VBA

Om du behöver jämföra Excel -filer eller -ark ganska ofta är det en bra idé att ha en klar Excel -makro -VBA -kod och använda den när du behöver göra jämförelsen.

Du kan också lägga till makrot i verktygsfältet Snabbåtkomst så att du kan komma åt med en enda knapp och omedelbart veta vilka celler som är olika i olika filer/ark.

Anta att du har två ark Jan och Feb och du vill jämföra och markera skillnader i Jan -bladet, du kan använda nedanstående VBA -kod:

Sub CompareSheets () Dim rngCell som intervall för varje rngCell i kalkylblad ("Jan"). UsedRange If Not rngCell = Worksheets ("Feb"). Celler (rngCell.Row, rngCell.Column) Sedan rngCell.Interior.Color = vbYellow End If Next rngCell End Sub

Koden ovan använder For Next -slingan för att gå igenom varje cell i Jan -arket (hela det använda intervallet) och jämför den med motsvarande cell i Feb -bladet. Om den hittar en skillnad (som kontrolleras med If-Then-satsen) markerar den cellerna i gult.

Du kan använda den här koden i en vanlig modul i VB Editor.

Och om du behöver göra det ofta är det bättre att spara den här koden i arbetsboken Personal Macro och sedan lägga till den i verktygsfältet Snabbåtkomst. På det sättet kommer du att kunna göra denna jämförelse med ett klick på en knapp.

Här är stegen för att få Personal Macro Workbook i Excel (den är inte tillgänglig som standard så du måste aktivera den).

Här är stegen för att spara den här koden i Personal Macro Workbook.

Och här hittar du stegen för att lägga till denna makrokod i QAT.

Använda ett tredjepartsverktyg - XL -komparator

Ett annat snabbt sätt att jämföra två Excel-filer och söka efter matchningar och skillnader är att använda ett gratis tredjepartsverktyg som XL Comparator.

Detta är ett webbaserat verktyg där du kan ladda upp två Excel-filer och det kommer att skapa en jämförelsefil som innehåller data som är vanliga (eller olika data baserat på vilket alternativ du valde.

Anta att du har två filer som har kunddatauppsättningar (t.ex. namn och e -postadress), och du vill snabbt kontrollera vilka kunder det finns, fil 1 och inte fil 2.

Nedan ser du hur du jämför två Excel -filer och skapar en jämförelsesrapport:

1. Öppna https://www.xlcomparator.net/
2. Använd alternativet Välj fil för att ladda upp två filer (maximal storlek på varje fil kan vara 5 MB)
3. Klicka på knappen Nästa.
4. Välj den gemensamma kolumnen i båda dessa filer. Verktyget kommer att använda denna vanliga kolumn för att leta efter matchningar och skillnader
5. Välj ett av de fyra alternativen, oavsett om du vill få matchande data eller annan data (baserat på fil 1 eller fil 2)
6. Klicka på Nästa
7. Ladda ner jämförelsesfilen som innehåller data (baserat på vilket alternativ du valde i steg 5)

Nedan finns en video som visar hur XL Comparator -verktyget fungerar.

Ett problem du kan ha när du använder ett tredjepartsverktyg för att jämföra Excel-filer handlar om sekretess. Om du har konfidentiell data och sekretess verkligen är viktigt för det är det bättre att använda andra metoder som visas ovan. Observera att XL Comparator -webbplatsen nämner att de tar bort alla filer efter 1 timmes jämförelse.

Detta är några av de metoder du kan använda för att jämföra två olika Excel -filer (eller kalkylblad i samma Excel -fil). Hoppas du tyckte att denna Excel -handledning var användbar.