Hvordan lage et spredningsdiagram i Excel (XY -diagram)

Excel har noen nyttige diagramtyper som kan brukes til å plotte data og vise analyse.

Et vanlig scenario er hvor du vil plotte X- og Y -verdier i et diagram i Excel og vise hvordan de to verdiene henger sammen.

Dette kan gjøres ved å bruke en Spred diagram i Excel.

Hvis du for eksempel har dataene Høyde (X -verdi) og Vekt (Y -verdi) for 20 studenter, kan du plotte dette i et spredningsdiagram, og det vil vise deg hvordan dataene er relatert.

Nedenfor er et eksempel på et spredningsdiagram i Excel (også kalt XY -diagrammet):

I denne opplæringen vil jeg vise deg hvordan du lager et spredningsdiagram i Excel, de forskjellige typene spredningsplott, og hvordan du tilpasser disse diagrammene.

Hva er et spredningsdiagram og når skal det brukes?

Spredningsdiagrammer brukes til å forstå sammenhengen (relatert) mellom to datavariabler.

Et spredningsdiagram har prikker der hver prikk representerer to verdier (X-akseverdi og Y-akseverdi) og basert på disse verdiene er disse punktene plassert i diagrammet.

Et eksempel på dette i virkeligheten kan være markedsføringskostnader og inntekter fra en gruppe selskaper i en bestemt bransje.

Når vi plotter disse dataene (Marketing Expense vs. Revenue) i et spredningsdiagram, kan vi analysere hvor sterkt eller løst disse to variablene er forbundet.

Opprette et spredningsdiagram i Excel

Anta at du har et datasett som vist nedenfor, og du vil opprette et spredningsdiagram med disse dataene.

Målet med dette diagrammet er å se om det er noen sammenheng mellom markedsføringsbudsjettet og inntektene eller ikke.

For å lage et spredningsdiagram er det viktig å ha begge verdiene (av de to variablene du vil plotte i spredningsdiagrammet) i to separate kolonner.

Kolonnen til venstre (Marketing Expense-kolonnen i vårt eksempel) vil bli plottet på X-aksen og inntektene vil bli plottet på Y-aksen.

Nedenfor er trinnene for å sette inn et spredningsdiagram i Excel:

1. Velg kolonnene som har dataene (unntatt kolonne A)

2. Klikk på alternativet Sett inn

3. I diagramgruppen klikker du på ikonet Sett inn punktdiagram

4. Klikk på alternativet "Spred diagram" i diagrammene som vises

De ovennevnte trinnene vil sette inn et spredningsdiagram som vist nedenfor i regnearket.

Kolonnen til venstre (Marketing Expense-kolonnen i vårt eksempel) vil bli plottet på X-aksen og inntektene vil bli plottet på Y-aksen. Det er best å ha den uavhengige beregningen i venstre kolonne og den du trenger for å finne sammenhengen i kolonnen til høyre.

Legge til en trendlinje i scatter -diagrammet

Selv om jeg vil dekke flere måter å tilpasse spredningsplottet i Excel senere i denne opplæringen, er en ting du kan gjøre umiddelbart etter at du har bygget spredningsplottet, å legge til en trendlinje.

Det hjelper deg raskt å få en følelse av om dataene er positivt eller negativt korrelert, og hvor tett/løst korrelert det er.

Nedenfor er trinnene for å legge til en trendlinje til et spredningsdiagram i Excel:

1. Velg Scatter -plottet (der du vil legge til trendlinjen)
2. Klikk kategorien Diagramdesign. Dette er en kontekstuell fane som bare vises når du velger diagrammet

3. Klikk på "Legg til diagramelement" -alternativet i diagramoppsettgruppen

4. Gå til alternativet "Trendline", og klikk deretter på "Lineær"

De ovennevnte trinnene vil legge til en lineær trendlinje i spredningsdiagrammet.

Bare ved å se på trendlinjen og datapunktene plottet i spredningsdiagrammet, kan du få en følelse av om dataene er positivt korrelert, negativt korrelert eller ikke korrelert.

I vårt eksempel ser vi en positiv stigning i trendlinjen som indikerer at dataene er positivt korrelert. Dette betyr at når markedsføringsutgiftene stiger, øker inntektene, og hvis markedsføringskostnadene går ned, går inntektene ned.

Hvis dataene er negativt korrelert, vil det være en omvendt relasjon. I så fall, hvis markedsføringsutgiftene stiger, vil inntektene gå ned og omvendt.

