LINEST (funkcia)

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štantná hodnota. Všimnite si, že vektory môžu byť y, x a m. Pole, ktoré vráti funkcia LINEST , 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_y a zná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á argument štatistika hodnotu TRUE, funkcia LINEST vráti dodatočnú regresnú štatistiku. výsledkom je, že vrátené pole je {mn,mn-1,...,m1;b; sen,sen-1,...,se1,seb; r 2,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ú:

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

Poznámky

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

  Smernica (m):
  Ak chcete nájsť sklon čiary, často napísaný ako m, vziať dva body na trati, (x1,y1) a (x2,y2); smernica sa rovná (y2 - y1)/(x2 - x1).

  Priesečník y (b):
  Priesečník y čiary, často napísaný ako b, je hodnota y v bode, kde čiara pretína 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 zapojí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:

  Svah:
  =INDEX(LINEST(known_y;known_x);1)

  Priesečník 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:

  

  

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

• Funkcie LINEST a LOGEST dokážu vypočítať najlepšiu priamku alebo exponenciálnu krivku, ktorá vyhovuje vašim údajom. Musíte sa však rozhodnúť, ktorý z týchto dvoch výsledkov najlepšie vyhovuje vašim údajom. Môžete vypočítať trend (known_y,known_x) pre priamku alebo GROWTH(known_y, known_x) pre exponenciálnu krivku. Tieto funkcie bez argumentu new_x vrátia pole hodnôt y predpovedaných pozdĺž tejto čiary alebo krivky v skutočných údajových bodoch. Potom môžete porovnať predpovedané hodnoty so skutočnými hodnotami. Možno ich budete chcieť zobraziť v grafe pre vizuálne porovnanie.

• V regresnej analýze Excel vypočíta pre každý bod rozdiel medzi hodnotou y odhadovanou pre daný bod a jej skutočnou hodnotou y. Súčet týchto druhých rozdielov sa nazýva reziduálny súčet štvorcov, ssresid. Excel potom vypočíta celkový súčet štvorcov, sstotal. Ak argument konštanty = TRUE alebo je vynechaný, celkový súčet štvorcov je súčtom druhých rozdielov medzi skutočnými hodnotami y a priemerom hodnôt y. Keď argument konštanty = FALSE, celkový súčet štvorcov je súčtom 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ť z: 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 koeficientu určenia r², čo je indikátor toho, ako dobre rovnica vyplývajúca z regresnej analýzy vysvetľuje vzťah medzi premennými. Hodnota r² sa rovná ssreg/sstotal.

• V niektorých prípadoch jeden alebo viacero stĺpcov X (predpokladajme, že stĺpce Y a X sú v stĺpcoch) nemusia mať žiadnu ďalšiu prediktívnu hodnotu v prítomnosti ostatných stĺpcov X. Inými slovami, odstránenie jedného alebo viacerých stĺpcov X môže viesť k predpovedaným hodnotám Y, ktoré sú rovnako presné. V takom prípade by sa tieto nadbytočné stĺpce X mali vynechať z regresného modelu. Tento jav sa nazýva kolinearita, pretože akýkoľvek nadbytočný stĺpec X možno vyjadriť ako súčet násobkov stĺpcov x, ktoré nie sú nadbytočné. Funkcia LINEST skontroluje kolinearitu 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 LINEST ako s 0 koeficientmi okrem 0 se hodnôt. Ak sa niektoré stĺpce odstránia ako nadbytočné, bude to mať vplyv na hodnotu df, pretože df závisí od počtu stĺpcov X, ktoré sa skutočne používajú na prediktívne účely. Podrobnosti o výpočte df nájdete v príklade 4. Ak sa df zmení z dôvodu odstránenia nadbytočných stĺpcov X, ovplyvní to aj hodnoty sey a F. Kolinearita by mala byť v praxi relatívne zriedkavá. Jeden prípad, v ktorom je väčšia pravdepodobnosť vzniku, je, že niektoré stĺpce X obsahujú iba 0 a 1 hodnoty ako indikátory toho, či predmet experimentu je alebo nie je členom určitej skupiny. Ak 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 hodnotou 1 pre každý predmet v prípade samca alebo 0, ak nie, a tiež máte stĺpec s hodnotou 1 pre každý predmet, ak je samica, alebo 0, ak nie, tento druhý stĺpec je nadbytočný, pretože jeho položky možno získať odčítaním položky v stĺpci "male indicator" od 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.

• 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 SLOPE a INTERCEPT. 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! Ak je zadané umiestnenie pred prvou alebo za poslednou položkou v poli, výsledkom vzorca bude chybová hodnota #ODKAZ!. Algoritmus funkcií SLOPE 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