Jak vytvořit tabulkový graf v aplikaci Excel

Tento tutoriál předvede, jak vytvořit panelový graf ve všech verzích aplikace Excel: 2007, 2010, 2013, 2016 a 2022.

Panelový graf (také nazývaný mřížkový graf nebo malý násobek) je sada podobných menších grafů porovnávaných vedle sebe a dělených oddělovači. Vzhledem k tomu, že tyto mini grafy sdílejí stejné osy a jsou měřeny ve stejném měřítku, v podstatě panelový graf konsoliduje všechny na jedno místo.

Graf vám nejen umožní úhledně zobrazit více informací, ale také vám pomůže rychle porovnat nebo analyzovat vztah mezi více datovými sadami najednou-a zároveň vám ušetří spoustu místa na palubní desce.

Podívejte se například na níže uvedený panelový graf, který kombinuje čtyři oddělené liniové grafy porovnávající roční tržby franšízantů společností Orange Ltd a Banana Ltd ve čtyřech státech za posledních šest let.

Jak vidíte, panelový graf ukazuje bezednou studnici užitečných dat, na rozdíl od spleti řádků, které byste nakonec získali, kdybyste se jednoduše rozhodli pro jeho vestavěný protějšek.

Tento typ grafu bohužel není v Excelu podporován, což znamená, že jej budete muset ručně sestavit sami. Než ale začneme, podívejte se na doplněk Chart Creator, univerzální nástroj pro vytváření pokročilých grafů a grafů aplikace Excel pouhým několika kliknutími.

V tomto tutoriálu se naučíte, jak vykreslit přizpůsobitelný panelový graf v Excelu od základů.

Začínáme

Abychom ilustrovali kroky, které je třeba provést, musíme začít s některými daty. Jak jste si již možná domysleli, porovnáme historickou historii franšízantů dvou společností, Orange Ltd a Banana Ltd, ve čtyřech různých státech-Iowa, Minnesota, Texas a Utah.

Se vším, co bylo řečeno, zvažte následující tabulku:

Pojďme se podrobněji zabývat každým sloupcem.

• Stát - Tento sloupec představuje kategorie, podle kterých bude graf rozdělen na menší grafy (panely). V našem případě každý miniřádkový graf ukazuje dynamiku výkonu pro každý ze stavů.
• Rok - Tento sloupec určuje měřítko vodorovné osy. Hodnoty a formátování by měly být stejné na všech panelech.
• Orange Ltd a Banana Ltd - Toto jsou vaše skutečné hodnoty. Pro ilustraci použijeme pouze dvě sady dat, ale s metodou ukázanou v tomto tutoriálu je obloha limitem.

Nakonec chceme, aby naše data vypadala takto:

Technicky můžete s daty manipulovat ručně (pokud ano, přejděte na Krok č. 5). Tento přístup může být vhodnější, pokud jsou vaše data jednoduchá. Jinak si ukážeme, jak pomocí kontingenční tabulky manipulovat s daty do potřebného formátu.

Pojďme se tedy ponořit přímo dovnitř.

Krok č. 1: Přidejte oddělovače.

Než budete moci vytvořit panelový graf, musíte data uspořádat správným způsobem.

Nejprve napravo od vašich skutečných údajů (sloupec E), vytvořte pomocný sloupec s názvem „Oddělovač.”Účelem tohoto sloupce je rozdělit data do dvou střídajících se kategorií-vyjádřených hodnotami 1 a 2-a vytvořit tak základ pro budoucí kontingenční tabulku.

Zadejte „1”Do každé odpovídající buňky sloupce Oddělovač které patří do první kategorie, Iowa (E2: E7). Poté napište „2“Do všech příslušných buněk, které spadají do druhé kategorie, Minnesota (E8: E13).

Stejným způsobem vyplňte zbývající prázdné buňky ve sloupci a při přechodu ze stavu do stavu se střídejte mezi dvěma hodnotami oddělovače.

Krok č. 2: Přidejte kontingenční tabulku.

Jakmile dokončíte Krok 1, vytvořte kontingenční tabulku.