Og så er det et tilfelle der det ikke er noen sammenheng. I dette tilfellet, når markedsføringskostnadene øker, kan inntektene deres øke eller ikke.

Vær oppmerksom på at skråningen bare forteller oss om dataene er positivt eller negativt korrelert. det forteller oss ikke hvor nært det er relatert.

For eksempel, i vårt eksempel, ved å se på trendlinjen kan vi ikke si hvor mye inntektene vil stige når markedsføringskostnaden øker med 100%. Dette er noe som kan beregnes ved hjelp av korrelasjonskoeffisienten.

Du finner det ved å bruke formelen nedenfor:

= CORREL (B2: B11, C2: C11)

Korrelasjonskoeffisienten varierer mellom -1 og 1, hvor 1 indikerer en perfekt positiv korrelasjon og -1 indikerer en perfekt negativ korrelasjon

I vårt eksempel returnerer den 0,945, noe som indikerer at disse to variablene har en høy positiv korrelasjon.

Identifisere klynger ved hjelp av Scatter Chart (praktiske eksempler)

En av måtene jeg brukte til å bruke spredningsdiagrammer i arbeidet mitt som finansanalytiker, var å identifisere klynger med datapunkter som viser lignende oppførsel.

Dette fungerer vanligvis bra når du har et mangfoldig datasett med mindre generell korrelasjon.

Anta at du har et datasett som vist nedenfor, hvor jeg har 20 selskaper med inntekts- og fortjenestemarginalnummer.

Når jeg lager en scatterplot for disse dataene, får jeg noe som vist nedenfor:

I dette diagrammet kan du se at datapunktene er overalt og at det er en veldig lav korrelasjon.

Selv om dette diagrammet ikke forteller oss mye, kan du bruke det til å identifisere klynger i de fire kvadranter i diagrammet.

For eksempel er datapunktet i nedre venstre kvadrant de selskapene der inntektene er lave og netto fortjenestemarginen er lave, og selskapene i nedre høyre kvadrant er de der inntektene er høye, men nettoresultatmarginen er lav.

Dette pleide å være et av de svært diskuterte diagrammene i ledermøtet da vi pleide å identifisere potensielle kunder basert på deres økonomiske data.

Ulike typer spredningsplott i Excel

Bortsett fra det vanlige spredningsdiagrammet som jeg har dekket ovenfor, kan du også opprette følgende spredningsdiagramtyper i Excel:

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

Alle disse fire spredningsdiagrammene ovenfor er egnet når du har færre datapunkter og når du plotter to serier i diagrammet.

Anta for eksempel at du har markedsføringsutgifter vs inntektsdata som vist nedenfor, og at du vil plotte et spredningsdiagram med glatte linjer.

Nedenfor er trinnene for å gjøre dette:

1. Velg datasettet (unntatt kolonnen med firmanavn)
2. Klikk på Sett inn -fanen
3. I gruppen Diagrammer klikker du på alternativet Sett inn punktdiagram
4. Klikk på Alternativer for spredning med glatte linjer og markører

Du vil se noe som vist nedenfor.

Dette diagrammet kan raskt bli uleselig hvis du har flere datapunkter. Dette er grunnen til at det bare anbefales å bruke dette med færre datapunkter.

Jeg har aldri brukt dette diagrammet i arbeidet mitt, da jeg ikke tror det gir noen meningsfull innsikt (siden vi ikke kan plotte flere datapunkter til det).

Tilpasse spredningsdiagram i Excel

På samme måte som alle andre diagrammer i Excel, kan du enkelt tilpasse spredningsdiagrammet.

I denne delen vil jeg dekke noen av tilpasningene du kan gjøre med et spredningsdiagram i Excel:

Legge til / fjerne diagramelementer

Når du klikker på scatter -diagrammet, vil du se et plussikon øverst til høyre i diagrammet.

Når du klikker på dette plussikonet, viser det deg alternativer som du enkelt kan legge til eller fjerne fra scatter -diagrammet.

Her er alternativene du får:

• Økser
• Akse Tittel
• Diagramtittel
• Datatiketter
• Feilfelt
• Rutenett
• Legende
• Trendlinjer

Noen av disse alternativene er allerede tilstede i diagrammet, og du kan fjerne disse elementene ved å klikke i avmerkingsboksen ved siden av alternativet (eller legge til disse ved å klikke i avmerkingsboksen hvis den ikke er merket av allerede).

For eksempel, hvis jeg vil fjerne diagramtittelen, kan jeg ganske enkelt fjerne merket for alternativet, og det ville være borte,

