Statistické funkce aplikace Excel: LINTREND

Tento článek popisuje funkci LINTREND v aplikaci Excel 2003 a vyšších verzích aplikace Excel, ilustruje způsob jejího využití a porovnává výsledky této funkce v aplikaci Excel 2003 a vyšších verzích aplikace Excel s výsledky funkce LINTREND v nižších verzích aplikace Excel.

Funkce LINTREND je vyhodnocována voláním související funkce LINREGRESE. V tomto článku se seznámíme s rozsáhlými změnami funkce LINREGRESE v aplikaci Excel 2003 a vyšších verzích aplikace Excel a s jejich implikacemi pro funkci LINTREND.

Informace o aplikaci Microsoft Excel 2004 pro systém Macintosh

Statistické funkce byly v aplikaci Microsoft Excel 2004 pro systém Macintosh aktualizovány za použití stejných algoritmů jako v aplikaci Excel 2003 a vyšších verzích aplikace Excel. Jakékoli informace uvedené v tomto článku, které popisují fungování nebo změnu některé funkce v aplikaci Excel 2003 nebo ve vyšších verzích aplikace Excel, se rovněž týkají aplikace Excel 2004 pro systém Macintosh.

Funkce LINTREND(pole_y;pole_x;nová_x;b) slouží k provádění lineární regrese. Používá se kritérium nejmenších čtverců a funkce LINTREND se pokouší o nalezení nejvhodnějšího řešení v rámci tohoto kritéria. Argument pole_y představuje data závislé proměnné a argument pole_x představuje data jedné či více nezávislých proměnných. Informace o vzácných případech, kdy může být druhý nebo třetí argument vynechán, naleznete v souboru nápovědy k funkci LINTREND.

Je-li poslední argument b nastaven na hodnotu PRAVDA, znamená to, že chcete, aby regresní model zahrnoval koeficient pro průnik. Pokud je poslední argument nastaven na hodnotu NEPRAVDA, nebude koeficient pro průnik zahrnut v modelu a přizpůsobená regrese bude postupovat podle počátku. Poslední argument je nepovinný a v případě vynechání je interpretován jako hodnota PRAVDA.

V zájmu usnadnění výkladu budeme ve zbytku tohoto článku předpokládat, že data jsou uspořádána ve sloupcích, takže argument pole_y představuje sloupec dat y a argument pole_x se skládá z jednoho nebo více sloupců dat x. Rozměry (délky) jednotlivých sloupců musí být samozřejmě stejné. Předpokládáme rovněž, že hodnoty argumentu nová_x jsou také uspořádány ve sloupcích, přičemž argument nová_x musí tvořit stejný počet sloupců jako argument pole_x. Následující informace jsou platné také v případě, že data nejsou uspořádána do sloupců, ale toto rozvržení se snáze popisuje a je také nejčastěji využívané.

Po výpočtu nejvhodnějšího regresního modelu (v podstatě voláním funkce LINREGRESE aplikace Excel) vrátí funkce LINTREND předpovídané hodnoty přiřazené k hodnotám nová_x.

Příklady v tomto článku ilustrují vztah funkcí LINTREND a LINREGRESE a upozorňují na problémy s funkcí LINREGRESE v aplikaci Microsoft Excel 2002 a v nižších verzích aplikace Excel. Tyto problémy způsobovaly problémy s funkcí LINTREND. Zatímco kód funkce LINTREND zůstává v aplikaci Excel 2003 a vyšších verzích aplikace Excel nezměněn, v kódu funkce LINREGRESE byly provedeny rozsáhlé změny a vylepšení.

Funkce LINTREND volá a spustí funkci LINREGRESE, použije regresní koeficienty z výstupu funkce LINREGRESE pro výpočet předpovídaných hodnot y přiřazených k jednotlivým řádkům hodnot nová_x a prezentuje tento sloupec předpovídaných hodnot y uživateli. Z tohoto důvodu je důležité vědět o problémech při provádění funkce LINREGRESE.

