LINEST funkcija Excel - tiesinės regresijos statistika

Atsisiųskite pavyzdinę darbo knygą

Atsisiųskite darbaknygės pavyzdį

Ši pamoka parodo, kaip naudotis „Excel LINEST“ funkcija „Excel“ skaičiuoti tendencijų linijos statistiką.

LINEST funkcijų apžvalga

Funkcija LINEST Skaičiuoja tendencijų linijos, pritaikytos žinomiems duomenų taškams, statistiką, naudojant mažiausių kvadratų metodą.

Norėdami naudoti LINEST Excel darbalapio funkciją, pasirinkite langelį ir įveskite:

(Atkreipkite dėmesį, kaip atrodo formulės įvestys)

Funkcija LINEST Sintaksė ir įvestys

1	= LINEST (žinomi_ys, žinomi_xs, konst, statistika)

žinomas_y - Žinomų Y reikšmių masyvas.

žinomi_x - Žinomų X reikšmių masyvas.

konst - PASIRENKAMA. Loginė vertė, rodanti, ar apskaičiuoti B (perėmimas y = mx + b) naudojant mažiausių kvadratų metodą (TRUE Or Ommitted), ar rankiniu būdu nustatyti B = 0 (FALSE).

statistika - PASIRENKAMA. Pateikti papildomą statistiką (TRUE) arba grąžinti tik m (nuolydis) ir b (perimti) (FALSE arba praleista)

Kas yra LINEST?

Funkcija LINEST programoje „Excel“ yra funkcija, naudojama generuoti tiesinės regresijos modelio regresijos statistiką. LINEST yra masyvo formulė ir gali būti naudojama atskirai arba kartu su kitomis funkcijomis, norint apskaičiuoti konkrečią modelio statistiką.

Tiesinė regresija yra statistikos metodas, naudojamas prognozuoti duomenis po tiesia linija, naudojant žinomus duomenis. Regresija naudojama prognozuoti tokias vertes kaip pardavimų augimas, atsargų reikalavimai ar paprastos rinkos tendencijos.

LINEST yra panašus į PROGNOZĘ, nes pasiekia panašų rezultatą, tačiau turi daug daugiau informacijos apie jūsų regresijos modelį ir galimybę pritaikyti daugiau nei vieną nepriklausomą kintamąjį.

Tarkime, kad turiu duomenų lentelę su x ir y vertybes kur x yra nepriklausomas kintamasis ir y yra priklausomas kintamasis:

Noriu sužinoti, kas yra aukščiau pateiktų duomenų regresijos lygtis. Naudojant LINEST:

1	= LINIŠKIAUSIA (B3: B7, C3: C7, TRUE, FALSE)

Y-perėmimo vertė čia yra lygi 0, moksliškai žymint.

Tiesės lygtis y = 2x + 0. Atminkite, kad LINEST grąžina tiek linijos nuolydis ir perėjimas. Norint grąžinti abi reikšmes, formulė turi būti įvesta kaip masyvo formulė. Daugiau apie masyvo formules vėliau.

Kaip naudoti LINEST

Funkcija LINEST turi keturis argumentus:

1	= LINEST (žinomi_y, žinomi_x, konst, statistika)

Kur,

Argumentas	apibūdinimas
žinomas_y ir žinomi_x	Ar x ir y duomenų lentelėje
konst	TRUE/FALSE parinktis, ar y-pjūvis turi būti priverstas 0 arba normaliai apskaičiuotas
statistika	TRUE/FALSE parinktis, ar reikia grąžinti papildomą regresijos statistiką

Naudojant pirmąjį mūsų pavyzdį, funkcija parašyta taip:

1	= LINIŠKIAUSIA (B3: B7, C3: C7, TIESA, NETIESA)

Kai statistika parinktis nustatyta į TRUE, regresijos statistikos organizavimas yra toks:

Jums gali būti įdomu, ką reiškia kiekvienas kintamasis.

Statistika	apibūdinimas
mn	Nuolydžio koeficientai, skirti x kintamieji
b	y-perimti
sen	Standartinė kiekvieno nuolydžio koeficiento paklaida
seb	Standartinė y perėmimo klaida
r2	Nustatymo koeficientas
sey	Standartinė klaida y sąmata
F	F statistika (norint nustatyti, ar kintamųjų ryšys atsiranda atsitiktinai)
df	Laisvės laipsniai
ssreg	Regresinė kvadratų suma
ssliekana	Likusi kvadratų suma

Pagrindiniai statistiniai duomenys, kuriuos reikia suprasti, yra nuolydžio koeficientai, y pjūvis ir nustatymo koeficientas arba r² modelio vertė.

Naudodami aukščiau pateiktą pavyzdį ir pasirinkdami TRUE statistika parametras:

Pažymėtos ląstelės rodo nuolydį = 2, perėmimą = 0 ir r² = 1.

R² vertė yra modelio koreliacijos stiprumo rodiklis. Tai galima laikyti tinkamumo rodikliu. Mažas r² reikšmė reikštų prastą jūsų priklausomų ir nepriklausomų kintamųjų koreliaciją, o aukštam r yra priešingai² vertės, su r² = 1 puikiai tinka.

