LINEST
Funkce LINEST vrátí pole statistických údajů pro regresní přímku, která nejlépe aproximuje zadaná data a byla určena metodou nejmenších čtverců.
LINEST(známé-hodnoty-y; známé-hodnoty-x; nenulový-úsek-y; více-statistik)
známé-hodnoty-y: Sbírka obsahující známé hodnoty y. Údaj známé-hodnoty-Y musí obsahovat buď číselné hodnoty, nebo hodnoty datum/čas. Je-li k dispozici jen jedna sbírka známých hodnot x, může mít sbírka známé-hodnoty-y libovolnou velikost. Jestliže je k dispozici více sbírek známých hodnot x, může být sbírkou známé-hodnoty-y buď jeden sloupec, nebo jeden řádek obsahující hodnoty, avšak nikoli oba.
známé-hodnoty-x: Nepovinná sbírka obsahující známé hodnoty x. Údaj známé-hodnoty-X musí obsahovat buď číselné hodnoty, nebo hodnoty datum/čas. Není-li uvedena, předpokládá se, že jde o sbírku stejné velikosti jako známé-hodnoty-y začínající od čísla 1, například 1, 2, 3, jsou-li tři známé-hodnoty-y. Je-li k dispozici jen jedna množina známých hodnot x, musí mít sbírka známé-hodnoty-x, je-li uvedena, stejnou velikost jako sbírka známé-hodnoty-y. Jestliže je k dispozici více množin známých hodnot x, považuje se každý řádek či sloupec sbírky známé-hodnoty-x za jednu množinu a jeho velikost musí být stejná jako velikost řádku či sloupce sbírky známé-hodnoty-y.
nenulový-úsek-y: Nepovinná modální hodnota určující, jak má být vypočten posun ve směru osy y (konstanta b).
normálně (1, PRAVDA nebo neuvedeno): Hodnota posunu ve směru osy y (konstanta b) se vypočítá normálně.
vynutit hodnotu 0 (0, NEPRAVDA): Jako hodnota posunu ve směru osy y (konstanta b) se použije 0.
více-statistik: Nepovinná modální hodnota určující, zda má funkce vrátit doplňující statistické informace.
žádné dodatečné statistiky (0, NEPRAVDA nebo neuvedeno): Do výsledného pole nepřidávat doplňující regresní statistiky.
dodatečné statistiky (1, PRAVDA): Do výsledného pole přidat doplňující regresní statistiky.
Poznámky
Hodnoty vrácené touto funkcí jsou uloženy v poli. Jedním ze způsobů, jak číst hodnoty z pole, je funkce INDEX. Výsledek funkce LINEST lze zpracovat funkcí INDEX takto: =INDEX(LINEST(známé-hodnoty-y; známé-hodnoty-x; posun-na-ose-y; více-statistik); y; x), kde y a x představují sloupcový a řádkový index požadované hodnoty.
Pokud funkce nemá vracet dodatečné statistiky (parametr více-statistik má hodnotu NEPRAVDA), obsahuje výsledné pole jeden řádek. Počet jeho sloupců je roven počtu množin ve sbírce známé-hodnoty-x plus 1. Obsahuje sklony přímek (pro každý řádek či sloupec hodnot x jedna hodnota) v obráceném pořadí (první hodnota náleží poslednímu řádku či sloupci hodnot x) a po nich hodnotu konstanty b, posunutí ve směru osy y.
Pokud má funkce vracet dodatečné statistiky (parametr více-statistik má hodnotu PRAVDA), má výsledné pole pět řádků. Další informace o tomto poli najdete za příklady.
Příklady |
|---|
Máme následující tabulku obsahující známé-hodnoty-y (buňky A2:A6) a známé-hodnoty-x (buňky B2:B6): |
A | B | |
|---|---|---|
1 | Y | X |
2 | 0 | -1 |
3 | 8 | 10 |
4 | 9 | 12 |
5 | 4 | 5 |
6 | 1 | 3 |
Vzorec =INDEX(LINEST(A2:A6; B2:B6; 1; 0); 1) vrátí přibližně 0,752 707 581 227 437, je-li jako nenulový-úsek-y zvolen normální výpočet (1). Tato hodnota představuje sklon regresní přímky, neboť jsme požadovali první hodnotu z pole vráceného funkcí INDEX a uvedli jsme jen jednu množinu známé-hodnoty-x. Vzorec =INDEX(LINEST(A2:A6; B2:B6; 1; 0); 2) vrátí přibližně 0,034 296 028 880 864 6, což je konstanta b, průsečík regresní přímky s osou y. Tuto hodnotu jsme získali, neboť jsme požadovali druhou hodnotu z pole vráceného funkcí INDEX, která je právě druhou potřebnou hodnotou, jelikož jsme uvedli jen jednu množinu známé-hodnoty-x. |
Obsah pole dodatečných statistik
Funkce LINEST může do výsledného pole zahrnout doplňující statistické informace. Pro účely následující diskuse předpokládejme, že kromě známých hodnot y máme pět množin známých hodnot x. Dále předpokládejme, že známé-hodnoty-x jsou uloženy v pěti řádcích či sloupcích tabulky. Za těchto podmínek bude pole vrácené funkcí LINEST obsahovat následující hodnoty.
1 | 2 | 3 | 4 | 5 | 6 | |
|---|---|---|---|---|---|---|
1 | S5 | S4 | S3 | S2 | S1 | b |
2 | SE5 | SE4 | SE3 | SE2 | SE1 | SEb |
3 | 3 | SEy | ||||
4 | F | SV | ||||
5 | R1 | R2 |
Buňka na řádku 1 ve sloupci 1 obsahuje hodnotu S5 (sklon páté množiny známé-hodnoty-x) a řada pokračuje až po sloupec 5, který bude obsahovat hodnotu S1 (sklon první množiny známé-hodnoty-x). Všimněte si, že sklony pro jednotlivé množiny známé-hodnoty-x jsou vráceny v obráceném pořadí.
Poslední buňka v řádku 1 obsahuje hodnotu b, posun pro známé hodnoty x ve směru osy y. V našem příkladu je to řádek 1, sloupec 6.
Buňka na řádku 2 ve sloupci 1 obsahuje hodnotu SE5 (směrodatná odchylka koeficientu přidruženého k páté množině známé-hodnoty-x) a řada pokračuje až po sloupec 5, který bude obsahovat hodnotu SE1 (směrodatná odchylka koeficientu přidruženého k první množině známé-hodnoty-x). Hodnoty jsou vráceny v obráceném pořadí, tedy pro pět množin známých hodnot x je hodnota pro pátou množinu na prvním místě v poli. Jde tedy o stejné pořadí jako u hodnot sklonů.
Poslední buňka v řádku 2 obsahuje hodnotu SEb, směrodatnou odchylku přidruženou k hodnotě posunu ve směru osy y (b). V našem příkladu je to řádek 2, sloupec 6.
Řádek 3 ve sloupci 1 obsahuje hodnotu C, koeficient determinace. Tato statistika porovnává odhadované a skutečné hodnoty y. Pokud je roven 1, není mezi odhadovanou a skutečnou hodnotou y žádný rozdíl. Tento stav se nazývá dokonalá korelace. Je-li koeficient determinace roven 0, není mezi hodnotami žádná korelace a daná regresní rovnice není pro předpověď hodnoty y nijak užitečná.
Buňka na řádku 3 ve sloupci 2 obsahuje hodnotu SEy, což je směrodatná odchylka přidružená k odhadu hodnoty y.
Řádek 4 ve sloupci 1 obsahuje sledovanou hodnotu F. Sledovaná hodnota F pomáhá zjistit, zda je pozorovaný vztah mezi závislými a nezávislými proměnnými náhodný.
Řádek 4 ve sloupci 2 obsahuje hodnotu SV, počet stupňů volnosti. Pomocí statistiky počtu stupňů volnosti můžete určit hladinu spolehlivosti.
Buňka na řádku 5 ve sloupci 1 obsahuje hodnotu R1, což je součet čtverců regrese.
Buňka na řádku 5 ve sloupci 2 obsahuje hodnotu R2, což je součet čtverců zbytkových hodnot.
Ohledně pole dodatečných statistik je třeba mít na paměti následující skutečnosti:
Nezáleží na tom, zda jsou známé hodnoty x a y uloženy v řádcích či sloupcích. V obou případech je výsledné pole uspořádáno do řádků, jak ukazuje tabulka.
V příkladu se uvažovalo pět množin známých hodnot x. Kdyby těchto množin byl jiný počet než pět, změnil by se podle toho počet sloupců výsledného pole (ten je vždy roven počtu množin známých hodnot x plus 1), avšak počet řádků je vždy stejný.
Pokud v argumentech funkce LINEST není uveden požadavek na dodatečné statistiky, je výsledné pole rovno prvnímu řádku.