Kako napraviti raspršeni grafikon u Excelu (XY grafikon)

Excel ima neke korisne vrste grafikona koje se mogu koristiti za iscrtavanje podataka i prikaz analize.

Uobičajen je scenarij gdje želite iscrtati vrijednosti X i Y u grafikonu u Excelu i pokazati kako su dvije vrijednosti povezane.

To se može učiniti pomoću a Raspršeni grafikon u Excelu.

Na primjer, ako imate podatke o visini (vrijednost X) i težini (vrijednost Y) za 20 učenika, to možete iscrtati u raspršenom grafikonu i on će vam pokazati kako su podaci povezani.

Ispod je primjer Scatter Plot u Excelu (koji se naziva i XY grafikon):

U ovom tutorialu pokazat ću vam kako napraviti raspršeni grafikon u Excelu, različite vrste raspršenih grafikona i kako prilagoditi ove grafikone.

Što je raspršeni grafikon i kada ga koristiti?

Scatter grafikoni koriste se za razumijevanje korelacije (povezanosti) između dvije varijable podataka.

Raspršena ploha ima točke gdje svaka točka predstavlja dvije vrijednosti (vrijednost osi X i vrijednost osi Y) i na temelju tih vrijednosti te se točke postavljaju na grafikonu.

Primjer iz stvarnog života za to mogu biti marketinški troškovi i prihodi skupine tvrtki u određenoj industriji.

Kada ove podatke (marketinški troškovi nasuprot prihoda) unesemo u raspršeni grafikon, možemo analizirati koliko su snažno ili labavo povezane ove dvije varijable.

Stvaranje raspršenog grafikona u Excelu

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite stvoriti raspršenu plohu koristeći te podatke.

Cilj ovog grafikona je vidjeti postoji li povezanost između marketinškog proračuna i prihoda ili ne.

Za izradu raspršenog grafikona važno je imati obje vrijednosti (dvije varijable koje želite ucrtati u raspršeni grafikon) u dva odvojena stupca.

Stupac s lijeve strane (stupac marketinških troškova u našem primjeru) bio bi ucrtan na osi X, a prihod bi se iscrtao na osi Y.

U nastavku su navedeni koraci za umetanje raspršenog grafikona u Excel:

1. Odaberite stupce koji sadrže podatke (isključujući stupac A)

2. Pritisnite opciju Umetanje

3. U grupi Chart kliknite ikonu Insert Scatter Chart

4. Kliknite na opciju "Scatter chart" na grafikonima koji se pojavljuju

Gore navedeni koraci umetnuli bi raspršenu plohu kao što je dolje prikazano na radnom listu.

Stupac s lijeve strane (stupac marketinških troškova u našem primjeru) bio bi ucrtan na osi X, a prihod bi se iscrtao na osi Y. Najbolje je imati neovisnu metriku u lijevom stupcu i onu za koju trebate pronaći korelaciju u stupcu s desne strane.

Dodavanje linije trenda na raspršenu tablicu

Iako ću kasnije u ovom vodiču pokriti više načina prilagođavanja raspršenog grafikona u Excelu, jedna stvar koju možete učiniti odmah nakon izgradnje raspršenog grafikona je dodavanje linije trenda.

Pomaže vam da brzo steknete uvid u to jesu li podaci pozitivno ili negativno povezani i koliko su oni čvrsto/labavo povezani.

U nastavku su navedeni koraci za dodavanje linije trenda na raspršeni grafikon u Excelu:

1. Odaberite Scatter plot (gdje želite dodati liniju trenda)
2. Kliknite karticu Dizajn grafikona. Ovo je kontekstualna kartica koja se prikazuje samo kada odaberete grafikon

3. U grupi Rasporedi grafikona kliknite opciju ‘Dodaj element grafikona’

4. Idite na opciju "Trendline", a zatim kliknite na "Linear"

Gore navedeni koraci dodali bi linearnu liniju trenda vašem raspršenom grafikonu.

Samo gledajući liniju trenda i podatkovne točke ucrtane u raspršenom grafikonu, možete dobiti osjećaj jesu li podaci pozitivno povezani, negativno povezani ili nisu.

U našem primjeru vidimo pozitivan nagib u liniji trenda koji ukazuje na to da su podaci pozitivno povezani. To znači da kada se povećaju marketinški troškovi, prihod raste, a ako se marketinški troškovi smanje, prihodi se smanjuju.

U slučaju da su podaci negativno povezani, tada bi postojala inverzna veza. U tom slučaju, ako se troškovi marketinga povećaju, prihod bi se smanjio i obrnuto.

A onda postoji slučaj gdje nema korelacije. U ovom slučaju, kada se povećaju marketinški troškovi, njihov se prihod može povećati, ali i ne povećati.