1. Zvýrazněte libovolnou buňku v rozsahu datové sady (A1: E25).
2. Jděte na Vložit tab.
3. Vybrat "Kontingenční tabulka.”

Když Vytvořte kontingenční tabulku objeví se dialogové okno, vyberte „Stávající pracovní list,”Zvýrazněte prázdnou buňku poblíž vašich skutečných dat (G1) a klikněte na „OK.”

Krok č. 3: Navrhnout rozložení kontingenční tabulky.

Bezprostředně po vytvoření kontingenční tabulky se soubor Pole kontingenční tabulky vyskočí podokno úloh. V tomto podokně úloh přesuňte položky v seznamu polí do následujícího pořadí-pořadí je důležité-pro úpravu rozložení vaší nové kontingenční tabulky:

1. Přestěhovat se "Stát" a "Rok“Až„Řádky.”
2. Přestěhovat se "Oddělovač“Až„Sloupce.”
3. Přestěhovat se "Orange Ltd." a "Banana Ltd.“Až„Hodnoty.”

V tomto okamžiku by vaše kontingenční tabulka měla vypadat nějak takto:

Nyní změňte typ rozložení a odeberte nadbytečné prvky tabulky.

1. Vyberte libovolnou buňku v kontingenční tabulce (G4: L26).
2. Přejděte na Design tab.
3. Klikněte na „Rozložení sestavy.”
4. Vybrat "Zobrazit v tabulkové podobě.”

Stále v Design klikněte na kartu „Velké součty“A vyberte„Vypnuto pro řádky a sloupce”Z rozevírací nabídky.

Dále klikněte na „Mezisoučty“A vyberte„Nezobrazovat mezisoučty.”

Poté, co jste provedli všechny kroky, by měla být vaše kontingenční tabulka naformátována tímto způsobem:

Krok č. 4: Extrahujte data z kontingenční tabulky.

Protože nechcete vytvářet kontingenční graf (ne, nechcete), je na čase data od kontingenční tabulky oddělit. Zkopírujte všechny hodnoty z kontingenční tabulky (G4: L27) do prázdných buněk vedle ní. (Prázdný sloupec mezi soubory dat pomůže vyhnout se nejasnostem.)

Řádek záhlaví (P3: S3) vaší nově vytvořené tabulky odpovídá za zobrazení položek legendy grafu, pojďme je tedy také rychle přidat.

Protože kontingenční tabulka efektivně rozděluje data na dva kvaziřádkové grafy, řádek záhlaví by měl vypadat následovně:

Krok č. 5: Vytvořte spojnicový graf.

Nakonec nakreslete pravidelný spojnicový graf a uvidíte, co se stane.

1. Zvýrazněte tabulku obsahující data extrahovaná z kontingenční tabulky (N3: S27).
2. Jděte na Vložit tab.
3. Klikněte na „Vložte čárový nebo plošný graf" knoflík.
4. Vybrat "Čára.”

Krok č. 6: Zajistěte konzistentní barvy čar.

Jak vidíte, Excel vytvořil spojnicový graf se čtyřmi různými datovými řadami. Sjednoťte tato data pomocí stejných barev pro každou společnost.

Klikněte pravým tlačítkem na libovolnou datovou řadu a vyberte „Formát datové řady.”

V Formát datové řady podokno úloh, proveďte následující:

1. Přepněte na Fill & Line tab.
2. Pod „Čára,"Vyberte"Plná čára.”
3. Klikněte na „Barva obrysu”A vyberte barvu z palety. (Opakujte pro každý řádek.)
4. Znovu zkontrolujte legendu grafu, abyste se ujistili, že jste barvy sladili správným způsobem.

Krok č. 7: Vytvořte dělící čáry.

Úkol po ruce se může zdát jako nic zvláštního, ale není to tak snadné, jak to zní. Ano, můžete jednoduše nakreslit čáry pomocí předdefinovaných tvarů, ale skončily by špatně umístěné při každé změně velikosti grafu.

Místo toho použijeme svislé chybové pruhy, které budou umístěny mezi každým panelem, přesně tam, kde osa Y protíná osu kategorie.

K vyřešení problému však budete muset skočit přes několik obručí. Nejprve vytvoříme data pomocného grafu:

