Kontingenční tabulka napříč více datovými rozsahy

Formulace problému

Kontingenční tabulky jsou jedním z nejúžasnějších nástrojů v Excelu. Ale zatím bohužel žádná z verzí Excelu neumí za běhu tak jednoduchou a nezbytnou věc, jako je vytvoření souhrnu pro několik počátečních rozsahů dat umístěných například na různých listech nebo v různých tabulkách:

Než začneme, ujasněme si několik bodů. A priori se domnívám, že naše údaje splňují následující podmínky:

• Tabulky mohou mít libovolný počet řádků s libovolnými daty, ale musí mít stejné záhlaví.
• Na listech se zdrojovými tabulkami by neměla být žádná data navíc. Jeden list – jedna tabulka. Pro ovládání doporučuji použít klávesovou zkratku Ctrl+Konec, která vás přesune na poslední použitou buňku v listu. V ideálním případě by to měla být poslední buňka v datové tabulce. Pokud když kliknete na Ctrl+Konec zvýrazní se jakákoli prázdná buňka vpravo nebo pod tabulkou – smažte tyto prázdné sloupce vpravo nebo řádky pod tabulkou za tabulkou a soubor uložte.

Metoda 1: Vytvořte tabulky pro pivot pomocí Power Query

Počínaje verzí 2010 pro Excel je k dispozici bezplatný doplněk Power Query, který dokáže shromažďovat a transformovat jakákoli data a poté je poskytnout jako zdroj pro vytvoření kontingenční tabulky. Vyřešit náš problém pomocí tohoto doplňku není vůbec těžké.

Nejprve si v Excelu vytvoříme nový prázdný soubor – v něm proběhne montáž a následně se v něm vytvoří kontingenční tabulka.

Poté na kartě Data (pokud máte Excel 2016 nebo novější) nebo na záložce Dotaz na napájení (pokud máte Excel 2010-2013) vyberte příkaz Vytvořit dotaz – Ze souboru – Excel (Získat data — Ze souboru — Excel) a zadejte zdrojový soubor s tabulkami, které se mají shromáždit:

V okně, které se objeví, vyberte libovolný list (nezáleží na tom, který) a stiskněte tlačítko níže Přeměna (Edit):

V horní části Excelu by se mělo otevřít okno Power Query Query Editor. Na pravé straně okna na panelu Parametry požadavku odstranit všechny automaticky vytvořené kroky kromě prvního – Zdroj (Zdroj):

Nyní vidíme obecný seznam všech listů. Pokud jsou v souboru kromě datových listů ještě nějaké další postranní listy, pak je v tomto kroku naším úkolem vybrat pouze ty listy, ze kterých je třeba načíst informace, kromě všech ostatních pomocí filtru v záhlaví tabulky:

Smazat všechny sloupce kromě sloupce Datakliknutím pravým tlačítkem na záhlaví sloupce a výběrem Odstraňte další sloupce (Odstranit další sloupce):

Poté můžete obsah shromážděných tabulek rozbalit kliknutím na dvojitou šipku v horní části sloupce (zaškrtávací políčko Jako předponu použijte původní název sloupce můžete to vypnout):

Pokud jste vše udělali správně, v tomto okamžiku byste měli vidět obsah všech tabulek shromážděných pod sebou:

Zbývá tlačítkem zvednout první řádek do záhlaví tabulky Použijte první řádek jako záhlaví (Použít první řádek jako záhlaví) Karta Domů (Domov) a odstraňte duplicitní záhlaví tabulek z dat pomocí filtru:

Uložte vše, co jste provedli pomocí příkazu Zavřít a načíst – Zavřít a načíst… (Zavřít a načíst — Zavřít a načíst do…) Karta Domů (Domov)a v okně, které se otevře, vyberte možnost Pouze připojení (Pouze připojení):

Všechno. Zbývá pouze vytvořit shrnutí. Chcete-li to provést, přejděte na kartu Vložit – kontingenční tabulka (Vložit — kontingenční tabulka), vyberte možnost Použijte externí zdroj dat (Použít externí zdroj dat)a poté kliknutím na tlačítko Vyberte připojení, naše žádost. Další tvorba a konfigurace pivotu probíhá zcela standardním způsobem přetažením potřebných polí do oblasti řádků, sloupců a hodnot:

Pokud se v budoucnu změní zdrojová data nebo se přidá několik dalších listů obchodu, bude stačit aktualizovat dotaz a naše shrnutí pomocí příkazu Obnovit vše Karta Data (Data – Obnovit vše).

Metoda 2. Tabulky sjednocujeme příkazem UNION SQL v makru

