Kā izveidot izkliedes diagrammu programmā Excel (XY diagramma)

Programmā Excel ir daži noderīgi diagrammu veidi, kurus var izmantot, lai attēlotu datus un parādītu analīzi.

Parasts scenārijs ir tāds, ka Excel diagrammā vēlaties attēlot X un Y vērtības un parādīt, kā abas vērtības ir saistītas.

To var izdarīt, izmantojot a Izkliedes diagramma programmā Excel.

Piemēram, ja jums ir dati par augumu (X vērtība) un svaru (Y vērtība) par 20 skolēniem, varat to attēlot izkliedes diagrammā, un tas parādīs, kā dati ir saistīti.

Zemāk ir izkliedes diagrammas piemērs programmā Excel (saukts arī par XY diagrammu):

Šajā apmācībā es parādīšu, kā programmā Excel izveidot izkliedes diagrammu, dažādus izkliedes diagrammu veidus un kā pielāgot šīs diagrammas.

Kas ir izkliedes diagramma un kad to izmantot?

Izkliedes diagrammas tiek izmantotas, lai izprastu korelāciju (saistību) starp diviem datu mainīgajiem.

Izkliedes diagrammā ir punkti, kur katrs punkts apzīmē divas vērtības (X ass vērtība un Y ass vērtība), un, pamatojoties uz šīm vērtībām, šie punkti tiek novietoti diagrammā.

Reāls piemērs tam varētu būt mārketinga izdevumi un konkrētas nozares uzņēmumu grupas ieņēmumi.

Plānojot šos datus (mārketinga izdevumi pret ieņēmumiem) izkliedes diagrammā, mēs varam analizēt, cik spēcīgi vai brīvi šie divi mainīgie ir saistīti.

Izkliedes diagrammas izveide programmā Excel

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties izveidot izkliedes diagrammu, izmantojot šos datus.

Šīs diagrammas mērķis ir noskaidrot, vai pastāv kāda korelācija starp mārketinga budžetu un ieņēmumiem.

Izkliedes diagrammas veidošanai ir svarīgi, lai abas vērtības (no diviem mainīgajiem, kurus vēlaties attēlot izkliedes diagrammā) būtu divās atsevišķās kolonnās.

Kreisās puses sleja (mūsu piemērā sleja Mārketinga izdevumi) tiks attēlota uz X ass, bet ieņēmumi-uz Y ass.

Tālāk ir norādītas darbības, lai programmā Excel ievietotu izkliedes diagrammu:

1. Atlasiet slejas, kurās ir dati (izņemot A sleju)

2. Noklikšķiniet uz opcijas Ievietot

3. Grupā Diagramma noklikšķiniet uz ikonas Ievietot izkliedes diagrammu

4. Parādītajās diagrammās noklikšķiniet uz opcijas “Izkliedes diagramma”

Iepriekš minētās darbības ievietotu izkliedes diagrammu, kā parādīts zemāk darblapā.

Kreisās puses sleja (mūsu piemērā sleja Mārketinga izdevumi) tiktu attēlota uz X ass, bet ieņēmumi-uz Y ass. Labāk, ja kreisajā slejā ir neatkarīgs rādītājs, bet labajā slejā ir jāatrod korelācija.

Tendences līnijas pievienošana izkliedes diagrammai

Lai gan šajā apmācībā vēlāk apskatīšu vairāk veidu, kā pielāgot izkliedes diagrammu programmā Excel, viena lieta, ko varat darīt tūlīt pēc izkliedes diagrammas izveidošanas, ir tendences līnijas pievienošana.

Tas palīdz ātri saprast, vai dati ir pozitīvi vai negatīvi korelēti un cik cieši/brīvi tie ir saistīti.

Tālāk ir norādītas darbības, lai pievienotu tendenču līniju izkliedes diagrammai programmā Excel:

1. Atlasiet izkliedes diagrammu (kur vēlaties pievienot tendenču līniju)
2. Noklikšķiniet uz cilnes Diagrammas dizains. Šī ir konteksta cilne, kas parādās tikai tad, kad izvēlaties diagrammu

3. Diagrammas izkārtojumu grupā noklikšķiniet uz opcijas “Pievienot diagrammas elementu”

4. Dodieties uz opciju "Trendline" un pēc tam noklikšķiniet uz "Lineārs"

Iepriekš minētās darbības jūsu izkliedes diagrammai pievienotu lineāru tendenču līniju.

