LINEST (opis funkcije)

U ovom se članku opisuje sintaksa formula i korištenje funkcije LINEST u programu Microsoft Excel. Veze na dodatne informacije o izradi grafikona i provedbi regresijske analize potražite u odjeljku Dodatni sadržaji.

Opis

Funkcija LINEST izračunava statistiku za pravac korištenjem metode "najmanji kvadrati" za izračun ravnog pravca koji najbolje odgovara vašim podacima, a potom vraća polje koje opisuje taj pravac. Također možete kombinirati funkciju LINEST s drugim funkcijama da biste izračunali statistiku za druge vrste modela koji su linearni s nepoznatim parametrima, uključujući serije polinoma, logaritamske, eksponencijalne i potencijske serije. Budući da ova funkcija vraća polje vrijednosti, potrebno ju je unijeti kao formulu polja. Upute slijede primjere u ovom članku.

Jednadžba pravca je:

y = mx + b

– ili –

y = m1x1 + m2x2 + ... + b

ako postoji više raspona vrijednosti x, pri čemu su zavisne vrijednosti y funkcija nezavisnih vrijednosti x. Vrijednosti m koeficijenti su koji odgovaraju svakoj vrijednosti x, a b je konstantna vrijednost. Upamtite da y,x i m mogu biti vektori. Polje koje vraća funkcija LINEST je {mn;mn-1;...;m1;b}. LINEST također može vratiti dodatne regresijske statistike.

Sintaksa

LINEST(poznati_y; [poznati_x]; [konst]; [stat])

Sintaksa funkcije LINEST sadrži sljedeće argumente:

