Smíšený graf v excelu. Kombinace různých typů grafů v EXCELu

Budování kombinovaných grafů

V případech, kdy potřebujete na jednom grafu zobrazit data různých měřítek, je užitečné použít kombinované grafy. Typickým případem jsou absolutní (rubly) a relativní (procentuální) ukazatele. Například velikost pohledávek a jejich podíl na tržbách.

Kombinovaný (nebo smíšený ) je graf, který se skládá z více datových řad a současně používá různé typy grafů (například histogram a graf). K vytvoření kombinovaných grafů potřebujete alespoň dvě datové řady. Příklady smíšených diagramů jsou zveřejněny v knize Kombinovaný

Při vytváření kombinačních grafů platí určitá omezení:

· není možné míchat žádné typy grafů s objemovými typy;

· některé kombinace typů map vypadají extrémně neuspokojivě (například kombinace grafu a radarové mapy);

· smíšený graf používá jednu vykreslovací oblast, proto není možné vytvořit např. kombinovaný graf sestávající ze tří koláčových grafů;

· Kombinace grafu a sloupcového diagramu není podporována: osa kategorie sloupcového diagramu je vždy orientována svisle a osa grafu vždy vodorovně.

Obrázek ukazuje graf vytvořený pomocí tří datových řad. Teploty vzduchu a vody jsou navíc prezentovány ve formě histogramu a množství srážek je prezentováno v grafu.

Obrázek ukazuje histogram vytvořený pomocí dvou datových řad:

Změňme typ grafu pro druhou datovou řadu ( Srážky) a pro tuto řadu použijte samostatnou osu hodnot.

1. Vyberte řadu dat v diagramu Srážky a vyvolejte kontextovou nabídku řádku.

2. Otevřete okno Formát datové řady (Formát datové řady) a na kartě Možnosti řádek (Možnosti řady) nainstalujte spínač Podle pomocný sekery(sekundární osa).

3. Aniž byste odstranili výběr z řádku, spusťte příkaz Práce s grafy– Konstruktér – Typ – Změna typ diagramy(Schéma Nástroje – Design – Typ – Změna Schéma Typ) .

4. V dialogovém okně Změnit typ grafu vyberte typ Plán (Čára) a klikněte na tlačítko OK.

Údaje o srážkách jsou nyní vizualizovány jako přímkové segmenty s novým napravo Hodnotová osa (PROTIalueAxis).

Je důležité pochopit, že tým Práce s grafy– Konstruktér – Typ – Změna typ diagramy (Schéma Nástroje – Design – Typ – Změna Schéma Typ) funguje odlišně v závislosti na tom, co je vybráno. Pokud je vybrána řada grafu, příkaz změní typ pouze této řady. Pokud je vybrán jakýkoli jiný prvek diagramu, příkaz změní typ celého diagramu.

Překryvná vrstva diagramy (ÓverlayCjeleni)

Překryvný graf označuje umístění grafů jako grafické objekty: jeden nad druhým. Horní graf má obvykle průhlednou oblast grafu a oblast vykreslování. Odebere všechny prvky kromě například značek a čar. Překrývání grafu je ruční úkol, který vyžaduje přesné umístění objektů, nastavení hodnot a os kategorií.

Aby bylo možné uspořádat diagramy „na sebe“ ve správném pořadí, je třeba je vybrat jako objekty. Chcete-li místo grafu vybrat objekt, stiskněte Ctrl a klikněte na diagram.

Podívejme se na jednoduchý příklad (list Překryvná_1 knihy Kombinovaný). Je známo, že jedním z omezení při vytváření kombinovaných grafů je nemožnost smíchat jakékoli typy grafů s objemovými typy. Metoda překrytí grafu umožňuje kombinovat graf a objemový histogram na listu. Udělejme následující kroky:

1. Vyberte rozsah v datové tabulce A2:B7 a vytvořte objemový histogram se seskupením. Odeberme legendu grafu.

2. Vyberte nesousedící rozsahy A2:A7 A C2:C7 a vytvořte graf pomocí značek. Odeberme legendu grafu.

3. Na druhém grafu nastavte průhlednou výplň pro oblast grafu a oblast vykreslování grafu a odstraňte osy. Pojďme naformátovat čáry a značky grafu.

4. Ručně spojte graf a objemový histogram. Změnou velikosti objektu „Graph“ zajistíme, aby se značky grafu shodovaly se středem sloupců histogramu.

