Kontingenčná tabuľka naprieč viacerými rozsahmi údajov

Formulácia problému

Kontingenčné tabuľky sú jedným z najúžasnejších nástrojov v Exceli. Žiaľ, zatiaľ žiadna z verzií Excelu nedokáže za chodu urobiť takú jednoduchú a nevyhnutnú vec, ako je vytvorenie súhrnu pre niekoľko počiatočných rozsahov údajov umiestnených napríklad na rôznych hárkoch alebo v rôznych tabuľkách:

Skôr ako začneme, vyjasnime si niekoľko bodov. A priori sa domnievam, že naše údaje spĺňajú nasledujúce podmienky:

• Tabuľky môžu mať ľubovoľný počet riadkov s ľubovoľnými údajmi, ale musia mať rovnakú hlavičku.
• Na hárkoch so zdrojovými tabuľkami by nemali byť žiadne ďalšie údaje. Jeden list – jedna tabuľka. Na ovládanie vám odporúčam použiť klávesovú skratku ctrl+Koniec, čím sa presuniete do poslednej použitej bunky v hárku. V ideálnom prípade by to mala byť posledná bunka v tabuľke údajov. Ak keď kliknete na ctrl+Koniec zvýrazní sa ľubovoľná prázdna bunka vpravo alebo pod tabuľkou – vymažte tieto prázdne stĺpce vpravo alebo riadky pod tabuľkou za tabuľkou a súbor uložte.

Metóda 1: Vytvorte tabuľky pre kontingenčný bod pomocou Power Query

Od verzie 2010 pre Excel je k dispozícii bezplatný doplnok Power Query, ktorý dokáže zhromažďovať a transformovať akékoľvek údaje a potom ich poskytnúť ako zdroj na vytvorenie kontingenčnej tabuľky. Vyriešenie nášho problému pomocou tohto doplnku nie je vôbec ťažké.

Najprv si vytvorme nový prázdny súbor v Exceli – v ňom prebehne montáž a následne sa v ňom vytvorí kontingenčná tabuľka.

Potom na karte dátum (ak máte Excel 2016 alebo novší) alebo na karte mocenský dotaz (ak máte Excel 2010-2013) vyberte príkaz Vytvoriť dotaz – Zo súboru – Excel (Získať údaje – zo súboru – Excel) a špecifikujte zdrojový súbor s tabuľkami, ktoré sa majú zhromaždiť:

V zobrazenom okne vyberte ľubovoľný hárok (nezáleží na tom, ktorý) a stlačte tlačidlo nižšie Zmena (Edit):

V hornej časti Excelu by sa malo otvoriť okno Power Query Query Editor. Na pravej strane okna na paneli Parametre požiadavky odstrániť všetky automaticky vytvorené kroky okrem prvého – zdroj (Zdroj):

Teraz vidíme všeobecný zoznam všetkých listov. Ak sú v súbore okrem údajových listov aj ďalšie bočné listy, potom je v tomto kroku našou úlohou vybrať iba tie listy, z ktorých je potrebné načítať informácie, okrem všetkých ostatných pomocou filtra v hlavičke tabuľky:

Odstráňte všetky stĺpce okrem stĺpca dátumkliknutím pravým tlačidlom myši na hlavičku stĺpca a výberom Odstráňte ostatné stĺpce (Odstrániť ďalšie stĺpce):

Potom môžete obsah zhromaždených tabuliek rozbaliť kliknutím na dvojitú šípku v hornej časti stĺpca (zaškrtávacie políčko Ako predponu použite pôvodný názov stĺpca môžete ho vypnúť):

Ak ste urobili všetko správne, v tomto bode by ste mali vidieť obsah všetkých tabuliek zhromaždených pod sebou:

Zostáva zdvihnúť prvý riadok do hlavičky tabuľky tlačidlom Použite prvý riadok ako hlavičky (Použite prvý riadok ako hlavičky) pútko Domov (Domov) a odstráňte duplicitné hlavičky tabuliek z údajov pomocou filtra:

Uložte všetko vykonané pomocou príkazu Zavrieť a načítať – Zavrieť a naložiť... (Zavrieť a načítať – Zavrieť a načítať do...) pútko Domov (Domov)a v okne, ktoré sa otvorí, vyberte možnosť Iba pripojenie (Len pripojenie):

Všetko. Zostáva len zostaviť zhrnutie. Ak to chcete urobiť, prejdite na kartu Vložiť – kontingenčná tabuľka (Vložiť – kontingenčná tabuľka), vyberte možnosť Použite externý zdroj údajov (Použiť externý zdroj údajov)a potom kliknutím na tlačidlo Vyberte pripojenie, naša žiadosť. Ďalšia tvorba a konfigurácia pivotu prebieha úplne štandardným spôsobom pretiahnutím polí, ktoré potrebujeme, do oblasti riadkov, stĺpcov a hodnôt:

Ak sa v budúcnosti zmenia zdrojové údaje alebo sa pridá niekoľko ďalších hárkov obchodu, potom bude stačiť aktualizovať dotaz a naše zhrnutie pomocou príkazu Obnoviť všetko pútko dátum (Údaje – Obnoviť všetko).

Metóda 2. Tabuľky zjednotíme príkazom UNION SQL do makra

