Як створити діаграму розсіювання в Excel (діаграма XY)

Excel має кілька корисних типів діаграм, які можна використовувати для побудови даних та аналізу.

Поширений сценарій, коли потрібно нанести значення X і Y на діаграму в Excel і показати, як ці два значення пов'язані.

Це можна зробити за допомогою a Розсіяна діаграма в Excel.

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

Нижче наведено приклад діаграми розсіювання в Excel (її також називають діаграмою XY):

У цьому посібнику я покажу вам, як створити діаграму розсіювання в Excel, різні типи діаграм розсіювання та як налаштувати ці діаграми.

Що таке діаграма розсіювання і коли її використовувати?

Розсіяні діаграми використовуються для розуміння кореляції (спорідненості) між двома змінними даних.

Діаграма розсіювання має точки, де кожна точка представляє два значення (значення осі X та значення осі Y), і на основі цих значень ці точки розміщуються на діаграмі.

Прикладом цього в реальному житті можуть бути витрати на маркетинг та дохід групи компаній у певній галузі.

Коли ми розміщуємо ці дані (Витрати на маркетинг проти доходів) у діаграмі розсіювання, ми можемо проаналізувати, наскільки міцно або слабо пов'язані ці дві змінні.

Створення діаграми розсіювання в Excel

Припустимо, у вас є набір даних, як показано нижче, і ви хочете створити діаграму розсіювання, використовуючи ці дані.

Мета цієї діаграми - з’ясувати, чи існує якась кореляція між маркетинговим бюджетом та доходом чи ні.

Для створення діаграми розсіювання важливо мати обидва значення (двох змінних, які потрібно нанести на діаграмі розсіювання) у двох окремих стовпцях.

Стовпець ліворуч (у нашому прикладі стовпець Витрати на маркетинг) буде нанесено на осі X, а дохід-на осі Y.

Нижче наведено кроки для вставки діаграми розсіювання в Excel:

1. Виберіть стовпці, які містять дані (за винятком стовпця А)

2. Натисніть на опцію Вставити

3. У групі Діаграма натисніть значок Вставити діаграму розсіювання

4. Натисніть на опцію "Scatter chart" у діаграмах, які відображаються

Вищезазначені кроки вставлятимуть діаграму розсіювання, як показано нижче на робочому аркуші.

Стовпець ліворуч (у нашому прикладі стовпець Витрати на маркетинг) буде нанесено на осі X, а дохід-на осі Y. Найкраще мати незалежну метрику у лівому стовпці та ту, для якої вам потрібно знайти кореляцію у стовпці праворуч.

Додавання лінії тренду до діаграми розсіювання

Хоча я розгляну інші способи налаштування діаграми розсіювання в Excel далі в цьому посібнику, одна річ, яку ви можете зробити відразу після побудови діаграми розсіювання, це додати лінію тренду.

Це допомагає вам швидко зрозуміти, чи позитивно чи негативно корелюють дані, і наскільки це тісно/слабо співвідноситься.

Нижче наведено кроки, щоб додати лінію тренду до діаграми розсіювання в Excel:

1. Виберіть графік Scatter (куди потрібно додати лінію тренду)
2. Перейдіть на вкладку Проектування діаграм. Це контекстна вкладка, яка з'являється лише при виборі діаграми

3. У групі Макети діаграм натисніть опцію «Додати елемент діаграми»

4. Перейдіть до опції "Лінія тренда", а потім натисніть "Лінійна"

Наведені вище кроки додадуть лінійну лінію тренду до вашої діаграми розсіювання.

Просто переглянувши лінію тренда та точки даних, нанесені на діаграмі розсіювання, можна зрозуміти, чи позитивно корелюють дані дані, чи негативно корелюють, чи не корелюють.

У нашому прикладі ми бачимо позитивний нахил у лінії тренду, що вказує на те, що дані позитивно корелюють. Це означає, що коли маркетингові витрати зростають, то дохід зростає, а якщо маркетингові витрати падають, то і дохід падає.

У разі, якщо дані мають негативну кореляцію, то існує зворотна залежність. У цьому випадку, якщо витрати на маркетинг зростуть, дохід зменшиться і навпаки.

І тут є випадок, коли немає кореляції. У цьому випадку, коли витрати на маркетинг збільшуються, їх дохід може збільшуватися, а може і не збільшуватися.