Sintaksa

  • poznati_y    Obavezno. Skup vrijednosti y koje su vam već poznate u odnosu y = mx + b.

    • Ako je raspon poznati_y u jednom stupcu, svaki stupac u skupu poznati_x tumači se kao zasebna varijabla.

    • Ako je raspon skupa poznati_y sadržan u jednom retku, svaki redak skupa poznati_x tumači se kao zasebna varijabla.

  • poznati_x    Obavezno. Skup vrijednosti x koje su vam već poznate u odnosu y = mx + b.

    • Raspon poznati_x može obuhvaćati jedan ili više skupova varijabli. Ako se koristi samo jedna varijabla, poznati_y i poznati_x mogu biti rasponi bilo kojeg oblika, tako dugo dok su im dimenzije jednake. Ako se koristi više od jedne varijable, poznati_y mora biti vektor (odnosno raspon s visinom jednog retka ili širinom jednog stupca).

    • Ako se poznati_x izostavi, pretpostavlja se da je riječ o polju {1,2,3,...} iste veličine kao i poznati_y.

  • konst    Dodatno. Logička vrijednosti koja određuje hoće li konstanta b biti jednaka 0.

    • Ako je vrijednost konstTRUE ili je izostavljena, b se izračunava normalnim putem.

    • Ako je vrijednost konst je FALSE, određuje se da je b jednak 0, a vrijednosti m prilagođavaju se da bi odgovarale y = mx.

  • stat    Dodatno. Logička vrijednost koja određuje hoće li biti vraćene i dodatne statistike regresije.

    • Ako je statTRUE, LINEST vraća dodatne statistike regresije, a kao rezultat, vraćeno polje iznosi {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.

    • Ako je stat FALSE ili je izostavljen, LINEST vraća samo m-koeficijente i konstantu b.

      Dodatni statistički podaci regresije su sljedeći.

Statistički podaci

Opis

se1;se2;...;sen

Standardne vrijednosti pogreške za koeficijente m1,m2,...,mn.

seb

Standardna vrijednost pogreške za konstantu b (seb = #N/A kada je konstFALSE).

r2

Koeficijent određivanja. Uspoređuje procijenjene i stvarne vrijednosti y i raspona u vrijednosti od 0 do 1. Ako je 1, u uzorku postoji savršena korelacija – nema razlike između procijenjene vrijednosti y i stvarne vrijednosti y. U drugim ekstremnim slučajevima, ako je koeficijent određivanja 0, jednadžba regresije nije korisna u predviđanju vrijednosti y. Informacije o tome kako se izračunava R2 potražite u članku "primjedbe" u nastavku ove teme.

sey

Standardna pogreška za procijenjenu vrijednost y.

F

F statistika ili opažena F vrijednost. F statistiku koristite za određivanje je li opaženi odnos između zavisnih i nezavisnih varijabli slučajan.

df

Stupnjevi slobode. Koristite stupnjeve slobode da biste lakše pronašli kritične F vrijednosti u statističkoj tablici. Usporedite vrijednosti koje pronađete u tablici s F statistikom koju vraća funkcija LINEST da biste odredili razinu pouzdanosti modela. Dodatne informacije o izračunavanju varijable df potražite u odjeljku "Primjedbe" u nastavku ove teme. U primjeru 4 prikazano je korištenje varijabli F i df.

ssreg

Regresijski zbroj kvadrata.

ssresid

Rezidualni zbroj kvadrata. Informacije o izračunavanju varijabli ssreg i ssresid potražite u odjeljku "Primjedbe" u nastavku ove teme.

Sljedeća ilustracija prikazuje redoslijed kojim se vraćaju dodatni statistički podaci.

Grupa Stvaranje grafike na kartici Dizajn u odjeljku SmartArt alati

Primjedbe

  • Bilo koji pravac možete opisati pomoću nagiba i sjecišta osi y.

    Nagib (m):
    da biste pronašli nagib pravca, koji se često označava kao m, uzmite dvije točke pravca, (x1,y1) i (x2,y2); nagib je jednak (y2 - y1)/(x2 - x1).

    Sjecište osi Y (b):
    sjecište pravca i osi y, koje se često označava kao b, predstavlja vrijednost y u točki gdje pravac siječe os y.

    Jednadžba pravca je y = mx + b. Kada su vam poznate vrijednosti m i b, možete izračunati bilo koju točku na pravcu tako da u jednadžbu uvrstite vrijednost y ili vrijednost x. Također možete koristiti funkciju TREND.

  • Kad imate samo jednu nezavisnu varijablu x, nagib (m) i sjecište osi y (b) možete odrediti pomoću sljedećih formula:

    Nagib:
    =INDEX(LINEST(poznati_y;poznati_x);1)

    Sjecište osi Y:
    =INDEX(LINEST(poznati_y;poznati_x);2)

  • Točnost pravca izračunatog pomoću funkcije LINEST ovisi o stupnju raspršenosti podataka. Što su podaci linearniji, to je model LINEST točniji. LINEST koristi metodu najmanjih kvadrata za izračunavanje pravca koji najbolje odgovara podacima. Ako imate samo jednu neovisnu varijablu x, izračuni za m i b temelje se na sljedećim formulama:

    Jednadžba

    Jednadžba

    pri čemu su x i y aritmetičke sredine uzorka, odnosno x = AVERAGE(poznati_ x) i y = AVERAGE(poznati_y).

  • Funkcije LINEST i LOGEST koje odgovaraju linijima i krivulji mogu izračunati najbolju ravnu ili eksponencijalnu krivulju koja odgovara vašim podacima. No morate odlučiti koji od dvaju rezultata najbolje odgovara vašim podacima. Trend (known_y ' s, known_x' s) možete izračunati za ravnu crtu ili rast (known_y' s, Known_x' s) za eksponencijalnu krivulju. Ove funkcije, bez argumenta New_x ' s, vraćaju polje y-vrijednosti koje se predviđaju uz tu crtu ili krivulje na stvarnim točkama podataka. Nakon toga možete usporediti predviđene vrijednosti s stvarnim vrijednostima. Možda ćete ih htjeti grafički usporediti i za vizualnu usporedbu.

  • U regresijskoj analizi Excel za svaku točku izračunava kvadrat razlike između vrijednosti y procijenjene za tu točku i njezine stvarne vrijednosti y. Zbroj kvadrata tih razlika naziva se rezidualnim zbrojem kvadrata, odnosno ssresid. Excel tada izračunava ukupni zbroj kvadrata, odnosno sstotal. Kada je argument konst = TRUE ili je izostavljen, ukupni zbroj kvadrata zbroj je kvadrata razlika između stvarnih vrijednost y i prosjeka vrijednosti y. Kada je argument konst = FALSE, ukupni zbroj kvadrata je zbroj kvadrata stvarnih vrijednosti y (bez oduzimanja prosječne vrijednosti y od svake pojedine vrijednosti y). Regresijski zbroj kvadrata, ssreg, moguće je dobiti iz jednadžbe ssreg = sstotal - ssresid. Što je u usporedbi s ukupnim zbrojem kvadrata rezidualni zbroj kvadrata manji, to je veća vrijednost koeficijenta determinacije r2, koji je pokazatelj koliko je dobro jednadžbom koja proizlazi iz regresijske analize objašnjen odnos između varijabli. Vrijednost r2 jednaka je ssreg/sstotal.

  • U nekim slučajevima, jedan ili više stupaca X (pretpostavimo da su Y i X-ovi u stupcima) možda nema dodatnih prediktivnih vrijednosti u prisustvu ostalih stupaca X. Drugim riječima, eliminiranje jednog ili više stupaca X može dovesti do predviđenih Y vrijednosti koje su jednako točne. U tom slučaju te suvišne X stupce treba izostaviti iz regresijskog modela. Taj se fenomen naziva "collinearnost" jer se bilo koji redundantni X stupac može izraziti kao zbroj višestrukih stupaca X koji nisu redundantni. Funkcija LINEST provjerava kolinearnost i uklanja bilo koji suvišni stupac X iz regresijskog modela kada ih identificira. Uklonjene X stupce može se prepoznati u programu LINEST output kao da ima 0 koeficijenata uz 0 se vrijednosti. Ako se jedan ili više stupaca uklanja kao suvišan, df će biti zahvaćen jer df ovisi o broju X stupaca koji se koriste za prediktivne svrhe. Pojedinosti o računanje DF-a potražite u članku 4. Ako je funkcija DF izmijenjena jer se uklanjaju redundantni X stupci, utječu i vrijednosti sey i F. Kolinearnost mora biti relativno rijetka u praksi. Međutim, jedan slučaj u kojem je vjerojatnije da će se pojaviti kad neki X stupci sadrže samo 0 i 1 vrijednosti kao pokazatelje je li subjekt u eksperimentu ili nije član određene grupe. Ako je konst = TRUE ili je izostavljen, funkcija LINEST učinkovito umeće dodatni X stupac svih 1 vrijednosti da bi se model presresti. Ako imate stupac s 1 za svaki predmet ako je muškarac ili 0 ako ne, a imate i stupac s 1 za svaki predmet ako je žensko ili 0 ako ne, ovaj drugi stupac je suvišan jer se unose u njemu mogu nabaviti oduzimanjem unosa u "muškom indikatoru" stupac iz stavke u dodatnom stupcu svih 1 vrijednosti koje je dodala funkcija LINEST .

  • Vrijednost df izračunava se kako slijedi ako se stupci X ne uklanjanju iz modela zbog kolinearnosti: ako postoje stupci k s varijablama poznati_x i konst = TRUE ili izostavljen, df = n – k – 1. Ako je konst = FALSE, df = n - k. U oba slučaja, svaki stupac X koji je uklonjen zbog kolinearnosti povećava vrijednost varijable df za 1.

  • Formule koje vraćaju polja moraju se unijeti kao formule polja.

    Napomena: U Excel Online ne možete stvarati formule polja.

  • Pri unosu konstante polja (primjerice poznati_x) kao argumenta, koristite zareze da biste odvojili vrijednosti sadržane u istom redu i točke sa zarezom da biste odvojili retke. Znakovi razdjelnika ovise o regionalnim postavkama.

  • Primijetite da vrijednosti y predviđene jednadžbom regresije ne moraju biti valjane ako su izvan raspona vrijednosti y upotrijebljenih prilikom definiranja jednadžbe.

  • Algoritam u osnovi funkcije LINEST razlikuje se od algoritma koji se koristi za funkcije SLOPE i INTERCEPT. Razlika između ovih algoritama može dovesti do različitih rezultata ako su podaci neodređeni i kolinearni. Ako su, primjerice, točke podataka argumenta poznati_y 0 te ako su točke podataka argumenta poznati_x 1:

    • LINEST vraća vrijednost 0. Algoritam funkcije LINEST dizajniran je za vraćanje razumnih rezultata za kolinearne podatke te je, u ovom slučaju, moguće pronaći barem jedan odgovor.

    • Nagib i presretanje vraćaju #DIV/0! pogreška. Algoritam funkcija nagib i presretanje dizajniran je da traži samo jedan odgovor, a u ovom slučaju može postojati više od jednog odgovora.

  • Osim korištenja funkcije LOGEST za izračun statistike za druge vrste regresije, možete koristiti funkciju LINEST za izračun raspona drugih vrsta regresije unosom funkcija varijabli x i y kao nizova x i y za funkciju LINEST. Primjerice, sljedeća formula:

    =LINEST(yvrijednosti; xvrijednosti^COLUMN($A:$C))

    funkcionira kad imate jedan stupac vrijednosti y i jedan stupac vrijednosti x za izračun kubne (polinom 3. reda) aproksimacije oblika:

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

    Ovu formulu možete prilagoditi za izračun drugih vrsta regresije, ali u nekim slučajevima bit će potrebno usklađivanje izlaznih vrijednosti i ostalih statističkih podataka.

  • Vrijednost F-testa koju vraća funkcija LINEST razlikuje se od vrijednosti F-testa koju vraća funkcija FTEST. LINEST vraća F statistiku, a FTEST vjerojatnost.

Primjeri

Prvi primjer – nagib i sjecište osi y

Ogledne podatke kopirajte u sljedeću tablicu i zalijepite ih u ćeliju A1 novog radnog lista programa Excel. Da biste koristili formule za prikaz rezultata, odaberite ih pa pritisnite tipku F2, a zatim Enter. Ako je potrebno, prilagodite širine stupaca da biste vidjeli sve podatke.

Poznati y

Poznati x

1

0

9

4

5

2

7

3

Rezultat (nagib)

Rezultat (sjecište osi y)

2

1

Formula (formula polja u ćelijama A7:B7)

=LINEST(A2:A5;B2:B5;;FALSE)

Drugi primjer – jednostavna linearna regresija

Ogledne podatke kopirajte u sljedeću tablicu i zalijepite ih u ćeliju A1 novog radnog lista programa Excel. Da biste koristili formule za prikaz rezultata, odaberite ih pa pritisnite tipku F2, a zatim Enter. Ako je potrebno, prilagodite širine stupaca da biste vidjeli sve podatke.

Mjesec

Prodaja

1

15.500 kn

2

22.500 kn

3

22.000 kn

4

27.000 kn

5

37.500 kn

6

40.500 kn

Formula

Rezultat

=SUM(LINEST(B1:B6; A1:A6)*{9,1})

55 000 kn

Izračunava procjenu prodaje u devetom mjesecu na temelju prodaje od prvog do šestog mjeseca.

Treći primjer – višestruka linearna regresija

Ogledne podatke kopirajte u sljedeću tablicu i zalijepite ih u ćeliju A1 novog radnog lista programa Excel. Da biste koristili formule za prikaz rezultata, odaberite ih pa pritisnite tipku F2, a zatim Enter. Ako je potrebno, prilagodite širine stupaca da biste vidjeli sve podatke.

Površina kata (x1)

Uredi (x2)

Ulazi (x3)

Starost (x4)

Procijenjena vrijednost (y)

2310

2

2

20

710 000 kn

2333

2

2

12

720 000 kn

2356

3

1,5

33

755 000 kn

2379

3

2

43

750 000 kn

2402

2

3

53

695 000 kn

2425

4

2

23

845 000 kn

2448

2

1,5

99

630 000 kn

2471

2

2

34

714 500 kn

2494

3

3

23

815 000

2517

4

4

55

845 000 kn

2540

2

3

22

745 000 kn

- 234,2371645

13,26801148

0,996747993

459,7536742

1732393319

Formula (formula polja unesena u ćelije A14:A18)

=LINEST(E2:E12;A2:D12;TRUE;TRUE)

Četvrti primjer – korištenje statistika F i r2

U prethodnom primjeru koeficijent determinacije, ili r2, iznosi 0,99675 (vidi ćeliju A17 u rezultatima funkcije LINEST), što upućuje na snažnu vezu između nezavisnih varijabli i prodajne cijene. Možete koristiti statistiku F da biste odredili jesu li ti rezultati, s tako visokom vrijednošću r2, dobiveni slučajno.

Pretpostavimo na trenutak da nema povezanosti između varijabli, ali da imate rijedak uzorak od 11 poslovnih zgrada koji u statističkoj analizi pokazuje jaku povezanost. Izraz "Alfa" se koristi za vjerojatnost pogrešnog zaključka da postoji povezanost.

Vrijednosti F i DF u izlaznom obliku iz funkcije LINEST mogu se koristiti da bi se procijenila vjerojatnost da će se vrijednost većeg F-a slučajno pojavljivati. F se može usporediti s kritičnim vrijednostima u objavljenim tablicama F-distribucije ili se funkcija FDIST u programu Excel može koristiti da bi se izračunala vjerojatnost veće F vrijednosti koja se pojavljuje slučajno. Odgovarajuća F distribucija ima v1 i v2 stupanj slobode. Ako je n broj podatkovnih bodova i konst = TRUE ili izostavljen, zatim v1 = n – DF – 1 i v2 = df. (Ako je konst = FALSE, zatim v1 = n – DF i v2 = df) Funkcija FDIST – uz sintaksu FDIST(F, v1, v2) vratit će vjerojatnost da će se vrijednost većeg F-a pojavljivati slučajno. U ovom primjeru, df = 6 (Cell B18) i F = 459,753674 (Cell a18).

Uz pretpostavku Alfa vrijednosti 0,05, v1 = 11 – 6 – 1 = 4 i v2 = 6, kritičnu razinu F je 4,53. Budući da je F = 459,753674 znatno veći od 4,53, izuzetno je vjerojatno da se F vrijednost ove visoke dogodila slučajno. (Uz Alfa = 0,05, hipoteza da nema odnosa između known_y ' s i Known_x ' s mora biti odbijen kada F prelazi kritičnu razinu, 4,53.) Funkciju FDIST možete koristiti u programu Excel da biste postigli vjerojatnost da se vrijednost F-a tako visoka pojavila slučajno. Na primjer, FDIST(459,753674; 4; 6) = 1.37 e-7, izuzetno mala vjerojatnost. Možete zaključiti, ako pronađete kritičnu razinu F u tablici ili pomoću funkcije FDIST , da je regresijska jednadžba korisna u predviðanju procijenjene vrijednosti poslovnih građevina u ovom području. Imajte na umu da je ključno koristiti ispravne vrijednosti od v1 i v2 koje su izračunale u prethodnom odlomku.

Peti primjer – izračun t-statistike

Drugom provjerom hipoteze odredit ćete je li svaki koeficijent nagiba koristan za određivanje procijenjene vrijednosti poslovne zgrade u primjeru 3. Da biste, primjerice, testirali statističku značajnost koeficijenta starosti, podijelite -234,24 (koeficijent nagiba dobi) s 13,268 (procijenjena standardna pogreška koeficijenata dobi u ćeliji A15). Opažena t-vrijednost iznosi:

t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7

Ako je apsolutna vrijednost t dovoljno visoka, moguće je zaključiti da je koeficijent nagiba koristan za određivanje procijenjene vrijednosti poslovne zgrade u primjeru 3. U sljedećoj tablici prikazane su apsolutne vrijednosti 4 opažene t vrijednosti.

Ako konzultirate tablicu u statističkom priručniku, vidjet ćete da je kritična t vrijednost s dva kraka i 6 stupnjeva slobode i vrijednošću Alfa = 0,05 jednaka 2,447. Tu kritičnu vrijednost možete pronaći i pomoću funkcije TINV u programu Excel. TINV(0.05,6) = 2,447. Budući da je apsolutna vrijednost t (17,7) veća od 2,447, dob je važna varijabla pri određivanju procijenjene vrijednosti poslovne zgrade. Na sličan način moguće je testirati statističku važnost svake od preostalih varijabli. U nastavku navodimo opažene t-vrijednosti za svaku nezavisnu varijablu.

Varijabla

Opažena varijabla t

Količina prostora

5,1

Broj ureda

31,3

Broj ulaza

4,8

Starost

17,7

Ove vrijednosti imaju apsolutnu vrijednost veću od 2,447, pa možemo zaključiti kako su sve varijable u regresijskoj jednadžbi korisne u predviđanju procijenjene vrijednosti poslovnih zgrada na tom području.

Napomena:  Ova je stranica strojno prevedena te može sadržavati gramatičke pogreške ili netočnosti. Naša je namjera da vam ovaj sadržaj bude koristan. Jesu li vam te informacije bile korisne? Kao referencu možete pogledati i članak na engleskom jeziku.​

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×