Díky nové verzi 2016 nabízí Microsoft Excel svým nadšeným uživatelům až 15+ grafových variací. Kontingenční graf ve Excelu však zůstává solidním favoritem studentů, domácích uživatelů i profesionálů z oboru podnikání. Toto je ten jediný graf, který dokáže sbalit spravedlivé informace bez toho, aby čtenáře ohromil. Kromě toho může být postaven na otočném stole a kombinován s téměř každým jiným typem grafu, což z něj činí nesmírně flexibilní nástroj pro analýzu dat!

V tomto příspěvku vám ukážeme, jak můžete sestavit svůj první kontingenční graf v 10 jednoduchých krocích.

Krok 1: Připravte svá data v tabulkovém formátu

Prvním krokem je shrnutí nezpracovaných dat do sudých řádků a sloupců. Nejlepší je pracovat s tímto grafem na tabulce s nejméně 2-3 sloupci, takže kontingenční graf má dostatek polí sloupců k promítnutí smysluplné statistiky. Otočný graf v Excelu také pracuje s nulovými čísly; ale u vyváženého grafu zajistěte, aby ve sloupcích klíčů nebyly žádné nulové hodnoty. Rovněž se ujistěte, že vaše data jsou čistá a nemají duplicitní hodnoty.

V našem příkladu vytvoříme kontingenční graf s údaji od vydavatele střední velikosti, abychom zaznamenali údaje o statistice prodeje napříč populárními periodiky.

Tabulka bude obsahovat 3 sloupce polí: název deníku, měsíc publikace a celkový počet prodejů (ve stovkách). Kombinace (Journal + Month) jedinečně identifikuje řádek v tabulce. Dále byla věnována pozornost tomu, aby neexistovaly žádné nulové hodnoty pro žádný řádek. Údaje v tabulce byly také seřazeny podle měsíce, to však není nutné.

  • Připravte podobná data na čerstvém listu v Excelu a pojmenujte pracovní list jako „Raw Data“.

Doporučené kurzy

  • Školení online na makru VBA
  • Online školení o VBA a makrech
  • Excel 2007 Základní výcvikový kurz

Krok 2: Vytvoření kontingenční tabulky pro data

Kontingenční tabulka v ideálním případě uchovává data, která se v excelovském grafu přenášejí do kontingenčního grafu. Poznamenejte si - v tomto okamžiku můžete přímo vytvořit kontingenční graf odkazující na připravená nezpracovaná data. Excel implicitně vytvoří kontingenční tabulku a kontingenční graf. Ale to může ohromit nové uživatele, protože výsledný graf bude mít příliš mnoho parametrů pro správu.

Pro snazší pochopení vytvoříme explicitně kontingenční tabulku a poté ji použijeme k vložení kontingenčního grafu do Excelu. Díky tomu budete moci snadno porozumět postupným krokům a lépe spravovat datové parametry, které se dostanou do grafu. Jakmile se stanete odborníkem, můžete vytvořit kontingenční graf přímo ze surových dat.

Chcete-li vytvořit kontingenční tabulku,

  • Klikněte na záložku Vložit v hlavní stuze
  • Klikněte na rozevírací tlačítko kontingenční tabulky a vyberte kontingenční tabulku
  • Zde můžete vkládat data pomocí kterékoli z následujících možností:
    • Ručně vyberte rozsah buněk, které tvoří nezpracovaná data. Buňky budou zvýrazněny a jejich pozice v tabulce budou zachyceny (viz obrázek výše). NEBO,
    • Použijte externí zdroj dat (například připojení k databázi Microsoft Access nebo jakékoli jiné připojení zdroje dat).

První možnost vybereme ručně výběrem buněk v tabulce vytvořené v Kroku 1. Nezapomeňte zahrnout všechny řádky a sloupce vytvořených dat tabulky.

  • Zaškrtněte políčko „Nový list“, takže požadovaná kontingenční tabulka je vytvořena v novém listu aplikace Excel, aby byla jasnější. Excel vytvoří kontingenční tabulku v novém listu. Pojmenujte tuto „kontingenční tabulku“.

Krok 3: Seznamte se s parametry kontingenční tabulky