Išleidimuose po 2022 m. Sausio mėn. „Excel“ programoje „Microsoft 365“ (anksčiau „Office 365“) dinaminiai masyvai pakeitė masyvo formulių vertinimo būdą. Nebereikia naudoti CTRL + SHIFT + ENTER arba paryškinti masyvo užimamų langelių srities. Tiesiog įveskite formulę ir spustelėkite „Enter“, o gautos ląstelės „išsilieja“ į masyvą.

Likusioje šio straipsnio dalyje kalbėsime apie LINEST naudojimą dinaminių masyvų programoje „Microsoft 365 Excel“.

Prognozavimas naudojant LINEST (paprasta regresija)

Sujungus LINEST ir SUM funkcijas, galima numatyti priklausomo kintamojo vertę y, atsižvelgiant į žinomą x ir y duomenis. Žemiau yra pavyzdys, parodantis, kas yra y reikšmė bus, kai x = 14.

1	= SUMA (LINIJA (C3: C7, B3: B7)*{14,1})

Modelio forma yra y = mx + b. Tai tas pats, kas y = a+ bx, tik kitoks lygties atvaizdavimo būdas. Patarimas, kurį reikia turėti omenyje tiesinėms lygtims, yra kintamasis šalia x visada yra nuolydis, o kintamasis, einantis po pliuso ar minuso ženklo, visada yra perėmimas, neatsižvelgiant į lygtyje naudojamas raides.

Naudojant formulę: = SUM (LINEST (C3: C7, B3: B7)*{14,1}) pateikia rezultatą 28. Kadangi tai yra vienas rezultatas, nebūtina įvesti kaip masyvo.

Anksčiau pateiktos formulės *{14,1} uodega nurodo nepriklausomą kintamąjį, kuris turi būti naudojamas priklausomam kintamajam numatyti, šiuo atveju 14.

Tai galime patikrinti įvesdami x = 14 tiesės lygtyje, y = 2x + 0.

Prognozavimas naudojant LINEST (kelių linijų regresiją)

Ši duomenų lentelė yra iš „Microsoft“ palaikymo svetainės LINEST puslapio.

Kai kuriais atvejais, kuriant tiesinės regresijos modelį, reikia atsižvelgti į daugiau nei vieną nepriklausomą kintamąjį. Tai vadinama daugybine tiesine regresija (t. Y. Keliais nepriklausomais kintamaisiais). Jei noriu apskaičiuoti biurų pastato kainą, lygties dalis būtų tokie dalykai kaip plotas, įėjimų į pastatą skaičius, pastato amžius ir biurų skaičius. Pažiūrėkime pavyzdį.

Įvedę LINEST formulę į langelį G29 ir ją vykdydami, gauname:

1	= LINIŠKIAUSIA (E3: E13, A3: D13, TRUE, TRUE)

Modelis pateikiamas tokia forma:

Atminkite, kad LINEST rezultatų masyvas yra atvirkštine tvarka iš lygties. Aukščiau pateiktame pavyzdyje 52 317,8 yra mūsų perimta, b ir 27,6 yra mūsų m₁ arba „Floor space“ kintamojo nuolydžio reikšmė, x₁.

Naudojant funkciją LINEST su pateiktais duomenimis, mūsų regresijos modelis yra toks:

Su r² vertė yra 0,997, o tai rodo stiprų arba labai koreliuojantį modelį. Naudodamiesi modeliu, dabar galite nuspėti, kokia bus biurų pastato įvertinta vertė, remiantis bet kuriuo aukščiau išvardytų nepriklausomų kintamųjų deriniu.

LINESTINIAI patarimai

1. Įsitikinkite, kad turite naujausią „Microsoft 365“ versiją, kad galėtumėte naudoti LINEST su dinaminiais masyvais. Jei norite naudoti dinaminio masyvo funkcijas, gali tekti įgalinti „Office Insider Current Channel“ (peržiūra). Paskyros puslapyje:
   
2. Jei naudojate ne „Microsoft 365“ leidimą, masyvo formulių vertinimui turėsite naudoti seną CTRL + SHIFT + ENTER (TPP) metodą.
3. Jei naudojate seną metodą, stulpelių, kuriuos reikia pabrėžti įvedant LINEST masyvo funkciją, skaičius visada yra x kintamieji jūsų duomenyse plius 1. Masyvui pasirinkti eilučių skaičius yra 5.
4. Jei bendrinsite „Excel“ versiją, kurioje įgalintas dinaminis masyvas, su asmeniu, kuris naudoja ne „Microsoft 365“ leidimą, naudokite seną TPP metodą, kad išvengtumėte suderinamumo problemų.

Domina daugiau prognozių?

Peržiūrėkite kitus mūsų straipsnius apie prognozavimą naudojant eksponentinio išlyginimo, TREND, GROWTH ir LOGEST funkcijas.

LINIŠKIAUSIA funkcija „Google“ skaičiuoklėse

Funkcija LINEST „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“.

LINESTI VBA pavyzdžiai

Taip pat galite naudoti funkciją LINEST VBA. Tipas:
application.worksheetfunction.linest (žinomi_ys, žinomi_xs, konst, statistika)

Vykdykite šį VBA teiginį

1	Diapazonas ("D2") = Application.WorksheetFunction.LinEst (Diapazonas ("A2: A8"), diapazonas ("B2: B8"))

duos šiuos rezultatus

Funkcijos argumentams (žinomiems_y ir kt.) Galite juos įvesti tiesiai į funkciją arba nustatyti kintamuosius, kuriuos naudoti.

Grįžkite į visų „Excel“ funkcijų sąrašą