Ako vytvoriť bodový graf v programe Excel (graf XY)

Program Excel má niekoľko užitočných typov grafov, ktoré je možné použiť na vykreslenie údajov a zobrazenie analýzy.

Bežným scenárom je, keď chcete vykresliť hodnoty X a Y do grafu v programe Excel a ukázať, ako tieto dve hodnoty súvisia.

To sa dá dosiahnuť pomocou a Bodový graf v programe Excel.

Ak máte napríklad údaje Výška (hodnota X) a Váha (hodnota Y) pre 20 študentov, môžete to vykresliť v bodovom grafe a ten vám ukáže, ako údaje spolu súvisia.

Nasleduje príklad bodového grafu v programe Excel (nazývaný aj graf XY):

V tomto tutoriále vám ukážem, ako vytvoriť bodový graf v programe Excel, rôzne typy bodových grafov a ako prispôsobiť tieto grafy.

Čo je to bodový graf a kedy ho použiť?

Bodové grafy sa používajú na pochopenie korelácie (príbuznosti) medzi dvoma dátovými premennými.

Bodový graf má bodky, kde každá bodka predstavuje dve hodnoty (hodnota osi X a hodnota osi Y) a na základe týchto hodnôt sú tieto body umiestnené v grafe.

Reálnym príkladom toho môžu byť marketingové náklady a príjmy skupiny spoločností v konkrétnom odvetví.

Keď vykreslíme tieto údaje (marketingové výdavky vs. výnosy) do bodového grafu, môžeme analyzovať, ako silne alebo voľne sú tieto dve premenné prepojené.

Vytvorenie bodového grafu v programe Excel

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a pomocou týchto údajov chcete vytvoriť bodový graf.

Cieľom tohto grafu je zistiť, či existuje súvislosť medzi marketingovým rozpočtom a príjmami alebo nie.

Na vytvorenie bodového grafu je dôležité mať obe hodnoty (dvoch premenných, ktoré chcete vykresliť v bodovom grafe) v dvoch oddelených stĺpcoch.

Stĺpec vľavo (v našom prípade stĺpec Marketing Expense) by bol vynesený na os X a výnosy by boli vynesené na os Y.

Nasledujú kroky na vloženie bodového grafu do programu Excel:

1. Vyberte stĺpce, ktoré obsahujú údaje (okrem stĺpca A)

2. Kliknite na možnosť Vložiť

3. V skupine Graf kliknite na ikonu Vložiť bodový graf

4. V zobrazených grafoch kliknite na možnosť „Bodový graf“

Vyššie uvedené kroky by vložili bodový graf, ako je uvedené nižšie v pracovnom hárku.

Stĺpec vľavo (v našom prípade stĺpec Marketing Expense) by bol vynesený na os X a výnosy by boli vynesené na os Y. Najlepšie je mať nezávislú metriku v ľavom stĺpci a metriku, pre ktorú potrebujete nájsť koreláciu v stĺpci vpravo.

Pridanie trendovej čiary do bodového grafu

Aj keď sa neskôr v tomto návode budem zaoberať ďalšími spôsobmi prispôsobenia bodového grafu v Exceli, jedna vec, ktorú môžete urobiť bezprostredne po vytvorení bodového grafu, je pridať trendovú čiaru.

Pomáha vám to rýchlo pochopiť, či sú údaje v pozitívnej alebo negatívnej korelácii a ako úzko/voľne korelujú.

Nasledujú kroky na pridanie trendovej čiary do bodového grafu v programe Excel:

1. Vyberte bodový graf (kam chcete pridať trendovú čiaru)
2. Kliknite na kartu Návrh grafu. Toto je kontextová karta, ktorá sa zobrazí iba vtedy, keď vyberiete graf

3. V skupine Rozloženie grafu kliknite na možnosť „Pridať prvok grafu“

4. Prejdite na možnosť „Trendline“ a potom kliknite na „Lineárne“

Vyššie uvedené kroky by do vášho bodového grafu pridali lineárnu trendovú čiaru.

