Zmiešaný graf v exceli. Kombinovanie rôznych typov grafov v programe EXCEL

Vytváranie kombinovaných grafov

V prípadoch, keď potrebujete na jednom grafe zobraziť údaje rôznych mierok, je užitočné použiť kombinované grafy. Typickým prípadom sú absolútne (ruble) a relatívne (percentuálne) ukazovatele. Napríklad veľkosť pohľadávok a ich podiel na tržbách.

Kombinované (alebo zmiešané ) je graf, ktorý pozostáva z viacerých radov údajov a súčasne používa rôzne typy grafov (napríklad histogram a graf). Na vytvorenie kombinovaných grafov potrebujete aspoň dva rady údajov. Príklady zmiešaných diagramov sú uvedené v knihe Kombinované

Pri vytváraní kombinovaných grafov existujú určité obmedzenia:

· nie je možné miešať žiadne typy grafov s objemovými typmi;

· niektoré kombinácie typov máp vyzerajú mimoriadne neuspokojivo (napríklad kombinácia grafu a radarovej mapy);

· zmiešaný graf používa jednu vykresľovaciu oblasť, preto nie je možné vytvoriť napríklad kombinovaný graf pozostávajúci z troch koláčových grafov;

· Kombinácia grafu a stĺpcového diagramu nie je podporovaná: os kategórie stĺpcového diagramu je vždy orientovaná vertikálne a os grafu je vždy orientovaná horizontálne.

Obrázok ukazuje graf zostavený pomocou troch radov údajov. Okrem toho sú teploty vzduchu a vody prezentované vo forme histogramu a množstvo zrážok je prezentované v grafe.

Obrázok ukazuje histogram zostavený pomocou dvoch radov údajov:

Zmeňme typ grafu pre druhú sériu údajov ( Zrážky) a pre tento rad použite samostatnú os hodnôt.

1. Vyberte sériu údajov na diagrame Zrážky a zavolajte kontextovú ponuku riadku.

2. Otvorte okno Formát radov údajov (Formátovať rad údajov) a na karte možnosti riadok (Možnosti série) nainštalujte spínač Autor: pomocný osi(sekundárna os).

3. Bez odstránenia výberu z riadku vykonajte príkaz Práca s grafmi– Konštruktér – Typ – Zmeniť typu diagramy(Graf Nástroje – Dizajn – Typ – Zmeniť Graf Typ) .

4. V dialógovom okne Zmeniť typ grafu vyberte typ Rozvrh (Linka) a kliknite na tlačidlo OK.

Údaje o zrážkach sú teraz vizualizované ako priame segmenty s novým na pravej strane Hodnotová os (ValueAxis).

Je dôležité pochopiť, že tým Práca s grafmi– Konštruktér – Typ – Zmeniť typu diagramy (Graf Nástroje – Dizajn – Typ – Zmeniť Graf Typ) funguje odlišne v závislosti od toho, čo je vybraté. Ak je vybratá séria grafov, príkaz zmení typ iba tejto série. Ak je vybratý akýkoľvek iný prvok diagramu, príkaz zmení typ celého diagramu.

Prekrytie diagramy (OverlayCjeleny)

Prekrytie grafu sa vzťahuje na umiestnenie grafov ako grafické objekty: jeden na druhom. Horný graf má zvyčajne priehľadnú oblasť grafu a oblasť vykresľovania. Odstráni všetky prvky okrem napríklad značiek a čiar. Prekrytie grafu je manuálna úloha, ktorá vyžaduje presné umiestnenie objektov, nastavenie osí hodnôt a kategórií.

Aby bolo možné usporiadať diagramy „naskladané“ v príslušnom poradí, je potrebné ich vybrať ako objekty. Ak chcete namiesto grafu vybrať objekt, stlačte Ctrl a kliknite na diagram.

