Funktsioon LINEST Excel - lineaarse regressiooni statistika

Laadige alla töövihiku näide

Laadige alla töövihiku näide

See õpetus näitab, kuidas Funktsioon Excel LINEST Excelis trendijoone statistika arvutamiseks.

Funktsiooni LINEST ülevaade

Funktsioon LINEST Arvutab teadaolevatele andmepunktidele sobitatud trendijoone statistika väikseimate ruutude meetodil.

LINEST Exceli töölehe funktsiooni kasutamiseks valige lahter ja tippige:

(Pange tähele, kuidas valemisisendid ilmuvad)

Funktsioon LINEST Süntaks ja sisendid

1	= LINEST (teadaolevad_ys, tuntud_x -d, konst, statistika)

tuntud_y - Massiiv teadaolevaid Y väärtusi.

tuntud_x - Teadaolevate X -väärtuste massiiv.

const - VALIKULINE. Loogiline väärtus, mis näitab, kas arvutada B (lõikepunkt y = mx + b) vähimruutude meetodil (TRUE or Ommitted) või määrata käsitsi B = 0 (FALSE).

statistika - VALIKULINE. Tagastab täiendavat statistikat (TÕENE) või tagastab ainult m (kalle) ja b (pealtkuulamine) (VÄÄR või välja jäetud)

Mis on LINEST?

Funktsioon LINEST Excelis on funktsioon, mida kasutatakse lineaarse regressioonimudeli regressioonistatistika genereerimiseks. LINEST on massiivivalem ja seda saab kasutada üksi või koos muude funktsioonidega mudeli konkreetse statistika arvutamiseks.

Lineaarne regressioon on statistika meetod, mida kasutatakse andmete ennustamiseks sirgjooneliselt, kasutades teadaolevaid andmeid. Regressiooni kasutatakse selliste väärtuste ennustamiseks nagu müügi kasv, varude nõuded või lihtsad turusuundumused.

LINEST sarnaneb prognoosiga, kuna saavutab sarnase tulemuse, kuid sisaldab palju rohkem teavet teie regressioonimudeli kohta ja võimalust sobitada rohkem kui üks sõltumatu muutuja.

Oletame, et mul on andmete tabel x ja y väärtused kus x on sõltumatu muutuja ja y on sõltuv muutuja:

Tahan teada, milline on ülaltoodud andmete regressioonivõrrand. LINESTi kasutamine:

1	= LIND (B3: B7, C3: C7, TRUE, FALSE)

Siin on y-lõikepunkti väärtus teaduslikus tähenduses võrdne 0-ga.

Sirge võrrand on y = 2x + 0. Pange tähele, et LINEST tagastab mõlemad joone kalle ja lõikumine. Mõlema väärtuse tagastamiseks tuleb valem sisestada massiivivalemina. Massiivivalemitest lähemalt hiljem.

Kuidas LINESTit kasutada

Funktsioon LINEST sisaldab nelja argumenti.

1	= LINEST (teadaolevad_y -d, tuntud_x -d, konst, statistika)

Kus,

Argument	Kirjeldus
tuntud_y ja tuntud_x	On x ja y andmed teie andmetabelis
const	ÕIGE/VÄÄR valik, kas y-lõikepunkt tuleks sundida 0-le või arvutada normaalselt
statistika	TRUE/FALSE valik, kas täiendav regressioonistatistika tuleks tagastada

Kasutades meie esimest näidet, kirjutatakse funktsioon järgmiselt:

1	= LIND (B3: B7, C3: C7, TRUE, FALSE)

Kui statistika suvandiks TRUE, on regressioonistatistika korraldus järgmine:

Teil võib tekkida küsimus, mida iga muutuja tähendab.

Statistika	Kirjeldus
mn	Kalde koefitsiendid x muutujat
b	y-pealtkuulamine
sen	Iga tõusu koefitsiendi standardviga
seb	Y-lõikepistiku standardviga
r2	Määramistegur
sey	Standardviga y hinnang
F	F statistika (et teha kindlaks, kas muutujate suhe tekib juhuslikult)
df	Vabadusastmed
ssreg	Ruutude regressioonisumma
ssjääk	Ruutude jääksumma

Peamine statistika, millest aru saada, on kalde koefitsiendid, y-lõikepunkt ja määramistegur või r² mudeli väärtus.

Kasutades ülaltoodud näidet ja valides väärtuseks TRUE statistika parameeter:

Esiletõstetud lahtrid näitavad kallakut = 2, lõikepunkti = 0 ja r² = 1.

R² väärtus on mudeli korrelatsiooni tugevuse näitaja. Seda võib pidada sobivuse näitajaks. Madal r² väärtus tähendaks halba korrelatsiooni teie sõltuvate ja sõltumatute muutujate vahel ning kõrge r puhul on vastupidi² väärtused, koos r² = 1 sobib ideaalselt.

