Как да направите скатер в Excel (XY диаграма)

Excel има някои полезни типове диаграми, които могат да се използват за начертаване на данни и показване на анализ.

Често срещан сценарий е, когато искате да начертаете стойности X и Y в диаграма в Excel и да покажете как са свързани двете стойности.

Това може да стане с помощта на a Скатер диаграма в Excel.

Например, ако имате данни за височина (стойност X) и тегло (стойност Y) за 20 ученици, можете да начертаете това в диаграма за разсейване и тя ще ви покаже как са свързани данните.

По -долу е даден пример за Scatter Plot в Excel (наричан още XY диаграма):

В този урок ще ви покажа как да направите скатер график в Excel, различните типове разсейващи графики и как да персонализирате тези диаграми.

Какво е разпръскваща диаграма и кога да я използваме?

Скатерните диаграми се използват за разбиране на корелацията (свързаността) между две променливи данни.

Графикът на разсейване има точки, където всяка точка представлява две стойности (стойност по оста X и стойност по оста Y) и въз основа на тези стойности тези точки се позиционират в диаграмата.

Пример от това в реалния живот могат да бъдат маркетинговите разходи и приходите на група компании в конкретна индустрия.

Когато начертаем тези данни (маркетингови разходи срещу приходи) в разпръсната диаграма, можем да анализираме колко силно или слабо са свързани тези две променливи.

Създаване на Scatter Plot в Excel

Да предположим, че имате набор от данни, както е показано по -долу и искате да създадете диаграма за разсейване, използвайки тези данни.

Целта на тази диаграма е да се види дали има някаква връзка между маркетинговия бюджет и приходите или не.

За да направите разсейващ график, е важно да имате и двете стойности (от двете променливи, които искате да начертаете в разсейващата диаграма) в две отделни колони.

Колоната вляво (колонка за маркетингови разходи в нашия пример) ще бъде нанесена на оста X, а приходите ще бъдат нанесени на оста Y.

По -долу са описани стъпките за вмъкване на график за разсейване в Excel:

1. Изберете колоните, които съдържат данните (с изключение на колона А)

2. Щракнете върху опцията Вмъкване

3. В групата Диаграма кликнете върху иконата Вмъкване на разсейваща диаграма

4. Кликнете върху опцията „Scatter chart“ в диаграмите, които се показват

Горните стъпки биха вмъкнали график за разсейване, както е показано по -долу в работния лист.

Колоната вляво (колона за маркетингови разходи в нашия пример) ще бъде нанесена на оста X, а приходите ще бъдат нанесени на оста Y. Най -добре е да имате независим показател в лявата колона и този, за който трябва да намерите корелацията в колоната вдясно.

Добавяне на линия на тенденция към разсейващата диаграма

Въпреки че по -нататък в този урок ще разгледам повече начини за персонализиране на диаграмата за разсейване в Excel, едно нещо, което можете да направите веднага след изграждането на диаграмата за разсейване, е да добавите линия на тенденция.

Помага ви бързо да разберете дали данните са корелирани положително или отрицателно и колко тясно/слабо са свързани.

По -долу са стъпките за добавяне на линия на тренда към разсейваща диаграма в Excel:

1. Изберете графиката Scatter (където искате да добавите линията на тренда)
2. Щракнете върху раздела Дизайн на диаграма. Това е контекстуален раздел, който се появява само когато изберете диаграмата

3. В групата Оформления на диаграми кликнете върху опцията „Добавяне на елемент на диаграма“

4. Отидете на опцията „Trendline“ и след това кликнете върху „Linear“

Горните стъпки биха добавили линейна линия на тренда към вашата диаграма за разсейване.

Само като погледнете линията на тренда и точките от данни, нанесени в диаграмата на разсейване, можете да получите представа дали данните са корелирани положително, отрицателно или не.

В нашия пример виждаме положителен наклон в линията на тренда, показващ, че данните са положително свързани. Това означава, че когато маркетинговите разходи се повишат, тогава приходите се увеличават, а ако маркетинговите разходи спадат, тогава приходите намаляват.

В случай, че данните са отрицателно свързани, тогава би имало обратна връзка. В този случай, ако разходите за маркетинг се увеличат, приходите ще спаднат и обратно.

И тогава има случай, когато няма връзка. В този случай, когато разходите за маркетинг се увеличат, техните приходи могат или не могат да се увеличат.

Имайте предвид, че наклонът ни казва само дали данните са положително или отрицателно свързани. не ни казва колко тясно е свързано.

