Hej Paula, kan du sammenligne lister i Excel?
eller
Hvad er den bedste måde at sammenligne to datasæt i Excel?
Ofte er der et krav i Excel om at sammenligne to lister eller to datasæt for at finde manglende eller matchende elementer. Da det er Excel, er der altid mere end én måde at gøre tingene på, herunder at sammenligne data. Fra formler og betinget formatering til Power Query. I denne artikel vil vi se på en række måder at sammenligne to lister i Excel på, og vi vil også se på sammenligning af hele rækker i et datasæt.
Måske er din foretrukne måde ikke medtaget nedenfor. Hvis ikke, hvorfor smider du ikke en kommentar nedenfor og deler med os, hvordan du kan lide at sammenligne to lister eller datasæt i Excel. Jeg ser frem til at læse dine kommentarer.
Vi ønsker at sammenligne Liste 1 med Liste 2.
Indhold
Snakkekonditionel formatering til sammenligning af to datakolonner
Klargøring af den betingede formatering
Match data i Excel ved hjælp af MATCH-funktionen
Sammenlign 2 lister i Excel 365 med MATCH eller XMATCH som en Dynamic Array-funktion
MATCH og dynamiske arrays til sammenligning af 2 lister
XMATCH Excel 365 til sammenligning af 2 lister
Tabeller – Sammenligning af lister i Excel, hvor intervallernes størrelse kan ændre sig
Hæv forskelle i lister ved hjælp af brugerdefineret betinget formatering
Kopiering formel til Brugerdefineret betinget formatering
Andre formler, der bruges til at sammenligne to lister i Excel
VLOOKUP til at sammenligne to lister i Excel
XLOOKUP til at sammenligne to lister i Excel
COUNTIF til at sammenligne to lister i Excel
Hvordan sammenligner man 2 datasæt i Excel
Sammenligning af lister eller datasæt ved hjælp af Power Query
Hurtig betinget formatering for at sammenligne to datakolonner
Betinget formatering giver dig mulighed for at fremhæve en celle eller et område på baggrund af foruddefinerede kriterier. Den hurtigste og enkleste måde at sammenligne disse to kolonner hurtigt visuelt på er at bruge den foruddefinerede regel om fremhævelse af dobbeltværdi.
Start med at vælge de to datakolonner.
Fra fanen Start skal du vælge rullemenuen Betinget formatering. Vælg derefter Fremhæv celleregler. Vælg derefter Duplikér værdier.
Der åbnes et indstillingsfelt for Duplikér værdier, hvor du kan definere formateringen og vælge mellem Duplikér eller Unikke værdier.
Ved valg af Duplikér vil alle tilbagevendende poster blive indstillet til den valgte formatering. Nu kan du hurtigt se de poster i Liste 1, der er i Liste 2, da det er de formaterede poster. Du kan også hurtigt se de poster i liste 2, der ikke er i liste 1, da disse ikke har fået påført formatering.
Du kan dog også formatere de Unikke poster. Det kan du gøre ved at vælge Unikke i indstillingsboksen Duplikatværdier.
I dette tilfælde har vi anvendt to forskellige betingede formateringer. Den røde, der angiver dubletterne, og den grønne, der angiver de unikke elementer.
Bemærk, at jeg ikke bare tog cellerne med data, jeg tog alle kolonnerne A til C. Kolonne B har ingen data, så den kan ikke påvirke resultaterne. Disse celler indeholder dog den betingede formatering, der er anvendt, så det ville være bedre praksis kun at vælge de celler, du har brug for.
Rydde den betingede formatering
For at rydde al betinget formatering skal du først vælge cellen eller området. Vælg derefter rullemenuen Betinget formatering på båndet Start. Vælg derefter Ryd regler. Vælg til sidst Ryd regler fra valgte celler.
Hvis du har anvendt mere end én betinget formatering på én gang, og du kun vil fjerne én af disse, skal du vælge Administrer regler fra rullemenuen for betinget formatering. Vælg den regel, du vil slette, og vælg derefter Slet regel.
Du trykker på OK, og reglen fjernes fra Regelhåndtering, og cellerne indeholder ikke længere formateringen.
Så det er den mest grundlæggende måde, du kan sammenligne to lister i Excel på. Den er hurtig, enkel og effektiv. Du kan også anvende betinget formatering baseret på formler, hvilket vi vil se på senere i denne artikel.
Sammenlign data i Excel ved hjælp af MATCH-funktionen
Der findes mange opslagsformler, som du kan bruge til at sammenligne to intervaller eller lister i Excel. Den første, vi vil se på, er MATCH-funktionen.
MATCH-funktionen returnerer den relative position i en liste. Et tal baseret på dets position, hvis det findes, i opslagsarrayet.
Syntaksen for MATCH er
=MATCH (opslagsværdi, opslagsarray, matchtype)
Hvor opslagsværdi er den værdi, du ønsker at finde et match for. Lookup array er den liste, som du søger efter et match i. Og Match type giver dig mulighed for at vælge mellem et nøjagtigt eller tilnærmelsesvis match.
Vi ønsker at skrive en matchformel for at se, om elementerne i Liste 2 er i Liste 1.
I celle E3 kan vi indtaste formlen
=MATCH(C2, $A$2:$A$21,0)
Gennem at udfylde denne formel, vil der for de værdier, hvor Excel finder et match, blive returneret positionen for dette match. Hvor der ikke er noget match, vil returværdien være en #N/A.
Meget ofte har den relative position eller #N/A ingen værdi for os, og vi har brug for at konvertere disse værdier til sandt eller falsk. For at gøre dette kan vi nemt udvide vores Match-formel ved hjælp af en logisk funktion. Da Match returnerer et tal, kan vi bruge ISNUMBER-funktionen
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Sammenlign 2 lister i Excel 365 med MATCH eller XMATCH som en dynamisk array-funktion
Hvis du bruger Excel 365, har du yderligere alternativer, når du bruger MATCH til at sammenligne lister eller data. Da Excel 365 tænker i arrays, kan vi nu videregive et array som opslagsværdi for MATCH, og vores resultater vil blive spildt for os. Dette fjerner behovet for at kopiere formlen ned, og med kun 1 formel vil dit regneark være mindre udsat for fejl og mere kompakt.
MATCH og dynamiske arrays til sammenligning af 2 lister
Hvis du endnu ikke er bekendt med dynamiske arrays, vil jeg foreslå, at du læser denne artikel: Excel Dynamic Arrays – A new way to model your Excel Spreadsheets: for at få en bedre forståelse af, hvordan de fungerer og spildområder.
Den eneste ændring i matchformlen er, at i stedet for at vælge celle C2 som vores opslagsværdi vil vi vælge området C2:12
=ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))
Som du kan se, spilder den ene formel resultaterne ned i kolonne E.
XMATCH Excel 365 til at sammenligne to lister
Excel 365 introducerer også den nye funktion XMATCH. Ligesom MATCH-funktionen returnerer XMATCH en relativ position i en liste. Nu kender du XLOOKUP, som erstatter den gamle VLOOKUP-funktion, og du ved, at XLOOKUP kommer med yderligere muligheder. Dette kommer i form af nye betingelser i formelsyntaksen som f.eks. søgemåde og matchtyper. XMATCH har også disse ekstra muligheder i forhold til sin forgænger MATCH.
Syntaksen for XMATCH er
XMATCH (Lookup Value, Lookup Array, ,)
Hvor
Lookup Value er den værdi, du søger at finde den relative position
Lookup Array er den række eller kolonne, der indeholder Lookup Value
Match mode er valgfri. I modsætning til den gamle MATCH-funktion er standardindstillingen et nøjagtigt match. Du kan også vælge mellem
- Eksakt match eller næstmindste
- Eksakt match eller næststørste
- Wildcard match
Søgemodus er også valgfri. Standardindstillingen (og den eneste mulighed i den gamle MATCH-funktion) er at søge fra toppen og nedad. Du kan også vælge søgning fra sidst til først og binære søgninger. Hvis du arbejder med binære søgninger. Den vilde matchmulighed fungerer ikke.
Med XMATCH kan vi bruge enten Dynamic arrays eller cellereferencer til at oprette formlen, ligesom vi har kigget på med MATCH. I dette eksempel vil vi bruge dynamiske arrays. Formlen er meget lig den, vi brugte med MATCH; bortset fra at vi ikke behøver at vælge 0 for et nøjagtigt match, da det i XMATCH er standardindstillingen. Lad os blande tingene lidt op denne gang et kig på at finde elementer i liste 2, der ikke er i liste 1.
I dette tilfælde kan vi bruge formlen
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Hvor not vil forvandle sandheder til falsk og falsk til sandheder.
Tabeller – Sammenligning af lister i Excel, hvor intervallernes størrelse kan ændre sig
I hver af de formler, vi har kigget på indtil videre, har vi valgt et celleområde i vores Match-funktioner, som ikke er dynamisk. Det betyder, at hvis vi tilføjer nye data til en af listerne, har vi et manuelt skridt til at opdatere vores formel for at inkludere de nye data.
For at konvertere listerne til tabeller skal du vælge en af listerne og trykke på CTRL. Dette er tastaturgenvejen til at konvertere til en tabel. Hvis du har valgt overskriften i celleområdet, skal du sikre dig, at du markerer feltet for at bekræfte, at din tabel har overskrifter.
Tabeller anvender i sagens natur en struktureret navngivning. Når du skriver en formel, og du vælger en kolonne fra en tabel, vises derfor ikke cellehenvisninger, men kolonnens navn, når du skriver en formel.
Hvis vi ser på vores tidligere formel, hvor vi bruger XMATCH til at finde elementer i liste 2, der ikke er i liste 1, kan vi nu omskrive denne funktion ved hjælp af vores tabelreferencer
=NOT (ISNUMBER(XMATCH(list2,list1)))
Nu, da vi har brugt tabeller, hvis vi tilføjer en ny række til en af tabellerne, vil vores spill-område også øges for at inkludere de nye data.
Fremhæv forskelle i lister ved hjælp af brugerdefineret betinget formatering
Tidligere i denne artikel kiggede vi på en meget hurtig måde at sammenligne disse to lister på ved hjælp af en foruddefineret regel for dubletter. Vi kan dog også bruge Brugerdefineret betinget formatering. Hvis du ikke er bekendt med Brugerdefineret betinget formatering, vil jeg foreslå dig at tjekke denne artikel: Excel Dynamic Conditional Formatting Tricks:
Vi er nødt til at se på to forskellige tilgange her for at fremhæve forskelle. Når vi ikke bruger tabeller, og vi har oprettet en sand/falsk-formel til at identificere forskellene, kan vi tage en kopi af formlen og tilføje den til vores Brugerdefineret formatering. Men med tabeller skal vi tvinge brugen af cellehenvisninger frem.
Kopier formel til Brugerdefineret betinget formatering
Start med at tage en kopi af formlen. Da vi har testet formlen i regnearket, kan vi se, at den virker, før vi bruger den i betinget formatering. Dette er bedste praksis, da det meget ofte med relative og absolutte cellehenvisninger kan være svært at få formlen korrekt.
Vælg de celler, som du vil anvende den brugerdefinerede formatering på. Vælg derefter fra Home-båndet drop-down-menuen Betinget formatering, og vælg Ny regel
Feltet Opsætning af ny formatteringsregel åbnes, og vælg Brug formel til at bestemme, hvilke celler der skal formateres. Indsæt derefter formlen, og indstil formateringstypen
Dette vil resultere i, at alle celler, der er i begge lister, bliver formateret til det valgte format.
Husk, at vi har valgt et område af celler til at anvende denne betingede formatering, og at den ikke er dynamisk. Hvis vi skulle bruge tabeller, ville dette blive opdateret uden at skulle ændre noget!
Andre formler, der bruges til at sammenligne to lister i Excel
Der er mange formler, du kan bruge til at sammenligne to lister i Excel. Vi har allerede set på MATCH og XMATCH, men nu skal vi se på et par stykker mere. Enhver af opslagsfunktionerne vil virkelig fungere sammen med nogle andre!
VLOOKUP til at sammenligne to lister i Excel
Hvis du ikke er bekendt med VLOOKUP, kan du læse om det her. Enkelt sagt vil VLOOKUP returnere en tilsvarende værdi fra en celle, hvis der ikke er nogen tilsvarende værdi vil en #N/A fejl blive returneret. I vores eksempel arbejder vi med tekst. Så vi kan udføre en VLOOKUP og teste, om den returnerer tekst. Hvis vi brugte tal, kunne vi erstatte ISTEXT med ISNUMBER.
Vi kunne bruge funktionen =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Og hvis vi brugte Dynamiske arrays i Excel 365, kunne vi bruge funktionen
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP til at sammenligne to lister i Excel
XLOOKUP blev introduceret i Excel 365, og du kan læse mere om den her. Meget ligesom VLOOKUP vil XLOOKUP returnere en tilsvarende værdi fra en celle, og du kan definere et resultat, hvis værdien ikke findes. Ved hjælp af Dynamic arrays ville funktionen være
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF til at sammenligne to lister i Excel
Funktionen COUNTIF tæller antallet af gange, hvor en værdi eller tekst er indeholdt i et område. Hvis værdien ikke findes, returneres 0. Vi kan kombinere dette med en IF-anvisning for at returnere vores sande og falske værdier.
=IF(COUNTIF (A2:A21,C2:C12)<>0, “True”, “False”)
Sådan sammenligner du 2 datasæt i Excel
Det er nemt nok at sammenligne to lister, og vi har nu set på flere måder at gøre dette på. Men det kan være lidt vanskeligere at sammenligne to datasæt.
Lad os se på et eksempel. Vi har to tabeller med data, som hver indeholder de samme kolonneoverskrifter. Når vi ser på billedet, kan vi se, at det vil kræve at se på mere end én kolonne at sammenligne disse to tabeller.
Når du har brug for at se på mere end én kolonne, vil løsningen være at oprette en sammensat kolonne, der kombinerer dataene i én kolonne. Dette vil skabe en unik kolonne for hver række, som vi derefter kan bruge som den matchende kolonne
I dette eksempel kunne vi kombinere Navn og DoB for at give hver tabel en unik identifikator
Der er mange måder at sammenføje indholdet af en celle på, i dette tilfælde vil vi lave en simpel sammenkædning. Da vi bruger tabeller, vil formlen skuffe tabellens navngivningsformat.
= &”-“&
Gentag trinene for den anden tabel.
Nu kan vi bruge et af ovenstående eksempler til at matche disse to nye datakolonner. Hvor de stemmer overens, ved vi, at de stemmer overens med hele rækken.
Sammenligning af lister eller datasæt ved hjælp af Power Query
Du kan også sammenligne lister og datasæt ved hjælp af Excels Power Query. Ved at forbinde til tabellerne og derefter sammenlægge tabellerne ved hjælp af forskellige join-typer kan vi sammenligne begge lister.
I denne video lærer du, hvordan du sammenligner eller afstemmer to forskellige datasæt ved hjælp af Excels Power query
Der er et datasæt til at gå sammen med denne video og øvelse sammen, som du kan hente fra denne artikel.