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, namiesto toho, aby ste sa vracali a znova vyberali ú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ť umiestnenie v poli umiestnenia.
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.
Stlačte kláves Altbutton 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 a neodhodia staré funkcie (ako to urobil MS s win 8. Bolo to úbohé). Toto umožňuje staršiemu používateľovi normálne pracovať na nových verziách, ako pracoval 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 (Očividne)
Región: Región poriadku v krajine
Zákazník: Meno zákazníka (čo iné to 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).
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.
STĹPCE: Zadajte požadované polia 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žka (vyberte ľubovoľný stĺpec, len sa uistite, že medzi ním nie je prázdna bunka) v hodnotových poliach.
Vyberte Položka zo zoznamu polí a presuňte ho do poľa Hodnota.
Máme svoju odpoveď. Kontingenčné tabuľky mi hovoria, že existuje celkom 43 objednávok. Toto je správne.
Pro tip: Mali by ste skontrolovať svoje údaje, či sú správne alebo nie. Ak sa toto číslo nezhoduje s údajmi, znamená to, že ste vybrali nesprávny rozsah alebo že pole obsahuje prázdne bunky.
Info: Pole hodnoty štandardne počíta počet záznamov v stĺpci, ak obsahuje text, a súčty, ak pole obsahuje iba hodnoty. Môžete to zmeniť v nastaveniach hodnotového poľa. Ako? Uvidíme sa neskôr v tomto návode na používanie kontingenčnej tabuľky.
Teraz, keď som si vybral Dátum objednávky vo Valuesarea 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 Centrle 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ý.
INFO: Bez ohľadu na to, aké zmeny vykonáte vo svojich zdrojových údajoch, kontingenčná tabuľka bude naďalej pracovať so starými údajmi kým ho neobnovíte. Excel vytvorí kontingenčnú vyrovnávaciu pamäť a v tejto vyrovnávacej pamäti beží kontingenčná tabuľka. Po obnovení sa stará vyrovnávacia pamäť zmení s novými údajmi.
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 ponuku Medzisúčty.
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 takto:
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 tabuľkový formulár možnosť. 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 stĺpce.
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…
- Kliknite pravým tlačidlom myši kdekoľvek na súčet stĺpca Jednotkové náklady v kontingenčnej tabuľke
- Kliknite Nastavenia poľa hodnoty
- 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 Tabuľka analýzy
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 Priemer Náklady alebo čokoľvek sa vám páči, excelu to nebude vadiť. V vzorec vstupné pole, napíšte a stlačte 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.
