LINEST (funkcija LINEST)

Aprašas

Funkcija LINEST apskaičiuoja linijos statistiką naudodama metodą "mažiausi kvadratai", kad apskaičiuotų tiesę, kuri geriausiai atitinka jūsų duomenis, tada grąžina masyvą, aprašantį liniją. Taip pat galite derinti LINEST su kitomis funkcijomis, kad apskaičiuotumėte kitų tipų modelių, kurie yra tiesiniai nežinomuose parametruose, įskaitant polinominius, logaritmines, eksponentines ir laipsnines sekas, statistiką. Ši funkcija grąžina reikšmių masyvą, todėl ji turi būti įvedama kaip masyvo formulė. Instrukcijos pateiktos pagal šiame straipsnyje pateiktus pavyzdžius.

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 (teisinga), LINEST grąžina papildomus regresinės statistikos duomenis; todėl grąžintas masyvas yra {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r², sey; F,df; ssreg,ssresid}.

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

    Papildomos regresinės statistikos duomenys yra šie.

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

Pastabos

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

  Krypties koeficientas (m):
  Norėdami rasti linijos nuolydį, dažnai užrašytą kaip m, take two points on the line, (x1,y1) and (x2,y2); krypties koeficientas yra lygus (y2 - y1)/(x2 - x1).

  Y ašis (b):
  Linijos y ašis, dažnai parašyta kaip b, yra y reikšmė taške, kuriame linija 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:

  Nuolydis:
  =INDEX(LINEST(known_y,known_x's),1)

  Y ašis:
  =INDEX(LINEST(known_y,known_x's),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:

  

  

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

• Linijos ir kreivės montavimo funkcijos LINEST ir LOGEST gali apskaičiuoti geriausią tiesę arba eksponentinę kreivę, atitinkančią jūsų duomenis. Tačiau turite nuspręsti, kuris iš dviejų rezultatų geriausiai atitinka jūsų duomenis. Trend(known_y,known_x's) galite apskaičiuoti tiesei linijai arba GROWTH(known_yknown_x's) – eksponentinė kreivei. Šios funkcijos, be new_x argumento, pateikia masyvą y reikšmių, prognozuojamų išilgai tos linijos, arba kreivę ties faktiniais duomenų taškais. Tada galima palyginti prognozuojamas reikšmes su faktinėmis reikšmėmis. Galite norėti juos abu pateikti vaizdiniam palyginimui.

• 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 r² reikšmė, o tai rodo, kaip gerai regresinės analizės metu gauta lygtis paaiškina santykį tarp kintamųjų. r² reikšmė lygi ssreg/sstotal.

• Kai kuriais atvejais vienas ar daugiau X stulpelių (tarkime, kad Y ir X yra stulpeliuose) gali neturėti jokios papildomos nuspėjamosios reikšmės, jei yra kitų X stulpelių. Kitaip tariant, panaikinus vieną ar daugiau X stulpelių, gali būti prognozuojamos Y reikšmės, kurios yra vienodai tikslios. Tokiu atveju šie pertekliniai X stulpeliai turėtų būti praleisti iš regresijos modelio. Šis reiškinys vadinamas "kolinearumu", nes bet kuris nereikalingas X stulpelis gali būti išreikštas kaip perteklinių X stulpelių kartotinių suma. Funkcija LINEST tikrina, ar yra kolinearumo, ir pašalina visus perteklinius X stulpelius iš regresijos modelio, kai juos identifikuoja. Pašalinti X stulpeliai gali būti atpažįstami funkcijos LINEST išvestyje kaip turintys 0 koeficientų kartu su 0 se reikšmėmis. Jei vienas ar daugiau stulpelių pašalinami kaip pertekliniai, ll paveiktas, nes ll priklauso nuo X stulpelių skaičiaus, iš tikrųjų naudojamų nuspėjamiems tikslams. Daugiau informacijos apie ll skaičiavimą žr. 4 pavyzdyje. Jei ll pakeičiamas, nes pašalinami pertekliniai X stulpeliai, taip pat paveikiamos sey ir F reikšmės. Kolinearumas praktiškai turi būti palyginti retas. Tačiau vienas atvejis, kai labiau tikėtina, kad jis atsiras, kai kai kuriuose X stulpeliuose kaip indikatorius yra tik 0 ir 1 reikšmės, ar eksperimento objektas yra konkrečios grupės narys, ar ne. Jei konstanta = TRUE arba praleista, linest funkcija efektyviai įterpia papildomą X stulpelį iš visų 1 reikšmių, kad modeliuotų ašį. Jei turite stulpelį, kuriame yra po 1 kiekvienam dalykui (jei vyras) arba 0, jei ne, ir stulpelį, kuriame yra po 1 kiekvienam subjektui (jei moteris, arba 0, jei ne), šis stulpelis yra nereikalingas, nes jame esančius įrašus galima gauti iš stulpelio "vyriškas indikatorius" atimant įrašą iš visų 1 reikšmių, kurias pridėjo funkcija LINEST , papildomo stulpelio įrašo.

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

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

  • SLOPE ir INTERCEPT grąžina #DIV/0! klaidą. Funkcijų SLOPE ir INTERCEPT algoritmas skirtas ieškoti tik vieno atsakymo, 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