Sadržaj
Imamo dvije tablice (npr. stara i nova verzija cjenika) koje trebamo usporediti i brzo pronaći razlike:
Odmah je jasno da je u novom cjeniku nešto dodano (hurme, češnjak...), nešto nestalo (kupine, maline...), nekim robama su se promijenile cijene (smokve, dinje...). Morate brzo pronaći i prikazati sve te promjene.
Za svaki zadatak u Excelu gotovo uvijek postoji više od jednog rješenja (obično 4-5). Za naš problem može se koristiti mnogo različitih pristupa:
- funkcija VPR (VLOOKUP) — potražite nazive proizvoda iz novog cjenika u starom i prikažite staru cijenu uz novu, pa uhvatite razlike
- spojite dvije liste u jednu i zatim na temelju toga napravite pivot tablicu u kojoj će razlike biti jasno vidljive
- koristite dodatak Power Query za Excel
Uzmimo ih sve redom.
Metoda 1. Usporedba tablica s funkcijom VLOOKUP
Ako niste potpuno upoznati s ovom divnom značajkom, prvo pogledajte ovdje i pročitajte ili pogledajte video upute o njoj – uštedite si nekoliko godina života.
Obično se ova funkcija koristi za povlačenje podataka iz jedne tablice u drugu podudaranjem s nekim zajedničkim parametrom. U ovom slučaju, upotrijebit ćemo ga da gurnemo stare cijene u novu cijenu:
Oni proizvodi, kod kojih se pojavila greška #N/A, nisu na starom popisu, tj. dodani su. Jasno su vidljive i promjene cijena.
Prozodija ova metoda: jednostavna i jasna, "klasika žanra", kako kažu. Radi u bilo kojoj verziji programa Excel.
Cons je također tamo. Za traženje proizvoda dodanih na novi cjenik morat ćete napraviti isti postupak u suprotnom smjeru, odnosno povući nove cijene na staru cijenu uz pomoć VLOOKUP-a. Ako se sutra promijene veličine tablica, tada će se formule morati prilagoditi. Pa, i na stvarno velikim stolovima (> 100 tisuća redaka), sva će ta sreća pristojno usporiti.
Metoda 2: Usporedba tablica pomoću pivota
Kopirajmo naše tablice jednu ispod druge, dodajući stupac s nazivom cjenika, tako da kasnije možete razumjeti iz koje liste koji redak:
Sada ćemo na temelju stvorene tablice kreirati sažetak kroz Umetanje – zaokretna tablica (Umetni — zaokretna tablica). Bacimo polje Proizvod na područje linija, polje Cijena na područje stupca i polje ЦEna u rasponu:
Kao što vidite, pivot tablica će automatski generirati opći popis svih proizvoda iz starog i novog cjenika (bez ponavljanja!) i sortirati proizvode po abecedi. Jasno su vidljivi dodani proizvodi (nemaju staru cijenu), uklonjeni proizvodi (nemaju novu cijenu) i promjene cijena, ako ih ima.
Ukupni zbrojevi u takvoj tablici nemaju smisla i mogu se onemogućiti na kartici Konstruktor – Ukupni zbrojevi – Onemogući za retke i stupce (Dizajn — ukupni zbrojevi).
Ako se mijenjaju cijene (ali ne i količina robe!), tada je dovoljno jednostavno ažurirati kreirani sažetak desnim klikom na njega – Osvježiti.
Prozodija: Ovaj pristup je red veličine brži s velikim tablicama nego VLOOKUP.
Cons: potrebno je ručno kopirati podatke jedan ispod drugog i dodati stupac s nazivom cjenika. Ako se veličine stolova promijene, onda morate sve ponoviti iznova.
Metoda 3: Usporedba tablica pomoću Power Queryja
Power Query je besplatni dodatak za Microsoft Excel koji vam omogućuje učitavanje podataka u Excel iz gotovo bilo kojeg izvora i zatim transformaciju tih podataka na bilo koji željeni način. U programu Excel 2016 ovaj je dodatak već ugrađen prema zadanim postavkama na kartici Datum (Podaci), a za Excel 2010-2013 trebate ga zasebno preuzeti s Microsoftove web stranice i instalirati – nabavite novu karticu Upit snage.
Prije učitavanja naših cjenika u Power Query, prvo ih je potrebno pretvoriti u pametne tablice. Da biste to učinili, odaberite raspon s podacima i pritisnite kombinaciju na tipkovnici Ctrl+T ili odaberite karticu na vrpci Početna – Format kao tablica (Početna — Formatiraj kao tablicu). Imena kreiranih tablica mogu se korigirati na kartici Konstruktor (Napustit ću standard Tablica 1 и Tablica 2, koji se dobivaju prema zadanim postavkama).
Učitajte staru cijenu u Power Query pomoću gumba Iz tablice/raspona (Iz tablice/raspona) s kartice Datum (Datum) ili s kartice Upit snage (ovisno o verziji Excela). Nakon učitavanja vratit ćemo se natrag u Excel iz Power Queryja s naredbom Zatvori i učitaj – Zatvori i učitaj u… (Zatvori i učitaj — Zatvori i učitaj u...):
… i u prozoru koji se pojavi odaberite Samo stvorite vezu (Samo veza).
Ponovite isto s novim cjenikom.
Kreirajmo sada treći upit koji će kombinirati i usporediti podatke iz prethodna dva. Da biste to učinili, odaberite u Excelu na kartici Podaci – Dohvati podatke – Kombiniraj zahtjeve – Kombiniraj (Podaci — Dohvati podatke — Spoji upite — Spoji) ili pritisnite gumb Kombinirati (Sjediniti) kartica Upit snage.
U prozoru za spajanje na padajućim listama odaberite naše tablice, odaberite stupce u kojima se nalaze nazivi robe te na dnu postavite način spajanja – Kompletan vanjski (Potpuni vanjski):
Nakon što kliknete OK trebala bi se pojaviti tablica od tri stupca, gdje u trećem stupcu trebate proširiti sadržaj ugniježđenih tablica pomoću dvostruke strelice u zaglavlju:
Kao rezultat toga, dobivamo spajanje podataka iz obje tablice:
Bolje je, naravno, preimenovati nazive stupaca u zaglavlju dvostrukim klikom na razumljivije:
A sada ono najzanimljivije. Idi na karticu Dodajte stupac (Dodaj stupac) i kliknite na gumb Uvjetni stupac (Uvjetni stupac). Zatim u prozor koji se otvori unesite nekoliko testnih uvjeta s odgovarajućim izlaznim vrijednostima:
Ostaje kliknuti na OK i prenesite rezultirajuće izvješće u Excel koristeći isti gumb zatvorite i preuzmite (Zatvori i učitaj) kartica Naslovna (Dom):
Ljepota.
Štoviše, ako dođe do bilo kakvih promjena u cjenicima u budućnosti (redci se dodaju ili brišu, cijene se mijenjaju itd.), tada će biti dovoljno samo ažurirati naše zahtjeve tipkovničkom prečicom Ctrl+drugo+F5 ili gumbom Osvježi sve (Osvježi sve) kartica Datum (Datum).
Prozodija: Možda najljepši i najprikladniji način od svih. Pametno radi s velikim stolovima. Ne zahtijeva ručno uređivanje prilikom promjene veličine tablica.
Cons: Zahtijeva instaliranje dodatka Power Query (u programu Excel 2010-2013) ili programu Excel 2016. Imena stupaca u izvornim podacima ne smiju se mijenjati, inače ćemo dobiti pogrešku "Stupac takav i takav nije pronađen!" prilikom pokušaja ažuriranja upita.
- Kako prikupiti podatke iz svih Excel datoteka u određenoj mapi pomoću Power Queryja
- Kako pronaći podudaranja između dva popisa u Excelu
- Spajanje dva popisa bez duplikata