LINEST (funkcija LINEST)

Opis

Funkcija LINEST izračunava statistiku za liniju pomoću metoda „najmanjih kvadrata“ da bi se izračunale prava linija koja najbolje odgovara vašim podacima, a zatim daje niz koji tu liniju opisuje. Funkciju LINEST možete i da kombinujete sa drugim funkcijama da biste izračunali statistiku za druge tipove modela koji imaju linearne nepoznate parametre, kao što su polinomski, logaritamski, eksponencijalni i potencijalni redovi. Ova funkcija mora da se unese kao formula niza zato što daje niz vrednosti. U ovom članku uputstva se nalaze posle primera.

Jednačina za liniju je:

y = mx + b

– ili –

y = m1x1 + m2x2 + ... + b

ako postoji više opsega x vrednosti gde su zavisne y vrednosti funkcija nezavisnih x vrednosti. M vrednosti su koeficijenti koji odgovaraju svakoj x vrednosti, a b je konstantna vrednost. Imajte u vidu da y, x i m mogu da budu vektori. Niz koji funkcija LINEST daje je {mn;mn-1;...;m1;b}. Funkcija LINEST takođe može da vrati dodatnu statistiku regresije.

Sintaksa

LINEST(poznati_y, [poznati_x], [konstanta], [statistika])

Sintaksa funkcije LINEST ima sledeće argumente:

Sintaksa

• poznati_y    Obavezno. Skup y vrednosti koji vam je već poznat iz relacije y = mx + b.

  • Ako opseg argumenta poznati_y predstavlja jednu kolonu, svaka kolona argumenta poznati_x tumači se kao zasebna promenljiva.

  • Ukoliko se opseg argumenta poznati_y nalazi u jednom redu, svaki red argumenta poznati_x tumači se kao zasebna promenljiva.

• poznati_x    Opcionalno. Skup x vrednosti koji vam već može biti poznat iz relacije y = mx + b.

  • Opseg argumenta poznati_x može da sadrži jedan ili više skupova promenljivih. Ako se koristi samo jedna promenljiva, argumenti poznati_y i poznati_x mogu da budu opsezi bilo kog oblika, sve dok imaju jednake dimenzije. Ukoliko se koristi više promenljivih, argument poznati_y mora biti vektor (to jest, opseg visine jednog reda ili širine jedne kolone).

  • Ako je argument poznati_x izostavljen, podrazumeva se da je to niz {1,2,3,...} koji ima istu veličinu kao argument poznati_y.

• konstanta    Opcionalno. Logička vrednost kojom se određuje da li će konstanta b biti jednaka 0.

  • Ako argument konstanta ima vrednost TRUE ili je izostavljen, b se računa normalno.

  • Ukoliko argument konstanta ima vrednost FALSE, b je jednako 0, a m vrednosti su podešene tako da se uklapaju u y = mx.

• statistika    Opcionalno. Logička vrednost koja određuje da li će se vratiti dodatna statistika regresije.

  • Ako statistika ima vrednost TRUE, funkcija LINEST daje dodatnu statistiku regresije; kao rezultat toga, dobijeni niz je {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r^2,sey; F,df; ssreg,ssresid}.

  • Ukoliko je argument statistika FALSE ili izostavljen, funkcija LINEST daje samo m koeficijente i konstantu b.

    Dodatna statistika regresije ide sledećim redom.

Sledeća ilustracija prikazuje redosled dobijanja dodatne statistike regresije.

Primedbe

• Možete da opišete bilo koju pravu liniju sa nagibom i y odsečak:

  Nagib (m):
  Da biste pronašli nagib linije, koji se često piše kao m, uzmite dve tačke na liniji, (x1,y1) i (x2,y2); nagib je jednak (y2 – y1)/(x2 - x1).

  Y odsečak (b):
  Y odsečak linije, koji se često piše kao b, jeste vrednost y u tački u kojoj linija prelazi y osu.

  Jednačina prave linije je y = mx + b. Kada znate vrednosti m i b, možete da izračunate bilo koju tačku na liniji tako što ćete y ili x vrednost uneti u tu jednačinu. Takođe možete da koristite funkciju TREND.

