Kako narediti razpršeno ploskev v Excelu (grafikon XY)

Excel ima nekaj uporabnih vrst grafikonov, ki jih lahko uporabite za risanje podatkov in prikaz analize.

Pogost scenarij je, ko želite narisati vrednosti X in Y v grafikonu v Excelu in pokazati, kako sta ti dve vrednosti povezani.

To lahko storite z uporabo a Razpršeni grafikon v Excelu.

Na primer, če imate podatke o višini (vrednost X) in teži (vrednost Y) za 20 študentov, lahko to narišete v razpršilni grafikon in prikazal vam bo, kako so podatki povezani.

Spodaj je primer zemljevida razpršitve v Excelu (imenovan tudi grafikon XY):

V tej vadnici vam bom pokazal, kako narediti razpršeno ploskev v Excelu, različne vrste razpršenih ploskev in kako prilagoditi te grafikone.

Kaj je razpršilna karta in kdaj jo uporabiti?

Scatter karte se uporabljajo za razumevanje korelacije (sorodnosti) med dvema podatkovnima spremenljivkama.

Razpršena ploskev ima pike, kjer vsaka pika predstavlja dve vrednosti (vrednost osi X in vrednost osi Y) in na podlagi teh vrednosti se te pike umestijo v grafikon.

Primer tega v resničnem življenju bi lahko bili stroški trženja in prihodki skupine podjetij v določeni panogi.

Ko te podatke (odhodki za trženje v primerjavi s prihodki) vnesemo v razpršen grafikon, lahko analiziramo, kako močno ali ohlapno sta ti dve spremenljivki povezani.

Ustvarjanje razpršilne ploskve v Excelu

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite s temi podatki ustvariti razpršeno ploskev.

Namen tega grafikona je ugotoviti, ali obstaja kakšna povezava med tržnim proračunom in prihodki ali ne.

Za izdelavo razpršenega grafikona je pomembno, da imata obe vrednosti (od dveh spremenljivk, ki jih želite narisati v razpršenem grafikonu) v dveh ločenih stolpcih.

Stolpec na levi strani (stolpec Stroški trženja v našem primeru) bi bil narisan na osi X, prihodek pa na osi Y.

Spodaj so navedeni koraki za vstavljanje razpršene ploskve v Excel:

1. Izberite stolpce, ki vsebujejo podatke (razen stolpca A)

2. Kliknite možnost Vstavi

3. V skupini Chart kliknite ikono Insert Scatter Chart

4. Kliknite možnost »Razpršeni grafikon« na prikazanih grafikonih

Zgornji koraki bi vstavili razpršeno ploskev, kot je prikazano spodaj na delovnem listu.

Stolpec na levi strani (stolpec Stroški trženja v našem primeru) bi bil narisan na osi X, prihodek pa na osi Y. Najbolje je, da imate neodvisno metriko v levem stolpcu in tisto, za katero morate najti korelacijo v stolpcu na desni.

Dodajanje linije trendov v razpršilno karto

Medtem ko bom kasneje v tej vadnici obravnaval več načinov prilagajanja razpršilne ploskve v Excelu, lahko eno stvar, ki jo lahko naredite takoj po izgradnji razpršene ploskve, dodate linijo trenda.

Pomaga vam, da hitro ugotovite, ali so podatki pozitivno ali negativno povezani in kako tesno/ohlapno so povezani.

Spodaj so navedeni koraki za dodajanje črte trenda v razpršeni grafikon v Excelu:

1. Izberite razpršilno ploskev (kamor želite dodati trendno črto)
2. Kliknite zavihek Načrt grafikona. To je kontekstualni zavihek, ki se prikaže samo, ko izberete grafikon

3. V skupini Postavitve grafikonov kliknite možnost »Dodaj element grafikona«

4. Pojdite na možnost 'Trendline' in kliknite na 'Linear'

Zgornji koraki bi v vaš razpršeni grafikon dodali linearno linijo trenda.

Že s pogledom na linijo trenda in podatkovne točke, narisane v razpršenem grafikonu, lahko dobite občutek, ali so podatki pozitivno povezani, negativno povezani ali ne.

V našem primeru vidimo pozitiven nagib v liniji trenda, ki kaže, da so podatki pozitivno povezani. To pomeni, da ko se stroški trženja povečajo, se prihodki povečajo, če pa se stroški trženja znižajo, se prihodki znižajo.

V primeru, da so podatki negativno povezani, obstaja obratna povezava. Če se bodo stroški trženja povečali, se bodo prihodki znižali in obratno.

In potem obstaja primer, ko ni povezave. V tem primeru se lahko ob povečanju stroškov trženja njihovi prihodki povečajo ali pa tudi ne.

