Hajautuskaavion tekeminen Excelissä (XY -kaavio)

Excelissä on hyödyllisiä kaaviotyyppejä, joita voidaan käyttää tietojen piirtämiseen ja analyysin näyttämiseen.

Yleinen skenaario on se, että haluat piirtää X- ja Y -arvot Excelin kaavioon ja näyttää, miten nämä kaksi arvoa liittyvät toisiinsa.

Tämä voidaan tehdä käyttämällä a Pistekaavio Excelissä.

Jos sinulla on esimerkiksi korkeus (X -arvo) ja paino (Y -arvo) tietoja 20 oppilasta, voit piirtää tämän pistekaavioon ja se näyttää, miten tiedot liittyvät toisiinsa.

Alla on esimerkki hajontakaaviosta Excelissä (jota kutsutaan myös XY -kaavioksi):

Tässä opetusohjelmassa näytän sinulle, miten voit tehdä sirontakaavion Excelissä, erityyppisiä sirontakaavioita ja miten voit muokata näitä kaavioita.

Mikä on pistekaavio ja milloin sitä käytetään?

Pistekaavioita käytetään kahden datamuuttujan välisen korrelaation (sukulaisuuden) ymmärtämiseen.

Hajontakaaviossa on pisteitä, joissa jokainen piste edustaa kahta arvoa (X-akselin arvo ja Y-akselin arvo), ja näiden pisteiden perusteella nämä pisteet sijoitetaan kaavioon.

Tosielämän esimerkki tästä voisi olla tietyn toimialan yritysryhmän markkinointikustannukset ja tulot.

Kun piirtämme nämä tiedot (markkinointikulut vs. tulot) pistekaavioon, voimme analysoida, kuinka voimakkaasti tai löysästi nämä kaksi muuttujaa liittyvät toisiinsa.

Hajotaulukon luominen Excelissä

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat luoda sirontakaavion näiden tietojen avulla.

Tämän kaavion tavoitteena on nähdä, onko markkinointibudjetin ja tulojen välillä korrelaatiota vai ei.

Hajontakaavion tekemiseksi on tärkeää, että molemmat arvot (kahdesta muuttujasta, jotka haluat piirtää pistekaavioon) on kahdessa erillisessä sarakkeessa.

Vasemmanpuoleinen sarake (esimerkissämme markkinointikulut-sarake) piirretään X-akselille ja tulot Y-akselille.

Alla on vaiheet, joilla hajakaavio lisätään Exceliin:

1. Valitse sarakkeet, joissa on tietoja (paitsi sarake A)

2. Napsauta Lisää -vaihtoehtoa

3. Napsauta Kaavio -ryhmässä Lisää pistekaavio -kuvaketta

4. Napsauta näkyvissä olevien kaavioiden pistekaaviota

Yllä olevat vaiheet lisäisivät sirontakaavion alla olevan taulukon mukaisesti.

Vasemmanpuoleinen sarake (esimerkissämme markkinointikulut-sarake) piirretään X-akselille ja tulot Y-akselille. On parasta pitää riippumaton mittari vasemmassa sarakkeessa ja se, jonka korrelaatio sinun on löydettävä oikeanpuoleisesta sarakkeesta.

Trendiviivan lisääminen pistekaavioon

Vaikka käsittelen lisää tapoja muokata sirontakaaviota Excelissä myöhemmin tässä opetusohjelmassa, yksi asia, jonka voit tehdä heti sirontakaavion rakentamisen jälkeen, on lisätä trendiviiva.

Sen avulla saat nopeasti käsityksen siitä, korreloivatko tiedot positiivisesti vai negatiivisesti ja kuinka tiukasti/löyhästi ne korreloivat toisiinsa.

Seuraavassa on ohjeet trendiviivan lisäämiseen pistekaavioon Excelissä:

1. Valitse hajontakaavio (johon haluat lisätä trendiviivan)
2. Napsauta Kaavion suunnittelu -välilehteä. Tämä on asiayhteydellinen välilehti, joka näkyy vain, kun valitset kaavion

3. Napsauta Kaavioasettelut -ryhmässä Lisää kaavion elementti -vaihtoehtoa

4. Siirry Trendline -vaihtoehtoon ja napsauta sitten Lineaarinen

