En af Microsoft Excel's mange muligheder er evnen til at sammenligne to lister over data, identificere matches mellem listerne og identificere, hvilke elementer der findes i kun en liste. Dette er nyttigt, når du sammenligner finansielle poster eller kontrollerer for at se, om et bestemt navn er i en database. Du kan bruge MATCH-funktionen til at identificere og markere matchende eller ikke-matchende poster, eller du kan bruge conditionformatering med COUNTIF-funktionen. De følgende trin fortæller dig, hvordan du bruger hver for at matche dine data.

Metode En af To:
Identifikation af poster med MATCH-funktionen

  1. 1 Kopier datalisterne på et enkelt regneark. Excel kan arbejde med flere regneark i en enkelt projektmappe eller med flere arbejdsbøger, men du kan sammenligne listerne lettere, hvis du kopierer deres oplysninger til et enkelt regneark.
  2. 2 Giv hvert liste element en unik identifikator. Hvis dine to lister ikke deler en fælles måde at identificere dem, kan du muligvis tilføje en ekstra kolonne til hver dataliste, der identificerer det pågældende emne i Excel, så det kan se, om et emne i en given liste er relateret til en vare i den anden liste. Typen af ​​denne identifikator afhænger af den type data, du forsøger at matche. Du skal bruge en identifikator for hver kolonneliste.
    • For økonomiske data, der er knyttet til en given periode, f.eks. Med skattejournaler, kan dette være beskrivelsen af ​​et aktiv, datoen for aktivet blev erhvervet, 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 skabe kampe, hvor der ikke er nogen eller ignorerer kampe, der skal laves.
    • I nogle tilfælde kan du tage varer fra en liste og kombinere dem med elementer fra en anden liste for at oprette en identifikator, f.eks. En fysisk aktivbeskrivelse og det år, det blev købt. For at oprette en sådan identifikator sammenkæder du (tilføj, kombinerer) data fra to eller flere celler ved hjælp af ampersand (&). For at 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 en liste bruger fulde datoer og de andre anvendelser kun år), vil du inkludere ÅR-funktionen ved at indtaste '= F3 & "" & ÅR (G3)' i celle E3 i stedet. (Indsæt ikke de enkelte citater, de er kun til at angive eksemplet.)
    • Når du har oprettet formlen, kan du kopiere den til alle andre celler i identifikatorkolonnen ved at vælge cellen med formlen og trække fyldhåndtaget over de andre celler i kolonnen, hvor du vil kopiere formlen. Når du slipper din museknap, vil hver celle, du har trukket over, blive befolket med formlen, med cellehenvisningerne justeret til de relevante celler i samme række.
  3. 3 Standardiser data, hvor det er muligt. Mens sindet erkender, at "Inc." og "Incorporated" betyder det samme, Excel, medmindre du har det, formaterer du ikke et ord eller det andet. Ligeledes kan du overveje værdier som $ 11.950 og $ 11.999.95 så tæt nok at matche, men Excel vil ikke medmindre du fortæller det til.
    • Du kan håndtere forkortelser som "Co" for "Company" og "Inc" for "Incorporated ved at bruge LEFT string-funktionen til at afkorte de ekstra tegn. Andre forkortelser, f.eks." Assn "for" Association ", er bedst at være behandles ved at oprette en vejledning til dataindtastning og derefter skrive et program til at slå op og rette ukorrekte formater.
    • For strenge af tal, f.eks. Postnumre, hvor nogle poster indeholder ZIP + 4-suffixet, og andre ikke, kan du igen bruge LEFT-streng-funktionen til at genkende og matche kun 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 ROUND-funktionen til at runde tætte værdier til det samme nummer og matche dem.
    • Ekstra mellemrum, såsom at skrive to mellemrum i stedet for en, kan fjernes ved hjælp af TRIM-funktionen.
  4. 4 Opret kolonner til sammenligningsformlen. Ligesom du måtte lave kolonner til listidentifikatorerne, skal du oprette kolonner for den formel, der sammenligner for dig. Du skal bruge en kolonne for hver liste.
    • Du vil gerne mærke disse kolonner med noget som "Mangler?"
  5. 5 Indtast sammenligningsformlen i hver celle. Til sammenligningsformlen bruger du MATCH-funktionen indlejret i en anden Excel-funktion, ISNA.
    • Formlen har form af "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", hvor en celle i identifikationssøjlen i den første liste sammenlignes med hver af identifikatorerne i den anden liste til se om det passer til en af ​​dem. Hvis det ikke stemmer overens, mangler en post, og ordet "TRUE" vises i den pågældende celle. Hvis det passer, er posten til stede, og ordet "FALSE" vises. (Når du indtaster formlen, skal du ikke medtage de vedlagte citater.)
    • Du kan kopiere formlen til de resterende celler i kolonnen på samme måde som du kopierede celleidentifikationsformlen. I dette tilfælde ændres kun cellereferencen for identifikationscellen, idet det at sætte dollartegnene foran rækken og kolonnehenvisninger for de første og sidste celler i listen over de andre celleidentifikatorer gør dem absolutte referencer.
    • Du kan kopiere sammenligningsformlen til den første liste i den første celle i kolonnen til den anden liste. Du skal derefter redigere cellehenvisningerne, så "G3" erstattes med referencen til den første identifikationscelle i den anden liste, og "$ L $ 3: $ L $ 14" erstattes med den første og sidste identifikationscelle i den anden liste.(Lad dollarsedlerne og kolon alene være.) Du kan derefter kopiere denne redigerede formel til de resterende celler i sammenligningsraden på den anden liste.
  6. 6 Sorter listerne for 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 nedenstående understreg konverterer formlerne til værdier for at undgå omberegningsfejl, og hvis dine lister er store, vil du undgå en lang omregningstid.
    • Træk musen over alle cellerne i en liste for at vælge den.
    • Vælg Kopier fra menuen Rediger i Excel 2003 eller fra Udklipsholdergruppen i Hjemmebåndet i Excel 2007 eller 2010.
    • Vælg Indsæt special fra Rediger-menuen i Excel 2003 eller fra rullemenuen Indsæt i udklipsholderen i Excel 2007 eller 2010s hjemmebånd.
    • Vælg "Værdier" fra listen Indsæt som i dialogboksen Indsæt special. Klik på OK for at lukke dialogboksen.
    • Vælg Sorter fra Data-menuen i Excel 2003 eller Sorter og Filter-gruppen i Data-båndet i Excel 2007 eller 2010.
    • Vælg "Header row" fra listen "Mine dataområder har" i dialogboksen Sorter efter, vælg "Mangler?" (eller navnet du faktisk gav sammenligningskolonneoverskriften) og klik på OK.
    • Gentag disse trin til den anden liste.
  7. 7 Sammenlign de ikke-matchende elementer visuelt for at se, hvorfor de ikke stemmer overens. Som tidligere nævnt er Excel designet til at søge efter nøjagtige datakampe, medmindre du sætter den op for at se efter omtrentlige. Din ikke-match kan være 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 de noterede aktiver skal rapporteres i første omgang.