Зауважте, що нахил говорить нам лише про те, чи позитивно чи негативно корелюють дані. це не повідомляє нам, наскільки тісно це пов'язано.

Наприклад, у нашому прикладі, дивлячись на лінію тренда, ми не можемо сказати, наскільки збільшиться дохід, коли витрати на маркетинг збільшаться на 100%. Це те, що можна обчислити за допомогою коефіцієнта кореляції.

Ви можете знайти це, використовуючи формулу нижче:

= CORREL (B2: B11, C2: C11)

Коефіцієнт кореляції коливається між -1 і 1, де 1 вказує на абсолютно позитивну кореляцію, а -1 вказує на абсолютно негативну кореляцію

У нашому прикладі він повертає 0,945, вказуючи на те, що ці дві змінні мають високу позитивну кореляцію.

Визначення кластерів за допомогою діаграми розсіювання (практичні приклади)

Одним із способів, якими я використовував діаграми розсіювання у своїй роботі як фінансовий аналітик, було виявлення кластерів точок даних, які демонструють подібну поведінку.

Це зазвичай добре працює, коли у вас є різноманітний набір даних з меншою загальною кореляцією.

Припустимо, у вас є набір даних, як показано нижче, де я маю 20 компаній з їхніми показниками доходу та прибутку.

Коли я створюю діаграму розсіювання для цих даних, я отримую щось, як показано нижче:

На цій діаграмі ви можете побачити, що точки даних є повсюдно і кореляція дуже низька.

Хоча ця діаграма не говорить нам багато чого, одним із способів її використання можна визначити кластери у чотирьох квадрантах діаграми.

Наприклад, точкою даних у нижньому лівому квадранті є ті компанії, де дохід низький, а норма чистого прибутку низька, а компанії в нижньому правому квадранті - це ті компанії, де дохід високий, але маржа чистого прибутку низька.

Раніше це була одна з найбільш обговорюваних діаграм на засіданні керівництва, коли ми використовували ідентифікацію потенційних клієнтів на основі їх фінансових даних.

Різні типи діаграм розсіювання в Excel

Крім звичайної діаграми розсіювання, яку я розглянув вище, ви також можете створити в Excel такі типи діаграм розсіювання:

• Scatter з гладкими лініями
• Скатер з гладкими лініями та маркерами
• Скаттер з прямими лініями
• Скаттер з прямими лініями та маркерами

Усі ці чотири наведених вище діаграми розсіювання підходять, коли у вас менше точок даних і коли ви наносите дві діаграми на діаграму.

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

Нижче наведено кроки для цього.

1. Виберіть набір даних (за винятком стовпця з назвою компанії)
2. Перейдіть на вкладку Вставка
3. У групі Діаграми натисніть на опцію Вставити діаграму розсіювання
4. Натисніть на опції Scatter with Smooth Lines and Markers

Ви побачите щось, як показано нижче.

Ця діаграма може швидко стати нечитабельною, якщо у вас більше точок даних. Ось чому рекомендується використовувати це лише з меншою кількістю точок даних.

Я ніколи не використовував цю діаграму у своїй роботі, оскільки не думаю, що вона дає будь -яке значуще уявлення (оскільки ми не можемо нанести на неї більше даних).

Налаштування діаграми розсіювання в Excel

Як і будь -яку іншу діаграму в Excel, ви можете легко налаштувати діаграму розсіювання.

У цьому розділі я розгляну деякі налаштування, які ви можете зробити за допомогою діаграми розсіювання в Excel:

Додавання / видалення елементів діаграми

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

Коли ви натиснете на цей значок плюс, він покаже вам параметри, які ви можете легко додати або видалити зі своєї діаграми розсіювання.

Ось варіанти, які ви отримаєте:

• Сокири
• Назва осі
• Заголовок діаграми
• Мітки даних
• Панелі помилок
• Лінії сітки
• Легенда
• Лінії тренда

Деякі з цих параметрів вже є у вашій діаграмі, і ви можете видалити ці елементи, поставивши прапорець біля опції (або додати їх, поставивши прапорець, якщо вона ще не встановлена).

Наприклад, якщо я хочу видалити заголовок діаграми, я можу просто зняти прапорець, і його не буде,

