Изтеглете примерната работна книга
Този урок демонстрира как да използвате Функция Excel LINEST в Excel за изчисляване на статистически данни за линията на тренда.
LINEST Функция Преглед
Функцията LINEST Изчислява статистика за линия на тренда, монтирана към известни точки от данни, използвайки метода на най -малките квадрати.
За да използвате функцията LINEST Excel на работен лист, изберете клетка и въведете:
(Забележете как се появяват входовете на формулата)
LINEST функция Синтаксис и входове
| 1 | = LINEST (известен_ys, известен_xs, const, статистика) |
известен_y's - Масив от известни Y стойности.
известни_х - Масив от известни X стойности.
const - ОПЦИОНАЛНО. Логическа стойност, показваща дали да се изчисли B (прихващането в y = mx + b), като се използва методът на най -малките квадрати (TRUE или Ommit) или да се зададе ръчно B = 0 (FALSE).
статистика - ОПЦИОНАЛНО. Връща допълнителна статистика (TRUE) или само m (наклон) и b (прихващане) (FALSE или пропуснато)
Какво е LINEST?
Функцията LINEST в Excel е функция, използвана за генериране на регресионна статистика за линеен регресионен модел. LINEST е формула на масив и може да се използва самостоятелно или с други функции за изчисляване на специфични статистически данни за модела.
Линейната регресия е метод в статистиката, използван за прогнозиране на данни след права линия, използвайки известни данни. Регресията се използва за прогнозиране на стойности като растеж на продажбите, изисквания за запаси или прости пазарни тенденции.
LINEST е подобно на ПРОГНОЗАТА с това, че постига подобен резултат, но с много повече информация за вашия модел на регресия, както и опцията за побиране на повече от една независима променлива.
Да предположим, че имам таблица с данни с х и y стойности където х е независимата променлива и y е зависимата променлива:
Искам да знам какво е уравнението на регресията на горните данни. Използване на LINEST:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Стойността на y-прихващане тук е еквивалентна на 0, в научна нотация.
Уравнението на линията е y = 2x + 0. Обърнете внимание, че LINEST се връща и двете наклона и прихващането на линията. За да се върнат и двете стойности, формулата трябва да бъде въведена като формула на масив. Повече за формулите на масива по -късно.
Как да използвате LINEST
Функцията LINEST приема четири аргумента:
| 1 | = LINEST (известни_y's, known_x's, const, статистика) |
Където,
| Аргумент | Описание |
| известен_y's и известни_х | Дали е х и y данни във вашата таблица с данни |
| const | Опция TRUE/FALSE за това дали y-прихващането трябва да бъде принудено на 0 или да се изчисли нормално |
| статистика | Опция TRUE/FALSE дали трябва да се върнат допълнителни статистически данни за регресия |
Използвайки първия ни пример, функцията е написана като:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Когато статистика опцията е зададена на TRUE, организацията на статистиката за регресия е, както следва:
Може би се чудите какво означава всяка променлива.
| Статистически | Описание |
| мн | Коефициенти на наклон за x променливи |
| б | y-прихващане |
| сен | Стандартна грешка за всеки коефициент на наклона |
| себ | Стандартна грешка за y-прихващане |
| r2 | Коефициент на детерминация |
| сеy | Стандартна грешка за y оценка |
| F | F статистиката (за да се определи дали връзката на променливите възниква случайно) |
| де | Степени на свобода |
| ssрег | Регресионна сума от квадрати |
| ssостатък | Остатъчна сума от квадрати |
Основните статистически данни, които трябва да се разберат, са коефициентите на наклона, y-прихващането и коефициентът на определяне или r2 стойността на модела.
Използвайки горния пример и избирайки ИСТИНА за статистика параметър:
Осветените клетки показват наклон = 2, прихващане = 0 и r2 = 1.
The r2 стойността е показател за силата на корелацията на модела. Може да се разглежда като индикатор за годност. Нисък r2 стойността би означавала лоша корелация между вашите зависими и независими променливи, а обратното е вярно за високо r2 стойности, с r2 = 1 е идеално прилягане.
В изданията след януари 2022 г. на Excel в Microsoft 365 (преди Office 365) динамичните масиви са променили начина на оценяване на формулите на масивите. Вече не се изисква да използвате CTRL + SHIFT + ENTER или да маркирате областта на клетките, които масивът ще заема. Просто въведете формулата и щракнете върху Enter и получените клетки ще се „излеят“ в масива.
За останалата част от тази статия ще се позоваваме на LINEST по отношение на динамичните масиви в Microsoft 365 Excel.
Прогнозиране с LINEST (проста регресия)
Комбинирането на функциите LINEST и SUM може да се използва за прогнозиране на стойността на зависима променлива y, предвид известни х и y данни. По -долу е даден пример, показващ какво y стойността ще бъде, когато x = 14.
| 1 | = SUM (LINEST (C3: C7, B3: B7)*{14,1}) |
Моделът идва под формата y = mx + b. Това е същото като y = a+ bx, само различен начин за представяне на уравнението. Съвет, който трябва да имате предвид при линейните уравнения, е променливата до х винаги е наклонът, а променливата след знак плюс или минус винаги е прихващането, независимо от буквите, използвани в уравнението.
Използвайки формулата: = 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, х1.
Използвайки функцията LINEST с предоставените данни, нашият регресионен модел е:
С r2 стойност 0,997, което показва силен или силно корелиран модел. Използвайки модела, сега можете да предвидите каква ще бъде оценената стойност на офис сграда въз основа на всяка комбинация от горните независими променливи.
LINEST Съвети
- Уверете се, че имате най -актуалната версия на Microsoft 365, за да използвате LINEST с динамични масиви. Може да се наложи да активирате текущия канал на Office Insider (Визуализация), за да използвате функциите на динамичен масив. На страницата на акаунта:
- Ако сте в издание, различно от Microsoft 365, ще трябва да използвате наследения CTRL + SHIFT + ENTER (CSE) метод за оценка на формулите на масива.
- Ако използвате наследения метод, броят на колоните, които трябва да се подчертаят при въвеждане на функция LINEST масив, винаги е броят на х променливи във вашите данни плюс 1. Броят редове за избор за масива е 5.
- Ако ще споделяте версията на Excel с активиран динамичен масив с някой, който използва версия, различна от Microsoft 365, използвайте наследения CSE метод, за да избегнете проблеми със съвместимостта.
Интересувате ли се от още прогнози?
Вижте другите ни статии за прогнозиране с функции на експоненциално изглаждане, TREND, GROWTH и LOGEST.
LINEST функция в Google Таблици
Функцията LINEST работи абсолютно същото в Google Sheets, както и в Excel.
LINEST Примери във VBA
Можете също да използвате функцията LINEST във VBA. Тип:application.worksheetfunction.linest (известен_ys, известен_xs, const, статистика)
Изпълнение на следния VBA израз
| 1 | Диапазон ("D2") = Application.WorksheetFunction.LinEst (Диапазон ("A2: A8"), Обхват ("B2: B8")) |
ще даде следните резултати
За аргументите на функцията (известни_y и т.н.) можете или да ги въведете директно във функцията, или да определите променливи, които да използвате вместо тях.
Връщане към списъка с всички функции в Excel