Metode To af to:
Betinget formatering med COUNTIF

  1. 1 Kopier datalisterne på et enkelt regneark.
  2. 2 Bestem i hvilken liste du vil fremhæve matchende eller ikke-matchende poster. Hvis du vil markere poster i kun en liste, vil du sandsynligvis gerne markere de poster, der er unikke for den pågældende liste; det vil sige optegnelser, der ikke matcher optegnelser i den anden liste. Hvis du vil markere poster i begge lister, vil du gerne markere poster, der passer til hinanden. I dette eksempel antages det, at den første liste optager cellerne G3 til og med G14, og den anden liste optager cellerne L3 til og med L14.
  3. 3 Vælg de elementer i listen, du vil markere unikke eller matchende elementer i. Hvis du vil fremhæve matchende elementer i begge lister, skal du vælge listerne en ad gangen og anvende sammenligningsformlen (beskrevet i næste trin) til hver liste.
  4. 4 Anvend den relevante 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 på menuen Format, mens du i Excel 2007 og 2010 klikker på knappen Betinget formatering i stilen-gruppen af ​​hjemmebåndet. Vælg regeltypen som "Formel" og indtast din formel i feltet Rediger regelbeskrivelsen.
    • Hvis du vil markere poster, der er unikke for den første liste, vil formlen være "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", idet rækkevidden af ​​celler i den anden liste gengives som absolutte værdier og referencen til den første celle i den første liste som en relativ værdi. (Indtast ikke de lukkede citater.)
    • Hvis du vil markere poster, der er unikke for den anden liste, vil formlen være "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", idet rækkevidden af ​​celler i den første liste gengives som absolutte værdier og referencen til den første celle i den anden liste som en relativ værdi. (Indtast ikke de lukkede citater.)
    • Hvis du vil markere optegnelserne i hver liste, der findes i 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) ". bemærket tidligere, vælger du den første liste for at anvende dens formel og derefter vælge den anden liste for at anvende dens formel.
    • Anvend uanset formatering, du vil markere de poster, der markeres. Klik på OK for at lukke dialogboksen.