Pozrime sa na jednoduchý príklad (list Prekrytie_1 knihy Kombinované). Je známe, že jedným z obmedzení vytvárania kombinovaných grafov je nemožnosť miešať ľubovoľné typy grafov s objemovými typmi. Metóda prekrývania grafu vám umožňuje kombinovať graf a objemový histogram na hárku. Urobme nasledujúce kroky:

1. Vyberte rozsah v tabuľke údajov A2:B7 a vytvorte objemový histogram so zoskupením. Odstránime legendu grafu.

2. Vyberte nesusediace rozsahy A2:A7 A C2:C7 a vytvorte graf pomocou značiek. Odstránime legendu grafu.

3. Na druhom grafe nastavte priehľadnú výplň pre oblasť grafu a oblasť vykresľovania grafu a odstráňte osi. Naformátujme čiary a značky grafu.

4. Manuálne skombinujte graf a objemový histogram. Zmenou veľkosti objektu „Graph“ zabezpečíme, aby sa značky grafu zhodovali so stredom stĺpcov histogramu.

5. Po konečnom formátovaní dostaneme prekrytý diagram.

6. Ak po výbere dvoch diagramov ako grafických objektov ich zoskupíte, výsledný superponovaný diagram sa skopíruje a presunie ako jeden objekt. Upozorňujeme, že pokus umiestniť graf na samostatný hárok zničí prekrytie.

Nasledujúci obrázok zobrazuje prekrytie 3-D koláčového grafu a 3-D histogramu. Celkový rám vyvoláva dojem, že ide o jeden diagram. Ide však o dva samostatné diagramy. Rám nepatrí im, ale bunkám pracovného hárka. Nadpis je voľne pohyblivý nápis. Údaje pre takéto prekrytie sú uvedené na hárku Kruhový _objemový knihy Kombinované.

Objemové histogramy Excelu neumožňujú zobraziť ďalšie riadky do hĺbky. Prekrývajúce sa grafy prekonávajú toto obmedzenie. Stačí zostaviť tri objemové histogramy s akumuláciou (pre každý rok zvlášť) a skombinovať ich tak, aby „vytvorili“ tretiu os do hĺbky. Graf umožňuje vizuálne porovnávať výsledky podľa troch parametrov – krajov, mesiacov a rokov. Pôvodná dátová tabuľka je v pracovnom hárku Tri_zväzok knihy Kombinované.

Štandardné excelové nástroje pre koláčové grafy vám umožňujú používať iba jednu množinu údajov. Tento príspevok vám ukáže, ako vytvoriť koláčový graf na základe dvoch sád hodnôt.

Ako príklad som vzal populáciu Zeme podľa kontinentov v rokoch 1950 a 2000. (pozri hárok „Populácia“ v súbore Excel; Austráliu som odstránil, pretože jej podiel je zanedbateľný a diagram sa stáva ťažko čitateľným :)). Najprv vytvorte základný koláčový graf: vyberte rozsah A1:C6, prejdite na Vložiť → Koláč → Koláč.

Ryža. 1. Vytvorte obyčajný koláčový graf

Stiahnite si poznámku vo formáte, príklady vo formáte

Kliknite pravým tlačidlom myši na graf a v kontextovej ponuke vyberte položku Formátovať rad údajov. Vyberte "Pozdĺž vedľajšej osi" a potom posuňte posúvač smerom k "Separation" na približne 70 % (obrázok 2). Sektory toho istého radu sa „rozdelia“.

Ryža. 2. Pozdĺž vedľajšej osi

Postupne vyberte jednotlivé sektory (pomalým dvojitým kliknutím) a zmeňte ich výplň a umiestnenie, pričom spojte všetky sektory v strede (obr. 3).

Ryža. 3. Formátovanie bodov riadkov (jednotlivé sektory)

Naformátujte všetky sektory tak, aby farby zodpovedajúce rovnakému kontinentu v rôznych riadkoch boli rovnakého rozsahu, ale s rôznou intenzitou. Doplňte tabuľku štítkami údajov, legendou a názvom (obrázok 4).

