Додаток г Функція excel лінейн

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

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

y = m_x + b або y = m₁x₁ + m₂x₂ + ... + b , (Г.1)

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

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

b – постійна.

Зазначимо, що y, x й m можуть бути векторами. Функція ЛІНЕЙН повертає масив {m_n; m_n-1; ...; m₁; 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.

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

Якщо статистика має значення ІСТИНА, то функція ЛІНЕЙН повертає додаткову регресійну статистику, так що масив, що повертається, матиме вигляд: {m_n; m_n-1;...; m₁; b; se_n; se_n-1;...; se₁; se_b; r²; se_y; F; df; ssreg; ssresid}.

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

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

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

se₁,se₂,...,se_n – стандартні значення помилок для коефіцієнтів m₁,m₂,...,m_n.

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

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

se_y – стандартна помилка для оцінки 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. Отже, всі змінні, використані в рівнянні регресії, корисні для передбачення оцінної вартості будинку під офіс у даному районі.

4