Yllä olevat vaiheet lisäisivät sirontakaavioon lineaarisen trendiviivan.

Pelkästään katsomalla trendiviivaa ja pistekaavioon piirrettyjä datapisteitä saat käsityksen siitä, onko tiedot korreloineet positiivisesti, korreloineet negatiivisesti vai eivät.

Esimerkissämme näemme trendilinjalla positiivisen kaltevuuden, joka osoittaa, että tiedot korreloivat positiivisesti. Tämä tarkoittaa sitä, että kun markkinointikustannukset nousevat, tulot kasvavat ja jos markkinointikustannukset laskevat, tulot pienenevät.

Jos tiedot korreloivat negatiivisesti, olisi käänteinen suhde. Siinä tapauksessa, jos markkinointikustannukset nousevat, tulot pienenevät ja päinvastoin.

Ja sitten on tapaus, jossa ei ole korrelaatiota. Tässä tapauksessa, kun markkinointikustannukset kasvavat, niiden tulot voivat kasvaa tai eivät.

Huomaa, että kaltevuus kertoo vain, korreloivatko tiedot positiivisesti vai negatiivisesti. se ei kerro meille kuinka läheisesti se liittyy.

Esimerkiksi esimerkissämme, kun katsomme trendiviivaa, emme voi sanoa, kuinka paljon tulot kasvavat, kun markkinointikustannukset kasvavat 100%. Tämä voidaan laskea käyttämällä korrelaatiokerrointa.

Löydät sen käyttämällä alla olevaa kaavaa:

= OIKEA (B2: B11, C2: C11)

Korrelaatiokerroin vaihtelee -1: n ja 1: n välillä, missä 1 ilmaisee täydellisen positiivisen korrelaation ja -1 osoittaa täysin negatiivisen korrelaation

Esimerkissämme se palauttaa 0,945, mikä osoittaa, että näillä kahdella muuttujalla on korkea positiivinen korrelaatio.

Klusterien tunnistaminen pistekaavion avulla (käytännön esimerkkejä)

Yksi tapa, jolla käytin pistekaavioita työssäni talousanalyytikkona, oli tunnistaa datapisteiden klustereita, joilla on samanlainen käyttäytyminen.

Tämä toimii yleensä hyvin, kun sinulla on monipuolinen tietojoukko, jolla on vähemmän yleistä korrelaatiota.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko, jossa minulla on 20 yritystä, joiden tulot ja voittomarginaalit ovat.

Kun luon hajontakaavion näille tiedoille, saan jotain alla olevan kuvan mukaisesti:

Tästä kaaviosta näet, että datapisteet ovat kaikkialla ja korrelaatio on erittäin alhainen.

Vaikka tämä kaavio ei kerro paljon, yksi tapa, jolla voit tunnistaa sen kaavion neljän kvadrantin klustereiksi.

Esimerkiksi vasemman alakulman neljänneksen datapiste on yritykset, joiden liikevaihto on alhainen ja nettovoittomarginaali alhainen, ja oikeassa alakulmassa olevat yritykset, joissa tulot ovat korkeat, mutta nettotavoite on alhainen.

Tämä oli aiemmin yksi erittäin keskusteltuista kaavioista johtoryhmässä, kun tunnistimme mahdolliset asiakkaat heidän taloudellisten tietojensa perusteella.

Erilaisia ​​sirontakaavioita Excelissä

Edellä käsitellyn tavanomaisen pisteytyskaavion lisäksi voit luoda Excelissä myös seuraavat hajautaulukotyypit:

• Hajottaa sileillä viivoilla
• Hajottaa tasaisilla viivoilla ja tussilla
• Hajottaa suorilla viivoilla
• Hajottaa suorilla viivoilla ja tussilla

Kaikki nämä neljä yllä olevaa sirontakaaviota ovat sopivia, kun sinulla on vähemmän datapisteitä ja kun piirtät kaavioon kahta sarjaa.

Oletetaan esimerkiksi, että sinulla on markkinointikustannukset vs. tuototiedot alla esitetyllä tavalla ja haluat piirtää hajonnan tasaisilla viivoilla.

Alla on vaiheet tämän tekemiseen:

1. Valitse tietojoukko (lukuun ottamatta yrityksen nimen saraketta)
2. Napsauta Lisää -välilehteä
3. Napsauta Kaaviot -ryhmässä Lisää pistekaavio -vaihtoehtoa
4. Napsauta Scatter with Smooth Lines and Markers -vaihtoehtoja

Näet jotain alla olevan kuvan mukaisesti.

Tämä kaavio voi muuttua nopeasti lukukelvottomaksi, jos sinulla on enemmän datapisteitä. Siksi on suositeltavaa käyttää tätä vain vähemmän datapisteitä.

En ole koskaan käyttänyt tätä kaaviota työssäni, koska en usko, että se antaa mitään merkityksellistä tietoa (koska emme voi piirtää siihen enemmän datapisteitä).

Pistekaavion mukauttaminen Excelissä

Aivan kuten mikä tahansa muu Excelin kaavio, voit helposti muokata sirontakaaviota.

Tässä osassa käsitän joitain mukautuksia, joita voit tehdä Excel -pistekaavion avulla:

Kaavioelementtien lisääminen / poistaminen

Kun napsautat pistekaaviota, näet pluskuvakkeen kaavion oikeassa yläkulmassa.

Kun napsautat tätä pluskuvaketta, se näyttää vaihtoehtoja, jotka voit helposti lisätä tai poistaa pistekaaviosta.

Tässä ovat vaihtoehdot, jotka saat:

• Kirveet
• Akselin otsikko
• Kaavion otsikko
• Data Labels
• Virhepalkit
• Ruudukkoviivat
• Legenda
• Trendlines

Jotkin näistä vaihtoehdoista ovat jo kaaviossa, ja voit poistaa nämä elementit napsauttamalla vaihtoehdon vieressä olevaa valintaruutua (tai lisätä ne napsauttamalla valintaruutua, jos sitä ei ole vielä valittu).

Jos esimerkiksi haluan poistaa kaavion otsikon, voin yksinkertaisesti poistaa vaihtoehdon valinnan ja se olisi poissa,

Jos tarvitset enemmän hallintaa, voit napsauttaa pientä mustaa nuolta, joka tulee näkyviin, kun viet kursorin jonkin vaihtoehdon päälle.

Napsauttamalla sitä saat lisää vaihtoehtoja kyseiselle kaavioelementille (nämä avautuvat ruuduksi oikealla puolella).

Huomautus: Kaikki näyttämäsi kuvakaappaukset ovat Excelin viimeisimmästä versiosta (Microsoft 365). Jos käytät vanhempaa versiota, saat samat vaihtoehdot napsauttamalla hiiren kakkospainikkeella mitä tahansa kaavion elementtiä ja napsauttamalla Muoto-vaihtoehtoa.

Käydään nopeasti läpi nämä elementit ja joitain mahtavia mukautuksia, joita voit tehdä hajottaaksesi kaavioita sen avulla.

Kirveet

Akselit ovat pysty- ja vaaka -arvot, jotka näet aivan kaavion vieressä.

Yksi hyödyllisimmistä mukautuksista, joita voit tehdä akseleilla, on sen näyttämän enimmäis- ja vähimmäisarvon säätäminen.

Jos haluat muuttaa tätä, napsauta kaavion akseleita hiiren kakkospainikkeella ja valitse sitten Muotoile akselit. Tämä avaa Muotoile akseli -ruudun.

Akseli -vaihtoehdossa voit asettaa minimi- ja enimmäisrajat sekä suuret ja pienet yksiköt.

Useimmissa tapauksissa voit asettaa tämän automaattiseksi, ja Excel huolehtii siitä tietojoukon perusteella. Mutta jos haluat tiettyjä arvoja, voit muuttaa niitä täältä.

Yksi esimerkki voisi olla, kun et halua Y-akselin vähimmäisarvon olevan 0, mutta jotain muuta (esimerkiksi 1000). Alarajan muuttaminen arvoon 1000 säätää kaavion siten, että pystysuoran akselin vähimmäisarvo on 1000.

Akselin otsikko

Akselin otsikon avulla voit määrittää, mitä X- ja Y-akseli edustavat Excel-pistekaaviossa.

Esimerkissämme se olisi X-akselin nettotulo ja Y-akselin markkinointikustannus.