Jako doplněk k tomuto článku důrazně doporučujeme také článek o funkci LINREGRESE. Ten obsahuje několik příkladů a dokumentuje problémy s funkcí LINREGRESE v aplikaci Excel 2002 a nižších verzích aplikace Excel.

Další informace naleznete v následujícím článku znalostní báze Microsoft Knowledge Base:

Vzhledem k tomu, že se tento článek zaměřuje na numerické problémy v aplikaci Excel 2002 a nižších verzích aplikace Excel, není v něm zahrnuto mnoho praktických příkladů funkce LINTREND. Užitečné příklady naleznete v souboru nápovědy k funkci LINTREND.

Syntaxe

Argumenty pole_y, pole_x a nová_x musí mít formu matice nebo rozsahu buněk s odpovídajícími rozměry. Jestliže argument pole_y tvoří jeden sloupec krát m řádků, argument pole_x by mělo tvořit c sloupců krát m řádků, přičemž c je větší než nebo rovno jedné. C je počet prognostických proměnných, m je počet datových bodů. Argument nová_x musí tvořit c sloupců krát r řádků, přičemž r je větší než nebo rovno jedné. (Podobné vztahy týkající se rozměrů musí platit, pokud jsou data namísto do sloupců rozvržena do řádků.) B je logický argument, jenž musí být nastaven na hodnotu PRAVDA nebo NEPRAVDA (případně 0 nebo 1, což aplikace Excel interpretuje jako hodnoty NEPRAVDA (0) nebo PRAVDA (1)). Poslední tři argumenty funkce LINTREND jsou nepovinné. Informace o možnostech vynechání druhého nebo třetího argumentu (nebo obou) naleznete v souboru nápovědy k funkci LINTREND. Pokud vynecháte čtvrtý argument, bude interpretován jako PRAVDA.

V nejobvyklejším modelu použití funkce LINTREND se používají dva rozsahy buněk obsahujících data, například LINTREND(A1:A100;B1:F100;B101:F108;PRAVDA). Protože se obvykle používá více než jedna prognostická proměnná, obsahuje druhý argument v tomto příkladu více sloupců. V tomto příkladu se používá 100 subjektů, jedna hodnota závislé proměnné (pole_y) pro každý subjekt a pět hodnot závislých proměnných (pole_x) pro každý subjekt. Je v něm osm dalších hypotetických subjektů, pro které chcete pomocí funkce LINTREND vypočítat předpovídané hodnoty y.

Příklad použití

Pomocí ukázkového listu aplikace Excel si osvětlíme následující klíčové koncepty:

• způsob interakce funkce LINTREND s funkcí LINREGRESE,
• problémy způsobované kolineárními argumenty pole_x u funkce LINTREND (nebo LINREGRESE) v aplikaci Excel 2002 a nižších verzích aplikace Excel.

Rozsáhlé vysvětlení týkající se druhého z těchto bodů v kontextu funkce LINREGRESE naleznete v článku věnovaném této funkci.

Problém s kolinearitou u funkce LINTREND si můžeme ilustrovat následovně: Vytvořte prázdný list aplikace Excel, zkopírujte následující tabulku a potom v listu aplikace Excel vyberte buňku A1. Zkopírované položky vložte tak, aby vyplnily v listu buňky A1:K35. Po vložení této tabulky do nového listu aplikace Excel klepněte na položku Možnosti vložení a potom na příkaz Přizpůsobit formátování cíli. V době, kdy je stále vybraný vložený rozsah buněk, použijte některý z následujících postupů odpovídající používané verzi aplikace Excel:

• V aplikaci Microsoft Office Excel 2007 klepněte na kartu Domů, ve skupině Buňky klepněte na příkaz Formát a poté na příkaz Přizpůsobit šířku sloupců.
• V aplikaci Excel 2003 přejděte v nabídce Formát na příkaz Sloupec a potom klepněte na příkaz Přizpůsobit.

