LINEST (funkcia)

V tomto článku sa popisuje syntax vzorca a používanie funkcie LINEST v Microsoft Exceli. Prepojenia na ďalšie informácie o vytváraní grafov a vykonávaní regresnej analýzy nájdete v časti Pozrite tiež.

Popis

Funkcia LINEST vypočítava štatistiky pre určitú čiaru tak, že pomocou metódy najmenších štvorcov vypočítava priebeh priamky, ktorá najlepšie zodpovedá daným údajom, a potom vráti pole popisujúce túto priamku. Funkciu LINEST môžete skombinovať s inými funkciami a vypočítavať tak štatistiky pre iné typy modelov s lineárnymi neznámymi vrátane polynomických, logaritmických, exponenciálnych a mocninových radov. Keďže táto funkcia vráti pole hodnôt, musí byť zadaná ako vzorec poľa. Pokyny k tejto funkcii sa vzťahujú na príklady v tomto článku.

Rovnica pre výpočet tejto priamky je:

y = mx + b

alebo

y = m1x1 + m2x2 + ... + b

ak existujú viaceré rozsahy hodnôt x, kde závislé hodnoty y sú funkciou nezávislých hodnôt x. Hodnoty m sú koeficienty zodpovedajúce jednotlivým hodnotám x a b je konštanta. Uvedomte si, že y, x a m môžu byť vektory. Pole, ktoré je výsledkom funkcie LINEST, má tvar {mn;mn-1;...;m1;b}. Funkciu LINEST možno použiť aj na získanie ďalších regresných štatistík.

Syntax

LINEST(známe_y; [známe_x]; [konštanta]; [štatistika])

Syntax funkcie LINEST obsahuje nasledovné argumenty:

Syntax

  • známe_y    Povinný argument. Je to množina známych hodnôt y pre rovnicu y = mx + b.

    • Ak sa rozsah argumentu známe_y nachádza v jednom stĺpci, potom sa každý stĺpec argumentu známe_x považuje za samostatnú premennú.

    • Ak sa rozsah argumentu známe_y nachádza v jedinom riadku, potom sa každý riadok argumentu známe_x považuje za samostatnú premennú.

  • známe_x    Voliteľný argument. Je to množina známych hodnôt x pre rovnicu y = mx + b.

    • Rozsah pre argument známe_x môže zahŕňať viacero množín premenných. Ak sa použije iba jedna premenná, argumenty známe_yznáme_x môžu byť rozsahy ľubovoľného tvaru, ak majú rovnaké rozmery. Ak sa použijú viaceré premenné, argument známe_y musí byť vektor (t.j. rozsah s výškou jedného riadka alebo šírkou jedného stĺpca).

    • Ak sa argument známe_x vynechá, predpokladá sa, že ide o pole {1;2;3;...} rovnakej veľkosti, akú má aj argument známe_y.

  • konštanta    Voliteľný argument. Je to logická hodnota, ktorá určuje, či sa má konštanta b rovnať hodnote 0.

    • Ak je hodnota argumentu konštanta TRUE alebo nie je zadaná, konštanta b sa vypočítava normálne.

    • Ak je hodnota argumentu konštanta FALSE, konštanta b = 0 a hodnoty m sa upravia tak, aby platilo, že y = mx.

  • štatistika    Voliteľný argument. Je to logická hodnota, ktorá určuje, či má funkcia vrátiť aj ďalšie regresné štatistiky.

    • Ak je hodnota argumentu štatistika TRUE, funkcia LINEST vráti ďalšie regresné štatistiky, takže výsledné pole má tvar {mn;mn-1;...;m1;b|sen;sen-1;...;se1;seb|r2;sey|F;df|ssreg;ssresid}.

    • Ak je hodnota argumentu štatistika FALSE alebo nie je zadaná, funkcia LINEST vráti iba koeficienty m a konštantu b.

      Ďalšie regresné štatistiky sú:

Štatistika

Popis

se1;se2;...;sen

Štandardné chyby pre regresné koeficienty m1;m2;...;mn.

seb