Ryža. 4. Koláčový graf s dvoma súbormi údajov

Diagram napríklad jasne ukazuje, že podiel Ázie vzrástol za 50 rokov z 55,8 % na 60,9, zatiaľ čo podiel Európy sa za rovnaký čas znížil z 21,8 % na 12,1 %.

Ak nie ste fanúšikom koláčových grafov, môžete použiť prstencový graf, ktorý v štandarde Excel pracuje s viacerými množinami údajov (obr. 5); pozri tiež hárok „Ring“ v súbore Excel. Vyberte oblasť údajov (v našom príklade je to A1:C6) a prejdite do ponuky Vložiť – Grafy – Iné grafy – Donut:

Ryža. 5. Vytvorte prstencový graf

Stačí len trochu upraviť diagram, aby bol vizuálnejší (obr. 6)

Ryža. 6. Donutový graf

Nápad bol videný v knihe D. Kholeyho, R. Kholeyho „Excel 2007. Triky“.

Histogram v Exceli je spôsob, ako vytvoriť vizuálny graf, ktorý zobrazuje zmeny v niekoľkých typoch údajov za určité časové obdobie.

Histogram je užitočný na znázornenie a porovnanie rôznych parametrov. Pozrime sa na najpopulárnejšie typy histogramov a naučíme sa ich zostavovať.

Ako vytvoriť aktualizovaný histogram?

Máme údaje o predaji rôznych druhov mliečnych výrobkov za každý mesiac za rok 2015.

Zostavme aktualizovaný histogram, ktorý bude reagovať na zmeny vykonané v tabuľke. Vyberte celé pole vrátane hlavičky a kliknite na kartu INSERT. Vyhľadáme DIAGRAMY - HISTOGRAM a vyberieme úplne prvý typ. Volá sa to HISTOGRAM SO ZOSKUPOVANÍM.

Dostali sme histogram, ktorého veľkosť poľa je možné zmeniť. Z tohto grafu je napríklad zrejmé, že najväčší predaj bol v novembri za mlieko a najmenší za smotanu v júni.

Ak vykonáme zmeny v tabuľke, vzhľad zmení sa aj histogram. Napríklad namiesto 1400 v januári za kefír dajme 4000. Vidíme, ako zelený stĺpec vyletel nahor.

Skladaný histogram

Teraz sa pozrime na to, ako vytvoriť skladaný histogram v Exceli. Ďalší typ histogramu, ktorý umožňuje zobraziť údaje v percentách. Stavia sa úplne rovnako, ale je zvolený iný typ.

Získame diagram, na ktorom môžete vidieť, že napríklad v januári sa predalo viac mlieka ako kefíru alebo smotany. A v auguste sa v porovnaní s inými mliečnymi výrobkami predalo málo mlieka. A tak ďalej.

Histogramy v Exceli je možné upravovať. Ak teda klikneme pravým tlačidlom myši do prázdnej oblasti diagramu a vyberieme ZMENIŤ TYP, môžeme ho trochu upraviť. Zmeňme typ nášho histogramu zo skladaného na normalizovaný. Výsledkom bude rovnaký graf, ale na osi Y sa zobrazia pomery v percentách.

Podobne môžete vykonať ďalšie zmeny v histograme, čo sme urobili:

• zmenil písmo na Arial a zmenil jeho farbu na fialovú;
• urobil podčiarknutie bodkovanou čiarou;
• posunul legendu o niečo vyššie;
• pridané titulky do stĺpcov.

Ako skombinovať histogram a graf v Exceli?

Niektoré množiny údajov vyžadujú konštrukciu zložitejších diagramov, ktoré kombinujú niekoľko typov údajov. Napríklad histogram a graf.

Pozrime sa na príklad. Najprv do tabuľky s údajmi pridáme ďalší riadok, kde sa zaznamenávajú tržby za každý mesiac. Uvádza sa v rubľoch.

