Главная Обратная связь Поможем написать вашу работу!

Дисциплины:

Архитектура (936)
Биология (6393)
География (744)
История (25)
Компьютеры (1497)
Кулинария (2184)
Культура (3938)
Литература (5778)
Математика (5918)
Медицина (9278)
Механика (2776)
Образование (13883)
Политика (26404)
Правоведение (321)
Психология (56518)
Религия (1833)
Социология (23400)
Спорт (2350)
Строительство (17942)
Технология (5741)
Транспорт (14634)
Физика (1043)
Философия (440)
Финансы (17336)
Химия (4931)
Экология (6055)
Экономика (9200)
Электроника (7621)






Пакети аналізу Þ Регресія



 

При виконанні командиСервіс, Аналіз даних вибирається Регресія, з'являється однойменне діалогове вікно

 

Рисунок В.1 – Діалогове вікно режиму Регресія

 

У даному вікні задаються:

Вхідний інтервал У — інтервал результативної ознаки (залежних даних), що підлягають аналізу. Залежні дані повинні бути введені в окремому стовпці.

Вхідний інтервал Х — інтервал факторів (незалежнихданих), що підлягають аналізу. Незалежні змінні впорядковані зліва направо із використанням індексів 1, 2, 3 і т.д. у вихідній таблиці (максимальне число вхідних інтервалів дорівнює 16).

Константа нуль — лінія регресії проходить черезпочатоккоординат;

Рівень надійності — за замовчуванням 95%.

Вихідний інтервал — верхній лівий осередок інтервалу, у який виводяться вихідні таблиці (не менш семи стовпців для підсумкової вихідної таблиці).

Результати регресійного аналізу розміщуються в трьох таблицях. Наприклад, проведення регресійного аналізу для курсу акції у взаємозв'язку з номіналом, емісією й попитом на цінні папери дає такі результати:

Таблиця В.1 – Регресійна статистика — оцінка кореляційного зв'язку

Регресійна статистика
Множинний R 0,922
R-квадрат 0,850
Нормований R-квадрат 0,738
Стандартна помилка 0,020
Спостереження

 

Пояснення до таблиці В.1:

Множинний R — коефіцієнт кореляції Пірсона, дорівнює кореню квадратному з R-квадрат.

R-квадрат — коефіцієнт детермінації, характеризує тісний зв'язок фактичних значень і розрахованих за отриманою регресійною моделлю. Чим ближче до одиниці тим точки лінії регресії ближче до фактичних значень y.

Нормований R-квадрат.

• Стандартна помилка — середньоквадратичне значення відхилення регресії від емпіричних даних.

Спостереження — кількість (n) спостережень у масиві.

 

Таблиця В.2 – Дисперсійний аналіз — ANOVA

Дисперсійний аналіз df ss MS F Значущість F
Регресія 0,0095 0,0032 7,582 0,0398
Залишок 0,0017 0,0004    
Разом 0,0111      

 



Пояснення до таблиці В.2:

df — число ступенів волі; для рядка Регресія це число змінних (розглянутих факторів) у рівнянні регресії — у цьому випадку m=3 (номінал, емісія й попит на цінні папери); для рядка Залишок — розмір вибірки мінус число параметрів у регресії мінус 1 (n-m-1); для рядка Разом — розмір вибірки мінус 1 (n-1).

• SS — сума квадратів відхилень для розрахунку дисперсії:

для рядка Регресія — факторної, для рядка Залишок — залишкової, для рядка Разом — загальної.

MS — дисперсія, що розраховується як відношення суми квадратів відхилень до величини df.

F — статистика для оцінки зв'язку між залежною й незалежною змінними, визначається як:

МS(Регресія)/МS(Залишки),

Значущість F — значення рівня значущості aF, що відповідає обчисленому значенню F.

У розглянутому прикладі модель регресії правомірна:

обчислена за формулою ймовірність правильного прогнозу близька до 1:

Р = 1 — 0,0398 = 0,9602;

якщо рівень значущості aF =0,05, то й у цьому випадку буде дотримана вимога Fрасч > Fтабл, тому що за таблицями Фішера Fk1,k2,a=5,82, а обчислене значення 7,582.



 

Таблиця В.3 – Параметри моделі і статистичні оцінки

    Y-перетинання (a0) Номінал ЦБ (a1) Емісія ЦБ (a2) Попит ЦБ (a3)
