LINEST (funkcija LINEST)

V tem članku je opisana sintaksa formule in uporaba LINEST v Microsoft Excelu. Povezave do več informacij o risanju grafikonov in izvajanju regresivne analize so v razdelku Glejte tudi.

Opis

Funkcija LINESTizračuna statistiko za premico, in sicer z načinom »najmanjših kvadratov« izračuna premico, ki vašim podatkom najbolj ustreza, in vrne matriko, ki opisuje premico. Funkcijo LINEST lahko združite tudi z drugimi funkcijami in izračunate statistiko drugih vrst modelov, ki so linearni v neznanih parametrih, vključno s polinomskimi, logaritmičnimi, eksponentnimi in naraščajočimi nizi. Funkcija vrne matriko vrednosti, zato mora biti v obliki matrične formule. Navodila se nanašajo na primere v tem članku.

Enačba premice je:

y = mx + b

–ali–

y = m1x1 + m2x2 +... + b

če je več obsegov vrednosti x, kjer so odvisne vrednosti y funkcija neodvisnih vrednosti x. Vrednosti m so koeficienti, ki ustrezajo vsaki vrednosti x. B je konstanta. Y, x in m so lahko tudi vektorji. Matrika, ki jo vrne funkcija LINEST, je {mn\mn-1\...\m1\b}. LINEST lahko vrne tudi dodatno regresivno statistiko.

Sintaksa

LINEST(znani_y-i, [znani_x-i], [konstanta], [statistika])

V sintaksi funkcije LINEST so ti argumenti:

Sintaksa

  • znani_y-i    Obvezen. Nabor vrednosti y, ki jih že poznate v razmerju y = mx + b.

    • Če je obseg znani_y-i v enem stolpcu, je vsak stolpec argumenta znani_x-i obravnavan kot ločena spremenljivka.

    • Če je obseg argumenta znani_y-i v eni vrstici, je vsaka vrstica argumenta znani_x-i obravnavana kot ločena spremenljivka.

  • znani_x-i    Neobvezen. Nabor vrednosti x, ki jih morda že poznate v razmerju y = mx + b.

    • Obseg znani_x-i lahko vsebuje enega ali več naborov spremenljivk. Če uporabite samo eno spremenljivko, sta lahko argumenta znani_y-i in znani_x-i obsega poljubne oblike, imeti morata le enake mere. Če pa uporabljate več spremenljivk, mora biti argument znani_y-i vektor (torej obseg z višino ene vrstice in širino enega stolpca).

    • Če argument znani_x-i izpustite, privzame program zanj vrednost matrike {1;2;3;...}, ki je iste velikosti kot argument znani_y-i.

  • konstanta    Neobvezen. Logična vrednost, ki navaja, ali naj bo konstanta b enaka 0.

    • Če je argument konstanta enak TRUE ali če ga izpustite, je b izračunan normalno.

    • Če je konstanta FALSE, je b enak 0 in vrednost m se prilagodijo tako, da ustrezajo y = mx.

  • statistika    Neobvezen. Logična vrednost, ki določa, ali naj funkcija vrne dodatno regresijsko statistiko.

    • Če je argument statistika TRUE, funkcija LINEST vrne dodatno regresijsko statistiko; posledično je vrnjena matrika enaka {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.

    • Če je argument statistika FALSE ali izpuščen, LINEST vrne le koeficiente m in konstanto.

      Dodatne regresivne statistike so:

Statistika

Opis

se1,se2,...,sen

Standardne vrednosti napak za koeficiente m1,m2,...,mn.

seb

Standardne vrednosti napak za konstanto b (seb = #N/V, kadar je argument konstanta FALSE).

r2

Koeficient določnosti. Primerja ocenjene in dejanske vrednosti y in jih razvrsti od 0 do 1. Če je 1, obstaja v vzorcu popolna korelacija – med ocenjeno in dejansko vrednostjo y ni razlik. V drugi skrajnosti, če je koeficient določnosti enak 0, vam regresivna enačba pri predvidevanju vrednosti y ne pomaga. Če želite informacije o tem, kako se izračuna r2, v tem poglavju glejte odsek »Opombe«.

sey

Standardna napako z vrednostjo za y ocene.

F

F statistika, ali F-opazovana vrednost. F statistiko uporabite, če želite ugotoviti ali se opazovana zveza med odvisno in neodvisno spremenljivko pojavi slučajno.

df

Stopnje prostosti. Stopnje prostosti uporabite za pomoč pri iskanju F-kritičnih vrednosti v statistični tabeli. Če želite določiti stopnjo zaupanja v model, primerjajte vrednosti, ki so v tabeli, s statistiko F, ki jo vrne LINEST. Če želite več informacij o tem, kako se izračuna df, glejte »Opombe« v nadaljevanju te teme. V 4. primeru je prikazana uporaba F in df.

ssreg

Regresivna vsota kvadratov.

ssresid

Vsota ostankov kvadratov. Če želite več informacij o tem, kako se izračunata ssreg in ssresid, glejte »Opombe« v nadaljevanju te teme.

Naslednji primer ilustrira vrstni red v katerem se dodatne regresivne statistike vrnejo.

Delovni list

Opombe

  • Vsako premico lahko opišete z naklonom in y-presečišči:

    Naklon (m):
    Če želite ugotoviti naklon premice, pogosto napisane kot m, vzemite dve točki na premici (x1,y1) in (x2,y2); naklon je enak (y2 - y1)/(x2 - x1).

    Y-presečišče (b):
    Y-presečišče premice, pogosto napisano kot b, je vrednost y v točki, kjer premica seka os y.

    Enačba premice je y = mx + b. Ko poznate vrednosti m in b, lahko na premici izračunate katero koli točko, tako da v enačbo vstavite y ali x. Prav tako lahko uporabite funkcijo TREND.

  • Kadar imate le eno neodvisno vrednost spremenljivke x, lahko naklon in presečišče neposredno s temi formulami:

    Naklon:
    =INDEX(LINEST(znani_y-i; znani_x-i); 1)

    Y-presečišče:
    =INDEX(LINEST(znani_y-i; znani_x-i); 2)

  • Natančnost premice, izračunane z LINEST je odvisna od stopnje raztresenosti podatkov. Bolj kot so podatki linearni, bolj točen je model funkcije LINEST. Funkcija LINEST za določanje najboljšega ujemanja s podatki uporablja način najmanjših kvadratov. Kadar imate le eno neodvisno spremenljivko x, izračun za m in b temelji na teh formulah:

    Enačba

    Enačba

    pri čemer sta x in y vzorčni srednji vrednosti; tj. x = AVERAGE(znani_x-i) in y = AVERAGE(znani_y-i).

  • Funkciji za ustreznost premice in krivulje LINEST in LOGEST lahko izračunata najustreznejšo premico ali eksponentno krivuljo, ki se prilega podatkom. Odločiti pa se morate, kateri od dveh rezultatov vam bolj ustreza. Za premico lahko izračunate TREND(znani_y-i; znani_x-i) ali GROWTH(znani_y-i; znani_x-i) za eksponentno krivuljo. Ti funkciji brez argumenta novi_x-i vrneta matriko vrednosti y, predvidenih vzdolž premice ali krivulje dejanskih podatkovnih točk. Predvidene vrednosti lahko nato primerjate z dejanskimi vrednostmi. Morda boste želeli obojne podatke prikazati z grafikonom, da jih boste lahko vizualno primerjali.

  • Pri regresivni analizi Excel za vsako točko izračuna razliko kvadratov med ocenjeno vrednostjo y te točke in njeno dejansko vrednostjo y. Seštevek teh razlik kvadratov se imenuje vsota ostankov kvadratov, ssresid. Nato Excel izračuna končno vsoto kvadratov, sstotal. Če je argument konstanta = TRUE ali je izpuščen, je končna vsota kvadratov enaka vsoti razlik kvadratov med dejanskimi vrednostmi y in povprečjem vrednosti y. Če je argument konstanta = FALSE, je končna vsota kvadratov enaka vsoti kvadratov dejanskih vrednosti y (brez odštevanja povprečne vrednosti y od posamezne vrednosti y). Regresivno vsoto kvadratov, ssreg, lahko potem izračunamo tako: ssreg = sstotal - ssresid. Manjša kot je vsota ostankov kvadratov v primerjavi s končno vsoto kvadratov, večja je vrednost determinacijskega koeficienta r2, ki je pokazatelj tega, kako dobro enačba, ki izhaja iz regresivne analize, opisuje zveze vzdolž spremenljivk. Vrednost r2 je enaka ssreg/sstotal.

  • V nekaterih primerih morda kateri od stolpcev X (predpostavimo, da so vrednosti X in Y v stolpcih) nima dodatne predvidljive vrednosti ob prisotnosti drugih stolpcev X. Z drugimi besedami, če odstranimo enega ali več stolpcev X, lahko pridemo do napovedanih vrednosti Y z isto natančnostjo. V tem primeru je treba odvečne stolpce X izpustiti iz regresijskega modela. Ta pojav imenujemo »kolinearnost«, ker lahko vsak odvečen stolpec X izrazimo kot vsoto več potrebnih stolpcev X. Funkcija LINEST preveri kolinearnost in iz regresijskega modela odstrani odvečne stolpce X, ko jih identificira. Odstranjene stolpce X lahko v rezultatu funkcije LINEST prepoznamo po tem, da imajo koeficiente in vrednosti »se« enake 0. Če odstranite enega ali več odvečnih stolpcev, se spremeni vrednost df, saj je ta vrednost odvisna od dejanskega števila stolpcev X, ki se jih uporabi za napovedovanje. Če želite podrobnosti o izračunu df, glejte 4. primer. Če se vrednost df spremeni zaradi odstranitve odvečnih stolpcev X, to vpliva tudi na vrednosti sey in F. Kolinearnost se v praksi uporablja redko. En primer, ko je uporaba pogostejša, pa je, ko nekateri stolpci X vsebujejo samo vrednosti 0 in 1, ki povejo, ali je opazovani predmet v eksperimentu član določene skupine ali ne. Če je argument konstanta = TRUE ali izpuščen, funkcija LINEST učinkovito vstavi dodaten stolpec X z vsemi vrednostmi 1, da modelira presečišče. Če imate stolpec, v katerem vrednost 1 pomeni, da je oseba moški, vrednost 0 pa pomeni, da ni, in še en stolpec, kjer vrednost 1 pomeni, da je oseba ženska, in 0 pomeni, da ni, je drugi stolpec odvečen, saj lahko njegove vrednosti pridobimo tako, da vrednosti iz stolpca »moški« odštejemo od vseh vrednosti 1 v stolpcu, ki ga je dodala funkcija LINEST.

  • Ko iz modela zaradi kolinearnosti ni odstranjen noben stolpec, vrednost df izračunamo tako: če imamo k stolpcev z vrednostmi znani_x-i in je argument konstanta = TRUE ali je izpuščen, potem velja: df = n – k – 1. Če je argument konstanta = FALSE, potem velja: df = n - k. V obeh primerih pa se za vsak stolpec X, ki je bil odstranjen zaradi kolinearnosti, vrednost df poveča za 1.

  • Formule, katerih rezultat so matrike, morate vnašati kot matrične formule.

    Opomba :  V programu Excel Online ni mogoče ustvarjati matričnih formul.

  • Kadar matrično konstanto (kot je znani_x-i) vnašate kot argument, s podpičji ločite vrednosti v isti vrstici in s poševnicami nazaj ločite posamezne vrstice. Ločilni znaki so lahko tudi drugi, odvisno od področnih nastavitev.

  • Bodite pozorni na to, da vrednosti y, predvidene z regresivno analizo, morda niso veljavne, če so zunaj obsega y vrednosti, ki ste jih uporabili za določanje enačbe.

  • Temeljni algoritem, ki se uporablja v funkciji LINEST, je drugačen od temeljnega algoritma, ki se uporablja v funkcijah SLOPE in INTERCEPT. Razlika med tema algoritmoma lahko vodi do različnih rezultatov, ko so podatki nedoločeni in kolinearni. Če so na primer podatkovne točke argumenta znani_y-i 0 in podatkovne točke argumenta znani_x-i 1:

    • LINEST vrne vrednost 0. Algoritem funkcije LINEST je oblikovan tako, da vrne stvarne rezultate za kolinearne podatke in v tem primeru je mogoče poiskati vsaj en odgovor.

    • SLOPE in INTERCEPT vrneta napako #DEL/0!. Algoritem funkcij SLOPE in INTERCEPT je oblikovana za iskanje le enega odgovora in v tem primeru je mogočih več odgovorov.

  • Poleg tega, da lahko s funkcijo LOGEST izračunate statistiko za druge regresijske vrste, lahko s funkcijo LINEST izračunate obseg drugih regresijskih vrst tako, da vnesete funkcije spremenljivk X in Y kot niza X in Y za LINEST. Na primer ta formula:

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

    Deluje, ko imate en stolpec z vrednostmi Y in en stolpec z vrednostmi X za izračun kvadratnega (polinomski vrstnega reda 3) približka oblike:

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

    To formulo lahko prilagodite za izračun drugih vrst regresije, vendar v nekaterih primerih to zahteva prilagoditev izhodnih vrednosti in drugih statistik.

  • Vrednost F-tesat, ki jo vrne funkcija LINEST, se razlikuje od vrednosti F-testa, ki jo vrne funkcija FTEST. LINEST vrne F-statistiko, medtem ko FTEST vrne verjetnost.

Primeri

1. primer – Naklon in y-presečišče

Kopirajte vzorčne podatke iz te tabele in jih prilepite v celico A1 v novem Excelovem delovnem listu. Če želite, da formule prikažejo rezultate, jih izberite, pritisnite F2 in nato tipko ENTER. Po potrebi lahko prilagodite širine stolpcev in si ogledate vse podatke.

Znani y

Znani x

1

0

9

4

5

2

7

3

Rezultat (naklon)

Rezultat (presečišče z y)

2

1

Formula (formula s polji v celicah A7:B7)

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

2. primer – Preprosta linearna regresija

Kopirajte vzorčne podatke iz te tabele in jih prilepite v celico A1 v novem Excelovem delovnem listu. Če želite, da formule prikažejo rezultate, jih izberite, pritisnite F2 in nato tipko ENTER. Po potrebi lahko prilagodite širine stolpcev in si ogledate vse podatke.

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 €

Izračuna oceno prodaje v devetem mesecu, ki temelji na prodaji od 1. do 6. meseca.

3. primer – Večkratna linearna regresija

Kopirajte vzorčne podatke iz te tabele in jih prilepite v celico A1 v novem Excelovem delovnem listu. Če želite, da formule prikažejo rezultate, jih izberite, pritisnite F2 in nato tipko ENTER. Po potrebi lahko prilagodite širine stolpcev in si ogledate vse podatke.

Površina tal (x1)

Pisarne (x2)

Vhodi (x3)

Starost (x4)

Ocenjena vrednost (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

Formula (formula s polji v celicah A14:A18)

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

4. primer – Uporaba statistik F in r2

V prejšnjem primeru je bil determinacijski koeficient ali r2 enak 0,99675 (glejte celico A17 v rezultatu funkcije LINEST), kar pomeni veliko odvisnost med neodvisnimi spremenljivkami in prodajno ceno. Če želite ugotoviti, ali so se rezultati tako visoke vrednosti r2 pojavili po naključju, uporabite statistiko F.

Za trenutek si zamislite, da odnos med spremenljivkami sploh ne obstaja, ampak da ste izbrali redek vzorec 11 poslovnih prostorov, kar je povzročilo, da statistične analize prikazujejo močan odnos. Izraz »alfa« se uporablja za verjetnost zmotnih zaključkov, da odnos obstaja.

Z vrednostma F in df v rezultatu funkcije LINEST lahko ocenite verjetnost, da se naključno pojavi višja vrednost F. F lahko primerjate s kritičnimi vrednostmi v objavljenih tabelah porazdelitve F ali pa s funkcijo FDIST v Excelu izračunajte verjetnost, da se naključno pojavi višja vrednost F. Ustrezna porazdelitev F ima stopnji prostosti v1 in v2. Če je n število podatkovnih točk in je argument »konstanta« = TRUE ali je izpuščen, potem velja v1 = n – df – 1 in v2 = df. (Če je argument »konstanta« = FALSE, potem velja v1 = n – df in v2 = df.) Funkcija FDIST – s sintakso FDIST(F;v1;v2) – vrne verjetnost, da se naključno pojavi višja vrednost F. V tem primeru velja df = 6 (celica B18) in F = 459,753674 (celica A18).

Če privzamemo vrednost argumenta »alfa« 0,05, v1 = 11 – 6 – 1 = 4 in v2 = 6, je kritična raven F enaka 4,53. Ker je vrednost F = 459,753674 veliko večja od 4,53, je zelo malo verjetno, da se je tako visoka vrednost F pojavila naključno. (Pri vrednosti argumenta »alfa« = 0,05 moramo hipotezo, da med argumentoma znani_y-i in znani_x-i ni povezave, zavrniti, ko F preseže kritično raven 4,53). Z Excelovo funkcije FDIST lahko izračunate verjetnost, da se je tako visoka vrednost F pojavila naključno. Na primer FDIST(459,753674; 4; 6) = 1,37E-7 pokaže zelo majhno verjetnost. Če najdete kritično raven F v tabeli ali če jo izračunate z Excelovo funkcijo FDIST, lahko ugotovite, da je regresijska enačba uporabna za napovedovanje ocenjenih vrednosti pisarniških zgradb na tem območju. Zapomnite si, da je zelo pomembno, da uporabite pravilne vrednosti v1 in v2, ki ste jih izračunali v prejšnjem odstavku.

5. primer – Izračun statistike t

Drugi hipotetični preskus bo določil, če je vsak koeficient naklona uporaben za ocenjevanje ocenjene vrednosti poslovnega prostora v 3. primeru. Če želite na primer preskusiti starostni koeficient za pomembnost statistike, delite -234,24 (koeficient naklona za starost) z 13,268 (ocenjena standardna napaka koeficienta starosti v celici A15). Opazovana vrednost t je ta:

t = m4 ÷ se4 =-234.24 ÷ 13,268 =-17.7

Če je absolutna vrednost t dovolj visoka, lahko sklepate, da je koeficient naklona uporaben za ocenjevanje ocenjene vrednosti pisarniških zgradb v 3. primeru. Spodnja tabela prikazuje absolutne vrednosti štirih opazovanih vrednosti t.

Če si ogledate tabelo v statističnem priročniku, boste ugotovili, da je dvorepa kritična vrednost t s stopnjo prostosti 6 in argumentom »alfa« = 0,05 enaka 2,447. To kritično vrednost lahko dobite tudi z Excelovo funkcijo TINV. TINV(0,05; 6) = 2,447. Ker je absolutna vrednost t (17,7) večja od 2,447, je starost pomembna spremenljivka, kadar ocenjujete ocenjeno vrednost poslovnega prostora. Statistično pomembnost drugih neodvisnih spremenljivk lahko preskusite na podoben način. V nadaljevanju so opazovane vrednosti t za vsako neodvisno spremenljivko.

Spremenljivka

opazovana vrednost t

Kvadratura

5,1

Število pisarn

31,3

Število vhodov

4,8

Starost

17,7

Absolutne vrednosti teh spremenljivk so vse večje od 2,447; zaradi tega so vse spremenljivke, uporabljene v regresivni enačbi, uporabne za napoved ocenjene vrednosti poslovnih prostorov v predelu.

Razširite svoja znanja
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×