V tomto kroku neexistují žádné explicitní akce. Je to však značka, která vám pomůže pochopit různé parametry kontingenční tabulky.

Po vytvoření kontingenční tabulky zobrazíte kliknutím na libovolné místo v tabulce 2 karty:

  1. Skutečná kontingenční tabulka zobrazená na levé straně listu. Při prvním vytvoření tabulky se zobrazí pouze prázdná šablona. Jakmile jsou parametry tabulky definovány a přizpůsobeny, je tabulka naplněna hodnotami z poskytnutých prvotních dat.
  2. Seznam polí obsahující sloupce, které chcete naplnit do tabulky. To se dále dělí na:
    1. Pole kontingenční tabulky: Ve výchozím nastavení bude obsahovat všechna pole sloupce dostupná ve vybraných surových datech.
    2. Popisky řádků a popisky sloupců : Určuje pole surových dat (řádky a sloupce), které se zobrazí v kontingenční tabulce. (Pole sloupce se tak může vložit do statistik tabulky výběrem v (a) výše, ale zůstane skryto před zobrazením, dokud nebude výslovně vybráno v označení sloupce.)
    3. Filtr zpráv : Zde nastavíte všechna omezení pro odfiltrování řádků ze surových dat.
    4. Hodnoty: Tím se určí statistická data vypočtená pomocí kontingenční tabulky.

Tato obrazovka společně označuje zobrazení „Nástroje kontingenčních tabulek“. Kdykoli chcete změnit nebo obnovit kontingenční tabulku, vraťte toto zobrazení. V dalších krocích se naučíme přizpůsobit vytvořenou kontingenční tabulku tak, aby odhalila údaje relevantní pro naše nezpracovaná data.

Krok 4: Vyberte pole sloupce použitelná pro kontingenční tabulku

Ve výchozím nastavení jsou všechna pole sloupců vybraná v prvotních datech k dispozici pro použití v kontingenční tabulce. Musí však být explicitně vybrány, aby se vztahovaly na nově vytvořenou kontingenční tabulku. Budeme to jednoduché tím, že zkontrolujeme všechny sloupce našich nezpracovaných dat na kartě „Seznam polí kontingenční tabulky“.

Krok 5: Přizpůsobte pole kontingenční tabulky

Pořadí, ve kterém vyberete pole sloupců pro kontingenční tabulku, určuje pořadí, v jakém jsou přidána do tabulky. To zase řídí popisky řádků a sloupců a ovlivňuje konečné hodnoty vypočtené v kontingenční tabulce. Takže podle pořadí vybraného v Kroku 4 (1) Deník 2) Měsíc a 3) Prodej) bude tabulka zobrazovat celkový prodej za každý deník, seskupený podle měsíce.

  • Nyní zrušte zaškrtnutí všech políček a zkuste tuto revidovanou objednávku: 1) Měsíc 2) Deník 3) Prodej. Zobrazí se celkový prodej za každý měsíc, seskupený podle deníku.

Jak vidíte, pořadí výběru má významný vliv na způsob vykreslování kontingenční tabulky. V našem příkladu si zachováme pořadí zachycené v Kroku 4.

Krok 6: Přizpůsobte hodnoty kontingenční tabulky

Ve výchozím nastavení používá MS Excel funkci SUM k výpočtu hodnot vykreslených v tabulce.

  • Předpokládejme například, že přidáte pouze 2 sloupcová pole v následujícím pořadí: 1) Měsíc 2) Prodej. Tím se automaticky zobrazí celkový součet prodejů za každý měsíc.

Nyní zvažte, že chcete zobrazit celkový počet publikací, které byly prodávány každý měsíc, ve všech časopisech. Můžete to vykreslit pomocí funkce COUNT následujícím způsobem:

  • Klepněte na kartu „Hodnoty“ v zobrazení tabulky kontingenčních tabulek. Ve výchozím nastavení je funkce SUMA.
  • Klikněte na rozevírací seznam a vyberte „Nastavení hodnot pole…“ a ve výsledném seznamu vyberte funkci COUNT.
  • Tabulka vypočítá celkový počet prodejů každý měsíc (s uvedením počtu prodaných publikací), jak je uvedeno níže.

