LINEST -funktio Excel - Lineaarinen regressiotilasto

Lataa esimerkkityökirja

Lataa esimerkkityökirja

Tämä opetusohjelma osoittaa, miten Excel LINEST -toiminto Excelissä laskemaan trendiviivan tilastot.

LINEST -toiminnon yleiskatsaus

LINEST -funktio laskee tilastot trendiviivasta, joka on sovitettu tunnettuihin datapisteisiin pienimpien neliöiden menetelmällä.

Jos haluat käyttää LINEST Excel -laskentataulukkotoimintoa, valitse solu ja kirjoita:

(Huomaa, miten kaavasyötteet näkyvät)

LINEST -toiminto Syntaksi ja tulot

1	= LINEST (tunnettu_ys, tunnettu_xs, const, tilastot)

tiedossa_v - Joukko tunnettuja Y -arvoja.

tiedossa_x - Joukko tunnettuja X -arvoja.

const - VALINNAINEN. Looginen arvo, joka ilmaisee, lasketaanko B (leikkaus y = mx + b) käyttäen pienimmän neliösumman menetelmää (TRUE Or Ommitted) vai asetetaanko manuaalisesti B = 0 (FALSE).

tilastot - VALINNAINEN. Palauta lisätilastoja (TOSI) tai palauta vain m (kaltevuus) ja b (sieppaus) (EPÄTOSI tai puuttuu)

Mikä on LINEST?

LINEST -funktio Excelissä on funktio, jolla luodaan regressiotilastoja lineaariselle regressiomallille. LINEST on matriisikaava, ja sitä voidaan käyttää yksin tai muiden toimintojen kanssa mallin tilastojen laskemiseen.

Lineaarinen regressio on tilastomenetelmä, jota käytetään ennustamaan tietoja suoran linjan jälkeen käyttämällä tunnettua dataa. Regressiota käytetään ennustamaan arvoja, kuten myynnin kasvua, varastovaatimuksia tai yksinkertaisia ​​markkinatrendejä.

LINEST on kuin ENNUSTE, koska se saavuttaa samanlaisen lopputuloksen, mutta sisältää paljon enemmän tietoa regressiomallistasi ja mahdollisuuden sovittaa useampi kuin yksi riippumaton muuttuja.

Oletetaan, että minulla on taulukko tietoja x ja y arvot missä x on riippumaton muuttuja ja y on riippuvainen muuttuja:

Haluan tietää, mikä on yllä olevien tietojen regressioyhtälö. LINESTin käyttö:

1	= LINJAINEN (B3: B7, C3: C7, TOSI, EPÄTOSI)

Y-leikkausarvo vastaa tässä 0: ta tieteellisessä merkinnässä.

Suoran yhtälö on y = 2x + 0. Huomaa, että LINEST palauttaa molemmat linjan kaltevuus ja leikkaus. Molempien arvojen palauttamiseksi kaava on annettava taulukkokaavana. Lisätietoja matriisikaavoista myöhemmin.

LINESTin käyttö

LINEST -funktio sisältää neljä argumenttia:

1	= LINEST (tunnettu_vuosi, tunnetut_x: t, vakiot, tilastot)

Missä,

Perustelu	Kuvaus
tiedossa_v ja tiedossa_x	On x ja y tiedot taulukossasi
const	TOSI/EPÄTOSI vaihtoehto, onko y-leikkaus pakotettava 0: een vai laskettava normaalisti
tilastot	TOSI/EPÄTOSI vaihtoehto, palautetaanko muita regressiotilastoja

Ensimmäistä esimerkkiä käyttämällä funktio kirjoitetaan seuraavasti:

1	= LINJAINEN (B3: B7, C3: C7, TOSI, EPÄTOSI)

Kun tilastot vaihtoehdon asetuksena on TOSI, regressiotilastojen järjestely on seuraava:

Saatat ihmetellä, mitä kukin muuttuja tarkoittaa.

Tilastollinen	Kuvaus
mn	Kaltevuuskertoimet kohteelle x muuttujaa
b	y-siepata
sen	Vakiovirhe kullekin kaltevuuskertoimelle
seb	Vakiovirhe y-leikkauksessa
r2	Määrityskerroin
sey	Vakiovirhe y arvio
F	F -tilasto (sen selvittämiseksi, tapahtuuko muuttujien suhde sattumalta)
df	Vapauden asteet
ssreg	Neliöiden regressiosumma
ssjäännös	Jäljellä oleva neliöiden summa

Tärkeimmät ymmärrettävät tilastot ovat kaltevuuskertoimet, y-leikkaus ja määrityskerroin tai r² mallin arvo.

Käyttämällä yllä olevaa esimerkkiä ja valitsemalla TOSI tilastot parametri:

Korostetut solut näyttävät kaltevuuden = 2, leikkauksen = 0 ja r² = 1.

R² arvo on osoitus mallin korrelaation vahvuudesta. Sitä voidaan ajatella sopivuuden indikaattorina. Matala r² arvo merkitsisi huonoa korrelaatiota riippuvaisten ja riippumattomien muuttujien välillä, ja päinvastoin on korkea r² arvot, r² = 1 on täydellinen istuvuus.