Voit valita, ettet näytä mitään akselin otsikkoa, ja voit poistaa ne valitsemalla kaavion, napsauttamalla plus -kuvaketta ja poistamalla sitten valinnan Akselin otsikko -ruudusta.

Jos haluat muuttaa akselin otsikon tekstiä, kaksoisnapsauta sitä ja kirjoita sitten mitä haluat akselin otsikoksi.

Voit myös linkittää akselin otsikon arvon soluun.

Jos esimerkiksi haluat, että solun B1 arvo näkyy pystysuoran akselin otsikossa, napsauta akselin otsikkoruutua ja kirjoita sitten kaavapalkkiin = B1. Tämä näyttää arvon solussa B1 akselin otsikossa.

Voit tehdä saman vaaka -akselin otsikolle ja linkittää sen tiettyyn soluun. Tämä tekee näistä otsikoista dynaamisia ja jos solun arvo muuttuu, myös akselien otsikot muuttuvat.

Jos haluat hallita enemmän akselien otsikoiden muotoilua, napsauta mitä tahansa akselia, napsauta hiiren kakkospainikkeella ja valitse sitten Muotoile akselin otsikko.

Näillä asetuksilla voit muuttaa otsikon täytettä ja reunusta, muuttaa tekstin väriä, kohdistusta ja kiertoa.

Kaavion otsikko

Akselin otsikoiden tapaan voit myös muotoilla kaavion otsikon Excel -hajontakaaviossa.

Kaavion otsikkoa käytetään yleensä kuvaamaan, mistä kaaviossa on kyse. Voin esimerkiksi käyttää kaavion otsikkona ”Markkinointikulut vs. tulot”.

Jos et halua kaavion otsikkoa, voit napsauttaa ja poistaa sen. Ja jos sinulla ei ole sitä, valitse kaavio, napsauta plus -kuvaketta ja valitse sitten kaavion otsikko.

Jos haluat muokata kaavion otsikon tekstiä, kaksoisnapsauta ruutua ja kirjoita haluamasi teksti sinne. Ja jos haluat tehdä kaavion otsikosta dynaamisen, voit napsauttaa otsikkoruutua ja kirjoittaa sitten soluviittauksen tai kaavan kaavapalkkiin.

Jos haluat muotoilla kaavion otsikon, napsauta kaavion otsikkoa hiiren kakkospainikkeella ja valitse sitten Muotoile kaavion otsikko. Tämä näyttää kaavion otsikon muotoilu -ruudun oikealla puolella.

Näillä asetuksilla voit muuttaa otsikon täytettä ja reunusta, muuttaa tekstin väriä, kohdistusta ja kiertoa.

Data Labels

Oletuksena datatarrat eivät ole näkyvissä, kun luot hajakaavion Excelissä.

Mutta voit helposti lisätä ja muotoilla nämä.

Lisää datatarrat pistekaavioon, valitse kaavio, napsauta oikealla olevaa plus -kuvaketta ja valitse sitten datatarrat -vaihtoehto.

Tämä lisää datatarrat, jotka näyttävät Y-akselin arvon kullekin pistekaavion datapisteelle.

Jos haluat muotoilla datatarrat, napsauta mitä tahansa datatarraa hiiren kakkospainikkeella ja napsauta sitten "Muotoile datatarrat" -vaihtoehtoa.

Tämä avaa entisen datatarrat -ruudun oikealla puolella ja voit muokata niitä käyttämällä ruudussa lueteltuja eri vaihtoehtoja.

Säännöllisen muotoilun, kuten täytön, reunuksen, tekstin värin ja kohdistuksen, lisäksi voit käyttää myös muita tarra -asetuksia, joita voit käyttää.

"Tarra sisältää" -vaihtoehdoissa voit näyttää sekä X-akselin että Y-akselin arvon vain Y-akselin sijasta.