Upoštevajte, da nam naklon pove le, ali so podatki pozitivno ali negativno povezani. nam ne pove, kako tesno je to povezano.

Na primer, v našem primeru s pogledom na trendno črto ne moremo reči, koliko se bodo prihodki povečali, ko se stroški trženja povečajo za 100%. To je nekaj, kar je mogoče izračunati z uporabo korelacijskega koeficienta.

To lahko ugotovite po spodnji formuli:

= CORREL (B2: B11, C2: C11)

Korelacijski koeficient se giblje med -1 in 1, kjer 1 pomeni popolnoma pozitivno korelacijo, -1 pa popolnoma negativno korelacijo

V našem primeru vrne 0,945, kar kaže, da imata ti dve spremenljivki visoko pozitivno korelacijo.

Prepoznavanje grozdov z razpršilno tabelo (praktični primeri)

Eden od načinov, kako sem pri svojem delu kot finančni analitik uporabljal razpršene grafikone, je bil prepoznavanje grozdov podatkovnih točk, ki kažejo podobno vedenje.

To običajno dobro deluje, če imate raznolik nabor podatkov z manjšo splošno korelacijo.

Recimo, da imate niz podatkov, kot je prikazano spodaj, kjer imam 20 podjetij s številkami prihodkov in dobička.

Ko za te podatke ustvarim razpršilec, dobim nekaj, kot je prikazano spodaj:

Na tem grafikonu lahko vidite, da so podatkovne točke povsod in da je korelacija zelo nizka.

Čeprav nam ta grafikon ne pove veliko, ga lahko na en način uporabite za prepoznavanje grozdov v štirih kvadrantih na grafikonu.

Na primer, podatkovna točka v spodnjem levem kvadrantu so tista podjetja, kjer so prihodki nizki, stopnja čistega dobička pa nizka, podjetja v spodnjem desnem kvadrantu pa tista, kjer so prihodki visoki, vendar je čista dobička nizka.

To je bil nekoč eden izmed zelo razpravljanih grafikonov na sestanku vodstva, ko smo na podlagi njihovih finančnih podatkov identificirali potencialne stranke.

Različne vrste razpršenih ploskev v Excelu

Poleg običajnega razpršenega grafikona, ki sem ga obravnaval zgoraj, lahko v Excelu ustvarite tudi naslednje vrste razpršenih ploskev:

• Razpršite z gladkimi črtami
• Razpršite z gladkimi črtami in označevalci
• Razpršite z ravnimi črtami
• Razpršite z ravnimi črtami in označevalci

Vse te štiri zgoraj prikazane razpršitve so primerne, če imate manj podatkovnih točk in ko v grafikon narišete dve seriji.

Recimo, da imate na primer podatke o stroških trženja v primerjavi s prihodki, kot je prikazano spodaj, in želite narisati razpršitev z grafikonom gladkih črt.

Spodaj so navedeni koraki za to:

1. Izberite nabor podatkov (razen stolpca z imenom podjetja)
2. Kliknite zavihek Vstavi
3. V skupini grafikonov kliknite možnost Vstavi razpršeni grafikon
4. Kliknite na Scatter with Smooth Lines and Markers

Videli boste nekaj, kot je prikazano spodaj.

Ta grafikon lahko hitro postane nečitljiv, če imate več podatkovnih točk. Zato je priporočljivo, da to uporabite samo z manj podatkovnimi točkami.

Tega grafikona nikoli nisem uporabljal pri svojem delu, saj mislim, da ne daje pomembnega vpogleda (saj nanj ne moremo narisati več podatkovnih točk).

Prilagajanje razpršenega grafikona v Excelu

Tako kot kateri koli drug grafikon v Excelu lahko tudi preprosto prilagodite razpršeno ploskev.

V tem razdelku bom obravnaval nekatere prilagoditve, ki jih lahko naredite z razpršenim grafikonom v Excelu:

Dodajanje / odstranjevanje elementov grafikona

Ko kliknete razpršeni grafikon, boste v zgornjem desnem delu grafikona videli ikono plus.

Ko kliknete to ikono plus, se prikažejo možnosti, ki jih lahko preprosto dodate ali odstranite iz razpršenega grafikona.

Tu so možnosti, ki jih dobite:

• Osi
• Naslov osi
• Naslov grafikona
• Podatkovne oznake
• Vrstice napak
• Mrežne črte
• Legenda
• Trendline

Nekatere od teh možnosti so že prisotne v vašem grafikonu in te elemente lahko odstranite s klikom na potrditveno polje poleg možnosti (ali pa jih dodate s klikom na potrditveno polje, če že ni označeno).

Če na primer želim odstraniti naslov grafikona, lahko preprosto počistim polje in ga ne bo več,

Če potrebujete več nadzora, lahko kliknete na majhno črno puščico, ki se prikaže, ko miškin kazalec premaknete na katero koli možnost.

