LINEST (funkcija LINEST)

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 obstaja več obsegov vrednosti x, kjer so odvisne vrednosti y funkcija neodvisnih x-vrednosti. Vrednosti m so koeficienti, ki ustrezajo vsaki x-vrednosti, b pa je konstantna vrednost. Vedite, da so y, x in m lahko tudi vektorji. Matrika, ki jo vrne funkcija LINEST , je {mn,mn-1,...,m1,b}. LINEST lahko vrne tudi dodatno regresijsko 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, vrne LINEST dodatno regresijsko statistiko; posledično je vrnjena matrika {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r^2,sey; F; df; ssreg, ssresid}.

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

    Dodatne regresivne statistike so:

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

Opombe

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

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

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

  Enačba ravne črte je y = mx + b. Ko poznate vrednosti m in b, lahko izračunate katero koli točko v vrstici tako, da v enačbo priključite y ali x. Uporabite lahko tudi funkcijo TREND .

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

  Pobočju:
  =INDEX(LINEST(known_y; known_x; 1)

  Y-presečišče:
  =INDEX(LINEST(known_y; known_x); 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:

  

  

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

• Funkciji line- in curve-fitting LINEST in LOGEST lahko izračunata najboljšo ravno črto ali eksponentno krivuljo, ki ustreza vašim podatkov. Vendar pa se morate odločiti, kateri od obeh rezultatov najbolje ustreza vašim podatkovom. TREND(e known_y,known_x) lahko izračunate za ravno črto ali GROWTH(known_y-e, known_x-e) za eksponentno krivuljo. Te funkcije brez argumenta new_x vrne matriko vrednosti y, napovedanih vzdolž te črte ali krivulje v dejanskih podatkovnih točkah. Nato lahko primerjate predvidene vrednosti z dejanskimi vrednostmi. Morda boste želeli obe grafikoni ustvariti v obliki grafikona za vizualno primerjavo.

• V regresijski analizi Excel za vsako točko izračuna kvadratno razliko med y-vrednostjo, ocenjeno za to točko in dejansko vrednostjo y. Vsota teh kvadratnih razlik se imenuje vsota ostankov kvadratov, ssresid. Excel nato izračuna skupno vsoto kvadratov, sstotal. Če je argument konstanta = TRUE ali je izpuščen, je skupna vsota kvadratov vsota kvadratnih razlik med dejanskimi vrednostmi y in povprečjem y-vrednosti. Ko je argument » konstanta« = FALSE, je skupna vsota kvadratov vsota kvadratov dejanskih y-vrednosti (brez odštevanja povprečne vrednosti y od vsake posamezne y-vrednosti). Nato regresijsko vsoto kvadratov, ssreg, je mogoče najti iz: ssreg = sstotal - ssresid. Manjša kot je vsota ostankov kvadratov, v primerjavi s skupno vsoto kvadratov, večja je vrednost koeficienta določne vrednosti r², ki je kazalnik, kako dobro enačba, ki je posledica regresijske analize, pojasnjuje razmerje med spremenljivkami. Vrednost r^{2 je} enaka ssreg/sstotal.

• V nekaterih primerih eden ali več stolpcev X (recimo, da sta stolpca Y in X) morda nima dodatne napovedne vrednosti v prisotnosti drugih stolpcev X. Z drugimi besedami, če odstranite enega ali več stolpcev X, boste morda vodili do enako točnih predvidenih vrednosti Y. V tem primeru morate te odvečne stolpce X izpustiti iz regresivnega modela. Ta pojav se imenuje kolinearnost, ker lahko kateri koli odvečni stolpec X izrazite kot vsoto večkratnikov ne redundantnih stolpcev X. Funkcija LINEST preveri kolinearnost in odstrani odvečne stolpce X iz regresivnega modela, ko jih prepozna. Odstranjene stolpce X je v linestu mogoče prepoznati kot koeficiente 0 poleg vrednosti 0 se. Če odstranite enega ali več stolpcev kot odvečnih, to vpliva na df, ker je df odvisen od števila stolpcev X, ki se dejansko uporabljajo za predvidevanje. Če želite podrobnosti o izračunu df, glejte 4. primer. Če se vrednost df spremeni, ker so odstranjeni odvečni stolpci X, vplivate tudi na vrednosti sey in F. Kolinearnost mora biti v praksi razmeroma redka. Vendar pa je bolj verjetno, da bo prišlo do tega, če nekateri stolpci X vsebujejo le vrednosti 0 in 1 kot indikator, ali je predmet v preskusu ali ne član določene skupine. Če je argument konstanta = TRUE ali je izpuščen, funkcija LINEST učinkovito vstavi dodatni stolpec X vseh vrednosti 1 za modeliranje presečišče. Če imate stolpec z 1 za vsak predmet, če je moški, ali 0, če ni, in imate tudi stolpec z 1 za vsak predmet, če je ženski, ali 0, če ni, je ta zadnji stolpec odveč, ker je vnose v njem mogoče dobiti odštte iz vnosa v stolpcu »moški indikator« od vnosa v dodatni stolpec vseh 1 vrednosti, dodanih s funkcijo 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.

• 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 #DIV/0! napaka #REF!. Algoritem funkcij SLOPE in INTERCEPT je zasnovan tako, da poišče le en odgovor in v tem primeru je lahko odgovor več.

• 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