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 existuje viacero rozsahov hodnôt x, kde závislé hodnoty y sú funkciou nezávislých hodnôt x. Hodnoty m sú koeficienty zodpovedajúce každej hodnote x a b je konštanta. Všimnite si, že y, x a m môžu byť vektory. Pole, ktoré vráti funkcia LINEST, je {mn;mn-1,...,m1;b}. LineST môže vrátiť aj ďalšie regresné štatistiky.

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 má štatistika hodnotu TRUE, funkcia LINEST vráti ďalšie regresné štatistiky. Výsledkom je vrátené pole {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

Koeficient na určenie. Porovnáva odhadované a skutočné hodnoty y a rozsahy v rozsahu od 0 do 1. Ak je hodnota 1, vo vzorke je dokonalý korelácia – neexistuje žiadny rozdiel medzi odhadovanou hodnotou y a skutočnou hodnotou y. Ak je na druhej extréme koeficient 0, regresná rovnica nie je nápomocná pri predpovedení hodnoty y. Informácie o výpočtehodnoty 2 nájdete v časti Poznámky tejto témy.

sey

Štandardná chyba odhadu y.

o

F-štatistika alebo pozorovaná hodnota F. F-štatistika sa používa na určovanie, či je zistená závislosť medzi závislými a nezávislými premennými pravdepodobnosťou.

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.

Hárok

Poznámky

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

    Smernica (m):
    Smernicu čiary, často písanej ako m, nájdete tak, že vezmete dva body tejto čiary, (x1,y1) a (x2,y2). smernica sa rovná (y2 - y1)/(x2 - x1).

    Prieseesené y (b):
    Priesech riadka s osou y, často napísaný ako b, je hodnota y v bode, kde čiara prekríži os y.

    Rovnica priamky je y = mx + b. Keď poznáte hodnoty m a b, môžete vypočítať ľubovoľný bod v riadku tak, že do rovnice pripojíte hodnotu y alebo x. Môžete 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(known_y;known_x;1)

    Prieseesené y:
    =INDEX(LINEST(known_y;known_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 LINEST a CURVE-fit dokáže vypočítať najlepšiu priamku alebo exponenciálnu krivku, ktorá vyhovuje údajom. Musíte sa však rozhodnúť, ktorý z týchto dvoch výsledkov najlepšie vyhovuje vašim údajom. Funkcia TREND(known_y;known_x) môžete vypočítať pre priamku alebo GROWTH(known_y, known_xje) pre exponenciálnu krivku. Tieto funkcie vrátia bez new_x argumentu hodnoty y predpokladané pozdĺž tejto čiary alebo krivky na aktuálnych údajových bodoch. Potom môžete porovnávať predpokladané hodnoty s aktuálnymi hodnotami. Možno ich budete chcieť vytvoriť v grafe na vizuálne porovnanie.

  • V regresnej analýze Excel vypočíta pre každý bod rozdiel k štvorcovi medzi hodnotou y odhadom pre tento bod a jeho skutočnou hodnotou y. Súčet týchto druhých mocníz rozdielov sa nazýva reziduálny súčet štvorcov, ssresid. Excel vypočíta celkový súčet štvorcov, ssstotal. Ak argument básn = TRUE alebo je vynechaný, celkový súčet druhých mocnín je súčet druhých mocnín rozdielov medzi skutočnými hodnotami y a priemerom hodnôt y. Ak argument const = FALSE, celkový súčet štvorcov je súčet druhých mocnín skutočných hodnôt y (bez odčítania priemernej hodnoty y od každej jednotlivej hodnoty y). Potom možno nájsť regresný súčet štvorcov, ssreg, z: ssreg = sstotal - ssresid. Čím je reziduálny súčet štvorcov menší, v porovnaní s celkovým súčtom štvorcov, tým väčšia je hodnota určujúceho koeficientur2,čo je indikátor toho, ako dobre rovnica vyplývajúca z regresnej analýzy vysvetľuje vzťah medzi premennými. Hodnota r2 sa rovná ssreg/sstotal.

  • V niektorých prípadoch jeden alebo viacero stĺpcov X (predpokladá sa, že Y a X sú v stĺpcoch) nemusia mať v prítomnosti iných stĺpcov X ďalšiu prediktívnu hodnotu. Inými slovami, odstránenie jedného alebo viacerých stĺpcov X môže viesť k predpovedať hodnoty Y, ktoré sú rovnako presné. V tomto prípade by sa tieto nadbytočné stĺpce X mali vynechať z regresného modelu. Táto priepustnosť sa nazýva "kolinearita", pretože každý nadbytočný stĺpec X možno vyjadriť ako súčet násobkov ne redundantných stĺpcov X. Funkcia LINEST kontroluje kolinearitu a odstráni nadbytočné stĺpce X z regresného modelu, keď ich identifikuje. Odstránené stĺpce X je možné vo výstupe LINEST rozpoznať ako hodnoty s 0 koeficientmi okrem 0 se. Ak sa jeden alebo viaceré stĺpce odstránia ako nadbytočné, df bude ovplyvnené, pretože df závisí od počtu stĺpcov X použitých na predvídateľné účely. Podrobnosti o výpočte df nájdete v príklade 4. Pri zmene df z dôvodu nadbytočných stĺpcov X sa tiež odstránia hodnoty sey a F. Kolinearita by v praxi mala byť relatívne zriedkavá. Ak však niektoré stĺpce X obsahujú iba hodnoty 0 a 1 ako ukazovatele toho, či predmet experimentu je alebo nie je členom konkrétnej skupiny, je to prípad, v ktorom je pravdepodobnejšie, že ide o jeden prípad. Ak argument const = TRUE alebo sa vynechá, funkcia LINEST efektívne vloží ďalší stĺpec X všetkých 1 hodnôt na modelovanie prieseného úseku. Ak máte stĺpec s číslom 1 pre každý predmet, ak muž, alebo 0, ak nie, a máte tiež stĺpec s číslom 1 pre každú tému, ak žena, alebo 0, ak nie, tento druhý stĺpec je nadbytočný, pretože jej príspevky možno získať z odčítania záznamu v stĺpci "Mužský indikátor" od položky v druhom stĺpci všetkých 1 hodnôt 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.

  • 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 SLOPE a INTERCEPT vrátia #DIV/0! . Algoritmus funkcií SLOPE a INTERCEPT je navrhnutý tak, aby hľadať iba jednu odpoveď, a v tomto prípade môže odpovedať aj viac než jedna odpoveď.

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

2310

2

2

20

142 000 EUR

2333

2

2

12

144 000 EUR

2356

3

1,5

33

151 000 EUR

2379

3

2

43

150 000 EUR

2402

2

3

53

139 000 EUR

2425

4

2

23

169 000 EUR

2448

2

1,5

99

126 000 EUR

2471

2

2

34

142 900 EUR

2494

3

3

23

163 000 EUR

2517

4

4

55

169 000 EUR

2540

2

3

22

149 000 EUR

-234,2371645

13,26801148

0,996747993

459,7536742

1732393319

Vzorec (vzorec dynamického poľa zadaný v poli A19)

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

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

V predchádzajúcom príklade sa na určenie koeficientur2používa hodnota 0,99675 (pozrite bunku A17 vo výstupe pre LINEST), čo môže naznačovať silnú vzťah 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.

Hodnoty F a df na výstup z funkcie LINEST možno použiť na vyhodnotenie pravdepodobného výskytu vyššej hodnoty F. F je možné porovnať s kritickými hodnotami vo publikovaných F-distribučných tabuľkách alebo pomocou funkcie FDIST v Excel možno použiť na výpočet pravdepodobnosti výskytu väčšej hodnoty F v náhodnej tabuľke. Príslušné rozdelenie F má počet stupňov voľnosti v1 a v2. Ak n je počet údajových bodov a argument const = TRUE alebo vynechaný údaj, potom v1 = n – df – 1 a v2 = df. (Ak argument básn = FALSE, potom v1 = n – df a v2 = df.) Funkcia FDIST s syntaxou FDIST(F;v1;v2) vráti pravdepodobnosť, že sa bude nachádzať vyššia hodnota F, ktorá sa bude vyskytol byť náhodná. V tomto príklade df = 6 (bunka B18) a F = 459,753674 (bunka A18).

Ak má alfa hodnotu 0,05, v1 = 11 – 6 – 1 = 4 a v2 = 6, kritická úroveň F je 4,53. Keďže hodnota F = 459,753674 je oveľa vyššia ako 4,53, je veľmi nepravdepodobná, že by sa táto hodnota F vyskytla náhodou. (Pri argumente Alfa = 0,05 sa hypotéza, že medzi known_y a known_x a known_x sa nemá odmietnuť, ak F prekročí kritickú úroveň (4,53). Funkciu FDIST môžete použiť pri Excel na získanie pravdepodobnosti, že sa táto vysoká hodnota F vyskytla manuálne. Napríklad FDIST(459,753674; 4; 6) = 1,37E-7, mimoriadne malá pravdepodobnosť. Môžete ju ukončiť vyhľadaním kritickej úrovne F v tabuľke alebo použitím funkcie FDIST, že regresná rovnica je užitočná pri predpovedaní hodnoty úradných budov v tejto oblasti. Nezabudnite, že použitie správnych hodnôt v1 a v2, ktoré boli vypočítané v predchádzajúcom odseku, je veľmi dôležité.

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.

Potrebujete ďalšiu pomoc?

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