Ďalšie riešenie nášho problému predstavuje toto makro, ktoré pomocou príkazu vytvorí množinu údajov (cache) pre kontingenčnú tabuľku UNITY SQL dotazovací jazyk. Tento príkaz kombinuje tabuľky zo všetkých uvedených v poli Názvy hárkov hárkov knihy do jednej tabuľky s údajmi. To znamená, že namiesto fyzického kopírovania a vkladania rozsahov z rôznych hárkov na jeden robíme to isté v pamäti RAM počítača. Potom makro pridá nový hárok s daným názvom (premenná ResultSheetName) a na základe nazbieranej kešky na ňom vytvorí plnohodnotný (!) sumár.

Ak chcete použiť makro, použite tlačidlo Visual Basic na karte vývojka (vývojár) alebo klávesová skratka ostatné+F11. Potom cez menu vložíme nový prázdny modul Vložiť – Modul a skopírujte tam nasledujúci kód:

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 'názov hárka, kde sa zobrazí výsledný pivot' ResultSheetName = "Pivot" mená so zdrojovými tabuľkami SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'vytvárame vyrovnávaciu pamäť pre tabuľky z listov z SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound (SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Otvoriť Join$( arSQL, " UNION ALL "), _ Join$(Array("Poskytovateľ=Microsoft.Jet.OLEDB.4.0; Zdroj údajov=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) End With 'znova vytvorte hárok na zobrazenie výslednej kontingenčnej tabuľky On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'zobraziť súhrn vygenerovanej vyrovnávacej pamäte na tomto hárku Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsAivotTable. objPivotCache = Nothing Range("A3").Vyberte End With End Sub    

Hotové makro je možné následne spustiť klávesovou skratkou ostatné+F8 alebo tlačidlo Makrá na karte vývojka (Vývojár – makrá).

Nevýhody tohto prístupu:

• Údaje sa neaktualizujú, pretože vyrovnávacia pamäť nemá žiadne spojenie so zdrojovými tabuľkami. Ak zmeníte zdrojové údaje, musíte znova spustiť makro a znova zostaviť súhrn.
• Pri zmene počtu listov je potrebné upraviť kód makra (pole Názvy hárkov).
  

Nakoniec však dostaneme skutočnú plnohodnotnú kontingenčnú tabuľku postavenú na niekoľkých radoch z rôznych listov:

Voila!

Technická poznámka: ak sa pri spustení makra zobrazí chyba ako „Poskytovateľ nie je zaregistrovaný“, potom s najväčšou pravdepodobnosťou máte nainštalovanú 64-bitovú verziu Excelu alebo neúplnú verziu balíka Office (bez prístupu). Ak chcete situáciu vyriešiť, nahraďte fragment v kóde makra:

	 Poskytovateľ=Microsoft.Jet.OLEDB.4.0;  

na:

	Poskytovateľ=Microsoft.ACE.OLEDB.12.0;  

A stiahnite si a nainštalujte si bezplatný nástroj na spracovanie údajov z Accessu z webovej lokality Microsoft – Microsoft Access Database Engine 2010 Redistributable

Metóda 3: Konsolidácia sprievodcu kontingenčnou tabuľkou zo starých verzií Excelu

Táto metóda je trochu zastaraná, ale stále stojí za zmienku. Formálne povedané, vo všetkých verziách až do roku 2003 vrátane bola v sprievodcovi kontingenčnou tabuľkou možnosť „vytvoriť pivot pre niekoľko rozsahov konsolidácie“. Takto zostavená zostava však, žiaľ, bude len žalostným zdanie skutočného plnohodnotného súhrnu a nepodporuje mnohé „čipy“ konvenčných kontingenčných tabuliek:

V takomto pivote nie sú v zozname polí žiadne nadpisy stĺpcov, neexistuje flexibilné nastavenie štruktúry, množina použitých funkcií je obmedzená a vo všeobecnosti to všetko nie je veľmi podobné kontingenčnej tabuľke. Možno práve preto Microsoft od roku 2007 odstránil túto funkciu zo štandardného dialógového okna pri vytváraní zostáv kontingenčných tabuliek. Teraz je táto funkcia dostupná iba prostredníctvom vlastného tlačidla Sprievodca kontingenčnou tabuľkou(Sprievodca kontingenčnou tabuľkou), ktorú je možné v prípade potreby pridať na panel s nástrojmi Rýchly prístup pomocou Súbor – Možnosti – Prispôsobiť panel nástrojov Rýchly prístup – Všetky príkazy (Súbor — Možnosti — Prispôsobiť panel s nástrojmi Rýchly prístup — Všetky príkazy):

Po kliknutí na pridané tlačidlo musíte v prvom kroku sprievodcu vybrať príslušnú možnosť:

A potom v ďalšom okne postupne vyberte každý rozsah a pridajte ho do všeobecného zoznamu:

Ale opäť to nie je plnohodnotné zhrnutie, takže od toho nečakajte príliš veľa. Túto možnosť môžem odporučiť len vo veľmi jednoduchých prípadoch.

• Vytváranie zostáv pomocou kontingenčných tabuliek
• Nastavte výpočty v kontingenčných tabuľkách
• Čo sú makrá, ako ich používať, kam skopírovať kód VBA atď.
• Zber údajov z viacerých hárkov do jedného (doplnok PLEX)