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, pričom 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štantná hodnota. Všimnite si, že y, x a m môžu byť vektormi. Pole, ktoré funkcia LINEST vracia, je {MN, MN-1,..., M1, b}. Funkcia 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 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

Koeficient stanovenia. Porovná odhadované a skutočné hodnoty y a rozsahy v hodnote od 0 do 1. Ak je hodnota 1, v ukážke sa nachádza perfektná korelácia – neexistuje rozdiel medzi odhadovanou hodnotou y a skutočnou hodnotou y. Ak je koeficient stanovenia 0, regresná rovnica nie je pri predpovedaní hodnoty y užitočná. Informácie o tom, ako sa vypočítava R2, nájdete v téme "poznámky" ďalej v tejto téme.

sey

Štandardná chyba odhadu y.

o

Štatistická hodnota F alebo F-pozorovaná hodnota. Ak chcete zistiť, či sa sledovaný vzťah medzi závislými a nezávislými premennými vyskytuje náhodou, použite štatistiku F.

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 rovnej čiary je y = mx + b. Keď budete poznať hodnoty m a b, môžete vypočítať ľubovoľné miesto na priamke tak, že do tejto rovnice zapojíte hodnotu y alebo x. Môžete tiež použiť 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 čiarových a zakrivených funkcií LINEST a LOGEST dokážu vypočítať najlepšiu rovnú čiaru alebo exponenciálnu krivku, ktorá vyhovuje vašim údajom. Musíte sa však rozhodnúť, ktoré z týchto dvoch výsledkov najlepšie zodpovedá vašim údajom. Môžete vypočítať trend (známe_hodnoty_y; známe_hodnoty_x) pre rovnú čiaru alebo rast (známe_hodnoty_y; známe_hodnoty_x) pre exponenciálnu krivku. Tieto funkcie, bez argumentu new_x's , vrátia pole s hodnotami y predpokladané pozdĺž tejto čiary alebo krivky na skutočné údajové body. Potom môžete porovnať predpovedané hodnoty so skutočnými hodnotami. Možno budete chcieť zmapovať obidva vizuálne porovnanie.

  • Pri regresnej analýze vypočíta Excel pre každý bod štvorcový rozdiel medzi hodnotou y odhadnutou pre daný bod a skutočnou hodnotou y. Súčet týchto štvorcových rozdielov sa nazýva reziduálny súčet štvorcov, ssresid. Excel potom vypočíta celkový súčet štvorcov, sstotal. Keď argument CONST = True alebo je vynechaný, celkový súčet druhých mocnín je súčet štvorcových rozdielov medzi skutočnými hodnotami y a priemerom hodnôt y. Keď argument CONST = false, celkový súčet druhých mocnín je súčet druhých mocnín skutočných hodnôt y (bez odčítania priemernej hodnoty y z každej jednotlivej hodnoty y). Potom regresný súčet štvorcov, ssreg, možno nájsť od: ssreg = sstotal-ssresid. Čím menší je reziduálny súčet štvorcov v porovnaní s celkovým súčtom štvorcov, tým je väčšia hodnota koeficientu stanovenia, R2, čo je ukazovateľ toho, ako dobre sa rovnica vyplývajúca z regresnej analýzy vysvetľuje vzťah medzi premenné. Hodnota R2 sa rovná ssreg/sstotal.

  • V niektorých prípadoch môže mať jeden alebo viacero stĺpcov X (Predpokladajme, že hodnoty Y a X sú v stĺpcoch) nemusia mať v prítomnosti ostatných X stĺpcov žiadnu ďalšiu prediktívnu hodnotu. Inými slovami, odstránenie jedného alebo viacerých stĺpcov X môže viesť k predpokladaným hodnotám Y, ktoré sú rovnako presné. V takom prípade by sa mali tieto nadbytočné stĺpce X vynechať z regresného modelu. Tento jav sa nazýva "kolinearity", pretože akýkoľvek nadbytočný stĺpec X môže byť vyjadrený ako súčet násobkov nenadbytočných stĺpcov X. Funkcia LINEST kontroluje kolinearity a odstráni všetky nadbytočné stĺpce X z regresného modelu, keď ich identifikuje. Odstránené stĺpce X možno rozpoznať vo výstupe funkcie LINEST , pretože majú 0 koeficienty spolu s hodnotami 0 SE. Ak sa jeden alebo viaceré stĺpce odstránia ako nadbytočné, DF sa ovplyvní, pretože DF závisí od počtu stĺpcov X, ktoré sa skutočne použili na účely predpovedania. Podrobnosti o výpočte df nájdete v téme Príklad 4. Ak sa zmení DF, pretože sa odstránia nadbytočné stĺpce X, ovplyvnia sa aj hodnoty sey a F. Kolinearity by mali byť v praxi relatívne zriedkavé. Avšak v jednom prípade, keď je pravdepodobnejšie, že niektoré stĺpce X obsahujú len hodnoty 0 a 1, ako ukazovatele toho, či je predmet v experimente alebo nie je členom konkrétnej skupiny. Ak je argument CONST = True alebo je vynechaný, funkcia LINEST efektívne vloží ďalší stĺpec X všetkých 1 hodnôt na modelovanie priesečníka. Ak máte stĺpec s písmenom 1 pre každý predmet, ak je muž alebo 0, ak nie, a máte aj stĺpec s 1 pre každý predmet, ak je žena alebo 0, ak nie, tento posledný stĺpec je nadbytočný, pretože položky v ňom možno získať odčítaním položky v "mužskom indikátore". stĺpec z položky v ďalšom 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.

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

    Poznámka: V Excel pre web 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ď.

    • Svah a INTERCEPT vrátia #DIV/0! Ak je zadané umiestnenie pred prvou alebo za poslednou položkou v poli, výsledkom vzorca bude chybová hodnota #ODKAZ!. Algoritmus funkcií zjazdovky a intercept je navrhnutý tak, aby hľadal iba jednu odpoveď a v tomto prípade môže existovať viac ako 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)

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.