Например в нашия пример, разглеждайки линията на тренда, не можем да кажем колко ще се увеличат приходите, когато разходите за маркетинг се увеличат със 100%. Това е нещо, което може да се изчисли с помощта на коефициента на корелация.

Можете да откриете това, като използвате формулата по -долу:

= CORREL (B2: B11, C2: C11)

Коефициентът на корелация варира между -1 и 1, където 1 означава перфектно положителна корелация, а -1 означава перфектно отрицателна корелация

В нашия пример връща 0.945, което показва, че тези две променливи имат висока положителна корелация.

Идентифициране на клъстери с помощта на Скатер диаграма (Практически примери)

Един от начините, по които използвах разпръснати диаграми в работата си като финансов анализатор, беше да идентифицирам групи от точки от данни, които проявяват подобно поведение.

Това обикновено работи добре, когато имате разнообразен набор от данни с по -малка обща корелация.

Да предположим, че имате набор от данни, както е показано по -долу, където имам 20 компании с техните номера на приходите и маржа на печалбата.

Когато създавам скатер за тези данни, получавам нещо, както е показано по -долу:

В тази диаграма можете да видите, че точките от данни са навсякъде и има много ниска корелация.

Въпреки че тази диаграма не ни казва много, един начин можете да я използвате за идентифициране на клъстери в четирите квадранта в диаграмата.

Например точката за данни в долния ляв квадрант са онези компании, в които приходите са ниски, а маржът на нетната печалба е нисък, а компаниите в долния десен квадрант са тези, в които приходите са високи, но маржът на нетната печалба е нисък.

Това беше една от най -обсъжданите диаграми на срещата на ръководството, когато използвахме за идентифициране на потенциални клиенти въз основа на техните финансови данни.

Различни видове разпръснати графики в Excel

Освен обикновената диаграма за разсейване, която разгледах по -горе, можете също да създадете следните типове разсейване в Excel:

• Скатер с гладки линии
• Скатер с гладки линии и маркери
• Скатер с прави линии
• Скатер с прави линии и маркери

Всички тези четири горепосочени графики са подходящи, когато имате по -малко точки от данни и когато нанасяте две серии в диаграмата.

Да предположим например, че имате данни за маркетинговите разходи спрямо приходите, както е показано по -долу, и искате да начертаете скатер с диаграма с гладки линии.

По -долу са описани стъпките за това:

1. Изберете набора от данни (с изключение на колоната с името на компанията)
2. Щракнете върху раздела Вмъкване
3. В групата Графики щракнете върху опцията Вмъкване на разсейваща диаграма
4. Кликнете върху опциите Скатер с гладки линии и маркери

Ще видите нещо, както е показано по -долу.

Тази диаграма може бързо да стане нечетлива, ако имате повече точки от данни. Ето защо се препоръчва да се използва само с по -малко точки от данни.

Никога не съм използвал тази диаграма в работата си, тъй като не мисля, че дава смислена представа (тъй като не можем да начертаем повече точки от данни към нея).

Персонализиране на Scatter Chart в Excel

Подобно на всяка друга диаграма в Excel, можете лесно да персонализирате диаграмата за разсейване.

В този раздел ще разгледам някои от персонализациите, които можете да направите с разсейваща диаграма в Excel:

Добавяне / премахване на елементи на диаграма

Когато кликнете върху разсейващата диаграма, ще видите икона плюс в горната дясна част на диаграмата.

Когато щракнете върху тази икона плюс, тя ще ви покаже опции, които лесно можете да добавите или премахнете от вашата диаграма за разсейване.

Ето опциите, които получавате:

• Брадви
• Заглавие на оста
• Заглавие на диаграмата
• Етикети с данни
• Ленти за грешки
• Линии на мрежата
• Легенда
• Тенденции

Някои от тези опции вече присъстват във вашата диаграма и можете да премахнете тези елементи, като щракнете върху квадратчето до опцията (или да ги добавите, като щракнете върху квадратчето за отметка, ако вече не е поставено отметка).

Например, ако искам да премахна заглавието на диаграмата, мога просто да премахна отметката от опцията и тя ще изчезне,

В случай, че имате нужда от повече контрол, можете да кликнете върху малката черна стрелка, която се появява, когато задържите курсора на мишката върху някоя от опциите.

Кликването върху него ще ви даде повече възможности за този конкретен елемент на диаграма (те се отварят като панел от дясната страна).

