En av Microsoft Excel mange funksjoner er muligheten til å sammenligne to lister med data, identifisere treff mellom listene og identifisere hvilke elementer som finnes i bare én liste. Dette er nyttig når du sammenligner økonomiske poster eller sjekker om et bestemt navn er i en database. Du kan bruke MATCH-funksjonen til å identifisere og merke matchende eller ikke-matchende poster, eller du kan bruke kondisjoneringsformatering med COUNTIF-funksjonen. Følgende trinn forteller deg hvordan du bruker hver for å matche dataene dine.
Trinn
Metode 1 av 2: Identifisere poster med MATCH -funksjonen
Trinn 1. Kopier datalistene til et enkelt regneark
Excel kan fungere med flere regneark i en enkelt arbeidsbok, eller med flere arbeidsbøker, men du vil lettere sammenligne listene hvis du kopierer informasjonen til et enkelt regneark.
Trinn 2. Gi hvert listeelement en unik identifikator
Hvis de to listene ikke deler en felles måte å identifisere dem på, må du kanskje legge til en ekstra kolonne i hver dataliste som identifiserer elementet i Excel, slik at det kan se om et element i en gitt liste er relatert til et element i den andre listen. Denne identifikatorens art vil avhenge av typen data du prøver å matche. Du trenger en identifikator for hver kolonneliste.
- For økonomiske data knyttet til en gitt periode, for eksempel med skatteregistreringer, kan dette være beskrivelsen av en eiendel, datoen eiendelen ble anskaffet, eller begge deler. I noen tilfeller kan en oppføring identifiseres med et kodenummer; Men hvis det samme systemet ikke brukes for begge listene, kan denne identifikatoren opprette treff der det ikke er noen eller ignorere treff som bør gjøres.
- I noen tilfeller kan du ta elementer fra en liste og kombinere dem med elementer fra en annen liste for å opprette en identifikator, for eksempel en fysisk eiendomsbeskrivelse og året den ble kjøpt. For å opprette en slik identifikator kobler du sammen (legger til, kombinerer) data fra to eller flere celler ved hjelp av ampersand (&). For å kombinere en varebeskrivelse i celle F3 med en dato i celle G3, atskilt med et mellomrom, angir du formelen '= F3 & "" & G3' i en annen celle i den raden, for eksempel E3. Hvis du bare ville inkludere året i identifikatoren (fordi den ene listen bruker hele datoer og den andre bare bruker år), vil du inkludere YEAR -funksjonen ved å skrive inn '= F3 & "" & YEAR (G3)' i celle E3 i stedet. (Ikke ta med de enkelte anførselstegnene; de er der bare for å angi eksemplet.)
- Når du har opprettet formelen, kan du kopiere den til alle andre celler i identifikator -kolonnen ved å velge cellen med formelen og dra fyllehåndtaket over de andre cellene i kolonnen der du vil kopiere formelen. Når du slipper museknappen, vil hver celle du drar over bli fylt ut med formelen, med cellereferansene justert til de riktige cellene i samme rad.
Trinn 3. Standardisere data der det er mulig
Mens sinnet erkjenner at "Inc." og "Inkorporert" betyr det samme, Excel gjør det ikke med mindre du har formatert det ene ordet eller det andre. På samme måte kan du vurdere verdier som $ 11, 950 og $ 11, 999,95 som nær nok til å matche, men Excel vil ikke gjøre det med mindre du sier det til det.
- Du kan håndtere noen forkortelser, for eksempel "Co" for "Company" og "Inc" for "Incorporated ved å bruke VENSTRE strengfunksjon for å avkorte tilleggstegnene. Andre forkortelser, for eksempel" Assn "for" Association ", kan best være behandlet ved å etablere en datainføringsstilguide og deretter skrive et program for å slå opp og korrigere feil formater.
- For tallstrenger, for eksempel postnummer der noen oppføringer inkluderer postnummeret+4 og andre ikke, kan du igjen bruke VENSTRE strengfunksjonen til å gjenkjenne og matche bare de primære postnummerene. For å få Excel til å gjenkjenne numeriske verdier som er nære, men ikke det samme, kan du bruke RUND -funksjonen til å runde lukke verdier til det samme tallet og matche dem.
- Ekstra mellomrom, for eksempel å skrive to mellomrom mellom ord i stedet for ett, kan fjernes ved å bruke TRIM -funksjonen.
Trinn 4. Lag kolonner for sammenligningsformelen
Akkurat som du måtte lage kolonner for listeidentifikatorene, må du lage kolonner for formelen som gjør sammenligningen for deg. Du trenger en kolonne for hver liste.
Du vil merke disse kolonnene med noe som "mangler?"
Trinn 5. Skriv inn sammenligningsformelen i hver celle
For sammenligningsformelen bruker du MATCH -funksjonen som er nestet i en annen Excel -funksjon, ISNA.
- Formelen har formen "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", der en celle i identifikator -kolonnen i den første listen sammenlignes med hver av identifikatorene i den andre listen til se om det stemmer overens med en av dem. Hvis den ikke stemmer overens, mangler en post, og ordet "TRUE" vil bli vist i cellen. Hvis den stemmer overens, er posten tilstede, og ordet "FALSE" vil bli vist. (Når du angir formelen, må du ikke inkludere vedlagte anførselstegn.)
- Du kan kopiere formelen til de resterende cellene i kolonnen på samme måte som du kopierte celleidentifikatorformelen. I dette tilfellet endres bare cellehenvisningen for identifikasjonscellen, ettersom det å sette dollartegnene foran raden og kolonnehenvisninger for de første og siste cellene i listen over de andre celleidentifikatorene gjør dem til absolutte referanser.
- Du kan kopiere sammenligningsformelen for den første listen til den første cellen i kolonnen for den andre listen. Du må deretter redigere cellereferansene slik at "G3" erstattes med referansen for den første identifikasjonscellen i den andre listen og "$ L $ 3: $ L $ 14" erstattes med den første og siste identifikasjonscellen i andre liste. (La dollartegnene og kolon være i fred.) Du kan deretter kopiere denne redigerte formelen til de resterende cellene i sammenligningsraden i den andre listen.
Trinn 6. Sorter listene for å se verdier som ikke samsvarer lettere, om nødvendig
Hvis listene dine er store, må du kanskje sortere dem for å sette alle verdiene som ikke samsvarer. Instruksjonene i deltrinnene nedenfor vil konvertere formlene til verdier for å unngå omberegningsfeil, og hvis listene dine er store, vil du unngå lang omberegningstid.
- Dra musen over alle cellene i en liste for å velge den.
- Velg Kopier fra Rediger -menyen i Excel 2003 eller fra utklippstavlen -gruppen på Hjem -båndet i Excel 2007 eller 2010.
- Velg Lim inn spesial fra Rediger -menyen i Excel 2003 eller fra rullegardinmenyen Lim inn i utklippstavlen i Excel 2007 eller 2010s Home -bånd.
- Velg "Verdier" fra Lim inn som -listen i dialogboksen Lim inn spesial. Klikk OK for å lukke dialogboksen.
- Velg Sorter fra Data -menyen i Excel 2003 eller Sorter og filtrer -gruppen på Databåndet i Excel 2007 eller 2010.
- Velg "Overskriftsrad" fra listen "Mitt dataområde har" i dialogboksen Sorter etter, velg "Mangler?" (eller navnet du faktisk ga sammenligningskolonneoverskriften) og klikk OK.
- Gjenta disse trinnene for den andre listen.
Trinn 7. Sammenlign de ikke-matchende elementene visuelt for å se hvorfor de ikke stemmer overens
Som nevnt tidligere er Excel designet for å lete etter eksakte datatilpasninger, med mindre du konfigurerer det for å se etter omtrentlige. Din ikke-match kan være så enkel som en utilsiktet transponering av bokstaver eller sifre. Det kan også være noe som krever uavhengig bekreftelse, for eksempel å sjekke om børsnoterte eiendeler må rapporteres i utgangspunktet.
Metode 2 av 2: Betinget formatering med COUNTIF
Trinn 1. Kopier datalistene til et enkelt regneark
Trinn 2. Bestem hvilken liste du vil markere matchende eller ikke-matchende poster
Hvis du vil markere poster i bare én liste, vil du sannsynligvis markere postene som er unike for listen; det vil si poster som ikke samsvarer med poster i den andre listen. Hvis du vil markere poster i begge listene, vil du markere poster som stemmer overens med hverandre. I dette eksemplet antar vi at den første listen tar opp cellene G3 til G14 og den andre listen tar opp cellene L3 til og med L14.
Trinn 3. Velg elementene i listen du vil markere unike eller matchende elementer i
Hvis du ønsker å markere matchende elementer i begge listene, må du velge listene en om gangen og bruke sammenligningsformelen (beskrevet i neste trinn) på hver liste.
Trinn 4. Bruk passende sammenligningsformel
For å gjøre dette må du få tilgang til dialogboksen Betinget formatering i din versjon av Excel. I Excel 2003 gjør du det ved å velge Betinget formatering fra Format -menyen, mens du i Excel 2007 og 2010 klikker på knappen Betinget formatering i gruppen Stiler på båndet Hjem. Velg regeltypen som "Formel" og skriv inn formelen din i feltet Rediger regelbeskrivelse.
- Hvis du vil markere poster som er unike for den første listen, vil formelen være "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", med celleområdet i den andre listen gjengitt som absolutte verdier og referansen til den første cellen i den første listen som en relativ verdi. (Ikke skriv inn anførselstegnene.)
- Hvis du vil markere poster som er unike for den andre listen, vil formelen være "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", med celleområdet i den første listen gjengitt som absolutte verdier og referansen til den første cellen i den andre listen som en relativ verdi. (Ikke skriv inn anførselstegnene.)
- Hvis du vil markere postene i hver liste som finnes i den andre listen, trenger du to formler, en for den første listen og en for den andre. Formelen for den første listen er "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", mens formelen for den andre listen er COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Som nevnt tidligere, velger du den første listen for å bruke formelen og deretter den andre listen for å bruke formelen.
- Bruk hvilken formatering du vil for å markere postene som flagges. Klikk OK for å lukke dialogboksen.
Video - Ved å bruke denne tjenesten kan noe informasjon bli delt med YouTube
Tips
- I stedet for å bruke en cellereferanse med COUNTIF betinget formateringsmetode, kan du angi en verdi som skal søkes etter og flagge en eller flere lister for forekomster av den verdien.
- For å forenkle sammenligningsskjemaene kan du opprette navn på listen din, for eksempel "List1" og "List2". Når du skriver formlene, kan disse listenavnene erstatte de absolutte celleområdene som brukes i eksemplene ovenfor.
