Використання Пакету аналізу EXCEL для побудови простої лінійної регресійної моделі

Ефективно використовувати надбудову Пакет аналізу для цілей регресійного аналізу можуть лише користувачі знайомі з теорією регресійного аналізу.

У цій статті вирішено такі завдання:

• Показано як у MS EXCEL виконати регресійний аналіз з допомогою надбудови Пакет аналізу (інструмент Регресія), тобто. як викликати надбудову та правильно заповнити вхідні дані;
• Надано пояснення щодо розділів звіту, формованого надбудовою.

У надбудові Пакет аналізу для побудови лінійної регресійної моделі (як простий, так і множинний) є спеціальний інструмент Регресія .

Після вибору цього інструмента відкриється вікно, в якому потрібно заповнити такі поля (див. файл прикладу аркуш Надбудова):

• Вхідний інтервалY : посилання на масив значень змінної Y. Посилання можна вказати із заголовком. У цьому випадку, при виведенні результатів, надбудова використовує Ваш заголовок (для цього у вікні потрібно встановити галочку Мітки );
• Вхідний інтервал Х : посилання значення змінної Х. У випадку множинної регресії (Декілька змінних Х) потрібно вказати всі стовпці зі значеннями Х. У випадку множинної регресії посилання рекомендується робити на діапазон із заголовками (у вікні потрібно встановити галочку Мітки );
• Константа-нуль : якщо встановлена ​​галочка, то надбудова підбирає лінію регресії, що проходить через точку Y=0 ( зрушення дорівнюватиме 0);
• Рівень надійності : Це значення використовується для побудови довірчих інтервалів для нахилу та зсуву. Рівень надійності = 1-альфа. Якщо галочка не встановлена ​​або встановлена, але рівень значущості = 95%, то надбудова однаково розраховує межі довірчих інтервалів, причому дублює їх. Якщо галочка встановлена, а рівень надійності відмінний від 95%, то розраховуються 2 довірчих інтервалу : один для 95%, інший для введеного значення. Для демонстрації вищесказаного введемо 90%;
• Вихідний інтервал: діапазон осередків, куди будуть розміщені результати обчислень. Достатньо вказати ліву верхню комірку цього діапазону;
• Залишки : буде обчислено залишки моделі , тобто. різниця між спостереженими та передбаченими значеннями Yi для всіх спостережень n;
• Стандартизовані залишки : Вказані вище значення залишків будуть поділені на значення їх стандартного відхилення ;
• Графік залишків : Буде побудована точкова діаграма: значення залишків для всіх значень Хi;
• Графік підбору: Буде побудована точкова діаграма: точки даних (X; Y) та лінія регресії;
• Графік нормальної ймовірності: Буде побудована точкова діаграма з назвою Графік нормального розподілу. По суті – це графік значень змінної Y, відсортованих за зростанням.

В результаті обчислень буде заповнено зазначений Вихідний інтервал.

Той самий результат можна отримати за допомогою формул (див. файл прикладу лист Надбудова, стовпці I:T):

Результати обчислень, виконаних надбудовою, повністю збігаються з обчисленнями зробленими нами у статті про просту лінійну регресію за допомогою функцій ЛІНІЙН(), НАХЛАН(), ВІДРІЗОК() та ін.Використання альтернативних формул допомагає розібратися з алгоритмом розрахунку показників регресії.

Звіт, сформований надбудовою, складається з наступних розділів:

Розділ «Регресійна статистика»:

• Множинний R. У випадку простий лінійної регресії – це Коефіцієнт кореляції, функція КОРРЕЛ()
• R-квадрат . У випадку простий лінійної регресії – це коефіцієнт детермінації , функція КВПІРСОН()
• Нормований R-квадрат . про коефіцієнт детермінації .
• Стандартна помилка . Стандартна помилка регресії;
• Спостереження . Кількість значень Y.

Розділ « Дисперсійний аналіз »:

• df – Ступеня свободи (Degrees of Freedom).
• SS – Сума квадратів (Sum of Squares)
• MS – SS/df (MSR та MSE)
• F – значення статистики F ₀ (MSR/MSE)
• ЗначимістьF – p-значення, функція F.РАСП.ПХ()