Забележка: Всички екранни снимки, които ви показах, са от последна версия на Excel (Microsoft 365). В случай, че използвате по-стара версия, можете да получите същите опции, когато щракнете с десния бутон върху някой от елементите на диаграмата и кликнете върху опцията Форматиране.

Нека бързо преминем през тези елементи и някои от страхотните персонализации, които можете да направите, за да разпръснете диаграми, използвайки го.

Брадви

Осите са вертикалните и хоризонталните стойности, които виждате точно до диаграмата.

Едно от най -полезните персонализации, които можете да направите с оси, е да регулирате максималната и минималната стойност, която може да покаже.

За да промените това, щракнете с десния бутон върху осите в диаграмата и след това щракнете върху Форматиране на оси. Това ще отвори прозореца Форматиране на оста.

В опцията Axis можете да зададете минималните и максималните граници, както и основните и второстепенните единици.

В повечето случаи можете да зададете това на автоматично и Excel ще се погрижи за това въз основа на набора от данни. Но в случай, че искате конкретни стойности, можете да ги промените от тук.

Един пример може да бъде, когато не искате минималната стойност в оста Y да бъде 0, а нещо друго (да речем 1000). Промяната на долната граница на 1000 ще коригира диаграмата, така че минималната стойност във вертикалната ос да бъде 1000.

Заглавие на оста

Заглавието на оста е нещо, което можете да използвате, за да посочите какво представляват оста X и Y в диаграмата за разсейване в Excel.

В нашия пример това ще бъде нетният доход за оста X и маркетинговите разходи за оста Y.

Можете да изберете да не показвате заглавие на оста и можете да ги премахнете, като изберете диаграмата, щракнете върху иконата плюс и след това премахнете отметката от квадратчето за заглавие на оста.

За да промените текста в заглавието на оста, просто щракнете двукратно върху него и след това въведете каквото искате като заглавие на оста.

Можете също да свържете стойността на заглавието на оста с клетка.

Например, ако искате стойността в клетка B1 да се показва в заглавието на вертикалната ос, щракнете върху полето за заглавие на оста и след това въведете = B1 в лентата с формули. Това ще покаже стойността в клетка B1 в заглавието на оста.

Можете да направите същото за заглавието на хоризонталната ос и да го свържете с определена клетка. Това прави тези заглавия динамични и ако стойността на клетката се промени, заглавията на осите също ще се променят.

Ако имате нужда от повече контрол при форматирането на заглавията на осите, щракнете върху която и да е ос, щракнете с десния бутон и след това щракнете върху Форматиране на заглавието на оста.

С тези опции можете да промените запълването и границата на заглавието, да промените цвета на текста, подравняването и завъртането.

Заглавие на диаграмата

Точно както заглавията на оста, можете също да форматирате заглавието на диаграма в разпръснат график в Excel.

Заглавието на диаграмата обикновено се използва, за да опише какво представлява диаграмата. Например, мога да използвам „Маркетингови разходи срещу приходи“ като заглавие на диаграмата.

Ако не искате заглавието на диаграмата, можете да щракнете върху него и да го изтриете. И в случай, че го нямате, изберете диаграмата, щракнете върху иконата плюс и след това проверете опцията Заглавие на диаграма.

За да редактирате текста в заглавието на диаграмата, щракнете двукратно върху полето и въведете ръчно желания от вас текст. И в случай, че искате да направите заглавието на диаграмата динамично, можете да щракнете върху полето със заглавие и след това да въведете препратката към клетката или формулата в лентата с формули.

За да форматирате заглавието на диаграмата, щракнете с десния бутон върху заглавието на диаграмата и след това върху опцията „Форматиране на заглавието на диаграмата“. Това ще покаже прозореца Форматиране на заглавието на диаграмата вдясно.

С тези опции можете да промените запълването и границата на заглавието, да промените цвета на текста, подравняването и завъртането.

Етикети с данни

По подразбиране етикетите с данни не се виждат, когато създавате диаграма за разсейване в Excel.

Но можете лесно да ги добавяте и форматирате.

Добавете етикетите с данни към диаграмата за разсейване, изберете диаграмата, щракнете върху иконата плюс вдясно и след това проверете опцията етикети за данни.

Това ще добави етикетите с данни, които ще показват стойността на оста Y за всяка точка от данните в графиката за разсейване.

За да форматирате етикетите с данни, щракнете с десния бутон върху някой от етикетите с данни и след това щракнете върху опцията „Форматиране на етикети с данни“.

