Zaokretna tablica u više raspona podataka

Formulacija problema

Zaokretne tablice jedan su od najčudesnijih alata u Excelu. 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 točaka. A priori, vjerujem da su sljedeći uvjeti ispunjeni u našim podacima:

• Tablice mogu imati neograničen broj redaka s bilo kojim podacima, ali moraju imati isto zaglavlje.
• Na listovima s izvornim tablicama ne bi trebalo biti dodatnih podataka. Jedan list – jedna tablica. Za kontrolu, savjetujem vam da koristite tipkovni prečac Ctrl+Kraj, koji vas premješta na posljednju korištenu ćeliju na radnom listu. U idealnom slučaju, ovo bi trebala biti zadnja ćelija u podatkovnoj tablici. Ako kada kliknete na Ctrl+Kraj bilo koja prazna ćelija desno ili ispod tablice je označena – izbrišite ove prazne stupce desno ili retke ispod tablice nakon tablice i spremite datoteku.

1. metoda: Izradite tablice za zaokretnu tablicu pomoću Power Queryja

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

Najprije napravimo novu praznu datoteku u Excelu – u njoj će se odvijati asembler, a zatim će se u njoj kreirati pivot tablica.

Zatim na tab Datum (ako imate Excel 2016 ili noviji) ili na kartici Upit snage (ako imate Excel 2010-2013) odaberite naredbu Kreiraj upit – Iz datoteke – Excel (Dohvati podatke — Iz datoteke — Excel) i navedite izvornu datoteku s tablicama koje treba prikupiti:

U prozoru koji se pojavi odaberite bilo koji list (nije bitno koji) i pritisnite gumb ispod Promijeniti (Uredi):

Prozor Power Query Query Editor trebao bi se otvoriti na vrhu programa Excel. Na desnoj strani prozora na ploči Parametri zahtjeva izbrišite sve automatski stvorene korake osim prvog – izvor (Izvor):

Sada vidimo opći popis svih listova. Ako osim podatkovnih listova u datoteci postoje još neki popratni listovi, tada je naš zadatak u ovom koraku odabrati samo one listove s kojih je potrebno učitati podatke, isključujući sve ostale pomoću filtra u zaglavlju tablice:

Izbriši sve stupce osim stupca Datumdesnim pritiskom na naslov stupca i odabirom Izbrišite ostale stupce (Ukloniti drugi stupci):

Zatim možete proširiti sadržaj prikupljenih tablica klikom na dvostruku strelicu na vrhu stupca (potvrdni okvir Koristite izvorni naziv stupca kao prefiks možete ga isključiti):

Ako ste sve učinili ispravno, tada biste u ovom trenutku trebali vidjeti sadržaj svih tablica prikupljenih jednu ispod druge:

Ostaje gumbom podići prvi red do zaglavlja tablice Koristite prvi redak kao zaglavlja (Koristite prvi red kao zaglavlja) kartica Naslovna (Dom) i uklonite duplicirana zaglavlja tablice iz podataka pomoću filtra:

Spremite sve što je učinjeno naredbom Zatvori i učitaj – Zatvori i učitaj u… (Zatvori i učitaj — Zatvori i učitaj u...) kartica Naslovna (Dom), te u prozoru koji se otvori odaberite opciju Samo veza (Samo veza):

Sve. Ostaje samo izgraditi sažetak. Da biste to učinili, idite na karticu Umetanje – zaokretna tablica (Umetni — zaokretna tablica), odaberite opciju Koristite vanjski izvor podataka (Koristite vanjski izvor podataka)a zatim klikom na gumb Odaberite vezu, naš zahtjev. Daljnja izrada i konfiguracija pivota odvija se na potpuno standardan način povlačenjem polja koja su nam potrebna u područje redaka, 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 kartica Datum (Podaci — Osvježi sve).

Metoda 2. Ujedinjujemo tablice naredbom UNION SQL u makronaredbi

Drugo rješenje našeg problema predstavlja ovaj makro, koji stvara skup podataka (cache) za zaokretnu tablicu pomoću naredbe JEDINSTVO SQL upitni jezik. Ova naredba kombinira tablice iz svih navedenih u nizu Nazivi listova listova knjige u jednu podatkovnu tablicu. To jest, umjesto fizičkog kopiranja i lijepljenja raspona s različitih listova na jedan, mi radimo isto u RAM-u računala. Zatim makronaredba dodaje novi list s danim imenom (varijabla ResultSheetName) i na njemu stvara puni (!) sažetak na temelju prikupljene predmemorije.