Teraz upravme existujúci diagram. Kliknite pravým tlačidlom myši na prázdne miesto a vyberte VYBRAŤ ÚDAJE. Zobrazí sa pole s výzvou na výber iného intervalu. Opäť vyberieme celú tabuľku, tentoraz však vrátane riadku s výnosmi.

Excel automaticky rozšíril rozsah hodnôt pozdĺž osi Y, takže údaje o počte predajov zostali úplne dole vo forme neviditeľných stĺpcov.

Ale takýto histogram je nesprávny, pretože na jednom diagrame máme čísla v rubľoch a kvantitatívne ekvivalenty (ruble a litre). Preto je potrebné vykonať zmeny. Presuňme údaje o výnosoch na pravú stranu. Kliknite pravým tlačidlom myši na fialové stĺpce, vyberte FORMÁT DÁTOVÉHO SÉRIE a vyberte POMOCNÁ OS.

Vidíme, že harmonogram sa okamžite zmenil. Teraz má fialový pruh príjmov svoju vlastnú oblasť hodnôt (vpravo).

To však stále nie je príliš výhodné, pretože stĺpce sa takmer spájajú. Preto vykonáme ešte jednu akciu navyše: kliknite pravým tlačidlom myši na fialové stĺpce a vyberte ZMENIŤ TYP GRAFu PRE SÉRIU. Zobrazí sa okno, v ktorom vyberieme graf, úplne prvý typ.

Dostaneme veľmi prehľadný diagram, ktorý je kombináciou histogramu a grafu. Vidíme, že maximálny výnos bol v januári a novembri a minimálny v auguste.

V Exceli môžete zobraziť výsledky výpočtov vo forme tabuľky alebo grafu, čím získate väčšiu prehľadnosť a pre porovnanie je niekedy potrebné zostaviť dva grafy vedľa seba. Ďalej sa pozrieme na to, ako zostrojiť dva grafy v Exceli na jednom poli.

Začnime tým, že nie každý typ grafu v Exceli dokáže zobraziť presne taký výsledok, aký očakávame. Napríklad existujú výsledky výpočtov pre niekoľko funkcií na základe rovnakých počiatočných údajov. Ak z týchto údajov zostavíte pravidelný histogram alebo graf, pri konštrukcii sa nebudú brať do úvahy pôvodné údaje, ale len ich množstvo, medzi ktorými budú určené rovnaké intervaly.

Vyberieme dva stĺpce výsledkov výpočtu a zostavíme pravidelný histogram.

Teraz skúsme pridať k existujúcim histogramom ďalší s rovnakým počtom výsledkov výpočtov. Ak chcete pridať graf v Exceli, aktivujte existujúci graf jeho výberom a na zobrazenej karte "konštruktér" vybrať "Vybrať údaje". V okne, ktoré sa zobrazí v sekcii "Prvky legendy" kliknite na pridať a zadajte bunky "Názov riadku:" A "Hodnoty:" na hárku, čo budú hodnoty výpočtu funkcie "j".

Teraz sa pozrime, ako bude vyzerať náš graf, ak k existujúcim histogramom pridáme ďalší, ktorý má takmer dvojnásobok hodnôt. Pridajme hodnoty funkcií do grafu "k".

Ako vidíte, nedávno pridaných hodnôt je oveľa viac a sú také malé, že ich na histograme prakticky nevidno.

Ak zmeníme typ grafu z histogramu na bežný graf, výsledok v našom prípade bude prehľadnejší.

Ak na zostavenie grafov v Exceli použijete bodový graf, tak výsledné grafy budú zohľadňovať nielen výsledky výpočtu, ale aj zdrojové údaje, t.j. medzi množstvami bude jasný vzťah.

Ak chcete vytvoriť bodový graf, vyberte stĺpec počiatočných hodnôt a pár stĺpcov výsledkov pre dve rôzne funkcie. Na karte "Vložiť" vyberte bodový graf s hladkými krivkami.

