Výpočet hodnôt v kontingenčnej tabuľke

Na výpočet hodnôt v kontingenčnej tabuľke môžete použiť niektoré alebo všetky z nasledujúcich typov spôsobov výpočtu:

• Súhrnné funkcie v poliach hodnôt    Údaje v oblasti hodnôt vytvárajú súhrn príslušných zdrojových údajov v kontingenčnej tabuľke. Príkladom môžu byť nasledujúce zdrojové údaje:

• Tieto údaje vytvárajú nasledujúce kontingenčné tabuľky a grafy. Ak z údajov v kontingenčnej tabuľke vytvoríte kontingenčný graf, hodnoty v danom kontingenčnom grafe budú odrážať výpočty, ktoré obsahuje priradená zostava kontingenčnej tabuľky.

• Pole stĺpca Mesiac v kontingenčnej tabuľke obsahuje položky Marec a Apríl. Pole riadka Oblasť obsahuje položky Sever, Juh, Východ a Západ. Hodnota v prieniku stĺpca Apríl a riadka Sever je celkový výnos z predaja zo záznamov v zdrojových údajoch, ktoré obsahujú Apríl v poli Mesiac a hodnoty Sever v poli Oblasť.

• Pole Oblasť v kontingenčnom grafe môže byť poľom kategórií, ktoré obsahuje kategórie Sever, Juh, Východ a Západ. Pole Mesiac môže byť poľom radov, ktoré zobrazuje položky Marec, Apríl a Máj ako rady znázornené v legende. Pole hodnôt s názvom Celkový predaj môže obsahovať údajové značky predstavujúce celkové výnosy v jednotlivých oblastiach za každý mesiac. Jedna údajová značka môže napríklad predstavovať celkový predaj za apríl v oblasti Sever, a to na základe svojej polohy na zvislej osi hodnôt.