Якщо вам потрібен більший контроль, ви можете натиснути на маленьку чорну стрілку, яка з’являється, коли наводите курсор на будь -який із параметрів.

Натиснувши на нього, ви отримаєте більше можливостей для цього конкретного елемента діаграми (вони відкриваються як панель праворуч).

Примітка: Усі скріншоти, які я вам показав, є з останньої версії Excel (Microsoft 365). Якщо ви використовуєте стару версію, ви можете отримати ті ж параметри, клацнувши правою кнопкою миші на будь-якому з елементів діаграми та натиснувши на опцію Формат.

Давайте швидко розглянемо ці елементи та деякі чудові налаштування, які ви можете зробити, щоб розкидати діаграми за його допомогою.

Сокири

Осі - це вертикальні та горизонтальні значення, які ви бачите прямо біля діаграми.

Одне з найбільш корисних налаштувань, яке ви можете зробити за допомогою осей, - це налаштувати максимальне та мінімальне значення, яке воно може показувати.

Щоб змінити це, клацніть правою кнопкою миші на осях у діаграмі, а потім клацніть на Формат осей. Відкриється панель «Формат осі».

В опції Ось можна встановити мінімальну та максимальну межі, а також основні та другорядні одиниці.

У більшості випадків ви можете встановити для цього значення автоматичний, і Excel почне це робити на основі набору даних. Але якщо вам потрібні конкретні значення, ви можете змінити їх тут.

Одним із прикладів може бути, коли ви не хочете, щоб мінімальне значення на осі Y було 0, а щось інше (скажімо 1000). Зміна нижньої межі на 1000 відрегулює діаграму так, щоб мінімальне значення у вертикальній осі становило 1000.

Назва осі

Заголовок осі-це те, що можна використовувати для визначення того, що вісь X і Y представляють на діаграмі розсіювання в Excel.

У нашому прикладі це буде чистий дохід для осі X та маркетингові витрати для осі Y.

Ви можете не показувати заголовок осі, а також видалити їх, вибравши діаграму, натиснувши на значок плюса, а потім знявши прапорець біля заголовка Осі.

Щоб змінити текст у заголовку осі, просто двічі клацніть на ньому, а потім введіть все, що завгодно, як заголовок осі.

Ви також можете прив'язати значення заголовка Осі до клітинки.

Наприклад, якщо ви хочете, щоб значення у комірці B1 відображалося у заголовку вертикальної осі, клацніть на полі заголовка осі, а потім введіть = B1 у рядку формул. Це покаже значення у комірці B1 у заголовку осі.

Ви можете зробити те ж саме для заголовка горизонтальної осі та зв’язати його з певною коміркою. Це робить ці заголовки динамічними, і якщо змінюється значення комірки, змінюються і назви осей.

Якщо вам потрібен додатковий контроль над форматуванням заголовків осей, клацніть на будь-якій осі, клацніть правою кнопкою миші, а потім клацніть на Формат заголовка осі.

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

Заголовок діаграми

Так само, як і назви осі, ви також можете відформатувати назву діаграми в діаграмі розкиду в Excel.

Заголовок діаграми зазвичай використовується для опису того, про що діаграма. Наприклад, я можу використати "Маркетингові витрати проти доходів" як назву діаграми.

Якщо вам не потрібна назва діаграми, ви можете натиснути та видалити її. І якщо у вас його немає, виберіть діаграму, клацніть піктограму плюс, а потім перевірте параметр Назва діаграми.

Щоб відредагувати текст у заголовку діаграми, двічі клацніть на полі та вручну введіть потрібний текст. І якщо ви хочете зробити назву діаграми динамічною, ви можете натиснути поле заголовка, а потім ввести посилання на комірку або формулу у рядку формул.

Щоб відформатувати назву діаграми, клацніть правою кнопкою миші на заголовку діаграми, а потім натисніть на опцію "Форматувати назву діаграми". Це покаже панель Формат заголовка діаграми праворуч.

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

Мітки даних

За замовчуванням мітки даних не відображаються під час створення діаграми розсіювання в Excel.

Але їх можна легко додати та відформатувати.

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

Це додасть мітки даних, які покажуть значення осі Y для кожної точки даних на графіку розсіювання.

Щоб відформатувати мітки даних, клацніть правою кнопкою миші будь-яку з міток даних, а потім виберіть опцію «Форматувати мітки даних».

