Sådan laver du et spredningsdiagram i Excel (XY -diagram)

Excel har nogle nyttige diagramtyper, der kan bruges til at plotte data og vise analyse.

Et almindeligt scenario er, hvor du vil plotte X- og Y -værdier i et diagram i Excel og vise, hvordan de to værdier hænger sammen.

Dette kan gøres ved at bruge en Spred diagram i Excel.

Hvis du f.eks. Har dataene Højde (X -værdi) og Vægt (Y -værdi) for 20 elever, kan du plotte dette i et spredningsdiagram, og det viser dig, hvordan dataene er relateret.

Nedenfor er et eksempel på et Scatter Plot i Excel (også kaldet XY -diagrammet):

I denne vejledning viser jeg dig, hvordan du laver et spredningsdiagram i Excel, de forskellige typer spredningsdiagrammer, og hvordan du tilpasser disse diagrammer.

Hvad er et spredningsdiagram og hvornår skal det bruges?

Spredningsdiagrammer bruges til at forstå sammenhængen (relation) mellem to datavariabler.

Et spredningsdiagram har prikker, hvor hver prik repræsenterer to værdier (X-akse værdi og Y-akse værdi) og baseret på disse værdier er disse prikker placeret i diagrammet.

Et virkeligt eksempel på dette kan være marketingudgifter og indtægter fra en gruppe virksomheder i en bestemt branche.

Når vi plotter disse data (marketingudgifter vs. indtægter) i et spredningsdiagram, kan vi analysere, hvor stærkt eller løst disse to variabler er forbundet.

Oprettelse af et Scatter Plot i Excel

Antag, at du har et datasæt som vist nedenfor, og du vil oprette et spredningsdiagram ved hjælp af disse data.

Målet med dette diagram er at se, om der er nogen sammenhæng mellem marketingbudgettet og indtægterne eller ej.

For at lave et spredningsdiagram er det vigtigt at have begge værdierne (for de to variabler, du vil tegne i spredningsdiagrammet) i to separate kolonner.

Kolonnen til venstre (Marketing Expense-kolonnen i vores eksempel) ville blive afbildet på X-aksen, og indtægten ville blive afbildet på Y-aksen.

Nedenfor er trinene til at indsætte et spredningsdiagram i Excel:

1. Vælg de kolonner, der har dataene (eksklusive kolonne A)

2. Klik på indstillingen Indsæt

3. I diagramgruppen skal du klikke på ikonet Indsæt punktdiagram

4. Klik på indstillingen 'Spred diagram' i de diagrammer, der vises

Ovenstående trin ville indsætte et spredningsdiagram som vist nedenfor i regnearket.

Kolonnen til venstre (Marketing Expense-kolonnen i vores eksempel) ville blive afbildet på X-aksen, og indtægten ville blive afbildet på Y-aksen. Det er bedst at have den uafhængige metrik i venstre kolonne og den, som du skal finde sammenhængen i i kolonnen til højre.

Tilføjelse af en trendlinje til scatterdiagrammet

Selvom jeg vil dække flere måder at tilpasse spredningsplottet i Excel senere i denne vejledning, er en ting, du kan gøre umiddelbart efter opbygningen af ​​spredningsplottet, at tilføje en trendlinje.

Det hjælper dig hurtigt med at få en fornemmelse af, om dataene er positivt eller negativt korreleret, og hvor tæt/løst korreleret det er.

Nedenfor er trinene til at tilføje en trendlinje til et spredningsdiagram i Excel:

1. Vælg Scatter -plottet (hvor du vil tilføje trendlinjen)
2. Klik på fanen Diagramdesign. Dette er en kontekstuel fane, der kun vises, når du vælger diagrammet

3. Klik på indstillingen 'Tilføj diagramelement' i gruppen Chartlayouts

4. Gå til indstillingen ‘Trendline’, og klik derefter på ‘Lineær’

Ovenstående trin tilføjer en lineær trendlinje til dit spredningsdiagram.

Bare ved at se på trendlinjen og datapunkterne, der er afbildet i spredningsdiagrammet, kan du få en fornemmelse af, om dataene er positivt korreleret, negativt korreleret eller ikke korreleret.

I vores eksempel ser vi en positiv hældning i trendlinjen, der indikerer, at dataene er positivt korreleret. Det betyder, at når marketingudgifterne stiger, stiger indtægterne, og hvis marketingudgifterne falder, falder indtægterne.

Hvis dataene er negativt korreleret, ville der være en omvendt relation. I så fald ville omsætningsudgifterne stige, så ville omsætningen falde og omvendt.

