LINEST (funkcija LINEST)

Šiame straipsnyje aprašoma formulės sintaksė ir funkcijos LINEST naudojimas programoje „Microsoft Excel“. Saitai su išsamesne informacija apie regresinės analizės atlikimą ir jos diagramų kūrimą pateikiami skyriuje Taip pat skaitykite.

Aprašas

Funkcija LINEST skaičiuoja tiesės statistiką naudodama mažiausiųjų kvadratų metodą, skirtą geriausiai jūsų duomenis atitinkančiai tiesei apskaičiuoti, ir tada grąžina masyvą, aprašantį liniją. Ši funkcija grąžina reikšmių masyvą, todėl ji turi būti įvedama kaip masyvo formulė. Funkciją LINEST galite derinti su kitomis funkcijomis, jei norite skaičiuoti kitų tipų modelių, kurių nežinomi parametrai yra tiesiniai, statistiką, įskaitant polinomines, logaritmines ir laipsnio rodiklio sekas. Kadangi funkcija grąžina reikšmių masyvą, ją reikia įvesti kaip masyvo formulę. Nurodymai, kaip tai daryti, pateikti šio straipsnio pavyzdžiais.

Tiesės lygtis yra:

y = mx + b

Arba

y = m1x1 + m2x2 + ... + b

jeigu yra keli x reikšmių diapazonai, kur priklausoma y reikšmė yra nepriklausomų x reikšmių funkcija. M reikšmės yra kiekvieną x reikšmę atitinkantys koeficientai, o b yra konstanta. Atkreipkite dėmesį, kad y, x ir m gali būti vektoriai. Masyvas, kurį grąžina funkcija LINEST, yra {mn,mn-1,...,m1,b}. LINEST taip pat gali grąžinti papildomus regresinės statistikos duomenis.

Sintaksė

LINEST(žinomi_y, [žinomi_x], [konst], [statistika])

Funkcijos LINEST sintaksė turi tokius argumentus:

Sintaksė

  • žinomi_y    Būtinas. Susijęs su jau žinomų y reikšmių aibe y = mx + b.

    • Jei žinomi_y diapazonas yra viename stulpelyje, kiekvienas žinomų_x stulpelis laikomas atskiru kintamuoju.

    • Jei žinomi_y diapazonas yra vienoje eilutėje, kiekviena žinomų_x eilutė laikoma atskiru kintamuoju.

  • žinomi_x    Pasirinktinis. Nebūtinai susiję su jau žinomų x reikšmių aibe y = mx + b.

    • Žinomi_x diapazone gali būti viena ar daugiau kintamųjų aibių. Jei naudojamas tik vienas kintamasis, žinomi_y ir žinomi_x gali būti bet kokios formos diapazonai, jei tik jų dimensijos sutampa. Jei naudojama daugiau nei vienas kintamasis, žinomi_y turi būti vektorius (t. y. diapazonas, kurio aukštis – viena eilutė arba plotis – vienas stulpelis).

    • Jei argumentas žinomi_x praleistas, laikoma, kad tai masyvas {1;2;3,...}, kuris yra tokio pat dydžio kaip ir žinomi_y.

  • konst    Pasirinktinis. Loginė reikšmė, nurodanti, ar konstanta b turi būti lygi 0.

    • Jei konstanta yra TRUE arba praleista, b skaičiuojama įprastai.

    • Jei konstanta yra FALSE, b prilyginama 0, o m reikšmės koreguojamos taip, kad atitiktų lygybę y = mx.

  • statistika    Pasirinktinis. Loginė reikšmė, nurodanti, ar reikia grąžinti papildomus regresinės statistikos duomenis.

    • Jei statistika yra TRUE, LINEST grąžina papildomus regresinės statistikos duomenis, todėl grąžintas masyvas yra {mn;mn-1;...;m1;b;spn;spn-1;...;sp1;spb;r2;spy;F;ll;ksreg;ksliek}.

    • Jei statistika yra FALSE arba praleista, LINEST grąžina tik m koeficientus ir konstantą b.

      Papildomos regresinės statistikos duomenys yra šie.