Коефіцієнти 1,01 0,00 -0,03 0,03
Стандартна помилка 0,03 0,00 0,01 0,01
t-статистика 33,04 1,32 -2,64 2,90
Р-значення 0,00 0,26 0,06 0,( '-,
Нижні 95% 0,92 0,00 -0,07 0,00
Верхні 95% 1,09 0,00 0,00 0,07

 

* Таблиця В.3 подана в транспонованому вигляді.

 

Пояснення до таблиці:

Коефіцієнти — значення параметрів моделі регресії.

Стандартна помилка — параметрів рівняння регресії.

• t-статистика — відношення Коефіцієнт/Стандартна

помилка.

• Р-значення — рівень значущості aз для значень t-Cmaтистики.

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

 

Таблиця 1.4 – Вивідпрогнозних значень за моделлю й залишків

Спостереження Передбачений Курс ЦБ Залишки Стандартні залишки Персентиль Курс ЦБ
1,05 0,00 0,00 6,25 0.97
1,05 0,02 1,35 18,75 0,97
0,98 -0,01 -0,74 31,25 0,97
0,99 -0,01 -0,90 43,75 0,98
0,99 -0,01 -0,90 56,25 0,98
0,95 0,02 1,05 68,75 0,98
0.95 0,02 1,05 81,25 1,05
0,99 -0,01 -0,90 93,75 1,07

 



Пояснення до таблиці 1.4:

Передбачений Y — розрахункові значення за моделлю регресії.

Залишки — різниця емпіричної й передбаченої за моделлю регресії значень.

За бажанням користувача можуть бути виведені такі види графіків:

Графік залишків — для кожної незалежної змінної забезпечує відображення залишків як різниць між емпіричними й регресійними значеннями;

Графік підбора — діаграма для зіставлення передбачених значень за регресійною моделлю з даними спостережень.

Графік нормального розподілу — діаграма для нормальних імовірностей прогнозних значень. Автоматично формується інтервал персентилей, для яких вказуються відповідні моделі значення ДО

 

 


Додаток Г

Функція EXCEL ЛІНЕЙН

 

Розраховує параметри лінійної регресії із застосуванням методу найменших квадратів. Функція повертає масив, що описує отриману пряму. Оскільки повертається масив значень, функція має задаватися у вигляді формули масиву.

Рівняння для прямої лінії має такий вигляд:

 

y = mx + b або y = m1x1 + m2x2 + ... + b , (Г.1)

 

де y – функція незалежного значення x;

m – це коефіцієнти, що відповідають кожній незалежній змінній х;

b – постійна.

Зазначимо, що y, x й m можуть бути векторами. Функція ЛІНЕЙН повертає масив {mn; mn-1; ...; m1; b}.

ЛІНЕЙН може також повертати додаткову регресійну статистику.

 

Синтаксис

 

ЛІНЕЙН(відомі_значення_y;відомі_значення_x;конст;статистика)

 

Відомі_значення_y – це множина значень y, які вже відомі для співвідношення y = mx + b.

 

Якщо масив відомі_значення_y має один стовпець, то кожний стовпець масиву відомі_значення_x інтерпретується як окрема змінна.

Якщо масив відомі значення y має один рядок, то кожний рядок масиву відомі_значення_x інтерпретується як окрема змінна.

Відомі_значення_x – це необов'язкова множина значень x, які вже відомі для співвідношення y = mx + b.

Масив відомі_значення_x може мати одну або декілька множин змінних. Якщо використовується тільки одна змінна, то відомі_значення_y і відомі_значення_x можуть бути масивами будь-якої форми за умови, що вони мають однакову розмірність. Якщо використовується більше однієї змінної, то відомі_значення_y мають бути вектором (тобто інтервалом висотою в один рядок або шириною в один стовпець).

 

Якщо відомі_значення_x опущені, то зважають на те, що це масив {1;2;3;...} такого ж розміру як і відомі_значення_y.

 

Конст – це логічне значення, що вказує, чи потрібно, щоб константа b дорівнювала 0.

Якщо конст має значення ІСТИНА або опущено, то b визначається звичайним чином.

Якщо конст має значення НЕПРАВДА, то b береться рівним 0 і значення m підбираються так, щоб виконувалась рівність y = mx.

 

Статистика – це логічне значення, що вказує, чи потрібно повернути додаткову статистику з регресії.

Якщо статистика має значення ІСТИНА, то функція ЛІНЕЙН повертає додаткову регресійну статистику, так що масив, що повертається, матиме вигляд: {mn; mn-1;...; m1; b; sen; sen-1;...; se1; seb; r2; sey; F; df; ssreg; ssresid}.

Якщо статистика має значення НЕПРАВДА або опущена, то функція ЛІНЕЙН повертає тілки коефіцієнти m і постійну b.

 

Таблиця Г.1 – Додаткова регресійна статистика

 

mn mn-1 ……………… m1 b
sen sen-1 ……………… se1 seb
r2 sey
F df
ssreg ssresid

 

se1,se2,...,sen – стандартні значення помилок для коефіцієнтів m1,m2,...,mn.

seb – стандартне значення помилки для постійної b (seb = #Н/Д, якщо конст має значення НЕПРАВДА).

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

sey – стандартна помилка для оцінки y.

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

df – ступінь волі. Ступінь волі корисний для знаходження F-критичних значень у статистичній таблиці. Для визначення рівня надійності моделі потрібно порівняти значення в таблиці з F-статистикою, що повертає функцією ЛІНЕЙН.

ssreg – регресійна сума квадратів.

Ssresid – залишкова сума квадратів.

Точність апроксимації за допомогою прямої, обчисленої функцією ЛІНЕЙН, залежить від ступеня розкиду даних. Чим ближче дані до прямої, тим більше точною є модель, використовувана функцією ЛІНЕЙН. Функція ЛІНЕЙН використовує метод найменших квадратів для визначення найкращої апроксимації даних. Коли є тільки одна незалежна змінна x, m й b обчислюються за наступними формулами:

 

Функції апроксимації ЛІНЕЙН і ЛГРФПРИБЛ можуть обчислити пряму або експонентну криву, що найкраще описує Ваші дані. Однак, Ви самі повинні вирішувати, який із двох результатів найбільшою мірою підходить до Ваших даних. Можна також обчислити функцію ТЕНДЕНЦІЯ(відомі_значення_y; відомі_значення_x) для прямої або функцію РІСТ(відомі_значення_y; відомі_значення_x) для експонентної кривої. Ці функції, якщо не задавати аргумент нові_значення_x, повертають масив обчислених значень y для фактичних значень x відповідно до прямої або кривої. Тепер можна порівняти обчислені значення з фактичними значеннями. Можна також побудувати діаграми для візуального порівняння.

 

Проводячи регресійний аналіз, Microsoft Excel розраховує для кожної точки квадрат різниці між прогнозованим значенням y і фактичним значенням y. Сума цих квадратів різниць називається остаточною сумою квадратів. Потім Microsoft Excel підраховує суму квадратів різниць між фактичними значеннями y і середнім значенням y, яка називається загальною сумою квадратів (регресійна сума квадратів + остаточна сума квадратів). Чим менша остаточна сума квадратів в порівнянні з загальною сумою квадратів, тим більше значення коефіцієнта детермінованості r2, який показує, наскільки добре рівняння, отримане за допомогою регресійного аналізу, пояснює взаємозв’язки між змінними.

 

Формули, які повертають масиви, повинні бути введені як формули масивів. Додаткові відомості про формули масивів можна отримати, натиснувши кнопку.

При введенні масиву констант як, наприклад, аргумент відомі_значення_x, треба використовувати точку з комою для розділення значень в одному рядку і двокрапку для розділення рядків. Знаки-розділителі можуть бути різними і залежать від установок для різних країн.

 

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

 

Приклад 1. Нахил І Y-перетинання

 

ЛІНЕЙН({1;9;5;7};{0;4;2;3}) дорівнює {2;1}, нахил = 2 й y-перетинання = 1.

 

Приклад 2. Проста Лінійна Регресія

 

Припустимо, що невелика фірма мала продажі на суму 3100 руб., 4500 руб., 4400 руб., 5400 руб., 7500 руб. й 8100 руб. за перші шість місяців звітного року. Нехай ці значення перебувають в інтервалі осередків B2:B7. Тоді можна використати наступну просту лінійну регресійну модель для оцінки обсягу продажів у дев'ятому місяці.

 

СУМ(ЛІНЕЙН(B2:B7)*{9;1}) дорівнює СУМ({1000;2000}*{9;1}) дорівнює 11 000 руб.

 

У загальному випадку СУМ({m;b}*{x;1}) дорівнює mx + b, тобто значенню y для даного значення x. Для цих же цілей можна використати функцію ТЕНДЕНЦІЯ.

 

Приклад 3. Множинна Лінійна Регресія

 

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

 

Забудовник може використати множинний регресійний аналіз для оцінки ціни офісного будинку в заданому районі на основі наступних змінних.

 

Змінна Зміст змінної

y Оцінна ціна будинку під офіс

x1 Загальна площа у квадратних метрах

x2 Кількість офісів

x3 Кількість входів

x4 Час експлуатації будинку в роках

 

У цьому прикладі передбачається, що існує лінійна залежність між кожною незалежною змінною (x1, x2, x3 й x4) і залежною змінною (y), тобто ціною будинку під офіс у даному районі.

Забудовник навмання вибирає 11 будинків з наявних 1500 й одержує наступні дані.

 

"Пів-входу" (1/2) означає вхід тільки для доставки кореспонденції. При введенні як масив наведена нижче формула:

 

ЛІНЕЙН(E2:E12;A2:D12;ІСТИНА;ІСТИНА)

 

повертає наступні результати.

 

Рівняння множинної регресії y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b тепер може бути отримане з рядка 14:

 

y = 27,64*x1 + 12 530*x2 + 2 553*x3+ 234,24*x4 + 52 318.

 

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

 

y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = 158 261 руб.

 

Це значення може бути також обчислене за допомогою функції ТЕНДЕНЦІЯ. Для одержання більш докладної інформації див. ТЕНДЕНЦІЯ.

 

Приклад 4. Використання F і R2 Статистик

 

У попередньому прикладі коефіцієнт детермінованості r2 дорівнює 0,99675 (див. осередок A16 у результатах функції ЛІНЕЙН), що вказує на сильну залежність між незалежними змінними й продажною ціною. Можна використати F-статистику, щоб визначити, чи є цей результат (з таким високим значенням r2 ) випадковим.

 

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

 

Якщо F-спостережуване більше, ніж F-критичне, то взаємозв'язок між змінними є. F-критичне можна одержати з таблиці F-критичних значень у будь-якому довіднику за математичною статистикою. Для того, щоб знайти це значення, використовуючи однобічний тест, покладемо величину Альфа, що дорівнює 0,05, а для числа ступенів волі (позначуваних звичайно v1 й v2), покладемо

v1 = k = 4

і (1.2)

v2 = n - (k + 1) = 11 - (4 + 1) = 6,

де k – це число змінних;

n – число точок даних.

 

З таблиці довідника F-критичне дорівнює 4,53.

 

Спостережуване F-значення дорівнює 459,753674 (осередок A17), що помітно більше, ніж F-критичне значення 4,53. Отже, отримане регресійне рівняння корисно для передбачення оцінної вартості будинків у даному районі.

Приклад 5. Обчислення T-Статистики

 

Інший гіпотетичний експеримент визначить, чи корисний кожен коефіцієнт нахилу для оцінки вартості будинку під офіс у прикладі 3. Наприклад, для перевірки того, що строк експлуатації будинку має статистичну значущість, розділимо -234,24 (коефіцієнт нахилу для строку експлуатації будинку) на 13,268 (оцінка стандартної помилки для коефіцієнта часу експлуатації з осередку A15). Нижче наводиться спостережуване t-значення:

 

t = m4 ч se4 = -234.24 ч 13.268 = -17.7

 

Якщо подивитися в таблицю довідника за математичною статистикою, то виявиться, що t-критичне з 6 ступенями волі й Альфа = 0,05 дорівнює 1,94. Оскільки абсолютна величина t рівна 17,7, більше, ніж 1,94, строк експлуатації – це важлива змінна для оцінки вартості будинку під офіс. Аналогічним чином можна протестувати всі інші змінні на статистичну значущість. Нижче наводяться спостережувані t-значення для кожної з незалежних змінних:

 

Змінна t-спостережуване значення

Загальна площа 5,1

Кількість офісів 31,3

Кількість входів 4,8

Строк експлуатації 17,7

 

Всі ці значення мають абсолютну величину більшу, ніж 1,94. Отже, всі змінні, використані в рівнянні регресії, корисні для передбачення оцінної вартості будинку під офіс у даному районі.

 

 


Просмотров 323

Эта страница нарушает авторские права




allrefrs.ru - 2021 год. Все права принадлежат их авторам!