Ak chcete pridať ďalší graf, vyberte existujúce a na karte "konštruktér" stlačte tlačidlo "Vybrať údaje".

V novom okne v stĺpci "Prvky legendy" stlačte tlačidlo "Pridať" a označte bunky pre "Názov riadku:", "Hodnoty X:" A "Hodnoty Y:". Pridajme funkciu týmto spôsobom "j" do grafu.

Grafy zvyčajne používajú jeden štýl, napríklad stĺpcový, pruhový alebo koláčový graf. Ak má váš graf viac ako jeden rad údajov, môžete v grafe zobraziť rôzne štýly a vytvoriť tak zmiešaný graf.

Na obr. Obrázok 136.1 ukazuje diagram, ktorý je vhodný na konverziu na zmiešaný. Graf znázorňuje priemernú teplotu a zrážky podľa mesiacov. Ale je tu problém. Tieto dva rozsahy údajov sa jasne líšia veľkosťou, takže pruhy zrážok sú v grafe sotva viditeľné.

Na obr. Obrázok 136.2 je diagram po tom, čo som ho previedol na zmiešaný diagram. Hodnoty zrážok sú teraz vykreslené ako čiara a používa sa druhá zvislá os sprava. Väčšina z vás bude súhlasiť s tým, že tento graf predstavuje výrazné zlepšenie oproti pôvodnému. Môžete ho použiť pri analýze nákladov na tovar v klenotníckom salóne vo Vologde.

Príkaz Zmiešané grafy môžete hľadať na páse s nástrojmi Excel celý deň a stále ho nenájdete. Ak ste už vytvorili svoj graf (ktorý má aspoň dva rady údajov), môžete ho jednoducho previesť na zmiešaný graf. Nasleduj tieto kroky:

1. Kliknite na riadky, ktoré chcete zmeniť.
2. Vyberte Práca s grafmi Typ dizajnéra Zmena typu grafu.
3. V dialógovom okne Zmeniť typ grafu vyberte ikonu, ktorá predstavuje typ grafu pre vybraté série.
4. Ak chcete použiť druhú zvislú os pre skonvertovaný rad, vyberte sériu a kliknite Ctrl+1 otvoríte dialógové okno Formát radov údajov; prejdite do sekcie Parametre série a nastavte prepínač Zostavte rad do polohy Pozdĺž vedľajšej osi.

Schéma na obr. 136.1 bol štandardný stĺpcový graf s dvoma riadkami. Niekoľko kliknutí a jeden zo série (v tomto príklade zrážky) prevedený zo stĺpcov na čiaru. Pridal som aj druhú os a označil som osi, aby bolo jasné, ktorá stupnica sa používa pre ktorú sériu. Ak chcete pridať názvy osí, spustite príkaz Práca s názvami osi rozloženia grafov.

Na obr. Obrázok 136.3 zobrazuje ďalší zmiešaný diagram. Kombinuje stĺpcový a stĺpcový graf a oba rady používajú rovnakú zvislú os. V tomto prípade zobrazenie mesačných cieľov predaja ako stĺpcového grafu uľahčuje vyhľadanie mesiacov, v ktorých bol cieľ splnený.

Na obr. Obrázok 136.4 ukazuje, ako ďaleko môžete zájsť so zmiešanými diagramami. Tento graf kombinuje päť rôznych typov grafov: koláčový, plošný, pruhový (stĺpcový), pruhový a bodový (XY). Neviem si predstaviť, v akej situácii by bol takýto diagram vhodný, ale je to celkom zaujímavé demo.

V niektorých prípadoch nie je možné kombinovať typy grafov. Nemôžete napríklad vytvoriť zmiešaný graf, ktorý obsahuje bublinový a povrchový (3D) graf. Ak pre sériu vyberiete nekompatibilný typ grafu, Excel vás na to upozorní.