Vienkārši aplūkojot tendenču līniju un izkliedes diagrammā attēlotos datu punktus, jūs varat saprast, vai dati ir pozitīvi korelēti, negatīvi vai nav korelēti.

Mūsu piemērā mēs redzam pozitīvu tendences slīpumu, kas norāda, ka dati ir pozitīvi korelēti. Tas nozīmē, ka, pieaugot mārketinga izdevumiem, palielinās ieņēmumi, un, ja samazinās mārketinga izdevumi, samazinās ieņēmumi.

Ja dati ir negatīvi korelēti, pastāv apgriezta sakarība. Tādā gadījumā, ja palielinās mārketinga izdevumi, samazināsies ieņēmumi un otrādi.

Un tad ir gadījums, kad nav korelācijas. Šajā gadījumā, palielinoties mārketinga izdevumiem, to ieņēmumi var palielināties vai nepalielināties.

Ņemiet vērā, ka slīpums mums tikai norāda, vai dati ir pozitīvi vai negatīvi korelēti. tas mums nepasaka, cik cieši tas ir saistīts.

Piemēram, mūsu piemērā, aplūkojot tendenču līniju, mēs nevaram pateikt, cik palielināsies ieņēmumi, kad mārketinga izdevumi palielināsies par 100%. To var aprēķināt, izmantojot korelācijas koeficientu.

To var atrast, izmantojot zemāk esošo formulu:

= CORREL (B2: B11, C2: C11)

Korelācijas koeficients svārstās no -1 līdz 1, kur 1 norādītu uz pilnīgi pozitīvu korelāciju un -1 norādītu uz pilnīgi negatīvu korelāciju

Mūsu piemērā tas atgriež 0,945, norādot, ka šiem diviem mainīgajiem ir augsta pozitīva korelācija.

Klasteru identificēšana, izmantojot izkliedes diagrammu (praktiski piemēri)

Viens no veidiem, kā es savā finanšu analītiķa darbā izmantoju izkliedes diagrammas, bija identificēt datu punktu kopas, kurām ir līdzīga uzvedība.

Tas parasti darbojas labi, ja jums ir daudzveidīga datu kopa ar mazāku vispārējo korelāciju.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, kur man ir 20 uzņēmumi ar ieņēmumiem un peļņas normu.

Veidojot šo datu izkliedes diagrammu, tiek parādīts šāds attēls:

Šajā diagrammā varat redzēt, ka datu punkti ir visur un korelācija ir ļoti zema.

Lai gan šī diagramma mums daudz nestāsta, viens veids, kā to izmantot, lai identificētu kopas diagrammas četros kvadrantos.

Piemēram, datu punkts apakšējā kreisajā kvadrantā ir tie uzņēmumi, kuru ieņēmumi ir zemi un neto peļņas norma ir zema, un uzņēmumi labajā apakšējā kvadrantā ir tie uzņēmumi, kuru ieņēmumi ir augsti, bet neto peļņas norma ir zema.

Šī bija viena no ļoti apspriestajām diagrammām vadības sanāksmē, kad mēs identificējām potenciālos klientus, pamatojoties uz viņu finanšu datiem.

Dažādi izkliedes diagrammu veidi programmā Excel

Papildus parastajai izkliedes diagrammai, kuru es apskatīju iepriekš, programmā Excel varat izveidot arī šādus izkliedes diagrammas veidus:

• Izkaisīt ar gludām līnijām
• Izkaisīt ar gludām līnijām un marķieriem
• Izkliedēt ar taisnām līnijām
• Izkliedēt ar taisnām līnijām un marķieriem

Visi šie četri iepriekš izkliedētie grafiki ir piemēroti, ja jums ir mazāk datu punktu un diagrammā attēlojat divas sērijas.

Piemēram, pieņemsim, ka jums ir mārketinga izdevumu un ieņēmumu dati, kā parādīts zemāk, un vēlaties uzzīmēt izkliedi ar gludām līniju diagrammām.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Atlasiet datu kopu (izņemot sleju uzņēmuma nosaukums)
2. Noklikšķiniet uz cilnes Ievietot
3. Grupā Diagrammas noklikšķiniet uz opcijas Ievietot izkliedes diagrammu
4. Noklikšķiniet uz opcijām Izkliedēt ar gludām līnijām un marķieriem

Jūs redzēsit kaut ko, kā parādīts zemāk.

