3 Короткі теоретичні відомості

Функція ЛИНЕЙН

Ця функція використовує метод найменших квадратів, щоб вирахувати пряму лінію, яка найкращим чином апроксимує наявні дані. Функція повертає масив, який описує отриману пряму. Рівняння для прямої лінії має наступний вид:

y = m₁x₁ + m₂x₂ + ... + b

або

y = mx + b,

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

m - коефіцієнти, які відповідають кожній незалежній змінній x,

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.

Додаткова регресійна статистика:

Величина Опис

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

se_b Стандартне значення похибки для постійної b (se_b = #Н/Д, якщо конст має значення ЛОЖЬ).

r² Коефіцієнт детермінованості. Порівнюються фактичні значення y і значення, отримані з рівняння прямої; за результатами порівняння обчислюється коефіцієнт детермінованості, нормований від 0 до 1. Якщо він дорівнює 1, то має місце повна кореляція з моделлю - немає різниці між фактичним і оціночним значеннями y. В противному випадку, якщо коефіцієнт детермінованості дорівнює 0, то рівняння регресії не підходить для передбачення значень y. Проводячи регресійний аналіз, Microsoft Excel обчислює для кожної точки квадрат різниці між прогнозованим значенням y і фактичним значенням y. Сума цих квадратів різниць називається остаточною сумою квадратів. Потім Microsoft Excel підраховує суму квадратів різниць між фактичними значеннями y і середнім значенням y, яка називається загальною сумою квадратів (регресійна сума квадратів + остаточна сума квадратів). Чим менше остаточна сума квадратів у порівнянні з загальною сумою квадратів, тим більше значення коефіцієнта детермінованості r², який показує, наскільки добре рівняння, отримане з допомогою регресійного аналізу пояснює взаємозв’язки між змінними.

se_y Стандартна похибка для оцінки y.

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

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

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

ssresid Остаточна сума квадратів.

Додаткова регресійна статистика повертається в наступному порядку:

se1	se2	...	sen	seb
r2	sey
F	df
ssreg	ssresid

Точність апроксимації з допомогою прямої, отриманої з допомогою функції ЛИНЕЙН залежить від ступеня розкиду даних. Чим ближче дані до прямої, тим більш точною є модель, яка використовується функцією ЛИНЕЙН.

Функція ЛГРФПРИБЛ

Обчислює експоненційну криву, яка апроксимує дані та повертає масив, що описує цю криву. Рівняння кривої наступне:

y = (b*(m₁^x₁)*(m₂^x₂)*_)

або

y = b*m^x ,

де y - функція незалежних значень x.

m - основа для піднесення до степеня x,

b - постійна.

Зауважимо, що y, x та m можуть бути векторами. Функція ЛГРФПРИБЛ повертає масив {m_n; m_n-1; ...; m₁; b}.

Синтаксис функції ЛГРФПРИБЛ аналогічний синтаксису функції ЛИНЕЙН. Величини додаткової регресійної статистики, яку повертає функція ЛГРФПРИБЛ аналогічні відповідним величинам додаткової регресійної статистики, яку повертає функція ЛИНЕЙН. Але додаткова статистика, яку повертає функція ЛГРФПРИБЛ, грунтується на наступній лінійній моделі:

ln y = x₁ ln m₁ + ... + x_n ln m_n + ln b

Це слід пам’ятати при оцінці додаткової статистики, особливо значень se_i та se_b, які слід порівнювати з ln m_i та ln b, а не з m_i та b.

Функція ТЕНДЕНЦИЯ

Повертає значення у відповідності з лінійним трендом. Апроксимує прямою лінією (за методом найменших квадратів) масиви известные_знач_y і известные_знач_x. Повертає значення y, які відповідають цій прямій для заданого масиву новые_знач_x.

Синтаксис:

ТЕНДЕНЦИЯ(известные_знач_y;известные_знач_x;

новые_знач_x; конст)

Параметри функції ТЕНДЕНЦИЯ известные_знач_y, известные_знач_x та конст аналогічні першим трьом параметрам функції ЛИНЕЙН.

Новые_знач_x - це нові значення x, для яких функції ТЕНДЕНЦИЯ повертає відповідні значення y.

Новые_знач_x повинні містити стовпчик (або рядок) для кожної незалежної змінної, так само, як известные_знач_x. Таким чином, якщо известные_знач_y має один стовпчик, то известные_знач_x та новые_знач_x повинні мати однакову кількість стовпчиків. Якщо известные_знач_y має один рядок, то известные_знач_x та новые_знач_x повинні мати однакову кількість рядків.

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

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

Функція РОСТ

Апроксимує експоненціальною кривою известные_знач_y та известные_знач_x і повертає відповідні до цієї кривої значення для значень x, які визначаються аргументом новые_знач_x.

Синтаксис функції аналогічний синтаксису функції ТЕНДЕНЦИЯ: РОСТ(известные_знач_y; известные_знач_x; новые_знач_x; конст).

Функції апроксимації ЛИНЕЙН та ЛГРФПРИБЛ можуть обчислити пряму або експоненційну криву, які найкращим чином описують Ваші дані. Але вирішити, який з двох результатів найбільшою мірою підходить до Ваших даних, Ви повинні самі.

Для перевірки відповідності отриманих залежностей можна використати функцію ТЕНДЕНЦИЯ для прямої або функцію РОСТ для експоненційної кривоі. Ці функції, якщо не задавати аргумент новые_знач_x, повертають масив обчислених значень y для фактичних значень x у відповідності з прямою або кривою.

УВАГА! Формули, які повертають масиви, повинні бути введені як формули масивів, тобто:

1) перед вводом формули треба виділити необхідний для виводу результату діапазон клітинок;

