Vrtilna tabela v več obsegih podatkov

Formulacija problema

Vrtilne tabele so eno najbolj neverjetnih orodij v Excelu. Toda do zdaj na žalost nobena od različic Excela ne more narediti tako preproste in potrebne stvari sproti, kot je sestavljanje povzetka za več začetnih obsegov podatkov, ki se nahajajo na primer na različnih listih ali v različnih tabelah:

Preden začnemo, razjasnimo nekaj točk. A priori menim, da so v naših podatkih izpolnjeni naslednji pogoji:

• Tabele imajo lahko poljubno število vrstic s poljubnimi podatki, vendar morajo imeti enako glavo.
• Na listih z izvornimi tabelami ne sme biti dodatnih podatkov. En list – ena tabela. Za nadzor vam svetujem uporabo bližnjice na tipkovnici Ctrl+konec, ki vas premakne v zadnjo uporabljeno celico na delovnem listu. V idealnem primeru bi morala biti to zadnja celica v podatkovni tabeli. Če ob kliku na Ctrl+konec katera koli prazna celica na desni ali pod tabelo je označena – izbrišite te prazne stolpce na desni ali vrstice pod tabelo za tabelo in shranite datoteko.

1. način: sestavite tabele za vrtilno tabelo z uporabo Power Query

Od različice 2010 za Excel je na voljo brezplačen dodatek Power Query, ki lahko zbira in preoblikuje vse podatke ter jih nato ponudi kot vir za izdelavo vrtilne tabele. Reševanje našega problema s pomočjo tega dodatka sploh ni težko.

Najprej ustvarimo novo prazno datoteko v Excelu – v njej bo potekalo sestavljanje in nato bo v njej ustvarjena vrtilna tabela.

Nato na zavihku datum (če imate Excel 2016 ali novejši) ali na zavihku poizvedba o moči (če imate Excel 2010-2013) izberite ukaz Ustvari poizvedbo – Iz datoteke – Excel (Pridobi podatke — Iz datoteke — Excel) in določite izvorno datoteko s tabelami, ki jih želite zbrati:

V oknu, ki se prikaže, izberite poljuben list (ni važno kateri) in pritisnite spodnji gumb Spreminjanje (Uredi):

Okno Power Query Query Editor bi se moralo odpreti na vrhu Excela. Na desni strani okna na plošči Parametri zahteve izbrišite vse samodejno ustvarjene korake razen prvega – vir (Vir):

Zdaj vidimo splošen seznam vseh listov. Če so poleg podatkovnih listov v datoteki še kakšni drugi stranski listi, potem je naša naloga v tem koraku izbrati samo tiste liste, iz katerih je treba naložiti informacije, vse druge pa izključiti s filtrom v glavi tabele:

Izbriši vse stolpce razen stolpca datumtako, da z desno miškino tipko kliknete naslov stolpca in izberete Izbrišite druge stolpce (Odstrani drugi stolpci):

Nato lahko razširite vsebino zbranih tabel s klikom na dvojno puščico na vrhu stolpca (potrditveno polje Kot predpono uporabite izvirno ime stolpca lahko izklopiš):

Če ste vse naredili pravilno, bi morali na tej točki videti vsebino vseh tabel, zbranih eno pod drugo:

Ostaja še dvigniti prvo vrstico do glave tabele z gumbom Prvo vrstico uporabite kot glave (Uporabi prvo vrstico kot glave) tab Domov (Domov) in odstranite podvojene glave tabel iz podatkov s filtrom:

Shranite vse narejeno z ukazom Zapri in naloži – Zapri in naloži v… (Zapri & naloži — Zapri in naloži v ...) tab Domov (Domov)in v oknu, ki se odpre, izberite možnost Samo povezava (Samo povezava):

Vse. Ostaja le sestaviti povzetek. Če želite to narediti, pojdite na zavihek Vstavi – vrtilna tabela (Vstavi — vrtilna tabela), izberite možnost Uporabite zunanji vir podatkov (Uporabi zunanji vir podatkov)in nato s klikom na gumb Izberite povezavo, naša zahteva. Nadaljnje ustvarjanje in konfiguracija vrtišča poteka na povsem standarden način, tako da povlečete polja, ki jih potrebujemo, v območje vrstic, stolpcev in vrednosti:

Če se bodo izvorni podatki v prihodnosti spremenili ali bo dodanih še nekaj listov trgovine, bo dovolj, da posodobimo poizvedbo in naš povzetek z ukazom Osveži vse tab datum (Podatki — Osveži vse).

Metoda 2. Tabele združimo z ukazom UNION SQL v makro

