LINEST (funkcija LINEST)

Napomena: Želimo da vam što pre pružimo najnoviji sadržaj pomoći čim na vašem jeziku. Ova stranica je prevedena automatski i može da sadrži gramatičke greške ili netačnosti. Naš cilj je da ovaj sadržaj bude koristan. Možete li nam na dnu ove stranice saopštiti da li su vam informacije bile od koristi? Ovo je članak na engleskom jeziku za brzu referencu.

Ovaj članak opisuje sintaksu formule i upotrebu funkcije LINEST u programu Microsoft Excel. Veze ka više informacija o pravljenju grafikona i izvršavanju regresivne analize pronaći ćete u odeljku Takođe pogledajte.

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 biti data dodatna statistika regresije.

    • Ako je argument statistika TRUE, funkcija LINEST daje dodatnu statistiku regresije, tako da je dobijeni niz {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,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.

Statistika

Opis

se1;se2;...;sen

Standardne vrednosti greške za koeficijente m1;m2;...;mn.

seb

Standardna vrednost greške za konstantu b (seb = #N/A ako argument konstanta ima vrednost FALSE).

r2

Koeficijent determinacije. Upoređuje predviđene i stvarne y vrednosti i opsege u vrednosti od 0 do 1. Ako je 1, onda postoji savršena korelacija u uzorku – ne postoji razlika između predviđene y vrednosti i stvarne y vrednosti. S druge strane gledano, ukoliko je koeficijent determinacije 0, jednačina regresije nije od pomoći u predviđanju y vrednosti. Za informacije o tome kako se računa r2, pogledajte „Primedbe" u nastavku ove teme.

sey

Standardna greška za y predviđanja.

F

F statistika ili F posmatrana vrednost. Koristite F statistiku da biste odredili da li se posmatran odnos između zavisnih i nezavisnih promenljivih pojavljuje slučajno.

df

Stepeni slobode. Koristite stepene slobode da biste pronašli F kritične vrednosti u statističkoj tabeli. Uporedite vrednosti koje ste pronašli u tabeli sa onima koje je dala F statistika pomoću funkcije LINEST da biste utvrdili nivo sigurnosti modela. Za informacije o tome kako se računa df, pogledajte „Primedbe" kasnije u nastavku ove teme. Primer 4 prikazuje korišćenje F i df.

ssreg

Regresija zbira kvadrata.

ssresid

Ostatak zbira kvadrata. Za informacije o tome kako se računa ssreg i ssresid pogledajte „Primedbe" u nastavku ove teme.

Sledeća ilustracija prikazuje redosled dobijanja dodatne statistike regresije.

Radni list

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 obeležava sa m, uzmite dve tačke na toj liniji, (x1,y1) i (x2,y2); nagib je jednak (y2 - y1)/(x2 - x1).

    Y odsečak (b):
    y odsečak neke linije, koji se često obeležava sa b, je vrednost y na mestu preseka te linije i y ose.

    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(poznati_y;poznati_x);1)

    Y odsečak:
    =INDEX(LINEST(poznati_y;poznati_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:

    Jednačina

    Jednačina

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

  • Linije i krive prikladno funkcije LINEST i LOGEST može da izračuna najbolje pravu liniju ili eksponencijalnu krivu koja se uklapa u vaše podatke. Međutim, morate da odlučite koji od dva rezultata najbolje uklapa u vaše podatke. Možete da izračunate TREND (poznati_y, poznati_x) za pravu liniju, ili GROWTH (poznati_y, poznati_x) za eksponencijalnu krivu. Ove funkcije, bez argument novi_x daju niz y vrednosti koje su predviđene duž te linije ili krive u stvarni podaci. Tada možete da uporedite predviđene vrednosti sa stvarnih vrednosti. Možda ćete želeti da biste ih obe grafikon za vizuelno poređenja.

  • 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, r2, koji je pokazatelj toga koliko dobro jednačina koja proističe iz analize regresije objašnjava odnos između promenljivih. Vrednost r2 je jednaka ssreg/sstotal.

  • U nekim slučajevima, neke od X kolona (potrebno je da to Y i x nalaze u kolonama) možda ne sadrži dodatu predviđenu vrednost u prisustvu drugih X kolona. Drugim rečima, eliminisanju jednu ili više kolona X može dovesti do predviđene Y vrednosti koje su podjednako precizne. U tom slučaju ove redundantne X kolone bi trebalo da bude izostavljena iz modela regresije. Ovu pojavu se naziva „kolinearnost” zato što sve redundantne X kolone se može iskazati kao zbir umnožaka broja na koji nisu-redundantne X kolone. Funkcija LINEST proverava kolinearnost i uklanja sve redundantne X kolone iz modela regresije kada ih. Uklonjene X kolone mogu prepoznati u LINEST izlazu kao 0 koeficijente pored 0 se vrednosti. Ako jedan ili više kolona ukloni kao suvišne, se utiče na df zato što df zavisi od broja X kolona zapravo koriste za predviđanja. Za detalje o isplate df, pogledajte primer 4. Ako se df promeni zato što redundantne X kolone uklonjene, vrednosti sey| i F takođe utiče na. Kolinearnost treba da bude relativno retko u praksi. Međutim, jedan slučaj gde će se verovatnije da se jave je kada neke X kolone sadrže samo 0 i 1 vrednosti kao indikatori da li temu u eksperiment je ili nije član određene grupe. Ako argument konstanta = TRUE ili je izostavljen, funkcija LINEST efikasno umeće dodatnu X kolonu svih 1 vrednosti za odsečak. Ako imate kolonu sa 1 za svaku temu ako muški ili 0 ako ne i takođe imate kolonu sa 1 za svaku temu ako žena ili 0 ako ne vidite, kasnije u ovoj koloni je suvišne zato što stavke u njoj se mogu dobiti od oduzimanja stavke u „muški indikator” kolona iz stavke u dodatnu kolonu svih 1 vrednosti 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.

  • Formule koje daju nizove moraju da se unesu kao formule niza.

    Napomena:  U programu Excel Online nije moguće napraviti formule niza.

  • 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 grešku #DIV/0! grešku. Algoritam za funkcije SLOPE i INTERCEPT je dizajniran da biste potražili samo jedan odgovor i u ovom slučaju može biti više od jednog 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

1. primer – kosina i Y presek

Kopirajte date primere podataka u sledeću tabelu i nalepite ih u ćeliju A1 novog radnog lista u programu Excel. Ako želite da formule izračunaju rezultate, izaberite formule, pritisnite taster F2, a zatim pritisnite taster Enter. Ako je potrebno, možete prilagoditi širinu kolona kako biste videli sve podatke u njima.

Poznato y

Poznato x

1

0

9

4

5

2

7

3

Rezultat (nagib)

Rezultat (y-odsečak)

2

1

Formula (formula niza u ćelijama A7:B7)

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

2. primer – prosta linearna regresija

Kopirajte date primere podataka u sledeću tabelu i nalepite ih u ćeliju A1 novog radnog lista u programu Excel. Ako želite da formule izračunaju rezultate, izaberite formule, pritisnite taster F2, a zatim pritisnite taster Enter. Ako je potrebno, možete prilagoditi širinu kolona kako biste videli sve podatke u njima.

Mesec

Prodaja

1

$3,100

2

$4,500

3

$4,400

4

$5,400

5

$7,500

6

$8,100

Formula

Rezultat

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

11.000 din.

Izračunava procenu prodaje u devetom mesecu, na osnovu prodaje od prvog do šestog meseca.

3. primer – višestruka linearna regresija

Kopirajte date primere podataka u sledeću tabelu i nalepite ih u ćeliju A1 novog radnog lista u programu Excel. Ako želite da formule izračunaju rezultate, izaberite formule, pritisnite taster F2, a zatim pritisnite taster Enter. Ako je potrebno, možete prilagoditi širinu kolona kako biste videli sve podatke u njima.

Površina poda (x1)

Kancelarije (x2)

Ulazi (x3)

Starost (x4)

Procenjena vrednost (y)

2310

2

2

20

142.000 din.

2333

2

2

12

144.000 din.

2356

3

1,5

33

151.000 din.

2379

3

2

43

150.000 din.

2402

2

3

53

139.000 din.

2425

4

2

23

169.000 din.

2448

2

1,5

99

126.000 din.

2471

2

2

34

142.900 din.

2494

3

3

23

163.000 din.

2517

4

4

55

169.000 din.

2540

2

3

22

149.000 din.

-234,2371645

13,26801148

0,996747993

459,7536742

1732393319

Formula (formula niza uneta u opseg ćelija A14:A18)

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

4. primer – upotreba F i r2 statistika

U prethodnom primeru koeficijent determinacije ili r2 iznosi 0,99675 (vidite ćeliju A17 u rezultatu funkcije LINEST), što ukazuje na jaku vezu između nezavisnih promenljivih i prodajne cene. F statistiku možete da koristite da biste odredili da li su se ovi rezultati, sa tako visokom vrednošću r2, pojavili slučajno.

Pretpostavimo na trenutak da, u stvari, ne postoji nikakva veza među promenljivim, već da ste vi izvukli redak primerak od 11 poslovnih zgrada koji je dao statističku analizu koja prikazuje jaku vezu. Termin „Alfa" se koristi za verovatnoću pogrešnog zaključivanja da postoji veza.

F i df vrednosti izlaz iz funkcije LINEST može koristiti za procenu verovatnoću više F vrednosti koja se slučajno pojavljuje. F može da se poredi sa kritične vrednosti u objavljeni F raspodelu tabelama ili funkcija FDIST u programu Excel može se koristiti za izračunavanje verovatnoće veće F vrednosti koja se slučajno pojavljuje. Odgovarajuća F raspodela ima v1 i v2 stepena slobode. Ako je n broj tačaka podataka i konstanta = TRUE ili je izostavljen, onda je v1 = n – df – 1 i v2 = df. (Ako je konstanta = FALSE, onda je v1 = n – df i v2 = df.) Funkcija FDIST – sa sintaksom FDIST(F, v1, v2) – vratiće vrednost verovatnoće za više F vrednosti koja se slučajno pojavljuje. U ovom primeru, df = 6 (ćelija B18) i F = 459,753674 (ćelija A18).

Pod pretpostavkom da je Alfa vrednost od 0,05, v1 = 11 – 6 – 1 = 4 i v2 = 6, kritičnih nivoa F je 4,53. Pošto je F = 459,753674 je mnogo veći od 4,53, to je verovatno ovako visoka F vrednost pojavili slučajno. (Kod Alfa = 0,05, hipoteza da ne postoji odnos između poznati_y i poznati_x je da se odbaci kada F premaši kritičnih nivo, 4,53.) Funkcija FDIST u programu Excel možete da koristite da biste dobili verovatnoću da ovako visoka F vrednost slučajno. Na primer, FDIST(459,753674, 4, 6) = 1, 37e-7, izuzetno mala verovatnoća. Možete da zaključite, tako što ćete pronaći kritičnih nivoa F u tabeli ili korišćenjem funkcije FDIST je jednačina regresije korisne za predviđanje procenjena vrednost poslovne zgrade u ovoj oblasti. Zapamtite da je kritično koristiti ispravne vrednosti v1 i v2 koji su izračunati u prethodnom pasusu.

5. primer – izračunavanje t-statistike

Još jedan hipotetički test će utvrditi da li je svaki koeficijent nagiba koristan u predviđanju procenjene vrednosti poslovne zgrade u primeru 3. Na primer, za testiranje koeficijenta za starost u statističke svrhe, podelite -234,24 (koeficijent nagiba za starost) sa 13,268 (predviđena standardna greška koeficijenata za starost u ćeliji A15). Sledi t posmatrana vrednost:

t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

Ukoliko je apsolutna vrednost t dovoljno visoka, može se zaključiti da je koeficijent nagiba koristan u predviđanju procenjene vrednosti poslovne zgrade u primeru 3. Sledeća tabela prikazuje apsolutne vrednosti 4 t posmatrane vrednosti.

Ukoliko pogledate tabelu u statističkom priručniku, videćete da je t kritična vrednost, sa dva kraja, sa 6 stepeni slobode i Alfa = 0,05 je 2,447. Ova kritična vrednost se takođe može pronaći pomoću funkcije TINV u programu Excel. TINV(0,05;6) = 2,447. Budući da je apsolutna vrednost t (17,7) veća od 2,447, starost je važna promenljiva u predviđanju procenjene vrednosti poslovne zgrade. Svaka druga nezavisna promenljiva može da se na sličan način testira u statističke svrhe. Slede t posmatrane vrednosti za svaku nezavisnu promenljivu.

Promenljiva

t posmatrana vrednost

Površina poda

5,1

Broj kancelarija

31,3

Broj ulaza

4,8

Starost

17,7

Sve ove vrednosti imaju apsolutnu vrednost veću od 2,447. Stoga su sve promenljive koje se koriste u jednačini regresije korisne za predviđanje procenjene vrednosti poslovnih zgrada u ovom području.

Razvijte Office veštine
Istražite obuku
Prvi nabavite nove funkcije
Pridružite se Office Insider korisnicima

Da li su vam ove informacije koristile?

Hvala vam na povratnim informacijama!

Hvala za povratne informacije! Zvuči da će biti od pomoći ako vas povežemo sa našim agentima Office podrške.

×