Kontingenčná tabuľka obsahuje niekoľko rozložení, ktoré zostave poskytujú preddefinovanú štruktúru, tieto rozloženia však nie je možné prispôsobiť. Ak potrebujete väčšiu flexibilitu pri navrhovaní rozloženia zostavy kontingenčnej tabuľky, môžete skonvertovať bunky na vzorce hárka a potom zmeniť rozloženie týchto buniek tak, že plne využijete všetky funkcie dostupné v hárku. Bunky môžete skonvertovať na vzorce, ktoré používajú funkcie kocky, alebo použiť funkciu GETPIVOTDATA. Konverzia buniek na vzorce výrazne zjednodušuje proces vytvárania, aktualizácie a údržby týchto prispôsobených kontingenčných tabuliek.
Keď konvertujete bunky na vzorce, tieto vzorce pristupujú k rovnakým údajom ako kontingenčná tabuľka a je možné ich obnoviť, aby sa zobrazili aktuálne výsledky. S možnou výnimkou filtrov zostáv však už nemáte prístup k interaktívnym funkciám kontingenčnej tabuľky, ako je napríklad filtrovanie, zoraďovanie alebo rozširovanie a zbalenie úrovní.
Poznámka: Pri konverzii kontingenčnej tabuľky OLAP (Online Analytical Processing) môžete pokračovať v obnovovaní údajov, aby ste získali aktuálne hodnoty mierky, ale nemôžete aktualizovať skutočných členov, ktorí sa zobrazujú v zostave.
Získajte informácie o bežných scenároch konverzie kontingenčných tabuliek na vzorce hárka
Nižšie sú uvedené typické príklady toho, čo môžete urobiť po skonvertovaní buniek kontingenčnej tabuľky na vzorce hárka a prispôsobiť rozloženie konvertovaných buniek.
Zmena usporiadania a odstránenie buniek
Povedzme, že máte pravidelnú zostavu, ktorú je potrebné vytvoriť každý mesiac pre svojich zamestnancov. Potrebujete len podmnožinu informácií o zostave a údaje radšej rozložíte prispôsobeným spôsobom. Bunky môžete jednoducho premiestniť a usporiadať v požadovanom rozložení návrhu, odstrániť bunky, ktoré nie sú potrebné pre mesačnú zostavu zamestnancov, a potom formátovať bunky a hárok tak, aby vyhovovali vašim preferenciám.
Vloženie riadkov a stĺpcov
Povedzme, že chcete zobraziť informácie o predaji za predchádzajúce dva roky rozdelené podľa oblasti a skupiny produktov a že chcete vložiť rozšírený komentár do ďalších riadkov. Stačí vložiť riadok a zadať text. Okrem toho chcete pridať stĺpec, ktorý zobrazuje predaj podľa oblasti a skupiny produktov, ktorá nie je v pôvodnej kontingenčnej tabuľke. Stačí vložiť stĺpec, pridať vzorec, aby ste získali požadované výsledky, a potom vyplniť stĺpec nadol a získať výsledky pre každý riadok.
Použitie viacerých zdrojov údajov
Povedzme, že chcete porovnať výsledky medzi produkčnou a testovacou databázou, aby sa zabezpečilo, že testovacia databáza bude vytvárať očakávané výsledky. Vzorce buniek môžete jednoducho skopírovať a potom zmeniť argument pripojenia tak, aby smerovať na testovaciu databázu a porovnať tieto dva výsledky.
Použitie odkazov na bunky na zmenu vstupu používateľa
Povedzme, že chcete zmeniť celú zostavu na základe vstupu používateľa. Môžete zmeniť argumenty na vzorce kocky na odkazy na bunky v hárku a potom do týchto buniek zadať rôzne hodnoty na odvodenie rôznych výsledkov.
Vytvorenie neuniformného rozloženia riadka alebo stĺpca (nazývané aj asymetrické vykazovanie)
Povedzme, že potrebujete vytvoriť zostavu, ktorá obsahuje stĺpec 2008 s názvom Skutočný predaj, stĺpec 2009 s názvom Plánovaný predaj, ale nechcete žiadne ďalšie stĺpce. Na rozdiel od kontingenčnej tabuľky môžete vytvoriť zostavu obsahujúcu iba tieto stĺpce, ktorá vyžaduje symetrické vytváranie zostáv.
Vytvorenie vlastných vzorcov kocky a výrazov MDX
Povedzme, že chcete vytvoriť zostavu, ktorá bude zobrazovať predaj konkrétneho produktu podľa troch konkrétnych predajcov za mesiac júl. Ak máte vedomosti o výrazoch MDX a dotazoch OLAP, vzorce kocky môžete zadať sami. Hoci tieto vzorce môžu byť veľmi prepracované, môžete zjednodušiť vytváranie a zlepšiť presnosť týchto vzorcov pomocou funkcie Automatické dokončovanie vzorca. Ďalšie informácie nájdete v téme Používanie funkcie Automatické dokončovanie vzorca.
Poznámka: Kontingenčnú tabuľku OLAP (Online Analytical Processing) môžete skonvertovať iba pomocou tohto postupu.
-
Ak chcete kontingenčnú tabuľku uložiť na budúce použitie, pred konvertovaním kontingenčnej tabuľky odporúčame vytvoriť kópiu zošita kliknutím na položku Súbor > Uložiť ako. Ďalšie informácie nájdete v téme Uloženie súboru.
-
Pripravte kontingenčnú tabuľku tak, aby ste mohli minimalizovať zmenu usporiadania buniek po konverzii vykonaním nasledujúcich krokov:
-
Zmeňte rozloženie, ktoré najviac pripomína požadované rozloženie.
-
Pracujte so zostavou, ako je napríklad filtrovanie, zoraďovanie a prepracovanie zostavy, aby ste získali požadované výsledky.
-
-
Kliknite na kontingenčnú tabuľku.
-
Na karte Možnosti v skupine Nástroje kliknite na položku Nástroje OLAP a potom na položku Konvertovať na vzorce.
Ak nie sú k dispozícii žiadne filtre zostavy, operácia konverzie sa dokončí. Ak existuje jeden alebo viacero filtrov zostavy, zobrazí sa dialógové okno Konvertovať na vzorce .
-
Rozhodnite sa, ako chcete konvertovať kontingenčnú tabuľku:
Konverzia celej kontingenčnej tabuľky
-
Začiarknite políčko Konvertovať filtre zostáv .
Týmto sa skonvertujú všetky bunky na vzorce hárka a odstráni sa celá kontingenčná tabuľka.
Konvertovať iba označenia riadkov kontingenčnej tabuľky, označenia stĺpcov a hodnoty, ale ponechať filtre zostáv
-
Skontrolujte, či je začiarknuté políčko Konvertovať filtre zostavy . (Toto je predvolené nastavenie.)
Týmto sa konvertujú všetky označenie riadka, označenie stĺpca a bunky oblasti hodnôt na vzorce hárka a zachová sa pôvodná kontingenčná tabuľka, ale iba filtre zostavy, aby ste mohli pokračovať v filtrovaní pomocou filtrov zostavy.
Poznámka: Ak je formát kontingenčnej tabuľky verzie 2000-2003 alebo staršej, môžete skonvertovať iba celú kontingenčnú tabuľku.
-
-
Kliknite na položku Konvertovať.
Operácia konverzie najprv obnoví kontingenčnú tabuľku, aby sa zabezpečilo použitie aktuálnych údajov.
Počas konverzie sa v stavovom riadku zobrazí správa. Ak operácia trvá dlho a uprednostňujete konverziu v inom čase, operáciu zrušíte stlačením klávesu ESC.
Poznámky:
-
Bunky s filtrami použitými na skryté úrovne nie je možné konvertovať.
-
Nie je možné skonvertovať bunky, v ktorých polia obsahujú vlastný výpočet vytvorený prostredníctvom karty Zobraziť hodnoty ako v dialógovom okne Nastavenie poľa hodnôt . (Na karte Možnosti kliknite v skupine Aktívne pole na položku Aktívne pole a potom na položku Nastavenie poľa hodnôt.)
-
V prípade skonvertovaných buniek sa zachová formátovanie buniek, ale štýly kontingenčnej tabuľky sa odstránia, pretože tieto štýly sa môžu použiť iba v kontingenčných tabuľkách.
-
Funkciu GETPIVOTDATA môžete použiť vo vzorci na konvertovanie buniek kontingenčnej tabuľky na vzorce hárka, ak chcete pracovať so zdrojmi údajov, ktoré nie sú zdrojmi údajov OLAP, ak nechcete inovovať na nový formát kontingenčnej tabuľky verzie 2007 hneď, alebo keď sa chcete vyhnúť zložitosti používania funkcií kocky.
-
Uistite sa, že príkaz Generovať FUNKCIU GETPIVOTDATA v skupine Kontingenčná tabuľka na karte Možnosti je zapnutý.
Poznámka: Príkaz Generovať GETPIVOTDATA nastaví alebo vymaže možnosť Použiť funkcie GETPIVOTTABLE pre odkazy na kontingenčné tabuľky v kategórii Vzorce v časti Práca so vzorcami v dialógovom okne Excel – možnosti .
-
V kontingenčnej tabuľke skontrolujte, či je bunka, ktorú chcete použiť v každom vzorci, viditeľná.
-
Do bunky hárka mimo kontingenčnej tabuľky zadajte požadovaný vzorec až do bodu, do ktorého chcete zahrnúť údaje zo zostavy.
-
Kliknite na bunku v kontingenčnej tabuľke, ktorú chcete použiť vo vzorci v kontingenčnej tabuľke. Do vzorca, ktorý načíta údaje z kontingenčnej tabuľky, sa pridá funkcia hárka GETPIVOTDATA. Táto funkcia pokračuje v načítavaní správnych údajov, ak sa zmení rozloženie zostavy alebo ak obnovíte údaje.
-
Dokončite zadávanie vzorca a stlačte kláves ENTER.
Poznámka: Ak zo zostavy odstránite ľubovoľnú bunku, na ktorú sa odkazuje vo vzorci GETPIVOTDATA, vzorec vráti #REF!.