5. Po konečném formátování získáme překryvný diagram.

6. Pokud po výběru dvou diagramů jako grafických objektů je seskupíte, výsledný superponovaný diagram se zkopíruje a přesune jako jeden objekt. Všimněte si, že pokus umístit graf na samostatný list zničí překrytí.

Následující obrázek ukazuje překrytí 3D výsečového grafu a 3D histogramu. Celkový rám působí dojmem, že se jedná o jeden diagram. Jedná se však o dva samostatné diagramy. Rámeček nepatří jim, ale buňkám listu. Titulek je volně pohyblivý nápis. Údaje pro takové překrytí jsou uvedeny na listu Oběžník _objemový knihy Kombinovaný.

Objemové histogramy Excelu neumožňují zobrazit další řádky do hloubky. Překrývající se grafy toto omezení překonávají. Stačí sestavit tři objemové histogramy s akumulací (pro každý rok zvlášť) a zkombinovat je tak, aby „vytvořily“ třetí hloubkovou osu. Graf umožňuje vizuálně porovnávat výsledky podle tří parametrů – regionů, měsíců a let. Původní datová tabulka je v pracovním listu Tři_svazky knihy Kombinovaný.

Standardní nástroje Excelu pro koláčové grafy umožňují používat pouze jednu sadu dat. Tento příspěvek vám ukáže, jak vytvořit koláčový graf na základě dvou sad hodnot.

Jako příklad jsem vzal obyvatelstvo Země podle kontinentů v letech 1950 a 2000. (viz list „Populace“ v souboru Excel; Austrálii jsem odstranil, protože její podíl je zanedbatelný a diagram je obtížně čitelný :)). Nejprve vytvořte základní výsečový graf: vyberte rozsah A1:C6, přejděte na Vložit → Výsečový → Výsečový.

Rýže. 1. Vytvořte běžný koláčový graf

Stáhněte si poznámku ve formátu, příklady ve formátu

Klepněte pravým tlačítkem myši na graf a z kontextové nabídky vyberte Formát datové řady. Vyberte „Podél vedlejší osy“ a poté posuňte posuvník směrem k „Oddělení“ na přibližně 70 % (obrázek 2). Sektory stejného řádku se „rozdělí“.

Rýže. 2. Podél vedlejší osy

Postupně vyberte jednotlivé sektory (pomalým dvojitým kliknutím) a změňte jejich výplň a umístění, spojte všechny sektory ve středu (obr. 3).

Rýže. 3. Formátování bodů řádků (jednotlivé sektory)

Naformátujte všechny sektory tak, aby barvy odpovídající stejnému kontinentu v různých řadách byly ze stejného rozsahu, ale s různou intenzitou. Doplňte do grafu popisky dat, legendu a nadpis (obrázek 4).

Rýže. 4. Koláčový graf se dvěma soubory dat

Graf jasně ukazuje, že například podíl Asie vzrostl za 50 let z 55,8 % na 60,9, zatímco podíl Evropy se za stejnou dobu snížil z 21,8 % na 12,1 %.

Pokud nejste příznivci koláčových grafů, můžete využít prstencový graf, který ve standardu Excel pracuje s více datovými sadami (obr. 5); viz také list „Ring“ v souboru Excel. Vyberte datovou oblast (v našem příkladu je to A1:C6) a přejděte do nabídky Vložit – Grafy – Jiné grafy – Donut:

Rýže. 5. Vytvořte prstencový graf

Stačí diagram trochu upravit, aby byl vizuálnější (obr. 6)

Rýže. 6. Koblihový graf

Myšlenka byla spatřena v knize D. Kholeyho, R. Kholeyho „Excel 2007. Triky“.

Histogram v Excelu je způsob, jak vytvořit vizuální graf, který zobrazuje změny v několika typech dat za určité časové období.

Histogram je užitečný pro ilustraci a porovnání různých parametrů. Podívejme se na nejoblíbenější typy histogramů a naučte se je vytvářet.

Jak vytvořit aktualizovaný histogram?

Máme údaje o prodeji různých druhů mléčných výrobků za každý měsíc za rok 2015.

Pojďme sestavit aktualizovaný histogram, který bude reagovat na změny provedené v tabulce. Vyberte celé pole včetně záhlaví a klikněte na kartu INSERT. Najdeme DIAGRAMY - HISTOGRAM a vybereme úplně první typ. Říká se tomu HISTOGRAM SE SKUPENÍM.