Hvis du trenger mer kontroll, kan du klikke på den lille svarte pilen som vises når du holder markøren over noen av alternativene.

Hvis du klikker på det, får du flere alternativer for det spesifikke diagramelementet (disse åpnes som en rute på høyre side).

Merk: Alle skjermbildene jeg har vist deg er fra en nylig versjon av Excel (Microsoft 365). Hvis du bruker en eldre versjon, kan du få de samme alternativene når du høyreklikker på et av diagramelementene og klikker på alternativet Format.

La oss raskt gå gjennom disse elementene og noen av de fantastiske tilpasningene du kan gjøre for å spre diagrammer ved hjelp av det.

Økser

Akser er de vertikale og horisontale verdiene du ser rett ved siden av diagrammet.

En av de mest nyttige tilpasningene du kan gjøre med akser, er å justere maksimums- og minimumsverdien den kan vise.

For å endre dette, høyreklikk på aksene i diagrammet og klikk deretter på Format akser. Dette åpner ruten Formatakse.

I Axis -alternativet kan du angi minimums- og maksimumsgrenser, så vel som de store og mindre enhetene.

I de fleste tilfeller kan du sette dette til automatisk, og Excel vil ta seg av det basert på datasettet. Men hvis du vil ha spesifikke verdier, kan du endre dem herfra.

Ett eksempel kan være når du ikke vil at minimumsverdien i Y-aksen skal være 0, men noe annet (si 1000). Hvis du endrer den nedre grensen til 1000, justeres diagrammet slik at minimumsverdien i den vertikale aksen da vil være 1000.

Akse Tittel

Akstetittelen er noe du kan bruke til å spesifisere hva X- og Y-aksen representerer i spredningsdiagrammet i Excel.

I vårt eksempel vil det være nettoinntekten for X-aksen og markedsføringskostnad for Y-aksen.

Du kan velge å ikke vise noen aksetittel, og du kan fjerne disse ved å velge diagrammet, klikke på plussikonet og deretter fjerne merket for boksen for aksetittel.

For å endre teksten i aksetittelen, dobbeltklikker du bare på den og skriver inn hva du vil som aksetittel.

Du kan også koble aksetittelverdien til en celle.

For eksempel, hvis du vil at verdien i celle B1 skal vises i tittelen på den vertikale aksen, klikker du på aksetittelboksen og skriver inn = B1 i formellinjen. Dette viser verdien i celle B1 i aksetittelen.

Du kan gjøre det samme for tittelen på den horisontale aksen og koble den til en bestemt celle. Dette gjør disse titlene dynamiske, og hvis celleverdien endres, vil aksetitlene også endres.

Hvis du trenger mer kontroll over formatering av aksetitlene, klikker du på en hvilken som helst akse, høyreklikker og klikker deretter på Format Axis Title.

Med disse alternativene kan du endre fylling og kant av tittelen, endre tekstfarge, justering og rotasjon.

Diagramtittel

På samme måte som Axis -titler, kan du også formatere diagramtittelen i et spredningsdiagram i Excel.

En diagramtittel brukes vanligvis til å beskrive hva diagrammet handler om. For eksempel kan jeg bruke ‘Marketing Expense Vs Revenue’ som diagramtittelen.

Hvis du ikke vil ha diagramtittelen, kan du klikke og slette den. Og hvis du ikke har det, velg diagrammet, klikk på plussikonet og sjekk deretter alternativet Diagramtittel.

For å redigere teksten i diagramtittelen, dobbeltklikker du på boksen og skriver inn teksten manuelt der. Og hvis du vil gjøre diagramtittelen dynamisk, kan du klikke på tittelboksen og deretter skrive inn cellereferansen eller formelen i formellinjen.

For å formatere diagramtittelen, høyreklikk på diagramtittelen og klikk deretter på alternativet "Format diagramtittel". Dette viser ruten Format diagramtittel til høyre.

Med disse alternativene kan du endre fylling og kant av tittelen, endre tekstfarge, justering og rotasjon.

Datatiketter

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

Men du kan enkelt legge til og formatere disse.

Legg til datatikettene i spredningsdiagrammet, velg diagrammet, klikk på plussikonet til høyre, og kontroller deretter datatikettalternativet.

Dette vil legge til datatikettene som viser Y-aksens verdi for hvert datapunkt i spredningsgrafen.

For å formatere datatikettene, høyreklikker du på en av datatikettene og klikker deretter på alternativet "Formater datatiketter".