Imajte na umu da nam nagib samo govori jesu li podaci pozitivno ili negativno povezani. ne govori nam koliko je to povezano.

Na primjer, u našem primjeru, gledajući liniju trenda, ne možemo reći koliko će se prihod povećati kad se troškovi marketinga povećaju za 100%. To je nešto što se može izračunati korištenjem koeficijenta korelacije.

To možete pronaći pomoću donje formule:

= CORREL (B2: B11, C2: C11)

Koeficijent korelacije varira između -1 i 1, pri čemu 1 označava savršeno pozitivnu korelaciju, a -1 savršeno negativnu korelaciju

U našem primjeru vraća 0,945, što ukazuje da ove dvije varijable imaju visoku pozitivnu korelaciju.

Prepoznavanje klastera pomoću raspršenog grafikona (praktični primjeri)

Jedan od načina na koji sam u svom radu kao financijski analitičar koristio raštrkane grafikone bio je identificiranje skupina podatkovnih točaka koje pokazuju sličnu vrstu ponašanja.

To obično dobro funkcionira ako imate raznolik skup podataka s manje ukupne korelacije.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku, gdje imam 20 tvrtki s brojevima prihoda i marže.

Kad stvorim raspršeni grafikon za ove podatke, dobivam nešto kao što je prikazano u nastavku:

Na ovom grafikonu možete vidjeti da su podatkovne točke posvuda i da postoji vrlo niska korelacija.

Iako nam ovaj grafikon ne govori mnogo, na jedan način pomoću njega možete identificirati klastere u četiri kvadranta na grafikonu.

Na primjer, podatkovna točka u donjem lijevom kvadrantu su ona poduzeća u kojima je prihod nizak, a neto profitna marža niska, a tvrtke u donjem desnom kvadrantu ona su u kojima je prihod visok, ali je neto profitna marža niska.

Ovo je nekad bio jedan od visoko raspravljanih grafikona na sastanku uprave kada smo identificirali potencijalne kupce na temelju njihovih financijskih podataka.

Različite vrste raspršenih ploha u Excelu

Osim redovnog raspršenog grafikona koji sam gore opisao, u Excelu možete stvoriti i sljedeće vrste raspršenih grafikona:

• Raspršivanje s glatkim linijama
• Raspršivač s glatkim linijama i oznakama
• Raspršite s ravnim linijama
• Raštrkajte ravnim crtama i oznakama

Sve ove četiri gornje raspršene sheme prikladne su kada imate manje podatkovnih točaka i kada crtate dvije serije na grafikonu.

Na primjer, pretpostavimo da imate podatke o marketinškim troškovima u odnosu na prihode kao što je prikazano u nastavku, a želite iscrtati raspršeni grafikon glatkim linijama.

U nastavku su navedeni koraci za to:

1. Odaberite skup podataka (isključujući stupac naziva tvrtke)
2. Kliknite karticu Umetanje
3. U grupi Grafikoni kliknite opciju Umetni raspršeni grafikon
4. Pritisnite Scatter with Smooth Lines and Markers

Vidjet ćete nešto kako je dolje prikazano.

Ovaj grafikon može brzo postati nečitljiv ako imate više podatkovnih točaka. Zbog toga se preporučuje korištenje samo s manje podatkovnih točaka.

Nikada nisam koristio ovaj grafikon u svom radu jer mislim da ne daje nikakav smislen uvid (budući da na njega ne možemo nacrtati više podataka).

Prilagodba Scatter grafikona u Excelu

Kao i bilo koji drugi grafikon u Excelu, možete jednostavno prilagoditi raspršenje.

U ovom odjeljku pokriti ću neke prilagodbe koje možete napraviti pomoću raspršenog grafikona u Excelu:

Dodavanje / uklanjanje elemenata grafikona

Kada kliknete na raspršeni grafikon, vidjet ćete ikonu plus u gornjem desnom dijelu grafikona.

Kada kliknete na ovu ikonu plus, prikazat će vam se opcije koje možete jednostavno dodati ili ukloniti sa raspršenog grafikona.

Evo mogućnosti koje imate:

• Sjekire
• Naslov osi
• Naslov grafikona
• Oznake podataka
• Trake grešaka
• Rešetke
• Legenda
• Trendlines

Neke od ovih opcija već su prisutne na vašem grafikonu, a te elemente možete ukloniti klikom na potvrdni okvir pored opcije (ili ih dodati klikom na potvrdni okvir ako već nije označeno).

Na primjer, ako želim ukloniti naslov grafikona, mogu jednostavno poništiti odabir opcije i više je neće biti,

U slučaju da vam je potrebna veća kontrola, možete kliknuti na malu crnu strelicu koja se pojavi kada zadržite pokazivač miša iznad bilo koje od opcija.

Klikom na njega dobit ćete više mogućnosti za taj određeni element grafikona (oni se otvaraju kao okno s desne strane).