Další řešení našeho problému představuje toto makro, které pomocí příkazu vytvoří datovou sadu (cache) pro kontingenční tabulku JEDNOTA SQL dotazovací jazyk. Tento příkaz kombinuje tabulky ze všech zadaných v poli Názvy listů listů knihy do jediné datové tabulky. To znamená, že místo fyzického kopírování a vkládání rozsahů z různých listů na jeden děláme totéž v paměti RAM počítače. Poté makro přidá nový list s daným názvem (proměnná ResultSheetName) a na základě nasbírané keše na něm vytvoří plnohodnotný (!) souhrn.

Chcete-li použít makro, použijte tlačítko Visual Basic na kartě vývojka (Vývojář) nebo klávesovou zkratkou Další+F11. Poté přes nabídku vložíme nový prázdný modul Vložit – Modul a zkopírujte tam následující 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ázev listu, kde se zobrazí výsledný pivot' ResultSheetName = "Pivot list" jména se zdrojovými tabulkami SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'vytváříme mezipaměť pro tabulky z listů 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) & "$]" Další i Set objRS = CreateObject("ADODB.Recordset") objRS .Otevřít Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Zdroj dat=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) End With 'znovu vytvořte list pro zobrazení výsledné kontingenční tabulky On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'zobrazit vygenerovaný souhrn mezipaměti na tomto listu Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivotTable. objPivotCache = Nothing Range("A3"). Vyberte End With End Sub    

Hotové makro pak lze spustit klávesovou zkratkou Další+F8 nebo tlačítko Makra na kartě vývojka (Vývojář — Makra).

Nevýhody tohoto přístupu:

• Data nejsou aktualizována, protože mezipaměť nemá žádné připojení ke zdrojovým tabulkám. Pokud změníte zdrojová data, musíte znovu spustit makro a znovu vytvořit souhrn.
• Při změně počtu listů je nutné upravit kód makra (pole Názvy listů).
  

Ale nakonec dostaneme skutečnou plnohodnotnou kontingenční tabulku postavenou na několika řadách z různých listů:

Voila!

Technická poznámka: pokud se při spouštění makra zobrazí chyba jako „Poskytovatel není registrován“, pak s největší pravděpodobností máte 64bitovou verzi Excelu nebo je nainstalována neúplná verze Office (bez přístupu). Chcete-li situaci vyřešit, nahraďte fragment v kódu makra:

	 Poskytovatel=Microsoft.Jet.OLEDB.4.0;  

na:

	Poskytovatel=Microsoft.ACE.OLEDB.12.0;  

A stáhněte si a nainstalujte bezplatný modul pro zpracování dat z Accessu z webu Microsoft – Microsoft Access Database Engine 2010 Redistributable

Metoda 3: Konsolidace Průvodce kontingenční tabulkou ze starých verzí aplikace Excel

Tato metoda je trochu zastaralá, ale přesto stojí za zmínku. Formálně řečeno, ve všech verzích až do roku 2003 včetně byla v průvodci kontingenční tabulkou možnost „vytvořit pivot pro několik rozsahů konsolidace“. Takto konstruovaná sestava však bude bohužel jen žalostnou zdáním skutečného plnohodnotného shrnutí a nepodporuje mnoho „čipů“ konvenčních kontingenčních tabulek:

V takovém pivotu nejsou v seznamu polí žádná záhlaví sloupců, neexistuje flexibilní nastavení struktury, sada použitých funkcí je omezená a obecně to vše není příliš podobné kontingenční tabulce. Možná proto Microsoft počínaje rokem 2007 odstranil tuto funkci ze standardního dialogu při vytváření sestav kontingenčních tabulek. Nyní je tato funkce dostupná pouze prostřednictvím vlastního tlačítka Průvodce kontingenční tabulkou(Průvodce kontingenční tabulkou), kterou lze v případě potřeby přidat na panel nástrojů Rychlý přístup pomocí Soubor – Možnosti – Přizpůsobit panel nástrojů Rychlý přístup – Všechny příkazy (Soubor — Možnosti — Přizpůsobit panel nástrojů Rychlý přístup — Všechny příkazy):

Po kliknutí na přidané tlačítko musíte v prvním kroku průvodce vybrat příslušnou možnost:

A pak v dalším okně postupně vyberte každý rozsah a přidejte jej do obecného seznamu:

Ale opět se nejedná o plnohodnotné shrnutí, takže od toho moc nečekejte. Tuto možnost mohu doporučit pouze ve velmi jednoduchých případech.

• Vytváření sestav pomocí kontingenčních tabulek
• Nastavte výpočty v kontingenčních tabulkách
• Co jsou makra, jak je používat, kam kopírovat kód VBA atd.
• Sběr dat z více listů na jeden (doplňek PLEX)