В тази статия е описан синтаксисът и употребата на функцията LINEST в Microsoft Excel.
Описание
Функцията LINEST изчислява статистиките за линия с помощта на метода на най-малките квадрати, за да намери правата линия, която най-добре се съгласува с вашите данни, и връща масив, който описва линията. Освен това можете да комбинирате LINEST с други функции, за да изчислите статистиката за други типове модели, които са линейни при неизвестните параметри, включително полиномни, логаритмични, експоненциални и на степенни редове. Тъй като тази функция връща масив от стойности, тя трябва да бъде въведена като формула за масив. Инструкциите са по реда на примерите в тази статия.
Уравнението за линията е:
y = mx + b
–или–
y = m1x1 + m2x2 + ... + b
ако има множество диапазони от x-стойности, където зависимите стойности на 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.
конст Незадължително. Логическа стойност, задаваща дали константата b да се изравни на 0.
- Ако конст е TRUE или е пропуснато, b се изчислява нормално.
- Ако конст е FALSE, b се задава равно на 0 и m-стойностите се нагласяват така, че да се съгласуват с y = mx.
статистика Незадължително. Логическа стойност, задаваща дали да се върнат допълнителни регресионни статистики.
- Ако състояния е TRUE, LINEST връща допълнителните регресионни статистики; В резултат върнатият масив е {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; R2, Сей; 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. За информация как се изчислява2 вж. "Забележки" по-долу в тази тема. |
| sey | Стандартната грешка за оценката за y. |
| F | 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):
Често се означава с b и е стойността на y в точката, където линията пресича оста y.
Уравнението на права линия е y = mx + b. След като знаете стойностите на m и b, можете да изчислите всяка точка от линията, като поставите в това уравнение стойността на y или x. Можете също да използвате функцията TREND.Когато имате само една независима променлива x, можете да получите стойностите на наклона и пресечната точка директно с помощта на следните формули:
Наклон:
=INDEX(LINEST(known_y;known_x's);1)
Пресечна точка с оста Y:
=INDEX(LINEST(known_y;known_x's);2)Точността на изчислената с LINEST линия зависи от степента на разсейване във вашите данни. Колкото по-линейни са данните, толкова по-точен е моделът LINEST . LINEST използва метода на най-малките квадрати, за да определи най-доброто съгласуване с данните. Когато имате само една независима променлива x, изчисленията за m и b се основават на следните формули:
където x и y са средни стойности на извадките; т.е. x = AVERAGE(известни_x) и y = AVERAGE(known_y).Функциите за побиране на линейни и криви LINEST и LOGEST могат да изчислят най-добрата права линия или експоненциална крива, която пасва на вашите данни. Трябва обаче да решите кой от двата резултата най-добре отговаря на вашите данни. Можете да изчислите TREND(known_y's,known_x's) за права линия или GROWTH(known_y's, known_x's) за експоненциална крива. Без аргумента new_x тези функции връщат масив от стойности на y, прогнозирани по тази линия или крива в actual data points. След това можете да сравните прогнозираните стойности с действителните стойности. Може да начертаете и двете за визуално сравнение.
В регресионния анализ Excel изчислява за всяка точка повдигнатата на квадрат разлика между стойността на y, оценена за тази точка, и действителната стойност на y. Сумата на тези повдигнати на квадрат разлики се нарича остатъчна сума от квадрати, ssresid. След това Excel изчислява общата сума от квадрати, sstotal. Когато аргументът конст = TRUE или е пропусната, общата сума от квадрати е сумата на повдигнатите на квадрат разлики между действителните стойности на y и средната стойност на отделните стойности на y. Когато аргументът конст = FALSE, общата сума от квадрати е сумата на квадратите на действителните стойности на 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 като индикатори за това дали участникът в експеримента е или не е член на определена група. Ако конст = TRUE или е пропуснато, функцията LINEST ефективно вмъква допълнителна колона X от всичките 1 стойности, за да моделира пресечната точка. Ако имате колона с 1 за всеки обект, ако е мъжки, или 0, ако не, и имате колона с 1 за всеки подлог, ако е женско, или 0, ако не, последната колона е излишна, защото записите в нея могат да бъдат получени чрез изваждане на записа в колоната "мъжки показател" от записа в допълнителната колона на всички стойности 1, добавени от функцията LINEST .
Когато от модела не са премахнати колони за X поради колинеарност, стойността на df се изчислява по следния начин: ако има k колони от known_x и конст = TRUE или е пропуснато, df = n – k – 1. Ако конст = FALSE, df = n – k. И в двата случая всяка колона за X, премахната поради колинеарност, увеличава стойността на df с 1.
Когато въвеждате като аргумент масив от константи (например known_x), използвайте запетаи за отделяне на стойностите, съдържащи се в един и същ ред, и точка и запетая за отделяне на различните редове. Разделителните знаци могат да се различават, в зависимост от вашите регионални настройки
Забележете, че стойностите на y, прогнозирани от регресионното уравнение, може да не са валидни, ако са извън диапазона на стойностите на y, който сте използвали, за да дефинирате уравнението.
Алгоритъмът, на който се базира функцията LINEST, е различен от този, който се използва във функциите SLOPE и INTERCEPT. Разликата в тези алгоритми може да доведе до различни резултати, когато данните са неопределени и колинеарни. Ако например точките на данните на аргумента на known_y са 0 и точките данни на аргумента на known_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-стойности, за да изчислите кубично (полином от трета степен) приближение в следната форма:
y = m1*x + m2*x^2 + m3*x^3 + b
Можете да настроите тази формула за да изчислява други типове регресии, но в някои случаи тя изисква настройка на изходящите стойности и други статистики.Стойността на F-теста, която е върната от функцията LINEST се различава от стойността на F-теста, връщана от функцията FTEST. LINEST връща F статистика, докато FTEST връща вероятността.
Примери
Пример 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 | 3100 лв. |
| 2 | 4500 лв. |
| 3 | 4400 лв. |
| 4 | 5400 лв. |
| 5 | 7500 лв. |
| 6 | 8100 лв. |
| Формула | Резултат |
| =SUM(LINEST(B1:B6; A1:A6)*{9,1}) | 11 000 лв. |
| Изчислява оценката на продажбите през деветия месец на базата на продажбите за месеците от 1 до 6. |
Пример 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) |
Пример – Използване на статистиките F и r2
В предишния пример коефициентът на корелация, или r2, е 0,99675 (вж. клетка A17 в резултатите от LINEST), който показва силна връзка между независимите променливи и продажната цена. Можете да използвате 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 (клетка 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.) Можете да използвате функцията FDIST в Excel, за да получите вероятността стойност на F да е възникнала случайно. Например FDIST(459,753674, 4, 6) = 1,37E-7, изключително малка вероятност. Можете да заключите, като намерите критичното ниво на F в таблица или като използвате функцията FDIST , че регресионното уравнение е полезно за прогнозирането на оценената стойност на офисите в района. Не забравяйте, че е от особена важност да се използват правилните стойности на v1 и v2, които са изчислени в предишния абзац.
Пример – Изчисляване на t-статистика
Друг тест за проверка на хипотези ще определи дали всеки коефициент е полезен за оценка на стойността на сградите от пример 3. Например за да се проверите статистическата значимост на коефициента за възраст, разделете -234,24 (коефициента за възраст) на 13,268 (оценената стандартна грешка за коефициента за възраст в клетка A15). Стойността на t за наблюденията е:
t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7
Ако абсолютната стойност на t е достатъчно висока, може да бъде направен изводът, че коефициентът е полезен за намирането на оценената стойност на сградите в пример 3. Таблицата по-долу показва абсолютните стойности на t за четирите наблюдавани стойности.
Ако направите ръчно проверка в статистически справочник, ще намерите, че критичната стойност за t при двустранно разпределение с 6 степени на свобода и алфа = 0,05 е 2,447. Тази критична стойност може да бъде намерена и с помощта на функцията TINV. TINV(0,05,6) = 2,447. Тъй като абсолютната стойност на t (17,7), е по-голяма от 2,447, възрастта е важна променлива при оценката на стойността на сградите с офиси. По подобен начин може да се направи проверка за статистическата значимост на всяка от другите независими променливи. По-долу са показани стойностите на t за всяка от независимите променливи.
| Променлива | t стойност |
|---|---|
| Застроена площ | 5,1 |
| Брой офиси | 31,3 |
| Брой входове | 4,8 |
| Възраст | 17,7 |
Всички тези величини имат абсолютна стойност над 2,447; следователно всички променливи, използвани в регресионното уравнение, са полезни за прогнозирането на оценената стойност на офисите в района.