Excel

Kontingenčné tabuľky programu Excel

Excel Pivot Tables

Prehľad | Prečo Pivot? | Tipy | Príklady | Školenia

Kontingenčné tabuľky sú jednou z najsilnejších a najužitočnejších funkcií v programe Excel. S veľmi malým úsilím môžete pomocou kontingenčnej tabuľky vytvárať dobre vyzerajúce zostavy pre veľké množiny údajov. Tento článok je úvodom do kontingenčných tabuliek a ich výhod a podrobným návodom so vzorovými údajmi.



Chyť vzorové údaje a skús to. Učenie kontingenčných tabuliek je zručnosť, ktorá sa vám vráti znova a znova. Kontingenčné tabuľky môžu výrazne zvýšiť vašu efektivitu v programe Excel.

Čo je to kontingenčná tabuľka?

Kontingenčnú tabuľku si môžete predstaviť ako zostavu. Na rozdiel od statickej zostavy však kontingenčná tabuľka poskytuje súbor interaktívne zobrazenie vašich údajov . S veľmi malým úsilím (a bez vzorcov) sa môžete pozrieť na rovnaké údaje z mnohých rôznych perspektív . Údaje môžete zoskupovať do kategórií, rozdeľovať údaje na roky a mesiace, filtrovať údaje na zahrnutie alebo vylúčenie kategórií a dokonca vytvárať grafy.





Krása kontingenčných tabuliek spočíva v tom, že vám umožňujú interaktívne skúmať vaše údaje rôznymi spôsobmi.

Obsah

Návod krok za krokom

Aby ste pochopili kontingenčné tabuľky, musíte s nimi pracovať sami. V tejto sekcii vytvoríme niekoľko kontingenčných tabuliek krok za krokom zo sady vzorových údajov. So skúsenosťami je možné nižšie uvedené kontingenčné tabuľky zostaviť asi za 5 minút.



Ukážkové údaje

The vzorové údaje obsahuje 452 záznamov s 5 poliami informácií: dátum, farba, jednotky, tržby a región. Tieto údaje sú ideálne pre kontingenčnú tabuľku.

Ukážkové údaje o predaji už nájdete v tabuľke programu Excel

Údaje v riadnom Excel tabuľka s názvom „Tabuľka1“. Tabuľky programu Excel sú a skvelý spôsob, ako vytvoriť kontingenčné tabuľky , pretože sa automaticky upravujú pri pridávaní alebo odstraňovaní údajov.

Poznámka: Viem, že tieto údaje sú veľmi všeobecné. Na pochopenie kontingenčných tabuliek sú však dobré všeobecné údaje - nechcete, aby ste pri učení zábavných častí zakopli o detail.

Vložiť kontingenčnú tabuľku

1. Ak chcete začať, zvoľte ľubovoľná bunka v údajoch a kliknite na položku Kontingenčná tabuľka na karte Vložiť na páse s nástrojmi:

Click the button at Insert>Kontingenčná tabuľka Kontingenčná tabuľka' />

Excel zobrazí okno Vytvoriť kontingenčnú tabuľku. Všimnite si toho, že rozsah údajov je už vyplnený. Predvolené umiestnenie novej kontingenčnej tabuľky je Nový pracovný hárok.

2. Prepíšte predvolené umiestnenie a zadajte H4, čím umiestnite kontingenčnú tabuľku do aktuálneho pracovného hárka:

Okno Vytvoriť kontingenčnú tabuľku

3. Kliknite na tlačidlo OK a Excel vytvorí prázdnu kontingenčnú tabuľku začínajúc v bunke H4.

Nová prázdna kontingenčná tabuľka hľadí do bunky H4

Poznámka: Existujú dobré dôvody na umiestnenie kontingenčnej tabuľky na iný pracovný hárok. Pri učení kontingenčných tabuliek je však užitočné vidieť zdrojové údaje aj kontingenčnú tabuľku súčasne.

Excel tiež zobrazuje tablu Polia kontingenčnej tabuľky, ktorá je v tomto mieste prázdna. Upozorňujeme, že je uvedených všetkých päť polí, ale nie sú použité:

Tabla polí pre novú prázdnu kontingenčnú tabuľku

Ak chcete vytvoriť kontingenčnú tabuľku, presuňte polia do jednej oblasti Stĺpce, Riadky alebo Hodnoty. Oblasť Filtre sa používa na aplikáciu globálnych filtrov na kontingenčnú tabuľku.

Poznámka: Tabla s poliami kontingenčnej tabuľky ukazuje, ako boli polia použité na vytvorenie kontingenčnej tabuľky. Naučiť sa „čítať“ tabuľu polí chce trocha praxe. Pozri nižšie a aj tu pre ďalšie príklady.

Pridajte polia

1. Potiahnite pole Predaj do oblasti Hodnoty.

Program Excel vypočíta celkový súčet, 26356. Toto je súčet všetkých hodnôt predaja v celom súbore údajov:

Celkový súčet všetkých údajov v súbore údajov

2. Potiahnite pole Farba do oblasti Riadky.

Excel rozdeľuje predaj podľa farby. Môžete vidieť, že modrý je najpredávanejší, zatiaľ čo červený je na poslednom mieste:

Rozdelenie podľa farby

Všimnite si, že celkový súčet zostáva 26356. To dáva zmysel, pretože stále podávame správy o úplnom súbore údajov.

V tomto mieste sa pozrime na tablu polí. Môžete vidieť pole Farba je riadok a Predaj je pole hodnoty:

Tabla polí kontingenčnej tabuľky - predaj podľa farby

Formátovanie čísel

Kontingenčné tabuľky môžu automaticky používať a udržiavať formátovanie čísel na číselné polia. Toto je veľká úspora času, keď sa údaje často menia.

1. Kliknite pravým tlačidlom myši na akékoľvek predajné číslo a zvoľte Formát čísla:

Kliknite pravým tlačidlom myši a zvoľte Formát čísla

2. Použite formátovanie meny s nulovými desatinnými miestami, kliknite na tlačidlo OK:

Formát čísla meny s nulovými desatinnými miestami

Vo výslednej kontingenčnej tabuľke majú všetky hodnoty predaja aplikovaný formát meny:

Kontingenčná tabuľka s použitým formátom meny

Formát meny sa bude aj naďalej používať na hodnoty predaja, aj keď sa kontingenčná tabuľka prekonfiguruje alebo sa pridajú nové údaje.

Zoradenie podľa hodnoty

1. Kliknite pravým tlačidlom myši na ľubovoľnú hodnotu predaja a zvoľte Zoradiť> Od najväčšej po najmenšiu.

Right-click and select Sort>Od najväčších po najmenšie Od najväčších po najmenšie' />

Excel teraz uvádza zoznam najpredávanejších farieb ako prvý. Toto poradie zoradenia bude zachované pri zmene údajov alebo pri prekonfigurovaní kontingenčnej tabuľky.

Rozdelenie podľa farby, najskôr najpredávanejšie farby

Obnoviť údaje

Aby bolo možné poskytovať aktualizácie, je potrebné údaje v kontingenčnej tabuľke „aktualizovať“. Aby sme posilnili fungovanie tohto systému, urobíme veľkú zmenu zdrojových údajov a budeme sledovať, ako sa prenášajú do kontingenčnej tabuľky.

1. Vyberte bunku F5 a zmeňte 11,00 $ na 2 000 $.

2. Kliknite pravým tlačidlom myši kdekoľvek v kontingenčnej tabuľke a vyberte položku „Obnoviť“.

Ak chcete aktualizovať údaje, kliknite pravým tlačidlom myši a zvoľte

Všimnite si, že „červená“ je teraz najpredávanejšou farbou a automaticky sa presúva na začiatok:

Kontingenčná tabuľka po obnovení údajov

3. Zmeňte F5 späť na 11,00 $ a znova obnovte čap.

index vyhovuje viacerým kritériám bez poľa

Poznámka: zmena F5 na 2 000 dolárov nie je realistická, ale je to dobrý spôsob, ako vynútiť zmenu, ktorú môžete ľahko vidieť v kontingenčnej tabuľke. Skúste zmeniť existujúcu farbu na novú, napríklad „zlatú“ alebo „čiernu“. Keď obnovíte, zobrazí sa nová farba. Ak sa chcete vrátiť k pôvodným údajom a otočiť ich, môžete použiť príkaz Späť.

Druhé hodnotové pole

Ako pole hodnoty môžete pridať viac ako jedno pole.

1. Potiahnutím jednotiek do oblasti hodnôt zobrazíte predaj a jednotky spoločne:

Rozdelenie podľa farby podľa tržieb a jednotiek

Percento z celkového počtu

Existujú rôzne spôsoby zobrazenia hodnôt. Jednou z možností je zobraziť hodnoty ako percento z celkového počtu. Ak chcete rovnaké pole zobraziť rôznymi spôsobmi, pridajte pole dvakrát.

1. Odstráňte jednotky z oblasti Hodnoty

2. Pridajte pole Predaj (znova) do oblasti Hodnoty.

3. Kliknite pravým tlačidlom myši na druhú inštanciu a zvoľte '% celkového súčtu':

Right-click select Show values as>percent z celkového počtu „> percent z celkového počtu“ /></p> <p>Výsledkom je rozdelenie podľa farby spolu s percentom z celkového počtu:</p> <p> <img src=

Poznámka: formát čísla v percentách bol tiež upravený tak, aby zobrazoval 1 desatinné miesto.

V tomto mieste je tabla Polia:

Tabla polí kontingenčnej tabuľky - predaj podľa farby s percentom

Zoskupiť podľa dátumu

