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 na voljo več obsegov vrednosti x, kjer so odvisne y-vrednosti funkcija neodvisnih vrednosti x. Vrednosti m so koeficienti, ki ustrezajo posamezni vrednosti x, in b 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, 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

Stopnja svobode. Uporabite stopnjo svobode, s katero boste lažje našli F-kritične vrednosti v Statistični tabeli. Primerjajte vrednosti, ki jih najdete v tabeli, na F-statistiko, ki jo vrne LINEST , da določi raven zaupanja za model. Če želite več informacij o tem, kako se izračuna df, glejte» opombe «v nadaljevanju te teme. Primer 4 prikazuje uporabo 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 ravne črte je y = mx + b. Ko poznate vrednosti m in b, lahko izračunate poljubno mesto v vrstici tako, da priključite y-ali x-vrednost v to enačbo. 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(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).

  • Funkcije za vgradnjo črte in krivulje LINEST in LOGEST lahko izračunajo najboljšo premico ali eksponentno krivuljo, ki ustreza vašim podatkom. Vendar se morate odločiti, kateri od obeh rezultatov se najbolje ujema s podatki. Za eksponentno krivuljo lahko izračunate trend (known_y ' s, known_x' s) za premico ali rast (known_y' s; known_x' s) . Te funkcije, brez argumenta known , vrnejo polje y-vrednosti, predvidene ob tej vrstici ali krivulji na dejanskih podatkovnih točkah. Nato lahko primerjate predvidene vrednosti z dejanskimi vrednostmi. Morda boste želeli grafikon primerjati z vizualno primerjavo.

  • V regresivni analizi Excel izračuna za vsako točkovno razliko kvadrata med ocenjeno vrednostjo za to mesto in dejansko y vrednostjo. Vsota teh razlik kvadratov se imenuje preostala vsota kvadratov, ssresid. Excel nato izračuna skupno vsoto kvadratov, sstotal. Ko je argument CONST = TRUE ali je izpuščen, je skupna vsota kvadratov vsota razlik kvadratov med dejanskimi y vrednostmi in povprečjem y-vrednosti. Ko je argument CONST = FALSE, je skupna vsota kvadratov vsota kvadratov dejanskih y vrednosti (ne da bi odšteli povprečno y-vrednost iz posamezne y vrednosti). Nato regresivna vsota kvadratov, ssreg, lahko najdete v: ssreg = sstotal-ssresid. Manjša je preostala vsota kvadratov v primerjavi s skupno vsoto kvadratov, večja je vrednost koeficienta določanja, R2, ki je indikator, kako dobro je enačba, ki izhaja iz regresivne analize, razloži razmerje med spremenljivke. Vrednost R2 je enaka ssreg/sstotal.

  • V nekaterih primerih je eden ali več stolpcev X (Predpostavimo, da sta Y in X v stolpcih) morda ne bo imela dodatne predvidene vrednosti v prisotnosti drugih stolpcev X. Z drugimi besedami, odstranitev enega ali več stolpcev X lahko privede do predvidenih vrednosti Y, ki so enako natančne. V tem primeru je treba te odvečne stolpce X izpustiti iz regresivnega modela. Ta pojav se imenuje» collinearity «, ker je kateri koli odvečni stolpec X lahko izražen kot vsota večkratnikov stolpcev, ki niso odvečne X. Funkcija LINEST preveri collinearity in odstrani morebitne odvečne stolpce X iz regresivnega modela, ko jih prepozna. Odstranjene stolpce X je mogoče prepoznati v rezultatih funkcije LINEST , ker so poleg vrednosti 0. Če je eden ali več stolpcev odstranjenih kot odvečne, je DF prizadet, ker je DF odvisen od števila stolpcev X, ki se dejansko uporabljajo za predvidevanje. Če želite več informacij o izračunavanju funkcije DF, glejte primer 4. Če je argument» DF «spremenjen, ker so odvečni stolpci X odstranjeni, vplivajo tudi vrednosti Sey in F. Collinearity bi moral biti razmeroma redek v praksi. Vendar pa je v enem primeru večja verjetnost, da so v nekaterih stolpcih X le vrednosti 0 in 1 kot indikatorji, ali je predmet v poskusu ali ni član določene skupine. Če je argument» CONST «= TRUE ali je izpuščen, funkcija LINEST učinkovito Vstavi dodaten stolpec X vseh vrednosti 1, da model prestreže. Če imate stolpec z 1 za vsako zadevo, če je moški ali 0, če ne, in imate tudi stolpec z 1 za vsako zadevo, če je samica ali 0, če ne, je ta stolpec odveč, ker so vnosi v njem lahko pridobljeni od odštevanja vnosa v» moški indikator « stolpec iz vnosa v dodatni stolpec z vsemi 1 vrednostmi, 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.

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

    Opomba: V Excel Online ne morete ustvariti formul s polji.

  • 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.

    • Naklon in prestrezanje vrne #DIV/0! napaka #REF!. Algoritem funkcije nagiba in prestrezanja je zasnovan tako, da išče le en odgovor, v tem primeru pa lahko pride do 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.

Vrednosti F in DF v izhodu iz funkcije LINEST lahko uporabite za oceno verjetnosti višje vrednosti F, ki se pojavi po naključju. F lahko primerjate s kritičnimi vrednostmi v objavljenih tabelah F-porazdelitve ali funkcijo FDIST v Excelu lahko uporabite za izračun verjetnosti večje vrednosti F, ki se pojavi po naključju. Ustrezna porazdelitev F ima v1 in v2 stopinje svobode. Če je n število podatkovnih točk in Const = TRUE ali izpuščeno, nato v1 = n – DF – 1 in v2 = DF. (Če je CONST = FALSE, nato v1 = n – DF in v2 = DF.) Funkcija FDIST – s sintakso FDIST(F, v1, v2) – vrne verjetnost višje vrednosti F, ki se pojavi po naključju. V tem primeru je 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

Drug preskus hipoteze bo določil, ali je vsak koeficient nagiba uporaben pri ocenjevanju ocenjene vrednosti sistema Office v primeru 3. Na primer, če želite preskusiti starostni koeficient za statistično pomembnost, 234,24 (koeficient starostnega nagiba) do 13,268 (ocenjena standardna napaka starostnih koeficientov v celici A15). To je t-opazovana vrednost:

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

Če je absolutna vrednost t dovolj visoka, je mogoče zaključiti, da je koeficient nagiba uporaben pri ocenjevanju ocenjene vrednosti sistema Office v primeru 3. V spodnji tabeli so prikazane absolutne vrednosti 4 t-opazovanih vrednosti.

Č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.

Opomba:  Ta stran je bila prevedena z avtomatizacijo in lahko vsebuje slovnične napake ali nepravilnosti. Želimo, da bi bila ta vsebina za vas uporabna. Ali nam lahko sporočite, če so bile te informacije uporabne? Tukaj je referenčni članek v angleščini.

Razširite poznavanje Officea
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.

×