Väljaannetes pärast jaanuari 2022. aasta Microsoft 365 (varem Office 365) Exceli väljaandeid on dünaamilised massiivid muutnud massiivivalemite hindamise viisi. Enam pole vaja kasutada klahvikombinatsiooni CTRL + SHIFT + ENTER ega esile tõsta lahtrite ala, mille massiiv võtab. Lihtsalt sisestage valem ja klõpsake sisestusklahvi ning tulemuseks olevad rakud “valguvad” massiivi välja.

Selle artikli ülejäänud osas viitame LINEST -i kasutamisele Microsoft 365 Exceli dünaamiliste massiivide osas.

Ennustamine LINEST (lihtne regressioon) abil

Funktsioonide LINEST ja SUM kombineerimist saab kasutada sõltuva muutuja väärtuse ennustamiseks y, antud teada x ja y andmed. Allpool on näide, mis näitab y väärtus on siis, kui x = 14.

1	= SUMMA (LIND (C3: C7, B3: B7)*{14,1})

Mudel on kujul y = mx + b. See on sama, mis y = a+ bx, lihtsalt erinev viis võrrandi esitamiseks. Näpunäide, mida lineaarvõrrandite puhul meeles pidada, on kõrval olev muutuja x on alati kalle ja pluss- või miinusmärgile järgnev muutuja on alati lõikepunkt, sõltumata võrrandis kasutatud tähtedest.

Kasutades valemit: = SUM (LINEST (C3: C7, B3: B7)*{14,1}) tagastab tulemuse 28. Kuna tegemist on ühekordse tulemusega, pole massiivina sisestamine vajalik.

Ülaltoodud valemi tagumine ots *{14,1} määrab sõltumatu muutuja, mida kasutatakse sõltuva muutuja ennustamiseks, antud juhul 14.

Seda saame kontrollida, sisestades rea võrrandisse x = 14, y = 2x + 0.

Prognoosimine LINEST (mitu lineaarset regressiooni) abil

Järgmine andmetabel on pärit Microsofti toe veebisaidi LINEST lehelt.

Mõnel juhul on lineaarse regressioonimudeli loomisel rohkem kui üks sõltumatu muutuja. Seda nimetatakse mitu lineaarset regressiooni (st mitu sõltumatut muutujat). Kui ma tahan hinnata büroohoone maksumust, oleksid võrrandi osad sellised asjad nagu põrandapind, hoonete sissepääsude arv, hoone vanus ja kontorite arv. Vaatame näidet.

Kui sisestate lahtrisse G29 valemi LINEST ja käivitame selle, saame:

1	= LIND (E3: E13, A3: D13, TRUE, TRUE)

Mudel on saadaval järgmisel kujul:

Pidage meeles, et tulemuste massiiv LINEST on võrrandist vastupidises järjekorras. Ülaltoodud näites on 52 317,8 meie lõikepunkt, b ja 27,6 on meie m₁ või muutuja Põrandaruum kalle, x₁.

Kasutades esitatud andmetega funktsiooni LINEST, on meie regressioonimudel järgmine:

Koos r² väärtus 0,997, mis näitab tugevat või väga korrelatsioonis mudelit. Mudeli abil saate nüüd ülaltoodud sõltumatute muutujate mis tahes kombinatsiooni põhjal ennustada, milline on büroohoone hinnatav väärtus.

LINEST näpunäiteid

1. Veenduge, et teil oleks Microsoft 365 uusim versioon, et kasutada LINESTi dünaamiliste massiividega. Dünaamilise massiivi funktsioonide kasutamiseks peate võib -olla lubama Office Insideri praeguse kanali (eelvaate). Konto lehel:
   
2. Kui kasutate mitte-Microsoft 365 väljaannet, peate massiivivalemite hindamiseks kasutama pärandmeetodit CTRL + SHIFT + ENTER (CSE).
3. Pärandmeetodi kasutamisel on LINEST massiivifunktsiooni sisestamisel esile tõstetavate veergude arv alati x muutujaid teie andmetes pluss 1. Massiivi jaoks valitud ridade arv on 5.
4. Kui jagate oma dünaamilise massiivi toega Exceli versiooni kellegagi, kes kasutab mitte-Microsoft 365 versiooni, kasutage ühilduvusprobleemide vältimiseks päranditarkvara meetodit.

Kas olete huvitatud täpsematest prognoosidest?

Vaadake meie teisi artikleid prognoosimise kohta eksponentsiaalse silumise, TRENDI, KASVU ja LOGEST funktsioonidega.

LINEST funktsioon Google'i arvutustabelites

Funktsioon LINEST töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis.

LINEST Näited VBA -s

Funktsiooni LINEST saate kasutada ka VBA -s. Tüüp:
application.worksheetfunction.linest (teadaolevad_is, teadaolevad_x -d, konst, statistika)

Järgmise VBA avalduse täitmine

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

annab järgmised tulemused

Funktsiooniargumentide (tuntud_y jne) jaoks saate need kas otse funktsiooni sisestada või määrata selle asemel kasutatavad muutujad.

Tagasi kõigi Exceli funktsioonide loendisse