1. Vytvořte samostatný fiktivní stůl úplně stejným způsobem jak to vidíte na obrázku níže. Nechte celu U4 prázdné, aby věci fungovaly, a napište „Děliče“Do V4.
2. Zadejte „0“Do V5 a zkopírujte jej. Počet řádků obsahujících hodnoty v tabulce představuje počet chybových pruhů, které budou nakonec vytvořeny. V tomto případě potřebujeme tři chybové pruhy, takže budou tři buňky s „0" v nich.
3. Zadejte „=COUNTA (O4: O9) +0,5“Do U5.

Vzorec vypočítá počet hodnot měřítka osy x použitých pro jeden panel a vypočítá jeho střed pro umístění čar na ose X přesně tam, kde se obě osy setkávají.

Nyní zadejte „=U5+COUNTA (O4: O9) “ do U6 a zkopírujte jej do U7.

Jakmile nastavíte pomocnou tabulku, vložte tato data do panelového grafu.

1. Zvýrazněte všechny hodnoty v pomocné tabulce, kromě řádku záhlaví (U4: V7).
2. Vyberte oblast grafu.
3. Jděte na Domov tab.
4. Klikněte na „Vložit.”
5. Vybrat "Vložit speciální.”

V zobrazeném dialogovém okně proveďte několik jednoduchých kroků:

1. Pod „Přidat buňky jako,"Vyberte"Nová řada.”
2. Pod „Hodnoty (Y) v," Vybrat "Sloupce.”
3. Zkontrolovat "Názvy sérií v první řadě" a "Kategorie (X štítky) v prvním sloupci" krabice.
4. Klikněte na „OK.”

Krok č. 8: Změňte typ grafu fiktivní série.

Klikněte pravým tlačítkem na nově přidanou datovou řadu (Řada „Děliče“) a vyberte „Změnit typ grafu řady.”

Pro Řada „Děliče“ změna „Typ grafu“ na “Rozptyl přímkami.”

Krok č. 9: Upravte sekundární osy.

Nejprve odeberte sekundární vodorovnou osu. Klikněte pravým tlačítkem na čísla v horní části grafu a vyberte „Vymazat.”

Poté klikněte pravým tlačítkem na sekundární svislou osu podél pravé strany grafu a vyberte „Formátovat osu.”

V Formátovat osu podokno úloh, kamínkové kameny, rozsahy os:

1. Přepnout na Možnosti osy tab.
2. Změň Minimální hranice hodnota na “0.”
3. Nastav Maximální hranice hodnota na “1.”

Na stejné kartě přejděte dolů na Štítky sekci a změnit „Poloha štítku“Až„Žádný”Skrýt měřítko osy-pokud jej jednoduše odstraníte, zničí to graf.

Krok č. 10: Přidejte chybové pruhy.

Nyní byl položen základ, což znamená, že můžete konečně přidat chybové pruhy.

1. Vybrat Série „Děliče“.
2. Klikněte na Prvky grafu ikona.
3. Klikněte na šipku vedle "Chybové pruhy."
4. Vybrat "Více možností.”

V Chybové pruhy formátování podokno úloh, upravte oddělovače.

1. Přepnout na Možnosti chybové lišty tab.
2. Pod „Směr," Vybrat "Plus.”
3. Pod „Koncový styl,"Vyberte"Žádný Cap.”
4. Pod „Částka chyby," změna "Pevná hodnota“Až„1.”

Krok č. 11: Skryjte prvky pomocného grafu.

Jako poslední krok odeberte podkladový sekundární bodový graf, vodorovné chybové pruhy a duplicitní položky legendy.

Pojďme se nejprve zabývat atrapou série. Vybrat Řada „Děliče“ přejděte na Formát datové řady podokno úloh a v Možnosti řady na kartě vyberte „Žádná linka.”

Nyní odstraňte vodorovné chybové pruhy a každou položku legendy uložte pro „Orange Ltd." a "Banana Ltd.“Výběrem každé položky, kliknutím pravým tlačítkem a výběrem„Vymazat. ” Na konci tohoto kroku by váš panelový graf měl vypadat takto:

Změňte název grafu a váš úžasný panelový graf je připraven!