Už pri pohľade na trendovú čiaru a dátové body vykreslené v bodovom grafe môžete získať predstavu o tom, či údaje pozitívne korelujú, negatívne korelujú alebo nesúvisia.

V našom prípade vidíme v trendovej čiare pozitívny sklon, ktorý naznačuje, že údaje sú v pozitívnej korelácii. To znamená, že keď sa zvýšia náklady na marketing, príjmy sa zvýšia a ak sa znížia náklady na marketing, príjmy sa znížia.

V prípade, že údaje sú v negatívnej korelácii, potom by existoval inverzný vzťah. V takom prípade, ak sa zvýšia marketingové náklady, príjmy sa znížia a naopak.

A potom existuje prípad, keď neexistuje žiadna korelácia. V takom prípade, keď sa zvýšia marketingové náklady, ich príjmy sa môžu, ale nemusia zvýšiť.

Všimnite si toho, že sklon nám iba hovorí, či údaje sú v pozitívnej alebo negatívnej korelácii. nehovorí nám, ako úzko to súvisí.

Napríklad v našom prípade pri pohľade na trendovú čiaru nemôžeme povedať, o koľko sa príjmy zvýšia, keď sa náklady na marketing zvýšia o 100%. Je to niečo, čo sa dá vypočítať pomocou korelačného koeficientu.

Môžete to zistiť pomocou nasledujúceho vzorca:

= CORREL (B2: B11, C2: C11)

Korelačný koeficient sa pohybuje medzi -1 a 1, pričom 1 by znamenal dokonale pozitívnu koreláciu a -1 by znamenal úplne negatívnu koreláciu

V našom prípade vráti 0,945, čo znamená, že tieto dve premenné majú vysokú pozitívnu koreláciu.

Identifikácia klastrov pomocou bodového grafu (praktické príklady)

Jeden zo spôsobov, ktorými som vo svojej práci finančného analytika používal bodové grafy, bolo identifikovať zhluky dátových bodov, ktoré vykazujú podobný druh správania.

To zvyčajne funguje dobre, ak máte rozmanitý súbor údajov s menšou celkovou koreláciou.

Predpokladajme, že máte súbor údajov, ako je uvedené nižšie, kde mám 20 spoločností s číslami výnosov a ziskovej marže.

Keď pre tieto údaje vytvorím scatterplot, dostanem niečo ako je uvedené nižšie:

V tomto grafe môžete vidieť, že dátové body sú všade naokolo a existuje veľmi nízka korelácia.

Aj keď nám tento graf veľa nehovorí, jedným spôsobom ho môžete použiť na identifikáciu klastrov v štyroch kvadrantoch v grafe.

Dátovým bodom v ľavom dolnom kvadrante sú napríklad tie spoločnosti, kde sú príjmy nízke a čisté ziskové rozpätie nízke a spoločnosti v pravom dolnom kvadrante sú tie, kde sú príjmy vysoké, ale čisté ziskové rozpätie je nízke.

Kedysi sme používali na identifikáciu potenciálnych zákazníkov na základe ich finančných údajov jeden z veľmi diskutovaných grafov na stretnutí manažmentu.

Rôzne typy bodových grafov v programe Excel

Okrem pravidelného bodového grafu, ktorý som uviedol vyššie, môžete v programe Excel vytvoriť aj nasledujúce typy bodových grafov:

• Rozptyl hladkými čiarami
• Rozptyl hladkými čiarami a značkami
• Rozptyl rovnými čiarami
• Rozptyl rovnými čiarami a značkami

Všetky tieto štyri vyššie uvedené bodové grafy sú vhodné vtedy, ak máte menej dátových bodov a keď do grafu vykresľujete dve série.

Predpokladajme napríklad, že máte údaje o marketingových výdavkoch a výnosoch, ako je uvedené nižšie, a chcete vykresliť bodový graf s plynulými čiarami.

Nasledujú kroky, ako to urobiť:

