Ez a cikk a Microsoft Excel LIN.ILL függvényének képletszintaxisát és használatát ismerteti.
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
known_y Kötelező. Az y = mx + b összefüggésből már ismert y értékek.
- Ha a known_y tartománya egyetlen oszlop, akkor a known_x minden egyes oszlopát különálló változóként értelmezi a rendszer.
- Ha a known_y tartománya egyetlen sorban található, a known_x minden egyes sorát különálló változóként értelmezi a rendszer.
known_x Nem kötelező. Az y = mx + b összefüggésből már ismert x értékek.
- A known_x tartománya egy vagy több változócsoportot tartalmazhat. Ha csak egy változót használ, a known_y és a known_x tetszőleges egyenlő dimenziójú tartomány lehet. Ha egynél több változót használ, known_y egy vektornak kell lennie (azaz egy egyetlen sor magasságú vagy egyetlen oszlop szélességű tartománynak).
- Ha a known_x argumentumot nem adja meg, akkor a függvény az {1,2,3,...} tömböt használja, amely mérete megegyezik a known_y tömbjével.
konstans 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, az m értékek pedig az y = mx eligazodására kerülnek.
statisztika 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 értéke IGAZ, akkor a LIN.ILL kiegészítő regressziós statisztikai adatokat is ad vissza; Ennek eredményeképpen a visszaadott tömb {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r2,sey; F,df; ssreg,ssresid}.
- Ha a stat értéke HAMIS vagy hiányzik, akkor a LIN.ILL csak az m együtthatókat és a b állandót adja vissza.
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 = #N/A, ha konstans értéke HAMIS). |
| 2. sz. | A meghatározási együttható. A becsült és a tényleges y értékek összehasonlítása, és értéke 0 és 1 között van. Ha ez 1, a minta tökéletes korrelációt mutat – nincs különbség a becsült y és a tényleges y érték között. A másik véglet, ha a determinációs együttható 0, a regressziós egyenlet nem segít az y értékek becslésében. Az ár2 kiszámításának módjáról a jelen témakör "Megjegyzések" című szakaszában olvashat. |
| 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ágfokok. A szabadságfokok segítségével megtalálhatja a kritikus F-értékeket a statisztikai táblázatokban. A modell konfidenciaszintjének meghatározásához hasonlítsa össze a táblázatban található értékeket a LIN.ILL függvény által visszaadott F statisztikával. 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.
Megjegyzések
Minden egyenes egyenlete megadható meredekségének és az y tengellyel való metszéspontjának segítségével:
Meredekség (m):
A gyakran m-ként írt egyenes meredekségének megtalálásához vegyünk két pontot az egyenesre, (x1,y1) és (x2,y2); A meredekség egyenlő: (Y2 - Y1)/(X2 - X1).
Y-metszéspont (b):
Az y-metszéspont, amelyet gyakran b-ként írnak, az y értéke az a pont, ahol 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(known_y;known_x;1)
Y-metszéspont:
=INDEX(LIN.ILL(known_y;known_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:
ahol x és y a minta középértéke; vagyis x = ÁTLAG(ismert x) és y = ÁTLAG(known_y).A LIN.ILL és a LOG.ILL függvény képes kiszámítani az adatoknak leginkább megfelelő egyenest vagy exponenciális görbét. El kell azonban döntenie, hogy a két eredmény közül melyik felel meg legjobban az adatainak. Egyenes esetében kiszámíthatja a TREND(known_y;known_x), exponenciális görbe esetében pedig a NÖV(known_y, known_x) függvényt . Ezek a függvények a new_x argumentuma nélkül az adott egyenes vagy görbe mentén előre jelzett y értékek tömbjét adják vissza a tényleges adatpontoknál. Ezután összehasonlíthatja az előre jelzett értékeket a valós értékekkel. Érdemes lehet mindkettőt diagrammal ellátni a vizuális összehasonlítás céljából.
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 = IGAZ vagy hiányzik, akkor a négyzetek összege a tényleges y értékek és az y értékek átlaga közötti különbségek négyzetének összege lesz. Ha a konstans argumentum = HAMIS, a négyzetek összege a tényleges y értékek négyzetének összege (az egyes y értékekből nem vonva ki az átlagos y értéket). 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 négyzetek teljes összegéhez képest, annál nagyobb a meghatározási együttható értéke, r2, amely azt jelzi, hogy a regressziós elemzés eredményeként kapott egyenlet mennyire magyarázza meg a változók közötti kapcsolatot. Az r2 értéke egyenlő ssreg/sstotal értékkel.
Bizonyos esetekben előfordulhat, hogy egy vagy több X oszlop (feltételezve, hogy az Y és az X oszlopban van) nem rendelkezik további prediktív értékkel a többi X oszlop jelenlétében. Más szóval, egy vagy több X oszlop kihagyása egyformán pontos előre jelzett Y értékekhez vezethet. Ebben az esetben ezeket a redundáns X oszlopokat ki kell hagyni a regressziós modellből. Ezt a jelenséget kollinearitásnak, mivel bármely redundáns X oszlop kifejezhető a nem redundáns X oszlopok többszöröseinek összegeként. A LIN.ILL függvény ellenőrzi a kollinearitást, és eltávolítja a felesleges X oszlopokat a regressziós modellből, amint azonosítja őket. Az eltávolított X oszlopokat a LIN.ILL kimenetében úgy ismerheti fel, hogy 0 együtthatójuk van a 0 se értékek mellett. Ha egy vagy több oszlopot redundánsként távolít el, akkor a df hatással van, mivel a df függ a prediktív célokra ténylegesen használt x oszlopok számától. A df kiszámításának részleteit lásd a 4. példában. Ha a df értéke azért változik, mert redundáns X oszlopokat távolított el, az a sey és az F értékét is érinti. A kollinearitásnak viszonylag ritkának kell lennie a gyakorlatban. Valószínűbb azonban, amikor néhány X oszlop csak 0 és 1 értékeket tartalmaz annak jelzésére, hogy egy kísérletben részt vevő alany tagja-e egy adott csoportnak. Ha konstans = IGAZ vagy hiányzik, akkor a LIN.ILL függvény beszúr egy további oszlopba az összes 1 értékből a fogáspont modellezéséhez. Ha van egy olyan oszlopa, amelyben alanyonként 1 érték látható férfi esetén, vagy 0, ha nem, és szintén van olyan oszlopa, amelyben alanyonként 1, illetve 0 érték jelenik meg, ha nem, ez utóbbi oszlop redundáns, mivel a benne szereplő bejegyzéseket úgy lehet kinyerni, hogy a "férfi jelző" oszlopban lévő bejegyzést kivonjuk a LIN.ILL függvénnyel hozzáadott összes értéket tartalmazó kiegészítő oszlopban lévő bejegyzésből.
A df értékét a következőképpen számítja ki a rendszer, ha a kollinearitás miatt egyetlen X oszlop sem kerül eltávolításra a modellből: ha k oszlop van known_x és konstans = IGAZ vagy hiányzik, akkor df = n – k – 1. Ha 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.
Ha tömbállandót (például a known_x-t) ad meg argumentumként, akkor az egy sorban lévő értékeket ponttal, az egyes sorokat pedig 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 a known_y argumentumának adatpontjai 0 értékűek, míg a known_x argumentumának 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 A19 cellába írt dinamikus tömbképlet) | ||||
| =LIN.ILL(E2:E12;A2:D12;IGAZ;IGAZ) |
4. példa: Az F- és az r2-statisztika használata
Az előző példában a kiszámítási tényező (r2) 0,99675 (lásd a LIN.ILL kimenetének A17 celláját), ami szoros kapcsolatot jelez a független változók és az eladási ár 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 függvény kimenetében szereplő F és df értékek felhasználhatók annak valószínűségére, hogy véletlenszerűen magasabb F érték fordulhat elő. Az F értéke összehasonlítható a közzétett F-eloszlás táblázatok kritikus értékeivel, vagy az Excel F.ELOSZLÁS függvényével kiszámítható egy nagyobb F-érték véletlenszerű előfordulásának valószínűsége. A megfelelő F-eloszlás v1 és v2 szabadságfokkal rendelkezik. Ha n az adatpontok száma és konstans = IGAZ vagy hiányzik, akkor v1 = n – df – 1 és v2 = df. (Ha konstans = HAMIS, akkor v1 = n – df és v2 = nf.) Az F.ELOSZLÁS függvény – az F.ELOSZLÁS(F.ELOSZLÁS(F;V1;V2) szintaxissal – egy nagyobb F érték véletlenszerű előfordulásának valószínűségét adja eredményül. Ebben a példában szf = 6 (B18 cella) vagy F = 459,753674 (A18 cella).
Ha az alfa érték 0,05, v1 = 11 – 6 – 1 = 4 és v2 = 6, akkor F kritikus értéke 4,53. Mivel F = 459,753674 jóval magasabb, mint 4,53, rendkívül valószínűtlen, hogy egy ilyen magas F-érték véletlenül fordult elő. (Ha az alfa = 0,05, akkor azt a hipotézist, miszerint nincs kapcsolat known_y és known_x között, el kell utasítani, ha F túllépi a kritikus szintet (4,53) Az Excel F.ELOSZLÁS függvényével meghatározhatja annak valószínűségét, hogy egy ilyen magas F érték véletlenül jött létre. Például F.ELOSZLÁS(459,753674; 4; 6) = 1,37E-7, ami rendkívül kis valószínűség. Akár egy táblázatban, akár az F.ELOSZLÁS függvény használatával megállapítható, hogy a regressziós egyenlet hasznos lehet a terület irodaházainak becsült értékének becslésére. Ne feledje, hogy rendkívül fontos, hogy a v1 és a v2 helyes értékét használja, amelyeket az előző bekezdésben számított.
5. példa: A t-próba
Egy másik hipotézisteszt azt határozza meg, hogy az egyes meredekségi együtthatók hasznosak-e a 3. példában szereplő irodaépület becsült értékének becsléséhez. A koregyüttható statisztikai szignifikanciensének vizsgálatához például ossza el a -234,24-et (kormeredekség együtthatója) 13,268-cal (az A15 cellában lévő koregyütthatók becsült standard hibájával). A következő a vizsgált t érték:
t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7
Ha t abszolút értéke kellően magas, akkor arra a következtetésre juthatunk, hogy a meredekségi együttható hasznos egy irodaépület becsült értékének becsléséhez a 3. példában. Az alábbi táblázat a 4 t-vel vizsgált érték abszolút értékeit tartalmazza.
Ha áttekint egy táblázatot egy statisztikai kézikönyvben, azt találja, hogy t-kritikus, kétszélű, 6 szabadságfokkal és alfa = 0,05 értékkel 2,447. Ez a kritikus érték az Excel INVERZ.T függvényével is megkereshető. INVERZ.T(0,05;6) = 2,447. Mivel t abszolút értéke (17,7) nagyobb 2,447-nél, az életkor fontos változó egy irodaépület becsült értékének becslésekor. A többi független változó mindegyikének statisztikai szignifikanciája hasonló módon tesztelhető. Az alábbiakban az egyes független változók esetében a t által megfigyelt értékek 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.