Konverzia buniek kontingenčnej tabuľky na vzorce hárka

Kontingenčná tabuľka obsahuje niekoľko rozložení, ktoré poskytujú zostave preddefinovanú štruktúru, tieto rozloženia však nie je možné prispôsobiť. Ak potrebujete viac flexibility pri navrhovaní rozloženia zostavy kontingenčnej tabuľky, môžete bunky skonvertovať na vzorce hárka a potom zmeniť rozloženie týchto buniek tak, že naplno využijete všetky funkcie, ktoré má hárok k dispozícii. Môžete skonvertovať bunky na vzorce, ktoré používajú funkcie kocky, alebo použiť funkciu GETPIVOTDATA. Konvertovanie 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 majú prístup k rovnakým údajom ako kontingenčná tabuľka a možno ich obnoviť na zobrazenie aktuálnych výsledkov. Síce s výnimkou filtrov zostavy však už nebudete mať prístup k interaktívnym funkciám kontingenčnej tabuľky, ako je napríklad filtrovanie, zoraďovanie alebo rozbaľovanie a zbalenie úrovní.

Získajte informácie o bežných scenároch konvertovania 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ú musíte každý mesiac vytvárať pre zamestnancov. Potrebujete len podmnožinu informácií v zostave a radšej chcete rozloženie údajov prispôsobeným spôsobom. Bunky môžete jednoducho premiestňovať a usporiadať v ľubovoľnom rozložení návrhu, odstrániť bunky, ktoré nie sú potrebné pre mesačnú správu o zamestnancoch, a potom naformátovať bunky a hárok podľa svojich potrieb.

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 chcete vložiť rozšírený komentár do ďalších riadkov. Stačí vložiť riadok a zadať text. Okrem toho chcete pridať stĺpec zobrazujúci predaj podľa oblasti a skupiny produktov, ktoré nie sú v pôvodnej kontingenčnej tabuľke. Stačí vložiť stĺpec, pridať vzorec na získanie požadovaných výsledkov a potom vyplniť stĺpec nadol a získať výsledky pre každý riadok.

Používanie viacerých zdrojov údajov 

Povedzme, že chcete porovnať výsledky medzi produkčnou a testovacou databázou a zabezpečiť, aby testovacia databáza prinášala očakávané výsledky. Môžete jednoducho skopírovať vzorce buniek a potom zmeniť argument pripojenia tak, aby smeroval na testovaciu databázu na porovnanie týchto dvoch výsledkov.

Použitie odkazov na bunky na zmenu vstupu používateľa 

Povedzme, že chcete, aby sa celá zostava zmenila v závislosti od vstupu používateľa. Argumenty vo vzorcoch kocky môžete zmeniť na odkazy na bunky v hárku a potom zadať do týchto buniek rôzne hodnoty, aby ste získali rôzne výsledky.

Vytvorenie nejednotného rozloženia riadkov alebo stĺpcov (nazývané aj asymetrické zostavy) 

Povedzme, že potrebujete vytvoriť zostavu, ktorá obsahuje stĺpec z roku 2008 s názvom Skutočný predaj a stĺpec z roku 2009 s názvom Plánovaný predaj, ale nechcete žiadne ďalšie stĺpce. Na rozdiel od kontingenčnej tabuľky, ktorá vyžaduje symetrické zostavy, môžete vytvoriť zostavu obsahujúcu iba tieto stĺpce.

Vytvorenie vlastných vzorcov kocky a výrazov MDX 

Povedzme, že chcete vytvoriť zostavu, ktorá zobrazí predaj určitého produktu tromi konkrétnymi predajcami za mesiac júl. Ak máte skúsenosti s výrazmi MDX a dotazmi OLAP, vzorce kocky môžete zadať sami. Hoci tieto vzorce môžu byť pomerne zložité, môžete ich vytváranie zjednodušiť a zvýšiť presnosť pomocou funkcie Automatické dokončovanie vzorca. Ďalšie informácie nájdete v téme Používanie funkcie Automatické dokončovanie vzorca.

Konvertovanie buniek na vzorce využívajúce funkcie kocky