Data pro funkci LINTREND se nacházejí v buňkách A1:C8. (Položky v buňkách D2:D6 nejsou součástí dat, ale jsou použity pro ilustraci dále v tomto článku.) Výsledky dvou různých modelů funkce LINTREND se pro nižší resp. vyšší verze aplikace Excel nachází v buňkách E10:E16 resp. I10:I16. Výsledky v buňkách A10:A16 budou odpovídat verzi aplikace Excel, kterou používáte. Pro tuto chvíli se v tomto článku zaměříme na výsledky v aplikaci Excel 2003 a vyšších verzích aplikace Excel a vysvětlíme si, jak funkce LINTREND volá funkci LINREGRESE a jak používá její výsledky.

Interakci funkcí LINTREND a LINREGRESE si můžeme představit následovně:

1. Uživatel volá funkci LINTREND(pole_y;pole_x;nová_x;b).
2. Funkce LINTREND volá funkci LINREGRESE(pole_y;pole_x;b;PRAVDA).
3. Jako výsledek tohoto volání funkce LINREGRESE budou získány regresní koeficienty. Tyto koeficienty jsou zobrazeny v prvním řádku výstupní tabulky funkce LINREGRESE.
4. Pro každý řádek argumentu nová_x budou na základě koeficientů funkce LINREGRESE a hodnot argumentu nová_x v daném řádku vypočítány předpovídané hodnoty y.
5. Hodnoty vypočítané v kroku 4 budou ve výstupu funkce LINTREND vráceny v příslušných buňkách odpovídajících daným řádkům argumentu nová_x.

Má-li funkce LINTREND vrátit správné výsledky, musí funkce LINREGRESE v kroku 3 generovat správné výsledky. Problémy zde nastávají s kolineárními sloupci s prognostickými proměnnými.

Sloupce s prognostickými proměnnými (pole_x) jsou kolineární v případě, že alespoň jeden sloupec (c) může být vyjádřen jako součet násobků jiných (c1, c2 a další sloupce). Sloupec c je pak často označován jako redundantní, protože informace v něm obsažené lze zkonstruovat z jiných sloupců (c1, c2 a další). Základním principem při výskytu kolinearity je to, že výsledky by neměly být zahrnutím nebo vynecháním redundantního sloupce ve zdrojových datech ovlivněny. Vzhledem k tomu, že funkce LINREGRESE v aplikaci Excel 2002 a nižších verzích aplikace Excel výskyt kolinearity nekontrolovala, mohl být tento princip snadno porušen. Sloupce s prognostickými proměnnými jsou téměř kolineární v případě, že alespoň jeden sloupec (c) může být vyjádřen jako téměř rovný součtu násobků jiných (c1, c2 a další). Výraz „téměř rovný“ v tomto případě znamená velmi malý součet kvadratických odchylek položek ve sloupci c od odpovídajících položek ve vážených součtech sloupců c1, c2 a dalších. Výraz „velmi malý“ by mohl znamenat například hodnotu menší než 10^(-12).

V prvním modelu v řádcích 10 až 12 jsou sloupce B a C použity jako prognostické proměnné a požadujeme, aby aplikace Excel vymodelovala konstantu b (poslední argument je nastaven na hodnotu PRAVDA). Aplikace Excel následně v podstatě vloží další sloupec s prognostickými proměnnými, který bude podobný buňkám D2:D6. Jak vidíme, hodnoty ve sloupci C v řádcích 2 až 6 jsou přesně rovny součtům odpovídajících položek ve sloupcích B a D. Dochází tedy ke kolinearitě, protože sloupec C je součtem násobků:

• sloupce B,
• doplňkového sloupce aplikace Excel s hodnotami 1 vloženého proto, že třetí argument funkce LINREGRESE (který je stejný jako poslední argument funkce LINTREND) byl vynechán nebo nastaven na hodnotu PRAVDA („normální“ případ).

