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 determinációs együttható. A becsült és a tényleges y értéket hasonlítja össze. Értéke 0 és 1 közötti lehet. Ha értéke 1, akkor a minta elemei között teljes korrelációs kapcsolat van, vagyis a becsült és a tényleges érték megegyezik. Ha a determinációs együttható értéke 0, akkor a regressziós egyenlet nem alkalmas az y értékének előrejelzésére. Az r2 kiszámításának módját a „Megjegyzések” szakasz ismerteti.

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ágfok. A szabadságfokok száma a kritikus F értékek statisztikai táblázatokból való kikereséséhez nyújt segítséget. A LIN.ILL által adott és a táblázatban szereplő értékek összehasonlításával megállapíthatja a modell konfidenciaszintjét. A df kiszámításának módját a „Megjegyzések” szakasz ismerteti. A 4. példa az F és a df 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).

  • Az egyenest illesztő LIN.ILL és a görbét illesztő LOG.ILL függvény segítségével meghatározhatja az adatokhoz legjobban illeszkedő egyenest vagy exponenciális görbét. Végül azonban Önnek kell eldöntenie, hogy melyik eredmény illeszkedik jobban az adatokhoz. Ehhez a TREND(ismert_y; ismert_x) és a NÖV(ismert_y; ismert_x) függvény nyújthat segítséget. Ezek a függvények, ha az új_x argumentumnak nem ad értéket, eredményként egy tömböt adnak, amely az adatokból meghatározott egyenes vagy görbe által a valódi értékek helyén felvett értékeket tartalmazza. Az eredményként kapott értékeket ezután összehasonlíthatja a tényleges értékekkel. Az összehasonlításhoz diagramon is ábrázolhatja a két adatsort.

  • 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.

  • Néhány esetben előfordulhat, hogy egy vagy több X oszlopnak (tegyük fel, hogy az x és y értékek oszlopokban vannak) nincs további becsült értéke a többi X oszloppal együtt. Más szóval egy vagy több X oszlop elhagyása azonos becsült Y értékeket eredményezhet, amelyek mindegyike helyes. Ebben az esetben a redundáns X oszlopokat ki kellene hagyni a regressziós modellből. Ezt a jelenséget kollinearitásnak hívják, mert bármely redundáns X oszlop előállítható a nem redundáns X oszlopok szorzatának összegeként. A LIN.ILL ellenőrzi a kollinearitást, és ha redundáns X oszlopokat talál, eltávolítja azokat a regressziós modellből. Az eltávolított X oszlopok a LIN.ILL függvény eredményében úgy ismerhetők fel, hogy 0 a koefficiens és az se értékük is. A redundáns oszlopok eltávolítása hatással van a df értékre, mivel a df függ a becslés során ténylegesen használt X oszlopok számától. A df értékének számítását a 4. példában tanulmányozhatja. Ha a df értéke a redundáns X oszlopok eltávolítása miatt megváltozik, akkor a sey és az F érték is módosul. A kollinearitás viszonylag ritka a gyakorlatban. Előfordulhat azonban például olyankor, amikor néhány X oszlop csak 0-s és 1-es értékeket tartalmaz, mely azt jelzi, hogy a kísérlet egy tárgya része-e egy adott csoportnak vagy sem. Ha a konstans = IGAZ vagy hiányzik, a LIN.ILL a metszet modellezése érdekében beilleszt egy csupa 1-esből álló X oszlopot. Ha egy oszlopban 1-es érték van férfi, 0 pedig nő esetén, és van egy másik oszlop, melyben 1-es érték van nő, 0 pedig férfi esetén, akkor az utóbbi oszlop redundáns. Ennek oka az, hogy az utóbbi oszlop bejegyzéseit megkapja, ha a férfiakat jelző oszlop értékeiből kivonja a LIN.ILL által hozzáadott oszlop csupa 1-es értékeit.

  • 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:  Az Excel Online alkalmazásban 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(N.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.

A LIN.ILL eredményében lévő F és df érték segítségével meghatározható a véletlenül előforduló magasabb F érték valószínűsége. Az F értéket összehasonlíthatja a közzétett F-eloszlás táblázatainak kritikus értékeivel, vagy az Excel F.ELOSZLÁS függvényével kiszámíthatja a véletlenül előforduló magasabb F érték valószínűségét. A megfelelő F-eloszlás v1 és v2 szabadságfokkal rendelkezik. Ha az adatpontok száma n és a konstans = IGAZ vagy hiányzik, akkor v1 = n - df - 1 és v2 = df. (Ha a konstans = HAMIS, akkor v1 = n - df és v2 = df.) Az Excel F.ELOSZLÁS függvénye – F.ELOSZLÁS(F,v1,v2) szintaxissal – a véletlenül előforduló magasabb F érték valószínűségével tér vissza. A 4. példában df = 6 (B18-as cella) és F = 459,753674 (A18-as cella).

Ha az alfa értéke 0,05, v1 = 11 – 6 – 1 = 4 és v2 = 6, akkor az F kritikus értéke 4,53. Mivel F = 459,753674 sokkal nagyobb mint 4,53, nagyon kicsi a valószínűsége annak, hogy ennél nagyobb F érték véletlenül előfordulhat. (Az alfa = 0,05 értékkel számolva, ha az F értéke meghaladja a kritikus 4,53 szintet, vissza kell vonni azt a feltételezést, hogy az ismert_x és ismert_y értékek között nincs kapcsolat.) Az Excel F.ELOSZLÁS függvénye megadja annak a valószínűségét, hogy ilyen magas F érték véletlenül előfordul. Például F.ELOSZLÁS(459,753674, 4, 6) = 1.37E-7, ami egy rettentően kicsi valószínűség. Megállapítható tehát, hogy akár táblázatból keresi ki az F kritikus szintjét, akár az Excel F.ELOSZLÁS függvényét használja, a regressziós egyenlet hasznos eszköz az övezetben lévő irodaházak megállapított értékének becslésében. Ne feledje, hogy lényeges az előző bekezdésben kiszámolt v1 és v2 helyes értékének használata.

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

A t-próba annak eldöntésére alkalmas, hogy az egyes együtthatók szükségesek-e a 3. példában szereplő irodaépületek értékének becsléséhez. Például az életkor együttható statisztikai szignifikanciájának vizsgálatához ossza el az életkor együtthatóját (-23424) az életkor együtthatójának az A15 cellában szereplő becsült standard hibájával (1327). A megfigyelt t érték a következő:

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

Amennyiben a t abszolút értéke elég magas, megállapítható, hogy a meredekség együtthatója hasznos a 3. példában szereplő irodaépületek értékének becslésében. Az alábbi táblázat 4 mintából számított t érték abszolút értékét mutatja.

A statisztikai kézikönyvekben megtalálhatók a kritikus t értékek is. Kétszélű mintavétel esetében, hat szabadságfok és az Alfa 0,05-ös értéke mellett a kritikus t érték 2,447. Ez a kritikus értéket az Excel INVERZ.T függvényével is megkaphatja. INVERZ.T(0,05;6) = 2,447. Mivel a t abszolút értéke – 17,7 – nagyobb mint 2,447, az irodaépületek kora fontos együttható értékük becslésénél. Minden egyes független változó hasonló módszerrel ellenőrizhető. A következő táblázat a független változók mintából számított t értékeit mutatja:

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.

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.

×