Hoe om data-ooreenkomste in Excel te vind

Onder die ontelbare funksies van Microsoft Excel is daar een wat jou toelaat om twee lyste data te vergelyk, die vuurhoutjies tussen daardie lyste te identifiseer en ook te bepaal watter elemente slegs in een van die lyste is. Dit is baie handig om finansiële rekords te vergelyk of te kontroleer of `n spesifieke naam in `n databasis is. U kan die MATCH-funksie gebruik om ooreenstemmende en verkeerde rekords te identifiseer en te merk, of gebruik die COUNTIF funksie om `n voorwaardelike formaat te vestig. Die volgende stappe sal jou wys hoe om hierdie funksies te gebruik om ooreenkomste tussen data te vind.

conținut

Stappe
Metode 1identifiseer rekords deur die coincidir funksie
Metode 2voorwaardelike formatering deur die countif funksie
Wenke

stappe

Metode 1
Identifiseer rekords deur die COINCIDIR funksie

Kopieer die lyste van data in `n enkele sigblad. Excel kan werk met verskeie sigblaaie binne `n enkele boek of met verskeie boeke, maar jy sal sien dat dit makliker is om die lyste te vergelyk as jy al die inligting in een sigblad kopieer.

Voorsien `n unieke identifiseerder vir elke item in die lys. As die twee lyste nie `n gemeenskaplike identifikasiemetode deel nie, sal jy waarskynlik `n bykomende kolom moet byvoeg in elke lys data wat daardie element in Excel identifiseer. Op hierdie manier kan jy bepaal of daardie element van `n gegewe lys verband hou met `n element van die ander lys. Die eienskappe van hierdie identifiseerder sal afhang van die soort data wat u gaan vergelyk. Jy benodig `n identifiseerder vir elke lys van kolomme.

As u finansiële data het wat verband hou met `n gegewe tydperk, byvoorbeeld belastingrekeninge, kan die identifiseerder die beskrywing van die bate wees, die datum waarop die bate verkry is of albei. In sommige gevalle kan die inskrywings met `n numeriese kode geïdentifiseer word. As jy egter nie dieselfde stelsel vir albei lyste gebruik nie, kan hierdie identifiseerder wedstryde skep waar daar geen een is of ignoreer wat werklik bestaan nie.

In sommige gevalle kan jy items uit `n lys neem en dit kombineer met items uit `n ander lys om `n identifiseerder te skep, byvoorbeeld die beskrywing van `n fisiese bate en die jaar waarin dit gekoop is. Om so `n identifiseerder te skep, is dit nodig om die data van twee of meer selle saam te voeg (voeg by, kombineer) deur die simbool te gebruik ampersand (&). Om die beskrywing van `n element in sel F3 te kombineer met `n datum in sel G3, geskei deur `n spasie, moet u die formule `= F3& " "&G3 `. As u slegs die jaar in die identifiseerder wil insluit (omdat een lys volledige datums gebruik en die ander gebruike slegs jare gebruik), moet u die JAAR-funksie insluit deur `= F3&" "&JAAR (G3) `in sel E3 (sluit nie die enkele aanhalings in nie - in hierdie geval word hulle slegs gebruik om `n voorbeeld aan te dui).

Sodra jy die formule geskep het, kan jy dit kopieer na al die ander selle in die identifiseerderkolom. Om dit te doen, kies die sel waar die formule is en sleep die klein invulkassie na die ander selle in die kolom waar jy die formule wil kopieer. As jy die muisknoppie loslaat, sal elke sel wat jy gesleep het, outomaties ingevul word met die formule en sal die selverwysings opgedateer word met die selle wat ooreenstem met die ry.

Prent getiteld Wedstryd data in Excel Stap 3

