Jedna od mnogih mogućnosti programa Microsoft Excel je mogućnost uspoređivanja dva popisa podataka, identificiranja podudarnosti među popisima i identificiranja stavki koje se nalaze samo na jednom popisu. To je korisno pri usporedbi financijskih zapisa ili provjeri nalazi li se određeno ime u bazi podataka. Možete upotrijebiti funkciju MATCH za identifikaciju i označavanje zapisa koji se podudaraju ili ne podudaraju, ili možete koristiti oblikovanje uvjeta s funkcijom COUNTIF. Sljedeći koraci govore vam kako ih koristiti za usklađivanje podataka.
Koraci
Metoda 1 od 2: Identificiranje zapisa s funkcijom MATCH
Korak 1. Kopirajte popise podataka na jedan radni list
Excel može raditi s više radnih listova unutar jedne radne knjige ili s više radnih knjiga, ali uspoređivanje popisa bit će vam lakše ako kopirate njihove podatke na jedan radni list.
Korak 2. Dajte svakoj stavci popisa jedinstveni identifikator
Ako vaša dva popisa nemaju zajednički način identifikacije, možda ćete morati dodati svaki stupac svakom popisu podataka koji identificira tu stavku u Excelu kako bi mogao vidjeti je li stavka na danom popisu povezana s stavkom na drugom popisu. Priroda ovog identifikatora ovisit će o vrsti podataka koje pokušavate upariti. Za svaki popis stupaca trebat će vam identifikator.
- Za financijske podatke povezane s određenim razdobljem, kao što je porezna evidencija, to bi mogao biti opis imovine, datum stjecanja imovine ili oboje. U nekim slučajevima, unos se može identificirati kodnim brojem; međutim, ako se isti sustav ne koristi za oba popisa, ovaj identifikator može stvoriti podudarnosti tamo gdje ih nema ili zanemariti podudarnosti koje treba napraviti.
- U nekim slučajevima možete uzeti stavke s jednog popisa i kombinirati ih sa stavkama s drugog popisa kako biste stvorili identifikator, poput opisa fizičke imovine i godine kada je kupljena. Da biste stvorili takav identifikator, povezujete (dodajete, kombinirate) podatke iz dvije ili više ćelija pomoću znaka & (). Za kombiniranje opisa stavke u ćeliji F3 s datumom u ćeliji G3, odvojenim razmakom, unijeli biste formulu '= F3 & "" & G3' u drugu ćeliju u tom retku, poput E3. Ako želite u identifikator uključiti samo godinu (jer jedan popis koristi pune datume, a drugi samo godine), uključili biste funkciju GODINA tako da umjesto toga u ćeliju E3 upišete '= F3 & "" & GODINU (G3) ". (Ne navodite pojedinačne navodnike; oni su tu samo da naznače primjer.)
- Nakon što stvorite formulu, možete je kopirati u sve ostale ćelije stupca identifikatora odabirom ćelije s formulom i povlačenjem ručice ispune preko drugih ćelija stupca u koje želite kopirati formulu. Kad otpustite gumb miša, svaka ćelija koju prevučete bit će popunjena formulom, a reference ćelija prilagođene su odgovarajućim ćelijama u istom retku.
Korak 3. Standardizirajte podatke gdje je to moguće
Dok um prepoznaje da "Inc." i "Incorporated" znače istu stvar, Excel ne zna osim ako morate ponovno formatirati jednu ili drugu riječ. Slično, vrijednosti kao što su 11, 950 i 11, 999,95 USD možete smatrati dovoljno bliskim da se podudaraju, ali Excel to neće učiniti ako im to ne kažete.
- Možete se nositi s nekim kraticama, kao što su "Co" za "Company" i "Inc" za "Incorporated" pomoću funkcije LEFT string za skraćivanje dodatnih znakova. Druge kratice, poput "Assn" za "Association", mogu biti najbolje rješava se uspostavljanjem vodiča za stil unosa podataka, a zatim pisanjem programa za traženje i ispravljanje neprikladnih formata.
- Za nizove brojeva, poput poštanskih brojeva gdje neki unosi uključuju ZIP+4 sufiks, a drugi ne, ponovno možete upotrijebiti funkciju NIJEVI niz za prepoznavanje i podudaranje samo primarnih poštanskih brojeva. Da bi Excel prepoznao numeričke vrijednosti koje su bliske, ali nisu iste, pomoću funkcije ROUND možete zaokružiti vrijednosti zatvaranja na isti broj i uskladiti ih.
- Dodatni razmaci, poput upisivanja dva razmaka između riječi umjesto jednog, mogu se ukloniti pomoću funkcije TRIM.
Korak 4. Izradite stupce za usporedbenu formulu
Baš kao što ste morali stvoriti stupce za identifikatore popisa, morat ćete stvoriti stupce za formulu koja vrši usporedbu umjesto vas. Za svaki popis trebat će vam jedan stupac.
Ove stupce želite označiti nečim poput "Nedostaje?"
Korak 5. Unesite formulu za usporedbu u svaku ćeliju
Za formulu usporedbe upotrijebit ćete funkciju MATCH ugniježđenu unutar druge Excel funkcije, ISNA.
- Formula ima oblik "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", gdje se ćelija stupca identifikatora prvog popisa uspoređuje sa svakim od identifikatora na drugom popisu s provjerite odgovara li jednom od njih. Ako se ne podudara, zapis nedostaje, a riječ "TRUE" bit će prikazana u toj ćeliji. Ako se podudara, zapis je prisutan i prikazat će se riječ "FALSE". (Prilikom unosa formule nemojte uključivati navodnike u prilogu.)
- Formulu možete kopirati u preostale ćelije stupca na isti način na koji ste kopirali formulu identifikatora ćelije. U tom se slučaju mijenja samo referenca ćelije za ćeliju identifikatora jer ih stavljanje znakova dolara ispred retka i stupca za prvu i posljednju ćeliju na popisu drugih identifikatora ćelije čini apsolutnim referencama.
- Formulu za usporedbu za prvi popis možete kopirati u prvu ćeliju stupca za drugi popis. Zatim ćete morati urediti reference ćelija tako da se "G3" zamijeni referencom za prvu ćeliju identifikatora drugog popisa, a "$ L $ 3: $ L $ 14" zamijeni prvom i posljednjom ćelijom identifikatora drugi popis. (Ostavite znakove dolara i dvotočku na miru.) Zatim možete kopirati ovu uređenu formulu u preostale ćelije u redu za usporedbu na drugom popisu.
Korak 6. Poredajte popise kako biste lakše vidjeli neprikladne vrijednosti, ako je potrebno
Ako su vaši popisi veliki, možda ćete ih morati sortirati da biste spojili sve vrijednosti koje se ne podudaraju. Upute u podstupcima ispod pretvarat će formule u vrijednosti kako bi se izbjegle pogreške ponovnog izračuna, a ako su vaši popisi veliki, izbjeći će dugo vrijeme ponovnog izračuna.
- Povucite mišem preko svih ćelija na popisu da biste ga odabrali.
- Odaberite Kopiraj s izbornika Uređivanje u Excelu 2003 ili iz grupe Međuspremnik na vrpci Početna u programu Excel 2007 ili 2010.
- Odaberite Posebno lijepljenje s izbornika Uređivanje u Excelu 2003 ili s padajućeg gumba Zalijepi u grupi Međuspremnik na vrpci Početna u programu Excel 2007 ili 2010 -ih.
- Odaberite "Vrijednosti" s popisa Zalijepi kao u dijaloškom okviru Zalijepi posebno. Pritisnite U redu da biste zatvorili dijalog.
- Odaberite Razvrstaj iz izbornika Podaci u Excelu 2003 ili grupe Sortiranje i filtriranje vrpce Podaci u Excelu 2007 ili 2010.
- Odaberite "Redak zaglavlja" s popisa "Moj raspon podataka ima" u dijalogu Sortiraj po, odaberite "Nedostaje?" (ili naziv koji ste zapravo dali zaglavlju stupca za usporedbu) i kliknite U redu.
- Ponovite ove korake za drugi popis.
Korak 7. Vizualno usporedite stavke koje se ne podudaraju kako biste vidjeli zašto se ne podudaraju
Kao što je prethodno napomenuto, Excel je dizajniran za traženje točnih podataka, osim ako ga ne postavite za traženje približnih. Vaše nepodudaranje moglo bi biti jednostavno poput slučajnog premještanja slova ili znamenki. To bi također moglo biti nešto što zahtijeva neovisnu provjeru, poput provjere je li potrebno uopće prijaviti navedenu imovinu.
Metoda 2 od 2: Uvjetno oblikovanje s COUNTIF
Korak 1. Kopirajte popise podataka na jedan radni list
Korak 2. Odlučite na kojem popisu želite označiti podudarne ili neusklađene zapise
Ako želite označiti zapise samo na jednom popisu, vjerojatno ćete htjeti istaknuti zapise jedinstvene za taj popis; odnosno zapisi koji se ne podudaraju sa zapisima na drugom popisu. Ako želite označiti zapise na oba popisa, htjet ćete označiti zapise koji se međusobno podudaraju. Za potrebe ovog primjera, pretpostavit ćemo da prvi popis zauzima ćelije G3 do G14, a drugi popis zauzima ćelije L3 do L14.
Korak 3. Odaberite stavke na popisu u koje želite istaknuti jedinstvene ili odgovarajuće stavke
Ako želite označiti podudarne stavke na oba popisa, morat ćete odabrati popise jednu po jednu i primijeniti formulu za usporedbu (opisanu u sljedećem koraku) na svaki popis.
Korak 4. Primijenite odgovarajuću formulu za usporedbu
Da biste to učinili, morat ćete pristupiti dijalogu Uvjetno oblikovanje u svojoj verziji programa Excel. U programu Excel 2003 to činite odabirom uvjetnog oblikovanja na izborniku Oblikovanje, dok u programima Excel 2007 i 2010 kliknite gumb Uvjetno oblikovanje u grupi Stilovi na vrpci Početna. Odaberite vrstu pravila kao "Formula" i unesite svoju formulu u polje Uređivanje opisa pravila.
- Ako želite označiti zapise jedinstvene za prvi popis, formula bi bila "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", s rasponom ćelija drugog popisa prikazanim kao apsolutne vrijednosti i referencom u prvu ćeliju prvog popisa kao relativnu vrijednost. (Ne unosite bliske navodnike.)
- Ako želite označiti zapise jedinstvene za drugi popis, formula bi bila "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", s rasponom ćelija prvog popisa izraženim kao apsolutne vrijednosti i referencom u prvu ćeliju drugog popisa kao relativnu vrijednost. (Ne unosite bliske navodnike.)
- Ako želite istaknuti zapise na svakom popisu koji se nalaze na drugom popisu, trebat će vam dvije formule, jedna za prvi popis i jedna za drugi. Formula za prvi popis je "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", dok je formula za drugi popis COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Kao prethodno navedeno, odabirete prvi popis da biste primijenili njegovu formulu, a zatim odaberite drugi popis da biste primijenili njegovu formulu.
- Primijenite bilo koje oblikovanje koje želite označiti zapise označene zastavicom. Pritisnite U redu da biste zatvorili dijalog.
Video - Korištenjem ove usluge neki se podaci mogu podijeliti s YouTubeom
Savjeti
- Umjesto da koristite referencu ćelije s COUNTIF metodom uvjetnog oblikovanja, možete unijeti vrijednost koju ćete tražiti i označiti jedan ili više popisa za instance te vrijednosti.
- Da biste pojednostavili obrasce za usporedbu, možete stvoriti nazive za svoj popis, poput "Popis1" i "Popis2". Zatim, prilikom pisanja formula, ti nazivi popisa mogu zamijeniti apsolutne raspone ćelija korištene u gornjim primjerima.
