Een van die vele moontlikhede van Microsoft Excel is die vermoë om twee lyste data te vergelyk, ooreenkomste tussen die lyste te identifiseer en watter items slegs in een lys voorkom. Dit is handig as u finansiële rekords vergelyk of kyk of 'n spesifieke naam in 'n databasis is. U kan die MATCH-funksie gebruik om ooreenstemmende of nie-ooreenstemmende rekords te identifiseer en te merk, of u kan kondisioneringsformatering gebruik met die COUNTIF-funksie. Die volgende stappe vertel u hoe u elkeen moet gebruik om by u data te pas.
Stappe
Metode 1 van 2: Identifiseer rekords met die MATCH -funksie
Stap 1. Kopieer die datalyste op 'n enkele werkblad
Excel kan met veelvuldige werkblaaie in 'n enkele werkboek of met veelvuldige werkboeke werk, maar dit is makliker om die lyste te vergelyk as u die inligting op 'n enkele werkblad kopieer.
Stap 2. Gee vir elke lysitem 'n unieke identifiseerder
As u twee lyste nie 'n algemene manier om dit te identifiseer nie, moet u moontlik 'n ekstra kolom by elke datalys voeg wat die item in Excel identifiseer, sodat dit kan sien of 'n item in 'n gegewe lys met 'n item verband hou in die ander lys. Die aard van hierdie identifiseerder sal afhang van die soort data waarmee u probeer ooreenstem. U benodig 'n identifiseerder vir elke kolomlys.
- Vir finansiële data wat verband hou met 'n gegewe tydperk, soos met belastingrekords, kan dit die beskrywing van 'n bate wees, die datum waarop die bate verkry is, of albei. In sommige gevalle kan 'n inskrywing geïdentifiseer word met 'n kodenommer; As dieselfde stelsel egter nie vir albei lyste gebruik word nie, kan hierdie identifiseerder ooreenstemmings skep waar daar geen is nie, of ooreenkomste wat gemaak moet word, ignoreer.
- In sommige gevalle kan u items uit een lys neem en dit kombineer met items uit 'n ander lys om 'n identifiseerder te skep, soos 'n fisiese bate -beskrywing en die jaar waarin dit gekoop is. Om so 'n identifiseerder te skep, kombineer (voeg, kombineer) u data uit twee of meer selle met behulp van die ampersand (&). Om 'n itembeskrywing in sel F3 te kombineer met 'n datum in sel G3, geskei deur 'n spasie, voer u die formule '= F3 & "" & G3' in 'n ander sel in daardie ry in, soos E3. As u slegs die jaar in die identifiseerder wou opneem (omdat die een lys volledige datums gebruik en die ander slegs jare), sou u die JAAR -funksie insluit deur '= F3 & "" & YEAR (G3)' in sel E3 in te voer. (Moenie die enkele aanhalings insluit nie; dit is slegs daar om die voorbeeld aan te dui.)
- Sodra u die formule geskep het, kan u dit in alle ander selle van die identifiseerderkolom kopieer deur die sel met die formule te selekteer en die vulhandvatsel te sleep oor die ander selle van die kolom waar u die formule wil kopieer. As u die muisknop loslaat, word elke sel waaroor u gesleep het, gevul met die formule, met die selverwysings aangepas by die toepaslike selle in dieselfde ry.
Stap 3. Standaardiseer data waar moontlik
Terwyl die verstand erken dat "Inc." en 'Ingelyf' beteken dieselfde, Excel nie, tensy u die een of ander woord herformateer. Net so kan u waardes soos $ 11, 950 en $ 11, 999,95 as na aan mekaar beskou, maar Excel sal dit nie doen nie, tensy u dit vertel.
- U kan sommige afkortings hanteer, soos 'Co' vir 'Company' en 'Inc' vir 'Incorporated' deur die LINKER -stringfunksie te gebruik om die bykomende karakters af te sny. Ander afkortings, soos 'Assn' vir 'Association', kan die beste wees hanteer deur 'n gids vir die invoer van data in te stel en dan 'n program te skryf om verkeerde formate op te soek en reg te stel.
- Vir getalle, soos poskodes waar sommige inskrywings die zip+4 -agtervoegsel insluit en ander nie, kan u weer die LINKER -stringfunksie gebruik om slegs die primêre poskodes te herken en te pas. Om Excel numeriese waardes te laat herken wat naby is, maar nie dieselfde is nie, kan u die ROUND -funksie gebruik om die waardes na dieselfde nommer af te rond en dit te pas.
- Ekstra spasies, soos om twee spasies tussen woorde in plaas van een te tik, kan verwyder word deur die TRIM -funksie te gebruik.
Stap 4. Skep kolomme vir die vergelykingsformule
Net soos u kolomme vir die lys -identifiseerders moes skep, moet u kolomme skep vir die formule wat die vergelyking vir u doen. U benodig een kolom vir elke lys.
U sal hierdie kolomme wil benoem met iets soos "Ontbreek?"
Stap 5. Voer die vergelykingsformule in elke sel in
Vir die vergelykingsformule gebruik u die MATCH -funksie in 'n ander Excel -funksie, ISNA.
- Die formule het die vorm van "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, ONWAAR))", waar 'n sel van die identifiseerderkolom van die eerste lys vergelyk word met elk van die identifiseerders in die tweede lys met kyk of dit by een van hulle pas. As dit nie ooreenstem nie, ontbreek 'n rekord en word die woord 'WAAR' in die sel vertoon. As dit wel ooreenstem, is die rekord teenwoordig en word die woord "ONWAAR" vertoon. (As u die formule invoer, moet u nie die aangehegte aanhalings insluit nie.)
- U kan die formule in die oorblywende selle van die kolom kopieer op dieselfde manier as wat u die selidentifiseringsformule gekopieer het. In hierdie geval verander slegs die selverwysing vir die identiteitsel, aangesien die dollartekens voor die ry en kolomverwysings vir die eerste en laaste selle in die lys van die tweede selidentifiseerders absolute verwysings maak.
- U kan die vergelykingsformule vir die eerste lys in die eerste sel van die kolom vir die tweede lys kopieer. U moet dan die selverwysings wysig sodat "G3" vervang word met die verwysing vir die eerste identifiseringsel van die tweede lys en "$ L $ 3: $ L $ 14" word vervang met die eerste en laaste identifiseringsel van die tweede lys. (Laat die dollartekens en dubbelpunt alleen.) U kan hierdie bewerkte formule dan in die oorblywende selle in die vergelykingsry van die tweede lys kopieer.
Stap 6. Sorteer die lyste om nie-ooreenstemmende waardes makliker te sien, indien nodig
As u lyste groot is, moet u dit moontlik sorteer om al die nie-ooreenstemmende waardes bymekaar te maak. Die instruksies in die onderstappe hieronder sal die formules omskakel in waardes om herberekeningsfoute te voorkom, en as u lyste groot is, sal dit 'n lang herberekeningstyd vermy.
- Sleep u muis oor al die selle in 'n lys om dit te selekteer.
- Kies Kopieer in die menu Wysig in Excel 2003 of uit die knipbordgroep van die tuislint in Excel 2007 of 2010.
- Kies Plak spesiaal in die kieslys Wysig in Excel 2003 of vanaf die aftrekknoppie Plak in die knipbordgroep van Excel 2007 of 2010 se tuislint.
- Kies "Waardes" in die lys Plak as in die dialoogkassie Plak spesiaal. Klik OK om die dialoog te sluit.
- Kies Sorteer in die Data -kieslys in Excel 2003 of die Sorteer en filter -groep van die Data -lint in Excel 2007 of 2010.
- Kies 'Kopry' in die lys 'My data reeks het' in die dialoog Sorteer volgens, kies 'Ontbreek?' (of die naam wat u die kop van die vergelykingskolom gegee het) en klik op OK.
- Herhaal hierdie stappe vir die ander lys.
Stap 7. Vergelyk die items wat nie ooreenstem nie visueel om te sien waarom dit nie ooreenstem nie
Soos voorheen opgemerk, is Excel ontwerp om presiese data -ooreenkomste te soek, tensy u dit opgestel het om na benaderings te kyk. U nie-ooreenstem kan so eenvoudig wees as 'n toevallige transponering van letters of syfers. Dit kan ook iets wees wat onafhanklike verifikasie vereis, soos om te kyk of bates in die eerste plek gerapporteer moet word.
Metode 2 van 2: Voorwaardelike formatering met COUNTIF
Stap 1. Kopieer die datalyste op 'n enkele werkblad
Stap 2. Besluit in watter lys u bypassende of nie-ooreenstemmende rekords wil uitlig
As u rekords in slegs een lys wil uitlig, wil u waarskynlik die rekords wat uniek is aan die lys, uitlig; dit wil sê rekords wat nie ooreenstem met rekords in die ander lys nie. As u rekords in albei lyste wil uitlig, wil u rekords wat by mekaar pas, uitlig. Vir die doeleindes van hierdie voorbeeld aanvaar ons dat die eerste lys selle G3 tot G14 opneem en die tweede lys selle L3 tot en met L14.
Stap 3. Kies die items in die lys waarin u unieke of bypassende items wil uitlig
As u ooreenstemmende items in albei lyste wil uitlig, moet u die lyste een vir een kies en die vergelykingsformule (beskryf in die volgende stap) op elke lys toepas.
Stap 4. Pas die toepaslike vergelykingsformule toe
Om dit te kan doen, moet u toegang kry tot die dialoog Voorwaardelike opmaak in u weergawe van Excel. In Excel 2003 doen u dit deur voorwaardelike opmaak in die menu Opmaak te kies, terwyl u in Excel 2007 en 2010 op die knoppie Voorwaardelike opmaak in die groep Style van die tuislint klik. Kies die reëltipe as "Formule" en voer u formule in die veld Bewerk die reëlbeskrywing in.
- As u rekords wat uniek is vir die eerste lys, wil uitlig, is die formule "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", met die reeks selle van die tweede lys as absolute waardes en die verwysing na die eerste sel van die eerste lys as 'n relatiewe waarde. (Moenie die aanhalingstekens invoer nie.)
- As u rekords wat uniek is vir die tweede lys, wil uitlig, is die formule "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", met die reeks selle van die eerste lys as absolute waardes en die verwysing na die eerste sel van die tweede lys as 'n relatiewe waarde. (Moenie die aanhalingstekens invoer nie.)
- As u die rekords in elke lys wat in die ander lys voorkom, wil uitlig, benodig u twee formules, een vir die eerste lys en een vir die tweede. Die formule vir die eerste lys is "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", terwyl die formule vir die tweede lys COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". voorheen opgemerk, kies u die eerste lys om die formule toe te pas en kies dan die tweede lys om die formule toe te pas.
- Pas die opmaak toe wat u wil, om die rekords wat gemerk word, uit te lig. Klik OK om die dialoog te sluit.
Video - Deur hierdie diens te gebruik, kan sommige inligting met YouTube gedeel word
Wenke
- In plaas daarvan om 'n selverwysing te gebruik met die COUNTIF voorwaardelike opmaakmetode, kan u 'n waarde invoer waarna u moet soek en een of meer lyste vir gevalle van daardie waarde kan merk.
- Om die vergelykingsvorms te vereenvoudig, kan u name vir u lys skep, soos 'List1' en 'List2'. By die skryf van die formules kan hierdie lysname die absolute selreekse vervang wat in die voorbeelde hierbo gebruik word.
