Як додати лінійну регресію до графіків Excel

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

Додавання лінії тренду лінійної регресії до графіка

1. Спочатку відкрийте порожню електронну таблицю Excel, виберіть клітинку D3 і введіть «Місяць» як заголовок стовпця, який буде змінною x.
2. Потім клацніть клітинку E3 і введіть «Значення Y» як заголовок стовпця змінної y. В основному це таблиця із записаними рядами значень даних за місяці січень-травень.
3. Введіть місяці в клітинки D4–D8 та значення даних для них у клітинки E4–E8, як показано на знімку безпосередньо нижче.

Тепер ви можете налаштувати графік розсіювання для цієї таблиці.

1. Виділіть всі клітинки в таблиці за допомогою курсора.
2. Натисніть вкладку Вставка та виберіть Розкидатися > Розкидайте лише маркерами щоб додати графік до електронної таблиці, як показано нижче. Крім того, ви можете натиснути комбінацію клавіш Alt + F1, щоб вставити стовпчасту діаграму.
3. Потім потрібно клацнути правою кнопкою миші на діаграмі та вибрати Змінити тип діаграми >X Y (розкид) >Розкидайте лише маркерами.

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

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

3. Натисніть Параметри лінії тренда і виберіть звідти тип регресії. Ви можете вибрати Експоненціальна, Лінійний, Логарифмічний, Ковзне середнє, Потужність і Поліном варіанти типу регресії звідти.

4. Виберіть Лінійний і натисніть Закрити щоб додати цю лінію тренду до графіка, як показано безпосередньо нижче.

Лінія тренда лінійної регресії на графіку вище підкреслює, що існує загальний висхідний зв’язок між змінними x і y, незважаючи на кілька падінь на графіку. Зауважте, що лінія тренду лінійної регресії не перекриває жодну з точок даних на діаграмі, тому вона не те саме, що ваш середній лінійний графік, який з’єднує кожну точку.

Форматування лінії тренду лінійної регресії

Форматування лінії тренду лінійної регресії є важливим інструментом для створення розбірливих і чітких графіків у Excel.

1. Щоб почати форматування лінії тренду, клацніть її правою кнопкою миші та виберіть Формат лінії тренда.
2. Після цього знову відкриється вікно Format Trendline, з якого можна клацнути Колір лінії.
3. Виберіть Суцільна лінія і натисніть кнопку Колір , щоб відкрити палітру, з якої можна вибрати альтернативний колір для лінії тренду.
4. Щоб налаштувати стиль лінії, натисніть вкладку Стиль лінії. Потім ви можете налаштувати ширину стрілки та налаштувати параметри стрілки.
5. Натисніть кнопку Налаштування стрілки кнопки, щоб додати стрілки до рядка.

Ви також можете додати ефекти до лінії тренду в естетичних цілях

1. Додайте ефект світіння до лінії тренду, клацнувши Світіння і М'які краї. Це відкриє вкладку нижче, на якій ви можете додати світіння, натиснувши кнопку Попередні налаштування кнопку.
2. Потім виберіть варіант світіння, щоб вибрати ефект. Натисніть Колір щоб вибрати альтернативні кольори для ефекту, і ви можете перетягнути Розмір і Прозорість смуги для подальшого налаштування світіння лінії тренду.

Прогнозування значень за допомогою лінійної регресії

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

1. натисніть «Параметри лінії тренду» та введіть «3» у текстовому полі «Вперед».
2. Лінія тренду лінійної регресії підкреслює, що значення серпня, ймовірно, буде трохи вище 3500, як показано нижче.

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

1. Натисніть кнопку Відобразити рівняння на діаграмі поставте прапорець, щоб додати рівняння до графіка. Це рівняння включає значення нахилу та перерізу.
2. Щоб додати до графіка значення квадрата r, натисніть кнопку Відобразити значення R-квадрат на діаграмі прапорець. Це додає r у квадраті до графіка трохи нижче рівняння, як на знімку нижче.
3. Перетягніть поле рівняння та кореляції, щоб змінити його положення на діаграмі розсіювання.

Функції лінійної регресії

Excel також містить функції лінійної регресії, за допомогою яких ви можете знайти значення нахилу, перехоплення та квадратних значень r для масивів даних y та x.

1. Виберіть клітинку електронної таблиці, до якої потрібно додати одну з цих функцій, а потім натисніть кнопку Функція вставки кнопку. Функції лінійної регресії статистичні, тому виберіть Статистичний зі спадного меню категорії.
2. Тоді можна вибрати RSQ, СХІЛ або ПЕРЕРЕХВАННЯ щоб відкрити вікна функцій, як показано нижче.

Вікна RSQ, SLOPE та INTERCEPT майже однакові. Вони включають поля Known_y і Known_x, які ви можете вибрати, щоб додати значення змінних y і x зі своєї таблиці. Зверніть увагу, що клітинки мають містити лише числа, тому замініть місяці в таблиці відповідними цифрами, наприклад 1 для січня, 2 для лютого тощо. Натисніть в порядку щоб закрити вікно та додати функцію до електронної таблиці.

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

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