Hodnoty F a DF vo výstupe z funkcie LINEST sa môžu použiť na vyhodnotenie pravdepodobnosti vyššej hodnoty f, ktorá sa vyskytuje náhodou. F možno porovnávať s kritickými hodnotami v publikovaných tabuľkách F-rozdelenia alebo funkcia funkcia FDIST v Exceli sa môže použiť na výpočet pravdepodobnosti väčších hodnôt F, ktoré sa vyskytnú náhodou. Príslušné rozdelenie F je stupne voľnosti v1 a v2. Ak n je počet údajových bodov a CONST = TRUE alebo vynechaný, potom v1 = n – DF – 1 a v2 = DF. (Ak je CONST = FALSe, potom v1 = n – DF a v2 = DF.) Funkcia funkcia FDIST – so syntaxou funkcia FDIST(F; V1; V2) – vráti pravdepodobnosť vyššej hodnoty F, ktorá sa vyskytne náhodou. V tomto príklade DF = 6 (bunka B18) a F = 459,753674 (bunka a18).

Za predpokladu, že alfa hodnota 0,05, v1 = 11 – 6 – 1 = 4 a v2 = 6, kritická úroveň F je 4,53. Vzhľadom na to, že F = 459,753674 je oveľa vyššia ako 4,53, je veľmi nepravdepodobné, že hodnota F sa táto vysoká náhodou prejavila. (S alfa = 0,05 sa hypotéza, že neexistuje vzťah medzi známe_hodnoty_y a známe_hodnoty_x , musí byť odmietnutý, keď F prekročí kritickú úroveň, 4,53.) Môžete použiť funkciu funkcia FDIST v Exceli, aby ste získali pravdepodobnosť, že hodnota F, ktorú vyskytla Táto vysoká šanca. Napríklad funkcia FDIST(459,753674; 4; 6) = 1.37 e-7, veľmi malá pravdepodobnosť. Môžete uzavrieť buď tým, že vyhľadáte kritickú úroveň F v tabuľke alebo použijete funkciu funkcia FDIST , že regresná rovnica je užitočná pri predpovedaní vyhodnotenej hodnoty kancelárskych budov v tejto oblasti. Nezabudnite, že je 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.

Poznámka:  Táto stránka bola preložená automaticky a môže obsahovať gramatické chyby alebo nepresnosti. Naším cieľom je, aby bol tento obsah pre vás užitočný. Môžete nám dať vedieť, či boli tieto informácie pre vás užitočné? Tu nájdete anglický článok ako referenciu.

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

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.

×