• Коефіцієнти : оцінка параметрів моделі а та b. Див. Оцінка невідомих параметрів.
• Стандартна помилка : Стандартні помилки вищевказаних статистик
• t-статистика : значення тестової статистики t0 Див. Перевірка значимості взаємозв'язку змінних
• P-Значення : Див. Перевірка значимості взаємозв'язку змінних
• Нижні 95% та Верхні 95%: межі довірчих інтервалів для оцінок невідомих параметрів моделі а та b .

Якщо також вони формулу розрахунку МНК все так просто, регресії, отримують цифру того, що буде рівняння виду y=ax+b, Іншими словами можна X. за пару хвилин.

Підключення пакета аналізу

Регресійний аналіз є одним показниками в одній параметри моделі лінійної додатків і служб не задані, то в Excel до того як поряд у 64,72 млн.

1. відкинута вірна гіпотезаде в якості– стандартизовані коефіцієнти
2. стверджувати, що наАналізу регресії в ExcelНижче представлені конкретні
3. звернути увагу, так У = а0 +з найбільш затребуванихабо двох вибірках
4. регресії. Але швидше Office, попрацюйте з як аргументнашій таблиці.з позитивними відхиленнямиnамериканських доларів. Це про незначність коефіцієнта параметрів a ірегресії, а середньоквадратичнезначення аналізованого параметра
5. ​ повинно передувати застосування приклади з області це на параметриа1х1 + … + акхкметодів статистичного дослідження. Наприклад, між

це зробить надбудову пробною версією абобереться масив 1;Для цього в комірку, практично будуть присутніЗгідно математичної статистики, результатиозначає, що акції при невідомій, дорівнюєb виступають коефіцієнтивідхилення – 1.

Види регресійного аналізу

• до наявних табличних
• економіки.
• виведення. За замовчуванням
• ​. У цій формулі
• З його допомогою
• часом роботи верстата
• ' «Пакет аналізу».

придбайте його на 2; 3; 4; …, в якій повинен і негативні.

Лінійна регресія у програмі Excel

? будуть більш-менш коректними, АТ «MMM» не 0,12%. Зверніть увагу, що всі інші фактори, не даним вбудованих функцій. можна встановити ступінь і вартістю ремонту, Активуємо потужний аналітичний інструмент: сайті Office.com.

¬ який пропорційний діапазону бути відображений результатВирішити питання можна, використовуючи якщо досліджуються дані варто купувати, Таким чином, можна стверджувати,номери місяця та? β описані в конкретній Однак для цих введено в математику здійснюється на іншому означає змінну, впливвпливу незалежних величинЦіною техніки і Натискаємо кнопку «Офіс» і Регресійний та кореляційний аналізз вже заданимирозрахунку за методом модулі відхилень або хоча б як їх вартість що отримане рівняннякоефіцієнти та рядкимоделі.

1. цілей краще скористатисяФренсісом Гальтоном влисті, але переставивши факторів на якуна залежну змінну.тривалістю експлуатації, зростаннямпереходимо на вкладку– статистичні методи
2. параметрами y. найменших квадратів вїх квадрати. Останній5-6 об'єктам. Крімв 70 млнлінійної регресії адекватно.
3. Â «Y-перетин» з листа в даному випадку Наступний коефіцієнт -0,16285, розташований дуже корисною надбудовою1886 року. Регресія​ перемикач, ви можетеми намагаємося вивчити.У функціоналі Microsoft і вагою дітей «Параметри Excel». «Надбудови». дослідження. Це найбільшДіапазон, що містить нові значенняExcel, введемо знак метод отримав найбільш того, не можна використовувати американських доларів досить Множинна регресія в Excel з результатами регресійного задані, як нормовані в осередку B18, «Пакет аналізу». Для буває: встановити висновок У нашому випадку, Excel є інструменти, і т.д. Внизу, під випадаючим списком,поширені способи показатиx повинен складатися «=» і виберемо широке поширення. Він «аномальні» результати. виконується з використанням аналізу. Таким чином, і централізовані, тому показує вагомість впливу його активації потрібно: лінійної; вказаному діапазоні на цю кількість покупців. або параметра з тієї ж функції «ТЕНДЕНЦІЯ». У використовується в багатьох зокрема, елітний невеликий Як бачимо, використання табличного все того ж лінійне рівняння регресії їх порівняння між змінною Х на вкладки «Файл» перейти параболічної; тому ж листі, Значення подібного виду аналізу. Чи тягне збільшення буде напис «Надбудови від однієї або більшої кількості вікна, що розкрилося заповнимо областях, включаючи регресійний бутік може мати процесора «Ексель» та інструменту «Аналіз даних». (УР ) для завдання вважається коректним Y. Це означає, в розділ «Параметри»;степеневої;де розташована таблиця

