Převod buněk kontingenční tabulky na vzorce listu

Kontingenční tabulka má několik rozložení, která poskytují předdefinovanou strukturu sestavy, tato rozložení však není možné přizpůsobit. Pokud potřebujete při navrhování 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 s využitím všech funkcí dostupných na listu. Buňky můžete převést na vzorce využívající funkce datové krychle nebo funkci ZÍSKATKONTDATA. Převedení buněk na vzorce značně zjednodušuje proces vytváření, aktualizace a údržby těchto přizpůsobených kontingenčních tabulek.

Když převedete buňky na vzorce, mají tyto vzorce přístup ke stejným datům jako kontingenční tabulka a dají se aktualizovat, abyste získali aktuální výsledky. Snad s výjimkou filtrů sestavy už ale nemáte přístup k interaktivním funkcím kontingenční tabulky, jako je filtrování, řazení nebo rozbalování a sbalování úrovní.

Informace o běžných scénářích převodu kontingenčních tabulek na vzorce listu

Obvyklé příklady toho, co můžete dělat po převedení buněk kontingenční tabulky na vzorce listu, a přizpůsobit tak rozložení převedených buněk.

Změna uspořádání a odstranění buněk 

Dejme tomu, že máte pravidelnou sestavu, kterou musíte pro zaměstnance vytvořit každý měsíc. Potřebujete jenom podmnožinu informací sestavy a dáváte přednost individuálnímu rozložení dat. Buňky můžete jednoduše přesunout a uspořádat do požadovaného rozložení, odstranit buňky, které nejsou pro měsíční výkaz pracovníků potřeba, a poté naformátovat buňky a list podle svých představ.

Vložení řádků a sloupců 

Řekněme, že chcete zobrazit údaje o prodeji za předchozí dva roky rozdělené podle oblasti a produktové skupiny a že chcete do dalších řádků vložit rozšířený komentář. Stačí vložit řádek a zadat text. Navíc budete chtít přidat sloupec zobrazující prodeje podle oblasti a skupiny produktů, který není v původní kontingenční tabulce. Jednoduše vložíte sloupec, přidáte vzorec, abyste získali požadované výsledky, a pak vyplníte sloupec směrem 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 poskytuje očekávané výsledky. Vzorce buněk je možné snadno zkopírovat a potom změnit argument připojení tak, aby odkazoval na testovací databázi, a porovnat tyto dva výsledky.

Použití odkazů na buňky k obměně vstupu uživatele 

Řekněme, že chcete, aby se na základě vstupu uživatele změnila celá sestava. Argumenty vzorců datové krychle můžete na listu změnit na odkazy na buňky a potom do těchto buněk zadat různé hodnoty, abyste získali odlišné výsledky.

Vytvoření nejednotného rozložení řádků nebo sloupců (označovaného taky jako asymetrické vytváření sestav) 

Řekněme, že potřebujete vytvořit sestavu, která obsahuje sloupec roku 2008 s názvem Skutečné prodeje a sloupec roku 2009 s názvem Plánované prodeje, ale nechcete žádné další sloupce. Na rozdíl od kontingenční tabulky, která vyžaduje symetrické sestavy, můžete vytvořit sestavu, která bude obsahovat jenom tyto sloupce.

Vytváření vlastních vzorců datové krychle a výrazů MDX 

Řekněme, že chcete vytvořit sestavu, která zobrazuje prodeje určitého produktu třemi konkrétními prodejci za měsíc červenec. Pokud máte znalosti výrazů MDX a dotazů OLAP, můžete vzorce datové krychle zadat sami. Tyto vzorce mohou být značně komplikované, pomocí funkce automatického dokončování vzorců však můžete jejich vytváření zjednodušit a zvýšit jejich přesnost. Další informace najdete v tématu Použití funkce automatického dokončování vzorce.

Převod buněk na vzorce, které používají funkce datové krychle

1. Pokud chcete kontingenční tabulku uložit pro budoucí použití, doporučujeme před převodem kontingenční tabulky kliknutím na Soubor>Uložit jako vytvořit kopii sešitu. Další informace najdete v tématu Uložení souboru.