Obdrželi jsme histogram, jehož velikost pole lze změnit. Z tohoto grafu je například jasně patrné, že největší tržby byly za mléko v listopadu, nejmenší za smetanu v červnu.

Pokud provedeme změny v tabulce, vzhled histogram se také změní. Například místo 1400 v lednu za kefír dejme 4000. Vidíme, jak zelený sloupec letěl nahoru.

Skládaný histogram

Nyní se podíváme na to, jak vytvořit skládaný histogram v Excelu. Další typ histogramu, který umožňuje zobrazit data v procentech. Staví se úplně stejně, ale volí se jiný typ.

Dostáváme diagram, kde můžete vidět, že například v lednu se prodalo více mléka než kefíru nebo smetany. A v srpnu se oproti jiným mléčným výrobkům prodalo málo mléka. A tak dále.

Histogramy v Excelu lze upravovat. Pokud tedy klikneme pravým tlačítkem do prázdné oblasti diagramu a vybereme ZMĚNIT TYP, můžeme jej poněkud upravit. Změňme typ našeho histogramu ze skládaného na normalizovaný. Výsledkem bude stejný graf, ale osa Y ukáže poměry v procentech.

Podobně můžete provést další změny v histogramu, což jsme provedli:

• změnil písmo na Arial a změnil jeho barvu na fialovou;
• udělal podtržení tečkovanou čarou;
• posunul legendu o něco výše;
• přidány titulky do sloupců.

Jak zkombinovat histogram a graf v Excelu?

Některé datové sady vyžadují konstrukci složitějších diagramů, které kombinují několik typů dat. Například histogram a graf.

Podívejme se na příklad. Nejprve do tabulky s údaji přidáme další řádek, kde se zaznamenávají tržby za jednotlivé měsíce. Uvádí se v rublech.

Nyní upravíme stávající diagram. Klikněte pravým tlačítkem do prázdného místa a vyberte VYBRAT DATA. Zobrazí se pole s výzvou k výběru jiného intervalu. Znovu vybereme celou tabulku, ale tentokrát včetně řádku s tržbami.

Excel automaticky rozšířil rozsah hodnot podél osy Y, takže údaje o počtu prodejů zůstaly úplně dole v podobě neviditelných sloupců.

Ale takový histogram je nesprávný, protože na jednom diagramu máme čísla v rublech a kvantitativní ekvivalenty (rubly a litry). Proto je třeba provést změny. Přesuňme údaje o tržbách na pravou stranu. Klikněte pravým tlačítkem na fialové sloupce, vyberte FORMÁT DATOVÉ SÉRIE a vyberte POMOCNÁ OSA.

Vidíme, že harmonogram se okamžitě změnil. Nyní má fialová lišta příjmů svou vlastní oblast hodnoty (vpravo).

Ale to stále není příliš výhodné, protože sloupce téměř splývají. Proto provedeme ještě jednu akci navíc: klikněte pravým tlačítkem myši na fialové sloupce a vyberte ZMĚNIT TYP GRAFU PRO SÉRII. Objeví se okno, ve kterém vybereme graf, úplně první typ.

Získáme velmi přehledný diagram, který je kombinací histogramu a grafu. Vidíme, že maximální tržby byly v lednu a listopadu a minimální v srpnu.

V Excelu si můžete výsledky výpočtů zobrazit ve formě grafu nebo grafu, čímž získáte větší přehlednost a pro srovnání je někdy potřeba sestavit dva grafy vedle sebe. Dále se podíváme na to, jak sestrojit dva grafy v Excelu na jednom poli.

Začněme tím, že ne každý typ grafu v Excelu dokáže zobrazit přesně takový výsledek, jaký očekáváme. Například existují výsledky výpočtů pro několik funkcí na základě stejných počátečních dat. Pokud z těchto dat sestavíte pravidelný histogram nebo graf, pak se při konstrukci nebudou brát v úvahu původní data, ale pouze jejich množství, mezi kterými budou stanoveny stejné intervaly.

Vybereme dva sloupce výsledků výpočtu a vytvoříme pravidelný histogram.

Nyní zkusme přidat další histogram ke stávajícím se stejným počtem výsledků výpočtu. Chcete-li přidat graf v aplikaci Excel, aktivujte existující graf jeho výběrem a na zobrazené kartě "Konstruktor" Vybrat "Vybrat data". V okně, které se objeví v sekci "Elements of Legend" klikněte na přidat a zadejte buňky "Název řádku:" A "Hodnoty:" na listu, což budou hodnoty výpočtu funkce "j".