2) ввід формули повинен закінчуватись натисканням комбінації клавіш CTRL-SHIFT-ENTER.

Приклад. Фірма розглядає можливість купівлі невеликої будівлі під офіс серед групи будівель в традиційному діловому районі. Комерційний агент фірми може використати множинний регресійний аналіз для оцінки ціни необхідної для фірми будівлі під офіс на основі наступних змінних.

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

y Ціна будівлі під офіс

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

x₂ Кількість офісів

x₃ Кількість входів

x₄ Час попередньої експлуатації будівлі в роках

Припускається, що існує лінійна залежність між кожною незалежною змінною (x₁, x₂, x₃ та x₄) і залежною змінною (y), тобто ціною будівлі під офіс в даному районі.

Агент довільно вибирає 11 будівель з наявних 1230 і отримує наступні дані:

Таблиця 1.5 - Вибірка параметрів будівель

Будівля	X1	X2	X3	X4	Y
1	2310	2	2	20	142000
2	2333	2	2	12	144000
3	2356	3	2	33	151000
4	2379	3	2	43	151000
5	2402	2	3	53	139000
6	2425	4	2	23	169000
7	2448	2	1	99	126000
8	2471	2	2	34	142000
9	2494	3	3	23	163000
10	2517	4	4	55	169000
11	2540	2	3	22	149000

При вводі в якості масиву функція ЛИНЕЙН повертає наступні результати.

	-220.263	2378.639	12556.42	28.0664	51108.91
	17.41382	630.7686	509.564	6.405821	14494.56
	0.995008	1207.569
	298.9901	6
	1.74E+09	8749343

Рівняння множинної регресії y = m₁*x₁ + m₂*x₂ + m₃*x₃ + m₄*x₄ + b тепер може бути отримано з першого рядка результатів:

y = 28,07*x₁ + 12 556*x₂ + 2379*x₃+ 220,26*x₄ + 51109

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

y = 28,07*2500 + 12556*3 + 2379*2 - 220,26*25 + 51109 = 158 195 грн.

Використання F і R² статистик

Коефіцієнт детермінованості r² рівний 0,995, що вказує на сильну залежність між незалежними змінними і продажною ціною. Можна використати F-статистику, щоб визначити, чи є цей результат (з таким високим значенням r²) випадковим, чи ні.

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

Якщо F-спостережуване більше, ніж F-критичне, то взаємозв’язок між змінними є. F-критичне можна отримати з таблиці F-критичних значень в довіднику з математичної статистики. Для того, щоб знайти це значення, використовуючи односторонній тест, візьмемо  = 0,05, визначимо число ступенів свободи (які позначають звично через v₁ і v₂):

v₁ = k = 4 і v₂ = n - (k + 1) = 11 - (4 + 1) = 6, де k - число змінних, n - число точок даних. З таблиці довідника F-критичне дорівнює 4,53.

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

Обчислення t-статистики

Визначимо, чи кожний коефіцієнт нахилу для оцінки вартості будівлі під офіс суттєвий. Наприклад, для перевірки того, що термін попередньої експлуатації будівлі має статистичну значимість, розділимо -220,263 (коефіцієнт нахилу для терміну попередньої експлуатації будівлі) на 17,41382 (оцінка стандартної похибки для коефіцієнта терміну попередньої експлуатації). Нижче наводиться спостережуване t-значення:

t = m₄ / se₄ = -220,263 / 17,414 =-12,6488

З таблиці довідника з математичної статистики t-критичне з 6 степенями свободи і  = 0,05 дорівнює 1,94 . Оскільки абсолютна величина t, рівна 12,65, більша, ніж 1,94, термін попередньої експлуатації є важливою змінною для оцінки вартості будівлі під офіс. Аналогічним чином можна протестувати всі інші змінні на статистичну значимість. Спостережувані t-значення для кожної з незалежних змінних наступні:

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

Загальна площа 4,38

Кількість офісів 24,6

Кількість входів 3,77

Термін попередньої експлуатації 12,65

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