LINEST Function Excel - Lineáris regressziós statisztika

Példa munkafüzet letöltése

Töltse le a példa munkafüzetet

Ez az oktatóanyag bemutatja, hogyan kell használni Excel LINEST függvény Excelben a trendvonal statisztikáinak kiszámításához.

A LINEST funkció áttekintése

A LINEST függvény a legkisebb négyzetek módszerével kiszámítja az ismert adatpontokra illesztett trendvonal statisztikáit.

A LINEST Excel munkalap funkció használatához válasszon ki egy cellát, és írja be:

(Figyelje meg, hogyan jelennek meg a képletbemenetek)

LINEST függvény Szintaxis és bemenetek

1	= LINEST (ismert_ys, ismert_xs, const, statisztika)

ismert_y - Az ismert Y értékek tömbje.

ismert_x -ek - Az ismert X értékek tömbje.

const - VÁLASZTHATÓ. Logikai érték, amely azt jelzi, hogy a B -t (y = mx + b metszéspont) kell kiszámítani a legkisebb négyzetek módszerével (IGAZ vagy elhagyott), vagy kézzel kell beállítani a B = 0 -t (HAMIS).

statisztika - VÁLASZTHATÓ. További statisztikák (IGAZ) vagy csak m (lejtés) és b (elfogás) (HAMIS vagy kihagyott)

Mi a LINEST?

Az Excel LINEST függvénye regressziós statisztikák generálására szolgál egy lineáris regressziós modellhez. A LINEST tömbképlet, és önmagában vagy más funkciókkal együtt használható a modellre vonatkozó statisztikák kiszámításához.

A lineáris regresszió olyan módszer a statisztikákban, amelyet ismert adatok felhasználásával egyenes vonal után előre jeleznek. A regressziót olyan értékek előrejelzésére használják, mint az értékesítés növekedése, a készletigény vagy az egyszerű piaci trendek.

A LINEST olyan, mint az ELŐREJELZÉS abban az értelemben, hogy hasonló eredményt ér el, de sokkal több információval rendelkezik a regressziós modelljéről, valamint több független változó illesztésének lehetőségéről.

Tegyük fel, hogy van egy táblázatom az adatokkal x és y értékek hol x a független változó és y függő változó:

Azt szeretném tudni, hogy mi a fenti adatok regressziós egyenlete. A LINEST használata:

1	= LEGJOBB (B3: B7, C3: C7, IGAZ, HAMIS)

Az y-metszés értéke itt 0-val egyenértékű, tudományos jelölésben.

Az egyenlet egyenlete y = 2x + 0. Vegye figyelembe, hogy a LINEST visszatér mindkét a vonal meredeksége és metszése. Mindkét érték visszaadásához a képletet tömbképletként kell megadni. A tömbképletekről később.

Hogyan kell használni a LINEST -et?

A LINEST függvény négy érvet tartalmaz:

1	= LINEST (ismert_y -k, ismert_x -ek, const, statisztika)

Ahol,

Érv	Leírás
ismert_y és ismert_x -ek	Az a x és y adatokat az adattáblájában
const	IGAZ/HAMIS opció arra, hogy az y-metszést 0-ra kell kényszeríteni, vagy normálisan kell kiszámítani
statisztika	IGAZ/HAMIS opció, hogy vissza kell -e adni további regressziós statisztikákat

Az első példánkat használva a függvény így íródik:

1	= LEGJOBB (B3: B7, C3: C7, IGAZ, HAMIS)

Amikor az statisztika opció értéke IGAZ, a regressziós statisztika rendszere a következő:

Lehet, hogy kíváncsi, mit jelentenek az egyes változók.

Statisztikai	Leírás
mn	Lejtési együtthatók a következőhöz: x változó
b	y-elfog
sen	Standard hiba minden lejtési együtthatóhoz
seb	Az y-elfogás standard hibája
r2	Határozási együttható
sey	Standard hiba a y becslés
F	Az F statisztika (annak meghatározására, hogy a változók kapcsolata véletlenül következik -e be)
df	A szabadság fokai
ssreg	A négyzetek regressziós összege
ssmaradék	Négyzetek maradványösszege

A fő statisztika, amelyet meg kell érteni, a meredekségi együttható, az y-metszéspont és a determinációs együttható vagy az r² a modell értéke.

A fenti példa segítségével válassza ki az IGAZ lehetőséget a statisztika paraméter:

A kiemelt cellák a meredekséget = 2, az elfogást = 0 és az r mutatják² = 1.

Az r² érték a modell korrelációjának erősségét jelzi. Az illeszkedés mutatójának tekinthetjük. Egy alacsony r² érték gyenge korrelációt jelentene a függő és független változók között, és az ellenkezője igaz a magas r értékre² értékek, r -vel² = 1 tökéletes illeszkedés.

