Kontingenční tabulka má několik rozložení, která poskytují předdefinovanou strukturu sestavy, ale tato rozložení nelze přizpůsobit. Pokud potřebujete při návrhu rozložení sestavy kontingenční tabulky větší flexibilitu, můžete převést buňky na vzorce listu a pak změnit rozložení těchto buněk tím, že využijete všechny funkce, které jsou na listu k dispozici. Buňky můžete převést na vzorce, které používají funkce datové krychle, nebo použít funkci GETPIVOTDATA. Převod buněk na vzorce výrazně zjednodušuje proces vytváření, aktualizace a údržby těchto přizpůsobených kontingenčních tabulek.
Při převodu buněk na vzorce přistupují tyto vzorce ke stejným datům jako kontingenční tabulka a dají se aktualizovat, aby se zobrazily aktuální výsledky. S možnou výjimkou filtrů sestav už ale nemáte přístup k interaktivním funkcím kontingenční tabulky, jako je filtrování, řazení nebo rozbalování a sbalení úrovní.
Poznámka: Při převodu kontingenční tabulky OLAP (Online Analytical Processing) můžete pokračovat v aktualizaci dat, abyste získali aktuální hodnoty měr, ale nemůžete aktualizovat skutečné členy zobrazené v sestavě.
Informace o běžných scénářích převodu kontingenčních tabulek na vzorce listu
Tady jsou typické příklady toho, co můžete udělat po převodu buněk kontingenční tabulky na vzorce listu a přizpůsobit rozložení převedených buněk.
Změna uspořádání a odstranění buněk
Řekněme, že máte pravidelnou sestavu, kterou potřebujete každý měsíc vytvořit pro své zaměstnance. Potřebujete jenom podmnožinu informací sestavy a raději data rozložíte přizpůsobeným způsobem. Buňky můžete jednoduše přesunout a uspořádat v požadovaném rozložení návrhu, odstranit buňky, které nejsou pro měsíční sestavu pedagogů potřeba, a potom naformátovat buňky a list tak, aby vyhovovaly vašim preferencím.
Vložení řádků a sloupců
Řekněme, že chcete zobrazit informace o prodeji za předchozí dva roky rozdělené podle oblastí a produktových skupin a že chcete vložit rozšířený komentář do dalších řádků. Stačí vložit řádek a zadat text. Kromě toho chcete přidat sloupec, který zobrazuje prodej podle oblastí a produktových skupin, které nejsou v původní kontingenční tabulce. Jednoduše vložte sloupec, přidejte vzorec, abyste získali požadované výsledky, a pak vyplňte sloupec dolů, abyste získali výsledky pro každý řádek.
Použití více zdrojů dat
Řekněme, že chcete porovnat výsledky mezi produkční a testovací databází, abyste měli jistotu, že testovací databáze generuje očekávané výsledky. Můžete snadno zkopírovat vzorce buněk a potom změnit argument připojení tak, aby odkazovat na testovací databázi a porovnat tyto dva výsledky.
Použití odkazů na buňky k odlišným uživatelským vstupům
Řekněme, že chcete, aby se celá sestava změnila na základě vstupu uživatele. Argumenty vzorců datových krychlí můžete změnit na odkazy na buňky na listu a potom zadat do těchto buněk jiné hodnoty, aby se odvozily jiné výsledky.
Vytvoření neuniformovaného rozložení řádků nebo sloupců (označované také jako asymetrické generování sestav)
Řekněme, že potřebujete vytvořit sestavu, která obsahuje sloupec 2008 s názvem Skutečné prodeje, sloupec 2009 s názvem Projektované prodeje, ale nechcete žádné další sloupce. Na rozdíl od kontingenční tabulky, která vyžaduje symetrické generování sestav, můžete vytvořit sestavu, která obsahuje jenom tyto sloupce.
Vytvoření vlastních vzorců datové krychle a výrazů MDX
Řekněme, že chcete vytvořit sestavu, která zobrazuje prodeje pro konkrétní produkt podle tří konkrétních prodejců za měsíc červenec. Pokud znáte výrazy MDX a dotazy OLAP, můžete vzorce datových krychlí zadat sami. I když tyto vzorce můžou být poměrně propracované, můžete jejich vytváření zjednodušit a zlepšit jejich přesnost pomocí funkce Automatické dokončování vzorců. Další informace najdete v tématu Použití automatického dokončování vzorců.
Poznámka: Pomocí tohoto postupu můžete převést pouze kontingenční tabulku OLAP (Online Analytical Processing).
-
Pokud chcete kontingenční tabulku uložit pro budoucí použití, doporučujeme, abyste si před převodem kontingenční tabulky vytvořili kopii sešitu kliknutím na Soubor > Uložit jako. Další informace najdete v tématu Uložení souboru.
-
Připravte kontingenční tabulku tak, abyste mohli minimalizovat přeuspořádání buněk po převodu následujícím způsobem:
-
Změňte na rozložení, které se nejvíce podobá požadovanému rozložení.
-
Pomocí interakce se sestavou, například filtrováním, řazením a návrhem návrhu sestavy, získáte požadované výsledky.
-
-
Klikněte na kontingenční tabulku.
-
Na kartě Možnosti klikněte ve skupině Nástroje na nástroje OLAP a potom klikněte na Převést na vzorce.
Pokud nejsou k dispozici žádné filtry sestav, operace převodu se dokončí. Pokud existuje jeden nebo více filtrů sestav, zobrazí se dialogové okno Převést na vzorce .
-
Rozhodněte se, jak chcete kontingenční tabulku převést:
Převod celé kontingenční tabulky
-
Zaškrtněte políčko Převést filtry sestav .
Tím se všechny buňky převedou na vzorce listu a odstraní se celá kontingenční tabulka.
Převeďte jenom popisky řádků kontingenční tabulky, popisky sloupců a oblasti hodnot, ale zachovejte filtry sestav.
-
Ujistěte se, že políčko Převést filtry sestav není zaškrtnuté. (Toto je výchozí nastavení.)
Tím převedete všechny buňky popisků řádků, popisků sloupců a hodnot na vzorce listu a zachová se původní kontingenční tabulka, ale jenom s filtry sestavy, abyste mohli pokračovat v filtrování pomocí filtrů sestavy.
Poznámka: Pokud je formát kontingenční tabulky verze 2000–2003 nebo starší, můžete převést jenom celou kontingenční tabulku.
-
-
Klikněte na tlačítko Převést.
Operace převodu nejprve aktualizuje kontingenční tabulku, aby se zajistila použití aktuálních dat.
Během operace převodu se na stavovém řádku zobrazí zpráva. Pokud operace trvá dlouho a chcete ji převést jinou dobu, zrušte ji stisknutím klávesy ESC.
Poznámky:
-
Buňky s filtry použitými na skryté úrovně nelze převést.
-
Nelze převést buňky, ve kterých pole mají vlastní výpočet vytvořený prostřednictvím karty Zobrazit hodnoty jako v dialogovém okně Nastavení polí hodnot . (Na kartě Možnosti klikněte ve skupině Aktivní pole na aktivní pole a potom klikněte na Nastavení polí hodnot.)
-
U převedených buněk se formátování buněk zachová, ale styly kontingenční tabulky se odeberou, protože tyto styly se dají použít jenom pro kontingenční tabulky.
-
Funkci GETPIVOTDATA ve vzorci můžete použít k převodu buněk kontingenční tabulky na vzorce listu, pokud chcete pracovat se zdroji dat mimo OLAP, pokud nechcete hned upgradovat na nový formát kontingenční tabulky verze 2007 nebo pokud se chcete vyhnout složitosti používání funkcí datové krychle.
-
Ujistěte se, že je ve skupině Kontingenční tabulka na kartě Možnosti zapnutý příkaz Generate GETPIVOTDATA (Vygenerovat data GETPIVOTDATA).
Poznámka: Příkaz Vygenerovat funkci GETPIVOTDATA nastaví nebo vymaže možnost Použít funkce GETPIVOTTABLE pro odkazy na kontingenční tabulku v kategorii Vzorce v oddílu Práce se vzorci v dialogovém okně Možnosti aplikace Excel .
-
Zkontrolujte, jestli je v kontingenční tabulce viditelná buňka, kterou chcete použít v jednotlivých vzorcích.
-
Do buňky listu mimo kontingenční tabulku zadejte požadovaný vzorec až do bodu, kam chcete zahrnout data ze sestavy.
-
Klikněte na buňku v kontingenční tabulce, kterou chcete použít ve vzorci v kontingenční tabulce. Funkce listu GETPIVOTDATA je přidána do vzorce, který načte data z kontingenční tabulky. Tato funkce bude dál načítat správná data, pokud se změní rozložení sestavy nebo pokud data aktualizujete.
-
Dokončete psaní vzorce a stiskněte ENTER.
Poznámka: Pokud ze sestavy odeberete některou z buněk odkazovaných ve vzorci GETPIVOTDATA, vrátí vzorec #REF!.
