Üks Microsoft Exceli paljudest võimalustest on võimalus võrrelda kahte andmeloendit, tuvastades loendite vasted ja tuvastades, millised üksused on leitud ainult ühest loendist. See on kasulik finantsdokumentide võrdlemisel või kontrollimisel, kas konkreetne nimi on andmebaasis. Funktsiooni MATCH abil saate tuvastada ja märkida sobivaid või mittevastavaid kirjeid või kasutada konditsioneerimise vormindamist funktsiooni COUNTIF abil. Järgmised sammud ütlevad teile, kuidas neid igaüks oma andmetega sobitada.
Sammud
Meetod 1/2: kirjete tuvastamine funktsiooni MATCH abil
Samm 1. Kopeerige andmete loendid ühele töölehele
Excel saab töötada ühe töölehe mitme töölehega või mitme töövihikuga, kuid loendite võrdlemine on lihtsam, kui kopeerite nende andmed ühele töölehele.
Samm 2. Andke igale loendiüksusele kordumatu identifikaator
Kui teie kahel loendil pole ühist viisi nende tuvastamiseks, peate võib -olla lisama igale andmeloendile täiendava veeru, mis tuvastab selle üksuse Excelisse, et näha, kas antud loendi üksus on üksusega seotud teises nimekirjas. Selle identifikaatori olemus sõltub andmete tüübist, mida proovite sobitada. Iga veeru loendi jaoks on vaja identifikaatorit.
- Finantsandmete puhul, mis on seotud antud perioodiga, näiteks maksukirjetega, võib see olla vara kirjeldus, vara soetamise kuupäev või mõlemad. Mõnel juhul võidakse kirje identifitseerida koodnumbriga; kui aga mõlema loendi puhul sama süsteemi ei kasutata, võib see identifikaator luua vasteid, kus neid pole, või ignoreerida vasted, mis tuleks teha.
- Mõnel juhul võite võtta ühest loendist esemeid ja kombineerida need teise loendi üksustega, et luua identifikaator, näiteks füüsilise vara kirjeldus ja ostmise aasta. Sellise identifikaatori loomiseks ühendate (lisate, ühendate) andmed kahest või enamast lahtrist, kasutades märki & (). Kui soovite ühendada lahtris F3 oleva elemendi kirjelduse kuupäevaga lahtris G3, eraldades selle tühikuga, sisestage valem '= F3 & "" & G3' selle rea teise lahtrisse, näiteks E3. Kui soovite identifikaatorisse lisada ainult aasta (kuna üks loend kasutab täiskuupäevi ja teine ainult aastaid), lisage funktsioon AASTA, sisestades lahtrisse E3 selle asemel '= F3 & "" & YEAR (G3)'. (Ärge lisage üksikuid jutumärke; need on ainult näite näitamiseks.)
- Kui olete valemi loonud, saate selle kopeerida identifikaatori veeru kõikidesse teistesse lahtritesse, valides valemiga lahtri ja lohistades täitekäepideme üle selle veeru teiste lahtrite, kuhu soovite valemi kopeerida. Kui vabastate hiirenupu, täidetakse iga üle lohistatud lahter valemiga, kusjuures lahtriviited kohandatakse sama rea vastavate lahtritega.
Samm 3. Võimaluse korral standardige andmed
Kuigi mõistus tunneb ära, et "Inc." ja "Incorporated" tähendab sama asja, Excel seda ei tee, kui te ei lase üht või teist sõna uuesti vormindada. Samuti võite kaaluda selliseid väärtusi nagu 11, 950 ja 11, 999,95 dollarit, et need sobiksid piisavalt lähedale, kuid Excel ei tee seda, kui te seda ei ütle.
- Te saate tegeleda mõne lühendiga, näiteks „Co” tähise „Company” ja „Inc” puhul „Incorporated”, kasutades täiendavate märkide kärpimiseks LEFT stringi funktsiooni. Teised lühendid, näiteks „Assn” sõna „Association” jaoks, võivad olla paremad lahendada, koostades andmesisestusstiili juhendi ja kirjutades seejärel programmi sobimatute vormingute otsimiseks ja parandamiseks.
- Numbrite (nt sihtnumbrite) puhul, kus mõned kirjed sisaldavad ZIP+4 järelliidet ja teised mitte, saate uuesti kasutada funktsiooni LEFT, et tuvastada ja sobitada ainult esmased sihtnumbrid. Kui soovite, et Excel tuvastaks numbrilised väärtused, mis on lähedased, kuid mitte samad, saate funktsiooni ROUND abil ümardada sulgemisväärtused samale arvule ja sobitada need.
- Funktsiooni TRIM abil saab eemaldada täiendavaid tühikuid, näiteks sõnade vahele kahe tühiku sisestamise ühe asemel.
Samm 4. Looge võrdlusvalemi jaoks veerud
Nii nagu pidite loendite identifikaatorite jaoks veerge looma, peate looma veerud selle valemi jaoks, mis võrdleb teie eest. Iga loendi jaoks on vaja ühte veergu.
Soovite neile veergudele sildistada näiteks „Kadunud?”
Samm 5. Sisestage igasse lahtrisse võrdlusvalem
Võrdlusvalemi jaoks kasutate funktsiooni MATCH, mis on sisestatud teise Exceli funktsiooni ISNA sisse.
- Valem on kujul "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", kus esimese loendi identifikaatori veeru lahtrit võrreldakse iga teise loendi identifikaatoriga vaata, kas see sobib ühega neist. Kui see ei ühti, on kirje puudu ja selles lahtris kuvatakse sõna "TRUE". Kui see sobib, on kirje olemas ja kuvatakse sõna "FALSE". (Valemi sisestamisel ärge lisage hinnapakkumisi.)
- Saate kopeerida valemi veeru ülejäänud lahtritesse samamoodi, nagu kopeerisite lahtri identifikaatori valemi. Sel juhul muutub ainult identifikaatori lahtri viide, kuna dollarimärkide asetamine rea ja veeru viidete ette teise lahtri identifikaatorite loendi esimese ja viimase lahtri jaoks muudab need absoluutseteks viideteks.
- Saate kopeerida esimese loendi võrdlusvalemi teise loendi veeru esimesse lahtrisse. Seejärel peate redigeerima lahtri viiteid nii, et "G3" asendatakse teise loendi esimese identifikaatori lahtri viitega ja "$ L $ 3: $ L $ 14" asendatakse esimese ja viimase identifikaatori lahtriga. teine nimekiri. (Jätke dollarimärgid ja koolon rahule.) Seejärel saate selle muudetud valemi kopeerida teise loendi võrdlusrea ülejäänud lahtritesse.
Samm 6. Sorteerige loendid, et vajadusel hõlpsamini näha mittevastavaid väärtusi
Kui teie loendid on suured, peate võib-olla need sortima, et kõik mittevastavad väärtused kokku panna. Alltoodud alametappide juhised teisendavad valemid väärtusteks, et vältida ümberarvutusvigu, ja kui teie loendid on suured, väldite pikka ümberarvestusaega.
- Selle valimiseks lohistage hiirega üle kõigi loendi lahtrite.
- Valige Excel 2003 menüüst Redigeerimine või Excel 2007 või 2010 avalehe lõikelaua rühmast Kopeeri.
- Valige Excel 2003 menüüst Redigeerimine või Exceli 2007 või 2010 avalehe lõikelaua rühmas rippmenüüst Kleebi.
- Valige dialoogiboksi Eriline kleepimine loendist Kleebi nimega "Väärtused". Dialoogi sulgemiseks klõpsake nuppu OK.
- Valige Excel 2003 menüüst Andmed menüüst Sortimine või Excel 2007 või 2010 andmeriba rühm Sortimine ja filtreerimine.
- Valige dialoogi Sortimisvalik loendist "Minu andmevahemik" loendist "Päise rida" ja valige "Puudub?" (või nime, mille võrdlusveeru päise tegelikult andsite) ja klõpsake nuppu OK.
- Korrake neid samme teise loendi jaoks.
Samm 7. Võrrelge mittevastavaid üksusi visuaalselt, et näha, miks need ei ühti
Nagu varem märgitud, on Excel loodud otsima täpseid andmete vasteid, kui te pole seda seadistanud ligikaudsete andmete otsimiseks. Teie mittevastavus võib olla sama lihtne kui tähtede või numbrite juhuslik ülevõtmine. See võib olla ka midagi, mis nõuab sõltumatut kontrollimist, näiteks kontroll, kas noteeritud varad tuleb esmalt teatada.
Meetod 2/2: tingimuslik vormindamine COUNTIF -iga
Samm 1. Kopeerige andmete loendid ühele töölehele
Samm 2. Otsustage, millises loendis soovite esile tuua sobivaid või mittevastavaid kirjeid
Kui soovite kirjeid esile tõsta ainult ühes loendis, soovite tõenäoliselt esile tõsta selle loendi ainulaadseid kirjeid; see tähendab kirjed, mis ei vasta teise loendi kirjetele. Kui soovite mõlemas loendis kirjeid esile tõsta, siis tõstke esile kirjed, mis omavahel sobivad. Selle näite jaoks eeldame, et esimene loend võtab lahtrid G3 kuni G14 ja teine loend L3 kuni L14.
Samm 3. Valige loendist üksused, milles soovite esile tuua ainulaadseid või sobivaid üksusi
Kui soovite mõlemas loendis sobivaid üksusi esile tõsta, peate loendid ükshaaval valima ja igale loendile rakendama võrdlusvalemit (kirjeldatud järgmises etapis).
Samm 4. Rakendage sobivat võrdlusvalemit
Selleks peate oma Exceli versioonis avama dialoogi Tingimuslik vormindamine. Rakenduses Excel 2003 saate seda teha, valides menüüst Vorming tingimuslik vormindamine, Excelis 2007 ja 2010 klõpsates lindi Avaleht rühmas Stiilne vormindamine. Valige reegli tüüp "Valem" ja sisestage valem väljale Reegli kirjelduse muutmine.
- Kui soovite esile tõsta esimese loendi ainulaadseid kirjeid, oleks valem "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", kusjuures teise loendi lahtrite vahemik esitatakse absoluutväärtustena ja viitena esimese loendi esimesse lahtrisse suhtelise väärtusena. (Ärge sisestage lähedasi jutumärke.)
- Kui soovite esile tõsta teise loendi ainulaadseid kirjeid, oleks valem "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", kusjuures esimese loendi lahtrivahemik esitatakse absoluutväärtustena ja viide teise loendi esimesse lahtrisse suhtelise väärtusena. (Ärge sisestage lähedasi jutumärke.)
- Kui soovite esile tuua igas loendis olevad kirjed, mis on teises loendis, vajate kahte valemit, üks esimese loendi jaoks ja teine teise valemi jaoks. Esimese loendi valem on "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", samas kui teise loendi valem on COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". varem märgitud, valite selle valemi rakendamiseks esimese loendi ja seejärel valemi rakendamiseks teise loendi.
- Märgistatud kirjete esiletõstmiseks kasutage mis tahes vormingut. Dialoogi sulgemiseks klõpsake nuppu OK.
Video - selle teenuse kasutamisel võidakse YouTube'iga jagada teatud teavet
Näpunäiteid
- Selle asemel, et kasutada lahtriviidet tingimusliku vormindamismeetodiga COUNTIF, võite sisestada otsitava väärtuse ja märkida selle väärtuse eksemplaride jaoks ühe või mitu loendit.
- Võrdlusvormide lihtsustamiseks saate oma loendile luua nimesid, näiteks "Loend1" ja "Loend2". Seejärel võivad need valemite kirjutamisel need loendinimed asendada ülaltoodud näidetes kasutatud absoluutsed lahtrivahemikud.
