Zaokretna tabela u više raspona podataka

Formulacija problema

Pivot tabele su jedan od najneverovatnijih alata u Excel-u. Ali do sada, nažalost, nijedna od verzija Excela ne može učiniti tako jednostavnu i potrebnu stvar u hodu kao što je izgradnja sažetka za nekoliko početnih raspona podataka koji se nalaze, na primjer, na različitim listovima ili u različitim tablicama:

Prije nego što počnemo, razjasnimo nekoliko stvari. A priori smatram da su u našim podacima ispunjeni sljedeći uslovi:

• Tabele mogu imati bilo koji broj redova sa bilo kojim podacima, ali moraju imati isto zaglavlje.
• Na listovima sa izvornim tabelama ne bi trebalo biti dodatnih podataka. Jedan list – jedan sto. Za kontrolu, savjetujem vam da koristite prečicu na tastaturi Ctrl+kraj, što vas pomera na poslednju korišćenu ćeliju na radnom listu. U idealnom slučaju, ovo bi trebala biti posljednja ćelija u tabeli podataka. Ako kada kliknete na Ctrl+kraj bilo koja prazna ćelija desno ili ispod tabele je istaknuta – izbrišite ove prazne kolone desno ili redove ispod tabele posle tabele i sačuvajte datoteku.

Metoda 1: Napravite tabele za pivot koristeći Power Query

Počevši od verzije 2010 za Excel, postoji besplatni dodatak za Power Query koji može prikupiti i transformirati sve podatke, a zatim ih dati kao izvor za pravljenje pivot tablice. Rješavanje našeg problema uz pomoć ovog dodatka uopće nije teško.

Prvo, napravimo novu praznu datoteku u Excelu – u njoj će se odvijati montaža, a zatim će se u njoj kreirati pivot tablica.

Zatim na kartici podaci (ako imate Excel 2016 ili noviji) ili na kartici power query (ako imate Excel 2010-2013) odaberite naredbu Kreirajte upit – Iz datoteke – Excel (Preuzmi podatke — Iz datoteke — Excel) i navedite izvorni fajl sa tabelama koje treba prikupiti:

U prozoru koji se pojavi odaberite bilo koji list (nije važno koji) i pritisnite dugme ispod promjena (Edit):

Prozor Power Query Query Editor trebao bi se otvoriti na vrhu Excel-a. Na desnoj strani prozora na panelu Parametri zahtjeva izbrišite sve automatski kreirane korake osim prvog – izvor (Izvor):

Sada vidimo opštu listu svih listova. Ako pored listova sa podacima postoje i neki drugi bočni listovi u datoteci, tada je naš zadatak u ovom koraku odabrati samo one listove iz kojih je potrebno učitati informacije, isključujući sve ostale koristeći filter u zaglavlju tablice:

Izbrišite sve kolone osim kolone podacidesnim klikom na naslov kolone i odabirom Izbrišite druge kolone (Ukloni druge kolone):

Zatim možete proširiti sadržaj prikupljenih tabela klikom na dvostruku strelicu na vrhu kolone (potvrdni okvir Koristite originalno ime kolone kao prefiks možete ga isključiti):

Ako ste sve uradili ispravno, tada biste u ovom trenutku trebali vidjeti sadržaj svih tabela prikupljenih jedna ispod druge:

Ostaje podići prvi red do zaglavlja tabele pomoću dugmeta Koristite prvi red kao zaglavlja (Koristite prvi red kao zaglavlja) tabulator Početna (Dom) i uklonite duplirana zaglavlja tablice iz podataka pomoću filtera:

Sačuvajte sve što ste uradili komandom Zatvori i učitaj – Zatvori i učitaj u… (Zatvori i učitaj — Zatvori i učitaj do…) tabulator Početna (Dom), a u prozoru koji se otvori odaberite opciju Samo veza (Samo veza):

Sve. Ostaje samo da se napravi sažetak. Da biste to učinili, idite na karticu Umetanje – zaokretna tabela (Insert — Pivot Table), odaberite opciju Koristite eksterni izvor podataka (Koristite eksterni izvor podataka)a zatim klikom na dugme Odaberite vezu, naš zahtjev. Dalje kreiranje i konfiguracija pivota odvija se na potpuno standardan način povlačenjem polja koja su nam potrebna u područje redova, stupaca i vrijednosti:

Ako se izvorni podaci promijene u budućnosti ili se doda još nekoliko listova trgovine, tada će biti dovoljno ažurirati upit i naš sažetak pomoću naredbe Osvježi sve tabulator podaci (Podaci — Osvježi sve).

Metoda 2. Ujedinjujemo tabele sa UNION SQL naredbom u makro

