Завантажте зразок робочої книги
Цей підручник демонструє, як користуватися Функція Excel LINEST в Excel для розрахунку статистики про лінію тренда.
Огляд функції LINEST
Функція LINEST Обчислює статистику лінії тренду, встановленої для відомих точок даних, використовуючи метод найменших квадратів.
Щоб скористатися функцією LINEST Excel Worksheet, виберіть клітинку та введіть:
(Зверніть увагу, як виглядають вхідні дані формул)
Функція LINEST Синтаксис та входи
| 1 | = LINEST (відомий_ys, відомий_xs, const, статистика) |
відомі_y - Масив відомих значень Y.
відомі_х - Масив відомих значень X.
const - НЕОБОВ'ЯЗКОВО. Логічне значення, яке вказує, чи потрібно обчислювати B (перехоплення у y = mx + b) за допомогою методу найменших квадратів (TRUE або Ommit) або вручну встановити B = 0 (FALSE).
статистика - НЕОБОВ'ЯЗКОВО. Повернути додаткову статистику (TRUE) або повернути лише m (нахил) і b (перехоплення) (FALSE або Опущено)
Що таке LINEST?
Функція LINEST в Excel - це функція, що використовується для створення статистики регресії для лінійної регресійної моделі. LINEST - це формула масиву, яку можна використовувати окремо або з іншими функціями для обчислення конкретної статистики щодо моделі.
Лінійна регресія - це метод статистики, який використовується для прогнозування даних за прямою лінією з використанням відомих даних. Регресія використовується для прогнозування таких значень, як зростання продажів, вимоги до запасів або прості тенденції на ринку.
LINEST схожий на ПРОГНОЗ у тому, що він досягає аналогічних результатів, але з набагато більшою інформацією про вашу модель регресії, а також можливістю розміщення більш ніж однієї незалежної змінної.
Припустимо, у мене є таблиця даних з x та y значення де x є незалежною змінною та y є залежною змінною:
Я хочу знати, що таке рівняння регресії наведених вище даних. Використовуючи LINEST:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Значення перехоплення y тут еквівалентно 0, у наукових позначеннях.
Рівняння прямої дорівнює y = 2x + 0. Зауважте, що LINEST повертається обидва нахил і перехоплення лінії. Щоб повернути обидва значення, формулу необхідно ввести як формулу масиву. Детальніше про формули масиву пізніше.
Як користуватися LINEST
Функція LINEST приймає чотири аргументи:
| 1 | = LINEST (відомі_y, відомі_x, const, статистика) |
Де,
| Аргумент | Опис |
| відомі_y та відомі_х | Чи є x та y дані у вашій таблиці даних |
| const | Опція TRUE/FALSE для того, щоб перехоплення y примусово дорівнювало 0 або нормально обчислювалося |
| статистика | Опція TRUE/FALSE, чи потрібно повертати додаткову статистику регресії |
Використовуючи наш перший приклад, функція записується так:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Коли статистика для параметра TRUE, організація регресійної статистики виглядає наступним чином:
Вам може бути цікаво, що означає кожна змінна.
| Статистичні | Опис |
| мn | Коефіцієнти нахилу для x змінних |
| b | y-перехоплення |
| sen | Стандартна помилка для кожного коефіцієнта нахилу |
| seb | Стандартна помилка для y-перехоплення |
| r2 | Коефіцієнт детермінації |
| sey | Стандартна помилка для y кошторис |
| F | Статистика F (для визначення випадковості взаємозв’язку змінних) |
| df | Ступені свободи |
| ssreg | Регресійна сума квадратів |
| ssрезидент | Залишкова сума квадратів |
Основними статистичними даними для розуміння є коефіцієнти нахилу, y-перехоплення та коефіцієнт детермінації або r2 цінність моделі.
Використовуючи наведений вище приклад і вибравши ІСТИНУ для статистика параметр:
Виділені клітинки показують нахил = 2, перехоплення = 0 і r2 = 1.
The r2 значення є показником міцності кореляції моделі. Його можна розглядати як показник придатності. Низький r2 значення означало б погану кореляцію між вашими залежними та незалежними змінними, а навпаки вірно для високого r2 значення, з r2 = 1 ідеально підходить.
У випусках Excel за січень 2022 р. У Microsoft 365 (раніше Office 365) динамічні масиви змінили спосіб оцінки формул масивів. Більше не потрібно використовувати CTRL + SHIFT + ENTER або виділяти область клітинок, які буде займати масив. Просто введіть формулу та натисніть клавішу Enter, і отримані клітинки «виллються» в масив.
У решті цієї статті ми будемо посилатися на використання LINEST щодо динамічних масивів у Microsoft 365 Excel.
Прогнозування з LINEST (проста регресія)
Поєднання функцій LINEST і SUM можна передбачити значення залежної змінної y, з огляду на відоме x та y дані. Нижче наведено приклад, який показує, що таке y значення буде коли x = 14.
| 1 | = SUM (LINEST (C3: C7, B3: B7)*{14,1}) |
Модель має вигляд y = mx + b. Це те саме, що y = a+ bx, тільки інший спосіб представити рівняння. Порада, яку слід мати на увазі для лінійних рівнянь, - це змінна поруч x завжди є нахилом, а змінна, що йде за знаком плюс або мінус, завжди є перехопленням, незалежно від літер, використаних у рівнянні.
Використовуючи формулу: = SUM (LINEST (C3: C7, B3: B7)*{14,1}) повертає результат 28. Оскільки це єдиний результат, вводити його як масив не потрібно.
Хвіст наведеної вище формули *{14,1} визначає незалежну змінну, яка буде використовуватися для прогнозування залежної змінної, в даному випадку 14.
Ми можемо перевірити це, ввівши x = 14 у рівняння рядка, y = 2x + 0.
Прогнозування з LINEST (множинна лінійна регресія)
Нижче наведена таблиця даних зі сторінки LINEST веб -сайту служби підтримки Microsoft.
У деяких випадках існує більше однієї незалежної змінної, яку слід враховувати при створенні моделі лінійної регресії. Це називається множинною лінійною регресією (тобто множинними незалежними змінними). Якщо я хочу оцінити вартість офісної будівлі, такі речі, як площа приміщення, кількість входів у будівлю, вік будівлі та кількість офісів, будуть частиною рівняння. Розглянемо приклад.
Ввівши формулу LINEST у комірку G29 та виконавши її, ми отримаємо:
| 1 | = LINEST (E3: E13, A3: D13, TRUE, TRUE) |
Модель випускається у такому вигляді:
Пам’ятайте, що масив результатів LINEST знаходиться у зворотному порядку від рівняння. У наведеному вище прикладі 52 317,8 - це наше перехоплення, b, а 27,6 - наше m1 або значення нахилу для змінної Floor space, x1.
Використовуючи функцію LINEST з наданими даними, наша модель регресії виглядає так:
З r2 значення 0,997, що вказує на сильну або високо корельовану модель. За допомогою моделі тепер можна передбачити, що оцінювана вартість офісної будівлі ґрунтуватиметься на будь -якій комбінації вищезазначених незалежних змінних.
НАЙКРАЩІ Поради
- Переконайтеся, що у вас найновіша версія Microsoft 365 для використання LINEST з динамічними масивами. Можливо, вам доведеться ввімкнути поточний канал Office Insider (попередній перегляд) для використання функцій динамічного масиву. На сторінці облікового запису:
- Якщо ви перебуваєте у випуску, що не належить до Microsoft 365, вам доведеться використовувати застарілий метод CTRL + SHIFT + ENTER (CSE) для оцінки формул масиву.
- Якщо використовується застарілий метод, кількість стовпців, які слід виділити при введенні функції масиву LINEST, завжди є числом x змінних у ваших даних плюс 1. Кількість рядків для вибору масиву - 5.
- Якщо ви будете надсилати свою версію Excel із підтримкою динамічного масиву з кимось, хто використовує версію, що не є Microsoft 365, використовуйте застарілий метод CSE, щоб уникнути проблем із сумісністю.
Цікавить більше прогнозування?
Дивіться інші наші статті про прогнозування з експоненціальним згладжуванням, функції TREND, GROWTH та LOGEST.
LINEST функцію в Google Таблицях
Функція LINEST працює точно так само в Google Таблицях, як і в Excel.
НАЙКРАЩІ Приклади у VBA
Ви також можете використовувати функцію LINEST у VBA. Тип:application.worksheetfunction.linest (відомий_ys, відомий_xs, const, статистика)
Виконання наступного оператора VBA
| 1 | Діапазон ("D2") = Application.WorksheetFunction.LinEst (Діапазон ("A2: A8"), Діапазон ("B2: B8")) |
дасть такі результати
Для аргументів функції (відомі_y та ін.) Ви можете або ввести їх безпосередньо у функцію, або визначити змінні, які потрібно використовувати замість них.
Повернення до списку всіх функцій Excel