Og så er der et tilfælde, hvor der ikke er nogen sammenhæng. I dette tilfælde, når markedsføringsudgifterne stiger, stiger deres omsætning muligvis ikke.

Bemærk, at hældningen kun fortæller os, om dataene er positivt eller negativt korreleret. det fortæller os ikke, hvor tæt det hænger sammen.

For eksempel kan vi i vores eksempel ved at se på trendlinjen ikke sige, hvor meget omsætningen vil stige, når marketingudgiften stiger med 100%. Dette er noget, der kan beregnes ved hjælp af korrelationskoefficienten.

Du kan finde det ved hjælp af nedenstående formel:

= HØJRE (B2: B11, C2: C11)

Korrelationskoefficienten varierer mellem -1 og 1, hvor 1 angiver en perfekt positiv korrelation og -1 angiver en perfekt negativ korrelation

I vores eksempel returnerer det 0,945, hvilket indikerer, at disse to variabler har en høj positiv korrelation.

Identificering af klynger ved hjælp af Scatter Chart (praktiske eksempler)

En af de måder, jeg plejede at bruge scatter -diagrammer på i mit arbejde som finansanalytiker, var at identificere klynger af datapunkter, der udviser en lignende form for adfærd.

Dette fungerer normalt godt, når du har et mangfoldigt datasæt med mindre samlet korrelation.

Antag, at du har et datasæt som vist herunder, hvor jeg har 20 virksomheder med deres omsætning og fortjenstmargen.

Når jeg opretter en scatterplot for disse data, får jeg noget som vist herunder:

I dette diagram kan du se, at datapunkterne er overalt, og at der er en meget lav korrelation.

Selvom dette diagram ikke fortæller os meget, kan du på en måde bruge det til at identificere klynger i de fire kvadranter i diagrammet.

For eksempel er datapunktet i nederste venstre kvadrant de virksomheder, hvor omsætningen er lav, og nettoresultatmargenen er lav, og virksomhederne i den nederste højre kvadrant er dem, hvor omsætningen er høj, men nettooverskudsgraden er lav.

Dette plejede at være et af de meget diskuterede diagrammer i ledelsesmødet, da vi plejede at identificere potentielle kunder baseret på deres økonomiske data.

Forskellige typer spredningsplots i Excel

Bortset fra det almindelige spredningsdiagram, som jeg har dækket ovenfor, kan du også oprette følgende spredningsdiagramtyper i Excel:

• Spred med glatte linjer
• Spred med glatte linjer og markører
• Spred med lige linjer
• Spred med lige linjer og markører

Alle disse fire ovenstående spredningsdiagrammer er egnede, når du har færre datapunkter, og når du tegner to serier i diagrammet.

Antag f.eks., At du har marketingomkostninger vs indtægtsdata som vist nedenfor, og du vil plotte et punkt med diagram med glatte linjer.

Nedenfor er trinene for at gøre dette:

1. Vælg datasættet (undtagen kolonnen firmanavn)
2. Klik på fanen Indsæt
3. I gruppen Diagrammer skal du klikke på indstillingen Indsæt spredningsdiagram
4. Klik på Spred med glatte linjer og markører

Du vil se noget som vist herunder.

Dette diagram kan hurtigt blive ulæseligt, hvis du har flere datapunkter. Derfor anbefales det kun at bruge dette med færre datapunkter.

Jeg har aldrig brugt dette diagram i mit arbejde, da jeg ikke synes, det giver nogen meningsfuld indsigt (da vi ikke kan plotte flere datapunkter til det).

Tilpasning af scatterdiagram i Excel

Ligesom alle andre diagrammer i Excel kan du nemt tilpasse spredningsdiagrammet.

I dette afsnit vil jeg dække nogle af de tilpasninger, du kan gøre med et spredningsdiagram i Excel:

Tilføjelse / fjernelse af diagramelementer

Når du klikker på spredningsdiagrammet, vil du se plusikonet øverst til højre i diagrammet.

Når du klikker på dette plusikon, viser det dig muligheder, som du nemt kan tilføje eller fjerne fra dit spredningsdiagram.

Her er de muligheder, du får:

• Økser
• Akse titel
• Diagramtitel
• Datamærker
• Fejlstænger
• Gitterlinjer
• Legende
• Trendlines

Nogle af disse muligheder er allerede til stede i dit diagram, og du kan fjerne disse elementer ved at markere afkrydsningsfeltet ud for indstillingen (eller tilføje disse ved at klikke på afkrydsningsfeltet, hvis det ikke allerede er markeret).

For eksempel, hvis jeg vil fjerne diagramtitlen, kan jeg simpelthen fjerne markeringen af ​​indstillingen, og den ville være væk,

