LINEST (функція LINEST)

У цій статті наведено синтаксис формули та описано, як у програмі 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 має такі аргументи:

Синтаксис

  • Відомі_значення_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 і фактичним значенням y немає. Якщо коефіцієнт визначення дорівнює 0, рівняння регресії не корисно для прогнозування y-значення. Щоб отримати відомості про те, як обчислюється2 , перегляньте розділ "зауваження", а потім у цій статті.

sey

Стандартна помилка для очікуваного y

F

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

df

Степені вільності. За допомогою степенів вільності можна знаходити значення F-критичних значень у статистичній таблиці. Порівняйте значення, які ви знайшли в таблиці, до статистики F, повернутих за допомогою LINEST , щоб визначити рівень достовірності для моделі. Щоб отримати відомості про те, як обчислюється DF, перегляньте розділ "зауваження", а потім у цій статті. У прикладі 4 показано використання F і DF.

ssreg

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

ssresid

Залишкова сума квадратів. Щоб отримати відомості про те, як буде обчислено ssreg і ssresid, перегляньте розділ "зауваження", а потім у цій статті.

На наведеному нижче рисунку показано порядок повернення статистики додаткової регресії.

Ключ до статистики регресії

Примітки

  • Можна описати будь-яку пряму лінію за допомогою нахилу та перетину з віссю y:

    Нахил (m):
    Щоб відшукати нахил лінії, часто написану як m, візьміть дві точки на лінії, (X1, Y1) і (X2, Y2); Нахил дорівнює (Y2-Y1)/(X2-X1).

    Y-перетин (b):
    Координата y-перетин лінії, що часто пишеться як b, – значення y в точці, де лінія перетинає вісь y.

    Формула прямої лінії y = mx + b. Після того як ви знаєте значення m і b, ви можете обчислити будь-яку точку в рядку, підключивши значення "y-or x" до цього рівняння. Також можна скористатися функцією Trend .

  • Якщо є лише одна незалежна x-змінна, можна отримати значення нахилу та перетину з віссю y безпосередньо за допомогою таких формул:

    Нахил:
    = INDEX (LINEST (known_y, known_x ' s); 1)

    У-перехоплювати
    : = INDEX (LINEST (known_y, known_x ' s); 2)

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

    Формула

    Формула

    де x і y – вибіркові середні значення, тобто x = AVERAGE(відомі_значення_x), а y = AVERAGE(відомі_значення_y).

  • Функції "лінійний" та "крива" LINEST і LOGEST можуть обчислити найкращу пряму лінію або експоненційну криву, яка відповідає вашим даним. Однак, ви повинні вирішити, які з двох результатів найкращим чином відповідають вашим даним. Ви можете обчислити Trend (known_y, known_x) для прямої лінії або зростання (known_y, known_x) для експоненційної кривої. Ці функції без аргументу new_x повертають масив y-значень, які прогнозуються вздовж цього рядка або кривої на фактичних точках даних. Потім можна порівняти Прогнозовані значення з фактичними значеннями. Можливо, ви захочете виконати діаграму для візуального порівняння.

  • Здійснюючи регресійний аналіз, Excel обчислює для кожної точки квадрат різниці між прогнозованим значенням у та фактичним значенням у. Сума цих квадратів різниць називається залишковою сумою квадратів (ssresid). Потім Excel обчислює загальну суму квадратів (sstotal). Якщо аргумент конст приймає значення TRUE (істина) або цей аргумент не вказано, загальна сума квадратів дорівнюватиме сумі квадратів різниць між фактичними значеннями y та середніми значеннями у. Якщо аргумент конст приймає значення FALSE (хибність), загальна сума квадратів дорівнюватиме сумі квадратів фактичних значень у (без віднімання середнього значення у від кожного окремого значення у). Після цього регресійну суму квадратів (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-стовпці видаляються, також впливають на кількість значень Сі і ф. «Колінійність» має бути відносно рідкісним на практиці. Проте в одному випадку, коли деякі стовпці X містять лише значення 0 та 1, як індикатори того, що предмет експерименту є або не є учасником певної групи. Якщо аргумент істина = TRUE або пропущений, функція LINEST ефективно вставляє додатковий стовпець X на всі 1 значення, щоб моделювати перехоплення. Якщо у вас є стовпець з 1 для кожної теми, якщо чоловік, або 0, якщо ні, а також у вас є стовпець із 1 для кожної теми, якщо жінка або 0, якщо ні, це останній стовпець є зайвим, оскільки записи в ньому можна отримати від віднімання запису в стовпці "чоловічий індикатор" від запису в додатковому стовпці всіх значень 1, доданих функцією LINEST .

  • Для випадків, коли стовпці Х не видаляються з моделі внаслідок колінеарності, значення df обчислюється таким чином: якщо існує k стовпців аргументу відомі_значення_x і аргумент конст має значення TRUE (істина), або цей аргумент не вказано, то df=n-k-1. Якщо аргумент конст має значення FALSE (хибність), то df=n-k. В обох випадках видалення стовпців Х унаслідок колінеарності збільшує значення df на 1.

  • Вводячи константу масиву як, наприклад, аргумент відомі_значення_x, слід використовувати крапку з комою для розділення значень, які містяться в одному рядку, та двокрапку — для розділення рядків. Символи-роздільники можуть відрізнятися залежно від регіональних параметрів.

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

  • Основний алгоритм, використовуваний у функції LINEST, відрізняється від основного алгоритму, використовуваного у функціях SLOPE і INTERCEPT. Відмінність між цими алгоритмами може призвести до отримання різних результатів, якщо дані є невизначеними і колінеарними. Наприклад, якщо точки даних аргументу відомі_значення_y дорівнюють 0, а точки даних аргументу відомі_значення_x дорівнюють 1:

    • Функція LINEST повертає значення 0. Алгоритм функції LINEST створено, щоб повертати придатні результати для колінеарних даних, і в такому випадку можна знайти принаймні одну відповідь.

    • Нахил і перехоплення повертають #DIV/0! помилку #REF!. Алгоритм нахилу та перехоплення має шукати лише одну відповідь, і в цьому випадку може бути кілька відповідей.

  • Крім того, що функцію 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), що вказує на сильнеспіввідношення між незалежними змінними та ціною збуту. Ви можете використовувати F-статистика, щоб визначити, чи є ці результати, за допомогою такого високого значення R2, яке сталося випадково.

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

Значення F та DF у випуску з функції LINEST можна використовувати, щоб оцінити ймовірність збільшення значення F, що відбувається випадково. F можна порівняти з критичними значеннями у опублікованих таблицях розподілу F або функції FDIST у програмі Excel, які можна використовувати для обчислення ймовірності більшого значення F, що відбувається випадково. Відповідний F-розподіл має кількість V1 і V2 степенів вільності. Якщо n – кількість точок даних і константа = TRUE або пропущений, то V1 = n – DF – 1 і V2 = DF. (Якщо константа = FALSE, а потім V1 = n – DF та V2 = DF); Функція FDIST – за допомогою синтаксису FDIST(F, V1, V2) – поверне ймовірність вищого значення F, що відбувається випадково. У цьому прикладі DF = 6 (стільникова B18) і F = 459,753674 (стільникова автомагістралі А18).

Припускаючи, що альфа-значення 0,05, V1 = 11 – 6 – 1 = 4 і V2 = 6, критичний рівень F – 4,53. Оскільки F = 459,753674 є ще більшим, ніж 4,53, це дуже малоймовірно, що це високе значення. (З альфа = 0,05, гіпотеза про те, що зв'язок між known_y і known_x має бути відхилено, якщо F перевищує критичний рівень, 4,53.) Функцію FDIST можна використовувати в програмі Excel, щоб отримати ймовірність того, що це високе значення, яке відбулося в цьому випадку. Наприклад, FDIST(459,753674; 4; 6) = 1.37 e-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; тому всі змінні, використані в рівнянні регресії, мають значення для прогнозування оціночної вартості адміністративних будівель у цьому районі.

Примітка.:  Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була ця інформація корисною. Для довідки цю статтю можна переглянути англійською мовою.

Потрібна додаткова довідка?

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×