Це відкриє попередню панель міток даних праворуч, і ви можете налаштувати їх за допомогою різних параметрів, перерахованих на панелі.

Окрім звичайного форматування, такого як заливка, рамка, колір тексту та вирівнювання, ви також можете отримати деякі додаткові параметри міток.

У параметрах "Мітка містить" можна вибрати відображення осі X та осі Y замість лише осі Y.

Ви також можете вибрати опцію «Значення із клітинок». що дозволить вам мати мітки даних, які є у стовпці на робочому аркуші (він відкриває діалогове вікно, коли ви виберете цей параметр, і ви зможете вибрати діапазон клітинок, значення яких відображатимуться у мітках даних. У нашому прикладі, Я можу використовувати це, щоб відображати назви компаній у мітках даних

Ви також можете налаштувати розташування етикетки та формат її відображення.

Панелі помилок

Хоча я не бачив, щоб у діаграмах розсіювання використовувалися смуги помилок, у Excel є опція, яка дозволяє додавати ці смуги помилок до кожної точки даних у графіку розсіювання в Excel.

Щоб додати смуги помилок, виберіть діаграму, клацніть піктограму плюс, а потім перевірте параметр «Панелі помилок».

І якщо ви хочете ще більше налаштувати ці смуги помилок, клацніть правою кнопкою миші на будь-якій з цих панелей помилок, а потім клацніть на опції «Форматувати смуги помилок».

Це відкриє панель "Формат панелей помилок" праворуч, де ви можете налаштувати такі речі, як колір, напрямок та стиль панелей помилок.

Лінії сітки

Лінії сітки корисні, коли у вас на графіку багато точок даних, оскільки це дозволяє читачеві швидко зрозуміти положення точки даних.

Під час створення діаграми розсіювання в Excel лінії сітки вмикаються за замовчуванням.

Ви можете відформатувати ці лінії сітки, клацнувши правою кнопкою миші на будь-якій із ліній сітки та натиснувши на опцію Форматувати лінії сітки.

Це відкриє панель «Форматувати лінії сітки» у правильному напрямку, де можна змінити форматування, наприклад колір, товщину лінії сітки.

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

Між двома основними лініями сітки ви можете мати кілька незначних ліній сітки, які ще більше покращують читаність діаграми, якщо у вас є багато точок даних.

Щоб додати незначні горизонтальні або вертикальні лінії сітки, виберіть діаграму, клацніть значок плюса та наведіть курсор на параметр Лінії сітки.

Натисніть на товсту чорну стрілку, яка з’явиться, а потім поставте прапорець біля опції «Первинна мала горизонталь» або «Основна мала вертикальна», щоб додати другорядні лінії сітки

Легенда

Якщо у діаграмі розсіювання в Excel є кілька серій, ви можете використовувати легенду, яка позначатиме, яка точка даних відноситься до якої серії.

За замовчуванням при створенні діаграми розсіювання в Excel немає легенди.

Щоб додати легенду до діаграми розсіювання, виберіть діаграму, натисніть значок плюса, а потім перевірте варіант легенди.

Щоб відформатувати легенду, клацніть правою кнопкою миші на легенді, яка з’явиться, а потім натисніть опцію «Форматувати легенду».

На панелі Формат легенди, що відкриється, можна налаштувати колір заповнення, рамку та положення легенди на діаграмі.

Лінія тренда

Ви також можете додати лінію тренда в діаграму розсіювання, яка б показала, чи є позитивна чи негативна кореляція у наборі даних.

Я вже розглянув, як додати лінію тренда до діаграми розсіювання в Excel в одному з розділів вище.

Діаграма 3D -розсіювання в Excel (краще уникати)

На відміну від лінійної діаграми, стовпчастої діаграми або діаграми областей, в Excel немає вбудованої діаграми тривимірного розсіювання.

Хоча ви можете використовувати для цього сторонні надбудови та інструменти, я не можу придумати жодної додаткової переваги, яку ви отримаєте від діаграми розсіювання 3D у порівнянні зі звичайною діаграмою 2D розсіювання.

Насправді, я рекомендую триматися подалі від будь -якого типу 3D -діаграми, оскільки вона потенційно може спотворити дані та частини на діаграмі.

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

Сподіваюся, цей підручник був вам корисним.