Još jedno rješenje našeg problema predstavlja ovaj makro, koji kreira skup podataka (cache) za pivot tablicu koristeći naredbu UNITY SQL upitni jezik. Ova komanda kombinuje tabele iz svih navedenih u nizu SheetNames listove knjige u jednu tabelu sa podacima. To jest, umjesto fizičkog kopiranja i lijepljenja raspona od različitih listova do jednog, mi radimo isto u RAM-u računala. Zatim makro dodaje novi list sa datim imenom (promenljiva ResultSheetName) i kreira punopravni (!) sažetak na osnovu prikupljene keš memorije.

Da biste koristili makro, koristite dugme Visual Basic na kartici Developer (programer) ili prečica na tastaturi alt+F11. Zatim ubacujemo novi prazan modul kroz meni Umetak – Modul i tamo kopirajte sljedeći kod:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'ime lista gdje će se prikazati rezultirajući pivotNa sheet'anPheet" imena sa izvornim tabelama SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'formiramo keš za tabele iz listova iz SheetsNames sa ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Za i = LBound (Imena listova) Za UBound(Imena listova) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Dalje i Postavite objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Kraj sa 'ponovo kreirajte list da prikažete rezultujuću stožernu tabelu. U slučaju greške Nastavite sledeću aplikaciju.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'prikaži generirani sažetak keš memorije na ovom listu Postavi objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Postavi objPivotCache.Recordset = objRS Postavi objRS = Ništa Sa wsPivot objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Postavi objPivotCache.Recordset = objRS Postavi objRS = Ništa Sa wsPivot objPivotCache=TablePivotCache. objPivotCache = Opseg ničega("A3"). Izaberite Kraj sa završnom podlogom    

Gotov makro se tada može pokrenuti pomoću prečice na tastaturi alt+F8 ili dugme Makroi na kartici Developer (Programer — Makroi).

Nedostaci ovog pristupa:

• Podaci se ne ažuriraju jer keš memorija nema vezu s izvornim tablicama. Ako promijenite izvorne podatke, morate ponovo pokrenuti makro i ponovo napraviti sažetak.
• Prilikom promjene broja listova potrebno je urediti kod makroa (niz SheetNames).
  

Ali na kraju dobijamo pravi punopravni stožer, izgrađen na nekoliko raspona iz različitih listova:

Voilà!

tehnička napomena: ako dobijete grešku poput „Dobavljač nije registrovan“ prilikom pokretanja makroa, onda najvjerovatnije imate 64-bitnu verziju Excel-a ili je instalirana nepotpuna verzija Officea (nema pristupa). Da popravite situaciju, zamijenite fragment u kodu makroa:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

na:

	Provider=Microsoft.ACE.OLEDB.12.0;  

I preuzmite i instalirajte besplatni mehanizam za obradu podataka iz Accessa sa Microsoftove web stranice – Microsoft Access Database Engine 2010 Redistributable

Metoda 3: Čarobnjak za konsolidaciju zaokretne tabele iz starih verzija Excel-a

Ova metoda je malo zastarjela, ali ipak vrijedi spomenuti. Formalno govoreći, u svim verzijama do i uključujući 2003., postojala je opcija u čarobnjaku za zaokretnu tabelu da se „izgradi pivot za nekoliko opsega konsolidacije“. Međutim, ovako konstruiran izvještaj, nažalost, bit će samo jadan privid pravog punog sažetka i ne podržava mnoge „čipove“ konvencionalnih zaokretnih tablica:

U takvom pivot-u nema naslova kolona u listi polja, nema fleksibilne postavke strukture, skup funkcija koje se koriste je ograničen i, općenito, sve ovo nije baš slično pivot tablici. Možda je to razlog zašto je, počevši od 2007. godine, Microsoft uklonio ovu funkciju iz standardnog dijaloga prilikom kreiranja izveštaja pivot tabele. Sada je ova funkcija dostupna samo preko prilagođenog dugmeta Čarobnjak za zaokretnu tablicu(Čarobnjak za zaokretnu tablicu), koji se po želji može dodati na traku sa alatkama za brzi pristup putem Datoteka – Opcije – Prilagodite traku sa alatkama za brzi pristup – Sve naredbe (Datoteka — Opcije — Prilagodite traku sa alatkama za brzi pristup — Sve naredbe):

Nakon što kliknete na dugme za dodavanje, potrebno je da izaberete odgovarajuću opciju u prvom koraku čarobnjaka:

A zatim u sljedećem prozoru odaberite svaki raspon redom i dodajte ga na opću listu:

Ali, opet, ovo nije potpuni sažetak, tako da ne očekujte previše od njega. Ovu opciju mogu preporučiti samo u vrlo jednostavnim slučajevima.

• Kreiranje izvještaja sa zaokretnim tabelama
• Postavite proračune u zaokretnim tabelama
• Šta su makroi, kako ih koristiti, gdje kopirati VBA kod, itd.
• Prikupljanje podataka iz više listova u jedan (PLEX dodatak)