Šī diagramma var ātri kļūt neizlasāma, ja jums ir vairāk datu punktu. Tāpēc ieteicams to izmantot tikai ar mazāk datu punktiem.

Es nekad neesmu izmantojis šo diagrammu savā darbā, jo, manuprāt, tas nesniedz jēgpilnu ieskatu (jo mēs nevaram tam uzzīmēt vairāk datu punktu).

Izkliedes diagrammas pielāgošana programmā Excel

Tāpat kā jebkuru citu diagrammu programmā Excel, jūs varat viegli pielāgot izkliedes diagrammu.

Šajā sadaļā es apskatīšu dažus pielāgojumus, ko varat veikt ar izkliedes diagrammu programmā Excel:

Diagrammas elementu pievienošana / noņemšana

Noklikšķinot uz izkliedes diagrammas, diagrammas augšējā labajā daļā redzēsit plus ikonu.

Noklikšķinot uz šīs pluszīmes ikonas, tiks parādītas opcijas, kuras varat viegli pievienot vai noņemt no izkliedes diagrammas.

Šeit ir šādas iespējas:

• Cirvji
• Ass nosaukums
• Diagrammas nosaukums
• Datu etiķetes
• Kļūdu joslas
• Režģlīnijas
• Leģenda
• Trendlines

Dažas no šīm opcijām jau ir iekļautas jūsu diagrammā, un jūs varat noņemt šos elementus, noklikšķinot uz izvēles rūtiņas blakus opcijai (vai pievienot tos, noklikšķinot uz izvēles rūtiņas, ja tā vēl nav atzīmēta).

Piemēram, ja es vēlos noņemt diagrammas nosaukumu, es varu vienkārši noņemt atzīmi no opcijas, un tā vairs nebūtu,

Ja jums nepieciešama lielāka kontrole, varat noklikšķināt uz mazās melnās bultiņas, kas parādās, virzot kursoru virs jebkuras opcijas.

Noklikšķinot uz tā, jūs iegūsit vairāk iespēju šim konkrētajam diagrammas elementam (tie tiek atvērti kā rūts labajā pusē).

Piezīme. Visi jūsu parādītie ekrānuzņēmumi ir no jaunākās Excel versijas (Microsoft 365). Ja izmantojat vecāku versiju, varat iegūt tādas pašas iespējas, ar peles labo pogu noklikšķinot uz jebkura diagrammas elementa un noklikšķinot uz opcijas Formatēt.

Ātri apskatīsim šos elementus un dažus lieliskus pielāgojumus, ko varat veikt, lai izkliedētu diagrammas, izmantojot to.

Cirvji

Asis ir vertikālās un horizontālās vērtības, kuras redzat tieši blakus diagrammai.

Viens no visnoderīgākajiem pielāgojumiem, ko varat veikt ar asīm, ir pielāgot maksimālo un minimālo vērtību, ko tā var parādīt.

Lai to mainītu, ar peles labo pogu noklikšķiniet uz diagrammas asīm un pēc tam noklikšķiniet uz Formatēt asis. Tiks atvērta rinda Formatēt asi.

Opcijā Axis varat iestatīt minimālās un maksimālās robežas, kā arī galvenās un mazākās vienības.

Vairumā gadījumu jūs varat to iestatīt kā automātisku, un Excel par to parūpēsies, pamatojoties uz datu kopu. Bet, ja vēlaties noteiktas vērtības, varat tās mainīt šeit.

Viens piemērs varētu būt gadījums, kad nevēlaties, lai minimālā vērtība Y asij būtu 0, bet kaut kas cits (teiksim 1000). Mainot apakšējo robežu uz 1000, diagramma tiks pielāgota tā, lai minimālā vērtība vertikālajā asī būtu 1000.

Ass nosaukums

Asu virsrakstu var izmantot, lai norādītu, ko X un Y ass attēlo izkliedes diagrammā programmā Excel.

Mūsu piemērā tie būtu neto ienākumi X asij un mārketinga izdevumi Y asij.

Varat izvēlēties nerādīt nevienu ass nosaukumu, un varat tos noņemt, atlasot diagrammu, noklikšķinot uz pluszīmes ikonas un pēc tam noņemot atzīmi no ass nosaukuma izvēles rūtiņas.

Lai mainītu ass nosaukuma tekstu, vienkārši veiciet dubultklikšķi uz tā un pēc tam ierakstiet visu, ko vēlaties kā ass nosaukumu.