Voit myös valita vaihtoehdon "Arvo soluista". jonka avulla voit käyttää datatunnisteita, jotka ovat laskentataulukon sarakkeessa (se avaa valintaikkunan, kun valitset tämän vaihtoehdon, ja voit valita solualueen, jonka arvot näytetään datatarroissa. Esimerkissämme Voin käyttää tätä yritysten nimien näyttämiseen tietokilpeissä

Voit myös muokata tarran sijaintia ja muotoa, jossa se näkyy.

Virhepalkit

Vaikka en ole nähnyt virhepalkkien käyttöä pistekaavioissa, Excelissä on vaihtoehto, jonka avulla voit lisätä nämä virhepalkit kullekin Excelin hajontakaavion datapisteelle.

Jos haluat lisätä virhepalkkeja, valitse kaavio, napsauta plus -kuvaketta ja valitse sitten Virhepalkit -vaihtoehto.

Ja jos haluat muokata näitä virhepalkkeja edelleen, napsauta hiiren kakkospainikkeella mitä tahansa näistä virhepalkeista ja napsauta sitten Muotoile virhepalkit -vaihtoehtoa.

Tämä avaa oikealla olevan "Muotoile virhepalkit" -ruudun, jossa voit mukauttaa virhepalkkien väriä, suuntaa ja tyyliä.

Ruudukkoviivat

Ruudukko on hyödyllinen, kun kaaviossa on paljon datapisteitä, koska sen avulla lukija voi nopeasti ymmärtää datapisteen sijainnin.

Kun luot hajontakaavion Excelissä, ruudukko on oletusarvoisesti käytössä.

Voit muotoilla näitä ruudukkoja napsauttamalla hiiren kakkospainikkeella mitä tahansa ruudukkoa ja napsauttamalla Muotoile ruudukko -vaihtoehtoa.

Tämä avaa Muotoile ruudukko -ruudun oikealla tavalla, jolla voit muuttaa muotoilua, kuten ruudukon väriä, paksuutta.

Hajakaavion luomisen yhteydessä näkyvien pääruudukkojen lisäksi voit lisätä myös pieniä ruudukkoja.

Kahden suuren ruudukon välissä voi olla muutamia pieniä ruudukkoja, jotka parantavat edelleen kaavion luettavuutta, jos sinulla on paljon datapisteitä.

Jos haluat lisätä pieniä vaaka- tai pystysuuntaisia ​​ruudukkoja, valitse kaavio, napsauta plus -kuvaketta ja vie hiiri ruudukkoasetusten päälle.

Napsauta näkyviin tulevaa paksua mustaa nuolta ja lisää sitten pienet ruudukot valitsemalla "Ensisijainen vaakasuuntainen" tai "Ensisijainen pieni pystysuora" -vaihtoehto.

Legenda

Jos Excelin pistekaavioon on piirretty useita sarjoja, voit käyttää selitystä, joka ilmaisee, mikä datapiste viittaa mihin sarjaan.

Oletuksena ei ole selitystä, kun luot pistekaavion Excelissä.

Voit lisätä selityksen pistekaavioon valitsemalla kaavion, napsauttamalla plus -kuvaketta ja valitsemalla sitten selitevaihtoehdon.

Muotoile selite napsauttamalla hiiren kakkospainikkeella näkyvää selitettä ja napsauttamalla sitten Muotoile selite -vaihtoehtoa.

Avautuvassa Muotoile selite -ruudussa voit mukauttaa selitteen täytön väriä, reunusta ja sijaintia kaaviossa.

Trendline

Voit myös lisätä pistekaavioon trendilinjan, joka osoittaa, onko tietojoukossa positiivinen vai negatiivinen korrelaatio.

Olen jo käsitellyt trendiviivan lisäämistä pistekaavioon Excelissä yhdessä yllä olevista osista.

3D -hajautuskäyrä Excelissä (parasta välttää)

Toisin kuin viivakaavio, pylväskaavio tai aluekaavio, Excelissä ei ole sisäänrakennettua 3D -pistekaaviota.

Vaikka voit käyttää tähän kolmansien osapuolten apuohjelmia ja työkaluja, en voi ajatella mitään lisäetuja, jotka saat 3D-pistekaavion avulla verrattuna tavalliseen 2D-pistekaavioon.

Itse asiassa suosittelen pysymään poissa kaikentyyppisestä 3D -kaaviosta, koska se voi vääristää kaavion tietoja ja osia.

Joten tällä tavalla voit luoda sirontakaavion Excelissä ja muokata sitä vastaamaan brändiäsi ja vaatimuksiasi.

Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä.