Poređenje dve tabele

Imamo dvije tabele (npr. stara i nova verzija cjenovnika), koje trebamo uporediti i brzo pronaći razlike:

Poređenje dve tabele

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:

Poređenje dve tabele

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:

Poređenje dve tabele

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:

Poređenje dve tabele

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…):

Poređenje dve tabele

… 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):

Poređenje dve tabele

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:

Poređenje dve tabele

Kao rezultat, dobijamo spajanje podataka iz obje tabele:

Poređenje dve tabele

Bolje je, naravno, preimenovati nazive stupaca u zaglavlju dvostrukim klikom na razumljivije:

Poređenje dve tabele

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:

Poređenje dve tabele

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):

Poređenje dve tabele

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

Ostavite odgovor