1. Vyberte množinu údajov (okrem stĺpca s názvom spoločnosti)
2. Kliknite na kartu Vložiť
3. V skupine Grafy kliknite na možnosť Vložiť bodový graf
4. Kliknite na položku Rozptyl s hladkými čiarami a značkami

Uvidíte niečo, ako je uvedené nižšie.

Tento graf sa môže rýchlo stať nečitateľným, ak máte viac dátových bodov. Preto sa odporúča používať ho len s menším počtom dátových bodov.

Tento graf som vo svojej práci nikdy nepoužil, pretože si myslím, že neposkytuje žiadny zmysluplný prehľad (pretože doň nemôžeme vykresliť viac dátových bodov).

Prispôsobenie bodového grafu v programe Excel

Rovnako ako každý iný graf v programe Excel, bodový graf môžete ľahko prispôsobiť.

V tejto časti sa budem zaoberať niektorými prispôsobeniami, ktoré môžete vykonať pomocou bodového grafu v programe Excel:

Pridávanie / odstraňovanie prvkov grafu

Keď kliknete na bodový graf, v pravej hornej časti grafu sa vám zobrazí ikona plus.

Po kliknutí na túto ikonu plus sa vám zobrazia možnosti, ktoré môžete jednoducho pridať alebo odstrániť z bodového grafu.

Tu sú možnosti, ktoré získate:

• Sekery
• Názov osi
• Názov grafu
• Dátové štítky
• Chybové pruhy
• Mriežky
• Legenda
• Trendové čiary

Niektoré z týchto možností sú už vo vašom grafe prítomné a tieto prvky môžete odstrániť kliknutím na začiarkavacie políčko vedľa možnosti (alebo ich pridať kliknutím na začiarkavacie políčko, ak už nie je začiarknuté).

Napríklad, ak chcem odstrániť názov grafu, môžem jednoducho zrušiť začiarknutie možnosti a bolo by to preč,

V prípade, že potrebujete väčšiu kontrolu, môžete kliknúť na malú čiernu šípku, ktorá sa zobrazí pri umiestnení kurzora na ľubovoľnú z možností.

Kliknutím na ňu získate ďalšie možnosti pre konkrétny prvok grafu (tieto sa otvoria ako tabla na pravej strane).

Poznámka: Všetky snímky obrazovky, ktoré som vám ukázal, pochádzajú z najnovšej verzie programu Excel (Microsoft 365). V prípade, že používate staršiu verziu, získate rovnaké možnosti kliknutím pravým tlačidlom myši na ktorýkoľvek z prvkov grafu a kliknutím na možnosť Formátovať.

Poďme si rýchlo prejsť tieto prvky a niektoré z úžasných prispôsobení, ktoré môžete urobiť, aby ste pomocou neho rozptýlili grafy.

Sekery

Osy sú zvislé a vodorovné hodnoty, ktoré vidíte hneď vedľa grafu.

Jednou z najužitočnejších úprav, ktoré môžete s osami vykonať, je upraviť maximálnu a minimálnu hodnotu, ktorú môže zobrazovať.

Ak to chcete zmeniť, kliknite pravým tlačidlom myši na osi v grafe a potom kliknite na položku Formátovať osi. Tým sa otvorí tabla Formátovať os.

Vo voľbe osi môžete nastaviť minimálne a maximálne hranice, ako aj hlavné a vedľajšie jednotky.

Vo väčšine prípadov to môžete nastaviť na automatické a Excel sa o to postará na základe množiny údajov. Ak však chcete konkrétne hodnoty, môžete ich tu zmeniť.

Jeden príklad môže byť, keď nechcete, aby minimálna hodnota v osi Y bola 0, ale niečo iné (povedzme 1 000). Zmenou dolnej hranice na 1000 sa graf upraví tak, aby minimálna hodnota na zvislej osi bola 1000.

Názov osi

Názov osi je niečo, čo môžete použiť na zadanie toho, čo os X a Y predstavujú v bodovom grafe v programe Excel.

V našom prípade by to bol čistý príjem pre os X a marketingové výdavky pre os Y.

