Kontingenčné tabuľky sú jednou z najvýkonnejších funkcií programu Excel. Aj keď ste nováčik, veľké množstvo údajov môžete skrížiť na užitočné informácie. Kontingenčná tabuľka vám môže pomôcť vytvoriť správy za niekoľko minút. Jednoducho analyzujte svoje čisté údaje a ak údaje nie sú čisté, môže vám to pomôcť pri ich čistení. Nechcem vás nudiť, tak sa na to vrhneme a preskúmame.
Ako vytvoriť kontingenčnú tabuľku
Je to jednoduché. Stačí si vybrať svoje údaje. Prejdite na položku Vložiť. Kliknite na kontingenčnú tabuľku a je to.
Ale počkaj. Pred vytvorením kontingenčnej tabuľky sa uistite, že všetky stĺpce majú nadpis.
Ak niektorý nadpis stĺpca zostane prázdny, kontingenčná tabuľka sa nevytvorí a bude sa zobrazovať chybové hlásenie.
Požiadavka 1: Všetky stĺpce by mali mať nadpis, aby ste mohli začať s kontingenčnými tabuľkami v programe Excel
Vaše údaje by ste mali mať usporiadané so správnym nadpisom. Akonáhle to máte, môžete vložiť kontingenčnú tabuľku.
Vložte kontingenčnú tabuľku z pásky
Ak chcete vložiť kontingenčnú tabuľku z ponuky, postupujte takto:
1. Vyberte rozsah údajov
2. Prejdite na kartu vkladania
3. Kliknite na ikonu kontingenčnej tabuľky.
4. Zobrazí sa okno s možnosťou Vytvoriť kontingenčnú tabuľku.
5. Tu vidíte rozsah údajov, ktorý ste vybrali. Ak si myslíte, že toto nie je rozsah, ktorý chcete vybrať, zmeňte priamo svoj rozsah tu. Vráťte sa späť a znova vyberte údaje.
6. Ďalej si môžete vybrať, kde chcete svoju kontingenčnú tabuľku. Odporúčam použiť nový pracovný hárok, ale môžete použiť aj aktuálny pracovný hárok. Stačí definovať polohu v poli Poloha.
7. Teraz, keď ste s nastaveniami hotoví, kliknite na tlačidlo OK. Kontingenčnú tabuľku budete mať v novom hárku. Stačí vybrať polia pre súhrny. Uvidíme, ako vytvoríme súhrn údajov pomocou kontingenčnej tabuľky, ale najskôr si ujasnime základy. V tomto tutoriále o excelovej kontingenčnej tabuľke sa naučíte viac, ako by ste očakávali.
Vložiť skratku kontingenčnej tabuľky (Alt> N> V)
Toto je sekvenčná klávesová skratka na otvorenie okna Vytvoriť Kontingenčná tabuľka voliteľný box.
Kliknite na tlačidlo Alt a uvoľnite ho. Hit N. a pusti to. Hit V. a pusti to. Otvorí sa pole Vytvoriť kontingenčnú tabuľku.
Teraz postupujte podľa vyššie uvedeného postupu a vytvorte kontingenčnú tabuľku v programe Excel.
Vložiť skratku kontingenčnej tabuľky pomocou starej skratky programu Excel (Alt> D> P)
Jedna vec, ktorá sa mi na programe Microsoft Excel páči najviac, je, že v každej novej verzii programu Excel zavádzajú nové funkcie, ale nie sú nevyhadzujte staré funkcie (ako to urobil MS s win 8. Bolo to úbohé). Toto umožňuje starým používateľom pracovať normálne na nových verziách tak, ako pracovali na starších verziách.
Ak postupne stlačíte ALT, D a P na klávesnici sa otvorí Excel a vytvorí sprievodcu kontingenčnou tabuľkou.
Vyberte príslušnú možnosť. Vybraná možnosť na vyššie uvedenej snímke obrazovky nás dovedie k vytvoreniu kontingenčnej tabuľky, ako sme ju vytvorili predtým.
Ak chcete skontrolovať vybraný rozsah, stlačte kláves Enter alebo kliknite na Ďalej.
Znova stlačte Enter.
Vyberte nový pracovný hárok alebo kdekoľvek chcete, aby vaša kontingenčná tabuľka stlačila kláves Enter. A je hotovo.
Vytvárajte zostavy pomocou kontingenčných tabuliek
Teraz viete, ako vložiť kontingenčnú tabuľku. Začnime s vytváraním prehľadov pomocou kontingenčných tabuliek v priebehu niekoľkých minút.
Máme údaje pre stacionárnu objednávku.
Polia stĺpcov sú:
Dátum objednávky: Dátum objednávky (samozrejme)
Región: Región poriadku v krajine
Zákazník: Meno zákazníka (čo to ešte môže byť)
Položka: Objednaný tovar
Jednotka: Počet jednotiek objednanej položky
Jednotkové náklady: Na jednotkové náklady
Celkom: Celkové náklady na objednávku (jednotka*jednotkové náklady).
Na vytvorenie zostáv pomocou kontingenčných tabuliek použijeme
Polia kontingenčnej tabuľky: Obsahuje zoznam názvov stĺpcov vo vašich údajoch.
Kontingenčné oblasti: Tieto 4 oblasti slúžia na zobrazenie vašich údajov spôsobom.
FILTRE: Sem zadajte polia, z ktorých chcete vo svojom prehľade použiť filtre.
COLUMNS: Sem vložte požadované stĺpce do stĺpcov v správe: (Je lepšie ukázať, ako vysvetliť)
RIADKY: Pretiahnite polia, ktoré chcete zobraziť surovo, ako na obrázku vyššie, v ROWS som ukázal oblasť.
HODNOTY: Vyberte pole a získajte počet, súčet, priemer, percento (a mnoho ďalších) atď., Ktoré chcete vidieť.
Na základe vyššie uvedených informácií sme pripravili túto rýchlu kontingenčnú správu, ktorá ukazuje, z ktorého regiónu sa zadáva počet objednávok pre každú položku.
Teraz, keď rozumiete svojim údajom a kontingenčným poliam (koniec koncov ste múdri), odpovedzme si rýchlo na niektoré otázky súvisiace s týmito údajmi pomocou kontingenčnej tabuľky.
Q1. Koľko je objednávok?
Kontingenčná tabuľka je spočiatku prázdna, ako je znázornené na obrázku nižšie.
Ak chcete zobraziť súhrn alebo podrobnosti o tomto poli, musíte vybrať polia (názvy stĺpcov) v príslušných oblastiach.
Teraz, aby som odpovedal na vyššie uvedenú otázku, vyberiem položku (v ľubovoľnom stĺpci, uistite sa, že medzi nimi nie je prázdna bunka).
Vyberte položku zo zoznamu polí a presuňte ju do poľa Hodnota.
Máme svoju odpoveď. Kontingenčné tabuľky mi hovoria, že existuje celkom 43 objednávok. Toto je správne.
Teraz, keď som si vybral Dátum objednávky v oblasti Hodnoty to ukazuje 42. Toto prostriedky Dátum objednávky má prázdnu bunku od vieme, že celkový počet objednávok je 43.
Identifikujte nepravidelné údaje pomocou kontingenčných tabuliek a vyčistite ich.
Kontingenčná tabuľka vám môže pomôcť nájsť v údajoch nesprávne informácie.
Väčšinu času sú naše údaje pripravené operátormi zadávania údajov alebo užívateľmi, ktorí sú zvyčajne nepravidelní a na prípravu presnej správy a analýzy je potrebné ich vyčistiť.
Predtým, ako pripravíte akékoľvek správy, mali by ste svoje údaje vždy vyčistiť a pripraviť ich upraveným spôsobom. Niekedy však až po príprave správy zistíme, že naše údaje majú určitú nezrovnalosť. Príďte, ukážem vám, ako …
Q2: Povedzte počet objednávok z každého regiónu
Teraz odpovedzte na túto otázku:
Vyberte Región a presuňte ho na Riadky Plocha a Položka do Hodnoty Oblasť.
Získame rozdelené údaje Region-Wise. Môžeme odpovedať, z ktorého regiónu prišlo koľko zákaziek.
Podľa kontingenčnej tabuľky sú v našich údajoch celkom 4 oblasti. Ale počkajte, všimnite si to Centrálne a centrálne regiónu. Vieme, že Centrle by mal byť Centrálne. Vyskytla sa nezrovnalosť. Musíme ísť k svojim údajom a vykonať ich čistenie.
Na vyčistenie údajov v poli regiónu odfiltrujeme nesprávny názov oblasti (Centrle) a opravíme ho (Central).
Teraz sa vráťte k svojim kontingenčným údajom.
Kliknite pravým tlačidlom myši kdekoľvek v kontingenčnej tabuľke a kliknite na položku Obnoviť.
Váš prehľad bol teraz aktualizovaný.
Teraz vidíte, že v skutočnosti existujú iba 3 regióny.
Formátovanie kontingenčnej správy s kategorizovanými riadkami.
Niekedy budete potrebovať správy ako na obrázku vyššie. Vďaka tomu je ľahké zobraziť vaše údaje štruktúrovaným spôsobom. Ľahko zistíte, z ktorého regiónu je objednaný počet položiek. Pozrime sa, ako to môžete urobiť.
Pohyb Región a Položka do RIADKY oblasť. Uistite sa, že je región v hornej časti a položky v spodnej časti, ako je znázornené na obrázku.
Potiahnite Položka pre Hodnota Oblasť.
V dôsledku toho dostanete túto správu.
To bude stačiť Váš šéf však niekedy chce podať správu v tabuľkovej forme bez medzisúčtov. Na to musíme naformátovať našu kontingenčnú tabuľku.
Odstráňte medzisúčty z kontingenčnej tabuľky
Nasleduj tieto kroky:
1. Kliknite kdekoľvek na svoju kontingenčnú tabuľku.
2. Prejdite na Dizajn Tab.
3. Kliknite na medzisúčty Ponuka.
4. Kliknite na Nezobrazovať medzisúčty.
Môžete vidieť, že teraz neexistujú žiadne medzisúčty.
Dobre. Ale stále to nie je v tabuľkovej forme. Regióny a položky sú zobrazené v jednom stĺpci. Ukážte ich oddelene.
Vytvorte z kontingenčnej tabuľky tabuľku
Teraz, aby ste zobrazili oblasti a položky v rôznych stĺpcoch, postupujte podľa týchto krokov:
1. Kliknite kdekoľvek na kontingenčnú tabuľku
2. Prejdite na kartu Návrh
3. Kliknite na Rozloženie správy.
4. Kliknite na položku Zobraziť pre možnosť Tabuľkový formulár. Nakoniec budete mať tento prepracovaný pohľad na vašu správu.
Teraz poznáme celkový počet objednávok zadaných pre každú položku z každého regiónu. Je to zobrazené v grófovi položiek.
Zmeňte názov stĺpca na Objednávky.
Teraz to vyzerá lepšie.
Q3: Koľko jednotiek z každej položky je objednaných?
Na zodpovedanie tejto otázky potrebujeme súčet jednotiek. Ak to chcete urobiť, presuňte pole Jednotky do Hodnoty. Automaticky sa zhrnie počet jednotiek pre každú položku. Ak stĺpec v kontingenčnej tabuľke obsahuje iba hodnoty, kontingenčná tabuľka predvolene zobrazuje súčet týchto hodnôt. Dá sa to však zmeniť z nastavenia poľa hodnoty. Ako? Ukážem vám to druhé.
Nastavenia poľa hodnoty kontingenčnej tabuľky
Q4: Priemerná cena každej položky?
V našich vzorových údajoch je cena jednej položky pri rôznych objednávkach odlišná. Pozrite si napríklad spojivá.
Chcem vedieť priemerné náklady na každú položku v kontingenčnej tabuľke. Ak to chcete zistiť, presuňte Jednotkové náklady do poľa Hodnota. Zobrazí sa súčet jednotkových nákladov.
Nechceme Súčet jednotkových nákladov, chceme Priemer jednotkových nákladov. Urobiť tak…
1. Kliknite pravým tlačidlom myši kdekoľvek na súčet stĺpca Jednotkové náklady v kontingenčnej tabuľke
2. Kliknite na Nastavenia poľa hodnoty
3. Na základe dostupných možností, vyberte Priemer a stlačte OK.
Nakoniec budete mať tento kontingenčný prehľad:
Polia vypočítané pre kontingenčnú tabuľku
Jednou z najužitočnejších funkcií kontingenčnej tabuľky sú jej vypočítané polia. Vypočítané polia sú polia, ktoré sa získajú niektorými operáciami s dostupnými stĺpcami.
Poďme pochopiť, ako vložiť vypočítané polia do kontingenčnej tabuľky s jedným príkladom:
Na základe našich údajov sme pripravili túto správu.
Tu máme Súčet jednotiek a Celkové náklady. Práve som presunul stĺpec súčtu do poľa Hodnoty a potom ho premenoval na Celkové náklady. Teraz chcem vedieť priemernú cenu jednotky každej položky pre každý región. A to by bolo:
Priemerná cena = celkové náklady / celkové jednotky
Vložíme do kontingenčnej tabuľky pole, ktoré ukazuje priemernú cenu každej položky podľa regiónu:
Pri vkladaní vypočítaného poľa do kontingenčnej tabuľky postupujte podľa týchto krokov
1. Kliknite kdekoľvek na kontingenčnú tabuľku a prejdite na kartu Analýza
2. Kliknite na polia, položky a sady vo výpočtovej skupine.
3. Kliknite na Vypočítané polia.
Zobrazí sa toto vstupné pole pre vaše pole výpočtu:
4. Do poľa pre zadanie názvu napíšte priemerné náklady alebo čokoľvek, čo sa vám páči, Excelu to nebude vadiť. Do poľa na zadanie vzorca napíšte a kliknite na tlačidlo OK.
= Celkom / jednotky
Operácie môžete písať ručne z klávesnice alebo dvakrát kliknite na názvy polí uvedených v oblasti Polia.
5. Vaše vypočítané pole je teraz pridané do kontingenčnej tabuľky. Je pomenovaná ako súčet priemerných nákladov, ale nie je to suma. Excel iba spustí predvolenú funkciu na pomenovanie stĺpca (ako rituál). Premenujte tento stĺpec a obmedzte zobrazené desatinné číslice.
A tam máte vypočítané pole. Môžete to urobiť tak komplexným, ako chcete. Ako príklad som vzal túto jednoduchú priemernú operáciu.
Zoskupenie v kontingenčnej tabuľke
Tento pivotný prehľad máte pripravený.
Teraz chcem, aby bola táto správa rozdelená každoročne. Pozrite sa na snímku nižšie.
V stĺpci nájdete dátum objednávky. Presuňte pole OrderDate do riadkov v hornej časti.
Nevyzerá to ako požadovaná správa. Musíme mať skupinové termíny po roku.
Teraz, keď chcete zoskupiť pole v kontingenčnej tabuľke programu Excel, postupujte nasledovne:
1. Kliknite pravým tlačidlom myši na pole, ktoré chcete zoskupiť.
2. Kliknite na položku Skupina. Toto pole možností budete mať k dispozícii na prispôsobenie. Pretože sa jedná o stĺpec údajov, Excel nám ukazuje zodpovedajúce zoskupenie. Môžete si vybrať počiatočný a konečný dátum.
3. Vyberajte roky a stlačte OK. V kontingenčnej tabuľke programu Excel ste urobili ročné zoskupenie.
Krájače kontingenčnej tabuľky
Krájače boli predstavené v programe Excel 2010 ako doplnok. V Exceli 2013 a 2016 je predvolene k dispozícii rovnako ako filtre.
Krájače nie sú nič iné ako filtre. Na rozdiel od filtrov zobrazuje Slicers všetky dostupné možnosti priamo pred vami. Vďaka tomu bude váš informačný panel interaktívnejší.
Ako pridať krájače do kontingenčnej tabuľky, Excel 2016 a 2013
Krájače kontingenčných tabuliek je ľahké pridať. Nasleduj tieto kroky:
1. Kliknite kdekoľvek na kontingenčnú tabuľku a prejdite na kartu Analyzovať.
2. Kliknite na Vložiť krájač. Budete mať zoznam polí pre vaše údaje. Vyberte toľko, koľko chcete.
3. V tomto prípade zvoľte Región a kliknite na OK.
Do svojej správy ste pridali krájač. Teraz naneste filter jediným kliknutím.
Vložiť časovú os do Excelu 2016 a 2013
Toto je jedna z mojich obľúbených funkcií kontingenčných tabuliek programu Excel. Táto nová funkcia funguje iba s dátumami. Pomocou toho môžete vizuálne vybrať časové obdobie na filtrovanie údajov.
Ak chcete vložiť kurzor časovej osi, postupujte takto:
Ako pridať časovú os do kontingenčnej tabuľky, Excel 2016 a 2013
1. Kliknite kdekoľvek na kontingenčnú tabuľku a prejdite na kartu Analyzovať.
2. Kliknite na položku Vložiť časovú os zo skupiny filtrov. Všetky stĺpce obsahujúce časové hodnoty v zdrojových údajoch budú uvedené v poli s možnosťami, z ktorého si môžete vyberať. Tu máme iba jeden. Tak áno…
2. Vyberte svoje možnosti a stlačte kláves Enter alebo kliknite na tlačidlo OK. Hotovo a máte časovú os kontingenčnej tabuľky priamo pred sebou.
Môžete sa rozhodnúť zobrazovať ho denne, mesačne, štvrťročne alebo ročne. Vybral som si tu mesačne.
V tomto článku som sa zaoberal najdôležitejšími a najužitočnejšími funkciami kontingenčnej tabuľky. Preskúmali sme nové funkcie kontingenčnej tabuľky v Exceli 2016 a 2013. Dozvedeli sme sa o klasickom použití kontingenčnej tabuľky, ktorá bola spustená v Exceli 2007, 2010 a staršom. Stále sú užitočné. Ak ste tu nenašli svoju odpoveď týkajúcu sa kontingenčnej tabuľky, opýtajte sa v sekcii komentárov.
Existuje mnoho ďalších funkcií, ktoré ešte len budú vysvetlené. Pokročilú funkciu kontingenčnej tabuľky sa naučíme v nasledujúcom článku. Do tej doby Excel vo všetkom.