Napomena: Svi snimci zaslona koje sam vam pokazao potiču iz novije verzije programa Excel (Microsoft 365). U slučaju da koristite stariju verziju, iste opcije možete dobiti kada desnom tipkom miša kliknete bilo koji element grafikona i kliknete na opciju Oblikovanje.

Idemo brzo proći kroz ove elemente i neke od izvrsnih prilagodbi koje možete učiniti kako biste pomoću njih razbacili grafikone.

Sjekire

Osi su okomite i vodoravne vrijednosti koje vidite tik uz grafikon.

Jedna od najkorisnijih prilagodbi koje možete napraviti s osama je podešavanje maksimalne i minimalne vrijednosti koju može prikazati.

Da biste to promijenili, desnom tipkom miša kliknite osi na grafikonu, a zatim kliknite Oblikovanje osi. Ovo će otvoriti okno Oblikovanje osi.

U opciji Osa možete postaviti minimalne i maksimalne granice, kao i glavne i manje jedinice.

U većini slučajeva ovo možete postaviti na automatsko, a Excel će se pobrinuti za to na temelju skupa podataka. No u slučaju da želite određene vrijednosti, možete ih promijeniti odavde.

Jedan primjer može biti kada ne želite da minimalna vrijednost na osi Y bude 0, već nešto drugo (recimo 1000). Promjenom donje granice na 1000 grafikon će se prilagoditi tako da tada minimalna vrijednost u okomitoj osi bude 1000.

Naslov osi

Naslov osi je nešto što možete koristiti za navođenje onoga što osi X i Y predstavljaju u raspršenom grafikonu u Excelu.

U našem primjeru to bi bio neto prihod za os X i marketinški izdaci za os Y.

Možete odabrati da se ne prikazuje naslov osi, a možete ih ukloniti odabirom grafikona, klikom na ikonu plus, a zatim poništite okvir za naslov osi.

Da biste promijenili tekst u naslovu osi, jednostavno dvaput kliknite na njega, a zatim upišite što god želite kao naslov osi.

Također možete povezati vrijednost naslova osi s ćelijom.

Na primjer, ako želite da se vrijednost u ćeliji B1 prikaže u naslovu okomite osi, kliknite okvir naslova osi, a zatim unesite = B1 u traku s formulama. To će pokazati vrijednost u ćeliji B1 u naslovu osi.

Isto možete učiniti za naslov vodoravne osi i povezati ga s određenom ćelijom. To čini ove naslove dinamičnim, a ako se promijeni vrijednost ćelije, promijenit će se i naslovi osi.

Ako vam je potrebna veća kontrola oblikovanja naslova osi, kliknite bilo koju os, kliknite desnom tipkom miša, a zatim kliknite Oblikovanje naslova osi.

Pomoću ovih opcija možete promijeniti ispunu i obrub naslova, promijeniti boju teksta, poravnanje i rotaciju.

Naslov grafikona

Baš kao i naslove osi, možete i oblikovati naslov grafikona u raspršenom grafikonu u Excelu.

Naslov grafikona obično se koristi za opisivanje o čemu se radi grafikon. Na primjer, kao naslov grafikona mogu upotrijebiti "Marketing Expense vs Vs Revenue".

Ako ne želite naslov grafikona, možete ga kliknuti i izbrisati. A u slučaju da ga nemate, odaberite grafikon, kliknite ikonu plus, a zatim provjerite opciju Naslov grafikona.

Da biste uredili tekst u naslovu grafikona, dvaput kliknite okvir i ručno upišite željeni tekst. A u slučaju da naslov grafikona želite učiniti dinamičnim, možete kliknuti okvir naslova, a zatim upisati referencu ćelije ili formulu u traku s formulama.

Za oblikovanje naslova grafikona desnom tipkom miša kliknite naslov grafikona, a zatim kliknite opciju "Oblikovanje naslova grafikona". Ovo će prikazati okno Oblikovanje naslova grafikona s desne strane.

Pomoću ovih opcija možete promijeniti ispunu i obrub naslova, promijeniti boju teksta, poravnanje i rotaciju.

Oznake podataka

Prema zadanim postavkama, podatkovne naljepnice nisu vidljive kada stvorite raspršivanje u Excelu.

Ali možete ih jednostavno dodati i oblikovati.

Dodajte podatkovne oznake na raspršeni grafikon, odaberite grafikon, kliknite ikonu plus s desne strane, a zatim provjerite opciju podatkovnih oznaka.

To će dodati podatkovne oznake koje će prikazivati ​​vrijednost osi Y za svaku podatkovnu točku u raspršenom grafikonu.

Za oblikovanje podatkovnih oznaka desnom tipkom miša kliknite bilo koju od podatkovnih oznaka, a zatim kliknite opciju "Oblikovanje podatkovnih oznaka".