Drugo rešitev našega problema predstavlja ta makro, ki ustvari nabor podatkov (predpomnilnik) za vrtilno tabelo z uporabo ukaza UNITY SQL poizvedbeni jezik. Ta ukaz združuje tabele iz vseh navedenih v matriki SheetNames listov knjige v eno podatkovno tabelo. To pomeni, da namesto fizičnega kopiranja in lepljenja razponov iz različnih listov na enega, naredimo isto v RAM-u računalnika. Nato makro doda nov list z danim imenom (spremenljivka ResultSheetName) in na njem ustvari popoln (!) povzetek na podlagi zbranega predpomnilnika.

Če želite uporabiti makro, uporabite gumb Visual Basic na zavihku razvijalec (Razvijalec) ali bližnjico na tipkovnici druga+F11. Nato skozi meni vstavimo nov prazen modul Vstavi – Modul in tja kopirajte naslednjo kodo:

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, kjer bo prikazan nastali vrtilec ResultSheetName = "Pivot" 'matrika lista imena z izvornimi tabelami SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'tvorimo predpomnilnik za tabele iz listov iz SheetsNames With 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 ) Konec z 'ponovno ustvarite list za prikaz nastale vrtilne tabele Ob napaki Nadaljuj Naslednja aplikacija.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'prikaži ustvarjeni povzetek predpomnilnika na tem listu Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Nothing Range("A3").Izberite End With End Sub    

Končni makro lahko nato zaženete z bližnjico na tipkovnici druga+F8 ali gumb Makri na zavihku razvijalec (Razvijalec — Makri).

Slabosti tega pristopa:

• Podatki niso posodobljeni, ker predpomnilnik nima povezave z izvornimi tabelami. Če spremenite izvorne podatke, morate znova zagnati makro in znova sestaviti povzetek.
• Pri spreminjanju števila listov je potrebno urediti kodo makra (matrika SheetNames).
  

Toda na koncu dobimo pravo polno vrtilno tabelo, zgrajeno na več razponih iz različnih listov:

Voilà!

Tehnična opomba: če se med zagonom makra prikaže napaka, kot je »Ponudnik ni registriran«, imate najverjetneje 64-bitno različico Excela ali pa je nameščena nepopolna različica Officea (brez dostopa). Če želite popraviti situacijo, zamenjajte fragment v kodi makra:

	 Ponudnik=Microsoft.Jet.OLEDB.4.0;  

za:

	Ponudnik=Microsoft.ACE.OLEDB.12.0;  

Prenesite in namestite brezplačen mehanizem za obdelavo podatkov iz Accessa z Microsoftovega spletnega mesta – Microsoft Access Database Engine 2010 Redistributable

3. način: Konsolidirajte čarovnika za vrtilne tabele iz starih različic Excela

Ta metoda je nekoliko zastarela, a vseeno vredna omembe. Formalno gledano je v vseh različicah do vključno leta 2003 obstajala možnost v čarovniku za vrtilno tabelo, da "zgradite pivot za več konsolidacijskih obsegov". Vendar pa bo poročilo, sestavljeno na ta način, na žalost le bedna podoba pravega polnega povzetka in ne podpira številnih "čipov" običajnih vrtilnih tabel:

V takšnem vrtilnem stolpcu na seznamu polj ni naslovov stolpcev, ni nastavitev prilagodljive strukture, nabor uporabljenih funkcij je omejen in na splošno vse to ni zelo podobno vrtilni tabeli. Morda je zato Microsoft od leta 2007 odstranil to funkcijo iz standardnega pogovornega okna pri ustvarjanju poročil vrtilne tabele. Zdaj je ta funkcija na voljo samo prek gumba po meri Čarovnik za vrtilno tabelo(Čarovnik za vrtilno tabelo), ki ga po želji lahko dodate v orodno vrstico za hitri dostop prek Datoteka – Možnosti – Prilagodi orodno vrstico za hitri dostop – Vsi ukazi (Datoteka — Možnosti — Prilagodi orodno vrstico za hitri dostop — Vsi ukazi):

Po kliku na dodani gumb morate v prvem koraku čarovnika izbrati ustrezno možnost:

Nato v naslednjem oknu po vrsti izberite vsak obseg in ga dodajte na splošni seznam:

Ampak, še enkrat, to ni popoln povzetek, zato od njega ne pričakujte preveč. To možnost lahko priporočam samo v zelo preprostih primerih.

• Ustvarjanje poročil z vrtilnimi tabelami
• Nastavite izračune v vrtilnih tabelah
• Kaj so makri, kako jih uporabljati, kam kopirati kodo VBA itd.
• Zbiranje podatkov iz več listov v eno (dodatek PLEX)