Jūs varat arī saistīt ass nosaukuma vērtību ar šūnu.

Piemēram, ja vēlaties, lai šūnas B1 vērtība tiktu parādīta vertikālās ass nosaukumā, noklikšķiniet uz ass nosaukuma lodziņa un pēc tam formulas joslā ievadiet = B1. Tas parādīs vērtību B1 šūnā ass nosaukumā.

To pašu var izdarīt ar horizontālās ass nosaukumu un saistīt to ar noteiktu šūnu. Tas padara šos nosaukumus dinamiskus, un, mainoties šūnas vērtībai, mainās arī asu nosaukumi.

Ja jums ir nepieciešama lielāka kontrole asu nosaukumu formatēšanā, noklikšķiniet uz jebkuras ass, ar peles labo pogu noklikšķiniet un pēc tam noklikšķiniet uz Formatēt ass nosaukumu.

Izmantojot šīs opcijas, varat mainīt virsraksta aizpildījumu un apmali, mainīt teksta krāsu, līdzinājumu un pagriešanu.

Diagrammas nosaukums

Tāpat kā ass nosaukumus, varat arī formatēt diagrammas nosaukumu izkliedes diagrammā programmā Excel.

Diagrammas nosaukumu parasti izmanto, lai aprakstītu diagrammas būtību. Piemēram, kā diagrammas nosaukumu varu izmantot “Mārketinga izdevumu ieņēmumi”.

Ja nevēlaties diagrammas nosaukumu, varat to noklikšķināt un izdzēst. Un, ja jums tā nav, atlasiet diagrammu, noklikšķiniet uz pluszīmes ikonas un pēc tam atzīmējiet opciju Diagrammas nosaukums.

Lai rediģētu diagrammas nosaukuma tekstu, veiciet dubultklikšķi uz lodziņa un manuāli ierakstiet tur vajadzīgo tekstu. Un, ja vēlaties padarīt diagrammas nosaukumu dinamisku, varat noklikšķināt uz virsraksta lodziņa un pēc tam formulas joslā ierakstīt šūnas atsauci vai formulu.

Lai formatētu diagrammas nosaukumu, ar peles labo pogu noklikšķiniet uz diagrammas nosaukuma un pēc tam noklikšķiniet uz opcijas “Formatēt diagrammas nosaukumu”. Tiks parādīta rūts Formatēt diagrammas nosaukumu labajā pusē.

Izmantojot šīs opcijas, varat mainīt virsraksta aizpildījumu un apmali, mainīt teksta krāsu, līdzinājumu un pagriešanu.

Datu etiķetes

Pēc noklusējuma datu etiķetes nav redzamas, veidojot izkliedes diagrammu programmā Excel.

Bet jūs varat tos viegli pievienot un formatēt.

Pievienojiet datu etiķetes izkliedes diagrammai, atlasiet diagrammu, noklikšķiniet uz pluszīmes ikonas labajā pusē un pēc tam pārbaudiet datu etiķešu opciju.

Tādējādi tiks pievienotas datu etiķetes, kas parādīs Y ass vērtību katram datu punktam izkliedes diagrammā.

Lai formatētu datu etiķetes, ar peles labo pogu noklikšķiniet uz jebkuras datu etiķetes un pēc tam noklikšķiniet uz opcijas “Formatēt datu etiķetes”.

Tas atvērs bijušo datu etiķešu rūti labajā pusē, un jūs varat tos pielāgot, izmantojot dažādas rūts uzskaitītās opcijas.

Papildus parastajam formatējumam, piemēram, aizpildījumam, apmalēm, teksta krāsai un izlīdzināšanai, jūs saņemat arī dažas papildu etiķešu opcijas, kuras varat izmantot.

Opcijās “Etiķete satur” varat izvēlēties parādīt gan X, gan Y ass vērtību, nevis tikai Y asi.