S klikom nanj boste dobili več možnosti za ta poseben element grafikona (ti se odprejo kot podokno na desni strani).

Opomba: Vsi posnetki zaslona, ​​ki sem vam jih pokazal, so iz novejše različice Excela (Microsoft 365). Če uporabljate starejšo različico, lahko dobite enake možnosti, če z desno miškino tipko kliknete kateri koli element grafikona in kliknete možnost Oblika.

Poglejmo hitro te elemente in nekaj čudovitih prilagoditev, ki jih lahko uporabite za razprševanje grafikonov z njegovo uporabo.

Osi

Osi so navpične in vodoravne vrednosti, ki jih vidite tik ob grafikonu.

Ena najbolj uporabnih prilagoditev, ki jih lahko naredite z osmi, je prilagoditi največjo in najmanjšo vrednost, ki jo lahko prikaže.

Če želite to spremeniti, z desno miškino tipko kliknite osi v grafikonu in nato kliknite Oblikuj osi. S tem se odpre podokno Oblika osi.

V možnosti Axis lahko nastavite najmanjše in največje meje ter glavne in manjše enote.

V večini primerov lahko to nastavite na samodejno, Excel pa bo za to poskrbel na podlagi nabora podatkov. Če pa želite posebne vrednosti, jih lahko spremenite tukaj.

En primer je lahko, ko ne želite, da je minimalna vrednost na osi Y 0, ampak nekaj drugega (recimo 1000). Če spremenite spodnjo mejo na 1000, boste grafikon prilagodili tako, da bo najmanjša vrednost v navpični osi 1000.

Naslov osi

Naslov osi je nekaj, kar lahko uporabite za določitev, kaj osi X in Y predstavljata v razpršilni tabeli v Excelu.

V našem primeru bi bil to čisti dobiček za os X in stroški trženja za os Y.

Izberete lahko, da naslov osi ni prikazan, in jih odstranite tako, da izberete grafikon, kliknete ikono plus in počistite polje za naslov osi.

Če želite spremeniti besedilo v naslovu osi, ga dvokliknite in nato kot naslov osi vnesite, kar želite.

Vrednost naslova osi lahko povežete tudi s celico.

Če na primer želite, da se vrednost v celici B1 prikaže v naslovu navpične osi, kliknite polje z naslovom osi in nato vnesite = B1 v vrstico s formulo. To bo prikazalo vrednost v celici B1 v naslovu osi.

Enako lahko storite za naslov vodoravne osi in ga povežete z določeno celico. Zaradi tega so ti naslovi dinamični in če se vrednost celice spremeni, se bodo spremenili tudi naslovi osi.

Če potrebujete več nadzora pri oblikovanju naslovov osi, kliknite katero koli os, kliknite z desno miškino tipko in nato kliknite Oblikuj naslov osi.

S temi možnostmi lahko spremenite polnilo in obrobo naslova, spremenite barvo besedila, poravnavo in vrtenje.

Naslov grafikona

Tako kot naslove osi lahko tudi naslov grafikona oblikujete v razpršeni shemi v Excelu.

Naslov grafikona se običajno uporablja za opisovanje tega, kar prikazuje grafikon. Kot naslov grafikona lahko na primer uporabim „Prihodek od marketinških stroškov proti prihodkom“.

Če ne želite naslova grafikona, ga lahko kliknete in izbrišete. Če ga nimate, izberite grafikon, kliknite ikono plus in nato preverite možnost Naslov grafikona.

Če želite urediti besedilo v naslovu grafikona, dvokliknite polje in ročno vnesite želeno besedilo. Če želite, da je naslov grafikona dinamičen, lahko kliknete naslovno polje in nato vnesete sklic na celico ali formulo v vrstico s formulo.

Če želite oblikovati naslov grafikona, z desno miškino tipko kliknite naslov grafikona in nato kliknite možnost »Oblikuj naslov grafikona«. To bo prikazalo podokno Oblika naslova grafikona na desni.

S temi možnostmi lahko spremenite polnilo in obrobo naslova, spremenite barvo besedila, poravnavo in vrtenje.

Podatkovne oznake

Ko ustvarite razpršilno ploskev v Excelu, privzeto oznake podatkov niso vidne.

Lahko pa jih preprosto dodate in oblikujete.

Dodajte podatkovne oznake v razpršeni grafikon, izberite grafikon, kliknite ikono plus na desni in nato preverite možnost podatkovnih oznak.

To bo dodalo oznake podatkov, ki bodo prikazovale vrednost osi Y za vsako podatkovno točko v razpršenem grafu.

Če želite oblikovati podatkovne oznake, z desno miškino tipko kliknite katero koli od podatkovnih oznak in nato kliknite možnost »Oblikuj podatkovne oznake«.

