Припущення регресійного аналізу. Регресія в Excel: рівняння, приклади. Лінійна регресія. Розбір результатів аналізу
ВИСНОВОК ПІДСУМКІВ
| регресійна статистика | |
| множинний R | 0,998364 |
| R-квадрат | 0,99673 |
| Нормований R-квадрат | 0,996321 |
| стандартна помилка | 0,42405 |
| спостереження | 10 |
Спочатку розглянемо верхню частину розрахунків, представлену в таблиці 8.3А, - регресійну статистику.
Величина R-квадрат, звана також мірою визначеності, характеризує якість отриманої регресійної прямої. Це якість виражається ступенем відповідності між вихідними даними і регресійній моделі (розрахунковими даними). Міра визначеності завжди знаходиться в межах інтервалу.
У більшості випадків значення R-квадрат знаходиться між цими значеннями, званими екстремальними, тобто між нулем і одиницею.
Якщо значення R-квадрата близько до одиниці, це означає, що побудована модель пояснює майже всю мінливість відповідних змінних. І навпаки, значення R-квадрата, близьке до нуля, означає погана якість побудованої моделі.
У нашому прикладі міра визначеності дорівнює 0,99673, що говорить про дуже хорошій підгонці регресійної прямої до вихідних даних.
множинний R - коефіцієнт множинної кореляції R - висловлює ступінь залежності незалежних змінних (X) і залежною змінною (Y).
Множинний R дорівнює квадратному кореню з коефіцієнта детермінації, ця величина приймає значення в інтервалі від нуля до одиниці.
У простому лінійному регресійному аналізі множинний R рівний коефіцієнту кореляції Пірсона. Дійсно, множинний R в нашому випадку рівний коефіцієнту кореляції Пірсона з попереднього прикладу (0,998364).
| коефіцієнти | стандартна помилка | t-статистика | |
| Y-перетин | 2,694545455 | 0,33176878 | 8,121757129 |
| Мінлива X 1 | 2,305454545 | 0,04668634 | 49,38177965 |
| * Наведено усічений варіант розрахунків | |||
Тепер розглянемо середню частину розрахунків, представлену в таблиці 8.3б. Тут дані коефіцієнт регресії b (2,305454545) і зміщення по осі ординат, тобто константа a (2,694545455).
Виходячи з розрахунків, можемо записати рівняння регресії таким чином:
Y \u003d x * 2,305454545 + 2,694545455
Напрямок зв'язку між змінними визначається на підставі знаків (негативний або позитивний) коефіцієнтів регресії (Коефіцієнта b).
Якщо знак при коефіцієнті регресії - позитивний, зв'язок залежної змінної з незалежної буде позитивною. У нашому випадку знак коефіцієнта регресії позитивний, отже, зв'язок також є позитивною.
Якщо знак при коефіцієнті регресії - негативний, зв'язок залежної змінної з незалежної є негативною (зворотного).
У таблиці 8.3в. представлені результати виведення залишків. Для того щоб ці результати з'явилися в звіті, необхідно при запуску інструменту "Регресія" активувати чекбокс "Залишки".
ВИСНОВОК ЗАЛИШКУ
| спостереження | передбачене Y | залишки | стандартні залишки |
|---|---|---|---|
| 1 | 9,610909091 | -0,610909091 | -1,528044662 |
| 2 | 7,305454545 | -0,305454545 | -0,764022331 |
| 3 | 11,91636364 | 0,083636364 | 0,209196591 |
| 4 | 14,22181818 | 0,778181818 | 1,946437843 |
| 5 | 16,52727273 | 0,472727273 | 1,182415512 |
| 6 | 18,83272727 | 0,167272727 | 0,418393181 |
| 7 | 21,13818182 | -0,138181818 | -0,34562915 |
| 8 | 23,44363636 | -0,043636364 | -0,109146047 |
| 9 | 25,74909091 | -0,149090909 | -0,372915662 |
| 10 | 28,05454545 | -0,254545455 | -0,636685276 |
За допомогою цієї частини звіту ми можемо бачити відхилення кожної точки від побудованої лінії регресії. Найбільше абсолютне значення
регресійний аналіз метод моделювання вимірюваних даних і дослідження їх властивостей. Дані складаються з пар значень залежною змінною (Змінної відгуку) і незалежної змінної (Що пояснює змінної). Регресійна модель є функція незалежної змінної і параметрів з доданою випадкової змінної. Параметри моделі налаштовуються таким чином, що модель найкращим чином наближає дані. Критерієм якості наближення (цільовою функцією) зазвичай є середньоквадратична помилка: сума квадратів різниці значень моделі і залежною змінною для всіх значень незалежної змінної як аргумент. Регресійний аналіз розділ математичної статистики і машинного навчання. Передбачається, що залежна змінна є сума значень деякої моделі і випадкової величини. Щодо характеру розподілу цієї величини робляться припущення, звані гіпотезою породження даних. Для підтвердження або спростування цієї гіпотези виконуються статистичні тести, звані аналізом залишків. При цьому передбачається, що незалежна змінна не містить помилок. Регресійний аналіз використовується для прогнозу, аналізу часових рядів, тестування гіпотез і виявлення прихованих взаємозв'язків в даних.
Визначення регресійного аналізу
Вибірка може бути не функцією, а ставленням. Наприклад, дані для побудови регресії можуть бути такими:. У такій вибірці одному значенню змінної відповідає кілька значень змінної.
лінійна регресія
Лінійна регресія передбачає, що функція залежить від параметрів лінійно. При цьому лінійна залежність від вільної змінної необов'язкова,
У разі, коли функція лінійна регресія має вигляд
тут компоненти вектора.
Значення параметрів в разі лінійної регресії знаходять за допомогою методу найменших квадратів. Використання цього методу обгрунтовано припущенням про гауссовский розподілі випадкової змінної.
Різниці між фактичними значеннями залежної змінної і відновленими називаються регресійний залишками (Residuals). У літературі використовуються також синоніми: невязки і помилки. Однією з важливих оцінок критерію якості отриманої залежності є сума квадратів залишків:
Тут Sum of Squared Errors.
Дисперсія залишків обчислюється за формулою
Тут Mean Square Error, середньоквадратична помилка.
|
|
|
На графіках представлені вибірки, позначені синіми крапками, і регресивні залежності, позначені суцільними лініями. По осі абсцис відкладена вільна змінна, а по осі ординат залежна. Всі три залежності лінійні щодо параметрів.
нелінійна регресія
Нелінійні регресійні моделі - моделі виду
які не можуть бути представлені у вигляді скалярного твори
де - параметри регресійної моделі, - вільна змінна з простору, - залежна змінна, - випадкова величина і - функція з деякого заданого безлічі.
Значення параметрів в разі нелінійної регресії знаходять за допомогою одного з методів градієнтного спуску, наприклад алгоритму Левенберга-Марквардта.
Про термінах
Термін "регресія" був введений Френсісом Гальтон в кінці 19-го століття. Гальтон виявив, що діти батьків з високим або низьким ростом зазвичай не успадковують видатний зростання і назвав цей феномен "регресія до посередності". Спочатку цей термін використовувався виключно в біологічному сенсі. Після робіт Карла Пірсона цей термін стали використовувати і в статистиці.
У статистичній літературі розрізняють регресію за участю однієї вільної змінної і з декількома вільними змінними одновимірну і багатовимірну регресію. Передбачається, що ми використовуємо кілька вільних змінних, тобто, вільна змінна вектор. В окремих випадках, коли вільна змінна є скаляром, вона буде позначатися. розрізняють лінійну і нелінійну регресію. Якщо регресійну модель не є лінійною комбінацією функцій від параметрів, то говорять про нелінійної регресії. При цьому модель може бути довільною суперпозицией функцій з деякого набору. Нелінійними моделями є, експоненціальні, тригонометричні та інші (наприклад, радіальні базисні функції або персептрон Розенблатта), які вважають залежність між параметрами і залежною змінною нелінійної.
розрізняють параметричну і непараметричних регресію. Сувору межу між цими двома типами регрессий провести складно. Зараз не існує загальноприйнятого критерію відмінності одного типу моделей від іншого. Наприклад, вважається, що лінійні моделі є параметричними, а моделі, що включають усереднення залежною змінною по простору вільної змінної непараметричних. Приклад параметричної регресіонний моделі: лінійний предиктор, багатошаровий персептрон. Приклади змішаної регресійній моделі: функції радіального базису. Непараметрична модель ковзне усереднення у вікні деякої ширини. В цілому, непараметрическая регресія відрізняється від параметричної тим, що залежна змінна залежить не від одного значення вільної змінної, а від деякої заданої околиці цього значення.
Є відмінність між термінами: "наближення функцій", "апроксимація", "інтерполяція", і "регресія". Воно полягає в наступному.
Наближення функцій. Дана функція дискретного або безперервного аргументу. Потрібно знайти функцію з деякого параметричну сімейства, наприклад, серед алгебраїчних поліномів заданого ступеня. Параметри функції повинні доставляти мінімум деякого функціоналу, наприклад,
термін апроксимація синонім терміну "наближення функцій". Найчастіше використовується тоді, коли мова йде про заданої функції, як про функції дискретного аргументу. Тут також потрібно відшукати таку функцію, яка проходить найближче до всіх точок заданої функції. При цьому вводиться поняття невязки відстані між точками неперервної функції і відповідними точками функції дискретного аргументу.
інтерполяція функцій окремий випадок завдання наближення, коли потрібно, щоб в певних точках, званих вузлами інтерполяції збігалися значення функції і наближає її функції. У більш загальному випадку накладаються обмеження на значення деяких похідних похідних. Тобто, дана функція дискретного аргументу. Потрібно знайти таку функцію, яка проходить через всі крапки. При цьому метрика зазвичай не використовується, проте часто вводиться поняття "гладкості" шуканої функції.
Метою регресійного аналізу є вимірювання зв'язку між залежною змінною і однієї (парний регресійний аналіз) або декількома (множинний) незалежними змінними. Незалежні змінні називають також факторними, що пояснюють, що визначають, регресорів і предикторами.
Залежну змінну іноді називають визначається, що пояснюється, «відгуком». Надзвичайно широке поширення регресійного аналізу в емпіричних дослідженнях пов'язано не тільки з тим, що це зручний інструмент тестування гіпотез. Регресія, особливо множинна, є ефективним методом моделювання і прогнозування.
Пояснення принципів роботи з регресійним аналізом почнемо з простішого - парного методу.
Парний регресійний аналіз
Перші дії при використанні регресійного аналізу будуть практично ідентичні вжитим нами в рамках обчислення коефіцієнта кореляції. Три основні умови ефективності кореляційного аналізу за методом Пірсона - нормальний розподіл змінних, інтервальний вимір змінних, лінійна зв'язок між змінними - актуальні і для множинної регресії. Відповідно, на першому етапі будуються діаграми розсіювання, проводиться статистично-описовий аналіз змінних і обчислюється лінія регресії. Як і в рамках кореляційного аналізу, лінії регресії будуються методом найменших квадратів.
Щоб більш наочно проілюструвати відмінності між двома методами аналізу даних, звернемося до вже розглянутого прикладу зі змінними «підтримка СПС» і «частка сільського населення». Вихідні дані ідентичні. Відмінність в діаграмах розсіювання буде полягати в тому, що в регресійному аналізі коректно відкладати залежну змінну - в нашому випадку «підтримка СПС» по осі Y, тоді як в кореляційному аналізі це не має значення. Після чищення викидів діаграма розсіювання має вигляд:
Принципова ідея регресійного аналізу полягає в тому, що, маючи загальну тенденцію для змінних - у вигляді лінії регресії, - можна передбачити значення залежної змінної, маючи значення незалежної.
Уявімо звичайну математичну лінійну функцію. Будь-яку пряму в евклідовому просторі можна описати формулою:
де а - константа, що задає зміщення по осі ординат; b - коефіцієнт, що визначає кут нахилу лінії.
Знаючи кутовий коефіцієнт і константу, можна розрахувати (передбачити) значення у для будь-якого х.
Ця найпростіша функція і лягла в основу моделі регресійного аналізу з тим застереженням, що значення у ми передбачу не точно, а в рамках певного довірчого інтервалу, тобто приблизно.
Константою є точка перетину лінії регресії і осі ординат (F-перетин, в статистичних пакетах, як правило, позначається «interceptor»). У нашому прикладі з голосуванням за СПС її округлене значення складе 10,55. Кутовий коефіцієнт видання буде дорівнює приблизно -0,1 (як і в кореляційному аналізі, знак показує тип зв'язку - пряма або зворотна). Таким чином, отримана модель буде мати вигляд СП С \u003d -0,1 х Сів. нас. + 10,55.
Так, для випадку «Республіка Адигея» з часткою сільського населення 47% передбачене значення складе 5,63:
СПС \u003d -0,10 х 47 + 10,55 \u003d 5,63.
Різниця між вихідним і передбаченим значеннями називається залишком (з цим терміном - принциповим для статистики - ми вже стикалися при аналізі таблиць спряженості). Так, для випадку «Республіка Адигея» залишок буде дорівнює 3,92 - 5,63 \u003d -1,71. Чим більше модульне значення залишку, тим менш вдало передбачене значення.
Розраховуємо передбачені значення і залишки для всіх випадків:
|
Аналіз співвідношення вихідних і передбачених значень служить для оцінки якості отриманої моделі, її прогностичної здатності. Одним з головних показників регресійної статистики є множинний коефіцієнт кореляції R - коефіцієнт кореляції між вихідними і передбаченими значеннями залежної змінної. У парному регресійному аналізі він дорівнює звичайному коефіцієнту кореляції Пірсона між залежною і незалежною змінною, в нашому випадку - 0,63. Щоб змістовно інтерпретувати множинний R, його необхідно перетворити в коефіцієнт детермінації. Це робиться так само, як і в кореляційному аналізі - зведенням в квадрат. Коефіцієнт детермінації R -квадрат (R 2) показує частку варіації залежної змінної, що пояснюється незалежної (незалежними) змінними.
У нашому випадку R 2 \u003d 0,39 (0,63 2); це означає, що змінна «частка сільського населення» пояснює приблизно 40% варіації змінної «підтримка СПС». Чим більше величина коефіцієнта детермінації, тим вище якість моделі.
Іншим показником якості моделі є стандартна похибка оцінки (standard error of estimate). Це показник того, наскільки сильно точки «розкидані» навколо лінії регресії. Мірою розкиду для інтервальних змінних є стандартне відхилення. Відповідно, стандартна помилка оцінки - це стандартне відхилення розподілу залишків. Чим вище його значення, тим сильніше розкид і тим гірше модель. У нашому випадку стандартна помилка становить 2,18. Саме на цю величину наша модель буде «помилятися в середньому» при прогнозуванні значення змінної «підтримка СПС».
Регресійна статистика включає в себе також дисперсійний аналіз. З його допомогою ми з'ясовуємо: 1) яка частка варіації (дисперсії) залежною змінною пояснюється незалежної змінної; 2) яка частка дисперсії залежної змінної доводиться на залишки (непояснена частина); 3) як ставиться цих двох величин (/ "- відношення). Дисперсійна статистика особливо важлива для вибіркових досліджень - вона показує, наскільки ймовірна наявність зв'язку між незалежною і залежною змінними в генеральної сукупності. Однак і для суцільних досліджень (як в нашому прикладі) вивчення результатів дисперсійного аналізу корисно. у цьому випадку перевіряють, чи не викликана виявлена \u200b\u200bстатистична закономірність збігом випадкових обставин, наскільки вона характерна для того комплексу умов, в яких знаходиться обследуемая сукупність, тобто встановлюється істинність отриманого результату для якоїсь більш широкої генеральної сукупності, а ступінь його закономірності, свободи від випадкових впливів.
У нашому випадку статистика дисперсійного аналізу така:
| SS | df | MS | F | значення | |
| Регрес. | 258,77 | 1,00 | 258,77 | 54,29 | 0.000000001 |
| Остат. | 395,59 | 83,00 | Л, 11 | ||
| всього | 654,36 |
F-відношення 54,29 значимо на рівні 0,0000000001. Відповідно, ми можемо з упевненістю відкинути нульову гіпотезу (що виявлена \u200b\u200bнами зв'язок носить випадковий характер).
Аналогічну функцію виконує критерій t, але вже щодо регресійних коефіцієнтів (кутового і F-перетину). За допомогою критерію / перевіряємо гіпотезу про те, що в генеральній сукупності регресивні коефіцієнти дорівнюють нулю. У нашому випадку ми знову можемо впевнено відкинути нульову гіпотезу.
Множинний регресійний аналіз
Модель множинної регресії практично ідентична моделі парної регресії; різниця лише в тому, що в лінійну функцію послідовно включаються кілька незалежних змінних:
Y \u003d b1X1 + b2X2 + ... + bpXp + а.
Якщо незалежних змінних більше двох, ми не маємо можливості отримати візуальне уявлення про їх зв'язок, в цьому плані множинна регресія менш «наочна», ніж парна. При наявності двох незалежних змінних дані буває корисно відобразити на тривимірній діаграмі розсіювання. У професійних статистичних пакетах програм (наприклад, Statisticа) існує опція обертання тривимірної діаграми, що дозволяє добре візуально уявити структуру даних.
При роботі з множинною регресією, на відміну від парної, необхідно визначати алгоритм аналізу. Стандартний алгоритм включає в підсумкову регресійну модель все наявні предиктори. Покроковий алгоритм передбачає послідовне включення (виключення) незалежних змінних, виходячи з їх пояснювального «ваги». Покроковий метод хороший, коли є багато незалежних змінних; він «очищає» модель від відверто слабких предикторов, роблячи її більш компактною і лаконічною.
Додатковою умовою коректності множинної регресії (поряд з інтервальних, нормальністю і лінійністю) є відсутність мультиколінеарності - наявності сильних кореляційних зв'язків між незалежними змінними.
Інтерпретація статистики множинної регресії включає в себе всі злементи, розглянуті нами для випадку парної регресії. Крім того, в статистиці множинного регресійного аналізу є й інші важливі складові.
Роботу з множинною регресією ми проілюструємо на прикладі тестування гіпотез, що пояснюють відмінності в рівні електоральної активності по регіонах Росії. В ході конкретних емпіричних досліджень були висловлені припущення, що на рівень явки виборців впливають:
Національний фактор (змінна «російське населення»; операціоналізіровать як частка російського населення в суб'єктах РФ). Передбачається, що збільшення частки російського населення веде до зниження активності виборців;
Фактор урбанізації (змінна «міське населення»; операціоналізіровать як частка міського населення в суб'єктах РФ, з цим фактором ми вже працювали в рамках кореляційного аналізу). Передбачається, що збільшення частки міського населення також веде до зниження активності виборців.
Залежна змінна - «інтенсивність виборчої активності» ( «актив») операціоналізіровать через усереднені дані явки по регіонах на федеральних виборах з 1995 по 2003 р Вихідна таблиця даних для двох незалежних і однієї залежної змінної матиме такий вигляд:
| випадок | змінні | ||
| Актив. | Гор. нас. | Рус. нас. | |
| Республіка Адигея | 64,92 | 53 | 68 |
| Республіка Алтай | 68,60 | 24 | 60 |
| Республіка Бурятія | 60,75 | 59 | 70 |
| республіка Дагестан | 79,92 | 41 | 9 |
| Республіка Інгушетія | 75,05 | 41 | 23 |
| Республіка Калмикія | 68,52 | 39 | 37 |
| Карачаєво-Черкеська Республіка | 66,68 | 44 | 42 |
| республіка Карелія | 61,70 | 73 | 73 |
| Республіка Комі | 59,60 | 74 | 57 |
| Республіка Марій Ел | 65,19 | 62 | 47 |
І т.д. (Після чистки викидів залишається 83 випадки з 88)
Статистика, яка описувала якість моделі:
1. Множинний R \u003d 0,62; Л-квадрат \u003d 0,38. Отже, національний фактор і фактор урбанізації разом пояснюють близько 38% варіації змінної «електоральна активність».
2. Середня помилка становить 3,38. Саме настільки «в середньому помиляється» побудована модель при прогнозуванні рівня явки.
3. / л-відношення пояснене і непоясненим варіації становить 25,2 на рівні 0,000000003. Нульова гіпотеза про випадковості виявлених зв'язків відкидається.
4. Критерій / для константи і регресійних коефіцієнтів змінних «міське населення» і «російське населення» значущий на рівні 0,0000001; 0,00005 і 0,007 відповідно. Нульова гіпотеза про випадковості коефіцієнтів відкидається.
Додаткова корисна статистика в аналізі співвідношення вихідних і передбачених значень залежної змінної - відстань Махаланобіса і відстань Кука. Перше - міра унікальності випадку (показує, наскільки поєднання значень всіх незалежних змінних для даного випадку відхиляється від середнього значення по всім незалежним змінним одночасно). Друге - міра впливовості випадку. Різні спостереження по-різному впливають на нахил лінії регресії, і за допомогою відстані Кука можна зіставляти їх за цим показником. Це буває корисно при чищенні викидів (викид можна уявити як надмірно впливовий випадок).
У нашому прикладі до унікальних і впливовим випадків, зокрема, відноситься Дагестан.
| випадок | вихідні значення | предска значення | залишки | відстань Махаланобіса | відстань |
| Адигея | 64,92 | 66,33 | -1,40 | 0,69 | 0,00 |
| Республіка Алтай | 68,60 | 69.91 | -1,31 | 6,80 | 0,01 |
| Республіка Бурятія | 60,75 | 65,56 | -4,81 | 0,23 | 0,01 |
| республіка Дагестан | 79,92 | 71,01 | 8,91 | 10,57 | 0,44 |
| Республіка Інгушетія | 75,05 | 70,21 | 4,84 | 6,73 | 0,08 |
| Республіка Калмикія | 68,52 | 69,59 | -1,07 | 4,20 | 0,00 |
Власне регресійна модель має наступні параметрами: У-перетин (константа) \u003d 75,99; Ь (Гор. Нас.) \u003d -0,1; Комерсант (рос. Нас.) \u003d -0,06. Підсумкова формула:
Аактів, \u003d -0,1 х Гор. нас.n + - 0,06 х Рус. нас.n + 75,99.
Чи можемо ми порівнювати «пояснювальну силу» предикторов, виходячи із значення коефіцієнта 61. В даному випадку - так, так як обидві незалежні змінні мають однаковий процентний формат. Однак найчастіше множинна регресія має справу зі змінними, вимірюваними в різних шкалах (наприклад, рівень доходу в рублях і вік в роках). Тому в загальному випадку порівнювати Предсказательная можливості змінних по регрессионному коефіцієнту некоректно. У статистиці множинної регресії для цієї мети існує спеціальний бета-коефіцієнт (В), який вираховується окремо для кожної незалежної змінної. Він являє собою приватний (обчислений після врахування впливу всіх інших предикторів) коефіцієнт кореляції фактора і відгуку і показує незалежний внесок фактора в пророкування значень відгуку. У парному регресійному аналізі бета-коефіцієнт зі зрозумілих причин рівний коефіцієнту парної кореляції між залежною і незалежною змінною.
У нашому прикладі бета (Гор. Нас.) \u003d -0,43, бета (рос. Нас.) \u003d -0,28. Таким чином, обидва чинники негативно впливають на рівень електоральної активності, при цьому значимість фактора урбанізації істотно вище значущості національного чинника. Сукупний вплив обох факторів визначає близько 38% варіації змінної «електоральна активність» (див. Значення Л-квадрат).
Регресійний аналіз - це статистичний метод дослідження, що дозволяє показати залежність того чи іншого параметра від однієї або декількох незалежних змінних. У докомпьютерную еру його застосування було досить важко, особливо якщо мова йшла про великі обсяги даних. Сьогодні, дізнавшись як побудувати регресію в Excel, можна вирішувати складні статистичні завдання буквально за пару хвилин. Нижче представлені конкретні приклади з області економіки.
види регресії
Саме це поняття було введено в математику в 1886 році. Регресія буває:
- лінійної;
- параболічної;
- статечної;
- експоненційної;
- гіперболічної;
- показовою;
- логарифмічною.
приклад 1
Розглянемо задачу визначення залежності кількості звільнених членів колективу від середньої зарплати на 6 промислових підприємствах.
Завдання. На шести підприємствах проаналізували середньомісячну заробітну плату і кількість співробітників, які звільнилися за власним бажанням. У табличній формі маємо:
кількість звільнених | Зарплата |
||
30000 рублів |
|||
35000 рублів |
|||
40000 рублів |
|||
45000 рублів |
|||
50000 рублів |
|||
55000 рублів |
|||
60000 рублів |
Для завдання визначення залежності кількості звільнених працівників від середньої зарплати на 6 підприємствах модель регресії має вигляд рівняння Y \u003d а 0 + а 1 x 1 + ... + а k x k, де х i - впливають змінні, a i - коефіцієнти регресії, a k - число факторів.
Для даної задачі Y - це показник звільнених співробітників, а фактор, що впливає - зарплата, яку позначаємо X.
Використання можливостей табличного процесора «Ексель»
Аналізу регресії в Excel має передувати застосування до наявних табличних даних вбудованих функцій. Однак для цих цілей краще скористатися дуже корисною надбудовою «Пакет аналізу». Для його активації потрібно:
- з вкладки «Файл» перейти в розділ «Параметри»;
- у вікні, вибрати рядок «компонентами»;
- клацнути по кнопці «Перейти», розташованої внизу, праворуч від рядка «Управління»;
- поставити галочку поруч з назвою «Пакет аналізу» і підтвердити свої дії, натиснувши «Ок».
Якщо все зроблено правильно, в правій частині вкладки «Дані», розташованому над робочим листом «Ексель», з'явиться потрібна кнопка.
в Excel
Тепер, коли під рукою є всі необхідні віртуальні інструменти для здійснення економетричних розрахунків, можемо приступити до вирішення нашої задачі. Для цього:
- клацаємо по кнопці «Аналіз даних»;
- у вікні, натискаємо на кнопку «Регресія»;
- в що з'явилася вкладку вводимо діапазон значень для Y (кількість звільнених працівників) і для X (їх зарплати);
- підтверджуємо свої дії натисненням кнопки «Ok».
В результаті програма автоматично заповнить новий лист табличного процесора даними аналізу регресії. Зверніть увагу! В Excel є можливість самостійно задати місце, яке ви віддаєте перевагу для цієї мети. Наприклад, це може бути той же лист, де знаходяться значення Y і X, або навіть нова книга, спеціально призначена для зберігання подібних даних.
Аналіз результатів регресії для R-квадрата
В Excel дані отримані в ході обробки даних розглянутого прикладу мають вигляд:
Перш за все, слід звернути увагу на значення R-квадрата. Він являє собою коефіцієнт детермінації. В даному прикладі R-квадрат \u003d 0,755 (75,5%), т. Е. Розрахункові параметри моделі пояснюють залежність між розглянутими параметрами на 75,5%. Чим вище значення коефіцієнта детермінації, тим обрана модель вважається більш прийнятною для конкретного завдання. Вважається, що вона коректно описує реальну ситуацію при значенні R-квадрата вище 0,8. Якщо R-квадрата<0,5, то такой анализа регрессии в Excel нельзя считать резонным.
аналіз коефіцієнтів
Число 64,1428 показує, яким буде значення Y, якщо всі змінні xi в розглянутій нами моделі обнуляться. Іншими словами можна стверджувати, що на значення аналізованого параметра впливають і інші фактори, які не описані в конкретній моделі.
Наступний коефіцієнт -0,16285, розташований в осередку B18, показує вагомість впливу змінної Х на Y. Це означає, що середньомісячна зарплата співробітників в межах розглянутої моделі впливає на число звільнених з вагою -0,16285, т. Е. Ступінь її впливу зовсім невелика. Знак «-» вказує на те, що коефіцієнт має негативне значення. Це очевидно, тому що всім відомо, що чим більше зарплата на підприємстві, тим менше людей виявляють бажання розірвати трудовий договір або звільняється.
множинна регресія
Під таким терміном розуміється рівняння зв'язку з декількома незалежними змінними виду:
y \u003d f (x 1 + x 2 + ... x m) + ε, де y - це результативний ознака (залежна змінна), а x 1, x 2, ... x m - це ознаки-фактори (незалежні змінні).
оцінка параметрів
Для множинної регресії (МР) її здійснюють, використовуючи метод найменших квадратів (МНК). Для лінійних рівнянь виду Y \u003d a + b 1 x 1 + ... + b m x m + ε будуємо систему нормальних рівнянь (див. Нижче)
Щоб зрозуміти принцип методу, розглянемо двохфакторну випадок. Тоді маємо ситуацію, описану формулою
Звідси отримуємо:
де σ - це дисперсія відповідної ознаки, відбитого в індексі.
МНК застосуємо до рівняння МР в стандартизовані масштабі. В такому випадку отримуємо рівняння:
в якому t y, t x 1, ... t xm - стандартизовані змінні, для яких середні значення рівні 0; β i - стандартизовані коефіцієнти регресії, а середньоквадратичне відхилення - 1.
Зверніть увагу, що всі β i в даному випадку задані, як нормовані і централізіруемие, тому їх порівняння між собою вважається коректним і допустимим. Крім того, прийнято здійснювати відсів факторів, відкидаючи ті з них, у яких найменші значення βi.
Завдання з використанням рівняння лінійної регресії
Припустимо, є таблиця динаміки ціни конкретного товару N протягом останніх 8 місяців. Необхідно прийняти рішення про доцільність придбання його партії за ціною 1850 руб. / Т.
номер місяця | назва місяця | ціна товару N |
|
1750 рублів за тонну |
|||
1755 рублів за тонну |
|||
1767 рублів за тонну |
|||
1760 рублів за тонну |
|||
1770 рублів за тонну |
|||
1790 рублів за тонну |
|||
1810 рублів за тонну |
|||
1840 рублів за тонну |
|||
Для вирішення цього завдання в табличному процесорі «Ексель» потрібно задіяти вже відомий по представленому вище прикладу інструмент «Аналіз даних». Далі вибирають розділ «Регресія» і задають параметри. Потрібно пам'ятати, що в поле «Вхідний інтервал Y» повинен вводитися діапазон значень для залежної змінної (в даному випадку ціни на товар в конкретні місяці року), а в «Вхідний інтервал X» - для незалежної (номер місяця). Підтверджуємо дії натисненням «Ok». На новому аркуші (якщо так було зазначено) отримуємо дані для регресії.
Будуємо по ним лінійне рівняння виду y \u003d ax + b, де в якості параметрів a і b виступають коефіцієнти рядка з найменуванням номера місяця і коефіцієнти і рядки «Y-перетин» з листа з результатами регресійного аналізу. Таким чином, лінійне рівняння регресії (УР) для завдання 3 записується у вигляді:
Ціна на товар N \u003d 11,714 * номер місяця + 1727,54.
або в алгебраїчних позначеннях
y \u003d 11,714 x + 1727,54
Аналіз результатів
Щоб вирішити, чи адекватно отримане рівняння лінійної регресії, використовуються коефіцієнти множинної кореляції (КМК) і детермінації, а також критерій Фішера і критерій Стьюдента. У таблиці «Ексель» з результатами регресії вони виступають під назвами множинний R, R-квадрат, F-статистика і t-статистика відповідно.
КМК R дає можливість оцінити тісноту ймовірнісної зв'язку між незалежною і залежною змінними. Її високе значення свідчить про досить сильного зв'язку між змінними «Номер місяця» і «Ціна товару N в рублях за 1 тонну». Однак, характер зв'язку з цим залишається невідомим.
Квадрат коефіцієнта детермінації R 2 (RI) являє собою числову характеристику частки загального розкиду і показує, розкид якій частині експериментальних даних, тобто значень залежної змінної відповідає рівняння лінійної регресії. У розглянутій задачі ця величина дорівнює 84,8%, т. Е. Статистичні дані з високим ступенем точності описуються отриманим УР.
F-статистика, звана також критерієм Фішера, використовується для оцінки значущості лінійної залежності, спростовуючи або підтверджуючи гіпотезу про її існування.
(Критерій Стьюдента) допомагає оцінювати значимість коефіцієнта при невідомій або вільного члена лінійної залежності. Якщо значення t-критерію\u003e t кр, то гіпотеза про незначущості вільного члена лінійного рівняння відкидається.
У розглянутій задачі для вільного члена за допомогою інструментів «Ексель» було отримано, що t \u003d 169,20903, а p \u003d 2,89Е-12, т. Е. Маємо нульову ймовірність того, що буде відкинута правильна гіпотеза про незначущості вільного члена. Для коефіцієнта при невідомій t \u003d 5,79405, а p \u003d 0,001158. Іншими словами ймовірність того, що буде відкинута правильна гіпотеза про незначущості коефіцієнта при невідомій, дорівнює 0,12%.
Таким чином, можна стверджувати, що отримане рівняння лінійної регресії адекватно.
Завдання про доцільність покупки пакета акцій
Множинна регресія в Excel виконується з використанням все того ж інструменту «Аналіз даних». Розглянемо конкретну прикладну задачу.
Керівництво компанія «NNN» має прийняти рішення про доцільність купівлі 20% пакета акцій АТ «MMM». Вартість пакету (СП) становить 70 млн американських доларів. Фахівцями «NNN» зібрані дані про аналогічні угоди. Було прийнято рішення оцінювати вартість пакета акцій за такими параметрами, вираженим в мільйонах американських доларів, як:
- кредиторська заборгованість (VK);
- обсяг річного обороту (VO);
- дебіторська заборгованість (VD);
- вартість основних фондів (СОФ).
Крім того, використовується параметр заборгованість підприємства із заробітної плати (V3 П) в тисячах американських доларів.
Рішення засобами табличного процесора Excel
Перш за все, необхідно скласти таблицю вихідних даних. Вона має такий вигляд:
- викликають вікно «Аналіз даних»;
- вибирають розділ «Регресія»;
- в віконце «Вхідний інтервал Y» вводять діапазон значень залежних змінних з шпальти G;
- клацають по іконці з червоною стрілкою праворуч від вікна «Вхідний інтервал X» і виділяють на аркуші діапазон всіх значень з стовпців B, C, D, F.
Відзначають пункт «Новий робочий лист» і натискають «Ok».
Отримують аналіз регресії для даного завдання.
Вивчення результатів і висновки
«Збираємо» з округлених даних, представлених вище на аркуші табличного процесора Excel, рівняння регресії:
СП \u003d 0,103 * СОФ + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.
У більш звичному математичному вигляді його можна записати, як:
y \u003d 0,103 * x1 + 0,541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265,844
Дані для АТ «MMM» представлені в таблиці:
Підставивши їх в рівняння регресії, отримують цифру в 64,72 млн американських доларів. Це означає, що акції АТ «MMM» не варто купувати, так як їх вартість в 70 млн американських доларів досить завищена.
Як бачимо, використання табличного процесора «Ексель» і рівняння регресії дозволило прийняти обгрунтоване рішення щодо доцільності цілком конкретної угоди.
Тепер ви знаєте, що таке регресія. Приклади в Excel, розглянуті вище, допоможуть вам у вирішення практичних завдань з області економетрики.
Регресійний аналіз є одним з найбільш затребуваних методів статистичного дослідження. З його допомогою можна встановити ступінь впливу незалежних величин на залежну змінну. У функціоналі Microsoft Excel є інструменти, призначені для проведення подібного виду аналізу. Давайте розберемо, що вони собою представляють і як ними користуватися.
Але, для того, щоб використовувати функцію, що дозволяє провести регресійний аналіз, перш за все, потрібно активувати Пакет аналізу. Тільки тоді необхідні для цієї процедури інструменти з'являться на стрічці Ексель.
Тепер, коли ми перейдемо у вкладку «Дані», На стрічці в блоці інструментів «Аналіз» ми побачимо нову кнопку - «Аналіз даних».
Види регресійного аналізу
Існує кілька видів регрессий:
- параболічна;
- статечна;
- логарифмічна;
- експоненціальна;
- показова;
- гіперболічна;
- лінійна регресія.
Про виконання останнього виду регресійного аналізу в Ексель ми докладніше поговоримо далі.
Лінійна регресія в програмі Excel
Внизу, як приклад, наведено таблицю, в якій вказана середньодобова температура повітря на вулиці, і кількість покупців магазину за відповідний робочий день. Давайте з'ясуємо за допомогою регресійного аналізу, як саме погодні умови у вигляді температури повітря можуть вплинути на відвідуваність торгового закладу.
Загальне рівняння регресії лінійного виду виглядає наступним чином: У \u003d а0 + а1х1 + ... + акхк. У цій формулі Y означає змінну, вплив чинників на яку ми намагаємося вивчити. У нашому випадку, це кількість покупців. значення x - це різні фактори, що впливають на змінну. параметри a є коефіцієнтами регресії. Тобто, саме вони визначають значимість того чи іншого фактора. індекс k означає загальну кількість цих самих чинників.
Розбір результатів аналізу
Результати регресійного аналізу виводяться у вигляді таблиці в тому місці, яке зазначено в настройках.
Одним з основних показників є R-квадрат. У ньому вказується якість моделі. У нашому випадку даний коефіцієнт дорівнює 0,705 або близько 70,5%. Це прийнятний рівень якості. Залежність менше 0,5 є поганий.
Ще один важливий показник розташований в осередку на перетині рядка «Y-перетин» і стовпці «Коефіцієнти». Тут вказується яке значення буде у Y, а в нашому випадку, це кількість покупців, при всіх інших факторах рівних нулю. У цій таблиці дане значення дорівнює 58,04.
Значення на перетині граф «Мінлива X1» і «Коефіцієнти» показує рівень залежності Y від X. В нашому випадку - це рівень залежності кількості клієнтів магазину від температури. Коефіцієнт 1,31 вважається досить високим показником впливу.
Як бачимо, за допомогою програми Microsoft Excel досить просто скласти таблицю регресійного аналізу. Але, працювати з отриманими на виході даними, і розуміти їх суть, зможе тільки підготовлена \u200b\u200bлюдина.