Това ще отвори предишния панел с етикети на данни вдясно и можете да ги персонализирате, като използвате различни опции, изброени в прозореца.

Освен редовното форматиране, като запълване, рамка, цвят на текста и подравняване, получавате и някои допълнителни опции за етикети, които можете да използвате.

В опциите „Етикетът съдържа“ можете да изберете да показвате както стойността на оста X, така и на оста Y, вместо само оста Y.

Можете също да изберете опцията „Стойност от клетки“. което ще ви позволи да имате етикети с данни, които са там в колона в работния лист (тя отваря диалогов прозорец, когато изберете тази опция и можете да изберете диапазон от клетки, чиито стойности ще се показват в етикетите с данни. В нашия пример, Мога да използвам това, за да показвам имената на фирмите в етикетите с данни

Можете също така да персонализирате позицията на етикета и формата, в който е показан.

Ленти за грешки

Въпреки че не съм виждал да се използват ленти за грешки в разпръснати диаграми, Excel има опция, която ви позволява да добавяте тези ленти за грешки за всяка точка от данни в графиката за разсейване в Excel.

За да добавите лентите за грешки, изберете диаграмата, щракнете върху иконата плюс и след това проверете опцията Ленти за грешки.

И ако искате да персонализирате допълнително тези ленти за грешки, щракнете с десния бутон върху някоя от тези ленти за грешки и след това щракнете върху опцията „Форматиране на ленти за грешки“.

Това ще отвори прозореца „Форматиране на ленти за грешки“ вдясно, където можете да персонализирате неща като цвят, посока и стил на лентите за грешки.

Линии на мрежата

Линиите на мрежата са полезни, когато имате много точки от данни във вашата диаграма, тъй като позволява на читателя бързо да разбере позицията на точката с данни.

Когато създавате разпределение в Excel, линиите на мрежата са активирани по подразбиране.

Можете да форматирате тези линии на мрежата, като щракнете с десния бутон върху някоя от линиите на мрежата и щракнете върху опцията Форматиране на линиите на мрежата.

Това ще отвори прозореца Форматиране на линиите на мрежата по правилния начин, по който можете да промените форматирането, като например цвета, дебелината на мрежата.

Освен основните линии на мрежата, които вече са видими, когато създавате диаграма за разсейване, можете да добавите и малки линии на мрежата.

Между две основни линии на мрежата можете да имате няколко малки линии на мрежата, които допълнително подобряват четимостта на диаграмата, в случай че имате много точки от данни.

За да добавите малки хоризонтални или вертикални линии на мрежата, изберете диаграмата, щракнете върху иконата плюс и задръжте курсора на мишката над опцията Gridlines.

Щракнете върху дебелата черна стрелка, която се появява и след това проверете опцията „Първична малка хоризонтална“ или „Първична малка вертикална“, за да добавите второстепенните линии на мрежата

Легенда

Ако имате няколко серии, начертани в разсейващата диаграма в Excel, можете да използвате легенда, която да обозначава коя точка от данни се отнася до каква серия.

По подразбиране няма легенда, когато създавате диаграма за разсейване в Excel.

За да добавите легенда към разпръснатата диаграма, изберете диаграмата, щракнете върху иконата плюс и след това проверете опцията легенда.

За да форматирате легендата, щракнете с десния бутон върху легендата, която се показва и след това щракнете върху опцията „Форматиране на легендата“.

В прозореца Форматиране на легенда, който се отваря, можете да персонализирате цвета на запълване, рамката и позицията на легендата в диаграмата.

Тенденция

Можете също така да добавите линия на тренда в разсейващата диаграма, която да покаже дали има положителна или отрицателна корелация в набора от данни.

Вече разгледах как да добавя линия на тенденция към диаграма за разсейване в Excel в един от разделите по -горе.

3D Scatter Plot в Excel (най -добре се избягват)

За разлика от линейна диаграма, колонна диаграма или диаграма на площ, няма вградена 3D разсейваща диаграма в Excel.

Въпреки че можете да използвате добавки и инструменти на трети страни, за да направите това, не мога да се сетя за някакво допълнително предимство, което ще получите с 3D разсейваща диаграма в сравнение с обикновена 2D скатерна диаграма.

Всъщност препоръчвам да стоите настрана от всякакъв вид 3D диаграма, тъй като тя има потенциал за погрешно представяне на данните и частите в диаграмата.

Ето как можете да създадете разпръснат график в Excel и да го персонализирате така, че да отговаря на вашата марка и изисквания.

Надявам се, че сте намерили този урок за полезен.