• Na vypočítanie polí hodnôt sú k dispozícii nasledujúce súhrnné funkcie pre všetky typy zdrojových údajov (okrem zdrojových údajov OLAP (Online Analytical Processing).

  Funkcia	Poskytovaný súhrn
  Súčet	Súčet hodnôt. Toto je predvolená funkcia pre číselné údaje.
  Počet	Počet údajových hodnôt. Súhrnná funkcia Počet funguje rovnako ako funkcia COUNTA. Počet je predvolená funkcia pre údaje iné ako čísla.
  Priemer	Priemer hodnôt.
  Maximum	Najväčšia hodnota.
  Minimum	Najmenšia hodnota.
  Produkt	Súčin hodnôt.
  Počet čísel	Počet údajových hodnôt, ktoré sú číslami. Súhrnná funkcia Počet čísel funguje rovnako ako funkcia COUNT.
  StDev	Odhad smerodajnej odchýlky základného súboru, kde vzorka je podmnožinou celého súboru.
  StDevp	Smerodajná odchýlka základného súboru, pričom základný súbor predstavuje všetky údaje, ktoré sa majú sumarizovať.
  Odhad rozptylu	Odhad rozptylu základného súboru, kde vzorka je podmnožinou celého súboru.
  Rozptyl	Rozptyl základného súboru, pričom základný súbor predstavuje všetky údaje, ktoré sa majú sumarizovať.

• Vlastné výpočty    vlastný výpočet zobrazuje hodnoty na základe iných položiek alebo buniek v oblasti údajov. Môžete napríklad zobraziť hodnoty v údajovom poli Celkový predaj ako percento predaja v poli Marec alebo ako medzisúčet položiek v poli Mesiac.

  Na vlastné výpočty v poliach hodnôt sú dispozícii nasledujúce funkcie.

  Funkcia	Výsledok
  Žiadny výpočet	Zobrazí hodnotu zadanú do poľa.
  % z celkového súčtu	Zobrazí hodnoty ako percento celkového súčtu všetkých hodnôt alebo údajových bodov v zostave.
  % zo súčtu stĺpca	Zobrazí všetky hodnoty každého stĺpca alebo radu ako percento celkovej hodnoty stĺpca alebo radu.
  % zo súčtu riadka	Zobrazí hodnotu každého riadka alebo kategórie celkovej hodnoty riadka alebo kategórie.
  % z	Zobrazí hodnoty ako percento hodnoty základnej položky v základnom poli.
  % súčtu nadradeného riadka	Vypočíta hodnoty takto: (hodnota položky)/(hodnota nadradenej položky v riadkoch)
  % súčtu nadradeného stĺpca	Vypočíta hodnoty takto: (hodnota položky)/(hodnota nadradenej položky v stĺpcoch)
  % súčtu nadradenej položky	Vypočíta hodnoty takto: (hodnota položky)/(hodnota nadradenej položky vybraného základného poľa)
  Rozdiel medzi	Zobrazí hodnoty ako rozdiel oproti hodnote základnej položky v základnom poli.
  % rozdielu medzi	Zobrazí hodnoty ako percentuálny rozdiel oproti hodnote základnej položky v základnom poli.
  Medzisúčet v	Zobrazí medzisúčet za sebou idúcich položiek v základnom poli.
  % medzisúčtu	Vypočíta hodnotu ako percento pre po sebe nasledujúce položky v základnom poli, ktoré sa zobrazujú ako medzisúčet.
  Zobraziť od najmenšej hodnoty po najväčšiu	Zobrazí poradie vybraných hodnôt v konkrétnom poli, pričom ako pole 1 bude v zozname položka s najmenšou hodnotou a nasledovať budú väčšie hodnoty, ktorým sa priradí vyššia hodnota poradia.
  Zobraziť od najväčšej hodnoty po najmenšiu	Zobrazí poradie vybraných hodnôt v konkrétnom poli, pričom ako pole 1 bude v zozname položka s najväčšou hodnotou a nasledovať budú menšie hodnoty, ktorým sa priradí vyššia hodnota poradia.
  Register	Vypočíta hodnoty takto: ((hodnota v bunke) * (celkový súčet celkových súčtov))/((celkový súčet riadka) * (celkový súčet stĺpca))

• Vzorce.    Ak súhrnné funkcie a vlastné výpočty neposkytujú požadované výsledky, môžete vo vypočítavaných poliach a vypočítavaných položkách vytvoriť vlastné vzorce. Môžete napríklad pridať vypočítavanú položku so vzorcom provízie z predaja, ktorá sa môže v jednotlivých oblastiach líšiť. Zostava potom automaticky zahrnie províziu do medzisúčtov a celkových súčtov.

Výpočty a možnosti dostupné v zostave závisia od toho, či zdrojové údaje pochádzajú z databázy OLAP alebo zo zdroja údajov iného typu než OLAP.

• Výpočty založené na zdrojových údajoch OLAP.    V prípade kontingenčných tabuliek vytvorených z kociek OLAP sa súhrnné hodnoty najskôr vypočítavajú na serveri OLAP a až potom Excel zobrazuje výsledky. Spôsob, akým sa tieto vopred vypočítavané hodnoty vypočítavajú v kontingenčnej tabuľke, nie je možné zmeniť. Nemôžete napríklad zmeniť súhrnnú funkciu, ktorá sa používa na vypočítanie údajových polí alebo medzisúčtov, ani pridať vypočítavané polia alebo položky.

  Platí tiež, že ak server OLAP poskytuje vypočítavané polia, ktoré sa označujú ako výpočtové členy, zobrazia sa tieto polia v zozname polí kontingenčnej tabuľky. Zobrazia sa aj všetky vypočítavané polia a vypočítavané položky vytvorené pomocou makier, ktoré boli napísané v jazyku VBA (Visual Basic for Applications) a uložené v zošite, dané polia ani položky však nebudete môcť zmeniť. Ak potrebujete ďalšie typy výpočtov, obráťte sa na správcu databázy OLAP.

  Ak používate zdroj údajov OLAP, môžete pri výpočte medzisúčtov a celkových súčtov zahrnúť alebo vylúčiť hodnoty skrytých položiek.

• Výpočty založené na zdrojových údajoch iného typu než OLAP.    V kontingenčných tabuľkách založených na iných typoch externých údajov alebo údajoch z pracovných hárkov používa Excel súhrnnú funkciu Sum, pomocou ktorej vypočítava polia hodnôt obsahujúcich číselné údaje, a súhrnnú funkciu Count, pomocou ktorej vypočítava údajové polia obsahujúce text. Ak chcete ďalej analyzovať a prispôsobiť údaje, môžete vybrať inú súhrnnú funkciu, napríklad Average, Max alebo Min. Vytvorením vypočítavaného poľa alebo vypočítavanej položky v rámci poľa môžete tiež vytvoriť vlastné vzorce, ktoré používajú prvky zostavy alebo iné údaje z pracovného hárka.

Vzorce možno vytvárať len v zostavách založených na zdrojových údajoch iného typu než OLAP. V zostavách založených na databáze OLAP vzorce nie je možné používať. Pri používaní vzorcov v kontingenčných tabuľkách by ste mali poznať nasledujúce pravidlá syntaxe a správanie vzorcov:

• Prvky vzorcov kontingenčnej tabuľky.    Vo vzorcoch vytvorených pre vypočítavané polia a vypočítavané položky môžete používať operátory a výrazy rovnako ako v iných vzorcoch pracovného hárka. Môžete používať konštanty a odkazovať na údaje zo zostavy, nemôžete však používať odkazy na bunky ani definované názvy. Nemôžete používať funkcie pracovného hárka, ktoré ako argumenty vyžadujú odkazy na bunky alebo definované názvy, a takisto nemôžete používať funkcie poľa.

• Názvy polí a položiek.    Na identifikovanie prvkov zostavy vo vzorcoch používa Excel názvy polí a položiek. V nasledujúcom príklade používajú údaje v rozsahu C3:C9 názov poľa Mlieko. Vypočítavaná položka v poli Typ, ktorá odhaduje predaj nového výrobku na základe predaja mlieka, môže používať napríklad vzorec =Mlieko * 115%.

  

  Poznámka: V kontingenčnom grafe sa názvy polí zobrazujú v zozname polí kontingenčnej tabuľky a názvy položiek možno zobraziť v rozbaľovacích zoznamoch jednotlivých položiek. Tieto názvy si nezamieňajte s názvami zobrazovanými v popisoch grafu, ktoré predstavujú názvy radov a údajových bodov.

• Vzorce pracujú z celkovými súčtami, nie s jednotlivými záznamami    Vzorce pre vypočítavané polia pracujú s celkovým súčtom príslušných údajov v poliach vo vzorci. Napríklad vzorec pre vypočítavané pole =Predaj * 1,2 násobí celkový súčet predaja pre jednotlivé typy a oblasti hodnotou 1,2. Nepostupuje tak, že by najskôr vynásobil jednotlivé hodnoty predaja hodnotou 1,2 a potom vynásobené množstvá spočítal.

  Vzorce pre vypočítavané položky pracujú s jednotlivými záznamami. Napríklad vzorec pre vypočítavanú položku =Mlieko *115% násobí jednotlivé hodnoty predaja mlieka hodnotou 115 % a vynásobené množstvá potom zobrazuje ako súhrn v oblasti hodnôt.

• Medzery, čísla a symboly v názvoch.    V názve, ktorý zahŕňa viac ako jedno pole, môžu byť polia uvedené v ľubovoľnom poradí. Vo vyššie uvedenom prípade môžu byť bunky C6:D6 uvedené ako 'Apríl Sever' alebo 'Sever Apríl'. Názvy obsahujúce viaceré slová, čísla alebo symboly uveďte v jednoduchých úvodzovkách.

• Celkové súčty.    Vzorce nemôžu odkazovať na súčty (napríklad Marec súčet, Apríl súčet a Celkový súčet v príklade).

• Názvy polí v odkazoch na položky    Do odkazu na položku môžete zahrnúť názov poľa. Názov položky musí byť v hranatých zátvorkách, napríklad Oblasť[Sever]. Pomocou tohto formátu sa vyhnete chybám typu #NÁZOV?, ak majú dve položky v dvoch rôznych poliach zostavy rovnaký názov. Ak napríklad zostava obsahuje v poli Typ položku s názvom Mäso a ďalšiu položku s názvom Mäso v poli Kategória, chybám typu #NÁZOV? sa možno vyhnúť odkazovaním na tieto položky ako na Typ[Mäso] a Kategória[Mäso].

• Odkazovanie na položky podľa umiestnenia.    Na položku môžete odkazovať podľa jej umiestnenia v zostave podľa aktuálneho zoradenia a zobrazenia. Typ[1] je Mlieko a Typ[2] je Morské ryby. Položka, na ktorú sa odkazuje týmto spôsobom, sa môže zmeniť pri každej zmene umiestnenia položiek, alebo zobrazení alebo skrytí iných položiek. Skryté položky sa do tohto registra nezahŕňajú.

  Na položky môžete odkazovať pomocou relatívnych umiestnení. Umiestnenia sa určujú relatívne k vypočítavanej položke obsahujúcej vzorec. Ak je aktívnou oblasťou Juh, Oblasť[-1] je Sever. Ak je aktívnou oblasťou Sever, Oblasť[+1] je Juh. Pre vypočítavanú položku sa môže použiť napríklad vzorec =Oblasť[-1] * 3%. Pre vypočítavanú položku sa môže použiť napríklad vzorec =Oblasť[-1] * 3%. Ak je zadané umiestnenie pred prvou alebo za poslednou položkou v poli, výsledkom vzorca bude chybová hodnota #ODKAZ!.

Pri používaní vzorcov v kontingenčnom grafe vytvoríte vzorce v priradenej kontingenčnej tabuľke, kde sú zobrazené jednotlivé hodnoty tvoriace údaje, a výsledky zobrazíte graficky v kontingenčnom grafe.

Nasledujúci príklad kontingenčného grafu zobrazuje predaj jednotlivých predajcov v danej oblasti:

Ak chcete zistiť, ako by predaj vyzeral, ak by sa zvýšil o 10 percent, môžete v priradenej kontingenčnej tabuľke vytvoriť vypočítavané pole, ktoré používa napríklad vzorec =Predaj * 110%.

Výsledok sa okamžite zobrazí v kontingenčnej tabuľke, ako je to znázornené v nasledujúcom grafe:

Ak chcete zobraziť jednotlivé údajové značky pre predaj v severnej oblasti znížený o 8 percent dopravných nákladov, môžete vytvoriť vypočítavanú položku v poli Oblasť, ktorá používa napríklad vzorec =Sever – (Sever * 8%).

Výsledný graf bude vyzerať takto:

Vypočítavaná položka vytvorená v poli Predajca však môže v legende predstavovať rady a v grafe sa zobrazuje ako údajový bod v každej kategórii.

Skôr než začnete, rozhodnite sa, či chcete v rámci poľa vytvoriť vypočítavané pole alebo vypočítavanú položku. Vypočítavané pole použite, ak chcete vo vzorci použiť údaje z iného poľa. Vypočítavanú položku použite, ak má vzorec používať údaje z jedného alebo viacerých konkrétnych položiek v rámci poľa.

Pri použití vypočítavaných položiek môžete do jednotlivých buniek zadať rôzne vzorce. Ak má vypočítavaná položka s názvom PomarančovýKraj v rámci všetkých mesiacov vzorec =Pomaranče * ,25, môžete tento vzorec zmeniť pre jún, júl a august na =Pomaranče *,5.

Ak máte viacero vypočítavaných položiek alebo vzorcov, môžete upraviť poradie výpočtov.

Pridanie vypočítavaného poľa

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

   Zobrazí sa ponuka Nástroje pre kontingenčné tabuľky s kartami Analyzovať a Návrh.

2. Na karte Analyzovať kliknite v skupine Výpočty na položku Polia, položky a množiny a potom kliknite na položku Vypočítavané pole.

   

3. Do poľa Názov zadajte názov poľa.

4. Do poľa Vzorec zadajte vzorec pre pole.

   Ak chcete vo vzorci použiť údaje z iného poľa, kliknite v poli Polia na požadované pole a potom kliknite na položku Vložiť pole. Ak chcete napríklad v poli Predaj vypočítať 15-percentnú províziu pre každú hodnotu, môžete zadať vzorec = Predaj * 15%.

5. Kliknite na tlačidlo Pridať.

Pridanie vypočítavanej položky do poľa

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

   Zobrazí sa ponuka Nástroje pre kontingenčné tabuľky s kartami Analyzovať a Návrh.

2. Ak sú položky v poli zoskupené, na karte Analyzovať kliknite v skupine Zoskupiť na položku Oddeliť.

   

3. Kliknite na pole, do ktorého chcete pridať vypočítavanú položku.

4. Na karte Analyzovať kliknite v skupine Výpočty na položku Polia, položky a množiny a potom kliknite na položku Vypočítavaná položka.

   

5. Do poľa Názov zadajte názov vypočítavanej položky.

6. Do poľa Vzorec zadajte vzorec pre položku.

   Ak chcete vo vzorci použiť údaje z položky, kliknite v zozname Položky na požadovanú položku a potom kliknite na položku Vložiť položku (položka musí pochádzať z rovnakého poľa ako vypočítavaná položka).

7. Kliknite na tlačidlo Pridať.

Zadanie rôznych vzorcov pre vypočítavané položky do jednotlivých buniek

1. Kliknite na bunku, v ktorej chcete zmeniť vzorec.

   Ak chcete zmeniť vzorec vo viacerých bunkách, pri klikaní na ďalšie bunky podržte kláves CTRL.

2. Prejdite na riadok vzorcov a zadajte zmenený vzorec.

Úprava poradia výpočtov pre viaceré vypočítavané položky alebo vzorce

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

   Zobrazí sa ponuka Nástroje pre kontingenčné tabuľky s kartami Analyzovať a Návrh.

2. Na karte Analyzovať kliknite v skupine Výpočty na položku Polia, položky a množiny a potom kliknite na položku Poradie riešenia.

   

3. Kliknite na vzorec a potom na položku Posunúť nahor alebo Posunúť nadol.

4. Pokračujte, až kým vzorce nebudú v poradí, v akom sa majú vypočítavať.

V prípade potreby môžete zobraziť zoznam všetkých vzorcov použitých v aktuálnej kontingenčnej tabuľke.

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

   Zobrazí sa ponuka Nástroje pre kontingenčné tabuľky s kartami Analyzovať a Návrh.

2. Na karte Analyzovať kliknite v skupine Výpočty na položku Polia, položky a množiny a potom kliknite na položku Zoznam vzorcov.

   

Pred úpravou vzorca určite, či sa vzorec nachádza vo vypočítavanom poli alebo vypočítavanej položke. Ak je vzorec vo vypočítavanej položke, určite, či je to jediný vzorec pre danú položku.

Pri používaní vypočítavaných položiek môžete upraviť jednotlivé vzorce v konkrétnych bunkách vypočítavanej položky. Ak má vypočítavaná položka s názvom VýpPomarančov v rámci všetkých mesiacov vzorec =Pomaranče * ,25, môžete tento vzorec zmeniť pre jún, júl a august na =Pomaranče *,5.

Určenie, či sa vzorec nachádza vo vypočítavanom poli alebo vypočítavanej položke

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

   Zobrazí sa ponuka Nástroje pre kontingenčné tabuľky s kartami Analyzovať a Návrh.

2. Na karte Analyzovať kliknite v skupine Výpočty na položku Polia, položky a množiny a potom kliknite na položku Zoznam vzorcov.

   

3. V zozname vzorcov vyhľadajte v časti Vypočítavané pole alebo Vypočítavaná položka vzorec, ktorý chcete zmeniť.
   
   Ak pre vypočítavanú položku existuje viacero vzorcov, má predvolený vzorec, ktorý bol zadaný, keď sa vytvárala položka, názov vypočítavanej položky v stĺpci B. Pre ďalšie vzorce pre vypočítavanú položku obsahuje stĺpec B názov vypočítavanej položky a názvy položiek s prienikom.

   Pre vypočítavanú položku s názvom Položka môžete mať napríklad predvolený vzorec a aj ďalší vzorec označený ako Položka – predaj za január. V kontingenčnej tabuľke by ste tento vzorec našli v bunke Predaj pre riadok Položka a stĺpec Január.

4. Pokračujte použitím jedného z nasledujúcich spôsobov úpravy.

Úprava vzorca vypočítavaného poľa

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

   Zobrazí sa ponuka Nástroje pre kontingenčné tabuľky s kartami Analyzovať a Návrh.

2. Na karte Analyzovať kliknite v skupine Výpočty na položku Polia, položky a množiny a potom kliknite na položku Vypočítavané pole.

   

3. V poli Názov vyberte vypočítavané pole, ktorého vzor chcete zmeniť.

4. Upravte vzorec v poli Vzorec.

5. Kliknite na tlačidlo Upraviť.

Úprava jedného vzorca pre vypočítavanú položku

1. Kliknite na pole obsahujúce vypočítavanú položku.

2. Na karte Analyzovať kliknite v skupine Výpočty na položku Polia, položky a množiny a potom kliknite na položku Vypočítavaná položka.

   

3. V poli Názov vyberte vypočítavanú položku.

4. Upravte vzorec v poli Vzorec.

5. Kliknite na tlačidlo Upraviť.

Úprava individuálneho vzorca pre určitú bunku vypočítavanej položky

1. Kliknite na bunku, v ktorej chcete zmeniť vzorec.

   Ak chcete zmeniť vzorec vo viacerých bunkách, pri klikaní na ďalšie bunky podržte kláves CTRL.

2. Prejdite na riadok vzorcov a zadajte zmenený vzorec.

   Tip: Ak máte viacero vypočítavaných položiek alebo vzorcov, môžete upraviť poradie výpočtov. Ďalšie informácie nájdete v časti Úprava poradia výpočtov pre viaceré vypočítavané položky alebo vzorce.

1. Určite, či sa vzorec nachádza vo vypočítavanom poli alebo vypočítavanej položke.

   Vypočítavané polia sa zobrazujú v zozname polí kontingenčnej tabuľky. Vypočítavané položky sa zobrazujú ako položky v rámci iných polí.

2. Použite niektorý z nasledujúcich postupov:

   • Ak chcete odstrániť vypočítavané pole, kliknite na ľubovoľné miesto v kontingenčnej tabuľke.

   • Ak chcete odstrániť vypočítavanú položku, v kontingenčnej tabuľke kliknite na pole obsahujúce položku, ktorú chcete odstrániť.

     Zobrazí sa ponuka Nástroje pre kontingenčné tabuľky s kartami Analyzovať a Návrh.

3. Na karte Analyzovať kliknite v skupine Výpočty na položku Polia, položky a množiny a potom kliknite na položku Vypočítavané pole alebo Vypočítavaná položka.

   

4. V poli Názov vyberte pole alebo položku, ktorú chcete odstrániť.

5. Kliknite na tlačidlo Odstrániť.