To způsobuje numerické problémy, kvůli kterým aplikace Excel 2002 a nižší verze aplikace Excel nedokážou vypočítat výsledky a výstupní tabulka funkce LINTREND je plná chybových hodnot #NUM!.

Druhý model v řádcích 14 až 16 dokáže úspěšně zpracovat libovolná verze aplikace Excel. V tomto modelu ke kolinearitě nedochází a uživatel může opět vyžadovat, aby aplikace Excel vymodelovala konstantu b. Tento model je zde zahrnut ze dvou důvodů.

Za prvé se jedná o zřejmě nejtypičtější model využívaný v praxi: ke kolinearitě nedochází. Tyto případy jsou správně zpracovávány ve všech verzích aplikace Excel. Uklidňující by měl být fakt, že pokud používáte nižší verzi aplikace Excel, pak v tomto nejobvyklejším praktickém případě k numerickým problémům pravděpodobně nedojde.

Za druhé tento příklad slouží k porovnání chování aplikace Excel 2003 a vyšších verzí aplikace Excel v těchto dvou modelech. Většina z nejpoužívanějších balíčků pro statistické výpočty kolinearitu analyzuje a v případě jejího výskytu odebere sloupec, který je součtem násobků jiných, z modelu a zobrazí výstrahu typu „Sloupec C je lineárně závislý na jiných sloupcích s prognostickými proměnnými a byl z analýzy vyloučen“.

V aplikaci Excel 2003 a vyšších verzích aplikace Excel se tyto informace nezobrazí jako zpráva nebo textový řetězec, ale lze je rozpoznat ve výstupní tabulce funkce LINREGRESE. Funkce LINTREND nemá žádný mechanismus, který by umožňoval podobnou zprávu zobrazit. Pokud ale ve výstupní tabulce funkce LINREGRESE naleznete regresní koeficient s hodnotou 0 a standardní chybou 0, odpovídá tato situace koeficientu sloupce odebraného z modelu. Výstupní tabulky funkce LINREGRESE jsou obsaženy v řádcích 23 až 35 a odpovídají příslušnému výstupu funkce LINTREND v řádcích 10 až 16. Hodnoty v buňkách I24:I25 ukazují na odebraný redundantní sloupec s prognostickými proměnnými. V tomto příkladu funkce LINREGRESE odebrala sloupec C (koeficienty v buňkách I24, J24 a K24 odpovídají – v uvedeném pořadí – sloupci C, sloupci B a sloupci s konstantou b aplikace Excel). Dojde-li ke kolinearitě, může být odebrán libovolný sloupec, který se na tomto stavu podílí.

Ve druhém modelu v řádcích 30 až 35 ke kolinearitě nedochází a žádný ze sloupců není odebrán. Vidíme, že předpovídané hodnoty y jsou v obou modelech shodné. Je tomu tak proto, že odebráním redundantního sloupce, který je součtem násobků jiných, nedochází ke snížení přesnosti výsledného modelu. Takové sloupce jsou odebírány právě proto, že při hledání nejvhodnějších výsledků metodou nejmenších čtverců nepředstavují žádnou přidanou hodnotu.

Při zkoumání výstupu funkce LINREGRESE v aplikaci Excel 2003 a vyšších verzích aplikace Excel v buňkách I23:K35 si také můžeme všimnout, že poslední tři řádky výstupní tabulky jsou shodné a shodují se také hodnoty v buňkách I31:J32 a J24:K25. To dokazuje, že pokud je sloupec C obsažen v modelu, ale rozpoznán jako redundantní (výstup v buňkách I24:K28), budou vráceny stejné výsledky jako v případě, že je sloupec C z modelu odebrán již před použitím funkce LINREGRESE (výstup v buňkách I31:J35). Tento fakt je v souladu se základním principem uplatňovaným při výskytu kolinearity.