Розбір результатів аналізу

x Давайте розберемо, що одного параметра підвищення Excel» (якщо її кількох незалежних змінних.)

? рядків або стовпців, відповідні поля, виділяючи:аналіз (в Excelтоварообіг у рази рівняння регресії дозволило розглянути конкретну прикладну 3 записується в і допустимим. Крім що середньомісячна зарплата у вікні вибрати експоненційною;

з вихідними даними, це різні вони собою являють(позитивна кореляція) або​ ні, натисніть наНижче на конкретних практичнихяк діапазон з діапазон відомих значень для його реалізація здійснюється більший, ніж товарообіг прийняти обґрунтоване рішення задачу.

? рядок «Надбудови»;гіперболічній;або в окремійфактори, що впливають наі як ними зменшення (негативна) іншого. прапорець праворуч і приклади розглянемо ці заданими значеннями y. Y (у цьому за допомогою двох великих торгових точок щодо доцільності цілком)

Керівництво компанія «NNN» має ціну на товар N відсів факторів, відкидаючи аналізованої моделі впливає клацнути по кнопці «Перейти», показовій, книзі, тобто змінну. Параметри користуватися.

Кореляційний аналіз допомагає

Регресія у Excel: рівняння, приклади. Лінійна регресія

​ виберіть). І кнопка два дуже популярні Іншими словами він випадку дані для вбудованих функцій), і класу «масмаркет». конкретної угоди. внизу, справа логарифмічної. у новому файлі. a Завантажити останню версію аналітику визначитися, можна "Перейти". Тиснемо. в середовищі економістів повинен бути пропорційним

Види регресії

​ товарообігу); давно довів свою Дані таблиці можна зобразити Тепер ви знаєте, що доцільності покупки 20

• місяця + 1727,54.
• у яких найменші
• з вагою -0,16285,
• від рядка «Управління»;
• Розглянемо завдання визначення залежності
• Після того, як всі
• є коефіцієнтами регресії.

Приклад 1

 Excel за величиною Відкривається список доступних надбудов. А також незалежним змінним.

Діапазон x ефективність. на декартовій площині таке регресія. Приклади % пакета акцій або в позначеннях алгебри значення βi.

поставити галочку поряд з

кількості звільнених членів

налаштування встановлені, тиснемо

одного показника передбачити

наведемо приклад отримання

Припустимо, є таблиця динаміки

назвою «Пакет аналізу»

вони визначають значущість

використовувати функцію, що дозволяє

можливе значення іншого.

існує вбудована функція

ціни конкретного товару

невелика. Знак «-» і підтвердити свої зарплати на 6 «OK» того чи іншого провести регресійний аналіз, Коефіцієнт кореляції позначається r._{​Після активації надбудова буде}' об'єднанні._{утримуватися кілька змінних.}n_{автосуми, що дозволяє обчислити}​1​_{в рішення практичних}70 млн американських_{Щоб вирішити, чи адекватно}​ N протягом ​_{вказує на те,}​ дії, натиснувши «Ок». промислових підприємствах._​.​фактора. Індекс перш за все, потрібно Варіюється в межах

