Funkcija LINEST

V tem članku sta opisani sintaksa formule in uporaba funkcije LINEST v Microsoft Excelu.

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 pa je konstanta. 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:

• known_y Obvezno. Nabor vrednosti y, ki jih že poznate v razmerju y = mx + b.

  • Če je obseg known_y-jev v enem stolpcu, je vsak stolpec known_x-jev obravnavan kot ločena spremenljivka.
  • Če je obseg known_y v eni vrstici, je vsaka vrstica known_x-jev obravnavana kot ločena spremenljivka.

• known_x Izbirno. Nabor vrednosti x, ki jih morda že poznate v razmerju y = mx + b.

  • Obseg known_x lahko vključuje enega ali več naborov spremenljivk. Če uporabljate samo eno spremenljivko, sta lahko known_y-ja in known_x-ja obsega katere koli oblike, samo da imata enake razsežnosti. Če pa uporabljate več kot eno spremenljivko, morajo biti known_y vektor (torej obseg z višino ene vrstice in širino enega stolpca).
  • Če izpustite known_x , privzame program zanj vrednost matrike {1;2;3,...}, ki je iste velikosti kot matrika known_y.

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

  • Če je argument »konstanta« enak TRUE ali če ga izpustite, funkcija izračuna b na normalen način.
  • Če je konst FALSE, nastavi program b na 0 in vrednost m se prilagodi tako, da ustrezajo y = mx.

• statistika Izbirno. 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 pa ga izpustite, vrne LINEST le koeficiente m in konstanto b.
    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, ko je 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število 2 , glejte »Opombe« v nadaljevanju te teme.
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.

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. 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-i; known_x-i); 1)
  Y-presečišče:
  =INDEX(LINEST(known_y-i; known_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:
  
  
  pri čemer sta x in y vzorčni srednji vrednosti; To pomeni, x = AVERAGE(znani x-i) in y = AVERAGE(known_y-i).

• S funkcijama za prileganje črt in krivulj LINEST in LOGEST lahko izračunate najboljšo ravno črto ali eksponentno krivuljo, ki ustreza vašim podatkom. Vendar se morate odločiti, kateri od obeh rezultatov najbolj ustreza vašim podatkom. TREND(known_y-i; known_x-i) lahko izračunate za premico ali GROWTH(known_y-i, known_x-i) za eksponentno krivuljo. Te funkcije, brez argumenta new_x , vrnejo matriko vrednosti y, predvidene vzdolž te premice ali krivulje v dejanskih podatkovnih točkah. Nato lahko primerjate predvidene vrednosti z dejanskimi vrednostmi. Morda ju boste želeli prikazati v grafikonu za vizualno primerjavo.

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

• V nekaterih primerih eden ali več stolpcev X (predpostavimo, da sta stolpca Y in X v stolpcih) morda nima dodatne napovedne 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 regresijskega modela. Ta pojav se imenuje "kolinearnost", ker se lahko vsak odvečni stolpec X izrazi kot vsota večkratnikov neredundantnih stolpcev X. Funkcija LINEST preveri kolinearnost in odstrani vse odvečne stolpce X iz regresijskega modela, ko jih prepozna. Odstranjene stolpce X lahko v izhodu funkcije LINEST prepoznate kot stolpce z 0 poleg vrednosti 0 se. Če je eden ali več stolpcev odstranjenih kot odvečnih, to vpliva na df, ker je df odvisen od števila stolpcev X, ki se dejansko uporabljajo za napovedne namene. Za podrobnosti o izračunu df glej primer 4. Če se df spremeni, ker so odstranjeni odvečni stolpci X, to vpliva tudi na vrednosti sey in F. Kolinearnost bi morala biti v praksi razmeroma redka. Vendar pa je eden od primerov, ko je bolj verjetno, da se bo pojavil, ko nekateri stolpci X vsebujejo le vrednosti 0 in 1 kot kazalnike, ali je subjekt v poskusu član določene skupine ali ne. Če je const = TRUE ali je izpuščena, funkcija LINEST učinkovito vstavi dodaten stolpec X z vsemi 1 vrednostmi za modeliranje presezna. Če imate stolpec z 1 za vsak predmet, če je moški, ali 0, če ne, in imate tudi stolpec z 1 za vsak predmet, če je ženska, ali 0, če ni, je slednji stolpec odveč, ker je vnose v njem mogoče pridobiti tako, da vnos v stolpcu »moški indikator« odštejemo od vnosa v dodatnem stolpcu vseh 1 vrednosti, ki jih doda funkcija LINEST .

• Vrednost df se izračuna na naslednji način, ko zaradi kolinearnosti iz modela ni odstranjenih noben stolpec X: če je k stolpcev known_x in const = TRUE ali je izpuščeno, df = n – k – 1. Če je const = FALSE, df = n - k. V obeh primerih pa se za vsak stolpec X, ki je bil odstranjen zaradi kolinearnosti, vrednost df poveča za 1.

• Ko kot argument vnašate konstanto polja (na primer konstanto known_x), uporabite vejice za ločevanje vrednosti, ki so v isti vrstici, in podpičja za ločevanje vrstic. 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 known_y 0 in podatkovne točke argumenta known_x 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 išče samo en odgovor, v tem primeru pa je lahko več kot en odgovor.

• 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 z dinamičnim poljem, vnesena v celico A19)
=LINEST(E2:E12,A2:D12,TRUE,TRUE)

4. primer – Uporaba statistik F in R²

V prejšnjem primeru je bil determinacijski koeficient ali r² 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 rezultatu funkcije LINEST lahko uporabite za oceno verjetnosti, da se bo višja vrednost F pojavila naključno. F lahko primerjamo s kritičnimi vrednostmi v objavljenih F-porazdelitvenih tabelah, funkcijo FDIST v Excelu pa lahko uporabite za izračun verjetnosti, da se bo večja vrednost F pojavila naključno. Ustrezna F-porazdelitev ima prostostne stopnje v1 in v2. Če je n število podatkovnih točk in je konca = TRUE ali izpuščena, potem je v1 = n – df – 1 in v2 = df. (Če je konstanta = FALSE, potem je v1 = n – df in v2 = df.) Funkcija FDIST – s sintakso FDIST(F,v1,v2) – bo vrnila verjetnost, da se bo višja vrednost F pojavila naključno. 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 je treba hipotezo, da med known_y in known_x ni povezave, zavrniti, ko F preseže kritično raven 4,53.) Z Excelovo funkcijo 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, ali 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 sklepamo, da je koeficient naklona uporaben za ocenjevanje ocenjene vrednosti poslovnega prostora v 3. primeru. V spodnji tabeli so prikazane 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.