Varat arī izvēlēties opciju “Vērtība no šūnām”. kas ļaus jums izveidot datu etiķetes, kas atrodas darblapas kolonnā (kad tiek atlasīta šī opcija, tiek atvērts dialoglodziņš un varat izvēlēties šūnu diapazonu, kura vērtības tiktu parādītas datu etiķetēs. Mūsu piemērā Es varu to izmantot, lai datu etiķetēs parādītu uzņēmumu nosaukumus

Varat arī pielāgot etiķetes atrašanās vietu un formātu, kādā tā tiek parādīta.

Kļūdu joslas

Lai gan es neesmu redzējis kļūdu joslas, kas tiek izmantotas izkliedes diagrammās, programmā Excel ir opcija, kas ļauj jums pievienot šīs kļūdu joslas katram datu punktam Excel izkliedes diagrammā.

Lai pievienotu kļūdu joslas, atlasiet diagrammu, noklikšķiniet uz pluszīmes ikonas un pēc tam atzīmējiet opciju Kļūdu joslas.

Un, ja vēlaties tālāk pielāgot šīs kļūdu joslas, ar peles labo pogu noklikšķiniet uz jebkuras no šīm kļūdu joslām un pēc tam noklikšķiniet uz opcijas “Formatēt kļūdu joslas”.

Tas atvērs rūti “Formatēt kļūdu joslas” labajā pusē, kur varēsit pielāgot kļūdas joslu krāsu, virzienu un stilu.

Režģlīnijas

Režģlīnijas ir noderīgas, ja diagrammā ir daudz datu punktu, jo tas ļauj lasītājam ātri saprast datu punkta stāvokli.

Veidojot izkliedes diagrammu programmā Excel, režģlīnijas ir iespējotas pēc noklusējuma.

Jūs varat formatēt šīs režģlīnijas, ar peles labo pogu noklikšķinot uz jebkuras režģlīnijas un noklikšķinot uz opcijas Formatēt režģlīnijas.

Tādējādi tiks atvērta rūts Formatēt režģlīnijas, lai jūs varētu mainīt formatējumu, piemēram, režģa līnijas krāsu, biezumu.

Izņemot galvenās režģlīnijas, kas jau ir redzamas, veidojot izkliedes diagrammu, varat pievienot arī nelielas režģlīnijas.

Starp divām galvenajām režģlīnijām var būt dažas nelielas režģlīnijas, kas vēl vairāk uzlabo diagrammas lasāmību, ja jums ir daudz datu punktu.

Lai pievienotu nelielas horizontālas vai vertikālas režģlīnijas, atlasiet diagrammu, noklikšķiniet uz pluszīmes ikonas un virziet kursoru virs opcijas Režģlīnijas.

Noklikšķiniet uz tur redzamās biezās melnās bultiņas un pēc tam atzīmējiet opciju “Primārais mazais horizontālais” vai “Primārā mazākā vertikālā”, lai pievienotu mazākās režģlīnijas

Leģenda

Ja Excel izkliedes diagrammā ir attēlotas vairākas sērijas, varat izmantot leģendu, kas norādītu, kurš datu punkts attiecas uz sēriju.

Pēc noklusējuma, veidojot izkliedes diagrammu programmā Excel, nav leģendas.

Lai izkliedes diagrammai pievienotu leģendu, atlasiet diagrammu, noklikšķiniet uz pluszīmes ikonas un pēc tam atzīmējiet leģendas opciju.

Lai formatētu leģendu, ar peles labo pogu noklikšķiniet uz parādītās leģendas un pēc tam noklikšķiniet uz opcijas “Format Legend”.

Atvērtajā rūtī Format Legend varat pielāgot leģendas aizpildījuma krāsu, apmali un pozīciju diagrammā.

Trendline

Izkliedes diagrammā varat pievienot arī tendenču līniju, kas parādītu, vai datu kopā ir pozitīva vai negatīva korelācija.

Vienā no iepriekš minētajām sadaļām es jau apskatīju, kā Excel tendenču līniju pievienot izkliedes diagrammai.

3D izkliedes diagramma programmā Excel (vislabāk izvairīties)

Atšķirībā no līniju diagrammas, kolonnu diagrammas vai apgabala diagrammas, programmā Excel nav iebūvētas 3D izkliedes diagrammas.

Lai gan jūs varat izmantot trešo pušu pievienojumprogrammas un rīkus, lai to izdarītu, es nevaru iedomāties nevienu papildu labumu, ko jūs iegūsit, izmantojot 3D izkliedes diagrammu, salīdzinot ar parasto 2D izkliedes diagrammu.

Patiesībā es iesaku izvairīties no jebkāda veida 3D diagrammas, jo tā var maldināt diagrammas datus un daļas.

Tātad šādā veidā jūs varat izveidot izkliedes diagrammu programmā Excel un pielāgot to, lai tas atbilstu jūsu zīmolam un prasībām.

Es ceru, ka šī apmācība jums šķita noderīga.