Nyní se podívejme, jak bude náš graf vypadat, pokud ke stávajícím histogramům přidáme další, který má téměř dvojnásobek hodnot. Přidejme hodnoty funkcí do grafu "k".

Jak vidíte, nově přidaných hodnot je mnohem více a jsou tak malé, že na histogramu prakticky nejsou vidět.

Pokud změníme typ grafu z histogramu na běžný graf, bude výsledek v našem případě přehlednější.

Pokud použijete pro sestavení grafů v Excelu bodový graf, pak výsledné grafy zohlední nejen výsledky výpočtu, ale i zdrojová data, tzn. mezi množstvími bude jasný vztah.

Chcete-li vytvořit bodový graf, vyberte sloupec počátečních hodnot a pár sloupců výsledků pro dvě různé funkce. Na kartě "Vložit" vyberte bodový graf s hladkými křivkami.

Chcete-li přidat další graf, vyberte stávající a na kartě "Konstruktor" lis "Vybrat data".

V novém okně ve sloupci "Elements of Legend" lis "Přidat" a označte buňky pro "Název řádku:", "Hodnoty X:" A "Hodnoty Y:". Přidejme funkci tímto způsobem "j" do grafu.

Grafy obvykle používají jeden styl, například sloupcový, pruhový nebo výsečový graf. Pokud má váš graf více než jednu datovou řadu, můžete v grafu zobrazit různé styly a vytvořit tak smíšený graf.

Na Obr. Obrázek 136.1 ukazuje diagram, který je vhodný pro převod na smíšený. Graf znázorňuje průměrné teploty a srážky podle měsíců. Ale je tu problém. Tyto dva rozsahy dat se jasně liší velikostí, takže pruhy srážek jsou v grafu sotva viditelné.

Na Obr. Obrázek 136.2 je diagram poté, co jsem jej převedl na smíšený diagram. Hodnoty srážek jsou nyní vyneseny jako čára a je použita druhá svislá osa zprava. Většina z vás bude souhlasit s tím, že tento graf je výrazným vylepšením oproti originálu. Můžete jej použít při analýze nákladů na zboží v klenotnictví ve Vologdě.

Příkaz Smíšené grafy můžete hledat na pásu karet Excel celý den a stále jej nenajdete. Pokud jste již vytvořili graf (který má alespoň dvě datové řady), můžete jej snadno převést na smíšený graf. Následuj tyto kroky:

1. Klikněte na řádky, které chcete změnit.
2. Vybrat Práce s grafy Typ návrháře Změna typu grafu.
3. V dialogovém okně Změnit typ grafu vyberte ikonu, která představuje typ grafu pro vybranou řadu.
4. Pokud chcete pro převedenou řadu použít druhou vertikální osu, vyberte řadu a klikněte Ctrl+1 otevřete dialogové okno Formát datové řady; přejděte do sekce Parametry řady a nastavte spínač Sestavte řadu do polohy Po vedlejší ose.

Schéma na Obr. 136.1 byl standardní sloupcový graf se dvěma řádky. Několik kliknutí a jedna série (v tomto příkladu Srážky) převedená ze sloupců na čáru. Také jsem přidal druhou osu a označil osy, aby bylo jasné, které měřítko se pro kterou sérii používá. Chcete-li přidat názvy os, spusťte příkaz Práce s názvy os rozvržení grafů.

Na Obr. Obrázek 136.3 ukazuje další smíšený diagram. Kombinuje sloupcový a pruhový graf a obě řady používají stejnou vertikální osu. V tomto případě zobrazení měsíčních prodejních cílů jako sloupcového grafu usnadňuje nalezení měsíců, ve kterých byl cíl splněn.

Na Obr. Obrázek 136.4 ukazuje, jak daleko můžete zajít se smíšenými diagramy. Tento graf kombinuje pět různých typů grafů: výsečový, plošný, sloupcový, sloupcový a bodový (XY). Nedokážu si představit, v jaké situaci by se takový diagram hodil, ale je to docela zajímavé demo.

V některých případech nelze kombinovat typy grafů. Nemůžete například vytvořit smíšený graf, který obsahuje bublinový a plošný (3D) graf. Pokud pro řadu vyberete nekompatibilní typ grafu, Excel vás na to upozorní.