Môžete sa rozhodnúť nezobrazovať žiadny názov osi a môžete ich odstrániť tak, že vyberiete graf, kliknete na ikonu plus a potom zrušíte začiarknutie políčka pre názov osi.

Ak chcete zmeniť text v názve osi, jednoducho naň dvakrát kliknite a potom zadajte ako názov osi čokoľvek, čo chcete.

Hodnotu názvu osi môžete tiež prepojiť s bunkou.

Ak napríklad chcete, aby sa hodnota v bunke B1 zobrazovala v názve zvislej osi, kliknite na pole s názvom osi a potom do riadka vzorcov zadajte = B1. Zobrazí sa hodnota v bunke B1 v názve osi.

To isté môžete urobiť pre názov horizontálnej osi a prepojiť ho s konkrétnou bunkou. Vďaka tomu sú tieto názvy dynamické a ak sa zmení hodnota bunky, zmenia sa aj názvy osí.

Ak potrebujete väčšiu kontrolu nad formátovaním názvov osí, kliknite na ľubovoľnú os, kliknite pravým tlačidlom myši a potom kliknite na položku Formátovať názov osi.

Pomocou týchto možností môžete zmeniť výplň a orámovanie nadpisu, zmeniť farbu textu, zarovnanie a otočenie.

Názov grafu

Rovnako ako názvy osí môžete aj názov grafu formátovať v bodovom grafe v programe Excel.

Názov grafu sa zvyčajne používa na opis toho, o čom graf je. Ako názov grafu môžem napríklad použiť „Marketing Expense Vs Revenue“.

Ak názov grafu nechcete, môžete naň kliknúť a odstrániť ho. A ak ho nemáte, vyberte graf, kliknite na ikonu plus a potom začiarknite možnosť Názov grafu.

Ak chcete upraviť text v názve grafu, dvakrát kliknite na pole a zadajte požadovaný text ručne. A v prípade, že chcete, aby bol názov grafu dynamický, kliknite na pole s názvom a potom do panela vzorcov zadajte odkaz na bunku alebo vzorec.

Ak chcete formátovať názov grafu, kliknite pravým tlačidlom myši na názov grafu a potom kliknite na možnosť „Formátovať názov grafu“. Vpravo sa zobrazí tabla Formátovať názov grafu.

Pomocou týchto možností môžete zmeniť výplň a orámovanie nadpisu, zmeniť farbu textu, zarovnanie a otočenie.

Dátové štítky

Štítky údajov nie sú predvolene viditeľné, keď v programe Excel vytvoríte bodový graf.

Môžete ich však ľahko pridať a naformátovať.

Pridajte štítky údajov do bodového grafu, vyberte graf, kliknite na ikonu plus vpravo a potom začiarknite možnosť štítkov údajov.

Tým sa pridajú štítky údajov, ktoré budú zobrazovať hodnotu osi Y pre každý dátový bod v bodovom grafe.

Ak chcete formátovať štítky s údajmi, kliknite pravým tlačidlom myši na ktorýkoľvek z nich a potom kliknite na možnosť „Formátovať štítky s údajmi“.

Vpravo sa otvorí pôvodná tabla s údajmi a môžete ich prispôsobiť pomocou rôznych možností uvedených na table.

Okrem pravidelného formátovania, akým je výplň, orámovanie, farba textu a zarovnanie, získate aj niekoľko ďalších možností štítkov, ktoré môžete použiť.

V možnostiach „Štítok obsahuje“ si môžete zvoliť, aby sa namiesto osi Y zobrazovala hodnota osi X aj osi Y.

