У цій статті наведено синтаксис формули та описано, як у програмі Microsoft Excel використовувати функцію LINEST .
Опис
Функція LINEST обчислює статистику для лінії за допомогою методу "найменших квадратів", щоб обчислити пряму лінію, яка найбільше відповідає вашим даним, а потім повертає масив, що описує цю лінію. Можна також поєднати функцію LINEST з іншими функціями, щоб обчислити статистику для інших типів моделей, які є лінійними в невідомих параметрах, у тому числі поліноміальні, логарифмічні, експоненційні та ступеневі ряди. Оскільки ця функція повертає масив значень, її потрібно вводити як формулу масиву. Необхідні вказівки наведено у прикладах цієї статті.
Формула для лінії має такий вигляд:
y = mx + b
-або-
y = m1x1 + m2x2 + ... + b
якщо існує кілька діапазонів х-значень, де залежні y-значення — це функція незалежних x-значень. m-значення — це коефіцієнти, які відповідають кожному x-значенню, а b — константа. Зауважте, що y, x і m можуть бути векторами. Масив, який повертає функція LINEST, — {mn;mn-1;...,m1;b}. Функція LINEST також може повертати додаткову статистику регресії.
Синтаксис
LINEST(відомі_значення_y;[відомі_значення_x];[конст];[статистика])
Синтаксис функції LINEST має такі аргументи:
known_y Обов'язковий. Набір значень y, вже відомих із рівняння y = mx + b.
- Якщо діапазон known_y знаходиться в одному стовпці, кожен стовпець known_x інтерпретується як окрема змінна.
- Якщо діапазон known_y міститься в одному рядку, кожен із рядків known_x інтерпретується як окрема змінна.
known_x Необов'язковий. Сукупність значень x, які можуть бути вже відомі з рівняння y = mx + b.
- Діапазон known_x може містити один або кілька наборів змінних. Якщо використовується лише одна змінна, то known_y та known_x можуть бути діапазонами будь-якої форми, за умови, що вони мають однакову розмірність. Якщо використовується кілька змінних, known_y мають бути вектором (тобто діапазоном із висотою в один рядок і шириною в один стовпець).
- Якщо known_x пропущено, вважається, що це масив {1,2,3,...} такого самого розміру, що й масив known_y.
const Необов'язковий. Логічне значення, яке вказує, чи потрібно встановити для константи b значення 0.
- Якщо константа – TRUE або її не вказано, b обчислюється як завжди.
- Якщо аргумент "конст" має значення FALSE (хибність), b дорівнює 0, а значення m добираються так, щоб y = mx.
статистика Необов'язковий. Логічне значення, яке визначає, чи потрібно повертати додаткову статистику регресії.
- Якщо stats має значення TRUE, функція LINEST повертає додаткову статистику регресії; Як результат, повернений масив матиме вигляд {mn;mn-1,...,m1;b; Сен,Сен-1,...,Се1,Себ; р2, сей; Ф,дф; ssreg;ssresid}.
- Якщо аргумент "статистика" приймає значення FALSE (хибність) або цей аргумент пропущено, функція LINEST повертає лише m-коефіцієнти й константу b.
Додаткова статистика регресії має такий вигляд.
| Статистичні значення | Опис |
|---|---|
| se1;se2;...;sen | Стандартні значення помилок для коефіцієнтів m1;m2;...;mn. |
| seb | Стандартне значення помилки для константи b (seb = #N/A, якщо константа має значення FALSE). |
| Р2 | Коефіцієнт відповідності. Порівнює очікувані та фактичні y-значення і змінюється у значеннях від 0 до 1. Якщо коефіцієнт дорівнює 1, то у зразку є досконала кореляція — немає різниці між очікуваним і фактичним y-значенням. З іншого боку, якщо коефіцієнт визначення дорівнює 0, рівняння регресії не допоможе у прогнозуванні y-значення. Для отримання додаткових відомостей про обчисленнячисла 2 див. пункт «Примітки» нижче в цій статті. |
| sey | Стандартна помилка для очікуваного y |
| F | F-статистика, або спостережувані F-значення. Використовуйте для визначення, чи спостережуване відношення між залежною і незалежною змінними виникло випадково. |
| df | Ступені свободи. Використовуйте ступені свободи для пошуку критичних F-значень у статистичній таблиці. Порівняйте знайдені в таблиці значення з F-статистикою, поверненою функцією LINEST , щоб визначити довірчий рівень моделі. Для отримання додаткових відомостей щодо обчислення df дивіться пункт «Примітки» далі у цьому розділі. У прикладі 4 показано, як використовувати значення F і df. |
| ssreg | Регресійна сума квадратів. |
| ssresid | Залишкова сума квадратів. Для отримання додаткових відомостей щодо обчислення значень ssreg і ssresid див. пункт «Примітки» нижче в цьому розділі. |
На наведеному нижче рисунку показано порядок повернення статистики додаткової регресії.
Примітки
Можна описати будь-яку пряму лінію за допомогою нахилу та перетину з віссю y:
Нахил (м):
Щоб знайти нахил лінії, яка часто записується як m, візьміть дві точки на лінії, (x1,y1) і (x2,y2); Нахил дорівнює (y2 - y1)/(x2 - x1).
Перетин із віссю Y (b):
перетин з віссю y (зазвичай позначається як b) – це значення y в точці, де лінія перетинає вісь y.
Рівняння прямої має вигляд y = mx + b. Якщо відомі значення m і b, можна обчислити будь-яку точку прямої, підставляючи до рівняння значення y або x. Також можна скористатися функцією TREND .Якщо є лише одна незалежна x-змінна, можна отримати значення нахилу та перетину з віссю y безпосередньо за допомогою таких формул:
Нахил:
=INDEX(LINEST(known_y's;known_x's);1)
Перетин із віссю Y:
=INDEX(LINEST(known_y's;known_x's);2)Точність апроксимації за допомогою прямої, обчисленої функцією LINEST, залежить від степеня розкиду даних. Чим ближчі дані до прямої, тим точніша модель, яка використовується функцією LINEST. Функція LINEST використовує метод найменших квадратів для визначення найкращої апроксимації даних. Якщо є лише одна незалежна x-змінна, m і b обчислюються за такими формулами:
де x і y - вибіркові середні; тобто x = AVERAGE(відомі значення_x), а y = AVERAGE(known_y's).Функції апроксимації кривої LINEST і LOGEST можуть обчислити найкращу пряму або експоненційну криву, яка відповідає вашим даним. Проте ви повинні вирішити, який із двох результатів найкраще відповідає вашим даним. Можна обчислити значення TREND(known_y, known_x) для прямої лінії або GROWTH (known_y, known_x) для експоненційної кривої. Ці функції без аргументу new_x повертають масив значень y, прогнозованих уздовж цієї лінії або кривої в фактичних точках даних. Потім можна порівняти прогнозовані значення з фактичними. Ви можете побудувати їх обидві, щоб наочно порівняти їх.
Здійснюючи регресійний аналіз, Excel обчислює для кожної точки квадрат різниці між прогнозованим значенням у та фактичним значенням у. Сума цих квадратів різниць називається залишковою сумою квадратів (ssresid). Потім Excel обчислює загальну суму квадратів (sstotal). Якщо аргумент конст = ІСТИНА або пропущений, загальна сума квадратів дорівнює сумі квадратів різниць між фактичними y-значеннями та середнім значень y. Якщо аргумент « конст» = ХИБНІСТЬ, загальна сума квадратів дорівнює сумі квадратів фактичних y-значень (без віднімання середнього значення y від кожного окремого значення y). Після цього регресійну суму квадратів (ssreg) можна обчислити таким чином: ssreg=sstotal-ssresid. Чим менше залишкова сума квадратів, тим більше значення коефіцієнта детермінованості r2, який показує, наскільки вдало рівняння, отримане в результаті регресійного аналізу, пояснює взаємозв'язок між змінними. Значення r2 дорівнює ssreg/sstotal.
У деяких випадках один або кілька стовпців X (припустимо, що стовпці Y і X знаходяться у стовпцях) можуть не мати додаткового прогнозованого значення за присутності інших стовпців X. Іншими словами, видалення одного або кількох стовпців X може призвести до однаково точних прогнозованих значень Y. У такому разі ці надлишкові стовпці X слід виключити з регресійної моделі. Це явище називається колінеарністю, оскільки будь-який надлишковий стовпець X можна виразити як суму, кратну ненадлишковим стовпцям X. Функція LINEST перевіряє колінеарність і видаляє всі зайві стовпці X із моделі регресії, коли виявляє їх. Видалені X стовпці можуть бути розпізнані у виводі LINEST як такі, що мають 0 коефіцієнтів на додаток до 0 значень se. Якщо один або кілька стовпців видаляються як зайві, це впливає на значення df, оскільки df залежить від кількості стовпців X, які фактично використовуються для прогнозування. Детальніше про обчислення df дивіться у прикладі 4. Якщо df змінено через видалення зайвих стовпців X, це також впливає на значення sey і F. Колінеарність на практиці зустрічається відносно рідко. Однак один із випадків, коли вона може виникнути, це коли деякі стовпці X містять лише значення 0 і 1 як індикатори того, чи є суб'єкт експерименту членом певної групи. Якщо константа = ІСТИНА або пропущена, функція LINEST ефективно вставляє додатковий стовпець Х з усіма значеннями 1, щоб змоделювати перетин. Якщо у вас є стовпець з 1 для кожного предмета, якщо чоловік, або 0, якщо ні, і у вас також є стовпець з 1 для кожного предмета, якщо жінка, або 0, якщо ні, то останній стовпець зайвий, оскільки записи в ньому можна отримати, віднявши запис у стовпці «індикатор чоловіка» від запису в додатковому стовпці всіх 1 значень, доданих функцією LINEST .
Значення df обчислюється наступним чином, коли з моделі не видаляються стовпці X через колінеарність: якщо є k стовпців known_x і const = TRUE або пропущено, df = n – k – 1. Якщо конст = ХИБНІСТЬ, df = n - k. В обох випадках видалення стовпців Х унаслідок колінеарності збільшує значення df на 1.
Вводячи константу масиву як, наприклад, known_x як аргумент , слід використовувати крапку з комою для розділення значень, які містяться в одному рядку, та двокрапку — для розділення рядків. Символи-роздільники можуть відрізнятися залежно від регіональних параметрів.
Зауважте, що y-значення, прогнозовані рівнянням регресії, можуть не бути припустимими, якщо вони перебувають поза межами діапазону y-значень, використаного для визначення рівняння.
Основний алгоритм, використовуваний у функції LINEST, відрізняється від основного алгоритму, використовуваного у функціях SLOPE і INTERCEPT. Відмінність між цими алгоритмами може призвести до отримання різних результатів, якщо дані є невизначеними і колінеарними. Наприклад, якщо точки даних аргументу known_y дорівнюють 0, а точки даних аргументу known_x дорівнюють 1:
- Функція LINEST повертає значення 0. Алгоритм функції LINEST створено, щоб повертати придатні результати для колінеарних даних, і в такому випадку можна знайти принаймні одну відповідь.
- Функції SLOPE і INTERCEPT повертають #DIV/0! помилку #REF!. Алгоритм функцій SLOPE і INTERCEPT розрахований на пошук тільки однієї відповіді, а в цьому випадку відповідей може бути кілька.
Крім того, що функцію LOGEST можна використати для обчислення статистики для інших типів регресії, функцію LINEST можна використати для обчислення діапазону інших типів регресії за допомогою введення функцій змінних x та y як рядів x та y для функції LINEST. Наприклад, така формула:
=LINEST(y-значення; x-значення^COLUMN($A:$C))
працює, якщо є окремий стовпець y-значень і окремий стовпець x-значень для обчислення кубічного (поліноміального 3-го порядку) наближення форми:
y = m1*x + m2*x^2 + m3*x^3 + b
Цю формулу можна змінити для обчислення інших типів регресії, але в деяких випадках буде потрібно змінити результати та іншу статистику.Значення F-test, яке повертає функція LINEST, відрізняється від значення F-test, яке повертає функція FTEST1. LINEST повертає статистику F, у той час як FTEST1 повертає ймовірність.
Приклади
Приклад 1. Нахил і Y-перетин
Скопіюйте дані прикладу з наведеної нижче таблиці та вставте їх у клітинку A1 нового аркуша Excel. Щоб відобразити результат обчислення формул, виберіть їх, натисніть клавішу F2, а потім – клавішу Enter. За потреби можна змінити ширину стовпців, щоб відобразити всі дані.
| Відоме значення Y | Відоме значення X |
|---|---|
| 1 | 0 |
| 9 | 4 |
| 5 | 2 |
| 7 | 3 |
| Результат (нахил) | Результат (перетин з віссю Y) |
| 2 | 1 |
| Формула (формула масиву у клітинках A7:B7) | |
| =LINEST(A2:A5,B2:B5,,FALSE) |
Приклад 2. Проста лінійна регресія
Скопіюйте дані прикладу з наведеної нижче таблиці та вставте їх у клітинку A1 нового аркуша Excel. Щоб відобразити результат обчислення формул, виберіть їх, натисніть клавішу F2, а потім – клавішу Enter. За потреби можна змінити ширину стовпців, щоб відобразити всі дані.
| Місяць | Продажі |
|---|---|
| 1 | 3 100 грн. |
| 2 | 4 500 грн. |
| 3 | 4 400 грн. |
| 4 | 5 400 грн. |
| 5 | 7 500 грн. |
| 6 | 8 100 грн. |
| Формула | Результат |
| =SUM(LINEST(B1:B6; A1:A6)*{9,1}) | 11 000 грн. |
| Обчислює орієнтовну суму виручки в дев’ятому місяці з урахуванням продажів із першого по шостий місяці. |
Приклад 3. Множинна лінійна регресія
Скопіюйте дані прикладу з наведеної нижче таблиці та вставте їх у клітинку A1 нового аркуша Excel. Щоб відобразити результат обчислення формул, виберіть їх, натисніть клавішу F2, а потім – клавішу Enter. За потреби можна змінити ширину стовпців, щоб відобразити всі дані.
| Площа (x1) | Кількість офісів (x2) | Кількість входів (x3) | Час експлуатації (x4) | Оціночна вартість (y) |
|---|---|---|---|---|
| 2310 | 2 | 2 | 20 | 142 000 грн. |
| 2333 | 2 | 2 | 12 | 144 000 грн. |
| 2356 | 3 | 1,5 | 33 | 151 000 грн. |
| 2379 | 3 | 2 | 43 | 150 000 грн. |
| 2402 | 2 | 3 | 53 | 139 000 грн. |
| 2425 | 4 | 2 | 23 | 169 000 грн. |
| 2448 | 2 | 1,5 | 99 | 126 000 грн. |
| 2471 | 2 | 2 | 34 | 142 900 грн. |
| 2494 | 3 | 3 | 23 | 163 000 грн. |
| 2517 | 4 | 4 | 55 | 169 000 грн. |
| 2540 | 2 | 3 | 22 | 149 000 грн. |
| -234,2371645 | ||||
| 13,26801148 | ||||
| 0,996747993 | ||||
| 459,7536742 | ||||
| 1732393319 | ||||
| Формула (формула динамічного масиву, введена у клітинці A19) | ||||
| =LINEST(E2:E12,A2:D12,TRUE,TRUE) |
Приклад 4. Використання статистик F і r2
У попередньому прикладі коефіцієнт детермінованості, або r2, дорівнює 0,99675 (див. клітинку A17 у результатах функції LINEST), що вказує на сильний взаємозв'язок між незалежними змінними та ціною продажу. Можна використовувати F-статистику, щоб визначити, чи є ці результати (з таким високим значенням r2) випадковими.
Вважаємо, що насправді немає залежності між змінними, але відображено винятковий випадок створення вибірки з 11 адміністративних будівель, для якого статистичний аналіз свідчить про високу залежність. Елемент "Альфа" використовується на позначення ймовірності помилкового висновку про те, що така залежність є.
Значення F і df у вихідних даних функції LINEST можна використовувати для оцінки ймовірності випадкового виникнення вищого значення F. F можна порівняти з критичними значеннями в опублікованих таблицях F-розподілу або скористатися функцією FDIST в Excel, щоб випадково обчислити ймовірність випадкової появи більшого F-значення. Відповідний F-розподіл має ступені свободи v1 і v2. Якщо n – це кількість точок даних, а const = TRUE (істина) або не вказано, тоді v1 = n – df – 1 і v2 = df. (Якщо конст = ХИБНІСТЬ, тоді v1 = n – df і v2 = df.) Функція FDIST із синтаксисом FDIST(F;v1;v2) повертає ймовірність випадкового виникнення більшого значення F. У цьому прикладі df = 6 (клітинка B18) і F = 459,753674 (клітинка A18).
Якщо значення Альфа дорівнює 0,05, v1 = 11 – 6 – 1 = 4 і v2 = 6, критичний рівень F дорівнює 4,53. Оскільки F = 459,753674 набагато вище, ніж 4,53, вкрай малоймовірно, що таке високе значення F сталося випадково. (При Альфа = 0,05 гіпотеза про відсутність залежності між known_y і known_x повинна бути відкинута, коли F перевищить критичний рівень (4,53). В Excel можна використовувати функцію FDIST , щоб визначити ймовірність того, що таке високе значення F сталося випадково. Наприклад, FDIST(459,753674, 4, 6) = 1,37E-7, надзвичайно мала ймовірність. Знайшовши критичний рівень F у таблиці або скориставшись функцією FDIST , можна зробити висновок, що рівняння регресії корисне для прогнозування оціночної вартості адміністративних будівель у цьому районі. Пам'ятайте, що дуже важливо використовувати правильні значення v1 і v2, обчислені в попередньому параграфі.
Приклад 5. Обчислення t-статистики
Інша перевірка припущення визначить, чи кожний коефіцієнт нахилу допомагає в оціненні вартості адміністративної будівлі у прикладі 3. Наприклад, щоб визначити статистичну значність коефіцієнту часу експлуатації, поділіть -234,24 (коефіцієнт нахилу терміну) на 13,268 (очікувана стандартна помилка коефіцієнтів терміну у клітинці A15). Далі подано значення спостережуваного t:
t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7
Якщо абсолютне значення t досить високе, можна зробити висновок, що коефіцієнт нахилу допомагає в оціненні вартості адміністративної будівлі у прикладі 3. У поданій нижче таблиці наведено абсолютні значення для 4 значень спостережуваного t.
Звернувшись до таблиць у довіднику з математичної статистики, можна дізнатися, що t-критичне двобічне з 6 степенями вільності та Альфа = 0,05 дорівнює 2,447. Це критичне значення можна також знайти за допомогою функції TINV у програмі Excel. TINV(0,05;6) = 2,447. Оскільки абсолютна величина t, яка дорівнює 17,7, більше за 2,447, час експлуатації є важливою змінною для оцінки вартості адміністративної будівлі. Аналогічно можна визначити статистичну значимість усіх інших змінних. Нижче наведено спостережувані значення t для кожної з незалежних змінних.
| Змінна | Значення спостережуваного t |
|---|---|
| Площа | 5,1 |
| Кількість офісів | 31,3 |
| Кількість входів | 4,8 |
| Час експлуатації | 17,7 |
Всі ці значення мають абсолютне значення, яке більше за 2,447; тому всі змінні, використані в рівнянні регресії, мають значення для прогнозування оціночної вартості адміністративних будівель у цьому районі.