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 je na voljo več obsegov vrednosti x, kjer so odvisne vrednosti y funkcija neodvisnih x-vrednosti. Vrednosti m so koeficienti, ki ustrezajo vsaki vrednosti x, 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 statistika TRUE, LINEST vrne dodatno regresijsko statistiko; posledično je vrnjena matrika {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:

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 ugotoviti 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 na črti tako, da v enačbo vstavite 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:

  Naklon:
  =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).

• S funkcijami za prilagajanje črte in krivulj LINEST in LOGEST lahko izračunate najboljšo ravno črto ali eksponentno krivuljo, ki se prilega vašim podatkov. Vendar pa se morate odločiti, kateri od obeh rezultatov najbolje ustreza vašim podatkovom. TREND(known_y, known_x) lahko izračunate za ravno črto ali GROWTH(known_y, known_x) za eksponentno krivuljo. Te funkcije brez argumenta new_x vrne matriko vrednosti y, predvidenih vzdolž te črte ali krivulje, v dejanskih podatkovnih točkah. Nato lahko primerjate predvidene vrednosti z dejanskimi vrednostmi. Morda ju boste želeli oba grafikona z vizualno primerjavo.

• V regresijski analizi Excel za vsako točko izračuna kvadratno razliko med ocenjeno vrednostjo y za to točko in dejansko vrednostjo y. Vsota teh kvadratnih razlik se imenuje vsota ostankov kvadratov, ssresid. Excel izračuna skupno vsoto kvadratov, delna vsota. Če je argument »konst« = TRUE ali pa ga izpustite, je skupna vsota kvadratov vsota kvadratov razlik med dejanskimi vrednostmi y in povprečjem y-ov. Če je argument »konst« = FALSE, je skupna vsota kvadratov vsota kvadratov dejanskih y-vrednosti (ne da bi se odštela povprečna vrednost y od vsake posamezne y-vrednosti). Nato lahko regresijsko vsoto kvadratov, ssreg, najdete na tem: ssreg = sstotal - ssresid. Manjša vsota ostankov kvadratov je v primerjavi s skupno vsoto kvadratov, večja je vrednost koeficienta določne vrednosti, r², ki je indikator, kako dobro enačba, ki je posledica regresijske analize, razloži razmerje med spremenljivkami. Vrednost r^{2 je} enaka ssreg/sstotal.

• V nekaterih primerih eden ali več stolpcev X (recimo, da sta Y-i in X v stolpcih) morda nima dodatne napovedne vrednosti v prisotnosti drugih stolpcev X. Z drugimi besedami, če odstranite enega ali več stolpcev X, lahko to privede do napovenih vrednosti Y, ki so enako natančne. V tem primeru iz regresivnega modela izpustite te odvečne stolpce X. Ta poziv se imenuje »kolinearnost«, saj lahko kateri koli odvečni stolpec X izraziti kot vsoto večkratcev nepotrebnih stolpcev X. Funkcija LINEST preveri kolinearnost in odstrani odvečne stolpce X iz regresivnega modela, ko jih določi. Odstranjene stolpce X je v rezultatu LINEST mogoče prepoznati tako, da imajo poleg vrednosti 0 se tudi 0 koeficientov. Č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 spremeni df, ker so odstranjeni odvečni stolpci X, vplivate tudi na vrednosti sey in F. Kolinearnost bi morala biti razmeroma redka v vaji. Vendar pa je bolj verjetno, da bo to primer, ko nekateri stolpci X vsebujejo le 0 in 1 vrednosti kot indikatorje, ali je določen predmet v poskusu ali ne član določene skupine. Če je argument »konst« = TRUE ali če ga izpustite, funkcija LINEST učinkovito vstavi dodatni stolpec X vseh 1 vrednosti v model presečišče. Če imate stolpec z 1 za vsako zadevo, če ni, ali 0, če ni, in imate stolpec z 1 za vsako zadevo, če ženska, ali 0, če ni, je ta zadnji stolpec odveč, saj je mogoče vnose v tem stolpcu dobiti od odštevanje vnosa v stolpcu »moški« od vnosa v dodatnem stolpcu vseh 1 vrednosti, ki jih doda 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.

• 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 oblikovan za iskanje le enega odgovora in v tem primeru je lahko 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