Функція LINEST (ЛИНЕЙН)
- Назва в Excel
LINEST- У російській версії
ЛИНЕЙН- Категорія
- Статистичні
Функція LINEST (у російській версії Excel — ЛИНЕЙН) обчислює статистику для ряду за методом найменших квадратів, щоб знайти пряму, яка найкраще апроксимує наявні дані, і повертає масив, що описує цю пряму.
Назви функцій подано англійською, як в українському Excel. Скриншоти й книги-приклади — з російської версії.
Опис функції LINEST
Функція LINEST обчислює статистику для ряду за методом найменших квадратів, щоб знайти пряму, яка найкраще апроксимує наявні дані, і повертає масив, що описує цю пряму.
Функцію LINEST також можна поєднувати з іншими функціями для побудови моделей, лінійних щодо невідомих параметрів, зокрема поліноміальних, логарифмічних, експоненційних і степеневих. Оскільки повертається масив значень, цю функцію потрібно вводити як формулу масиву.
Рівняння для прямої має вигляд:
Синтаксис
=LINEST(відомі_значення_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.
- Якщо аргумент конст має значення TRUE або опущено, константа b обчислюється звичайним чином.
- Якщо аргумент конст має значення FALSE, то b приймається рівним 0, а коефіцієнти m підбираються так, щоб виконувалося співвідношення y = mx.
статистика
Необов'язковий аргумент. Логічне значення, яке вказує, чи повертати додаткову регресійну статистику.
- Якщо аргумент статистика має значення TRUE, функція LINEST повертає додаткову регресійну статистику. Повернутий масив матиме такий вигляд: {mn;mn-1;…;m1;b:sen;sen-1;…;se1;seb:r2;sey:F;df:ssreg;ssresid}.
- Якщо аргумент статистика має значення FALSE або опущено, функція LINEST повертає лише коефіцієнти m і константу b.
Додаткова регресійна статистика:
| Величина | Опис |
|---|---|
| se1,se2,…,sen | Стандартні похибки для коефіцієнтів m1,m2,…,mn. |
| seb | Стандартна похибка для константи b (seb = #N/A, якщо аргумент конст має значення FALSE). |
| r2 | Коефіцієнт детермінації. Порівнюються фактичні значення y і значення, оцінені за рівнянням прямої; на основі цього обчислюється коефіцієнт детермінації, нормований від 0 до 1. Якщо він дорівнює 1, модель повністю пояснює спостережувані зміни y. Якщо ж коефіцієнт дорівнює 0, використання рівняння регресії для прогнозування y не має сенсу. |
| sey | Стандартна похибка для оцінки y. |
| F | F-статистика або спостережуване значення F. F-статистика використовується для визначення, чи є спостережуваний зв'язок між залежною та незалежними змінними випадковим. |
| df | Ступені свободи. Ступені свободи корисні для знаходження критичних значень F у статистичних таблицях. Щоб оцінити надійність моделі, порівняйте значення в таблиці з F-статистикою, яку повертає функція LINEST. |
| ssreg | Регресійна сума квадратів. |
| ssresid | Залишкова сума квадратів. Детальніше про обчислення ssreg і ssresid див. у розділі «Зауваження» в кінці цієї статті. |
На рисунку нижче показано порядок повернення додаткової регресійної статистики:

Зауваження
- Будь-яку пряму можна описати її нахилом і перетином з віссю y:
Нахил (m):
щоб визначити нахил прямої, зазвичай позначуваний як m, слід взяти дві точки прямої (x1,y1) і (x2,y2); нахил дорівнюватиме (y2 — y1)/(x2 — x1).Y-перетин (b):
Y-перетином прямої, зазвичай позначуваним як b, є значення y у точці, в якій пряма перетинає вісь y.Рівняння прямої має вигляд
Якщо відомі значення m і b, можна обчислити будь-яку точку на прямій, підставивши значення y або x у рівняння. Також можна скористатися функцією TREND.y = mx + b - Якщо є лише одна незалежна змінна x, можна одержати нахил і y-перетин безпосередньо за допомогою таких формул:
Нахил:
=INDEX(LINEST(відомі_значення_y;відомі_значення_x);1)Y-перетин:
=INDEX(LINEST(відомі_значення_y;відомі_значення_x);2) - Точність апроксимації за допомогою прямої, обчисленої функцією LINEST, залежить від ступеня розкиду даних. Чим ближчі дані до прямої, тим точнішою є модель, яку використовує функція. LINEST застосовує для знаходження найкращої апроксимації метод найменших квадратів. Коли є лише одна незалежна змінна x, значення m і b обчислюються за такими формулами:

де: $$\overline{x}, \overline{y}$$ — вибіркові середні значення, наприклад:
аx = AVERAGE(відомі_значення_x)y = AVERAGE(відомі_значення_y) - Функції апроксимації LINEST і LOGEST дозволяють обчислити пряму або експоненційну криву, що найкраще описує дані. Проте вони не відповідають на питання, який із двох результатів краще підходить для вашого завдання. Також можна обчислити функцію
для прямої або функціюTREND(відомі_значення_y; відомі_значення_x)
для експоненціальної кривої. Ці функції, якщо не задавати аргумент новые_значения_x, повертають масив обчислених значень y для фактичних значень x відповідно до прямої або кривої. Після цього можна порівняти обчислені значення з фактичними. Також можна побудувати діаграми для візуального порівняння.GROWTH(відомі_значення_y; відомі_значення_x) - Проводячи регресійний аналіз, Microsoft Excel обчислює для кожної точки квадрат різниці між прогнозованим значенням y і фактичним значенням y. Сума цих квадратів різниць називається залишковою сумою квадратів (ssresid).
Потім Microsoft Excel підраховує загальну суму квадратів (sstotal). Якщо конст = TRUE або значення цього аргумента не вказано, загальна сума квадратів дорівнює сумі квадратів різниць фактичних значень y і середнього значення y. При конст = FALSE загальна сума квадратів дорівнює сумі квадратів фактичних значень y (без віднімання середнього значення y від окремого значення y). Після цього регресійну суму квадратів можна обчислити так: ssreg = sstotal — ssresid. Чим менша залишкова сума квадратів, тим більше значення коефіцієнта детермінації r2, який показує, наскільки добре рівняння, отримане в результаті регресійного аналізу, пояснює зв'язок між змінними. Коефіцієнт r2 дорівнює відношенню ssreg/sstotal. - У деяких випадках один або кілька стовпців X (припустімо, значення Y і X знаходяться в стовпцях) не впливають на результати за наявності інших стовпців X. Іншими словами, видалення одного або кількох стовпців X може призвести до обчислення значень Y з такою ж точністю. У цьому випадку надмірні стовпці X будуть виключені з моделі регресії. Це явище називається колінеарністю, бо надмірні стовпці X можуть бути представлені як сума декількох незалежних стовпців.
LINEST перевіряє на колінеарність і видаляє з моделі регресії всі надмірні стовпці X, якщо виявляє їх. Видалені стовпці X можна визначити у вихідних даних LINEST за коефіцієнтом, що дорівнює 0, та за значенням se, що дорівнює 0. Видалення одного або кількох стовпців як надмірних змінює величину df, оскільки вона залежить від кількості стовпців X, фактично використаних для прогнозування.
При зміні df внаслідок видалення надмірних стовпців значення sey і F також змінюються. Часто використовувати колінеарність не рекомендується. Однак її слід застосувати, якщо деякі стовпці X містять 0 або 1 як індикатор входження об'єкта експерименту в певну групу. Якщо конст = TRUE або значення цього аргумента не вказано, LINEST вставляє додатковий стовпець X для моделювання точки перетину. Якщо є стовпець зі значеннями 1 для позначення чоловіків і 0 — для жінок, а також стовпець зі значеннями 1 для позначення жінок і 0 — для чоловіків, то останній стовпець буде видалений, оскільки його значення можна отримати зі стовпця з «індикатором статі».
- Обчислення значення df для випадків, коли стовпці X видаляються з моделі внаслідок колінеарності, відбувається так: якщо існує k стовпців відомі_значення_x і конст = TRUE або не вказано, то df = n – k – 1. Якщо конст = FALSE, то df = n — k. В обох випадках видалення стовпців X внаслідок колінеарності збільшує значення df на 1.
- Формули, що повертають масиви, повинні вводитися як формули масиву.
- При введенні константи масиву (наприклад, як аргумент відомі_значення_x) слід використовувати крапку з комою для розділення значень в одному рядку і двокрапку для розділення рядків. Роздільники можуть відрізнятися залежно від регіональних параметрів.
Слід зазначити, що значення y, передбачені за допомогою рівняння регресії, можуть бути некоректними, якщо вони знаходяться поза інтервалом значень y, які використовувалися для визначення рівняння.
Основний алгоритм, який використовує LINEST, відрізняється від основного алгоритму функцій SLOPE і INTERCEPT. Різниця між алгоритмами може призвести до різних результатів при невизначених і колінеарних даних. Наприклад, якщо точки даних аргументу відомі_значення_y рівні 0, а точки даних аргументу відомі_значення_x рівні 1, то:
- LINEST повертає значення, рівне 0. Алгоритм LINEST призначений для повернення придатних значень для колінеарних даних і в цьому випадку може існувати щонайменше одна відповідь.
- Функції SLOPE і INTERCEPT повертають помилку #DIV/0!. Алгоритм функцій 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-тесту, що повертається LINEST, відрізняється від значення, яке повертає функція FTEST. LINEST повертає F-статистику, тоді як FTEST повертає ймовірність.