LIN.ILL függvény

Ez a témakör a Microsoft Excel LIN.ILL függvényének képletszintaxisát és használatát mutatja be. A Lásd még szakasz további információkra mutató hivatkozásokat tartalmaz a diagramkészítéssel és a regresszióelemzéssel kapcsolatban.

Leírás

A LIN.ILL függvény a legkisebb négyzetek módszerével kiszámolja a megadott adatokhoz legjobban illeszkedő egyenes egyenletét, és eredményként az egyenest leíró tömböt adja vissza. A LIN.ILL más függvényekkel együtt való használatával kiszámíthatja lineáris ismeretlen paraméterekkel rendelkező, más típusú (például logaritmikus, polinomiális, exponenciális és hatványsor-) modellek statisztikáit is. Mivel ez a függvény tömböt ad eredményül, tömbképletként kell bevinni. A cikkben a példákat útmutató követi.

Az egyenes egyenlete a következő:

y = mx + b

– vagy –

y = m1x1 + m2x2 + ... + b

ha több x értéktartomány is meg van adva, ahol az y értékek a független x értékek függvényei. Az m értékek az egyes x értékek együtthatói, míg a b állandó érték. Az y, az x és az m érték vektor is lehet. A LIN.ILL függvény az {mn;mn-1;...;m1;b} tömböt adja eredményül. A LIN.ILL függvény egyéb regressziós statisztikai adatokat is vissza tud adni.

Szintaxis

LIN.ILL(ismert_y; [ismert_x]; [konstans]; [stat])

A LIN.ILL függvény szintaxisa az alábbi argumentumokat foglalja magában:

Szintaxis

  • ismert_y:    Megadása kötelező. Az y = mx + b összefüggésből már ismert y értékek.

    • Ha az ismert_y értékek tartománya egyetlen oszlop, akkor az ismert_x értékek minden egyes oszlopát különböző változóként értelmezi a függvény.

    • Ha az ismert_y értékek tartománya egyetlen sor, akkor az ismert_x értékek minden egyes sorát különböző változóként értelmezi a függvény.

  • ismert_x:    Megadása nem kötelező. Az y = mx + b összefüggésből már ismert x értékek.

    • Az ismert_x értékek tartománya egy vagy több különböző változó értékeit tartalmazhatja. Ha csak egy változót használ, akkor az ismert_y és az ismert_x tetszőleges alakú, egyenlő dimenziójú tartomány lehet. Ha egynél több változót használ, akkor az ismert_y tartománynak vektornak kell lennie (amely egyetlen sor magasságú vagy egyetlen oszlop szélességű tartomány).

    • Ha az ismert_x argumentumot nem adja meg, akkor a függvény az {1. 2. 3. ...} tömböt használja, amely az ismert_x tömbbel azonos méretű.

  • konstans:    Megadása nem kötelező. Logikai érték, amely azt határozza meg, hogy a b értéke mindenképpen 0 legyen-e.

    • Ha a konstans értéke IGAZ vagy hiányzik, akkor a függvény a b értéket korlátozás nélkül számolja ki.

    • Ha a konstans értéke HAMIS, akkor a b értéke 0 lesz, az m értékeket pedig az y = mx egyenlet alapján számolja ki a függvény.

  • stat:    Megadása nem kötelező. Logikai érték, amely azt határozza meg, hogy a függvény kiegészítő regressziós statisztikai adatokat is számoljon-e.

    • Ha a stat argumentum értéke IGAZ, a LIN.ILL kiegészítő statisztikai adatokat is visszaad. Az eredménytömb ekkor a következő: {mn . mn-1. ... . m1 . b; sen . sen-1. ... . se1 . seb ; r2 . sey . F . df; ssreg . ssresid}.

    • Ha a stat argumentum értéke HAMIS vagy hiányzik, akkor a LIN.ILL csak az m együtthatókat és a b állandót adja eredményül.

      A kiegészítő regressziós adatok a következők:

Adat

Leírás

se1, se2, ..., sen

Az m1, m2, ..., mn együtthatók standard hibáinak értékei.

seb