V buňkách A18:C21 ilustrují v tomto článku data z aplikace Excel 2003 a vyšších verzí aplikace Excel způsob, jakým funkce LINTREND přebírá výstup funkce LINREGRESE a vypočítává relevantní předpovídané hodnoty y. Prozkoumáním vzorců v buňkách A20:A21 a C20:C21 můžete zjistit, jakým způsobem jsou koeficienty funkce LINREGRESE kombinovány s daty argumentu nová_x v buňkách B7:C8 pro oba popisované modely (jak při použití sloupců B a C jako prognostických proměnných, tak při použití pouze sloupce B jako prognostické proměnné).

Kolinearita je funkcí LINREGRESE v aplikaci Excel 2003 a vyšších verzích aplikace Excel identifikována díky použití zcela jiné metody řešení regresních koeficientů. Tato metoda se nazývá rozklad QR. Popis algoritmu rozkladu QR naleznete v článku věnovaném funkci LINREGRESE.

Shrnutí výsledků v nižších verzích aplikace Excel

Výsledky funkce LINTREND jsou v aplikaci Excel 2002 a nižších verzích aplikace Excel negativně ovlivňovány nepřesnými výsledky funkce LINREGRESE.

Funkce LINREGRESE byla počítána metodou, která nevěnovala pozornost problémům způsobovaným kolinearitou. Výskyt kolinearity způsoboval chyby zaokrouhlení, nesprávné hodnoty standardních chyb regresních koeficientů a jistý nepřípustný stupeň nahodilosti. V některých případech byly problémy se zaokrouhlením natolik závažné, že výstupní tabulka funkce LINREGRESE byla plná chybových hodnot #NUM!.

Funkce LINREGRESE obvykle poskytovala přijatelné výsledky, pokud si uživatelé byli jistí, že sloupce s prognostickými proměnnými nebyly kolineární (nebo téměř kolineární), což v praxi ve velké většině případů platilo. Podobnou jistotu přijatelných výsledků v případě, že sloupce s prognostickými proměnnými nebyly kolineární (nebo téměř kolineární), mohli mít tedy uživatelé i u funkce LINTREND.

Shrnutí výsledků v aplikaci Excel 2003 a vyšších verzích aplikace Excel

Mezi vylepšení funkce LINREGRESE patří přechod na metodu rozkladu QR při určování regresních koeficientů. Rozklad QR má následující výhody:

• vyšší numerická stabilita (obecně menší chyby zaokrouhlení),
• analýza problémů způsobovaných kolinearitou.

Všechny problémy vyskytující se v aplikaci Excel 2002 a nižších verzích aplikace Excel, které byly popsány v tomto článku, jsou v aplikaci Excel 2003 a vyšších verzích aplikace Excel opraveny.

Závěry

Došlo ke zvýšení přesnosti výsledků funkce LINTREND, protože v aplikaci Excel 2003 a vyšších verzích aplikace Excel je výrazně vylepšena funkce LINREGRESE. Pokud používáte nižší verzi aplikace Excel, doporučujeme před použitím funkce LINTREND ověřit, že sloupce s prognostickými proměnnými nejsou kolineární.

Velká část informací v tomto článku a v článku věnovaném funkci LINREGRESE může při zběžném přečtení v uživatelích aplikace Excel 2002 a nižších verzí aplikace Excel vzbuzovat pocit vážného problému. Je však třeba připomenout, že k problému s kolinearitou dochází pouze v malém procentu případů. Pokud nedochází k problému s kolinearitou, poskytuje funkce LINTREND v nižších verzích aplikace Excel přijatelné výsledky.

Vylepšení provedená u funkce LINREGRESE mají rovněž příznivý vliv na nástroj pro lineární regresi v doplňku Analytické nástroje (který volá funkci LINREGRESE) a dvě související funkce aplikace Excel: LOGLINREGRESE a LOGLINTREND.