Štandardná chyba konštanty b (seb = #NEDOSTUPNÝ, ak je hodnota argumentu konštanta FALSE).

r2

Determinačný koeficient. Porovnáva odhadované a skutočné hodnoty y a dosahuje hodnoty v rozsahu od 0 do 1. Ak má hodnotu 1, ide o dokonalú koreláciu a medzi odhadovanými a skutočnými hodnotami y nie je žiadny rozdiel. Ak koeficient = 0, znamená to, že regresná rovnica nie je vhodná na predpovedanie hodnôt y. Ďalšie informácie o výpočte r2 získate v ďalšej časti tejto témy, ktorá má názov Poznámky.

sey

Štandardná chyba odhadu y.

o

F-štatistika alebo pozorovaná hodnota F. F-štatistika sa používa na určenie, či možno vzťah medzi závislými a nezávislými premennými považovať za náhodný.

df

Počet stupňov voľnosti. Stupne voľnosti sa používajú na určenie kritických hodnôt F v štatistickej tabuľke. Porovnaním hodnôt z tabuľky s F-štatistikou, ktorú vypočítala funkcia LINEST, môžete určiť hladinu spoľahlivosti modelu. Informácie o výpočte parametra df nájdete v časti Poznámky tejto témy. Použitie argumentov F a df je uvedené v príklade číslo 4.

ssreg

Regresný súčet štvorcov.

ssresid

Reziduálny súčet štvorcov. Informácie o spôsobe výpočtu ssreg a ssresid nájdete nižšie v časti Poznámky.

Nasledujúci príklad uvádza poradie, v ktorom sa vracajú dodatočné regresné štatistiky.

Kľúč regresnej štatistiky

Poznámky

  • Ľubovoľnú priamku môžete jednoznačne určiť pomocou smernice a priesečníka s osou y:

    Smernica (m):
    Smernicu priamky, často uvádzanú ako m, nájdete tak, že vezmete dva body tejto priamky, (x1;y1) a (x2;y2). Smernica sa potom rovná (y2 - y1)/(x2 - x1).

    Priesečník s osou y (b):
    Priesečník priamky s osou y, často uvádzaný ako b, je hodnota y v bode, kde priamka pretína os y.

    Rovnica priamky je y = mx + b. Ak poznáte hodnoty m a b, môžete vypočítať ľubovoľný bod tejto priamky tak, že do rovnice dosadíte hodnotu x alebo y. Môžete však použiť aj funkciu TREND.

  • Ak máte iba jedinú nezávislú premennú x, hodnoty smernice a priesečníka s osou y môžete získať priamo z nasledujúcich vzorcov:

    Smernica:
    =INDEX(LINEST(známe_hodnoty_y;známe_hodnoty_x);1)

    Priesečník s osou y:
    =INDEX(LINEST(známe_hodnoty_y;známe_hodnoty_x);2)

  • Presnosť priamky vypočítanej funkciou LINEST závisí od miery rozptylu údajov. Čím je linearita údajov väčšia, tým je model funkcie LINEST presnejší. Funkcia LINEST používa na určenie najvhodnejšej závislosti pre údaje metódu najmenších štvorcov. Ak máte iba jednu nezávislú premennú x, hodnoty m a b sa budú počítať podľa nasledovných vzorcov:

    Rovnica

    Rovnica

    kde x a y sú priemerné hodnoty vzorky, t. j. x =AVERAGE(známe_x) a y = AVERAGE(známe_y).

  • Funkcie LINESTLOGEST, ktoré sa používajú na regresné prekladanie priamky alebo krivky, vypočítavajú priamku alebo exponenciálnu krivku, ktorá najviac zodpovedá východiskovým údajom. Je však nutné rozhodnúť, ktorý z týchto dvoch výsledkov lepšie zodpovedá vašim údajom. Pre priamku môžete použiť funkciu TREND(známe_y;známe_x) a pre exponenciálnu krivku funkciu GROWTH(známe_y;známe_x). Tieto funkcie bez argumentu nové_x vrátia pole hodnôt y predpovedaných podľa priamky alebo krivky skutočných údajových bodov. Potom môžete porovnávať predpovedané hodnoty so skutočnými hodnotami. Hodnoty môžete zakresliť aj do grafu, aby ste ich mohli vizuálne porovnať.

  • Pri regresnej analýze program Excel vypočíta pre každý bod kvadratickú odchýlku medzi skutočnou a odhadovanou hodnotou y v tomto bode. Súčet týchto kvadratických odchýlok sa označuje ako reziduálny súčet štvorcov ssresid. Program Excel potom vypočíta celkový súčet štvorcov sstotal. Ak argument konštanta = TRUE alebo sa vynechá, celkový súčet štvorcov je súčtom kvadratických odchýlok medzi skutočnými hodnotami y a priemernou hodnotou y. Ak argument konštanta = FALSE, celkový súčet štvorcov je súčtom štvorcov skutočných hodnôt y (bez odčítania priemernej hodnoty y od jednotlivých hodnôt y). Regresný súčet štvorcov ssreg sa vypočíta takto: ssreg = sstotal - ssresid. Čím menší je reziduálny súčet štvorcov v porovnaní s celkovým súčtom štvorcov, tým väčšia je hodnota determinačného koeficientu r2, ktorý je ukazovateľom toho, ako spoľahlivo vyjadruje rovnica získaná regresnou analýzou vzťahy medzi premennými. Koeficient r2 sa rovná ssreg/sstotal.

  • V niektorých prípadoch niektoré stĺpce X (za predpokladu, že hodnoty Y a X sa nachádzajú v stĺpcoch) nemusia mať pri existencii ďalších stĺpcov X už žiadny význam pre zlepšenie odhadu. To znamená, že vylúčenie jedného alebo viacerých stĺpcov X môže viesť k odhadu hodnôt Y, ktoré sa budú zhodovať s predchádzajúcim odhadom. V tom prípade by sa mali tieto redundantné stĺpce X v regresnom modeli vynechať. Tento jav sa nazýva kolinearita, pretože ľubovoľný redundantný stĺpec X sa dá vyjadriť ako súčet násobkov neredundantných stĺpcov X. Funkcia LINEST zisťuje kolinearitu a z regresného modelu odstráni všetky zistené redundantné stĺpce X. Odstránené stĺpce X možno vo výstupe funkcie LINEST identifikovať tak, že majú nulové koeficienty a nulové hodnoty se. Odstránenie jedného alebo viacerých redundantných stĺpcov má vplyv na hodnotu df, pretože df závisí od počtu stĺpcov X, ktoré sa aktuálne používajú pri odhade. Informácie o výpočte df nájdete ďalej v príklade číslo 4. Ak sa hodnota df zmení z dôvodu odstránenia redundantných stĺpcov X, má to vplyv aj na hodnoty sey a F. Kolinearita by sa v praxi mala vyskytovať len zriedkavo. Výskyt kolinearity je však pravdepodobný vtedy, keď stĺpce X obsahujú len hodnoty 0 a 1, ktoré naznačujú, či daný subjekt v experimente je alebo nie je členom určitej skupiny. Ak argument konštanta = TRUE alebo nie je zadaný, funkcia LINEST vloží ďalší stĺpec X obsahujúci len hodnoty 1 na modelovanie hodnoty. Ak máte stĺpec s hodnotou 1 označujúcou subjekt mužského rodu alebo hodnotou 0, ak subjekt nie je mužského rodu, pričom existuje aj stĺpec s hodnotou 1 pre každý subjekt ženského rodu, alebo hodnotou 0, ak nie je ženského rodu, tento druhý stĺpec je redundantný, pretože jeho hodnoty sa dajú získať odčítaním hodnoty v stĺpci označujúcom mužský rod od hodnoty v dodatočnom stĺpci hodnôt 1 pridaných funkciou LINEST.

  • Ak sa z modelu neodstránia žiadne stĺpce X v dôsledku kolinearity, hodnota df sa vypočíta nasledovným spôsobom: Ak existuje počet stĺpcov k s hodnotami známe_x a argument konštanta = TRUE alebo je vynechaný, df = n – k – 1. Ak argument konštanta = FALSE, df = n – k. V oboch prípadoch každý stĺpec X, ktorý bol odstránený z dôvodu kolinearity, zvyšuje hodnotu df o 1.

  • Vzorec, ktorého výsledkom je pole, musí byť zadaný ako vzorec poľa.

    Poznámka :  V aplikácii Excel Online nie je možné vytvoriť vzorce poľa.

  • Keď zadávate ako argument konštantu poľa (ako je napríklad známe_x), hodnoty v tom istom riadku oddeľujte čiarkou a jednotlivé riadky oddeľujte bodkočiarkou. Znaky oddeľovača závisia od miestnych nastavení počítača a môžu sa odlišovať.

  • Poznamenávame, že hodnoty y predpovedané regresnou rovnicou nemusia platiť, ak sú mimo oblasti hodnôt y, z ktorých ste rovnicu vytvárali.

  • Algoritmus použitý vo funkcii LINEST je odlišný od algoritmu použitého vo funkciách SLOPEINTERCEPT. V prípade neurčených a kolineárnych údajov môže rozdiel medzi týmito algoritmami viesť k odlišným výsledkom. Ak napríklad údajové body argumentu známe_y sú 0 a údajové body argumentu známe_x sú 1:

    • Funkcia LINEST vráti hodnotu 0. Algoritmus funkcie LINEST je navrhnutý tak, aby vrátil primerané výsledky pre kolineárne údaje, a v tomto prípade je možné získať najmenej jednu odpoveď.

    • Funkcie SLOPEINTERCEPT vrátia chybu #DELENIE NULOU! Algoritmy funkcií SLOPEINTERCEPT sú navrhnuté tak, aby vrátili len jednu odpoveď, a v tomto prípade môže existovať viacero odpovedí.

  • Okrem funkcie LOGEST môžete na výpočet štatistiky iných regresných typov použiť aj funkciu LINEST, a to tak, že zadáte funkcie premenných x a y ako rady x a y funkcie LINEST. Napríklad pomocou vzorca:

    =LINEST(hodnotyy; hodnotyx^STĹPEC($A:$C))

    v prípade, že máte jeden stĺpec hodnôt y a jeden stĺpec hodnôt x, môžete vypočítať kubickú (polynómnu tretieho rádu) aproximáciu vo forme:

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

    Úpravou tohto vzorca môžete vypočítať iné typy regresií, ale v niektorých prípadoch treba upraviť výstupné hodnoty a iné štatistiky.

  • Hodnota F-testu, ktorú vracia funkcia LINEST, sa odlišuje od hodnoty F-testu, ktorú vracia funkcia FTEST. Funkcia LINEST vracia F-štatistiku, zatiaľ čo funkcia FTEST vracia pravdepodobnosť.

Príklady

Príklad 1 – Smernica a priesečník s osou y

Vzorové údaje skopírujte do nasledujúcej tabuľky a prilepte ich do bunky A1 nového excelového hárka. Ak chcete, aby vzorce zobrazovali výsledky, označte ich, stlačte kláves F2 a potom stlačte kláves Enter. V prípade potreby môžete upraviť šírku stĺpcov, aby sa údaje zobrazovali celé.

Známa hodnota y

Známa hodnota x

1

0

9

4

5

2

7

3

Výsledok (smernica)

Výsledok (priesečník s osou y)

2

1

Vzorec (vzorec poľa v bunkách A7:B7)

=LINEST(A2:A5;B2:B5;;FALSE)

Príklad 2 – Jednoduchá lineárna regresia

Vzorové údaje skopírujte do nasledujúcej tabuľky a prilepte ich do bunky A1 nového excelového hárka. Ak chcete, aby vzorce zobrazovali výsledky, označte ich, stlačte kláves F2 a potom stlačte kláves Enter. V prípade potreby môžete upraviť šírku stĺpcov, aby sa údaje zobrazovali celé.

Mesiac

Predaj

1

3 100 €

2

4 500 €

3

4 400 €

4

5 400 €

5

7 500 €

6

8 100 €

Vzorec

Výsledok

=SUM(LINEST(B1:B6; A1:A6)*{9;1})

11 000 EUR

Na základe obratu v 1. až 6. mesiaci vypočíta odhadovaný obrat v 9. mesiaci.

Príklad 3 – Viacnásobná lineárna regresia

Vzorové údaje skopírujte do nasledujúcej tabuľky a prilepte ich do bunky A1 nového excelového hárka. Ak chcete, aby vzorce zobrazovali výsledky, označte ich, stlačte kláves F2 a potom stlačte kláves Enter. V prípade potreby môžete upraviť šírku stĺpcov, aby sa údaje zobrazovali celé.

Podlahová plocha (x1)

Počet kancelárií (x2)

Počet vchodov (x3)

Vek (x4)

Odhadovaná hodnota (y)

2 310

2

2

20

142 000 EUR

2 333

2

2

12

144 000 EUR

2 356

3

1,5

33

151 000 EUR

2 379

3

2

43

150 000 EUR

2 402

2

3

53

139 000 EUR

2 425

4

2

23

169 000 EUR

2 448

2

1,5

99

126 000 EUR

2 471

2

2

34

142 900 EUR

2 494

3

3

23

163 000 EUR

2 517

4

4

55

169 000 EUR

2 540

2

3

22

149 000 EUR

-234,2371645

13,26801148

0,996747993

459,7536742

1 732 393 319

Vzorec (vzorec poľa zadaný v rozsahu A14:A18)

=LINEST(E2:E12,A2:D12,TRUE,TRUE)

Príklad 4 – Použitie štatistík F a r2

V predchádzajúcom príklade sa determinačný koeficient r2 rovnal hodnote 0,99675 (pozrite bunku A17 vo výstupe funkcie LINEST), čo môže naznačovať silnú závislosť medzi nezávislými premennými a predajnou cenou. F-štatistika sa môže použiť na určovanie, či nie sú tieto výsledky s takou vysokou hodnotou r2 náhodné.

Predpokladajme, že medzi premennými v skutočnosti neexistuje žiadna závislosť, ale vybrali ste nezvyčajnú vzorku 11 úradných budov, podľa ktorej štatistická analýza naznačuje silnú závislosť. Termín „Alfa“ sa používa na pravdepodobnosť chybného záveru o existencii závislosti.

Štatistické údaje F a df vo výstupe funkcie LINEST sa môžu použiť na odhad pravdepodobnosti náhodného výskytu vyššej hodnoty F. Hodnotu F je možné porovnať s kritickými hodnotami v publikovaných tabuľkách rozdelenia F, alebo sa na výpočet pravdepodobnosti náhodného výskytu vyššej hodnoty F môže použiť funkcia FDIST programu Excel. Príslušné rozdelenie F má stupne voľnosti v1 a v2. Ak n je počet údajových bodov a argument konštanta = TRUE alebo nie je zadaný, potom v1 = n – df – 1 a v2 = df. (Ak argument konštanta = FALSE, potom v1 = n – df a v2 = df.) Funkcia FDIST so syntaxou FDIST(F;v1;v2)  vráti pravdepodobnosť náhodného výskytu vyššej hodnoty F. V tomto príklade to bude df = 6 (bunka B18) a F = 459,753674 (bunka A18).

Predpokladajme, že hodnota Alfa je 0,05, v1 = 11 – 6 – 1 = 4 a v2 = 6, kritická úroveň hodnoty F je 4,53. Keďže F = 459,753674 je oveľa vyššia hodnota ako 4,53, náhodný výskyt takej vysokej hodnoty F je veľmi nepravdepodobný. (Pri hodnote Alfa = 0,05 a v prípade, že hodnota F prekročí kritickú úroveň 4,53, je možné zamietnuť hypotézu o tom, že medzi hodnotami známe_yznáme_x neexistuje závislosť.) Pomocou funkcie FDIST programu Excel získate pravdepodobnosť náhodného výskytu takej vysokej hodnoty F. FDIST(459,753674; 4; 6) = 1,37E-7, čo je veľmi nízka pravdepodobnosť. Zistenie kritickej úrovne hodnoty F v tabuľke alebo pomocou funkcie FDIST potvrdzuje, že regresná rovnica je vhodná na určenie odhadovanej hodnoty úradných budov v tejto oblasti. Je veľmi dôležité použiť správne hodnoty v1 a v2, ktoré boli vypočítané v predchádzajúcom odseku.

Príklad 5 – Výpočet t-štatistiky

Iný test štatistickej hypotézy určuje, či sa na odhad hodnoty úradných budov z príkladu číslo 3 vhodná ľubovoľná smernica. Ak napríklad chcete testovať štatistickú významnosť smernice pre „Vek“, vydeľte hodnotu -234,24 (smernica pre vek) číslom 13,268 (odhadovaná štandardná chyba pre smernicu veku v bunke A15). Nasledujúca rovnica udáva pozorovanú hodnotu t:

t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

Ak je absolútna hodnota t dostatočne vysoká, potvrdzuje, že táto smernica je vhodná na určenie odhadovanej hodnoty úradnej budovy v príklade číslo 3. Nasledujúca tabuľka obsahuje absolútne hodnoty štyroch pozorovaných hodnôt t.

Ak sa pozriete do tabuľky v nejakej štatistickej príručke, nájdete v nej, že kritická hodnota t pre obojstranný test so 6 stupňami voľnosti a hodnotou alfa = 0,05 je 2,447. Kritická hodnota sa dá zistiť aj pomocou funkcie TINV v programe Excel. Teda TINV(0,05;6) = 2,447. Keďže absolútna hodnota t (17,7) je väčšia než 2,447, vek bude dôležitou premennou pri odhade hodnoty úradnej budovy. Podobným spôsobom môžete testovať štatistickú významnosť pre každú z nezávislých premenných. Nasledujúca tabuľka uvádza pozorované hodnoty t pre každú nezávislú premennú.

Premenná

Pozorovaná hodnota t

Podlahová plocha

5,1

Počet kancelárií

31,3

Počet vchodov

4,8

Vek

17,7

Všetky tieto hodnoty majú absolútnu hodnotu väčšiu než 2,447 a teda všetky premenné, ktoré sa použili v regresnej rovnici, sú významné pre odhad hodnoty úradných budov v tejto oblasti.

Rozšírte svoje zručnosti
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×