Nemáte Power Pivot? Na tom nezáleží. Většina doplňku Power Pivot je integrována do aplikace Excel 2013 a ještě více do aplikace Excel 2016. Náš tip od Ash dnes spojuje tabulky v kontingenční tabulce.
Každou středu po dobu sedmi týdnů představuji jeden z oblíbených tipů od Ash Sharmy. Ash je produktový manažer v týmu Excel. Jeho tým vám přináší kontingenční tabulky a mnoho dalších dobrých věcí. Dnes je Ashovou oblíbenou funkcí spojování více datových sad pomocí relací a datového modelu.
Řekněme, že vaše IT oddělení vám poskytne soubor dat zobrazený ve sloupcích A: D. Existují pole pro zákazníka a trh. Musíte spojit určité trhy do regionů. Každý zákazník patří do sektoru. Region a sektor nejsou v původních datech, ale k získání těchto informací máte vyhledávací tabulky.
Normálně byste data zploštili pomocí VLOOKUP k načtení dat z oranžové a žluté tabulky do modré tabulky. Ale protože pole klíče není na levé straně každé tabulky, budete muset buď přepnout na INDEX a MATCH, nebo znovu uspořádat vyhledávací tabulky.
Počínaje Excelem 2013 můžete nechat vyhledávací tabulky tam, kde jsou, a zkombinovat je v samotné sestavě kontingenční tabulky.
Aby tato technika fungovala, musí být všechny tři tabulky formátovány jako tabulka. Vyberte jednu buňku v každé datové sadě a zvolte Domů, Formátovat jako tabulku nebo stiskněte Ctrl + T. Tyto tři tabulky se zpočátku budou jmenovat Tabulka1, Tabulka2 a Tabulka3. Používám kartu Návrh nástrojů tabulky na pásu karet a každou tabulku přejmenuji. Také měním barvu každé tabulky. V tomto příkladu se modrá tabulka nazývá Data. Oranžová tabulka je RegionTable. Žlutá tabulka je SectorTable.
Poznámka
Někteří vám řeknou, že byste měli používat podivná jména jako Fact, TblSector a TblRegion. Pokud vás někdo takto trápí, jednoduše mu ukradněte kapesní chránič a dejte mu vědět, že dáváte přednost anglicky znějícím jménům.
Chcete-li tabulku přejmenovat, zadejte nový název do pole na levé straně karty Návrh nástrojů tabulky. Názvy tabulek by neměly mít mezery.
Jakmile jsou definovány tři tabulky, přejděte na kartu Data a klikněte na Vztahy.
V dialogovém okně Správa vztahů klikněte na Nový. V dialogu Vytvořit vztah zadejte, že pole Zákazník v datové tabulce souvisí s polem Zákazník v Sektorové tabulce. Klikněte na OK.
Definujte další nový vztah mezi polem Market v polích Data a RegionTable. Po definování obou vztahů se zobrazí v dialogovém okně Správa vztahů.
Gratulujeme: právě jste do svého sešitu vytvořili datový model. Je na čase sestavit kontingenční tabulku.
Vyberte prázdnou buňku, kde se má kontingenční tabulka zobrazit. Ve výchozím nastavení se v dialogu Vytvořit kontingenční tabulku vybere možnost Použít datový model tohoto sešitu. Umístění kontingenční tabulky bude výchozí pro buňku, kterou jste vybrali. Klikněte na OK.
Seznam polí kontingenční tabulky zobrazí seznam všech tří tabulek. Pomocí trojúhelníku nalevo od tabulky rozbalte název tabulky a zobrazte pole.
Rozbalte tabulku Data. Vyberte pole Výnosy. Automaticky se přesune do oblasti Hodnoty. Rozbalte sektorovou tabulku. Vyberte pole Sektor. Přesune se do oblasti řádků. Rozbalte RegionTable. Přetáhněte pole Region do oblasti Sloupce. Nyní budete mít kontingenční tabulku, která shrnuje data ze tří tabulek.
Poznámka
V každé knize, kterou jsem dnes napsal, používám k sestavení této zprávy jinou techniku. Po definování tří tabulek zvolím buňku A1 a Vložit, Kontingenční tabulka. Zaškrtnu políčko Přidat tato data do datového modelu. V seznamu Pole kontingenční tabulky vyberte v horní části seznamu vše. Vyberte pole pro sestavu a poté definujte vztahy po faktu. Výše popsaná technika se zdá být plynulejší a ve skutečnosti vyžaduje trochu plánování dopředu. Lidé, kteří ve svém kódu VBA používají Option Explicit, by tuto metodu určitě měli rádi.
Díky vztahům v datovém modelu se Excel cítí spíše jako Access nebo SQL Server, ale se všemi dobrými vlastnostmi aplikace Excel.
Rád bych požádal tým aplikace Excel o jejich oblíbené funkce. Každou středu budu sdílet jednu z jejich odpovědí. Děkujeme Ash Sharma za poskytnutí této myšlenky.
Excel myšlenka dne
Požádal jsem své přátele Excel Master o radu ohledně Excelu. Dnešní myšlenka k zamyšlení:
"Nevyhledávejte, pokud jste ve vztahu"
John Michaloudis