Statistikos duomuo

Aprašas

sp1;sp2;...;spn

Standartinės paklaidos koeficientų m1,m2,...,mn reikšmės.

spb

Standartinės paklaidos konstantos b reikšmė (jei spb = #N/A argumentas, konstanta yra FALSE).

r2

Determinacijos koeficientas. Lygina apskaičiuotąsias ir tikrąsias y reikšmes ir yra nuo 0 iki 1. Jei jis yra 1, pavyzdyje egzistuoja ideali koreliacija – nėra jokio skirtumo tarp apskaičiuotosios ir tikrosios y reikšmės. Kitas kraštutinumas – jei determinacijos koeficientas yra 0, regresinė lygtis nėra naudinga prognozuojant y reikšmę. Plačiau apie tai, kaip skaičiuojamas r2, rasite šios temos skyriuje „Pastabos“.

spy

Y įverčio standartinė paklaida.

F

F statistika arba F stebima reikšmė. Naudokite F statistiką norėdami nustatyti, ar stebimas ryšys tarp priklausomų ir nepriklausomų kintamųjų yra atsitiktinis.

ll

Laisvės laipsniai. Laisvės laipsniais galite rasti F kritines reikšmes statistinėje lentelėje. Lyginkite lentelėje esančias reikšmes su funkcijos LINEST grąžinama F statistika, norėdami nustatyti modelio pasikliautinumo lygmenį. Plačiau apie tai, kaip skaičiuojamas ll, rasite šios temos skyriuje „Pastabos“. Toliau pateikiamame 4 pavyzdyje demonstruojamas F ir ll naudojimas.

ksreg

Regresijos kvadratų suma.

ksliek

Liekamoji kvadratų suma. Plačiau apie tai, kaip skaičiuojami ksreg ir ksliek, rasite šios temos skyriuje „Pastabos“.

Ši iliustracija rodo tvarką, kuria grąžinami papildomi regresinės statistikos duomenys.

Darbalapis

Pastabos

  • Bet kokią tiesę galite aprašyti krypties koeficientu ir y postūmiu:

    Krypties koeficientas (m):
    norėdami rasti tiesės krypties koeficientą, dažnai žymimą m, paimkite du tiesėje esančius taškus (x1;y1) ir (x2;y2); krypties koeficientas lygus (y2 - y1)/(x2 - x1).

    Y postūmis (b):
    tiesės y postūmis, dažnai žymimas b, yra y reikšmė taške, kuriame tiesė kerta y ašį.

    Tiesės lygtis yra y = mx + b. Jei žinotei reikšmes m ir b, galite apskaičiuoti bet kokį tiesėje esantį tašką, į šią lygtį įrašydami y arba x reikšmę. Taip pat galite naudoti funkciją TREND.

  • Jei turite tik vieną nepriklausomą kintamąjį x, galite gauti krypties koeficiento ir y postūmio reikšmes tiesiogiai naudodami šias formules:

    Krypties koeficientas:
    =INDEX(LINEST(žinomi_y;žinomi_x);1)

    Y postūmis:
    =INDEX(LINEST(žinomi_y;žinomi_x);2)

  • Funkcijos LINEST apskaičiuotos tiesės tikslumas priklauso nuo jūsų duomenų sklaidos koeficiento. Kuo tiesiškesni duomenys, tuo tikslesnis LINEST modelis. Funkcija LINEST geriausiai jūsų duomenis atitinkantį variantą parenka naudodama mažiausiųjų kvadratų metodą. Jei turite tik vieną nepriklausomą kintamąjį x, m ir b skaičiavimas pagrįstas šiomis formulėmis:

    Lygtis

    Lygtis

    kur x ir y yra pavyzdžio vidurkiai, t. y. x = AVERAGE(žinomi_x), o y = AVERAGE(žinomi_y).

  • Tiesių ir kreivių parinkimo funkcijos LINEST ir LOGEST gali apskaičiuoti geriausiai jūsų duomenis atitinkančią tiesę ar eksponentinę kreivę. Tačiau turėsite nuspręsti, kuris iš dviejų rezultatų geriausiai atitinka jūsų duomenis. Galite apskaičiuoti TREND(žinomi_y;žinomi_x) tiesės arba GROWTH(žinomi_y; žinomi_x) eksponentinės kreivės. Šios funkcijos, be argumento nauji_x, grąžina y reikšmių, numatytų pagal tą liniją ar kreivę ties jūsų realių duomenų taškais, masyvą. Tuomet galite lyginti numatytas reikšmes su realiomis. Jei norite, galite abi jas pavaizduoti diagramoje ir lyginti.

  • Regresinėje analizėje programa „Excel“ kiekvienam taškui randa skirtumo tarp to taško apskaičiuotos y reikšmės ir realios y reikšmės kvadratą. Šių skirtumų kvadratų suma vadinama liekamąja kvadratų suma ksliek. Tuomet programa „Excel“ apskaičiuoja bendrą kvadratų sumą ksbendra. Kai argumentas konstanta = TRUE arba praleistas, bendra kvadratų suma yra kvadratu pakeltų skirtumų tarp tikrųjų y reikšmių ir vidutinių y reikšmių suma. Kai argumentas konstanta = FALSE, bendra kvadratų suma yra kvadratu pakeltų tikrųjų y reikšmių (neatimant vidutinės y reikšmės iš kiekvienos y reikšmės) suma. Tuomet gali būti randama regresijos kvadratų suma ksreg: ksreg = ksbendra - ksliek. Kuo mažesnė liekamoji kvadratų suma, palyginti su bendra kvadratų suma, tuo didesnė determinacijos koeficiento r2 reikšmė, kuri rodo, kiek gerai iš regresinės analizės gauta lygtis paaiškina sąryšius tarp kintamųjų. r2 reikšmė lygi ksreg/ksbendra.

  • Kai kuriais atvejais vienas ar daugiau X stulpelių (laikysime, kad Y ir X yra stulpeliuose) gali neturėti papildomos vertės prognozuoti, atsižvelgiant į kitų X stulpelių buvimą. Kitaip tariant, pašalinę vieną ar daugiau X stulpelių gautume prognozuojamas Y reikšmes, kurios būtų tiek pat tikslios. Tokiu atveju šie pertekliniai X stulpeliai neturėtų būti įtraukiami į regresijos modelį. Šis fenomenas vadinamas „kolinearumu“, nes bet kuris perteklinis X stulpelis gali būti išreikštas kaip neperteklinių X stulpelių kartotinių suma. Funkcija LINEST tikrina kolinearumo buvimą ir iš regresijos modelio pašalina visus aptiktus perteklinius X stulpelius. Pašalinti X stulpeliai funkcijos LINEST išvestyje gali būti atpažinti, nes jų koeficientai ir sp yra 0. Jei vienas ar daugiau stulpelių yra pašalinami kaip pertekliniai, tai paveikia df, nes jis priklauso nuo X stulpelių, realiai naudojamų prognozės tikslams, skaičiaus. Plačiau apie df skaičiavimą žr. 4 pavyzdyje. Jei pašalinus perteklinius X stulpelius pasikeičia df, spy ir F reikšmės irgi pasikeičia. Praktikoje kolinearumas turėtų būti santykinai retas. Tačiau vienu atveju labiau tikėtina, kad jis atsiras – jei kai kurie X stulpeliai yra sudaryti tik iš reikšmių 0 ir 1, rodančių, ar bandymo subjektas yra tam tikros grupės narys, ar ne. Jei konstanta = TRUE arba praleista, funkcija LINEST iš tiesų įterpia papildomą X stulpelį, sudarytą vien iš 1, modeliuodama poslinkį. Jei turite stulpelį, kuriame kiekvienas vyriškos lyties subjektas žymimas 1, o moteriškos – 0, taip pat turite stulpelį, kur kiekvienas moteriškos lyties subjektas žymimas 1, o vyriškos – 0, antrasis stulpelis yra perteklinis, nes jis gali būti gaunamas atėmus „vyriškojo indikatoriaus“ stulpelyje esantį įrašą iš įrašo papildomame stulpelyje, sudarytame vien iš vienetų, kurį prideda funkcija LINEST.

  • Jei nė vienas X stulpelis nepašalinamas iš modelio dėl kolinearumo, ll reikšmė yra skaičiuojama taip: jei egzistuoja kžinomų_x stulpelių, o konstanta = TRUE arba praleista, ll = n – k – 1. Jei konstanta = FALSE, ll = n - k. Abiem atvejais kiekvienas X stulpelis, kuris buvo pašalintas dėl kolinearumo, padidina ll reikšmę vienetu.

  • Formulės, kurios grąžina masyvus, turi būti įvestos kaip masyvo formulės.

    Pastaba :  Programoje „Excel Online“ negalima kurti masyvo formulių.

  • Įvesdami konstantų masyvą (pvz., žinomi_x) kaip argumentą, atskirkite toje pačioje eilutėje esančias reikšmes kableliais, o eilutes – kabliataškiais. Skyriklio simboliai gali skirtis, atsižvelgiant į regiono parametrus.

  • Atkreipkite dėmesį, kad regresijos lygties prognozuojamos y reikšmės gali negalioti, jei jos yra už y reikšmių diapazono, naudoto lyčiai apibrėžti.

  • Esantis algoritmas, kuris naudojamas funkcijoje LINEST, skiriasi nuo funkcijose SLOPE ir INTERCEPT naudojamo algoritmo. Dėl šių algoritmų skirtumo, kai duomenys neapibrėžti ir linijiški, rezultatai gali skirtis. Pavyzdžiui, jei argumento žinomi y duomenų taškai lygūs 0, o argumento žinomi x duomenų taškai lygūs 1:

    • Funkcijos LINEST grąžinama reikšmė lygi 0. Funkcijos LINEST algoritmas skirtas tinkamiems linijiškų duomenų rezultatams grąžinti ir tokiu atveju galima rasti mažiausiai vieną atsakymą.

    • Funkcijos SLOPE ir INTERCEPT grąžina klaidą #DIV/0!. Funkcijų SLOPE ir INTERCEPT algoritmas skirtas vieninteliam atsakymui ieškoti, o šiuo atveju gali būti daugiau nei vienas atsakymas.

  • Be to, kad funkciją LOGEST naudojate kitų regresijų tipų statistikai skaičiuoti, galite naudoti funkciją LINEST kitų regresijų tipų diapazonui skaičiuoti įvesdami x ir y kintamųjų funkcijas kaip funkcijos LINEST x ir y sekas. Pavyzdžiui ši formulė:

    =LINEST(yreikšmės, xreikšmės^STULPELIS($A:$C))

    veikia, kai norite skaičiuoti vieno y reikšmių stulpelio ir vieno x reikšmių stulpelio kubinę formos (trečios eilės polinomo) aproksimaciją:

    y = m1*x + m2*x^2 + m3*x^3 + b

    Galite keisti šią formulę kitiems regresijos tipams skaičiuoti, bet kai kuriais atvejais reikia derinti išvesties reikšmes ir kitus statistinius duomenis.

  • F testo reikšmė, kurią grąžina funkcija LINEST, skiriasi nuo F testo reikšmės grąžinamos funkcijos FTEST. LINEST grąžina F statistiką, o funkcija FTEST grąžina tikimybę.

Pavyzdžiai

1 pavyzdyje: krypties koeficientas ir y poslinkis

Iš pateiktosios lentelės nusikopijuokite pavyzdinius duomenis ir įklijuokite į naujos „Excel“ darbaknygės langelį A1. Kad formulės rodytų rezultatus, jas pažymėkite, paspauskite F2 ir spauskite Enter. Jeigu reikia, pakoreguokite langelių plotį, kad matytųsi visi duomenys.

Žinomi y

Žinomi x

1

0

9

4

5

2

7

3

Rezultatas (nuokrypis)

Rezultatas (y ašis)

2

1

Formulė (masyvas diapazone A7:B7)

=LINEST(A2:A5,B2:B5,,FALSE)

2 pavyzdyje: paprasta tiesinė regresija

Iš pateiktosios lentelės nusikopijuokite pavyzdinius duomenis ir įklijuokite į naujos „Excel“ darbaknygės langelį A1. Kad formulės rodytų rezultatus, jas pažymėkite, paspauskite F2 ir spauskite Enter. Jeigu reikia, pakoreguokite langelių plotį, kad matytųsi visi duomenys.

Mėnuo

Pardavimas

1

3 100 EUR

2

4 500 EUR

3

4 400 EUR

4

5 400 EUR

5

7 500 EUR

6

8 100 EUR

Formulė

Rezultatas

=SUM(LINEST(B1:B6, A1:A6)*{9,1})

11 000 EUR

Apskaičiuoja devintojo mėnesio pardavimo įvertį, remiantis pardavimo laikotarpiu nuo 1 iki 6 mėnesio.

3 pavyzdyje: daugybinė tiesinė regresija

Iš pateiktosios lentelės nusikopijuokite pavyzdinius duomenis ir įklijuokite į naujos „Excel“ darbaknygės langelį A1. Kad formulės rodytų rezultatus, jas pažymėkite, paspauskite F2 ir spauskite Enter. Jeigu reikia, pakoreguokite langelių plotį, kad matytųsi visi duomenys.

Grindų plotas (x1)

Biurai (x2)

Įėjimai (x3)

Amžius (x4)

Įkainotoji vertė (y)

2310

2

2

20

142 000 EUR

2333

2

2

12

144 000 EUR

2356

3

1.5

33

151 000 EUR

2379

3

2

43

150 000 EUR

2402

2

3

53

139 000 EUR

2425

4

2

23

169 000 EUR

2448

2

1.5

99

126 000 EUR

2471

2

2

34

142 900 EUR

2494

3

3

23

163 000 EUR

2517

4

4

55

169 000 EUR

2540

2

3

22

149 000 EUR

-234.2371645

13.26801148

0.996747993

459.7536742

1732393319

Formulė (masyvas įvestas diapazone A14:A18)

=LINEST(E2:E12,A2:D12,TRUE,TRUE)

4 pavyzdyje: f ir r2 statistinių duomenų naudojimas

Ankstesniajame pavyzdyje determinacijos koeficientas r2 yra 0,99675 (žr. langelį A17 funkcijos LINEST išvestyje), ir tai rodo stiprų sąryšį tarp nepriklausomų kintamųjų ir pardavimo kainos. Naudodami F statistiką galite nustatyti, ar rezultatai su tokia aukšta r2 reikšme gauti atsitiktinai.

Tarkime, iš tiesų tarp kintamųjų nėra ryšio, bet jūs pasirinkote mažai tikėtiną imtį iš 11 biuro pastatų, kuriems statistinė analizė rodo stiprų sąryšį. Terminas „Alfa“ vartojamas klaidingos išvados apie tokį sąryšį tikimybei apibūdinti.

F ir ll reikšmės funkcijos LINEST išvestyje gali būti naudojamos norint įvertinti tikimybę, kad aukšta F reikšmė buvo atsitiktinė. F gali būti lyginamas su kritinėmis reikšmėmis, esančiomis paskelbtose F pasiskirstymo lentelėse, arba galima naudoti programos „Excel“ funkciją FDIST tikimybei, kad didesnė F reikšmė pasirodys atsitiktinai, įvertinti. Atitinkamas F pasiskirstymas turi v1 ir v2 laisvės laipsnius. Jei n yra duomenų taškų skaičius ir konstanta = TRUE arba praleista, tada v1 = n – ll – 1 ir v2 = ll. (Jei konstanta = FALSE, tada v1 = n – ll ir v2 = ll.) Funkcija FDIST, kurios sintaksė FDIST(F,v1,v2) , grąžins atsitiktinio didesnės F reikšmės pasirodymo tikimybę. 4 pavyzdyje ll = 6 (langelis B18) ir F = 459,753674 (langelis A18).

Imant Alfa reikšmę 0,05, v1 = 11 – 6 – 1 = 4 ir v2 = 6, kritinis F lygis yra 4,53. Kadangi F = 459,753674 yra daug didesnis už 4,53, labai mažai tikėtina, kad tokia didelė F reikšmė pasirodė atsitiktinai. (Kai Alfa = 0,05, hipotezė, kad tarp žinomų_y ir žinomų_x nėra sąryšio, turi būti atmetama F viršijus kritinį lygį 4,53). Galite naudoti programos „Excel“ funkciją FDIST apskaičiuoti tikimybei, kad tokia didelė F reikšmė buvo atsitiktinė. Pvz., FDIST(459,753674; 4; 6) = 1,37E-7, labai maža tikimybė. Naudodami kritinę F reikšmę lentelėje arba funkciją FDIST galite daryti išvadą, kad regresijos lygtis yra naudinga numatant įkainotąją pastatų vertę šioje vietovėje. Nepamirškite, kad labai svarbu naudoti teisingas prieš tai apskaičiuotas v1 ir v2 reikšmes.

5 pavyzdyje: t statistikos skaičiavimas

Kitu hipotezės tikrinimu bus nustatoma, kuris krypties koeficientas yra naudingas apskaičiuojant įkainotąją biuro pastato vertę 3 pavyzdyje. Pavyzdžiui, norėdami patikrinti amžiaus koeficiento statistinį reikšmingumą, padalykite -234,24 (amžiaus krypties koeficientas) iš 13,268 (amžiaus koeficientų apskaičiuotoji standartinė paklaida, esanti langelyje A15). Tai yra t stebimoji vertė:

t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

Jei vertės t modulis yra pakankamai didelis, gali būti daroma išvada, kad krypties koeficientas yra naudingas apskaičiuojant įkainotąją biuro pastatų vertę, žr. 3 pavyzdyje. Lentelėje pateikiami 4 t stebimųjų reikšmių moduliai.

Galite pasižiūrėti į lentelę statistikos vadovėlyje. Ten pamatysite, kad teigiamai arba neigiamai galinti nukrypti t kritinė reišmė, kurios minimalus parametrų kiekis yra 6, o Alfa = 0,05, yra 2,447. Šią kritinę reikšmę taip pat galite rasti naudodami programos „Excel“ funkciją TINV. TINV (0,05;6) = 2,447. Kadangi absoliučioji t reikšmė (17,7) yra didesnė už 2,447, amžius yra svarbus kintamasis, apskaičiuojant įkainotąją turto vertę. Kiekvieno kito nepriklausomo kintamojo statistinis reikšmingumas gali būti patikrintas panašiu būdu. Toliau pateikiamos t stebimosios reikšmės kiekvienam iš nepriklausomų kintamųjų.

Kintamasis

t stebimoji reikšmė

Grindų plotas

5,1

Biurų skaičius

31,3

Įėjimų skaičius

4,8

Amžius

17,7

Šių reikšmių absoliučioji reikšmė yra didesnė nei 2,447; taigi visi regresijos lygtyje panaudoti kintamieji yra naudingi nuspėjant įkainotąją biuro pastatų vertę šioje vietovėje.

Tobulinkite savo įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×