Hvis du har brug for mere kontrol, kan du klikke på den lille sorte pil, der vises, når du holder markøren over en af ​​mulighederne.

Hvis du klikker på det, får du flere muligheder for det specifikke diagramelement (disse åbnes som en rude i højre side).

Bemærk: Alle de skærmbilleder, jeg har vist, er fra en nylig version af Excel (Microsoft 365). Hvis du bruger en ældre version, kan du få de samme muligheder, når du højreklikker på et af diagramelementerne og klikker på indstillingen Format.

Lad os hurtigt gennemgå disse elementer og nogle af de fantastiske tilpasninger, du kan gøre for at sprede diagrammer ved hjælp af det.

Økser

Akser er de lodrette og vandrette værdier, som du ser lige ved siden af ​​diagrammet.

En af de mest nyttige tilpasninger, du kan gøre med akser, er at justere den maksimale og mindste værdi, den kan vise.

For at ændre dette skal du højreklikke på akserne i diagrammet og derefter klikke på Formater akser. Dette åbner ruden Formatakse.

I Axis -indstillingen kan du indstille minimums- og maksimumgrænser samt større og mindre enheder.

I de fleste tilfælde kan du indstille dette til automatisk, og Excel tager sig af det baseret på datasættet. Men hvis du vil have specifikke værdier, kan du ændre dem herfra.

Et eksempel kan være, når du ikke vil have minimumværdien i Y-aksen til at være 0, men noget andet (sig 1000). Hvis du ændrer den nedre grænse til 1000, justeres diagrammet, så minimumsværdien i den lodrette akse derefter ville være 1000.

Akse titel

Aksetitlen er noget, du kan bruge til at angive, hvad X- og Y-aksen repræsenterer i spredningsdiagrammet i Excel.

I vores eksempel ville det være nettoindkomsten for X-aksen og marketingudgifter for Y-aksen.

Du kan vælge ikke at vise nogen aksetitel, og du kan fjerne disse ved at vælge diagrammet, klikke på plusikonet og derefter fjerne markeringen i feltet for aksetitel.

For at ændre teksten i aksetitlen skal du blot dobbeltklikke på den og derefter skrive hvad du vil som aksetitel.

Du kan også linke aksens titelværdi til en celle.

For eksempel, hvis du vil have værdien i celle B1 til at blive vist i titlen på den lodrette akse, skal du klikke på boksen på aksetitlen og derefter indtaste = B1 i formellinjen. Dette viser værdien i celle B1 i aksetitlen.

Du kan gøre det samme for titlen på den vandrette akse og knytte den til en bestemt celle. Dette gør disse titler dynamiske, og hvis celleværdien ændres, ændres aksetitlerne også.

Hvis du har brug for mere kontrol med formatering af aksetitlerne, skal du klikke på en hvilken som helst akse, højreklikke og derefter klikke på Formatér aksetitel.

Med disse muligheder kan du ændre titelens udfyldning og kant, ændre tekstfarve, justering og rotation.

Diagramtitel

Ligesom Axis -titler kan du også formatere diagramtitlen i et spredningsdiagram i Excel.

En diagramtitel bruges normalt til at beskrive, hvad diagrammet handler om. For eksempel kan jeg bruge 'Marketing Expense Vs Revenue' som diagramtitel.

Hvis du ikke vil have diagramtitlen, kan du klikke og slette den. Og hvis du ikke har det, skal du vælge diagrammet, klikke på plusikonet og derefter kontrollere indstillingen Chart Title.

For at redigere teksten i diagramtitlen skal du dobbeltklikke på boksen og indtaste den tekst, du ønsker der, manuelt. Og hvis du vil gøre diagramtitlen dynamisk, kan du klikke på titelboksen og derefter skrive cellereferencen eller formlen i formellinjen.

For at formatere diagramtitlen skal du højreklikke på diagramtitlen og derefter klikke på indstillingen 'Format diagramtitel'. Dette viser ruden Formatdiagramtitel til højre.

Med disse muligheder kan du ændre titelens udfyldning og kant, ændre tekstfarve, justering og rotation.

Datamærker

Som standard er datatiketter ikke synlige, når du opretter et spredningsdiagram i Excel.

Men du kan nemt tilføje og formatere disse.

Tilføj datatiketterne til spredningsdiagrammet, vælg diagrammet, klik på plusikonet til højre, og kontroller derefter indstillingen dataetiketter.

Dette tilføjer datatiketterne, der viser Y-aksens værdi for hvert datapunkt i spredningsgrafen.

For at formatere datatiketterne skal du højreklikke på en af ​​datatiketterne og derefter klikke på 'Formatér dataetiketter'.

