Nemáte Power Pivot? Na tom nezáleží. Väčšina doplnku Power Pivot je zabudovaná do Excelu 2013 a ešte viac do Excelu 2016. Náš tip od Ash dnes spája tabuľky v kontingenčnej tabuľke.
Každú stredu počas siedmich týždňov uvádzam jeden z obľúbených tipov od Ash Sharma. Ash je produktový manažér v tíme Excel. Jeho tím vám prináša kontingenčné tabuľky a mnoho ďalších dobrých vecí. Dnes je Ashovou obľúbenou funkciou spájanie viacerých množín údajov pomocou vzťahov a dátového modelu.
Povedzme, že vaše IT oddelenie vám poskytne súbor údajov zobrazený v stĺpcoch A: D. Existujú polia pre zákazníka a trh. Musíte spojiť určité trhy do regiónov. Každý zákazník patrí do určitého odvetvia. Región a Sektor nie sú v pôvodných údajoch, ale na poskytnutie týchto informácií máte vyhľadávacie tabuľky.
Za normálnych okolností by ste údaje zlúčili pomocou nástroja VLOOKUP na stiahnutie údajov z oranžovej a žltej tabuľky do modrej tabuľky. Pretože ale kľúčové pole nie je na ľavej strane každej tabuľky, budete musieť prepnúť na INDEX a MATCH alebo znova usporiadať vyhľadávacie tabuľky.
Počnúc programom Excel 2013 môžete vyhľadávacie tabuľky nechať tam, kde sú, a skombinovať ich v samotnom prehľade kontingenčnej tabuľky.
Aby táto technika fungovala, musia byť všetky tri tabuľky formátované ako tabuľka. Vyberte jednu bunku v každej množine údajov a zvoľte Domov, Formátovať ako tabuľku alebo stlačte Ctrl + T. Tieto tri tabuľky sa spočiatku budú volať Tabuľka1, Tabuľka2 a Tabuľka3. Na páse s nástrojmi používam kartu Návrh nástrojov tabuľky a každú tabuľku premenujem. Tiež mením farbu každej tabuľky. V tomto príklade sa modrá tabuľka nazýva Údaje. Oranžová tabuľka je RegionTable. Žltá tabuľka je SectorTable.
Poznámka
Niektorí vám povedia, že by ste mali používať podivné názvy ako Fact, TblSector a TblRegion. Ak vás niekto takto trápi, jednoducho mu ukradnite chránič vrecka a oznámte mu, že uprednostňujete anglicky znejúce mená.
Ak chcete premenovať tabuľku, zadajte nový názov do poľa na ľavej strane karty Návrh nástrojov tabuľky. Názvy tabuliek by nemali mať medzery.
Po definovaní troch tabuliek prejdite na kartu Údaje a kliknite na položku Vzťahy.
V dialógovom okne Správa vzťahov kliknite na položku Nové. V dialógovom okne Vytvoriť vzťah zadajte, že pole Zákazník dátovej tabuľky súvisí s Zákazníckym poľom SectorTable. Kliknite na tlačidlo OK.
Definujte ďalší nový vzťah medzi poľom Market v poliach Data a RegionTable. Po definovaní obidvoch vzťahov ich uvidíte v dialógovom okne Správa vzťahov.
Gratulujeme: práve ste do svojho zošita vytvorili dátový model. Je čas zostaviť kontingenčnú tabuľku.
Vyberte prázdnu bunku, kde sa má zobraziť vaša kontingenčná tabuľka. V predvolenom nastavení sa v dialógovom okne Vytvorenie kontingenčnej tabuľky vyberie možnosť Použiť údajový model tohto zošita. Umiestnenie kontingenčnej tabuľky bude predvolene na bunku, ktorú ste vybrali. Kliknite na tlačidlo OK.
V zozname Polia kontingenčnej tabuľky budú uvedené všetky tri tabuľky. Pomocou trojuholníka vľavo od tabuľky rozbaľte názov tabuľky a zobrazte jej polia.
Rozbaľte tabuľku Údaje. Vyberte pole Výnosy. Automaticky sa presunie do oblasti Hodnoty. Rozbaľte sektorovú tabuľku. Vyberte pole Sektor. Presunie sa do oblasti Riadky. Rozbaľte tabuľku regiónov. Presuňte pole Oblasť do oblasti Stĺpce. Teraz budete mať kontingenčnú tabuľku so súhrnnými údajmi z troch tabuliek.
Poznámka
V každej knihe, ktorú som dnes napísal, používam na zostavenie tejto správy inú techniku. Po definovaní troch tabuliek zvolím bunku A1 a Vložiť, Kontingenčná tabuľka. Začiarknem políčko Pridať tieto údaje do dátového modelu. V zozname Polia kontingenčnej tabuľky vyberte v hornej časti zoznamu všetko. Vyberte polia pre správu a potom definujte vzťahy po skutočnosti. Vyššie popísaná technika sa javí plynulejšia a v skutočnosti vyžaduje trochu plánovania. Ľudia, ktorí používajú Option Explicit vo svojom kóde VBA, by sa tejto metóde určite páčili.
Vďaka vzťahom v dátovom modeli sa program Excel cíti skôr ako Access alebo SQL Server, ale so všetkou dobrotou Excelu.
Rád by som požiadal tím programu Excel o ich obľúbené funkcie. Každú stredu sa podelím o jednu z ich odpovedí. Ďakujem Ash Sharma za poskytnutie tejto myšlienky.
Excel myslel dňa
Požiadal som svojich priateľov programu Excel Master o radu o programe Excel. Dnešná myšlienka na zamyslenie:
„Nepátraj, ak máš vzťah“
John Michaloudis