2. Připravte kontingenční tabulku tak, abyste minimalizovali změnu uspořádání buněk po převodu. Uděláte to takto:

   • Změňte rozložení, které se nejvíce podobá požadovanému rozložení.
   • Pracujte se sestavou, například filtrujte, třídíte nebo měníte její vzhled, abyste získali požadované výsledky.

3. Klikněte na kontingenční tabulku.

4. Na kartě Možnosti klikněte ve skupině Nástrojena položku Nástroje OLAP a potom klikněte na tlačítko Převést na vzorce.
   Pokud nejsou dostupné filtry přehledů, bude operace převodu dokončena. Pokud existuje jeden nebo více filtrů sestavy, zobrazí se dialogové okno Převést na vzorce .

5. Rozhodněte se, jak chcete kontingenční tabulku převést:
   Převedení celé kontingenční tabulky 

   • Zaškrtněte políčko Převést filtry sestavy .
     Tím se všechny buňky převedou na vzorce listu a odstraní se celá kontingenční tabulka.
     Převést pouze popisky řádků, popisky sloupců a oblasti hodnot kontingenční tabulky, ale zachovat filtry sestavy 

   • Ujistěte se, že není zaškrtnuté políčko Převést filtry sestavy . (Toto je výchozí nastavení.)
     Tím se všechny buňky popisků řádků, sloupců a oblastí hodnot převedou na vzorce listu a zachová se původní kontingenční tabulka, ale jenom s filtry sestavy, takže můžete dál filtrovat pomocí filtrů sestavy.

     Poznámka

     Pokud je formát kontingenční tabulky verze 2000–2003 nebo starší, můžete převést pouze celou kontingenční tabulku.

6. Klikněte na tlačítko Převést.
   Operace převodu nejdřív aktualizuje kontingenční tabulku, aby se zajistily 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 jindy, zrušte ji stisknutím klávesy ESC.

   Poznámka

   • Buňky s filtry použitými na skryté úrovně nelze převést.
   • Nelze převést buňky, ve kterých mají pole vlastní výpočet, který byl vytvořen pomocí karty Zobrazit hodnoty jako dialogového okna Nastavení polí hodnot . (Na kartě Možnosti ve skupině Aktivní pole klikněte na Aktivní pole a potom klikněte na Nastavení pole hodnot.)
   • U převáděný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 u kontingenčních tabulek.

Převod buněk pomocí funkce ZÍSKATKONTDATA

Funkci ZÍSKATKONTDATA můžete použít ve vzorci k převodu buněk kontingenční tabulky na vzorce listu, pokud chcete pracovat s jinými zdroji dat než OLAP, pokud nechcete hned upgradovat na nový formát kontingenční tabulky verze 2007 nebo pokud se chcete vyhnout složitému používání funkcí pro datové krychle.

1. Ujistěte se, že je ve skupině Kontingenční tabulka na kartě Možnosti zapnutý příkaz Generovat ZÍSKATKONTDATA.

   Poznámka

   Příkaz Generovat příkaz ZÍSKATKONTDATA nastaví nebo zruší zaškrtnutí možnosti Použít funkce ZÍSKATKONTTABULKY pro odkazy na kontingenční tabulky v kategorii Vzorce v části Práce se vzorci v dialogovém okně Možnosti aplikace Excel.

2. V kontingenční tabulce se ujistěte, že je zobrazená buňka, kterou chcete použít v jednotlivých vzorcích.

3. Do buňky listu mimo kontingenční tabulku zadejte požadovaný vzorec až do bodu, do kterého chcete zahrnout data ze sestavy.

4. Klikněte na buňku v kontingenční tabulce, kterou chcete použít ve vzorci v kontingenční tabulce. Do vzorce se přidá funkce listu ZÍSKATKONTDATA, která načte data z kontingenční tabulky. Tato funkce bude načítat správná data i po změně rozložení sestavy nebo po aktualizaci dat.

5. Dokončete zadávání vzorce a stiskněte klávesu ENTER.

Problém: Buňky kontingenční tabulky nelze převést na vzorce listu