1. Ak chcete uložiť kontingenčnú tabuľku na neskoršie použitie, odporúčame vám pred skonvertovaním kontingenčnej tabuľky 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.

2. Pripravte kontingenčnú tabuľku tak, aby ste mohli minimalizovať zmenu usporiadania buniek po konverzii pomocou nasledujúceho postupu:

   • Zmeňte rozloženie, ktoré sa najviac podobá na požadované rozloženie.
   • Interakcia so zostavou, ako je filtrovanie, zoraďovanie a prepracovanie zostavy, aby ste dosiahli požadované výsledky.

3. Kliknite na kontingenčnú tabuľku.

4. Na karte Možnosti kliknite v skupine Nástrojena položku Nástroje OLAP a potom kliknite na položku Konvertovať na vzorce.
   Ak nie sú k dispozícii žiadne filtre zostavy, operácia konverzie sa dokončí. Ak je k dispozícii jeden alebo viacero filtrov zostavy, zobrazí sa dialógové okno Konvertovať na vzorce .

5. Rozhodnite sa, ako chcete konvertovať kontingenčnú tabuľku:
   Konvertovanie celej kontingenčnej tabuľky 

   • Začiarknite políčko Konvertovať filtre zostavy .
     Všetky bunky sa tým skonvertujú na vzorce hárka a odstráni sa celá kontingenčná tabuľka.
     Konvertovať iba označenia riadkov, stĺpcov a hodnoty kontingenčnej tabuľky, ale zachovať filtre zostavy 

   • Skontrolujte, či nie je začiarknuté políčko Konvertovať filtre zostáv . (Toto je predvolené nastavenie.)
     Všetky označenia riadkov, označenia stĺpcov a bunky oblasti hodnôt sa skonvertujú na vzorce hárka a zachová sa pôvodná kontingenčná tabuľka, ale len s filtrami zostavy, takže je možné naďalej filtrovať pomocou filtrov zostáv.

     Poznámka

     Ak je formát kontingenčnej tabuľky verzia 2000-2003 alebo staršia, môžete skonvertovať len celú kontingenčnú tabuľku.

6. Kliknite na položku Konvertovať.
   Operácia konverzie najprv obnoví kontingenčnú tabuľku, aby sa zabezpečilo používanie aktuálnych údajov.
   Počas priebehu operácie konverzie sa v stavovom riadku zobrazí správa. Ak operácia trvá dlho a chcete vykonať konverziu neskôr, stlačením klávesu ESC operáciu zrušte.

   Poznámka

   • Bunky s použitými filtrami na skryté úrovne nie je možné konvertovať.
   • 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 , nie je možné konvertovať. (Na karte Možnosti v skupine Aktívne pole kliknite na položku Aktívne pole a potom kliknite na položku Hodnoty alebo nastavenie poľa.)
   • V prípade konvertovaných buniek sa formátovanie buniek zachová, 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.

Konverzia buniek pomocou funkcie GETPIVOTDATA

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 inými ako OLAP, keď nechcete inovovať na nový formát kontingenčnej tabuľky verzie 2007 alebo ak sa chcete vyhnúť zložitosti používania funkcií kocky.

1. Uistite sa, že príkaz Generovať 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žívať 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 .

2. V kontingenčnej tabuľke skontrolujte, či je viditeľná bunka, ktorú chcete použiť v každom vzorci.

3. Do bunky hárka mimo kontingenčnej tabuľky zadajte požadovaný vzorec až do bodu, v ktorom chcete zahrnúť údaje zo zostavy.

4. Kliknite na bunku v kontingenčnej tabuľke, ktorú chcete použiť vo vzorci v kontingenčnej tabuľke. Do vzorca, ktorý načítava údaje z kontingenčnej tabuľky, sa pridá funkcia hárka GETPIVOTDATA. Táto funkcia naďalej načítava správne údaje, aj keď sa rozloženie zostavy zmení alebo ak obnovíte údaje.

5. Zadajte vzorec a stlačte kláves ENTER.

Problém: Konverzia buniek kontingenčnej tabuľky na vzorce pracovného hárka sa nedá uskutočniť