sadržaj
Imamo dvije tabele (npr. stara i nova verzija cjenovnika), koje trebamo uporediti i brzo pronaći razlike:
Odmah je jasno da je nešto dodato na novi cjenovnik (hurme, bijeli luk…), nešto je nestalo (kupine, maline…), cijene neke robe (smokve, dinje…). Morate brzo pronaći i prikazati sve ove promjene.
Za bilo koji 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 pored nove, a zatim uhvatite razlike
- spojite dvije liste u jednu, a zatim napravite pivot tablicu na osnovu nje, gdje će razlike biti jasno vidljive
- koristite Power Query dodatak za Excel
Uzmimo ih sve redom.
Metoda 1. Poređenje tablica s funkcijom VLOOKUP
Ako vam je ova divna funkcija potpuno nepoznata, prvo pogledajte ovdje i pročitajte ili pogledajte video tutorijal o njoj – uštedite sebi nekoliko godina života.
Obično se ova funkcija koristi za povlačenje podataka iz jedne tablice u drugu uparujući neki zajednički parametar. U ovom slučaju, koristit ćemo ga da gurnemo stare cijene u novu cijenu:
Proizvodi za koje se ispostavila greška #N/A nisu na staroj listi, odnosno dodani. Jasno su vidljive i promjene cijena.
pros ovaj metod: jednostavan i jasan, "klasika žanra", kako kažu. Radi u bilo kojoj verziji Excel-a.
Cons je također tu. Za traženje proizvoda dodanih na novi cjenovnik, morat ćete uraditi isti postupak u suprotnom smjeru, odnosno povući nove cijene na staru cijenu uz pomoć VLOOKUP-a. Ako se sutra promijene veličine tabela, tada će se formule morati prilagoditi. Pa, i na zaista velikim stolovima (> 100 hiljada redova), sva ova sreća će se pristojno usporiti.
Metoda 2: Poređenje tabela koristeći pivot
Kopirajmo naše tabele jednu ispod druge, dodajući kolonu sa nazivom cjenovnika, kako bi kasnije shvatili iz koje liste koji red:
Sada ćemo, na osnovu kreirane tabele, kreirati rezime Umetanje – zaokretna tabela (Insert — Pivot Table). Hajde da bacimo polje Proizvod na područje linija, polje cijena na područje kolone i polje Цena u rasponu:
Kao što vidite, pivot tabela će automatski generisati opštu listu svih proizvoda sa starog i novog cjenika (bez ponavljanja!) i sortirati proizvode po abecednom redu. Možete jasno vidjeti dodane proizvode (nemaju staru cijenu), uklonjene proizvode (nemaju novu cijenu) i promjene cijene, ako ih ima.
Ukupni zbrojevi u takvoj tabeli nemaju smisla i mogu se onemogućiti na kartici Konstruktor – Ukupni zbrojevi – Onemogući za redove i stupce (Dizajn — ukupni zbrojevi).
Ako se cijene mijenjaju (ali ne i količina robe!), dovoljno je jednostavno ažurirati kreirani sažetak desnim klikom na njega – osvježiti.
pros: 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 kolonu sa nazivom cjenovnika. Ako se veličine stolova promijene, onda morate sve raditi iznova.
Metoda 3: Poređenje tabela sa Power Queryjem
Power Query je besplatni dodatak za Microsoft Excel koji vam omogućava da učitate podatke u Excel iz gotovo bilo kojeg izvora, a zatim ih transformirate na bilo koji željeni način. U programu Excel 2016, ovaj dodatak je već ugrađen prema zadanim postavkama na kartici podaci (Podaci), a za Excel 2010-2013 morate ga zasebno preuzeti sa Microsoft web stranice i instalirati – nabavite novu karticu power query.
Prije učitavanja naših cjenovnika u Power Query, prvo ih morate pretvoriti u pametne tablice. Da biste to učinili, odaberite raspon s podacima i pritisnite kombinaciju na tipkovnici Ctrl+T ili odaberite karticu na traci Početna – Formatirajte kao tabelu (Početna — Format kao tabela). Nazivi kreiranih tabela mogu se korigovati na kartici konstruktor (Ostaviću standard Tabela 1 и Tabela 2, koji se dobijaju podrazumevano).
Učitajte staru cijenu u Power Query pomoću dugmeta Iz tabele/opseg (Iz tabele/dometa) sa kartice podaci (Datum) ili sa kartice power query (ovisno o verziji Excel-a). Nakon učitavanja, vratit ćemo se u Excel iz Power Queryja sa naredbom Zatvori i učitaj – Zatvori i učitaj u… (Zatvori i učitaj — Zatvori i učitaj za…):
… i u prozoru koji se pojavi odaberite Samo stvorite vezu (Samo veza).
Ponovite isto sa novim cjenovnikom.
Sada kreirajmo treći upit koji će kombinovati i upoređivati podatke iz prethodna dva. Da biste to učinili, odaberite u Excelu na kartici Podaci – Dobijte podatke – Kombinirajte zahtjeve – Kombinirajte (Podaci — Dobijte podatke — Spojite upite — Spojite) ili pritisnite dugme Kombinujte (Spajanje) tabulator power query.
U prozoru za spajanje izaberite naše tabele u padajućim listama, izaberite kolone sa nazivima robe u njima i na dnu podesite metod spajanja – Kompletna eksterna (puni vanjski):
Posle klika na OK trebala bi se pojaviti tabela od tri kolone, gdje u trećoj koloni trebate proširiti sadržaj ugniježđenih tabela pomoću dvostruke strelice u zaglavlju:
Kao rezultat, dobijamo spajanje podataka iz obje tabele:
Bolje je, naravno, preimenovati nazive stupaca u zaglavlju dvostrukim klikom na razumljivije:
A sada najzanimljivije. Idi na karticu Dodajte stupac (Dodaj kolonu) i kliknite na dugme Uslovna kolona (Uslovna kolona). A zatim u prozoru koji se otvori unesite nekoliko uslova testiranja sa odgovarajućim izlaznim vrijednostima:
Ostaje kliknuti na OK i prenesite rezultirajući izvještaj u Excel koristeći isto dugme zatvorite i preuzmite (Zatvori i učitaj) tabulator POČETNA (Dom):
Ljepota.
Štaviše, ako u budućnosti dođe do bilo kakvih promjena u cjenovnicima (dodavanje ili brisanje redova, promjena cijena itd.), tada će biti dovoljno samo ažurirati naše zahtjeve prečicom na tastaturi Ctrl+alt+F5 ili dugmetom Osvježi sve (Osvježi sve) tabulator podaci (Datum).
pros: Možda najljepši i najpovoljniji način od svih. Pametno radi sa velikim stolovima. Ne zahtijeva ručne izmjene prilikom promjene veličine tablica.
Cons: Zahteva da se instalira Power Query dodatak (u Excel 2010-2013) ili Excel 2016. Nazivi kolona u izvornim podacima se ne smiju mijenjati, inače ćemo dobiti grešku „Tava i takva kolona nije pronađena!“ kada pokušavate ažurirati upit.
- Kako prikupiti podatke iz svih Excel datoteka u datoj mapi koristeći Power Query
- Kako pronaći podudaranja između dvije liste u Excelu
- Spajanje dvije liste bez duplikata