Як виконати регресійний аналіз в Excel

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

ПРОГНОЗ: Прогноз залежних змінних за допомогою лінії, яка найкраще підходить

Функція ПРОГНОЗ знаходить значення y точки на лінії, яка найкраще підходить, отриману за допомогою набору значень x і y, заданих значенням x. Функція використовує синтаксис

=ПРОГНОЗ(x,відомі_y,відомі_x)

де x — значення незалежної змінної, відомий_y — діапазон робочого аркуша, що містить залежні змінні, а відомий_x — діапазон робочого аркуша, що містить незалежні змінні.

Функція FORECAST використовує значення відомих_y та відомих_x, які ви надаєте як аргументи, щоб обчислити рівняння y=mx+b, яке описує найкращу пряму лінію для даних. Потім функція розв’язує це рівняння, використовуючи аргумент x, який ви надаєте функції.

Щоб використовувати функції лінійної регресії, такі як функція ПРОГНОЗ, пам’ятайте, що рівняння для лінії має вигляд y=mx+b . y – залежна змінна, b – це перетинання або константа, m – нахил, а x – значення незалежної змінної.

INTERCEPT: перетин лінії осі Y

Функція INTERCEPT знаходить точку, де найкраще підібрана лінія, створена набором значень x і y, перетинає вісь y. Функція використовує синтаксис

=INTERCEPT(відомі_y,відомі_x)

де відомий_y — це діапазон робочого аркуша, що містить залежні змінні, а відомий_x — діапазон робочого аркуша, що містить незалежні змінні.

Якщо ви коли-небудь малювали пари точок даних на графіку XY, то, як працює функція INTERCEPT, досить знайомий. Функція INTERCEPT використовує значення відомих_y та відомих_x, які ви надаєте як аргументи, щоб обчислити найбільш підходящу пряму лінію для даних — по суті, з’ясовуючи рівняння y=mx+b для лінії.

Потім функція повертає значення b, оскільки це значення рівняння, коли незалежна змінна , або x , дорівнює нулю.

LINEST

Функція LINEST знаходить значення m і b для рядка на основі наборів і змінних. Функція використовує синтаксис

=LINEST(відомі_y,[відомі_x],[const],[статистика])

де known_y дорівнює масиву значень y, які ви вже знаєте, known_x надає масив значень x, які ви, можливо, вже знаєте, const — це перемикач, встановлений або в TRUE (що означає, що константа b дорівнює 0) або в FALSE (що означає, що обчислюється константа b ), і є іншим перемикачем, встановленим або в TRUE (це означає, що функція повертає купу інших статистичних даних регресії) або FALSE (що означає , що вже достатньо ).

SLOPE: нахил лінії регресії

Функція SLOPE обчислює нахил лінії регресії за допомогою значень x та y. Функція використовує синтаксис

=SLOPE(відомі_y,відомі_x)

Нахил вгору вказує, що незалежна змінна , або x , позитивно впливає на залежну, або y , змінну. Іншими словами, збільшення x призводить до збільшення у . Нахил вниз вказує на те, що незалежна змінна , або x , негативно впливає на залежну, або y, змінну. Чим крутіший схил, тим більший вплив незалежної змінної на залежну змінну.

STEYX: Стандартна помилка

Функція STEYX знаходить стандартну помилку передбачуваного значення y кожного із значень x в регресії. Функція використовує синтаксис

=STEYX(відомі_y,відомі_x)

ТЕНДЕНЦІЯ

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

=TREND(відомі_y,[відомі_x],[нові_x],[постійні])

LOGEST: Експоненціальна регресія

Функція LOGEST повертає масив, який описує експоненціальну криву, яка найкраще відповідає вашим даним. Функція використовує синтаксис

=LOGEST(відомі_y,[відомі_x],[const],[статистика])

де відомий_y — це набір значень y, відомий_x — набір значень x, const — це перемикач, встановлений у TRUE (що означає, що b обчислюється нормально) або FALSE (що означає, що b змушений дорівнювати 1), і stats є перемикачем, який має значення TRUE (у цьому випадку функція LOGEST повертає додаткову статистику регресії) або FALSE (що вказує функції пропускати повернення додаткової інформації).

У експоненційній регресії Excel повертає рівняння, яке набуває вигляду y=abx, що найкраще відповідає вашому набору даних.

ЗРОСТАННЯ: Експоненційне зростання

Функція GROWTH обчислює експоненціальне зростання для серії нових значень x на основі наявних значень x і y. Функція використовує синтаксис

=РОСТ(відомі_y,[відомі_x],[нові_x],[постійні])

де відомі_у — це набір значень y, відомі_x — це набір значень x, це набір значень x, для яких потрібно обчислити нові значення y, а const — це перемикач, встановлений на TRUE (що означає, що b обчислюється нормально) або FALSE (що означає, що b змушений дорівнювати 1).