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

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

Часто вашим першим кроком у будь-якому регресійному аналізі є створення діаграми розсіювання, яка дає змогу візуально досліджувати зв’язок між двома наборами значень. У Excel це можна зробити за допомогою діаграми XY (точкова).

Наприклад, припустимо, що ви хочете подивитися або проаналізувати ці значення. Діапазон робочого аркуша A1:A11 показує кількість оголошень. Діапазон робочого аркуша B1:B11 показує результати продажів. За допомогою цих зібраних даних ви можете досліджувати вплив реклами на продажі або відсутність ефекту.

Щоб створити діаграму розсіювання цієї інформації, виконайте такі дії:

Виберіть діапазон робочого аркуша A1:B11.

На вкладці Вставка клацніть командну кнопку діаграми XY (Розкидна).

Виберіть підтип діаграми, який не містить жодних ліній.

Excel відображає ваші дані на діаграмі XY (розкид).

Підтвердьте організацію даних діаграми.

Переконайтеся, що Excel справді правильно впорядкував ваші дані, подивившись на діаграму.

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

Додайте анотації до діаграми, якщо це необхідно.

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

Додайте лінію тренду, натиснувши командну кнопку «Лінія тренда» в меню «Додати елемент діаграми».

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

Excel відображає меню Trendline. Виберіть потрібний тип розрахунку лінії тренду або регресії, клацнувши одну з доступних опцій лінії тренду. Наприклад, щоб виконати просту лінійну регресію, натисніть кнопку Лінійна.

У Excel 2007 ви додаєте лінію тренду, клацнувши команду «Лінія тренду» на вкладці «Інструменти макета».

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

Щоб показати рівняння для лінії тренду, яку використовує діаграма розсіювання, виберіть команду «Більше параметрів лінії тренду» з меню «Лінія тренда».

Потім установіть прапорці «Відображати рівняння на діаграмі» та «Відображати значення R-квадрат на діаграмі». Це повідомляє Excel, щоб додати до вашої діаграми інформацію про простий регресійний аналіз, необхідну для лінії тренду. Зауважте, що вам може знадобитися прокрутити панель вниз, щоб побачити ці прапорці.

У Excel 2007 та Excel 2010 ви натискаєте кнопку «Лінія тренда» на вкладці «Макет діаграми» та вибираєте параметр «Більше ліній тренда», щоб відобразити діалогове вікно «Формат лінії тренда».

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

Ви також можете використовувати текстові поля «Прогноз вперед» та «Назад», щоб вказати, що лінія тренду повинна бути розширена назад або вперед за межі наявних даних або перед ними.

Натисніть кнопку OK.

Ви майже не можете побачити дані регресії, тому їх було відформатовано, щоб зробити їх більш розбірливими.