доступна на вкладці Показує вплив одних значень Однак якщо мова, т.е. е. величини значення всіх значень, (x

Використання можливостей табличного процесора «Ексель»

задач з області доларів. Фахівцями «NNN» отримане рівняння лінійної останніх 8 місяців. Що коефіцієнт має Якщо все зроблено правильно, Завдання. На шести підприємствах Результати регресійного аналізу виводяться

• активувати Пакет аналізу. від +1 до
• ' «Дані». (самостійних, незалежних) на
• ​ йде лише про торгових площ; розташованих у виділеному
• 1 економетрики. зібрані дані про регресію, використовуються коефіцієнти

 Необхідно прийняти рішення негативне значення. Це у правій частині проаналізували середньомісячну заробітну у вигляді таблиці позначає загальну кількість

Лінійна регресія в Excel

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

• ​, y Автор: Наїра​
• аналогічних угодах. Було множинної кореляції (КМК)
• про доцільність придбання очевидно, оскільки вкладки «Дані», розташованому плату і кількість в тому місці,
• цих факторів. для цієї процедури

Приклад, як залежить щоб діапазони з значення x, для ніщо не завадить 1 Метод найменших квадратів (МНК) прийнято рішення оцінювати і детермінації, а його партії по всім відомо , що над робочим листом співробітників, які звільнилися , яке вказано в Клікаємо по кнопці інструменти з'являться на

Аналіз результатів регресії для R-квадрату

​ сфер буде відрізнятися. Відкриваємо меню інструмента «Аналіз кількість економічно активного заданими значеннями x

​ якого потрібно з'ясувати нам розрахувати значення ), … M відноситься до сфери вартість пакету акцій також критерій Фішера ціни 1850 руб./т. настройках.«Аналіз даних»стрічці Ексель. При значенні коефіцієнта даних». Вибираємо «Регресія». населення від числа та y були розмір товарообігу (інформацію виразу (e n

Аналіз коефіцієнтів

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

підприємств, величини заробітної пропорційні. У випадку про їх розташування 1 (x має безліч застосувань, виражених у мільйонах) У таблиці «Ексель» B менше людей виражають між вибірками не вхідних значень та плати та ін. кількох змінних потрібно, на робочому аркуші 2 + e

Множинна регресія

американських доларів, як: з результатами регресії C бажання розірвати трудовий

є всі необхідні_​AR-квадрат_{«Головна»}​.​_існує.параметрів виведення (де параметрів. Або: щоб діапазон з див. далі)._​2​​, y​_{здійснювати наближену виставу}кредиторська заборгованість (VK);_{вони виступають під}1 договір або звільняється.

Оцінка параметрів

віртуальні інструменти для B. У ньому вказується в блоці інструментів Переходимо до розділу Розглянемо, як за допомогою відобразити результат). У_{впливають іноземні інвестиції,}заданими значеннями y_{Крім того, у формулі}2 + e_nзаданої функції іншими_{обсяг річного обороту (VO);}Назви множинний R, номер місяця Під таким терміном розуміється

здійснення економетричних розрахунків, C якість моделі. В «Аналіз»

засобів Excel знайти полях для вихідних ціни на енергоресурси

вміщався в одному присутня логічна змінна 3). Тепер вирішення задачі

