Trikovi u Excelu 160. deo – Dve kolone u tri koraka

Objedinite dve liste pomoću funkcije VLOOKUP

PROBLEM KORISNIKA: Imam listu prodaje po kupcima od početka meseca do juče. Moj saradnik mi je upravo poslao listu prodaje za jučerašnji dan. Treba da objedinim te dve liste (Slika 1).

REŠENJE: To je postupak u tri koraka:

Prvo primenite funkciju MATCH ili VLOOKUP(,,1,) na drugu listu da biste pronašli nove kupce. Dodajte te nove kupce prvoj listi s prethodnom prodajom u iznosu 0.

Sada kada prva lista sadrži nadskup s kupcima iz obe liste, primenite funkciju VLOOKUP na prvoj listi da biste preuzeli prodaju iz druge liste.

Dodajte prethodnu prodaju novoj pa konvertujte zbir u vrednosti. Sada možete da obrišete novu listu.

Kada treba utvrditi da li se neki kupac u koloni D već nalazi u koloni A, većina ljudi bi primenila sledeću funkciju: =VLOOKUP(D6,A6:A25,1,False). To će prikazati ili naziv kupca ili oznaku da podatak nije dostupan – #N/A. U ovom slučaju vas interesuju zapisi #N/A, jer su oni ti koji nedostaju sa originalne liste.

Međutim, pošto ste sad pročitali o funkciji MATCH, možete isto tako lako da upotrebite i tu funkciju da utvrdite koji kupci se nalaze na drugoj listi. Upotrebite funkciju =MATCH(D6,$A$6:$A$25,0). Na Slici 2 svi kupci u koloni D imaju svog para osim Forda.

Svi kupci s vrednošću #N/A su novi kupci i treba ih dodati listi kupaca u koloni A. Ako dobijete nekoliko vrednosti #N/A, sortirajte podatke po koloni F da biste nove kupce sakupili na jedno mesto. Iskopirajte samo naziv kupca i umetnite ga na dno liste u koloni A. Za nove kupce u kolonu B unesite $0 kao prodaju. U ovom primeru je nov samo Ford, pa možete da ga iskopirate iz ćelije D9 i umetnete u ćeliju A26. U ćeliju B26 upišite nulu.

Sada ste završili prvi od tri koraka. Sledeći korak je da prvoj listi dodate funkciju VLOOKUP.

Funkcija =VLOOKUP(A6,$E$ 6:$F$16,2,FALSE) dala bi kombinaciju vrednosti prihoda i poruka o grešci #N/A. Kada postojeći kupac nema prihod 18. juna, rezultat funkcije VLOOKUP biće #N/A. Da biste te vrednosti #N/A zamenili nulama, možete da upotrebite novu funkciju IFERROR.

Ispred kolone D ubacite dve nove kolone. Te privremene kolone označite kao Addl Rev (dodatni prihod) i New Total (novi zbir).

Formula u novoj ćeliji C6 glasi: =IFERROR(VLOOKUP(A6,$F$6:$G$16,2,FALSE),0). Iskopirajte tu formulu naniže do reda br. 26 (Slika 3).

Ako želite da proverite šta ste uradili, upotrebite AutoSum da dodate zbir na dnu kolona C i G. Oba ta zbira treba da se podudaraju.

Time se završava drugi od tri koraka. Završni korak je da se objedine prihodi iz kolona B i C (Slika 4).

U ćeliju D6 unesite formulu =B6+C6. Iskopirajte tu formulu naniže.

Iskopirajte opseg ćelija D6:D26. Izaberite ćeliju B6 pa opciju Paste Values.

PAŽNJA: Ne brinite što kolona D prikazuje veću vrednost no što bi trebalo. Kolona D još uvek sabira prihod tekućeg dana s novim zbirom u koloni B. Vrlo uskoro ćete obrisati kolonu D. Ako ne želite da kolona D ikada pokazuje pogrešnu vrednost, mogli biste da je prvo iskopirate pomoću opcije Paste Values na njeno isto mesto.

Promenite datum u ćeliji A3 da biste pokazali da izveštaj sadrži podatke zaključno sa 18. junom.

Sada obrišite privremene kolone C do H.

Izgleda da su originalni podaci sortirani po opadajućem prihodu. Izaberite ćeliju B6 pa kliknite na dugme za sortiranje na kartici Data trake s ikonicama.

Upoređivanje i objedinjavanje dve liste pomoću funkcije VLOOKUP jedan je od osnovnih metoda analize podataka. Postoji i brži i lakši način da se ovo uradi. Pogledajte tekst “Upotrebite pivot tabelu da uporedite dve liste”.