A b állandó standard hibájának értéke (seb = #HIÁNYZIK, ha a konstans értéke HAMIS).

r2

A meghatározás együtthatója. Összehasonlítja a becsült és a tényleges y értékeket, és a tartomány értéke 0 – 1. Ha 1, a minta tökéletes korrelációt mutat – a becsült y és a tényleges y érték között nincs különbség. A másik véglet, ha a meghatározás együtthatója 0, a regressziós egyenlet nem hasznos az y érték előrejelzésében. Az R2 számítási módjáról további információt a témakör későbbi, "Megjegyzések" című részében talál.

sey

Az y becsléséhez tartozó standard hiba.

F

Az F-próba eredményeként kapott érték. Az F-próba segítségével megállapítható, hogy a független és a függő változók között megfigyelt kapcsolat véletlenszerű-e.

df

A szabadság foka. A szabadság foka segítségével megkeresheti a statisztikai táblázat F-kritikus értékeit. Összehasonlíthatja a táblázatban található értékeket a Lin által visszaadott F-statisztika segítségével, hogy megállapíthassa a modell megbízhatósági szintjét. A DF számítási módjáról a jelen témakör "Megjegyzések" című szakaszában olvashat. Példa 4 az F és a DF függvény használatát mutatja be.

ssreg

A regressziós négyzetösszeg.

ssresid

A maradék négyzetösszeg. Az ssreg és ssresid kiszámításának módját a „Megjegyzések” szakasz ismerteti.

A következő táblázat azt mutatja be, hogy a függvény milyen sorrendben adja meg a kiegészítő regressziós statisztikai adatokat.

Munkalap

Megjegyzések

  • Minden egyenes egyenlete megadható meredekségének és az y tengellyel való metszéspontjának segítségével:

    Meredekség (m):
    Egy egyenes meredekségének (m) meghatározásához vegye az egyenes két pontját, ezek legyenek (x1,y1) és (x2,y2). Az egyenes meredeksége ekkor (y2 - y1)/(x2 - x1).

    Y-metszéspont (b):
    Az y-metszéspont (b) az az y érték, amelynél az egyenes az y tengelyt metszi.

    Az egyenes egyenlete y = mx + b. Ha ismeri az m és a b értéket, akkor az egyenes tetszőleges pontjának koordinátái kiszámíthatók az ismert x vagy y érték behelyettesítésével. Emellett használhatja a TREND függvényt is.

  • Ha csak egyetlen független x-változóval dolgozik, akkor a meredekséget és az egyenes y tengellyel való metszéspontját a következő függvények felhasználásával kaphatja meg közvetlenül:

    Meredekség:
    =INDEX(LIN.ILL(ismert_y;ismert_x);1)

    Y-metszéspont:
    =INDEX(LIN.ILL(ismert_y;ismert_x);2)

  • A LIN.ILL függvénnyel kiszámolt egyenes pontossága függ a felhasznált adatok szórásának nagyságától. A függő és a független változók kapcsolata minél inkább közelít a lineárishoz, annál pontosabb a LIN.ILL modell. A LIN.ILL a legkisebb négyzetek módszerét használja az adatokhoz legjobban illeszkedő egyenes meghatározására. Ha csak egyetlen független x változóval dolgozik, akkor az m és a b érték kiszámítása a következő egyenletek segítségével történik:

    Egyenlet

    Egyenlet

    ahol x és y az adatok középértékei, tehát x = ÁTLAG(ismert_ x) és y = ÁTLAG(ismert_y).

  • A vonal-és a görbe igazítási függvényei a Lin és a ill kiszámítják az adatokhoz illeszkedő legjobb egyenes vonalat vagy exponenciális görbét. Úgy kell azonban eldöntenie, hogy a két eredmény közül melyik illik legjobban az adatforráshoz. Az exponenciális görbe trendje (ismert_y, ismert_x) és a növekedés (ismert_y, ismert_x) kiszámítására használható. Ezek a függvények a new_x's argumentum nélkül a tényleges adatpontokban az adott vonal vagy görbe mentén előre jelzett y értékek tömbjét adják vissza. Ezt követően összehasonlíthatja a jelzett értékeket a tényleges értékekkel. Lehet, hogy a vizuális összehasonlításhoz egyaránt érdemes diagramot bemutatnia.

  • A regresszióanalízis során a Microsoft Excel kiszámítja az egyes becsült és tényleges y értékek eltéréseinek négyzetét. Ezeknek az eltérésnégyzeteknek az összege a maradék négyzetösszeg, ssresid. Az Excel ezután kiszámítja a négyzetek teljes összegét, az sstotal értéket. Ha a konstans argumentum értéke IGAZ vagy nincs megadva, akkor a teljes négyzetösszeg egyenlő az y értékek átlagának és a tényleges y értékek eltéréseinek négyzetösszegével. Ha a konstans argumentum értéke HAMIS, akkor a teljes négyzetösszeg a tényleges y értékek négyzetösszege (az y értékek átlagának az egyes y értékekből történő kivonása nélkül). A regressziós négyzetösszeg – ssreg – a következőképpen számítható ki: ssreg = sstotal - ssresid. Minél kisebb a maradék négyzetösszeg a teljes négyzetösszeghez képest, annál nagyobb a determinációs együttható (r2) értéke, amely azt mutatja meg, hogy a regresszióanalízis eredményeként kapott egyenlet mennyire pontosan írja le a változók közötti kapcsolatot. Az r2 értéke = ssreg / sstotal.

  • Bizonyos esetekben az X oszlopok közül egy vagy több (feltételezve, hogy az Y és az X oszlop oszlopokban van) nem lehet további előrejelzési értéket bevezetni a többi X oszlop jelenlétében. Más szóval az egy vagy több X oszlop kiiktatása az előre jelzett Y értékekhez hasonlóan pontos lehet. Abban az esetben, ha ezeket a redundáns X oszlopokat el kell hagyni a regressziós modellből. Ezt a jelenséget "kollinearitás" nevezzük, mert bármely redundáns X oszlop a nem redundáns X oszlopok többszörösének összegeként fejezhető ki. A Lin függvény ellenőrzi a kollinearitás, és eltávolítja a regressziós modellből az esetleges redundáns X oszlopokat az azonosításuk után. Az eltávolított X oszlopok a Lin kimenetében 0 együtthatóval, a 0 se értéken kívül is felismerhetők. Ha egy vagy több oszlopot redundánsan távolít el, a DF hatással van, mivel a DF a prediktív használatra ténylegesen használt X-oszlopok számától függ. A DF számításáról a példa 4című témakörben olvashat részletesen. Ha a DF változik, mert a redundáns X-hasábok törlődnek, akkor az értékeit is érinti. A kollinearitás viszonylag ritkán kell lennie a gyakorlatban. Egy esetben azonban, ha nagyobb valószínűséggel fordul elő, ha egyes X-oszlopok csak 0 és 1 értéket tartalmaznak, jelzi, hogy egy kísérlet tárgya vagy nem tagja-e egy adott csoportnak. Ha konstans = igaz vagy nincs megadva, akkor a Lin függvény minden 1 érték további X oszlopát szúrja be az elfogás modellezéséhez. Ha az összes tárgyhoz 1 oszlop tartozik, ha a hím vagy a 0 Ha nem, és ha nem, akkor az összes tárgyhoz 1 oszlop tartozik, ha a nőstény vagy a 0 Ha nem, akkor ez az utóbbi oszlop redundáns, mert a beérkező tételek a "férfi" jelzéssel kivonhatók. az Lin függvény által hozzáadott összes 1 érték további oszlopában lévő bejegyzésből származó oszlop.

  • Ha kollinearitás miatt nem kellett eltávolítani egyetlen X oszlopot sem, akkor a df értékét a következőképpen lehet kiszámolni: ha k darab ismert_x oszlop van és a konstans = IGAZ vagy hiányzik: df = n – k – 1. Ha a konstans = HAMIS: df = n - k. A kollinearitás miatt eltávolított minden egyes oszlop mindkét esetben 1-gyel növeli a df értékét.

  • A tömböket eredményül adó képleteket a megfelelő számú cella kijelölése után tömbképletként kell bevinni.

    Megjegyzés: A Webes Excel ban nem hozhatók létre tömbképletek.

  • Ha argumentumként tömböt ad meg (ilyen lehet például az ismert_x értékek tömbje), akkor az egy sorba tartozó értékeket ponttal, az egyes sorokat pontosvesszővel válassza el egymástól. A listaelválasztó karakterek a területi beállításoktól függenek.

  • Ne feledje, hogy a regressziós egyenlet által előre jelzett y értékek nem alkalmazhatók, ha kívül esnek az egyenlet meghatározására megadott y értékek tartományán.

  • A LIN.ILL függvény mögöttes algoritmusa eltér a MEREDEKSÉG és a METSZ függvényétől. Az algoritmusok különbözősége eltérő eredményekhez vezethet, ha az adatok határozatlanok és kollineárisak. Ha például az ismert_y adatpontok 0 értékűek, illetve az ismert_x adatpontjai 1 értékűek:

    • A LIN.ILL függvény értéke 0. A LIN.ILL algoritmus úgy van kialakítva, hogy kollineáris adatok esetén ésszerű eredményeket adjon, és ebben az esetben legalább egy válasz létezik.

    • A MEREDEKSÉG és a METSZ függvény értéke #ZÉRÓOSZTÓ! hiba. A MEREDEKSÉG és a METSZ algoritmus úgy van kialakítva, hogy kizárólag egy választ keressen, és ebben az esetben egynél több válasz lehetséges.

  • Azonfelül, hogy a LOG.ILL függvény segítségével statisztikai számításokat végezhet más típusú regressziók esetében, a LIN.ILL segítségével számításokat végezhet sok más regressziótípus esetében, ha az x és y változók függvényét x és y sorozatok formájában megadja a LIN.ILL függvénynek. Például a következő képlet:

    =LIN.ILL(yértékek; xértékek^OSZLOP($A:$C))

    akkor használható, ha az y és x értékek egy-egy oszlopban találhatók, és a következő egyenlet köbös (harmadrendű polinomiális) közelítését szeretné kiszámítani:

    y = m1*x + m2*x^2 + m3*x^3 + b

    E képlet módosított változataival kiszámíthat más típusú regressziót is, egyes esetekben azonban módosítani kell a kimeneti értékeket és más statisztikákat.

  • A LIN.ILL függvény által visszaadott F-próba érték eltér az F.PRÓBA függvény által adott F-próba értékétől. A LIN.ILL függvény a statisztikai F értékét adja meg, míg az F.PRÓBA függvény a valószínűséget.

Példák

1. példa: A meredekség és az Y-metszéspont meghatározása

Másolja a mintaadatokat az alábbi táblázatból, és illessze be őket egy új Excel-munkalap A1 cellájába. Ha azt szeretné, hogy a képletek megjelenítsék az eredményt, jelölje ki őket, és nyomja le az F2, majd az Enter billentyűt. Szükség esetén módosíthatja az oszlopok szélességét, hogy az összes adat látható legyen.

Ismert y

Ismert x

1

0

9

4

5

2

7

3

Eredmény (meredekség)

Eredmény (y-metszéspont)

2

1

Képlet (tömbképlet az A7:B7 cellatartományban)

=LIN.ILL(A2:A5;B2:B5;;HAMIS)

2. példa: Egyszerű lineáris regresszió

Másolja a mintaadatokat az alábbi táblázatból, és illessze be őket egy új Excel-munkalap A1 cellájába. Ha azt szeretné, hogy a képletek megjelenítsék az eredményt, jelölje ki őket, és nyomja le az F2, majd az Enter billentyűt. Szükség esetén módosíthatja az oszlopok szélességét, hogy az összes adat látható legyen.

Hónap

Értékesítés

1

3100 USD

2

4500 USD

3

4400 USD

4

5400 USD

5

7500 USD

6

8100 USD

Képlet

Eredmény

=SZUM(LIN.ILL(B1:B6;A1:A6)*{9;1})

1 100 000 Ft

A kilencedik hónap értékesítéseinek becslését számítja ki a 1–6. hónap értékesítési alapján.

3. példa: Többszörös lineáris regresszió

Másolja a mintaadatokat az alábbi táblázatból, és illessze be őket egy új Excel-munkalap A1 cellájába. Ha azt szeretné, hogy a képletek megjelenítsék az eredményt, jelölje ki őket, és nyomja le az F2, majd az Enter billentyűt. Szükség esetén módosíthatja az oszlopok szélességét, hogy az összes adat látható legyen.

Hasznos alapterület (x1)

Irodák száma (x2)

Bejáratok száma (x3)

Az épület kora (x4)

Az irodaépület becsült értéke (y)

2310

2

2

20

14 200 000 Ft

2333

2

2

12

14 400 000 Ft

2356

3

1,5

33

15 100 000 Ft

2379

3

2

43

15 000 000 Ft

2402

2

3

53

13 900 000 Ft

2425

4

2

23

16 900 000 Ft

2448

2

1,5

99

12 600 000 Ft

2471

2

2

34

14 290 000 Ft

2494

3

3

23

16 300 000 Ft

2517

4

4

55

16 900 000 Ft

2540

2

3

22

14 900 000 Ft

-234,2371645

13,26801148

0,996747993

459,7536742

1732393319

Képlet (az A14:A18 cellákban megadott tömbképlet)

=LIN.ILL(E2:E12;A2:D12;IGAZ;IGAZ)

4. példa: Az F- és az r2-próba

Az előző példában a determinációs együttható (r2) értéke 0,99675 volt (az A17 cella a LIN.ILL eredményében), ami erős kapcsolatot jelez a független változók és az irodaépület eladási ára között. Az F-próba segítségével megállapíthatja, hogy ezek az eredmények, például a magas r2 érték, véletlenszerűek-e.

Tegyük fel, hogy nincs tényleges kapcsolat a változók között, és hogy csak véletlenül választotta ki pont azt a 11 irodaházat mintaként, amelyek a statisztikai elemzéskor szoros kapcsolatot mutattak. Alfa értéke adja meg, hogy mi a valószínűsége annak, hogy következtetése hibás volt, és az eredmények alapján feltételezett kapcsolat nem létezik.

Az F és a DF értékét a kimenetből a Lin függvény segítségével értékelheti annak valószínűségét, hogy nagyobb az f érték a véletlenben. Az f függvényt összehasonlíthatja a közzétett F-eloszlású táblázatok kritikus értékével, vagy az Excel eloszlás függvényével kiszámíthatja a valószínűség szerint megjelenő nagyobb F érték valószínűségét. A megfelelő F-eloszlás a v1 és a v2 fok közötti szabadságot tartalmazza. Ha n az adatpontok száma és a konstans = igaz vagy hiányzik, akkor v1 = n – DF – 1 és v2 = DF. (Ha CONST = FALSE, majd v1 = n-DF és v2 = DF.) A eloszlás függvény – a szintaxis eloszlás(F, v1, v2) – azzal a valószínűséggel ad eredményül, hogy nagyobb F érték következik be a véletlenben. Ebben a példában a DF = 6 (Cell B18) és az F = 459,753674 (cella A18 cellában).

Az 0,05, a v1 = 11 – 6 – 1 = 4 és a v2 = 6 alfa értékét feltételezve a 4,53 kritikus szintje. Mivel az F = 459,753674 jóval magasabb, mint az 4,53, nagyon valószínűtlen, hogy az F érték nagy valószínűséggel jelentkezett. (Az alfa = 0,05 azt a feltételezést, hogy nincs kapcsolat az ismert_y és az ismert_x között, ha az F túllépi a kritikus szintet, 4,53.) Az Excel eloszlás függvényével megkeresheti annak valószínűségét, hogy az F érték nagy valószínűséggel bekövetkezett. Például eloszlás(459,753674; 4; 6) = 1.37 e-7, rendkívül kis valószínűséggel. Azt is megteheti, hogy megtalálta az F érték kritikus szintjét a táblázatban vagy a eloszlás függvény segítségével, hogy a regressziós egyenlet hasznosan használható az Office-épületek becsült értékének előrejelzéséhez ezen a területen. Ügyeljen arra, hogy kritikusan használja az előző bekezdésben kiszámított v1 és v2 képletek helyes értékét.

5. példa: A t-próba

Egy másik hipotézis teszt azt fogja meghatározni, hogy az egyes meredekség együtthatók hasznosak-e a 3 példakéntszolgáló irodaház becsült értékének becslése során. Ha például a statisztikai pontosságot szolgáló életkori együtthatót szeretné tesztelni, akkor a 234,24 (életkor meredeksége együttható) 13,268 (az életkori együtthatók becsült normál hibája a A15). A következő a t-megfigyelt érték:

t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7

Ha a t abszolút értéke elég magas, úgy lehet megállapítani, hogy a meredekség együttható hasznos az Office-épület kiértékelt értékének becslése során, a 3. Az alábbi táblázat a 4 t-megfigyelt érték abszolút értékét jeleníti meg.

Ha egy statisztikai kézikönyvben keres egy táblázatot, a t-kritikus, a két farkú, a 6 fokos szabadság és az alfa = 0,05 a 2,447. Ez a kritikus érték az Excel inverz függvényének használatával is megtalálhatók. Inverz (0,05, 6) = 2,447. Mivel a t (17,7) abszolút értéke nagyobb, mint 2,447, az életkor fontos változó az Office-épület kiértékelt értékének becslése során. A többi független változót hasonló módon is tesztelheti statisztikai szempontból. Az alábbiakban a minden független változó t-megfigyelt értékei láthatók.

Változó

Mintából számított t érték

hasznos alapterület

5,1

irodák száma

31,3

bejáratok száma

4,8

az épület kora

17,7

Mindegyik szám abszolút értéke nagyobb 2,447-nél, vagyis a regressziós egyenletben használt változók mindegyike fontos az ebben az övezetben lévő épületek értékének becsléséhez.

Megjegyzés:  Ez az oldal gépi fordítással lett lefordítva, ezért nyelvtani hibákat és pontatlanságokat tartalmazhat. A célunk az, hogy ezek a tartalmak felhasználóink hasznára váljanak. Hasznos volt ez az információ az Ön számára? Itt találja az eredeti angol nyelvű cikket.

Office-jártasság bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×