Dette åpner den tidligere datatikettruten til høyre, og du kan tilpasse disse ved hjelp av forskjellige alternativer som er oppført i ruten.

Bortsett fra den vanlige formateringen som fyll, kant, tekstfarge og justering, får du også noen ekstra etikettalternativer som du kan bruke.

I alternativene "Etikett inneholder" kan du velge å vise både X-aksen og Y-aksen, i stedet for bare Y-aksen.

Du kan også velge alternativet 'Verdi fra celler'. som lar deg ha datatiketter som er der i en kolonne i regnearket (den åpner en dialogboks når du velger dette alternativet, og du kan velge et område med celler hvis verdier skal vises i datatikettene. I vårt eksempel, Jeg kan bruke dette til å vise firmanavn i datatikettene

Du kan også tilpasse posisjonen til etiketten og formatet der den vises.

Feilfelt

Selv om jeg ikke har sett feilfelt brukes i spredningsdiagrammer, har Excel et alternativ som lar deg legge til disse feilfeltene for hvert datapunkt i spredningsdiagrammet i Excel.

For å legge til feillinjene, velg diagrammet, klikk på plussikonet, og kontroller deretter alternativet Feilfelt.

Og hvis du vil tilpasse disse feillinjene ytterligere, høyreklikker du på en av disse feillinjene og klikker deretter på alternativet "Format feillinjer".

Dette åpner "Format feillinjer" -ruten til høyre, der du kan tilpasse ting som farge, retning og stil på feillinjene.

Rutenett

Rutenettlinjer er nyttige når du har mange datapunkter på diagrammet, ettersom det lar leseren raskt forstå posisjonen til datapunktet.

Når du oppretter en scatterplot i Excel, er rutenett aktivert som standard.

Du kan formatere disse rutenettene ved å høyreklikke på hvilken som helst av rutenettene og klikke på alternativet Formater rutenett.

Dette åpner ruten Format Gridlines på den riktige måten du kan endre formateringen, for eksempel farge, tykkelse, på rutenettet.

Bortsett fra de store rutenettene som allerede er synlige når du lager spredningsdiagrammet, kan du også legge til mindre rutenettlinjer.

Mellom to store rutenettlinjer kan du ha noen få mindre rutenett som ytterligere forbedrer lesbarheten til diagrammet i tilfelle du har mange datapunkter.

Hvis du vil legge til mindre horisontale eller vertikale rutenett, velger du diagrammet, klikker på plussikonet og holder markøren over alternativet Rutenett.

Klikk på den tykke, svarte pilen der, og sjekk deretter alternativet ‘Primær mindre horisontal’ eller ’Primær mindre vertikal’ for å legge til de mindre rutenettene

Legende

Hvis du har flere serier plottet i spredningsdiagrammet i Excel, kan du bruke en forklaring som vil angi hvilket datapunkt som refererer til hvilken serie.

Som standard er det ingen forklaring når du oppretter et spredningsdiagram i Excel.

Hvis du vil legge til en forklaring i spredningsdiagrammet, velger du diagrammet, klikker på plussikonet og merker av forklaringsalternativet.

For å formatere legenden, høyreklikker du på forklaringen som vises, og klikker deretter på alternativet "Format legend".

I Format Legend -ruten som åpnes, kan du tilpasse fyllfargen, kantlinjen og plasseringen til forklaringen i diagrammet.

Trendline

Du kan også legge til en trendlinje i spredningsdiagrammet som viser om det er en positiv eller negativ korrelasjon i datasettet.

Jeg har allerede dekket hvordan du legger til en trendlinje til et spredningsdiagram i Excel i en av seksjonene ovenfor.

3D -spredningsplott i Excel (unngås best)

I motsetning til et linjediagram, kolonnediagram eller områdediagram er det ikke noe innebygd 3D -spredningsdiagram i Excel.

Selv om du kan bruke tredjeparts tillegg og verktøy for å gjøre dette, kan jeg ikke tenke meg noen ekstra fordel du vil få med et 3D-spredningsdiagram sammenlignet med et vanlig 2D-spredningsdiagram.

Faktisk anbefaler jeg å holde deg borte fra alle slags 3D -kart, ettersom det har potensial til å feilaktig representere dataene og delene i diagrammet.

Så dette er hvordan du kan lage et spredningsdiagram i Excel og tilpasse det slik at det passer til merkevaren din og kravene.

Jeg håper du synes denne opplæringen var nyttig.