Nemate Power Pivot? Nema veze. Većina programa Power Pivot ugrađena je u Excel 2013, a još više u Excel 2016. Danas se naš savjet tvrtke Ash pridružuje tablicama u zaokretnoj tablici.
Svake srijede, sedam tjedana, dajem jedan od omiljenih savjeta od Ash Sharme. Ash je voditelj proizvoda u Excel timu. Njegov vam tim donosi pivot tablice i mnoge druge dobre stvari. Danas je Ashina omiljena značajka pridruživanje više skupova podataka pomoću odnosa i podatkovnog modela.
Recimo da vam IT odjel daje skup podataka prikazan u stupcima A: D. Postoje polja za kupca i tržište. Morate kombinirati određena tržišta u regije. Svaki kupac pripada nekom sektoru. Regija i sektor nisu u izvornim podacima, ali imate tablice za pretraživanje kako biste pružili ove informacije.
Uobičajeno bi bilo izravnati podatke pomoću VLOOKUP-a za izvlačenje podataka iz narančaste i žute tablice u plavu tablicu. No budući da se ključno polje ne nalazi na lijevoj strani svake tablice, morat ćete se prebaciti na INDEX i MATCH ili ponovno preurediti tablice pretraživanja.
Počevši od programa Excel 2013, tablice pretraživanja možete ostaviti tamo gdje jesu i kombinirati ih u samom izvješću o zaokretnoj tablici.
Da bi ova tehnika funkcionirala, sve tri tablice moraju biti oblikovane kao tablica. Odaberite po jednu ćeliju u svakom skupu podataka i odaberite Početna, Oblikuj kao tablicu ili pritisnite Ctrl + T. Tri će se tablice u početku nazivati Tablica1, Tablica2 i Tablica3. Koristim karticu Dizajn alata za tablice na vrpci i preimenujem svaku tablicu. Također mijenjam boju svakog stola. U ovom se primjeru plava tablica naziva Podaci. Narančasta tablica je RegionTable. Žuta tablica je SectorTable.
Bilješka
Neki će vam reći da biste trebali koristiti geeky imena poput Fact, TblSector i TblRegion. Ako vas netko ovako gnjavi, samo mu ukradite džepni zaštitnik i javite mu da više volite imena koja zvuče na engleskom.
Da biste preimenovali tablicu, upišite novo ime u okvir s lijeve strane kartice Dizajn alata za tablice. Imena tablica ne bi trebala imati razmake.
Nakon što su definirane tri tablice, idite na karticu Podaci i kliknite na Odnosi.
U dijaloškom okviru Upravljanje vezama kliknite Novo. U dijaloškom okviru Stvori odnos navedite da je polje Korisnik tablice podataka povezano s korisničkim poljem Sektorske tablice. Kliknite U redu.
Definirajte još jedan novi odnos između polja Market u poljima Data i RegionTable. Nakon definiranja obje veze, vidjet ćete ih u dijaloškom okviru Upravljanje vezama.
Čestitamo: upravo ste ugradili model podataka u svoju radnu knjigu. Vrijeme je za izgradnju pivot tablice.
Odaberite praznu ćeliju u kojoj želite da se prikazuje zaokretna tablica. Prema zadanim postavkama, dijaloški okvir Stvori zaokretnu tablicu će odabrati Upotrijebi model podataka ove radne knjige. Mjesto zaokretne tablice prema zadanim postavkama ćelije koju ste odabrali. Kliknite U redu.
Na popisu polja zaokretne tablice naći će se sve tri tablice. Upotrijebite trokut s lijeve strane tablice da biste proširili naziv tablice kako bi vam prikazali polja.
Proširite tablicu podataka. Odaberite polje Prihod. Automatski će se premjestiti u područje Vrijednosti. Proširite sektorsku tablicu. Odaberite polje Sector. Premjestit će se u područje Redova. Proširite RegionTable. Povucite polje Regija na područje Stupci. Sada ćete imati stožernu tablicu koja sažima podatke iz tri tablice.
Bilješka
U svakoj knjizi koju sam napisao prije, koristim drugačiju tehniku za izgradnju ovog izvještaja. Nakon definiranja tri tablice, biram ćeliju A1 i Umetak, zaokretnu tablicu. Označio sam okvir Dodaj ove podatke u model podataka. Na popisu polja zaokretne tablice odaberite Sve s vrha popisa. Odaberite polja za izvješće, a zatim definirajte odnose nakon činjenice. Gore opisana tehnika djeluje uglađenije i zapravo uključuje malo planiranja unaprijed. Ljudi koji u svom VBA kodu koriste Option Explicit sigurno bi voljeli ovu metodu.
Odnosi u podatkovnom modelu čine da se Excel više osjeća kao Access ili SQL Server, ali uz svu dobrotu Excela.
Volim pitati Excel tim za njihove omiljene značajke. Svake srijede podijelit ću jedan od njihovih odgovora. Zahvaljujemo Ash Sharmi na pružanju ove ideje.
Excel misao dana
Pitao sam svoje prijatelje Excel Master za savjet o Excelu. Današnja misao za razmišljanje:
"Ne traži ako si u vezi"
John Michaloudis