Kontingenčné tabuľky majú špeciálnu funkciu na zoskupovanie dátumov do jednotiek, ako sú roky, mesiace a štvrťroky. Toto zoskupenie je možné prispôsobiť.

1. Odstráňte druhé pole Predaj (Predaj2).

2. Potiahnite pole Dátum do oblasti Stĺpce.

3. Kliknite pravým tlačidlom myši na dátum v oblasti hlavičky a zvoľte „Skupina“:

Pravým tlačidlom myši kliknite na dátum a vyberte skupinu

4. Keď sa zobrazí okno Skupina, zoskupte iba podľa rokov (zrušte výber mesiacov a štvrťrokov):

Nastavenia zoskupenia dátumov - zoskupiť iba podľa rokov

Teraz máme kontingenčnú tabuľku, ktorá zoskupuje predaj podľa farby a roku:

Dvojsmerná kontingenčná tabuľka - predaj podľa farby a roku

Všimnite si, že v rokoch 2016 a 2017 nie sú žiadne predaje striebra. Môžeme hádať, že striebro bolo predstavené ako nová farba v roku 2018. Kontingenčné tabuľky často odhaľujú vzory v dátach, ktoré je inak ťažké vidieť.

V tomto mieste je tabla Polia:

Tabla polí kontingenčnej tabuľky - predaj podľa farby a podľa roku

Obojsmerný pivot

Kontingenčné tabuľky môžu vykresľovať údaje v rôznych dvojrozmerných usporiadaniach.

1. Potiahnite pole Dátum z oblasti stĺpcov

2. Potiahnite oblasť do oblasti Stĺpce.

Excel vytvára obojsmernú kontingenčnú tabuľku, ktorá rozdeľuje predaj podľa farby a oblasti:

Obojsmerná kontingenčná tabuľka - predaj podľa farby a oblasti

3. Vymeňte oblasť a farbu (t.j. potiahnite oblasť do oblasti Riadky a Farba do oblasti Stĺpce).

Excel vytvára ďalšiu dvojrozmernú kontingenčnú tabuľku:

Obojsmerná kontingenčná tabuľka - predaj podľa regiónu a farby

Opäť upozorňujeme, že celkové tržby (26 356 dolárov) sú rovnaké v všetky kontingenčné tabuľky vyššie . Každá tabuľka predstavuje iný pohľad na rovnaké údaje , takže všetci súčet k celkom rovnaký .

Vyššie uvedený príklad ukazuje, ako rýchlo môžete z rovnakých údajov vytvoriť rôzne kontingenčné tabuľky. Môžete vytvárať mnoho ďalších typov kontingenčných tabuliek , pričom sa používajú všetky druhy údajov.

Kľúčové výhody kontingenčnej tabuľky

Jednoduchosť . Základné kontingenčné tabuľky sú veľmi jednoduché na nastavenie a prispôsobenie. Nie je potrebné učiť sa zložité vzorce.

Rýchlosť . Dobre vyzerajúci a užitočný prehľad s kontingenčnou tabuľkou môžete vytvoriť za niekoľko minút. Aj keď ste veľmi dobrý vo vzorcoch, kontingenčné tabuľky sa nastavujú rýchlejšie a vyžadujú si oveľa menej úsilia .

Flexibilita . Na rozdiel od vzorcov vás kontingenčné tabuľky nezablokujú v konkrétnom zobrazení vašich údajov. Kontingenčný stolík môžete rýchlo prestavať tak, aby vyhovoval vašim potrebám. Môžete dokonca klonovať kontingenčnú tabuľku a vytvorte samostatný pohľad.

Presnosť . Pokiaľ je kontingenčná tabuľka nastavená správne, môžete si byť istí, že výsledky sú presné. V skutočnosti kontingenčná tabuľka často upozorní na problémy s údajmi rýchlejšie než ktorýkoľvek iný nástroj.

Formátovanie . Kontingenčnú tabuľku je možné použiť automaticky pri použití konzistentného formátovania čísel a štýlov, a to aj pri zmenách údajov.

Aktualizácie . Kontingenčné tabuľky sú navrhnuté pre priebežné aktualizácie. Ak založíte kontingenčnú tabuľku na Excelovej tabuľke, veľkosť tabuľky sa podľa potreby zmení s novými údajmi. Všetko, čo musíte urobiť, je kliknúť na položku Obnoviť , a vaša kontingenčná tabuľka vám ukáže najnovšie.

Filtrovanie . Kontingenčné tabuľky obsahujú niekoľko nástrojov na filtrovanie údajov. Potrebujete sa pozrieť na Severnú Ameriku a Áziu, ale vylúčiť Európu? Kontingenčná tabuľka to zjednodušuje.

Grafy . Keď máte kontingenčnú tabuľku, môžete ľahko vytvorte kontingenčný graf .

Viac zdrojov kontingenčnej tabuľky

Autor Dave Bruns Prílohy Súbor vzorové údaje pre kontingenčnú tabuľku.xlsx


^