Üks Microsoft Exceli paljudest võimalustest on võimalus võrrelda kahte andmeloendit, tuvastades loendite vasteid ja tuvastades, millised üksused on leitud ainult ühest loendist. See on kasulik finantsdokumentide võrdlemisel või kontrollimisel, kas konkreetne nimi on andmebaasis. Vastavate või mittevastavate kirjete tuvastamiseks ja märgistamiseks saate kasutada funktsiooni MATCH või funktsiooni COUNTIF abil vormindamist. Järgmised sammud näitavad, kuidas neid andmeid oma andmete sobitamiseks kasutada.
1
Kopeerige andmeloendid ühele tööle. Excel võib töötada ühes töövihikus mitme töölehega või mitme töövihikuga, kuid loendite võrdlemine on lihtsam, kui kopeerite nende teabe ühele töölehele.
2
Andke igale loendiüksusele kordumatu identifikaator. Kui teie kahel loendil pole nende tuvastamiseks ühist viisi, peate võib-olla lisama igale andmeloendile täiendava veeru, mis identifitseerib selle üksuse Excelis, et see näeks, kas antud loendis olev üksus on seotud üksusega. teises loendis. Selle identifikaatori olemus sõltub sellest, millist tüüpi andmeid proovite vastendada. Teil on vaja iga veergude loendi jaoks identifikaatorit. Teatud perioodiga seotud finantsandmete (nt maksudokumentidega) puhul võib see olla vara kirjeldus, vara soetamise kuupäev või mõlemad. Mõnel juhul võib kirje identifitseerida koodinumbriga; kui aga sama süsteemi ei kasutata mõlema loendi jaoks, võib see identifikaator luua vasteid, kus neid pole, või ignoreerida vasteid, mida tuleks teha. Mõnel juhul võite võtta ühest loendist üksused ja kombineerida need teise loendi üksustega. looge identifikaator, näiteks füüsilise vara kirjeldus ja selle ostuaasta. Sellise identifikaatori loomiseks ühendate (lisate, ühendate) kahe või enama lahtri andmed, kasutades ampersandi (&). Lahtris F3 oleva üksuse kirjelduse kombineerimiseks lahtris G3 tühikuga eraldatud kuupäevaga sisestage valem „=F3&“ „&G3“ selle rea teise lahtrisse, näiteks E3. Kui soovite identifikaatorisse lisada ainult aasta (kuna ühes loendis kasutatakse täiskuupäevi ja teises ainult aastaid), lisage funktsioon YEAR, sisestades selle asemel lahtrisse E3 ‘=F3&” “&YEAR(G3)”. (Ärge lisage üksikuid jutumärke, need on ainult näite näitamiseks.) Kui olete valemi loonud, saate selle kopeerida kõigisse teistesse identifikaatori veeru lahtritesse, valides valemiga lahtri ja lohistades täitepidet veeru teiste lahtrite kohal, kuhu soovite valemit kopeerida. Kui vabastate hiirenupu, täidetakse iga lahter, mille üle lohistate, valemiga, kusjuures lahtriviited on kohandatud sama rea vastavate lahtrite järgi.
3
Võimaluse korral standardiseerige andmed. Kuigi mõistus tunneb ära, et “Inc.” ja “Incorporated” tähendavad sama asja, Excel mitte, kui te ei lase sellel üht või teist sõna ümber vormindada. Samuti võite pidada väärtusi, nagu 11 950 $ ja 11 999,95 $, piisavalt lähedaseks, et kattuda, kuid Excel ei tee seda, kui te seda ei ütle. Võite kasutada mõningaid lühendeid, näiteks “Co” tähendab “Ettevõte” ja “Inc” Lisatud, kasutades lisamärkide kärpimiseks funktsiooni LEFT string. Teisi lühendeid, nagu “Assn” tähistab “Association”, saab kõige paremini lahendada andmesisestusstiili juhendi koostamisega ja seejärel programmi kirjutamisega, mis otsib ja parandab sobimatuid vorminguid. Numbristringide, näiteks sihtnumbrite puhul, kus mõned kirjed sisaldavad ZIP+4 järelliidet ja teised mitte, saate uuesti kasutada LEFT stringi funktsiooni, et tuvastada ja sobitada ainult esmased sihtnumbrid. Et Excel tuvastaks numbrilised väärtused, mis on Sulge, kuid mitte sama, saate kasutada funktsiooni ROUND, et ümardada lähiväärtused samale arvule ja need sobitada. Täiendavaid tühikuid, näiteks sõnade vahele kahe tühiku sisestamist ühe asemel, saab eemaldada funktsiooni TRIM abil.
4
Looge võrdlusvalemi jaoks veerud. Nii nagu pidite looma loendi identifikaatorite jaoks veerud, peate looma veerud valemi jaoks, mis teie eest võrdleb. Teil on vaja iga loendi jaoks ühte veergu. Soovite need veerud sildistada näiteks “Puudunud?”
5
Sisestage igasse lahtrisse võrdlusvalem. Võrdlusvalemi jaoks kasutate funktsiooni MATCH, mis on pesastatud mõnes teises Exceli funktsioonis ISNA. Valem on kujul “=ISNA(MATCH(G3,$L$3:$L$14,FALSE))”, kus lahter esimese loendi identifikaatori veerust võrreldakse iga teise loendi identifikaatoriga, et näha, kas see ühtib ühega neist. Kui see ei ühti, puudub kirje ja selles lahtris kuvatakse sõna “TRUE”. Kui see kattub, on kirje olemas ja kuvatakse sõna “FALSE”. (Valemi sisestamisel ärge lisage ümbritsevaid jutumärke.) Saate kopeerida valemi veeru ülejäänud lahtritesse samamoodi, nagu kopeerisite lahtri identifikaatori valemit. Sel juhul muutub ainult identifikaatori lahtri lahtriviide, kuna dollarimärkide asetamine teise lahtri identifikaatorite loendi esimeste ja viimaste lahtrite ette rea- ja veeruviited muudab need absoluutseteks viideteks. Saate kopeerida esimese loendi võrdlusvalem teise loendi veeru esimesse lahtrisse. Seejärel peate redigeerima lahtriviiteid nii, et “G3” asendatakse teise loendi esimese identifikaatori lahtri viitega ja “$L$3:$L$14” asendatakse loendi esimese ja viimase identifikaatori lahtriga. teine nimekiri. (Jäta dollarimärgid ja koolon rahule.) Seejärel saate selle redigeeritud valemi kopeerida teise loendi võrdlusrea ülejäänud lahtritesse.
6
Sorteerige loendeid, et vajadusel hõlpsamini näha mittevastavaid väärtusi. Kui teie loendid on suured, peate võib-olla need sorteerima, et kõik mittevastavad väärtused kokku panna. Alltoodud alamsammudes toodud juhised teisendavad valemid väärtusteks, et vältida ümberarvutamise vigu ja kui teie loendid on suured, välditakse pikka ümberarvutamisaega.Lohistage hiirt loendis kõigi lahtrite kohal. Valige menüüst Redigeeri käsk Kopeeri Excel 2003 menüüst või Excel 2007 või 2010 lindi Avaleht lõikelaua rühmast. Valige Excel 2003 menüüst Redigeeri käsk Kleebi spetsiaalselt või Excel 2007 või 2010. aastate lindi avalehe grupi Lõikelaua rippmenüü nupust Kleebi. Valige “Väärtused”. ” dialoogiboksi Paste Special loendist Kleebi nimega. Dialoogi sulgemiseks klõpsake nuppu OK. Valige Excel 2003 menüüst Andmed käsk Sordi või Excel 2007 või 2010 lindi Andmed rühm Sorteeri ja filtreeri. Valige jaotises Sorteerimisalus loendist “Minu andmevahemikus on” valik “Päise rida”. dialoogis valige “Kadunud?” (või nimi, mille te võrdlusveeru päisele andsite) ja klõpsake nuppu OK. Korrake neid samme teise loendi jaoks.
7
Võrrelge mittevastavaid üksusi visuaalselt, et näha, miks need ei sobi. Nagu varem märgitud, on Excel loodud otsima täpseid andmete vasteid, välja arvatud juhul, kui olete seadistanud ligikaudseid vasteid otsima. Teie mittevastavus võib olla nii lihtne kui tähtede või numbrite kogemata ümberpaigutamine. See võib olla ka midagi, mis nõuab sõltumatut kontrolli, näiteks kontrollimine, kas loetletud varadest on vaja kõigepealt teatada.
8
Kopeerige andmeloendid ühele tööle.
9
Otsustage, millises loendis soovite vastavad või mittevastavad kirjed esile tõsta. Kui soovite kirjeid esile tõsta ainult ühes loendis, peaksite tõenäoliselt esile tõstma selle loendi ainulaadsed kirjed; st kirjed, mis ei ühti teise loendi kirjetega. Kui soovite mõlemas loendis kirjeid esile tõsta, peaksite esile tõstma kirjed, mis omavahel kattuvad. Selle näite puhul eeldame, et esimene loend võtab lahtrid G3 kuni G14 ja teine loend võtab lahtrid L3 kuni L14.
10
Valige loendist üksused, milles soovite unikaalseid või sobivaid üksusi esile tõsta. Kui soovite mõlemas loendis sobivaid üksusi esile tõsta, peate valima loendid ükshaaval ja rakendama võrdlusvalemit (kirjeldatakse järgmises etapis ) igasse loendisse.
11
Rakendage sobiv võrdlusvalem. Selleks peate oma Exceli versioonis avama tingimusvormingu dialoogi. Rakenduses Excel 2003 saate seda teha, valides menüüst Vorming suvandi Tingimuslik vormindamine, samas kui Excel 2007 ja 2010 puhul klõpsate lindi Avaleht jaotises Stiilid nuppu Tingimuslik vormindamine. Valige reegli tüübiks “Valem” ja sisestage oma valem väljale Redigeeri reegli kirjeldust. Kui soovite esile tõsta esimese loendi ainulaadsed kirjed, oleks valem “=COUNTIF($L$3:$L$14,G3= 0)”, kusjuures teise loendi lahtrite vahemik on renderdatud absoluutväärtustena ja viide esimese loendi esimesele lahtrile suhtelise väärtusena. (Ärge sisestage jutumärke.) Kui soovite esile tõsta teises loendis ainulaadsed kirjed, oleks valem “=COUNTIF($G$3:$G$14,L3=0)” lahtrite vahemikuga esimene loend on renderdatud absoluutväärtustena ja viide teise loendi esimesele lahtrile suhtelise väärtusena. (Ärge sisestage jutumärke.) Kui soovite esile tõsta igas loendis olevad kirjed, mis on teises loendis, vajate kahte valemit, ühte esimese loendi ja teist teise loendi 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)”. Eelnevalt märgitud, valite selle valemi rakendamiseks esimese loendi ja seejärel teise loendi selle valemi rakendamiseks. Rakendage mis tahes vormingut, mida soovite lipuga märgitud kirjete esiletõstmiseks. Dialoogi sulgemiseks klõpsake nuppu OK.