Za korištenje makronaredbe upotrijebite gumb Visual Basic na kartici razvijač (Programer) ili tipkovni prečac drugo+F11. Zatim kroz izbornik ubacujemo novi prazan modul 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 'naziv lista gdje će se prikazati rezultirajući pivot ResultSheetName = "Pivot" 'niz lista imena s izvornim tablicama SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'formiramo predmemoriju za tablice iz listova iz SheetsNames s ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Za i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set 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 ) Završite s 'ponovno stvorite list za prikaz dobivene zaokretne tablice Na pogrešku Nastavi Sljedeća aplikacija.DisplayAlerts = False Radni listovi(ResultSheetName).Delete Postavi wsPivot = Radni listovi.Dodaj wsPivo t. Name = ResultSheetName 'prikaži generirani sažetak predmemorije na ovom listu Postavi objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Postavi objPivotCache.Recordset = objRS Postavi objRS = Ništa s wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Postavi objPivotCache = Nothing Range("A3"). Odaberite End With End Sub    

Gotovu makronaredbu tada možete pokrenuti tipkovničkim prečacem drugo+F8 ili gumb Makronaredbe na kartici razvijač (Razvojni programer — Makronaredbe).

Nedostaci ovog pristupa:

• Podaci se ne ažuriraju jer predmemorija nema veze s izvornim tablicama. Ako promijenite izvorne podatke, morate ponovo pokrenuti makronaredbu i ponovo izgraditi sažetak.
• Prilikom promjene broja listova potrebno je urediti makro kod (niz Nazivi listova).
  

Ali na kraju dobivamo pravu punopravnu stožernu tablicu, izgrađenu na nekoliko raspona s različitih listova:

Evo ga!

Tehnička napomena: ako tijekom pokretanja makronaredbe dobijete pogrešku poput "Dobavljač nije registriran", najvjerojatnije imate 64-bitnu verziju programa Excel ili je instalirana nepotpuna verzija sustava Office (bez pristupa). Da biste popravili situaciju, zamijenite fragment u kodu makronaredbe:

	 Dobavljač=Microsoft.Jet.OLEDB.4.0;  

na:

	Dobavljač=Microsoft.ACE.OLEDB.12.0;  

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

Metoda 3: Konsolidirajte čarobnjaka zaokretne tablice iz starih verzija programa Excel

Ova metoda je malo zastarjela, ali ipak vrijedna spomena. Formalno govoreći, u svim verzijama do i uključujući 2003., postojala je opcija u čarobnjaku zaokretne tablice za "izgradnju zaokretne točke za nekoliko raspona konsolidacije". Međutim, izvješće sastavljeno na ovaj način, nažalost, bit će samo jadan izgled pravog punopravnog sažetka i ne podržava mnoge "čipove" konvencionalnih zaokretnih tablica:

U takvom stožeru nema naslova stupaca na popisu polja, nema postavki fleksibilne strukture, ograničen je skup korištenih funkcija i, općenito, sve to nije baš slično stožernoj tablici. Možda je zato, počevši od 2007. godine, Microsoft uklonio ovu funkciju iz standardnog dijaloškog okvira prilikom izrade izvješća zaokretne tablice. Sada je ova značajka dostupna samo putem prilagođenog gumba Čarobnjak zaokretne tablice(Čarobnjak zaokretne tablice), koji se po želji može dodati na alatnu traku za brzi pristup putem Datoteka – Mogućnosti – Prilagodite alatnu traku za brzi pristup – Sve naredbe (Datoteka — Opcije — Prilagodite alatnu traku za brzi pristup — Sve naredbe):

Nakon što kliknete na dodani gumb, potrebno je odabrati odgovarajuću opciju u prvom koraku čarobnjaka:

Zatim u sljedećem prozoru redom odaberite svaki raspon i dodajte ga na opći popis:

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

• Izrada izvješća sa zaokretnim tablicama
• Postavite izračune u zaokretnim tablicama
• Što su makronaredbe, kako ih koristiti, gdje kopirati VBA kod itd.
• Prikupljanje podataka s više listova na jedan (PLEX dodatak)