Standaardeer die data indien moontlik. Terwyl die verstand dit erken "Inc." en "Beperkte Maatskappy" hulle beteken dieselfde, Excel sal nie weet nie, tensy jy een van die twee uitdrukkings hervorm. Net so kan jy dink dat die waardes van $ 11,950 en $ 11,999.95 naby genoeg is om te oorweeg dat daar `n wedstryd is, maar Excel sal dit nie sien as jy dit nie aandui nie.

U kan die afkortings, soos byvoorbeeld, versorg "mede" vir "maatskappy" of "org" vir "organisasie" Gebruik die LEFT-funksie om die karakters wat oorgebly het, af te sny. Maar daar is ander afkortings wat nie afgeknip kan word nie, soos "S.r.l." vir "Beperkte aanspreeklikheidsmaatskappy". In daardie geval moet jy `n data-invoerstyl opstel en `n program gebruik wat die foutiewe formate soek en regstel.

In die aantal kettings kan die LEFT-funksie ook toegepas word. Byvoorbeeld, as jy poskode het met formaat "Poskode + 4" wat `n agtervoegsel na die nommer en eenvoudige poskode bevat, gebruik LINKS om die wedstryd net in die eerste deel van die kode te herken en te vind. Om Excel naby numeriese waardes te herken, maar nie gelyk nie, kan u die ROUND-funksie gebruik. Met hierdie funksie kan jy die noue waardes afrond na dieselfde getal, sodat wanneer hulle vergelyk word, daar `n toeval is tussen hulle.

Die ruimtes wat verby is, byvoorbeeld as daar twee spasies tussen woorde in plaas van net een is, kan deur die CUT-funksie uitgeskakel word.

Prent getiteld Wedstryd data in Excel Stap 4

Skep kolomme vir die vergelyking formule. Net soos jy kolomme moes maak vir die identifiseerders van die lyste, moet jy nou kolomme skep vir die formule wat die vergelyking sal maak. Jy benodig `n kolom vir elke lys.

Merk hierdie kolomme met `n titel wat sê "Ontbrekende rekord?" of iets soortgelyks.

Gee die vergelyking formule in elke sel. Om die vergelyking formule te maak, moet u die COINCID-funksie wat in `n ander Excel-funksie genaamd ESNOD gebruik word, gebruik.

Die formule moet lyk soortgelyk aan "= ESNOD (MATCH (G3- $ L $ 3: $ L $ 14-ONWAAR))", waar `n sel uit die identifiseerderkolom van die eerste lys geneem word en vergelyk word met elk van die identifiseerders van die tweede lys om te sien of daar `n wedstryd met enige is. As daar geen wedstryd is nie, beteken dit dat die registrasie en die woord ontbreek "WAAR" sal in die sel verskyn. As daar `n wedstryd is, beteken dit dat die rekord is en die woord sal verskyn "ONWAAR" (As jy die formule gaan skryf, moenie die aanhalings van die uiterstes insluit nie).

U kan die formule in die oorblywende selle van die kolom op dieselfde manier kopieer as u die formule van die identifiseerers gekopieer het. In hierdie geval verander slegs die selverwysing vir die identifiseerder. Die tekens van gewigte voor die ry en kolomverwysings van die eerste en laaste sel van die lys van identifiseerders in die tweede sel word gebruik om die verwysings om te skakel in "absolute verwysings".

U kan die vergelyking formule van die eerste lys in die eerste sel van die kolom van die tweede lys kopieer. In daardie geval moet u die verwysings van selle vervang "G3" deur die verwysing van die eerste identifiseerder sel van die tweede lys en "$ L $ 3: $ L $ 14" deur die eerste en laaste sel van identifiseerders van die tweede lys (laat die tekens van gewigte en die twee punte soos hulle is). Nou kan jy hierdie geredigeerde formule na die res van die selle in die vergelykingskolom van die tweede lys kopieer.

Sorteer die lyste sodat dit makliker is om die data te sien wat nie ooreenstem nie. As die lyste groot is, is dit beter om dit te bestel deur al die data wat nie ooreenstem nie, saam te voeg. Die instruksies vir die onderstaande subaktiwiteite dien om formules in waardes om te skakel en sodoende foute te voorkom wanneer herberekening. As die lyste lank is, sal dit ook die herberekeningstyd verminder.

Sleep die muis oor al die selle in die lys om hulle te kies.

Kies "kopie" in die spyskaart "uitgawe" (Excel 2003) of in die groep "klipbord" van die lint (Excel 2007 of 2010).

Kies "Spesiale vas" in die spyskaart "uitgawe" (Excel 2003) of in die vervolg keuselys "plak" van die groep "klipbord" (Excel 2007 of 2010) oortjie "inleiding" in die lint.

Kies "waardes" in die lys "Plak as" van die dialoogkassie "Spesiale vas". Klik dan op "aanvaar" Om die boks toe te maak.

Kies "Om" in die spyskaart "data" (Excel 2003) of in die groep "Sorteer en filter" van die oortjie "data" in die lint (Excel 2007 of 2010).

Kies "Opskrif ry" in die lys "My data reeks het" van die dialoogkassie "Sorteer volgens". Kies "Ontbrekende rekord?" (of die naam wat u gekies het as die kop vir die vergelykingskolom) en klik "aanvaar".

Herhaal hierdie stappe met die ander lys.

Prentjie getiteld Wedstryddata in Excel Stap 7

Vergelyk visuele vergelykende items om te sien hoekom hulle nie ooreenstem nie. Soos hierbo verduidelik, is Excel ontwerp om presiese ooreenkomste in die data te soek, tensy u dit stel om na benaderings te soek. die "geen toeval nie" Dit kan wees as gevolg van `n eenvoudige omskakeling fout van letters of syfers. Dit kan ook iets wees wat onafhanklike verifikasie vereis, soos om die data te kontroleer om te sien of die bates op die lys in die eerste plek aangemeld moet word.