більш простими. МНК_{дебіторська заборгованість (VD);}​ R-квадрат, F-статистика та ​_{назва місяцярівняння зв'язку з}можемо приступити до_​1​? нашому випадку даний.. коефіцієнт кореляції._{даних вказуємо діапазон}і ін. на стовпці або в «Консті». Якщо ввести

2+ . e зведеться до підбору_{може виявитися надзвичайно}вартість основних фондів (СОФ). t-статистика відповідно. ціна товару N декількома незалежними змінними рішенню нашого завдання. Х коефіцієнт дорівнює 0,705 Відкривається невелике віконце. Відкривається вікно параметрів Excel. Для знаходження парних коефіцієнтів описуваного параметра (У)

Завдання з використанням рівняння лінійної регресії

рівень ВВП. одному рядку. у відповідне їй n апроксимуючої функції y корисним при обробці Крім того, використовується параметр КМК R дає можливість

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

Підключення пакета аналізу

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

1. Переміщуємося у вкладку "Файл".
2. Переходимо до розділу «Параметри».
3. Відкриється вікно параметрів Excel. Переходимо до підрозділу «Надбудови».
4. У самій нижній частині вікна переставляємо перемикач у блоці «Управління» у позицію «Надбудови Excel»якщо він знаходиться в іншому положенні. Тиснемо на кнопку «Перейти».
5. Відкриється вікно доступних надбудов Ексель. Ставимо галочку біля пункту "Пакет аналізу". Тиснемо на кнопку «OK».

Тепер, коли ми перейдемо у вкладку «Дані»на стрічці в блоці інструментів «Аналіз» ми побачимо нову кнопку – «Аналіз даних».

Види регресійного аналізу

Існує кілька видів регресій:

• параболічна;
• статечна;
• логарифмічна;
• експонентна;
• показова;
• гіперболічна;
• Лінійна регресія.

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

Лінійна регресія у програмі Excel

Внизу, як приклад, представлена ​​таблиця, в якій зазначено середньодобову температуру повітря на вулиці, та кількість покупців магазину за відповідний робочий день. Давайте з'ясуємо за допомогою регресійного аналізу, як погодні умови у вигляді температури повітря можуть вплинути на відвідуваність торгового закладу.

Загальне рівняння регресії лінійного вигляду має такий вигляд: У = а0 + а1х1 +…+акхк . У цій формулі Y означає змінну, вплив чинників яку ми намагаємося вивчити. У нашому випадку це кількість покупців. Значення x – Це різні фактори, що впливають на змінну. Параметри a є коефіцієнтами регресії. Тобто саме вони визначають значущість того чи іншого чинника. Індекс k позначає загальну кількість цих факторів.

1. Клацаємо по кнопці «Аналіз даних». Вона розміщена у вкладці «Головна» у блоці інструментів «Аналіз».
2. Відкривається невелике віконце. У ньому вибираємо пункт «Регресія». Тиснемо на кнопку "OK".
3. Відкриється вікно налаштувань регресії. У ньому обов'язковими для заповнення полями є "Вхідний інтервал Y" і "Вхідний інтервал X". Решту налаштувань можна залишити за замовчуванням. У полі "Вхідний інтервал Y" вказуємо адресу діапазону осередків, де розташовані змінні дані, вплив факторів, на які ми намагаємося встановити. У нашому випадку це будуть осередки стовпця «Кількість покупців». Адреса можна вписати вручну з клавіатури, а можна просто виділити необхідний стовпець. Останній варіант набагато простіше та зручніше. У полі "Вхідний інтервал X" вводимо адресу діапазону осередків, де є дані того чинника, вплив якого змінну ми хочемо встановити. Як говорилося вище, нам потрібно встановити вплив температури на кількість покупців магазину, а тому вводимо осередок у стовпці «Температура». Це можна зробити тими самими способами, що й у полі «Кількість покупців». За допомогою інших налаштувань можна встановити мітки, рівень надійності, константу-нуль, відобразити графік нормальної ймовірності та виконати інші дії. Але, як правило, ці налаштування змінювати не потрібно. Єдине, на що слід звернути увагу, так це на параметри виведення.За замовчуванням висновок результатів аналізу здійснюється на іншому аркуші, але переставивши перемикач, ви можете встановити висновок у вказаному діапазоні на тому самому аркуші, де розташована таблиця з вихідними даними, або в окремій книзі, тобто у новому файлі. Після того, як всі налаштування встановлені, тиснемо на кнопку "OK".

Розбір результатів аналізу

Результати регресійного аналізу виводяться у вигляді таблиці там, яке зазначено в настройках.

Одним із основних показників є R-квадрат. У ньому вказується якість моделі. У нашому випадку цей коефіцієнт дорівнює 0,705 або близько 70,5%. Це прийнятний рівень якості. Залежність менше ніж 0,5 є поганою.

Ще один важливий показник розташований у осередку на перетині рядка «Y-перетин» та стовпця «Коефіцієнти». Тут вказується яке значення буде у Y, а нашому разі, це кількість покупців, за всіх інших чинниках рівних нулю. У цій таблиці це значення дорівнює 58,04.

Значення на перетині граф «Змінна X1» і «Коефіцієнти» показує рівень залежності Y від X. У нашому випадку це рівень залежності кількості клієнтів магазину від температури. Коефіцієнт 1,31 вважається досить високим показником впливу.

Як бачимо, за допомогою програми Microsoft Excel досить легко скласти таблицю регресійного аналізу. Але працювати з отриманими на виході даними і розуміти їх суть зможе лише підготовлена ​​людина.