V tem članku je opisana 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 obstaja več obsegov vrednosti x, kjer so odvisne vrednosti y funkcija neodvisnih vrednosti x. M-vrednosti 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 dodatne regresijske statistične podatke.
Sintaksa
LINEST(znani_y-i, [znani_x-i], [konstanta], [statistika])
V sintaksi funkcije LINEST so ti argumenti:
Sintaksa
known_y Obvezno. Nabor vrednosti y, ki jih že poznate v razmerju y = mx + b.
- Če je obseg known_y v enem stolpcu, se vsak stolpec known_x razlaga kot ločena spremenljivka.
- Če je obseg known_y v eni vrstici, se vsaka vrstica known_x razlaga kot ločena spremenljivka.
known_x Neobvezno. 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 je uporabljena samo ena spremenljivka, sta lahko known_y in known_x obsega poljubne oblike, če imata enake dimenzije. Če je uporabljenih več kot ena spremenljivka, mora biti known_y vektor (to je obseg z višino ene vrstice ali širino enega stolpca).
- Če je known_x izpuščena, se domneva, da je matrika {1,2,3,...} enake velikosti kot known_y.
Zaključek Neobvezno. Logična vrednost, ki navaja, ali naj bo konstanta b enaka 0.
- Če je const TRUE ali izpuščen, se b izračuna normalno.
- Če je const FALSE, je b nastavljen na 0, m-vrednosti pa se prilagodijo tako, da ustrezajo y = mx.
Statistični podatki Neobvezno. 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; r2,sey; F, df; ssreg,ssresid}.
- Če je argument »statistika « FALSE ali izpuščen, vrne LINEST le m-koeficiente in konstanto b.
Dodatne regresivne statistike so:
| Statistika | Opis |
|---|---|
| se1,se2,...,sen | Standardne vrednosti napak za koeficiente m1,m2,...,mn. |
| seb | Standardna vrednost napake za konstanto b (seb = #N/A, ko je const FALSE). |
| Pravilo2 | 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 več informacij o tem, kako se izračuna2 , 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 svobode. Uporabite prostostne stopnje, da boste lažje našli F-kritične vrednosti v statistični tabeli. Primerjajte vrednosti, ki jih najdete v tabeli, s statistiko F, ki jo vrne funkcija LINEST , da določite 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.
Opombe
Vsako premico lahko opišete z naklonom in y-presečišči:
Naklon (m):
Če želite najti naklon premice, ki se pogosto piše kot m, vzemite dve točki na premici, (x1,y1) in (x2,y2); Naklon je enak (y2 - y1)/(x2 - x1).
Prestrezanje Y (b):
Presečišče črte y, ki se pogosto piše kot b, je vrednost y na točki, kjer črta prečka 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 to enačbo priključite vrednost 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:
=INDEKS(LINEST(known_y;known_x-I);1)
Prestrezanje Y:
=INDEKS(LINEST(known_y;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:
kjer sta x in y vzorčni vrednosti; to pomeni, da je x = POVPREČJE (znano x) in y = POVPREČJE (known_y).Funkciji za prileganje črt in krivulj LINEST in LOGEST lahko izračunata najboljšo ravno črto ali eksponentno krivuljo, ki ustreza vašim podatkom. Vendar se morate odločiti, kateri od obeh rezultatov najbolje ustreza vašim podatkom. Izračunate lahko TREND(known_y,known_x-ih) za ravno črto ali GROWTH(known_y-ih, known_x-ih) za eksponentno krivuljo. Te funkcije brez argumenta new_x vrnejo matriko vrednosti y, predvidenih vzdolž te črte ali krivulje na dejanskih podatkovnih točkah. Nato lahko primerjate predvidene vrednosti z dejanskimi vrednostmi. Morda ju boste želeli prikazati za vizualno primerjavo.
V regresijski analizi Excel za vsako točko izračuna kvadratno razliko med vrednostjo y, ocenjeno za to točko, in njeno dejansko vrednostjo y. Vsota teh kvadratnih razlik se imenuje preostala vsota kvadratov, ssresid. Excel izračuna skupno vsoto kvadratov, sstotal. Če je argument konstante = TRUE ali ga izpustite, je skupna vsota kvadratov vsota kvadriranih razlik med dejanskimi y-vrednostmi in povprečjem y-vrednosti. Če je argument konstante = FALSE, je skupna vsota kvadratov vsota kvadratov dejanskih vrednosti y (brez odštevanja povprečne vrednosti y od vsake posamezne vrednosti y). Regresijsko vsoto kvadratov, ssreg, lahko poiščete iz: ssreg = sstotal - ssresid. Manjša kot je vsota preostalih kvadratov v primerjavi s skupno vsoto kvadratov, večja je vrednost koeficienta določnosti, r2, ki je pokazatelj, kako dobro enačba, ki je rezultat regresijske analize, pojasnjuje razmerje med spremenljivkami. Vrednost r2 je enaka ssreg/sstotal.
V nekaterih primerih eden ali več stolpcev X (recimo, da sta Y 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 pride do predvidenih vrednosti Y, ki so enako točne. V tem primeru je treba te odvečne stolpce X izpustiti iz regresijskega modela. Ta pojav se imenuje »kolinearnost«, ker je vsak odvečni stolpec X lahko izražen kot vsota večkratnikov neredundantnih stolpcev X. Funkcija LINEST preveri kolinearnost in odstrani vse odvečne stolpce X iz regresijskega modela, ko jih identificira. Odstranjene stolpce X lahko v rezultatu funkcije LINEST prepoznate tako, da imajo poleg vrednosti 0 se tudi koeficient 0. Če enega ali več stolpcev odstranite kot odvečne, to vpliva na df, ker je df odvisen od števila X stolpcev, ki so dejansko uporabljeni za predvidevanje. Za podrobnosti o izračunu df glejte 4. primer. Č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 bolj verjetno, da se bo pojavila, če nekateri stolpci X vsebujejo samo vrednosti 0 in 1 kot indikator, ali subjekt v poskusu je ali ni član določene skupine. Če je argument »konstanta « = TRUE ali če ga izpustite, funkcija LINEST učinkovito vstavi dodaten stolpec X vseh vrednosti 1 za modeliranje presečišča. Če imate stolpec z 1 za vsako zadevo, če je moški, ali z 0, če ni, in imate tudi stolpec z 1 za vsak predmet, če je ženska, ali z 0, če ni, je slednji stolpec odveč, ker je vnose v njem mogoče pridobiti tako, da se vnos v stolpcu »moški indikator« odšteje od vnosa v dodatnem stolpcu vseh vrednosti 1, 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 known_x inje konst = TRUE ali je izpuščen, potem velja: df = n – k – 1. Če je 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 known_x-a) 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 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 le en odgovor 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
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 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 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.