Ovo će otvoriti prethodno okno s oznakama podataka s desne strane i možete ih prilagoditi pomoću različitih opcija navedenih u oknu.

Osim uobičajenog oblikovanja, poput ispune, obruba, boje teksta i poravnanja, dobivate i neke dodatne opcije oznaka koje možete koristiti.

U opcijama "Oznaka sadrži" možete odabrati prikaz osi X i osi Y, umjesto samo osi Y.

Također možete odabrati opciju ‘Vrijednost iz ćelija’. što će vam omogućiti da imate oznake podataka koje se nalaze u stupcu na radnom listu (otvara dijaloški okvir kada odaberete ovu opciju i možete odabrati raspon ćelija čije bi se vrijednosti prikazale u oznakama podataka. U našem primjeru, To mogu upotrijebiti za prikaz naziva tvrtki na oznakama podataka

Također možete prilagoditi položaj naljepnice i format u kojem je prikazana.

Trake grešaka

Iako nisam vidio da se trake pogrešaka koriste u raspršenim grafikonima, Excel ima opciju koja vam omogućuje dodavanje ovih traka pogrešaka za svaku podatkovnu točku u raspršenom grafikonu u Excelu.

Da biste dodali trake pogrešaka, odaberite grafikon, kliknite ikonu plus, a zatim provjerite opciju Trake pogrešaka.

A ako želite dodatno prilagoditi ove trake pogrešaka, desnom tipkom miša kliknite bilo koju od ovih traka pogrešaka, a zatim kliknite opciju "Formatiraj trake pogrešaka".

Ovo će otvoriti okno "Oblikovanje traka pogrešaka" s desne strane gdje možete prilagoditi stvari poput boje, smjera i stila traka pogrešaka.

Rešetke

Mreže su korisne kada imate mnogo podatkovnih točaka na grafikonu jer čitatelju omogućuje brzo razumijevanje položaja podatkovne točke.

Kad kreirate raspršeni grafikon u Excelu, linije mreže su prema zadanim postavkama omogućene.

Možete oblikovati ove linije mreže tako da desnom tipkom miša kliknete bilo koju od linija mreže i kliknete na opciju Oblikovanje linija mreže.

Ovo će otvoriti okno Format Gridlines na pravi način na koji možete promijeniti oblikovanje, poput boje, debljine, linije mreže.

Osim glavnih linija mreže koje su već vidljive pri izradi dijagrama raspršivanja, možete dodati i manje crte mreže.

Između dvije glavne linije mreže možete imati nekoliko manjih linija mreže koje dodatno poboljšavaju čitljivost grafikona u slučaju da imate puno podatkovnih točaka.

Da biste dodali manje vodoravne ili okomite crte mreže, odaberite grafikon, kliknite ikonu plusa i zadržite pokazivač iznad opcije Mreže.

Kliknite debelu crnu strelicu koja će se pojaviti, a zatim provjerite opciju "Primarni manji vodoravni" ili "Primarni manji okomiti" da biste dodali manje crte mreže

Legenda

Ako imate više serija iscrtanih na raspršenom grafikonu u Excelu, možete upotrijebiti legendu koja bi označavala koja se podatkovna točka odnosi na koji niz.

Prema zadanim postavkama nema legende kada stvorite raspršeni grafikon u Excelu.

Da biste dodali legendu na raspršeni grafikon, odaberite grafikon, kliknite ikonu plus, a zatim provjerite opciju legende.

Da biste oblikovali legendu, desnom tipkom miša kliknite legendu koja se pojavi, a zatim kliknite opciju "Formatiraj legendu".

U oknu Oblikovanje legende koje se otvara možete prilagoditi boju ispune, obrub i položaj legende na grafikonu.

Trendline

Također možete dodati liniju trenda u raspršeni grafikon koji bi pokazao postoji li pozitivna ili negativna korelacija u skupu podataka.

Već sam opisao kako dodati liniju trenda na raspršeni grafikon u Excelu u jednom od gornjih odjeljaka.

3D Scatter Plot u Excelu (najbolje je izbjegavati)

Za razliku od linijskog grafikona, stupnog grafikona ili grafikona područja, u Excelu nema ugrađenog 3D dijagrama raspršivanja.

Iako za to možete koristiti dodatke i alate trećih strana, ne mogu se sjetiti nikakve dodatne koristi koju ćete imati s 3D raspršenim grafikonom u usporedbi s običnim 2D raspršenim grafikonom.

Zapravo, preporučujem da se klonite bilo koje vrste 3D karte jer ima potencijal krivog predstavljanja podataka i dijelova na grafikonu.

Dakle, evo kako možete stvoriti raspršenu plohu u Excelu i prilagoditi je tako da odgovara vašoj robnoj marki i zahtjevima.

Nadam se da vam je ovaj vodič bio koristan.