Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

Нахил лінії лінійної регресії виражає найкраще передбачення залежної змінної (Y) по незалежним змінним (X).

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

Усього в Excel є два методи знаходження цієї змінної.

  • За допомогою функції НАКЛОН;
  • За допомогою діаграми.

У цій статті я продемонструю вам обидва ці способи.

Що таке нахил лінії лінійної регресії?

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

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

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

“Нахил” буває позитивний, або негативний.

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

Крім нахилу лінії, є ще цікаве, це функція ВІДРІЗОК.

Найпростіше пояснити так:

Y = НАКЛОН*X + ВІДРІЗОК

Минулого кроку ми розрахували нахил лінії, але щоб обчислити значення Y, нам потрібно знати ще й ВІДРІЗОК.

Я також продемонструю вам як обчислювати та ВІДРІЗОК.

За допомогою функції НАКЛОН

За допомогою функції НАКЛОН можна розрахувати нахил лінії, це стандартний спосіб.

Нам потрібно просто вказати значення незалежної та залежної змінних. Далі функція зробить все сама.

Складання формули для функції НАКЛОН

Формула функції виглядає так:

= НАКЛОН (Y; X)

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

Не забувайте, що першим аргументом функції є Y , а другим уже X. Якщо ви забудете про це, то отримаєте неправильний результат.

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

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

Саме для нашого прикладу, функція набуде наступного вигляду:

=НАКЛОН(B2:B11;A2:A11)

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

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

Розрахунок нахилу тісно пов'язаний із ще однією важливою величиною – ВІДРІЗКОМ.

Є рівняння:

Y = Нахил * X + Відрізок

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

Обчислити його ми можемо за допомогою спеціальної функції в Excel:

=ВІДРІЗОК(B2:B11;A2:A11)

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

Отже, рівняння, яке ми показували вище, тепер приймає таку форму:

Y = 1007,54 * X + 8744,537

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

Y = 1007,54 * 37 + 8744,537

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

Інструкція з використання функції НАКЛОН та ВІДРІЗОК

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

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

За допомогою діаграми

Якщо вам подобається використовувати діаграми, можна використовувати цю функцію побудови діаграми в Excel, щоб обчислити НАКЛОН і ВІДРІЗОК.

Припустимо, у нас є та сама табличка з даними, яку ми використовували в першому способі.

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

Щоб побудувати діаграму, слідуйте покроковому керівництву:

  • Виділіть діапазон осередків, які будуть залежними та незалежними змінними .
  • Натисніть на розділ “Вставка” в меню Excel.

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

  • Виберіть «Вставити діаграму».
  • І вкажіть, що вам потрібна точкова діаграма.

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

  • Тепер Excel збудує вам графік, де будуть відповідні значення X та Y.

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

  • Щоб отримати детальнішу інформацію, натисніть правою кнопкою мишки на будь-яку точку на графіку. І натисніть на функцію «Додати лінію тренда». Відкриється панель «Формат області побудови».

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

  • На цій панелі вам потрібно поставити галочку на параметрі “показувати рівняння на діаграмі”.

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

Тепер на цій діаграмі нам показує повну інформацію щодо нахилу лінії та відрізку.

Розрахунок нахилу лінії лінійної регресії за допомогою функції НАКЛОН та діаграми

На графіці, ви побачите таке саме рівняння, яке ми становили в минулому способі.

y = 1007,5 x + 8744,5

Ось:

  • 1007,5 – нахил лінії лінійної регресії
  • 8744,5 – відрізок лінії лінійної регресії

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

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

І до того ж, я думаю, цей спосіб буде набагато швидшим, ніж варіант за допомогою формул.

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

Сподіваюся, вам допомогло це керівництво!

nBook
Додати коментар