Krok 7: Vytvořte graf pro promítání kontingenční tabulky

Nyní, když máme plně funkční kontingenční tabulku, jakýkoli typ grafu, který promítá tuto tabulku, slouží jako přerušovaný kontingenční graf ve Excelu. V našem příkladu vytvoříme základní 3D sloupcový graf k promítání naší kontingenční tabulky.

Pro tohle,

  • Klepněte na kontingenční tabulku vytvořenou v kroku 5 a poté na kartu Vložit.
  • Klikněte na rozbalovací nabídku Sloupec a vyberte 3D sloupec.

Tím se váš graf automaticky promítne do sloupcového grafu.

Zde je několik poznámek:

  • Název grafu je založen na sloupci Hodnota pole. Tady je to součet PRODEJŮ.
  • Graf obsahuje sloupce polí kontingenční tabulky. Zde byly pro vytvoření zobrazeného grafu vybrány sloupce 1) deník 2) měsíc a 3) tržby. Tyto parametry společně zachycují projekci dat o prodeji v deníku rozdělené podle měsíců.

A voila, vytvořili jste svůj první (přerušovaný) graf založený na kontingenční tabulce! Výborně! J

Poznamenejte si, že můžete vytvořit grafy, které vytvoříte na kontingenční tabulce, experimentováním s různými typy grafů poskytovaných Excelem v části Vložit kartu - grafy. V každém případě je grafem projekce dat kontingenční tabulky, a proto slouží jako kontingenční graf ve formátu Excel. V dalších krocích se naučíte, jak vytvořit a přizpůsobit kontingenční graf přímo ze vstupních prvotních dat.

Krok 8: Vytvoření kontingenčního grafu

Nyní, když jste obeznámeni s kontingenční tabulkou a grafy promítanými z jejích dat, přeskočíme kroky 2-7 a uvidíme, jak můžeme přímo promítat naše nezpracovaná data do kontingenčního grafu. Ano, žádná tabulka, žádný přerušovaný graf, vytvoříme kontingenční graf ze surových dat, přímo z kroku 1! Nyní máte znalosti pro správné pochopení a přizpůsobení jeho parametrů, takže kontingenční graf je čistý a relevantní.

Chcete-li vytvořit kontingenční graf,

  • Vyberte buňky, které obsahují vaše nezpracovaná data, jak bylo vytvořeno v kroku 1.
  • Klikněte na kartu Vložit a poté na rozbalovací nabídku „Kontingenční tabulka“. Vyberte „PivotChart“.
  • Vytvořte kontingenční graf stejně jako jste vytvořili kontingenční tabulku v kroku 2, přejděte na výchozí nastavení a klikněte na OK.
  • Kontingenční graf bude vytvořen tak, jak je uvedeno níže. Pojmenujte tento list jako „kontingenční graf“.

Několik poznámek:

  • Pohled, který se otevře po kliknutí na oblast kontingenčního grafu, je pohled „Nástroje kontingenčního grafu“. Nyní je to velmi známý pohled, protože kombinuje součet kroků 2-7. Když kliknete na buňky obsahující kontingenční tabulku, bude toto zobrazení dostupné. (Poznámka: Toto zobrazení vám dále umožňuje přizpůsobit předvolby kontingenční tabulky, ale my přejdeme s výchozím nastavením pro náš příklad.)
  • Ve výchozím nastavení aplikace Excel vykreslí sloupcový graf, podobně jako to, co jsme explicitně vytvořili v kroku 7. Toto můžete bez námahy změnit pomocí tlačítka „Změnit typ grafu“ na kartě Návrh (jak je zvýrazněno na obrázku výše).

V dalších krocích přizpůsobíme tento kontingenční graf ve formátu Excel tak, aby výsledný datový graf byl požadovaný. J

Krok 9: Parametrizace kontingenčního grafu

Dobře tedy rychle shrneme kroky použité v předchozím grafu na náš nově vytvořený kontingenční graf.

  • Kliknutím na buňky obsahující kontingenční tabulku získáte přístup k zobrazení seznamu kontingenčních tabulek na pravé straně.
  • Zkontrolujte v pořádku deník, měsíc a prodej. Tím se promítnou údaje o prodejním deníku podle měsíce.
  • Ponechte pole hodnoty na výchozí nastavení: Suma PRODEJ.