Microsoft 365: n (aiemmin Office 365) Excelin tammikuun 2022 jälkeisissä julkaisuissa dynaamiset taulukot ovat muuttaneet matriisikaavojen arviointitapaa. Ei enää tarvitse käyttää näppäinyhdistelmää CTRL + VAIHTO + ENTER tai korostaa solualuetta, jonka matriisi ottaa. Kirjoita yksinkertaisesti kaava ja napsauta enter, ja tuloksena olevat solut "valuvat" matriisiin.

Tämän artikkelin loppuosassa viitataan LINESTin käyttöön Microsoft 365 Excelin dynaamisten matriisien osalta.

Ennuste LINESTillä (yksinkertainen regressio)

LINEST- ja SUM -toimintojen yhdistämisellä voidaan ennustaa riippuvaisen muuttujan arvo y, annettu tiedossa x ja y tiedot. Alla on esimerkki siitä, mitä y arvo on, kun x = 14.

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

Malli on muodossa y = mx + b. Tämä on sama kuin y = a+ bx, vain eri tapa esittää yhtälö. Vinkki, joka pitää mielessä lineaarisille yhtälöille, on vieressä oleva muuttuja x on aina kaltevuus, ja plus- tai miinusmerkkiä seuraava muuttuja on aina leikkaus, riippumatta yhtälössä käytetyistä kirjaimista.

Käyttämällä kaavaa: = SUM (LINEST (C3: C7, B3: B7)*{14,1}) palauttaa tuloksen 28. Koska tämä on yksittäinen tulos, sitä ei tarvitse syöttää taulukkona.

Yllä olevan kaavan *{14,1} loppupää määrittelee riippumattoman muuttujan, jota käytetään riippuvan muuttujan ennustamiseen, tässä tapauksessa 14.

Voimme tarkistaa tämän kirjoittamalla x = 14 rivin yhtälöön, y = 2x + 0.

Ennustaminen LINEST -toiminnolla (moninkertainen lineaarinen regressio)

Seuraava tietotaulukko tulee Microsoftin tukisivuston LINEST -sivulta.

Joissakin tapauksissa lineaarista regressiomallia luotaessa on otettava huomioon useampi kuin yksi riippumaton muuttuja. Tätä kutsutaan moninkertaiseksi lineaariseksi regressioksi (eli useiksi riippumattomiksi muuttujiksi). Jos haluan arvioida toimistorakennuksen kustannuksia, esimerkiksi lattiapinta -ala, rakennuksen sisäänkäyntien lukumäärä, rakennuksen ikä ja toimistojen määrä kuuluisivat yhtälöön. Katsotaanpa esimerkkiä.

Kun kirjoitat LINEST -kaavan soluun G29 ja suoritat sen, saamme:

1	= LINJAINEN (E3: E13, A3: D13, TOSI, TOSI)

Malli on muodossa:

Muista, että LINEST -tulostaulukko on käänteisessä järjestyksessä yhtälöstä. Yllä olevassa esimerkissä 52 317,8 on meidän leikkaus, b ja 27,6 on m₁ tai kerroksen kaltevuusarvo, x₁.

Käyttämällä LINEST -funktiota annettujen tietojen kanssa regressiomallimme on:

Jossa r² arvo 0,997, mikä osoittaa vahvan tai hyvin korreloivan mallin. Mallin avulla voit nyt ennustaa mitä toimistorakennuksen arvioitu arvo perustuu mihin tahansa edellä mainittujen riippumattomien muuttujien yhdistelmään.

LINEST Vinkkejä

1. Varmista, että sinulla on Microsoft 365: n uusin versio LINESTin käyttämiseksi dynaamisten matriisien kanssa. Sinun on ehkä otettava käyttöön Office Insider Current Channel (Preview), jotta voit käyttää dynaamisia matriisitoimintoja. Tilisivulla:
   
2. Jos käytät muuta kuin Microsoft 365 -versiota, sinun on käytettävä vanhaa CTRL + VAIHTO + ENTER (CSE) -menetelmää taulukkokaavojen arvioimiseksi.
3. Jos käytät vanhaa menetelmää, korostettavien sarakkeiden määrä LINEST -matriisitoimintoa syötettäessä on aina numero x muuttujat tiedoissasi plus 1. Matriisille valittavien rivien määrä on 5.
4. Jos jaat dynaamisen taulukkotoiminnon Excel-versiosi jonkun muun kuin Microsoft 365 -version kanssa, vältä yhteensopivuusongelmat käyttämällä vanhaa täsmähakumenetelmää.

Kiinnostaako lisää ennusteita?

Katso muita artikkeleitamme ennustamisesta eksponentiaalisen tasoituksen, TRENDIN, KASVUN ja LOGEST -toimintojen avulla.

LINEST toiminto Google Sheetsissä

LINEST -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä.

LINEST Esimerkkejä VBA: sta

Voit myös käyttää LINEST -toimintoa VBA: ssa. Tyyppi:
application.worksheetfunction.linest (tunnettu_ys, tunnettu_xs, const, tilastot)

Seuraavan VBA -lausekkeen suorittaminen

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

tuottaa seuraavat tulokset

Funktioargumenteille (tunnettu_y jne.) Voit joko syöttää ne suoraan funktioon tai määrittää sen sijaan käytettäviä muuttujia.

Palaa Excelin kaikkien toimintojen luetteloon