S tem se odpre prejšnje podokno podatkovnih oznak na desni in jih lahko prilagodite z različnimi možnostmi, navedenimi v podoknu.

Poleg običajnega oblikovanja, na primer polnila, obrobe, barve besedila in poravnave, lahko uporabite tudi nekatere dodatne možnosti nalepk.

V možnostih »Oznaka vsebuje« lahko namesto samo osi Y prikažete vrednost osi X in osi Y.

Izberete lahko tudi možnost »Vrednost iz celic«. ki vam omogoča, da imate podatkovne oznake, ki so v stolpcu na delovnem listu (odpre pogovorno okno, ko izberete to možnost in lahko izberete obseg celic, katerih vrednosti bi bile prikazane v podatkovnih oznakah. V našem primeru, To lahko uporabim za prikaz imen podjetij na podatkovnih oznakah

Prilagodite lahko tudi položaj nalepke in obliko, v kateri je prikazana.

Vrstice napak

Čeprav nisem videl, da se vrstice napak uporabljajo v razpršenih grafikonih, ima Excel možnost, ki vam omogoča, da te vrstice napak dodate za vsako podatkovno točko v razpršilu v Excelu.

Če želite dodati vrstice napak, izberite grafikon, kliknite ikono plus in nato preverite možnost Vrstice napak.

Če želite te vrstice napak še dodatno prilagoditi, z desno miškino tipko kliknite katero koli od teh vrstic napak in nato kliknite možnost »Oblikuj vrstice napak«.

S tem se odpre podokno »Oblikuj vrstice napak« na desni, kjer lahko prilagodite stvari, kot so barva, smer in slog vrstic napak.

Mrežne črte

Mrežne črte so uporabne, če imate na grafikonu veliko podatkovnih točk, saj bralcu omogoča, da hitro razume položaj podatkovne točke.

Ko ustvarite razpršeno ploskev v Excelu, so mrežne črte privzeto omogočene.

Te mrežne črte lahko oblikujete tako, da z desno miškino tipko kliknete katero koli mrežno črto in kliknete možnost Oblikuj mrežne črte.

S tem se odpre podokno Oblikovanje mrežnih črt na pravi način, da spremenite oblikovanje, na primer barvo, debelino, mrežno črto.

Poleg glavnih mrežnih črt, ki so že vidne, ko ustvarite razpršeni diagram, lahko dodate tudi manjše mrežne črte.

Med dvema glavnima črtnima črtama imate lahko nekaj manjših mrežnih črt, ki dodatno izboljšajo berljivost grafikona, če imate veliko podatkovnih točk.

Če želite dodati manjše vodoravne ali navpične mrežne črte, izberite grafikon, kliknite ikono plus in miško premaknite nad možnostjo Mrežne črte.

Kliknite debelo črno puščico, ki se prikaže, nato pa preverite možnost »Primarna manjša vodoravna« ali »Primarna manjša navpična«, da dodate manjše mrežne črte

Legenda

Če imate v diagramu razpršitve v Excelu narisanih več serij, lahko uporabite legendo, ki označuje, katera podatkovna točka se nanaša na katero vrsto.

Ko ustvarite razpršen grafikon v Excelu, privzeto ni legende.

Če želite legendi dodati razpršeni grafikon, izberite grafikon, kliknite ikono plus in nato preverite možnost legende.

Če želite oblikovati legendo, z desno tipko miške kliknite legendo, ki se prikaže, in nato kliknite možnost »Oblikuj legendo«.

V podoknu Oblika legende, ki se odpre, lahko prilagodite barvo polnila, obrobo in položaj legende v grafikonu.

Trendline

V razpršen grafikon lahko dodate tudi linijo trenda, ki bi pokazala, ali je v nizu podatkov pozitivna ali negativna korelacija.

V enem od zgornjih razdelkov sem že obravnaval, kako dodati linijo trenda v razpršeni grafikon v Excelu.

3D Scatter Plot v Excelu (najbolje se jim izognite)

Za razliko od črtnega grafikona, stolpca ali območja, v Excelu ni vgrajenega 3D razpršenega grafikona.

Čeprav lahko za to uporabite dodatke in orodja drugih izdelovalcev, se ne morem spomniti nobene dodatne ugodnosti, ki jo boste imeli s 3D razpršilnim grafikonom v primerjavi z običajnim 2D razpršilnim grafikonom.

Pravzaprav priporočam, da se izogibate kakršni koli 3D -karti, saj lahko napačno prikaže podatke in dele v grafikonu.

Tako lahko v Excelu ustvarite razpršeno ploskev in jo prilagodite, da bo ustrezala vaši blagovni znamki in zahtevam.

Upam, da vam je bila ta vadnica koristna.