Excel har några användbara diagramtyper som kan användas för att plotta data och visa analys.
Ett vanligt scenario är där du vill rita X- och Y -värden i ett diagram i Excel och visa hur de två värdena är relaterade.
Detta kan göras med hjälp av en Sprid diagram i Excel.
Om du till exempel har data för höjd (X -värde) och vikt (Y -värde) för 20 elever kan du plotta detta i ett spridningsdiagram och det visar hur data är relaterade.
Nedan är ett exempel på en Scatter Plot i Excel (även kallat XY -diagrammet):
I den här självstudien visar jag dig hur du gör en spridningsdiagram i Excel, de olika typerna av spridningsdiagram och hur du anpassar dessa diagram.
Vad är ett spridningsdiagram och när ska det användas?
Spridningsscheman används för att förstå korrelationen (släktskap) mellan två datavariabler.
En spridningsdiagram har prickar där varje punkt representerar två värden (X-axelvärde och Y-axelvärde) och baserat på dessa värden är dessa prickar placerade i diagrammet.
Ett verkligt exempel på detta kan vara marknadsföringskostnader och intäkter från en grupp företag i en specifik bransch.
När vi plottar dessa data (marknadsföringskostnad kontra intäkter) i ett spridningsdiagram kan vi analysera hur starkt eller löst dessa två variabler är kopplade.
Skapa en Scatter Plot i Excel
Anta att du har en datamängd som visas nedan och du vill skapa en spridningsdiagram med hjälp av dessa data.
Syftet med detta diagram är att se om det finns något samband mellan marknadsföringsbudgeten och intäkterna eller inte.
För att göra ett spridningsdiagram är det viktigt att ha båda värdena (för de två variablerna som du vill plotta i spridningsdiagrammet) i två separata kolumner.
Kolumnen till vänster (Marketing Expense-kolumnen i vårt exempel) skulle ritas på X-axeln och intäkten skulle ritas på Y-axeln.
Nedan följer stegen för att infoga en spridningsdiagram i Excel:
- Välj de kolumner som har data (exklusive kolumn A)
- Klicka på alternativet Infoga
- Klicka på ikonen Infoga spridningsdiagram i diagramgruppen
- Klicka på alternativet "Scatter chart" i diagrammen som visas
Stegen ovan skulle infoga en spridningsdiagram som visas nedan i kalkylbladet.
Kolumnen till vänster (Marketing Expense-kolumnen i vårt exempel) skulle ritas på X-axeln och intäkten skulle ritas på Y-axeln. Det är bäst att ha det oberoende måttet i den vänstra kolumnen och den som du behöver hitta korrelationen i kolumnen till höger.
Lägga till en trendlinje i Scatter Chart
Medan jag kommer att täcka fler sätt att anpassa spridningsdiagrammet i Excel senare i den här självstudien, är en sak som du kan göra direkt efter att du har byggt upp spridningsdiagrammet att lägga till en trendlinje.
Det hjälper dig att snabbt få en känsla av om data är positivt eller negativt korrelerade, och hur tätt/löst korrelerat det är.
Nedan följer stegen för att lägga till en trendlinje till ett spridningsdiagram i Excel:
- Välj Scatter -plot (där du vill lägga till trendlinjen)
- Klicka på fliken Diagramdesign. Detta är en kontextuell flik som bara visas när du väljer diagrammet
- Klicka på alternativet "Lägg till diagramelement" i gruppen diagramlayouter
- Gå till alternativet "Trendline" och klicka sedan på "Linjär"
Stegen ovan skulle lägga till en linjär trendlinje i ditt spridningsdiagram.
Bara genom att titta på trendlinjen och datapunkterna som plottas i spridningsdiagrammet kan du få en känsla av om data är positivt korrelerade, negativt korrelerade eller inte korrelerade.
I vårt exempel ser vi en positiv lutning i trendlinjen som indikerar att data är positivt korrelerade. Det betyder att när marknadsföringskostnaderna stiger så ökar intäkterna och om marknadsföringskostnaderna sjunker går intäkterna ner.
Om data är negativt korrelerade skulle det finnas en omvänd relation. I så fall skulle marknadsföringskostnaderna stiga så skulle intäkterna sjunka och vice versa.
Och så finns det ett fall där det inte finns någon korrelation. I detta fall, när marknadsföringskostnaderna ökar, kan deras intäkter öka eller inte.
Observera att lutningen bara berättar om data är positivt eller negativt korrelerade. det säger inte hur nära det är.
Till exempel, i vårt exempel, genom att titta på trendlinjen kan vi inte säga hur mycket intäkterna kommer att öka när marknadsföringskostnaden ökar med 100%. Detta är något som kan beräknas med hjälp av korrelationskoefficienten.
Du kan hitta det med hjälp av följande formel:
= CORREL (B2: B11, C2: C11)
Korrelationskoefficienten varierar mellan -1 och 1, där 1 skulle indikera en perfekt positiv korrelation och -1 skulle indikera en perfekt negativ korrelation
I vårt exempel returnerar den 0,945, vilket indikerar att dessa två variabler har en hög positiv korrelation.
Identifiera kluster med hjälp av Scatter Chart (praktiska exempel)
Ett av de sätt som jag använde för att använda spridningsdiagram i mitt arbete som finansanalytiker var att identifiera kluster av datapunkter som uppvisar en liknande typ av beteende.
Detta fungerar vanligtvis bra när du har en mångsidig datauppsättning med mindre övergripande korrelation.
Antag att du har en datamängd som visas nedan, där jag har 20 företag med sina intäkter och vinstmarginalnummer.
När jag skapar en scatterplot för dessa data får jag något enligt nedan:
I det här diagrammet kan du se att datapunkterna är överallt och det finns en mycket låg korrelation.
Även om detta diagram inte säger så mycket, kan du på ett sätt använda det för att identifiera kluster i de fyra kvadranterna i diagrammet.
Till exempel är datapunkten i nedre vänstra kvadranten de företag där intäkterna är låga och nettovinstmarginalen är låg, och företagen i den nedre högra kvadranten är de där intäkterna är höga men nettovinstmarginalen är låg.
Detta brukade vara ett av de mycket diskuterade diagrammen i ledningsmötet när vi tidigare identifierade potentiella kunder baserat på deras finansiella data.
Olika typer av Scatter Plots i Excel
Bortsett från det vanliga spridningsschemat som jag har täckt ovan kan du också skapa följande scatterdiagramtyper i Excel:
- Sprida med släta linjer
- Sprida med släta linjer och markörer
- Sprida med raka linjer
- Sprida med raka linjer och markörer
Alla dessa fyra spridningsdiagram ovan är lämpliga när du har färre datapunkter och när du plottar två serier i diagrammet.
Anta till exempel att du har marknadsföringskostnad vs intäktsdata som visas nedan och du vill rita upp en scatter med jämna linjer.
Nedan följer stegen för att göra detta:
- Välj datauppsättningen (exklusive kolumnen företagsnamn)
- Klicka på fliken Infoga
- Klicka på alternativet Infoga spridningsdiagram i gruppen Diagram
- Klicka på alternativet Scatter with Smooth Lines and Markers
Du kommer att se något som visas nedan.
Detta diagram kan snabbt bli oläsligt om du har fler datapunkter. Det är därför det rekommenderas att använda detta endast med färre datapunkter.
Jag har aldrig använt det här diagrammet i mitt arbete eftersom jag inte tror att det ger någon meningsfull insikt (eftersom vi inte kan plotta fler datapunkter till det).
Anpassa Scatter Chart i Excel
Precis som alla andra diagram i Excel kan du enkelt anpassa spridningsdiagrammet.
I det här avsnittet kommer jag att täcka några av de anpassningar du kan göra med ett spridningsdiagram i Excel:
Lägga till / ta bort diagramelement
När du klickar på spridningsdiagrammet kommer du att se plusikonen längst upp till höger i diagrammet.
När du klickar på denna plusikon visar det alternativ som du enkelt kan lägga till eller ta bort från ditt scatterdiagram.
Här är alternativen du får:
- Axlar
- Axeltitel
- Diagramtitel
- Datatiketter
- Felstaplar
- Gridlines
- Legend
- Trendlinjer
Några av dessa alternativ finns redan i diagrammet, och du kan ta bort dessa element genom att markera kryssrutan bredvid alternativet (eller lägga till dessa genom att klicka på kryssrutan om den inte redan är markerad).
Om jag till exempel vill ta bort diagramtiteln kan jag helt enkelt avmarkera alternativet och det skulle vara borta,
Om du behöver mer kontroll kan du klicka på den lilla svarta pilen som visas när du håller markören över något av alternativen.
Genom att klicka på den får du fler alternativ för det specifika diagramelementet (dessa öppnas som en ruta till höger).
Obs! Alla skärmdumpar som jag har visat är från en ny version av Excel (Microsoft 365). Om du använder en äldre version kan du få samma alternativ när du högerklickar på någon av diagramelementen och klickar på alternativet Format.
Låt oss snabbt gå igenom dessa element och några av de fantastiska anpassningarna du kan göra för att sprida diagram med hjälp av det.
Axlar
Axlar är de vertikala och horisontella värdena som du ser bredvid diagrammet.
En av de mest användbara anpassningarna du kan göra med axlar är att justera det högsta och lägsta värdet det kan visa.
För att ändra detta, högerklicka på axlarna i diagrammet och klicka sedan på Formatera axlar. Detta öppnar rutan Format axel.
I alternativet Axel kan du ställa in minsta och högsta gräns såväl som större och mindre enheter.
I de flesta fall kan du ställa in detta till automatiskt, och Excel tar hand om det baserat på datamängden. Men om du vill ha specifika värden kan du ändra dem härifrån.
Ett exempel kan vara när du inte vill att minimivärdet i Y-axeln ska vara 0, men något annat (säg 1000). Om du ändrar den nedre gränsen till 1000 justeras diagrammet så att minimivärdet i den vertikala axeln då skulle vara 1000.
Axeltitel
Axeltiteln är något du kan använda för att ange vad X- och Y-axeln representerar i spridningsdiagrammet i Excel.
I vårt exempel skulle det vara nettoinkomsten för X-axeln och marknadsföringskostnaden för Y-axeln.
Du kan välja att inte visa någon axeltitel, och du kan ta bort dessa genom att markera diagrammet, klicka på plusikonen och sedan avmarkera rutan för axeltitel.
För att ändra texten i axeltiteln, dubbelklicka helt enkelt på den och skriv sedan vad du vill som axeltitel.
Du kan också länka axeltitelvärdet till en cell.
Om du till exempel vill att värdet i cell B1 ska visas i titeln för den vertikala axeln klickar du på axelrubriken och anger sedan = B1 i formelfältet. Detta visar värdet i cell B1 i axeltiteln.
Du kan göra samma sak för den horisontella axeltiteln och länka den till en specifik cell. Detta gör dessa titlar dynamiska och om cellvärdet ändras kommer axeltitlarna också att ändras.
Om du behöver mer kontroll över formatering av axeltitlar, klicka på valfri axel, högerklicka och klicka sedan på Formatera axeltitel.
Med dessa alternativ kan du ändra fyllning och kant för titeln, ändra textfärg, justering och rotation.
Diagramtitel
Precis som Axeltitlar kan du också formatera diagramtiteln i en spridningsdiagram i Excel.
En diagramtitel används vanligtvis för att beskriva vad diagrammet handlar om. Till exempel kan jag använda "Marketing Expense Vs Revenue" som diagramtitel.
Om du inte vill ha diagramtiteln kan du klicka och ta bort den. Och om du inte har det, välj diagrammet, klicka på plusikonen och markera sedan alternativet Diagramtitel.
För att redigera texten i diagramtiteln, dubbelklicka på rutan och skriv in texten manuellt där. Och om du vill göra diagramtiteln dynamisk kan du klicka på rubriken och sedan skriva cellreferensen eller formeln i formelfältet.
För att formatera diagramtiteln, högerklicka på diagramtiteln och klicka sedan på alternativet "Format diagramtitel". Detta visar rutan Format diagramtitel till höger.
Med dessa alternativ kan du ändra fyllning och kant för titeln, ändra textfärg, justering och rotation.
Datatiketter
Som standard är datatiketter inte synliga när du skapar en spridningsdiagram i Excel.
Men du kan enkelt lägga till och formatera dessa.
Lägg till datatiketterna i spridningsdiagrammet, välj diagrammet, klicka på plusikonen till höger och markera sedan alternativet datatiketter.
Detta lägger till datatiketterna som visar värdet på Y-axeln för varje datapunkt i spridningsdiagrammet.
För att formatera datatiketterna, högerklicka på någon av datatiketterna och klicka sedan på alternativet "Formatera datatiketter".
Detta öppnar det tidigare datatikettfönstret till höger, och du kan anpassa dessa med hjälp av olika alternativ som listas i rutan.
Förutom den vanliga formateringen som fyllning, kant, textfärg och justering får du också några ytterligare etikettalternativ som du kan använda.
I alternativen "Etikett innehåller" kan du välja att visa både X-axeln och Y-axelvärdet, istället för bara Y-axeln.
Du kan också välja alternativet "Värde från celler". som låter dig ha datatiketter som finns där i en kolumn i kalkylbladet (det öppnar en dialogruta när du väljer det här alternativet och du kan välja ett cellintervall vars värden skulle visas i datatiketterna. I vårt exempel, Jag kan använda detta för att visa företagsnamn i datatiketterna
Du kan också anpassa etikettens position och formatet där den visas.
Felstaplar
Även om jag inte har sett felstaplar som används i spridningsdiagram, har Excel ett alternativ som låter dig lägga till dessa felstaplar för varje datapunkt i spridningsplanen i Excel.
Om du vill lägga till felstaplarna väljer du diagrammet, klickar på plusikonen och markerar sedan alternativet Felstaplar.
Och om du vill anpassa dessa felrader ytterligare högerklickar du på någon av dessa felstaplar och klickar sedan på alternativet "Formatera felrader".
Detta öppnar rutan "Formatera felfält" till höger, där du kan anpassa saker som färg, riktning och stil för felstaplarna.
Gridlines
Gridlines är användbara när du har många datapunkter på ditt diagram eftersom det gör det möjligt för läsaren att snabbt förstå datapunktens position.
När du skapar en scatterplot i Excel är gridlines aktiverade som standard.
Du kan formatera dessa rutnät genom att högerklicka på någon av rutnäten och klicka på alternativet Formatera rutnät.
Detta öppnar rutan Format rutnät på det rätta sättet du kan ändra formateringen, till exempel färgen, tjockleken, på rutnätet.
Förutom de stora nätlinjer som redan är synliga när du skapar spridningsdiagrammet kan du också lägga till mindre rutnät.
Mellan två stora nätlinjer kan du ha några mindre rutlinjer som ytterligare förbättrar diagrammets läsbarhet om du har många datapunkter.
Om du vill lägga till mindre horisontella eller vertikala rutnät markerar du diagrammet, klickar på plusikonen och för markören över alternativet Gridlines.
Klicka på den tjocka svarta pilen som visas och kontrollera sedan alternativet "Primär mindre horisontellt" eller "Primärt mindre vertikalt" för att lägga till de mindre rutnäten
Legend
Om du har flera serier ritade i spridningsdiagrammet i Excel kan du använda en förklaring som anger vilken datapunkt som hänvisar till vilken serie.
Som standard finns det ingen förklaring när du skapar ett spridningsdiagram i Excel.
Om du vill lägga till en förklaring i spridningsdiagrammet markerar du diagrammet, klickar på plusikonen och markerar sedan alternativet förklaring.
För att formatera legenden, högerklicka på legenden som visas och klicka sedan på alternativet "Format legend".
I fönstret Formatförklaring som öppnas kan du anpassa fyllningsfärgen, gränsen och positionen för legenden i diagrammet.
Trendlinje
Du kan också lägga till en trendlinje i spridningsdiagrammet som visar om det finns en positiv eller negativ korrelation i datamängden.
Jag har redan täckt hur man lägger till en trendlinje till ett spridningsdiagram i Excel i ett av avsnitten ovan.
3D Scatter Plot i Excel (undviks bäst)
Till skillnad från ett linjediagram, kolumndiagram eller områdesdiagram finns det inget inbyggt 3D -spridningsdiagram i Excel.
Även om du kan använda tillägg och verktyg från tredje part för att göra detta, kan jag inte tänka mig någon ytterligare fördel som du kommer att få med ett 3D-spridningsdiagram jämfört med ett vanligt 2D-spridningsdiagram.
Faktum är att jag rekommenderar att hålla sig borta från alla typer av 3D -diagram eftersom det har potential att förvränga data och delar i diagrammet.
Så här kan du skapa en spridningsdiagram i Excel och anpassa den så att den passar ditt varumärke och krav.
Jag hoppas att du fann denna handledning användbar.
