En af Microsoft Excel mange muligheder er muligheden for at sammenligne to lister med data, identificere match mellem listerne og identificere, hvilke elementer der kun findes i en liste. Dette er nyttigt, når man sammenligner finansielle poster eller kontrollerer, om et bestemt navn er i en database. Du kan bruge funktionen MATCH til at identificere og markere matchende eller ikke-matchende poster, eller du kan bruge konditioneringsformatering med COUNTIF-funktionen. Følgende trin fortæller dig, hvordan du bruger hver til at matche dine data.
Trin
Metode 1 af 2: Identificering af poster med MATCH -funktionen
Trin 1. Kopier datalisterne til et enkelt regneark
Excel kan arbejde med flere regneark i en enkelt projektmappe eller med flere projektmapper, men det er lettere at sammenligne listerne, hvis du kopierer deres oplysninger til et enkelt regneark.
Trin 2. Giv hvert listeelement en unik identifikator
Hvis dine to lister ikke deler en fælles måde at identificere dem på, skal du muligvis tilføje en ekstra kolonne til hver dataliste, der identificerer elementet i Excel, så det kan se, om et element på en given liste er relateret til et element i den anden liste. Typen af denne identifikator afhænger af den slags data, du forsøger at matche. Du skal bruge en identifikator for hver kolonneliste.
- For finansielle data forbundet med en given periode, f.eks. Med skatteoptegnelser, kan dette være beskrivelsen af et aktiv, datoen for aktivets erhvervelse eller begge dele. I nogle tilfælde kan en post identificeres med et kodenummer; Men hvis det samme system ikke bruges til begge lister, kan denne identifikator oprette kampe, hvor der ikke er nogen eller ignorere kampe, der skal foretages.
- I nogle tilfælde kan du tage varer fra en liste og kombinere dem med varer fra en anden liste for at oprette en identifikator, f.eks. En fysisk aktivbeskrivelse og det år, den blev købt. For at oprette en sådan identifikator skal du sammenkoble (tilføje, kombinere) data fra to eller flere celler ved hjælp af ampersand (&). Hvis du vil kombinere en varebeskrivelse i celle F3 med en dato i celle G3, adskilt af et mellemrum, skal du indtaste formlen '= F3 & "" & G3' i en anden celle i den række, f.eks. E3. Hvis du kun ville medtage året i identifikatoren (fordi den ene liste bruger fulde datoer og den anden kun bruger år), ville du inkludere YEAR -funktionen ved at indtaste '= F3 & "" & YEAR (G3)' i celle E3 i stedet. (Inkluder ikke de enkelte citater; de er der kun for at angive eksemplet.)
- Når du har oprettet formlen, kan du kopiere den til alle andre celler i identifikationskolonnen ved at markere cellen med formlen og trække fyldhåndtaget hen over de andre celler i kolonnen, hvor du vil kopiere formlen. Når du slipper din museknap, udfyldes hver celle, du trak over, med formlen, hvor cellereferencerne blev justeret til de relevante celler i samme række.
Trin 3. Standardiser data, hvor det er muligt
Mens sindet anerkender, at "Inc." og "Incorporated" betyder det samme, Excel gør det ikke, medmindre du får det til at formatere det ene eller det andet ord igen. På samme måde kan du betragte værdier som $ 11, 950 og $ 11, 999,95 som tæt nok til at matche, men Excel vil ikke, medmindre du fortæller det til.
- Du kan håndtere nogle forkortelser, f.eks. "Co" for "Company" og "Inc" for "Incorporated ved at bruge VENSTRE strengfunktion til at afkorte de ekstra tegn. Andre forkortelser, f.eks." Assn "for" Association ", kan bedst være behandlet ved at etablere en dataindtastningsguide og derefter skrive et program til at slå op og rette forkerte formater.
- For talstrenge, f.eks. Postnumre, hvor nogle poster indeholder ZIP+4 -suffikset, og andre ikke gør det, kan du igen bruge VENSTRE strengfunktionen til kun at genkende og matche de primære postnumre. For at få Excel til at genkende numeriske værdier, der er tætte, men ikke det samme, kan du bruge funktionen RUND til at afrunde tætte værdier til det samme tal og matche dem.
- Ekstra mellemrum, f.eks. At skrive to mellemrum mellem ord i stedet for et, kan fjernes ved hjælp af funktionen TRIM.
Trin 4. Opret kolonner til sammenligningsformlen
Ligesom du skulle oprette kolonner til listeidentifikatorerne, skal du oprette kolonner for den formel, der sammenligner for dig. Du skal bruge en kolonne til hver liste.
Du vil gerne mærke disse kolonner med noget i stil med "Mangler du?"
Trin 5. Indtast sammenligningsformlen i hver celle
Til sammenligningsformlen bruger du MATCH -funktionen, der er indlejret i en anden Excel -funktion, ISNA.
- Formlen har formen "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", hvor en celle i identifikationskolonnen på den første liste sammenlignes med hvert af identifikatorerne i den anden liste med se om det matcher en af dem. Hvis den ikke matcher, mangler der en post, og ordet "TRUE" vises i cellen. Hvis den stemmer overens, er posten til stede, og ordet "FALSE" vises. (Når du indtaster formlen, må du ikke inkludere de vedlagte citater.)
- Du kan kopiere formlen til de resterende celler i kolonnen på samme måde som du kopierede celleidentifikatorformlen. I dette tilfælde ændres kun cellereferencen for identifikationscellen, idet dollartegnene foran rækken og kolonnehenvisninger for de første og sidste celler på listen over de anden celleidentifikatorer gør dem til absolutte referencer.
- Du kan kopiere sammenligningsformlen for den første liste til den første celle i kolonnen for den anden liste. Du bliver derefter nødt til at redigere cellereferencerne, så "G3" erstattes med referencen til den første identifikationscelle på den anden liste, og "$ L $ 3: $ L $ 14" erstattes med den første og sidste identifikationscelle i anden liste. (Lad dollartegnene og kolon være i fred.) Du kan derefter kopiere denne redigerede formel til de resterende celler i sammenligningsrækken på den anden liste.
Trin 6. Sorter listerne for lettere at se værdier, der ikke matcher, om nødvendigt
Hvis dine lister er store, skal du muligvis sortere dem for at sætte alle de ikke-matchende værdier sammen. Instruktionerne i deltrinnene nedenfor konverterer formlerne til værdier for at undgå genberegningsfejl, og hvis dine lister er store, vil det undgå en lang genberegningstid.
- Træk musen over alle cellerne på en liste for at markere den.
- Vælg Kopi fra menuen Rediger i Excel 2003 eller fra udklipsholdergruppen på båndet Hjem i Excel 2007 eller 2010.
- Vælg Indsæt special i menuen Rediger i Excel 2003 eller fra rullemenuen Indsæt i udklipsholderen i Excel 2007 eller 2010s Home -båndet.
- Vælg "Værdier" fra listen Indsæt som i dialogboksen Indsæt special. Klik på OK for at lukke dialogboksen.
- Vælg Sorter i menuen Data i Excel 2003 eller gruppen Sorter og filtrer på båndet Data i Excel 2007 eller 2010.
- Vælg "Header row" fra listen "My data range has" i dialogboksen Sorter efter, vælg "Mangler?" (eller det navn, du faktisk gav sammenligningskolonneoverskriften), og klik på OK.
- Gentag disse trin for den anden liste.
Trin 7. Sammenlign de ikke-matchende varer visuelt for at se, hvorfor de ikke matcher
Som tidligere bemærket er Excel designet til at lede efter nøjagtige datamatcher, medmindre du konfigurerer det til at lede efter omtrentlige dem. Din ikke-match kan være lige så simpel som en utilsigtet transponering af bogstaver eller cifre. Det kan også være noget, der kræver uafhængig verifikation, f.eks. At kontrollere, om børsnoterede aktiver i første omgang skulle rapporteres.
Metode 2 af 2: Betinget formatering med COUNTIF
Trin 1. Kopier datalisterne til et enkelt regneark
Trin 2. Beslut på hvilken liste du vil fremhæve matchende eller ikke-matchende poster
Hvis du kun vil fremhæve poster i én liste, vil du sandsynligvis fremhæve de poster, der er unikke for denne liste; det vil sige poster, der ikke matcher poster i den anden liste. Hvis du vil fremhæve poster i begge lister, vil du fremhæve poster, der matcher hinanden. I forbindelse med dette eksempel antager vi, at den første liste optager celler G3 til G14, og den anden liste optager cellerne L3 til og med L14.
Trin 3. Vælg de elementer på listen, du vil fremhæve unikke eller matchende elementer i
Hvis du ønsker at fremhæve matchende elementer på begge lister, skal du vælge listerne én ad gangen og anvende sammenligningsformlen (beskrevet i det næste trin) på hver liste.
Trin 4. Anvend den passende sammenligningsformel
For at gøre dette skal du få adgang til dialogboksen Betinget formatering i din version af Excel. I Excel 2003 gør du det ved at vælge Betinget formatering i menuen Format, mens du i Excel 2007 og 2010 klikker på knappen Betinget formatering i gruppen Stiler på båndet Hjem. Vælg regeltypen som "Formel", og indtast din formel i feltet Rediger regelbeskrivelse.
- Hvis du vil fremhæve poster, der er unikke for den første liste, ville formlen være "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", med celleområdet på den anden liste gengivet som absolutte værdier og referencen til den første celle i den første liste som en relativ værdi. (Indtast ikke de tætte citater.)
- Hvis du vil fremhæve poster, der er unikke for den anden liste, ville formlen være "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", med celleområdet på den første liste gengivet som absolutte værdier og referencen til den første celle i den anden liste som en relativ værdi. (Indtast ikke de tætte citater.)
- Hvis du vil fremhæve de poster i hver liste, der findes på den anden liste, skal du bruge to formler, en til den første liste og en til den anden. Formlen for den første liste er "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", mens formlen for den anden liste er COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Som tidligere bemærket, vælger du den første liste for at anvende dens formel og derefter vælger den anden liste for at anvende dens formel.
- Anvend den formatering, du vil markere de poster, der markeres. Klik på OK for at lukke dialogboksen.
Video - Ved at bruge denne service kan nogle oplysninger blive delt med YouTube
Tips
- I stedet for at bruge en cellereference med metoden COUNTIF betinget formatering kan du indtaste en værdi, der skal søges efter, og markere en eller flere lister for forekomster af denne værdi.
- For at forenkle sammenligningsformularerne kan du oprette navne til din liste, f.eks. "Liste1" og "Liste2". Når du skriver formlerne, kan disse listenavne erstatte de absolutte celleområder, der bruges i eksemplerne ovenfor.