A Microsoft 2022. januárját követő kiadásokban a Microsoft 365 -ben (korábban Office 365) az Excel 2022. januárjában a dinamikus tömbök megváltoztatták a tömbképletek kiértékelési módját. Már nem szükséges a CTRL + SHIFT + ENTER billentyűkombináció használata, vagy a tömb által elfoglalt cellaterület kiemelése. Egyszerűen írja be a képletet, majd kattintson az Enter gombra, és a kapott sejtek „kiömlnek” a tömbbe.

A cikk további részében a LINEST használatával hivatkozunk a Microsoft 365 Excel dinamikus tömbjeire.

Előrejelzés a LINEST (egyszerű regresszió) segítségével

A LINEST és a SUM függvények kombinálásával megjósolható egy függő változó értéke y, adott ismert x és y adat. Az alábbiakban egy példa mutatja, hogy mi az y értéke akkor lesz, ha x = 14.

1	= ÖSSZEG (VONAL (C3: C7, B3: B7)*{14,1})

A modell y = mx + b formában jelenik meg. Ez ugyanaz, mint y = a+ bx, csak másképpen ábrázoljuk az egyenletet. Egy tipp, amelyet szem előtt kell tartani a lineáris egyenleteknél, a mellette lévő változó x mindig a meredekség, és a plusz vagy mínusz jelet követő változó mindig a metszés, függetlenül az egyenletben használt betűktől.

A képlet használatával: = SUM (LINEST (C3: C7, B3: B7)*{14,1}) a 28 eredményét adja vissza. Mivel ez egyetlen eredmény, nem szükséges tömbként megadni.

A fenti képlet hátsó vége *{14,1} határozza meg a függő változó előrejelzéséhez használandó független változót, ebben az esetben 14.

Ezt úgy ellenőrizhetjük, hogy x = 14 beírjuk a sor egyenletébe, y = 2x + 0.

Előrejelzés LINEST (többszörös lineáris regresszió) segítségével

A következő adattábla a Microsoft támogatási webhely LINEST oldaláról származik.

Bizonyos esetekben egynél több független változót kell figyelembe venni a lineáris regressziós modell létrehozásakor. Ezt többszörös lineáris regressziónak (azaz több független változónak) nevezik. Ha meg akarom becsülni egy irodaház költségét, akkor az egyenlet részét képezik olyan dolgok, mint az alapterület, az épület bejáratainak száma, az épület kora és az irodák száma. Lássunk egy példát.

A LINEST képletet beírva a G29 cellába, és végrehajtva azt kapjuk:

1	= LEGJOBB (E3: E13, A3: D13, IGAZ, IGAZ)

A modell a következő formában jelenik meg:

Ne feledje, hogy a LINEST eredménytömb fordított sorrendben van az egyenlettől. A fenti példában 52 317,8 az elfogásunk, b és 27,6 a m₁ vagy a Floor space változó meredekség értéke, x₁.

A LINEST függvényt a megadott adatokkal használva regressziós modellünk a következő:

Egy r -vel² értéke 0,997, ami erős vagy erősen korreláló modellt jelez. A modell használatával most a fenti független változók bármely kombinációja alapján megjósolhatja, hogy egy irodaház értékelési értéke mi lesz.

LINEST Tippek

1. Győződjön meg arról, hogy a Microsoft 365 legfrissebb verziójával rendelkezik a LINEST dinamikus tömbökkel való használatához. Lehet, hogy engedélyeznie kell az Office Insider Current Channel (Preview) funkciót a dinamikus tömb funkciók használatához. A Fiók oldalon:
   
2. Ha nem Microsoft 365 kiadást használ, akkor a régi CTRL + SHIFT + ENTER (CSE) módszert kell használnia a tömbképletek kiértékeléséhez.
3. Ha örökölt módszert használ, akkor a LINEST tömbfüggvény megadásakor kiemelendő oszlopok száma mindig az x változók az adatokban plusz 1. A tömbhöz kiválasztandó sorok száma 5.
4. Ha az Excel dinamikus tömb-engedélyezett verzióját megosztja valakivel, aki nem Microsoft 365-ös kiadást használ, a kompatibilitási problémák elkerülése érdekében használja a korábbi egyéni keresőmotor módszert.

Érdekel a további előrejelzés?

Tekintse meg további cikkeinket az előrejelzésről exponenciális simítás, TREND, NÖVEKEDÉS és LOGEST funkciókkal.

LINEST funkció a Google Táblázatokban

A LINEST funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben.

LEGJOBB példák a VBA -ban

A LINBA funkciót a VBA -ban is használhatja. Típus:
application.worksheetfunction.linest (ismert_ys, ismert_xs, konst, statisztika)

A következő VBA utasítás végrehajtása

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

a következő eredményeket hozza

A függvény argumentumok (ismert_y stb.) Esetén megadhatja azokat közvetlenül a függvénybe, vagy definiálhat helyettük változókat.

Térjen vissza az Excel összes funkciójának listájához