Metode 2
Voorwaardelike formatering deur die COUNTIF funksie

Kopieer die lyste van data in `n nuwe sigblad.

Besluit watter lys jy die rekords wil uitlig wat ooreenstem of nie. As u slegs rekords in `n lys wil beklemtoon, is dit die beste om die unieke rekords in die lys te beklemtoon, dit wil sê diegene wat nie ooreenstem met die rekords in die ander lys nie. As u rekords van die twee lyste wil beklemtoon, is dit beter om diegene wat saam met die ander lys val, uit te lig. Vir die doeleindes van hierdie voorbeeld sal aanvaar word dat die eerste lys selle G3 tot en met G14 beslaan en die tweede beslaan selle L3 tot en met L14.

Kies die items in die lys waarin u unieke of ooreenstemmende items wil verlig. As u elemente in die twee lyste wil beklemtoon, moet u die lyste een op `n keer kies en die vergelyking formule (in die volgende stap) in elkeen gebruik.

Prentjie getiteld Wedstryddata in Excel Stap 11

Pas die ooreenstemmende vergelyking formule toe. Om dit te doen, moet jy toegang hê tot die tafel "Voorwaardelike formaat" van u weergawe van Excel. In Excel 2003, maak dit gekies "Voorwaardelike formaat" in die spyskaart "formaat". In Excel 2007 en 2010, klik op die knoppie "Voorwaardelike formaat" van die groep "style" van die oortjie "inleiding" in die lint. Kies "formule" Tik as `n reël die formule in die veld "Wysig `n reëlbeskrywing".

As u die unieke rekords in die eerste lys wil uitlig, sal die formule wees "= COUNTIF ($ L $ 3: $ L $ 14-G3 = 0)" met die reeks selle in die tweede lys uitgedruk in absolute waardes en die verwysing na die eerste sel in die eerste lys as `n relatiewe waarde (sluit nie die aanhalings in die formule in nie).

As u die unieke rekords in die tweede lys wil beklemtoon, sal die formule wees "= COUNTIF ($ G $ 3: $ G $ 14-L3 = 0)" met die reeks selle in die eerste lys uitgedruk in absolute waardes en die verwysing na die eerste sel in die tweede lys as `n relatiewe waarde (sluit nie aanhalings in die formule in nie).

As u die rekords van `n lys wat in die ander lys is, wil beklemtoon, benodig u twee formules, een vir die eerste lys en die ander vir die tweede. Die formule vir die eerste lys is "= COUNTIF ($ L $ 3: $ L $ 14-G3>0)", terwyl die formule vir die tweede lys is "= COUNTIF ($ G $ 3: $ G $ 14-L3>0)". Soos hierbo verduidelik, moet u die eerste lys kies om u formule toe te pas en kies dan die tweede om u formule toe te pas.

Pas die formaat toe waarmee u die rekords wat u wil merk, belig. Klik op "aanvaar" Om die dialoog te sluit.

wenke

In plaas daarvan om `n selverwysing met die COUNTIF funksie van die voorwaardelike formatteringsmetode te gebruik, kan u `n waarde invoer. Hierdie waarde sal in die lyste gesoek word en al die gevalle wat gevind word, sal uitgelig word.
Om die vergelyking formules te vereenvoudig, kan u byvoorbeeld `n naam vir elke lys skep "Lys1" en "Lys2". Dan, wanneer u die formule skryf, kan die name van die lyste gebruik word om die absolute selreekse van die vorige voorbeelde te vervang.

Deel op sosiale netwerke:

Verwante