Môžete tiež vybrať možnosť „Hodnota z buniek“. čo vám umožní mať štítky s údajmi, ktoré sa nachádzajú v stĺpci v pracovnom hárku (po výbere tejto možnosti sa otvorí dialógové okno a môžete si vybrať rozsah buniek, ktorých hodnoty by sa zobrazovali v štítkoch údajov. V našom prípade Môžem to použiť na zobrazenie názvov spoločností na štítkoch údajov

Môžete tiež prispôsobiť polohu štítka a formát, v ktorom sa zobrazuje.

Chybové pruhy

Aj keď som v bodových grafoch nevidel použitie chybových pruhov, Excel má možnosť, ktorá vám umožňuje pridať tieto chybové stĺpce pre každý údajový bod v bodovom grafe v programe Excel.

Ak chcete pridať chybové pruhy, vyberte graf, kliknite na ikonu plus a potom začiarknite možnosť Chybové pruhy.

A ak chcete tieto chybové pruhy ďalej prispôsobiť, kliknite pravým tlačidlom myši na niektorý z týchto chybových pruhov a potom kliknite na možnosť „Formátovať chybové pruhy“.

V pravej časti sa otvorí tabla „Formátovať chybové pruhy“, kde môžete prispôsobiť napríklad farbu, smer a štýl chybových pruhov.

Mriežky

Mriežka je užitočná vtedy, ak máte v grafe veľa dátových bodov, pretože umožňuje čitateľovi rýchlo pochopiť polohu dátového bodu.

Keď v programe Excel vytvoríte bodový graf, mriežky sú predvolene povolené.

Tieto mriežky môžete formátovať kliknutím pravým tlačidlom myši na ktorýkoľvek z mriežok a kliknutím na možnosť Formátovať mriežky.

Otvorí sa tabla Formát mriežok správnym spôsobom, akým môžete zmeniť formátovanie, ako je farba, hrúbka čiary mriežky.

Okrem hlavných mriežok, ktoré sú už viditeľné pri vytváraní bodového diagramu, môžete pridať aj menšie čiary mriežky.

Medzi dvoma hlavnými čiarami mriežky môžete mať niekoľko menších mriežok, ktoré ďalej zlepšujú čitateľnosť grafu v prípade, že máte veľa dátových bodov.

Ak chcete pridať menšie vodorovné alebo zvislé mriežky, vyberte graf, kliknite na ikonu plus a umiestnite kurzor na možnosť Mriežky.

Kliknite na hrubú čiernu šípku, ktorá sa zobrazí, a potom začiarknite možnosť „Primárne Minor Horizontálne“ alebo „Primárne Minor Vertikálne“ a pridajte vedľajšie mriežky

Legenda

Ak máte v bodovom grafe v programe Excel vykreslené viacero sérií, môžete použiť legendu, ktorá by označovala, ktorý údajový bod sa týka akej série.

Pri vytváraní bodového grafu v programe Excel štandardne neexistuje žiadna legenda.

Ak chcete do bodového grafu pridať legendu, vyberte graf, kliknite na ikonu plus a potom začiarknite možnosť legendy.

Ak chcete formátovať legendu, kliknite pravým tlačidlom na zobrazenú legendu a potom kliknite na možnosť „Formátovať legendu“.

Na table Legenda formátu, ktorá sa otvorí, môžete prispôsobiť farbu výplne, orámovanie a polohu legendy v grafe.

Trendová čiara

Do bodového grafu môžete tiež pridať trendovú čiaru, ktorá by ukazovala, či v súbore údajov existuje pozitívna alebo negatívna korelácia.

Už som sa zaoberal tým, ako pridať trendovú čiaru do bodového grafu v programe Excel v jednej z vyššie uvedených sekcií.

3D bodový graf v programe Excel (najlepšie sa mu vyhnúť)

Na rozdiel od čiarového grafu, stĺpcového grafu alebo plošného grafu neexistuje v programe Excel vstavaný 3D bodový graf.

Aj keď na to môžete použiť doplnky a nástroje tretích strán, nemôžem myslieť na žiadne ďalšie výhody, ktoré získate s 3D bodovým grafom v porovnaní s bežným 2D bodovým grafom.

V skutočnosti odporúčam vyhýbať sa akémukoľvek druhu 3D grafu, pretože má potenciál skresľovať údaje a časti v grafe.

Takto teda môžete vytvoriť bodový graf v programe Excel a prispôsobiť ho tak, aby zodpovedal vašej značke a požiadavkám.

Dúfam, že ste našli tento návod užitočný.