• Kada imate samo jednu nezavisnu x promenljivu, možete da obezbedite nagib i y odsečak vrednosti direktno korišćenjem sledećih formula:

  Nagib:
  =INDEX(LINEST(known_y,known_x,1)

  Y odsečak:
  =INDEX(LINEST(known_y,known_x,2)

• Tačnost linije izračunate pomoću funkcije LINEST zavisi od stepena rasejanosti u vašim podacima. Što su podaci više linearni, to je precizniji LINEST model. LINEST koristi metod najmanjih kvadrata za određivanje najboljeg uklapanja podataka. Kada imate samo jednu nezavisnu x promenljivu, računanja za m i b su zasnovana na sledećim formulama:

  

  

  gde su x i y srednje vrednosti, tj., x = AVERAGE(poznati x) i y = AVERAGE(poznati_y).

• Funkcije line-and curve-fitting funkcije LINEST i LOGEST mogu da izračunaju najbolju prave linije ili eksponencijalnu krivu koja odgovara vašim podacima. Međutim, morate da odlučite koji od ova dva rezultata najbolje odgovara vašim podacima. Možete da izračunate oznake TREND(known_y, known_x) za pravu liniju ili GROWTH(known_y, known_x) za eksponencijalnu krivu. Ove funkcije, bez new_x argumenta, vraćaju niz y vrednosti koje su predviđene duž te linije ili krive kod stvarnih tačaka podataka. Zatim možete da uporedite predviđene vrednosti sa stvarnim vrednostima. Trebalo bi da ih prikažete u grafikonu kako biste videli vizuelno poređenje.

• U analizi regresije program Excel za svaku tačku računa kvadratnu razliku između y vrednosti koja je za tu tačku predviđena i njene stvarne y vrednosti. Zbir tih kvadratnih razlika se zove ostatak zbira kvadrata, ssresid. Excel nakon toga računa ukupan zbir kvadrata, sstotal. Kada je argument const = TRUE ili je izostavljen, ukupan zbir kvadrata je zbir kvadratnih razlika između stvarnih y vrednosti i proseka y vrednosti. Kada je argument const = FALSE, ukupan zbir kvadrata je zbir kvadrata stvarnih y vrednosti (bez oduzimanja prosečne y vrednosti od svake pojedinačne y vrednosti). Nakon toga se regresivni zbir kvadrata, ssreg, može izračunati na sledeći način: ssreg = sstotal - ssresid. Što je manji ostatak zbira kvadrata, u poređenju sa ukupnim zbirom kvadrata, veća je vrednost koeficijenta determinacije, r², što je pokazatelj koliko dobro jednačina dobijena regresivnom analizom objašnjava odnos između promenljivih. Vrednost r^{2 jednaka} je ssreg/sstotal.

• U nekim slučajevima, neke X kolone (pretpostavljaju da su Y i X u kolonama) možda neće imati dodatnu predvidljivu vrednost u prisustvu drugih X kolona. Drugim rečima, eliminisanje nekih X kolona može dovesti do predviđenih Y vrednosti koje su podjednako tačne. U tom slučaju bi trebalo izostaviti ove suvišne X kolone iz modela regresije. Ovaj fenomen se naziva "kolinarnost" zato što se svaka suvišna X kolona može izraziti kao zbir višestrukih x kolona koje nisu redundantne. Funkcija LINEST proverava kolinarnost i uklanja sve suvišne X kolone iz modela regresije kada ih identifikuje. Uklonjene X kolone mogu se prepoznati u LINEST rezultatu kao da ima 0 koeficijenta pored 0 se vrednosti. Ako se neke kolone uklone kao redundantne, to utiče na df zato što df zavisi od broja X kolona koje se zapravo koriste u prediktivne svrhe. Detalje o procjeni df potražite u članku 4. primer. Ako se df promeni zato što su redundantne X kolone uklonjene, to utiče i na vrednosti sey i F. Kolinarnost bi trebalo da bude relativno retka u praksi. Međutim, jedan slučaj u kojem se verovatnije javlja jeste kada neke X kolone sadrže samo 0 i 1 vrednosti kao indikatore toga da li je tema u eksperimentu ili nije član određene grupe. Ako je argument konstanta = TRUE ili je izostavljen, funkcija LINEST efikasno umeće dodatnu X kolonu sa svim 1 vrednostima da bi modelovala odsečak. Ako imate kolonu sa brojem 1 za svaku temu ako je muško ili 0 ako nije, a imate i kolonu sa brojem 1 za svaku temu ako je ženska, ili 0 ako nije, ova druga kolona je suvišna zato što se stavke u njemu mogu dobiti od oduzimanjem stavke u koloni "indikator muškog pola" od stavke u dodatnoj koloni svih 1 vrednosti koje dodaje funkcija LINEST .

• Vrednost df se izračunava na sledeći način u slučaju da se nijedna X kolona ne uklanja iz modela zbog kolinearnosti: ukoliko postoje k kolone argumenta poznati_x, a argument konstanta = TRUE ili je izostavljen, onda je df = n – k – 1. Ako je argument konstanta = FALSE, onda je df = n - k. U oba slučaja, svaka X kolona koja je uklonjena zbog kolinearnosti povećava vrednost df za 1.

• Prilikom unosa konstante niza (poput poznati_x) kao argumenta, koristite zareze da biste razdvojili vrednosti u istom redu, a tačku i zarez da biste razdvojili redove. Znakovi za razdvajanje mogu se razlikovati u zavisnosti od regionalnih postavki.

• Obratite pažnju na to da y vrednosti koje su predviđene jednačinom regresije možda nisu ispravne ukoliko se nalaze izvan opsega y vrednosti koji ste koristili da odredite jednačinu.

• Osnovni algoritam koji se koristi u funkciji LINEST razlikuje se od osnovnog algoritma koji se koristi u funkcijama SLOPE i INTERCEPT. Razlika između ovih algoritama može prouzrokovati različite rezultate, kada su podaci neodređeni i kolinearni. Na primer, ako tačke podataka argumenta poznati_y iznose 0, a tačke podataka argumenta poznati_x iznose 1:

  • LINEST daje vrednost 0. Algoritam funkcije LINEST je dizajniran tako da za kolinearne podatke daje razumne rezultate, a u ovom slučaju se može pronaći najmanje jedan odgovor.

  • SLOPE i INTERCEPT daju #DIV/0! grešku. Algoritam funkcija SLOPE i INTERCEPT dizajniran je da potraži samo jedan odgovor, a u ovom slučaju može da postoji više odgovora.

• Pored korišćenja funkcije LOGEST za izračunavanje statističkih podataka o drugim tipovima regresije, možete da koristite funkciju LINEST za izračunavanje opsega drugih tipova regresije tako što ćete funkcije promenljivih x i y uneti kao x i y grupe za funkciju LINEST. Na primer, sledeća formula:

  =LINEST(yvrednosti, xvrednosti^COLUMN($A:$C))

  funkcioniše kada imate jednu kolonu y-vrednosti i jednu kolonu x-vrednosti za izračunavanje kubne aproksimacije (aproksimacije pomoću polinoma trećeg stepena) obrasca:

  y = m1*x + m2*x^2 + m3*x^3 + b

  Ovu formulu možete da podesite za izračunavanje drugih tipova regresije, ali u nekim slučajevima ćete možda morati da podesite vrednosti rezultata i druge statističke podatke.

• Vrednost F-testa koju vraća funkcija LINEST razlikuje se od vrednosti F-testa koju vraća funkcija FTEST. Funkcija LINEST vraća F statistiku, a funkcija FTEST vraća verovatnoću.

Primeri