Dette åbner den tidligere datatiketterrude til højre, og du kan tilpasse disse ved hjælp af forskellige muligheder, der er angivet i ruden.

Bortset fra den almindelige formatering som udfyldning, kant, tekstfarve og justering, får du også nogle ekstra etiketmuligheder, som du kan bruge.

I indstillingerne 'Etiket indeholder' kan du vælge at vise både X-aksen og Y-aksen i stedet for kun Y-aksen.

Du kan også vælge indstillingen 'Værdi fra celler'. som giver dig mulighed for at have datatiketter, der er der i en kolonne i regnearket (det åbner en dialogboks, når du vælger denne indstilling, og du kan vælge et område af celler, hvis værdier vil blive vist i datatiketterne. I vores eksempel, Jeg kan bruge dette til at vise firmanavne i datatiketterne

Du kan også tilpasse placeringen af ​​etiketten og det format, hvor den vises.

Fejlstænger

Selvom jeg ikke har set fejlbjælker blive brugt i spredningsdiagrammer, har Excel en mulighed, der giver dig mulighed for at tilføje disse fejllinjer for hvert datapunkt i scatterplot i Excel.

Hvis du vil tilføje fejllinjerne, skal du markere diagrammet, klikke på plusikonet og derefter kontrollere indstillingen Fejlbjælker.

Og hvis du vil tilpasse disse fejllinjer yderligere, skal du højreklikke på en af ​​disse fejllinjer og derefter klikke på indstillingen 'Formatér fejlstænger'.

Dette åbner ruden 'Formatér fejlstænger' til højre, hvor du kan tilpasse ting som farve, retning og stil på fejlbjælkerne.

Gitterlinjer

Gitterlinjer er nyttige, når du har mange datapunkter på dit diagram, da det giver læseren mulighed for hurtigt at forstå datapunktets position.

Når du opretter en scatterplot i Excel, er gridlines som standard aktiveret.

Du kan formatere disse gridlines ved at højreklikke på en af ​​gridlines og klikke på Format Format gridlines.

Dette åbner ruden Formatér gitterlinjer på den rigtige måde, du kan ændre formateringen, f.eks. Farve, tykkelse, på gitterlinjen.

Bortset fra de store gitterlinjer, der allerede er synlige, når du opretter spredningsdiagrammet, kan du også tilføje mindre gitterlinjer.

Mellem to store netlinjer kan du have et par mindre netlinjer, der yderligere forbedrer diagrammets læsbarhed, hvis du har mange datapunkter.

Hvis du vil tilføje mindre vandrette eller lodrette gitterlinjer, skal du markere diagrammet, klikke på plusikonet og holde markøren over indstillingen Gitterlinjer.

Klik på den tykke sorte pil, der vises, og kontroller derefter indstillingen 'Primær mindre vandret' eller 'Primær mindre lodret' for at tilføje de mindre gitterlinjer

Legende

Hvis du har tegnet flere serier i spredningsdiagrammet i Excel, kan du bruge en forklaring, der angiver, hvilket datapunkt der refererer til hvilken serie.

Som standard er der ingen forklaring, når du opretter et spredningsdiagram i Excel.

Hvis du vil tilføje en forklaring til spredningsdiagrammet, skal du vælge diagrammet, klikke på plusikonet og derefter kontrollere indstillingen for forklaring.

For at formatere forklaringen skal du højreklikke på den forklaring, der vises, og derefter klikke på indstillingen 'Format legende'.

I ruden Formatér forklaring, der åbnes, kan du tilpasse fyldfarve, kant og placering af forklaringen i diagrammet.

Trendline

Du kan også tilføje en trendlinje i spredningsdiagrammet, der viser, om der er en positiv eller negativ korrelation i datasættet.

Jeg har allerede dækket, hvordan man tilføjer en trendlinje til et spredningsdiagram i Excel i et af afsnittene ovenfor.

3D Scatter Plot i Excel (undgås bedst)

I modsætning til et stregdiagram, søjlediagram eller områdediagram er der intet indbygget 3D -spredningsdiagram i Excel.

Selvom du kan bruge tredjeparts tilføjelsesprogrammer og værktøjer til at gøre dette, kan jeg ikke tænke på nogen yderligere fordel, som du får med et 3D-spredningsdiagram i forhold til et almindeligt 2D-spredningsdiagram.

Faktisk anbefaler jeg at holde sig væk fra enhver form for 3D -diagram, da det har potentiale til at forkert gengive data og dele i diagrammet.

Så sådan kan du oprette et spredningsdiagram i Excel og tilpasse det, så det passer til dit brand og krav.

Jeg håber, at du fandt denne vejledning nyttig.