A kontingenční graf je nyní kompletní!

Krok 10: Přizpůsobte kontingenční graf ve formátu Excel

V tomto posledním kroku použijeme 3 přizpůsobení na náš vytvořený kontingenční graf ve Excelu, abyste pochopili rozsah změn, které v něm můžete provést jediným kliknutím myši.

Přizpůsobení oblasti grafu:

  • Výchozí kontingenční graf bude vykreslen s modrými řádky sloupců. Ale co když chcete dramatičtější zobrazení? To je možné pomocí různých stylů grafu dostupných na pásu karet „Nástroje kontingenčního grafu“.
  • Prozkoumejte styly grafů dostupné ve vašem systému a vyberte možnost s barevným schématem a displejem, které vyhovují vašim preferencím.
  • A…. zobrazení kontingenčního grafu se okamžitě změní podle nového barevného a okouzlujícího vzhledu! J

Přizpůsobte typ grafu - 1:

Výchozí sloupcový graf slouží většině potřeb grafu. Ale co když vaše data nejsou pro tento typ grafu nejvhodnější? Například naše data projektují prodej za 8 časopisů, za 3 měsíce, celkem 24 sloupců. Možná je to čitelnější, když se promítají jako vodorovné čáry na rozdíl od sloupců? Můžete to vyzkoušet jediným kliknutím myši, jak je zachyceno níže.

  • Klikněte na tlačítko „Změnit typ grafu“, nejvíce vlevo dostupné tlačítko v zobrazení „Nástroje kontingenčního grafu“.
  • Zobrazený seznam zobrazuje typy grafů podporované ve vaší verzi aplikace Microsoft Excel. Bez ohledu na zvolený typ grafu bude aktuální nastavení pokračovat (např. Pole vstupních dat, pole hodnot atd.)
  • Vyberte typ grafu „Bar“ a klikněte na OK. Změněné zobrazení grafu je uvedeno níže.

Jak vidíte, je to trochu čitelnější. J

Přizpůsobte typ grafu - 2:

V našem posledním příkladu se podívejme, že chcete prozkoumat celkový podíl na trhu, který má každá publikace. Už se nestaráte o měsíční členění, ale pouze o celkové tržby.

  • Prvním krokem je zrušení zaškrtnutí všech polí sloupců grafu a opětovné označení pouze 2 sloupců v pořadí: 1) deník a 2) prodej. Výsledná tabulka vypadá takto.

  • Dále sloupcové nebo sloupcové grafy nejsou pro tato data nejvhodnější. Takže klikněte na příkazové tlačítko „Změnit typ grafu“, přejděte zobrazeným seznamem dolů a vyberte výsečový graf. Výsledný kontingenční graf v Excelu vypadá takto:

Jak vidíte, jedná se o jemnější reprezentaci vašich dat než sloupcový a čárový graf. Můžete také prozkoumat příkazy „Rozvržení grafu“ a „Styly grafu“ a vizuální zobrazení změnit podle vašich požadavků.

Závěrečná poznámka…

Kontingenční graf v Excelu je v podstatě jakýkoli graf, který je vykreslen z kontingenční tabulky s definovanými sloupci pole a hodnotami. V tomto příspěvku jste se naučili jemnější nuance tohoto grafu, takže bez ohledu na to, jaký typ grafu vyberete, jste schopni efektivně vykreslit graf tak, aby vyhovoval vašim datům.

Hledejte další příspěvky na různých typech grafů dostupných v Excelu pro pokročilé znalosti.

Doporučený článek

To byl průvodce grafem Pivot v Excelu zůstává solidní favorit u studentů, domácích uživatelů i profesionálů v podnikání. Jedná se o následující externí odkaz související s kontingenčním grafem v Excelu.

  1. 8 Úžasných a užitečných funkcí sešitu aplikace Excel 2016
  2. SPSS vs EXCEL
  3. Excel vs Tableau
  4. Top 25 užitečných pokročilých vzorců a funkcí Excelu

Kategorie: