Hej Paula, kan du jämföra listor i Excel?
eller
Vad är det bästa sättet att jämföra två datamängder i Excel?
I Excel finns det ofta ett krav på att jämföra två listor eller två datamängder för att hitta saknade eller matchande objekt. Eftersom detta är Excel finns det alltid mer än ett sätt att göra saker och ting, inklusive att jämföra data. Från formler och villkorlig formatering till Power Query. I den här artikeln kommer vi att titta på ett antal sätt att jämföra två listor i Excel och vi kommer också att titta på att jämföra hela rader i en datamängd.
Möjligen är ditt föredragna sätt inte med nedan. Om inte, varför inte skriva en kommentar nedan och dela med dig av hur du gillar att jämföra två listor eller dataset i Excel. Jag ser fram emot att läsa dina kommentarer.
Vi vill jämföra lista 1 med lista 2.
Innehåll
Snabb villkorlig formatering för att jämföra två datakolumner
Rensa den villkorliga formateringen
Matcha data i Excel med hjälp av MATCH-funktionen
Genomföra 2 listor i Excel 365 med MATCH eller XMATCH som en. Dynamic Array-funktionen
MATCH och dynamiska arrayer för att jämföra två listor
XMATCH Excel 365 för att jämföra två listor
Tabeller – jämföra listor i Excel där storleken på områdena kan förändras
Highlighta skillnader i listor med hjälp av anpassad villkorlig formatering
Kopiera formel till anpassad villkorlig formatering
Andra formler som används för att jämföra två listor i Excel
VLOOKUP för att jämföra två listor i Excel
XLOOKUP för att jämföra två listor i Excel
COUNTIF för att jämföra två listor i Excel
Hur man jämför två datamängder i Excel
Genom att jämföra listor eller dataset med hjälp av Power Query
Snabb villkorlig formatering för att jämföra två datakolumner
Med hjälp av villkorlig formatering kan du markera en cell eller ett intervall baserat på fördefinierade kriterier. Det snabbaste och enklaste sättet att snabbt visuellt jämföra de här två kolumnerna är att använda den fördefinierade regeln för att markera dubbla värden.
Start med att markera de två datakolumnerna.
Från fliken Hem väljer du rullgardinsmenyn Villkorlig formatering. Välj sedan Regler för framhävande av celler. Välj sedan Duplicera värden.
En inställningsruta för Duplicera värden öppnas där du kan definiera formateringen och välja mellan Duplicera eller Unika värden.
Om du väljer Duplicera kommer alla återkommande poster att sättas till den valda formateringen. Nu kan du snabbt se vilka poster i Lista 1 som finns i Lista 2 eftersom det är de formaterade posterna. Du kan också snabbt se de poster i lista 2 som inte finns i lista 1 eftersom dessa inte har någon formatering.
Hur som helst kan du också formatera de unika posterna. Detta kan du göra genom att välja Unik i inställningsrutan Dubbla värden.
I det här fallet har vi tillämpat två olika villkorliga formateringar. Det röda indikerar dubbletter och det gröna indikerar unika objekt.
Notera att jag inte bara tog cellerna med data, jag tog alla kolumnerna A till C. Kolumn B har inga data, så den kan inte påverka resultatet. Dessa celler innehåller dock den villkorliga formateringen som tillämpats, så det skulle vara bättre praxis att bara välja de celler du behöver.
Rensa den villkorliga formateringen
Om du vill rensa all villkorlig formatering markerar du först cellen, eller området. Välj sedan rullgardinsmenyn Villkorlig formatering på bandet Hem. Därefter väljer du Rensa regler. Välj slutligen Rensa regler från valda celler.
Om du har tillämpat mer än en villkorlig formatering samtidigt och bara vill ta bort en av dessa väljer du Hantera regler i rullgardinsmenyn för villkorlig formatering. Markera den regel du vill ta bort och välj sedan Ta bort regel.
Om du trycker på OK tas regeln bort från Regelhanteraren och cellerna innehåller inte längre formateringen.
Det är alltså det mest grundläggande sättet du kan jämföra två listor i Excel. Det är snabbt, enkelt och effektivt. Du kan också tillämpa villkorlig formatering baserad på formler, vilket vi kommer att titta på senare i den här artikeln.
Match data i Excel med hjälp av funktionen MATCH
Det finns många uppslagsformler som du kan använda för att jämföra två intervall eller listor i Excel. Den första vi ska titta på är MATCH-funktionen.
MATCH-funktionen returnerar den relativa positionen i en lista. Ett nummer baserat på dess position, om det hittas, i uppslagsarrayn.
Syntaxen för MATCH är
=MATCH (uppslagsvärde, Uppslagsarray, Match typ)
Där uppslagsvärde är det värde som du vill hitta en matchning för. Lookup array är den lista i vilken du letar efter en matchning. Och Match type låter dig välja mellan en exakt eller ungefärlig matchning.
Vi vill skriva en matchningsformel för att se om objekten i Lista 2 finns i Lista 1.
I cell E3 kan vi skriva in formeln
=MATCH(C2, $A$2:$A$21,0)
Om du fyller i den här formeln kommer positionen för de värden där Excel hittar en matchning att returneras. Om det inte finns någon matchning kommer returvärdet att vara #N/A.
Softa har den relativa positionen eller #N/A inget värde för oss och vi måste omvandla dessa värden till sant eller falskt. För att göra detta kan vi enkelt utöka vår Match-formel med en logisk funktion. Eftersom Match returnerar ett tal kan vi använda funktionen ISNUMBER
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Jämför 2 listor i Excel 365 med MATCH eller XMATCH som Dynamic Array-funktion
Om du använder Excel 365 har du ytterligare alternativ när du använder MATCH för att jämföra listor eller data. Eftersom Excel 365 tänker i matriser kan vi nu skicka en matris som uppslagsvärde för MATCH och våra resultat kommer att spillas för oss. På så sätt slipper du kopiera formeln och med bara en formel blir kalkylbladet mindre känsligt för fel och mer kompakt.
MATCH och dynamiska matriser för att jämföra två listor
Om du ännu inte är bekant med dynamiska matriser föreslår jag att du läser den här artikeln: Om du inte är helt nybörjare på dynamiska matriser kan du läsa följande artikel: Excel Dynamic Arrays – A new way to model your Excel Spreadsheets: för att få en bättre förståelse för hur de fungerar och för spillområden.
Den enda ändringen i matchformeln är att istället för att välja cell C2 som vårt uppslagsvärde kommer vi att välja intervallet C2:12
=ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))
Som du kan se spiller den ena formeln ut resultaten i kolumn E.
XMATCH Excel 365 för att jämföra två listor
Excel 365 introducerar också den nya funktionen XMATCH. Precis som funktionen MATCH returnerar XMATCH en relativ position i en lista. Nu är du bekant med XLOOKUP, som ersätter den gamla VLOOKUP-funktionen, du vet att XLOOKUP kommer med ytterligare kraft. Detta kommer i form av nya villkor i formelsyntaxen, t.ex. sökläge och matchningstyper. XMATCH har också denna extra kraft jämfört med föregångaren MATCH.
Syntaxen för XMATCH är
XMATCH (Lookup Value, Lookup Array, ,)
Varifrån
Lookup Value är det värde som du letar efter för att hitta den relativa positionen
Lookup Array är den rad eller kolumn som innehåller Lookup Value
Match mode är valfritt. Till skillnad från den gamla MATCH-funktionen är standardvärdet en exakt matchning. Du kan också välja mellan
- Exakt matchning eller nästa minsta
- Exakt matchning eller nästa största
- Wildcard matchning
Sökläge är också valfritt. Standardvärdet (och enda alternativet i den gamla MATCH-funktionen) är att söka uppifrån och ned. Du kan också välja sökning från sist till först och binär sökning. Om du arbetar med binära sökningar. Alternativet wildcard matchning fungerar inte.
Med XMATCH kan vi använda antingen Dynamic arrays eller cellreferenser för att skapa formeln, precis som vi har tittat på med MATCH. I det här exemplet kommer vi att använda dynamiska matriser. Formeln är mycket lik den vi använde med MATCH, förutom att vi inte behöver välja 0 för en exakt matchning eftersom detta är standardinställningen i XMATCH. Låt oss blanda upp saker och ting lite den här gången och titta på att hitta objekt i lista 2 som inte finns i lista 1.
I det här fallet kan vi använda formeln
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Varvid not kommer att förvandla sanningar till falska och falska till sanningar.
Tabeller – Jämförelse av listor i Excel där intervallens storlek kan förändras
I varje formel som vi har tittat på hittills har vi valt ett cellområde i våra Match-funktioner som inte är dynamiskt. Det innebär att om vi lägger till nya data i en av listorna har vi ett manuellt steg för att uppdatera vår formel för att inkludera de nya uppgifterna.
För att konvertera listorna till tabeller markerar du en av listorna och trycker på CTRL. Detta är tangentbordsgenvägen för att konvertera till en tabell. Om du markerade rubriken i cellintervallet, se till att du kryssar i rutan för att bekräfta att tabellen har rubriker.
Tabeller använder till sin natur strukturerad namngivning. När du skriver en formel och väljer en kolumn i en tabell visas därför inte cellreferenser utan kolumnnamnet.
Om vi tittar på vår tidigare formel som använder XMATCH för att hitta objekt i lista 2 som inte finns i lista 1, kan vi skriva om denna funktion nu med hjälp av våra tabellreferenser
=NOT (ISNUMBER(XMATCH(list2,list1)))
Nu, eftersom vi har använt tabeller, om vi lägger till en ny rad i någon av tabellerna, kommer vårt spillområde också att öka för att inkludera de nya uppgifterna.
Markera skillnader i listor med hjälp av anpassad villkorlig formatering
Tidigare i den här artikeln tittade vi på ett mycket snabbt sätt att jämföra de här två listorna med hjälp av en fördefinierad regel för dubbletter. Vi kan dock också använda anpassad villkorlig formatering. Om du inte är bekant med anpassad villkorlig formatering föreslår jag att du läser den här artikeln: Excel Dynamic Conditional Formatting Tricks:
Vi måste titta på två olika tillvägagångssätt här för att belysa skillnader. När vi inte använder tabeller och vi har skapat en sant/felformel för att identifiera skillnaderna kan vi ta en kopia av formeln och lägga till den i vår anpassade formatering. Med tabeller måste vi dock tvinga fram användningen av cellreferenser.
Kopiera formel till anpassad villkorlig formatering
Start med att ta en kopia av formeln. Eftersom vi har testat formeln i kalkylbladet kan vi se att den fungerar innan vi använder den i villkorlig formatering. Detta är bästa praxis eftersom det mycket ofta med relativa och absoluta cellreferenser kan vara svårt att få formeln korrekt.
Välj de celler som du vill tillämpa den anpassade formateringen på. I bandet Hem väljer du sedan rullgardinsmenyn Villkorlig formatering och väljer Ny regel
Inställningsrutan Ny formateringsregel öppnas och välj Använd formel för att bestämma vilka celler som ska formateras. Klistra sedan in formeln och ange formateringstyp
Detta kommer att resultera i att alla celler som finns i båda listorna formateras i det format som du har valt.
Håll i åtanke att vi valde ett intervall av celler för att tillämpa den här villkorliga formateringen och att den inte är dynamisk. Om vi skulle använda tabeller skulle detta uppdateras utan att vi behöver ändra något!
Andra formler som används för att jämföra två listor i Excel
Det finns många formler som du kan använda för att jämföra två listor i Excel. Vi har redan tittat på MATCH och XMATCH, men nu ska vi titta på ytterligare några. Alla uppslagsfunktioner fungerar verkligen tillsammans med några andra!
VLOOKUP för att jämföra två listor i Excel
Om du inte är bekant med VLOOKUP kan du läsa om det här. Enkelt uttryckt VLOOKUP returnerar ett motsvarande värde från en cell, om det inte finns något motsvarande värde returneras ett #N/A-fel. I vårt exempel arbetar vi med text. Så vi kan utföra en VLOOKUP och testa om den returnerar text. Om vi använder siffror kan vi ersätta ISTEXT med ISNUMBER.
Vi skulle kunna använda funktionen =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Och om vi använde oss av Dynamiska matriser i Excel 365 skulle vi kunna använda funktionen
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP för att jämföra två listor i Excel
XLOOKUP infördes i Excel 365 och du kan läsa mer om den här. Mycket likt VLOOKUP returnerar XLOOKUP ett motsvarande värde från en cell, och du kan definiera ett resultat om värdet inte hittas. Med hjälp av Dynamic arrays skulle funktionen vara
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF för att jämföra två listor i Excel
Funktionen COUNTIF räknar antalet gånger som ett värde eller en text ingår i ett intervall. Om värdet inte hittas returneras 0. Vi kan kombinera detta med ett IF-statement för att returnera våra sanna och falska värden.
=IF(COUNTIF (A2:A21,C2:C12)<>0, ”True”, ”False”)
Så här kan du jämföra två datamängder i Excel
Det är enkelt att jämföra två listor och vi har nu tittat på flera olika sätt att göra detta. Men att jämföra två datamängder kan vara lite svårare.
Låt oss titta på ett exempel. Vi har två tabeller med data som båda innehåller samma kolumnrubriker. Om vi tittar på bilden kan vi se att om vi matchar dessa två tabeller måste vi titta på mer än en kolumn.
När du behöver titta på mer än en kolumn är lösningen att skapa en sammansatt kolumn som kombinerar data till en kolumn. Detta skapar en unik kolumn för varje rad som vi sedan kan använda som matchande kolumn
I det här exemplet kan vi kombinera Namn och DoB för att ge varje tabell en unik identifierare
Det finns många sätt att sammanfoga innehållet i en cell, i det här fallet kommer vi att göra en enkel concatenate. Eftersom vi använder tabeller kommer formeln att följa tabellens namngivningsformat.
= &”-”&
Förfarande samma steg för den andra tabellen.
Nu kan vi använda något av exemplen ovan för att matcha dessa två nya datakolumner. När de matchar vet vi att de matchar hela raden.
Jämförelse av listor eller datamängder med hjälp av Power Query
Du kan också jämföra listor och datamängder med Excels Power Query. Genom att ansluta till tabellerna och sedan slå samman tabellerna med hjälp av olika typer av sammanfogningar kan vi jämföra båda listorna.
I den här videon får du lära dig att jämföra eller stämma av två olika datamängder med hjälp av Excels Power Query
Det finns en datamängd som följer med den här videon och övningen som du kan hämta från den här artikeln.