Jedna od mnogih mogućnosti programa Microsoft Excel je mogućnost upoređivanja dvije liste podataka, identifikovanje podudarnosti između lista i identifikovanje stavki koje se nalaze samo na jednoj listi. Ovo je korisno pri poređenju finansijske evidencije ili provjeri da li se određeno ime nalazi 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 uvjetovanja s funkcijom COUNTIF. Sljedeći koraci govore vam kako ih koristiti za usklađivanje podataka.
Koraci
Metoda 1 od 2: Identificiranje zapisa pomoću funkcije MATCH
Korak 1. Kopirajte liste podataka na jedan radni list
Excel može raditi s više radnih listova u jednoj radnoj knjizi ili s više radnih knjiga, ali upoređivanje lista bit će vam lakše ako kopirate njihove podatke na jedan radni list.
Korak 2. Dajte svakoj stavci liste jedinstveni identifikator
Ako vaše dvije liste 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 danoj listi povezana sa stavkom na drugoj listi. Priroda ovog identifikatora ovisit će o vrsti podataka koje pokušavate uporediti. Za svaku listu kolona trebat će vam identifikator.
- Za financijske podatke povezane s određenim razdobljem, kao što je porezna evidencija, to bi mogao biti opis imovine, datum kada je sredstvo nabavljeno ili oboje. U nekim slučajevima, unos se može identificirati kodnim brojem; međutim, ako se isti sistem ne koristi za obje liste, ovaj identifikator može stvoriti podudaranja tamo gdje ih nema ili zanemariti podudarnosti koje treba napraviti.
- U nekim slučajevima možete uzeti stavke s jedne liste i kombinirati ih sa stavkama s druge liste kako biste stvorili identifikator, kao što je opis fizičke imovine i godina kada je kupljena. Da biste stvorili takav identifikator, povezujete (dodajete, kombinirate) podatke iz dvije ili više ćelija pomoću ampersanda (&). Da biste kombinirali opis 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 unijeti samo godinu u identifikator (jer jedna lista koristi pune datume, a druga samo godine), uključili biste funkciju GODINA tako što ćete umjesto toga unijeti '= F3 & "" & GODINU (G3) "u ćeliju E3. (Nemojte uključivati pojedinačne navodnike; oni su tu samo da naznače primjer.)
- Nakon što kreirate 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. Kada 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 ponovo 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 skraćenicama, kao što su "Co" za "Company" i "Inc" za "Incorporated" pomoću funkcije LEFT string za skraćivanje dodatnih znakova. Druge skraćenice, 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, možete ponovno 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. Kreirajte stupce za formulu za usporedbu
Baš kao što ste morali stvoriti stupce za identifikatore popisa, morat ćete stvoriti stupce za formulu koja vrši poređenje umjesto vas. Za svaku listu trebat će vam jedna kolona.
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 prve liste uspoređuje sa svakim od identifikatora na drugoj listi provjerite odgovara li jednom od njih. Ako se ne podudara, zapis nedostaje, a riječ "TRUE" će se prikazati u toj ćeliji. Ako se podudara, zapis je prisutan i bit će prikazana riječ "FALSE". (Kada unosite formulu, 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 ovom slučaju mijenja se samo referenca ćelije za ćeliju identifikatora, jer stavljanje znakova dolara ispred retka i kolone za prvu i posljednju ćeliju na popisu drugih identifikatora ćelije čini ih apsolutnim referencama.
- Formulu za usporedbu za prvu listu možete kopirati u prvu ćeliju kolone za drugu listu. Zatim ćete morati urediti reference ćelija tako da se "G3" zamijeni referencom za prvu ćeliju identifikatora druge liste, a "$ L $ 3: $ L $ 14" zamijeni prvom i posljednjom ćelijom identifikatora druga lista. (Ostavite znakove dolara i dvotočku na miru.) Zatim možete kopirati ovu uređenu formulu u preostale ćelije u redu za usporedbu druge liste.
Korak 6. Sortirajte liste da biste lakše vidjeli neusklađene vrijednosti, ako je potrebno
Ako su vaše liste velike, možda ćete ih morati sortirati da biste spojili sve vrijednosti koje se ne podudaraju. Upute u pod -koracima ispod pretvarat će formule u vrijednosti kako bi se izbjegle greške ponovnog izračunavanja, a ako su vaše liste velike, izbjeći će dugo vrijeme ponovnog izračunavanja.
- Prevucite mišem preko svih ćelija na listi da biste je odabrali.
- Odaberite Kopiraj iz izbornika Uređivanje u Excelu 2003 ili iz grupe Ostava na vrpci Početna u programu Excel 2007 ili 2010.
- Odaberite Posebno lijepljenje iz izbornika Uređivanje u Excelu 2003 ili iz padajućeg dugmeta Zalijepi u grupi Ostava na vrpci programa Excel 2007 ili 2010s Početna.
- Odaberite "Vrijednosti" sa liste Zalijepi kao u dijaloškom okviru Zalijepi posebno. Pritisnite OK da biste zatvorili dijalog.
- Odaberite Sortiraj iz izbornika Podaci u programu Excel 2003 ili grupe Sortiranje i filtriranje na vrpci Podaci u programu Excel 2007 ili 2010.
- Odaberite "Red zaglavlja" sa liste "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 drugu listu.
Korak 7. Vizuelno uporedite stavke koje se ne podudaraju da vidite zašto se ne podudaraju
Kao što je ranije napomenuto, Excel je dizajniran za traženje točnih podataka, osim ako ga niste postavili za traženje približnih. Vaše podudaranje moglo bi biti jednostavno kao slučajno prenošenje slova ili znamenki. To bi također moglo biti nešto što zahtijeva nezavisnu provjeru, poput provjere da li se prvo treba prijaviti popisana imovina.
Metoda 2 od 2: Uslovno oblikovanje sa COUNTIF
Korak 1. Kopirajte liste podataka na jedan radni list
Korak 2. Odlučite na kojoj listi želite označiti podudarne ili neusklađene zapise
Ako želite označiti zapise samo na jednoj listi, vjerojatno ćete htjeti istaknuti zapise jedinstvene za tu listu; odnosno zapisi koji se ne podudaraju sa zapisima na drugoj listi. Ako želite označiti zapise na obje liste, željet ćete označiti zapise koji se međusobno podudaraju. Za potrebe ovog primjera, pretpostavit ćemo da prva lista zauzima ćelije od G3 do G14, a druga lista zauzima ćelije od 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 obje liste, morat ćete odabrati liste jednu po jednu i primijeniti formulu za usporedbu (opisanu u sljedećem koraku) na svaku listu.
Korak 4. Primijenite odgovarajuću formulu za usporedbu
Da biste to učinili, morat ćete pristupiti dijalogu Uslovno oblikovanje u svojoj verziji programa Excel. U programu Excel 2003 to radite odabirom uvjetnog oblikovanja u 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 prvu listu, formula bi bila "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", s rasponom ćelija drugog popisa izraženim kao apsolutne vrijednosti i referencom u prvu ćeliju prve liste kao relativnu vrijednost. (Ne unosite zatvorene navodnike.)
- Ako želite označiti zapise jedinstvene za drugu listu, formula bi bila "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", s rasponom ćelija prve liste izraženim kao apsolutne vrijednosti i referencom u prvu ćeliju druge liste kao relativnu vrijednost. (Ne unosite zatvorene navodnike.)
- Ako želite označiti zapise na svakoj listi koji se nalaze na drugoj listi, trebat će vam dvije formule, jedna za prvu listu i jedna za drugu. Formula za prvu listu je "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", dok je formula za drugu listu COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Kao ranije navedeno, birate prvu listu da biste primijenili njenu formulu, a zatim odaberite drugu listu da biste primijenili njenu formulu.
- Primijenite bilo koje oblikovanje koje želite označiti zapise koji su označeni. Pritisnite OK da biste zatvorili dijalog.
Video - Korištenjem ove usluge neke se informacije mogu podijeliti s YouTubeom
Savjeti
- Umjesto da koristite referencu ćelije s COUNTIF metodom uvjetnog oblikovanja, možete unijeti vrijednost koju treba tražiti i označiti jednu ili više lista za instance te vrijednosti.
- Da biste pojednostavili obrasce za usporedbu, možete stvoriti imena za svoju listu, poput "Lista1" i "Lista2". Zatim, prilikom pisanja formula, ovi nazivi popisa mogu zamijeniti apsolutne raspone ćelija korištene u gornjim primjerima.
