Ena od številnih zmogljivosti programa Microsoft Excel je možnost primerjave dveh seznamov podatkov, prepoznavanja ujemanj med seznamoma in ugotavljanja, katere postavke se nahajajo samo na enem seznamu. To je uporabno pri primerjavi finančnih evidenc ali preverjanju, ali je določeno ime v bazi podatkov. S funkcijo MATCH lahko prepoznate in označite ujemajoče se ali neustrezne zapise ali pa uporabite oblikovanje pogojev s funkcijo COUNTIF. Naslednji koraki vam povedo, kako uporabiti vsakega za ujemanje vaših podatkov.
Koraki
Metoda 1 od 2: Prepoznavanje zapisov s funkcijo MATCH
Korak 1. Kopirajte sezname podatkov na en sam delovni list
Excel lahko dela z več delovnimi listi v enem delovnem zvezku ali z več delovnimi zvezki, vendar boste lažje primerjali sezname, če njihove podatke kopirate na en sam delovni list.
Korak 2. Vsakemu elementu seznama dajte edinstven identifikator
Če oba seznama nimata skupnega načina za njihovo identifikacijo, boste morda morali vsakemu seznamu podatkov, ki identificira to postavko, dodati dodaten stolpec v Excel, da bo lahko videl, ali je element na danem seznamu povezan z elementom. na drugem seznamu. Narava tega identifikatorja bo odvisna od vrste podatkov, ki jih poskušate ujemati. Za vsak seznam stolpcev boste potrebovali identifikator.
- Za finančne podatke, povezane z določenim obdobjem, na primer davčne evidence, je to lahko opis sredstva, datum nakupa sredstva ali oboje. V nekaterih primerih je vnos lahko označen s kodno številko; če pa se isti sistem ne uporablja za oba seznama, lahko ta identifikator ustvari ujemanja, kjer jih ni, ali pa prezre ujemanja, ki jih je treba narediti.
- V nekaterih primerih lahko vzamete predmete z enega seznama in jih združite s predmeti z drugega seznama, da ustvarite identifikator, na primer opis fizičnega sredstva in leto, ko je bil kupljen. Če želite ustvariti tak identifikator, povežete (dodate, združite) podatke iz dveh ali več celic s pomočjo znaka & (). Če želite opis elementa v celici F3 združiti z datumom v celici G3, ločenim s presledkom, vnesite formulo '= F3 & "" & G3' v drugo celico v tej vrstici, na primer E3. Če želite v identifikator vključiti le leto (ker en seznam uporablja polne datume, drugi pa samo leta), bi funkcijo YEAR vključili tako, da bi v celico E3 namesto tega vnesli '= F3 & "" & YEAR (G3)'. (Ne vključujte enojnih narekovajev; ti so samo za prikaz primera.)
- Ko ustvarite formulo, jo lahko kopirate v vse druge celice stolpca identifikatorja, tako da izberete celico s formulo in povlečete ročico za polnjenje nad druge celice stolpca, kamor želite kopirati formulo. Ko spustite gumb miške, bo vsaka celica, ki jo povlečete, napolnjena s formulo, sklici na celice pa bodo prilagojeni ustreznim celicam v isti vrstici.
Korak 3. Standardizirajte podatke, kjer je to mogoče
Medtem ko um prepozna, da "Inc." in "Incorporated" pomenita isto stvar, Excel pa ne, če je ne formatirate ene ali druge besede. Podobno lahko menite, da so vrednosti, kot so 11, 950 in 11, 999,95 USD, dovolj blizu, da se ujemajo, vendar Excel tega ne bo storil, če tega ne poveste.
- Z nekaterimi okrajšavami, na primer "Co" za "Company" in "Inc" za "Incorporated", se lahko ukvarjate z uporabo funkcije LEFT niz za skrajšanje dodatnih znakov. Druge okrajšave, na primer "Assn" za "Association", so najbolje odpraviti z oblikovanjem priročnika za slog vnosa podatkov in nato pisanjem programa za iskanje in odpravljanje nepravilnih oblik.
- Za nize številk, na primer poštne številke, pri katerih nekateri vnosi vključujejo pripono ZIP+4, drugi pa ne, lahko znova uporabite funkcijo NIZ levo za prepoznavanje in ujemanje samo primarnih poštnih številk. Če želite, da Excel prepozna številčne vrednosti, ki so blizu, vendar niso enake, lahko s funkcijo ROUND zaokrožite vrednosti zapiranja na isto število in jih ujemate.
- Dodatne presledke, na primer vnos dveh presledkov med besedami namesto enega, lahko odstranite s funkcijo TRIM.
Korak 4. Ustvarite stolpce za primerjalno formulo
Tako kot ste morali ustvariti stolpce za identifikatorje seznamov, boste morali ustvariti stolpce za formulo, ki vam bo omogočila primerjavo. Za vsak seznam potrebujete en stolpec.
Te stolpce boste želeli označiti z nečim, kot je "Manjka?"
Korak 5. V vsako celico vnesite primerjalno formulo
Za primerjalno formulo boste uporabili funkcijo MATCH, ki je ugnezdena v drugi Excelovi funkciji, ISNA.
- Formula je v obliki "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", kjer se celica stolpca identifikatorja prvega seznama primerja z vsakim od identifikatorjev na drugem seznamu z preverite, ali se ujema z enim od njih. Če se ne ujema, manjka zapis in beseda "TRUE" bo prikazana v tej celici. Če se ujema, je zapis prisoten in prikaže se beseda "FALSE". (Ko vnašate formulo, ne vključujte navodil, ki so v njej.)
- Formulo lahko kopirate v preostale celice stolpca na enak način, kot ste kopirali formulo za identifikator celice. V tem primeru se spremeni samo sklic na celico za celico identifikatorja, saj so z dolarskimi znaki pred sklici vrstice in stolpca za prvo in zadnjo celico na seznamu drugih identifikatorjev celic absolutni sklici.
- Formulo za primerjavo za prvi seznam lahko kopirate v prvo celico stolpca za drugi seznam. Nato boste morali urediti sklice na celice, tako da se "G3" nadomesti z referenco za prvo celico identifikatorja drugega seznama in "$ L $ 3: $ L $ 14" nadomesti s prvo in zadnjo celico identifikatorja drugi seznam. (Pustite znake za dolar in dvopičje pri miru.) To urejeno formulo lahko nato kopirate v preostale celice v vrstici za primerjavo na drugem seznamu.
Korak 6. Razvrstite sezname, da po potrebi lažje vidite neskladne vrednosti
Če so vaši seznami veliki, jih boste morda morali razvrstiti, da združite vse vrednosti, ki se ne ujemajo. Navodila v spodnjih podkorakih bodo pretvorila formule v vrednosti, da bi se izognili napakam pri ponovnem izračunu, in če so vaši seznami veliki, se bo izognil dolgemu času ponovnega izračuna.
- Povlecite miško nad vse celice na seznamu, da ga izberete.
- V meniju Urejanje v Excelu 2003 izberite Kopiraj ali v skupini Odložišče na traku Domov v Excelu 2007 ali 2010.
- V meniju Urejanje v Excelu 2003 izberite Prilepi posebno ali v spustnem gumbu Prilepi v skupini odložišče na traku Doma v Excelu 2007 ali 2010.
- Na seznamu Prilepi kot v pogovornem oknu Prilepi izberite "Vrednosti". Kliknite V redu, da zaprete pogovorno okno.
- Izberite Razvrsti v meniju Podatki v Excelu 2003 ali skupini Razvrsti in filtriraj na traku Podatki v Excelu 2007 ali 2010.
- Izberite "Vrstica glave" s seznama "Moj obseg podatkov ima" v pogovornem oknu Razvrsti po, izberite "Manjka?" (ali ime, ki ste ga dejansko dali glavi stolpca za primerjavo) in kliknite V redu.
- Ponovite te korake za drugi seznam.
Korak 7. Vizualno primerjajte neustrezne elemente, da vidite, zakaj se ne ujemajo
Kot je bilo že omenjeno, je Excel zasnovan za iskanje natančnih podatkov, razen če ga nastavite za iskanje približnih. Vaše ujemanje je lahko tako preprosto kot naključno prenašanje črk ali številk. Lahko bi bilo tudi nekaj, kar zahteva neodvisno preverjanje, na primer preverjanje, ali je treba prijavljena sredstva navesti.
Metoda 2 od 2: Pogojno oblikovanje z COUNTIF
Korak 1. Kopirajte sezname podatkov na en sam delovni list
Korak 2. Odločite se, na katerem seznamu želite označiti ujemajoče se ali ne ujemajoče se zapise
Če želite označiti zapise samo na enem seznamu, boste verjetno želeli označiti zapise, ki so edinstveni za ta seznam; to je zapisov, ki se ne ujemajo z zapisi na drugem seznamu. Če želite označiti zapise na obeh seznamih, boste želeli označiti zapise, ki se med seboj ujemajo. Za namene tega primera bomo domnevali, da prvi seznam zajema celice od G3 do G14, drugi pa celice od L3 do L14.
Korak 3. Na seznamu izberite elemente, v katerih želite označiti edinstvene ali ujemajoče se predmete
Če želite označiti ujemajoče se postavke na obeh seznamih, jih morate izbrati enega za drugim in za vsak seznam uporabiti formulo za primerjavo (opisano v naslednjem koraku).
Korak 4. Uporabite ustrezno primerjalno formulo
Če želite to narediti, morate v svoji različici Excela odpreti pogovorno okno Pogojno oblikovanje. V Excelu 2003 to storite tako, da v meniju Oblika izberete Pogojno oblikovanje, v Excelu 2007 in 2010 pa kliknete gumb Pogojno oblikovanje v skupini Slogi na traku Domov. Izberite vrsto pravila kot "Formula" in vnesite formulo v polje Urejanje opisa pravila.
- Če želite označiti zapise, edinstvene za prvi seznam, bi bila formula "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", pri čemer je obseg celic drugega seznama upodobljen kot absolutne vrednosti in referenca v prvo celico prvega seznama kot relativno vrednost. (Ne vnašajte bližnjih narekovajev.)
- Če želite označiti zapise, edinstvene za drugi seznam, bi bila formula "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", pri čemer je obseg celic prvega seznama upodobljen kot absolutne vrednosti in referenca v prvo celico drugega seznama kot relativno vrednost. (Ne vnašajte bližnjih narekovajev.)
- Če želite označiti zapise na vsakem seznamu, ki jih najdete na drugem seznamu, potrebujete dve formuli, eno za prvi seznam in eno za drugo. Formula za prvi seznam je "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", medtem ko je formula za drugi seznam COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Kot smo že omenili, izberete prvi seznam, da uporabite njegovo formulo, nato pa drugi seznam, da uporabite njegovo formulo.
- Uporabite poljubno oblikovanje, da označite zapise, označene z zastavico. Kliknite V redu, da zaprete pogovorno okno.
Video - z uporabo te storitve se lahko nekateri podatki delijo z YouTubom
Nasveti
- Namesto da bi uporabili sklic na celico s pogojnim načinom oblikovanja COUNTIF, lahko vnesete vrednost, ki jo želite poiskati, in označite enega ali več seznamov za primerke te vrednosti.
- Za poenostavitev primerjalnih obrazcev lahko ustvarite imena za svoj seznam, na primer "List1" in "List2". Nato lahko pri pisanju formul ta imena seznamov nadomestijo absolutne obsege celic, uporabljene v zgornjih primerih.
