Statistické funkce aplikace Excel: LINTREND

Souhrn

Tento článek popisuje funkci LINTREND v aplikaci Microsoft Office Excel 2003 a vyšších verzích aplikace Excel, ilustruje způsob se používá funkce 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 dřívějších verzích aplikace Excel.

TREND je vyhodnocována voláním související funkce LINREGRESE. 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.

Microsoft Excel 2004 pro systém Macintosh informace

Statistické funkce v aplikaci Microsoft Excel 2004 pro systém Macintosh byly aktualizovány pomocí stejných algoritmů jako Excel 2003 a vyšších verzích aplikace Excel. Všechny informace v tomto článku, který popisuje, jak určitá funkce pracuje nebo jak byla upravena pro aplikaci Excel 2003 a vyšších verzích aplikace Excel platí také pro Excel 2004 pro systém Macintosh.

Další informace

Funkce LINTREND (pole_y, pole_x, nová_x, konstantu) slouží k provádění lineární regrese. Používá se kritérium nejmenších čtverců a TREND se pokouší o nalezení nejvhodnějšího řešení v rámci tohoto kritéria. Pole_y představují data na "závislé proměnné" a pole_x představuje data na jeden nebo více "nezávislých proměnných". Soubor nápovědy k funkci LINTREND pojednává o výjimečných případech, kdy může být druhý nebo třetí argument vynechán.

Poslední argument "konstanta" nastavena na hodnotu TRUE, chcete-li regresní model zahrnoval koeficient pro průnik v regresní model. Pokud je poslední argument nastaven na hodnotu FALSE, je žádný koeficient pro průnik zahrnut; vybavené regrese bude muset projít původ. Poslední argument je nepovinný; Pokud je tento argument vynechán, je interpretován jako PRAVDA.

Pro jednoduchost budeme ve zbytku tohoto článku předpokládejte, že data jsou uspořádána do sloupců, takže argument pole_y je sloupec dat y a pole_x je jeden nebo více sloupců dat x. Rozměry (délky) jednotlivých sloupců musí být samozřejmě stejné. Nová_x budou rovněž předpokládat, musí být uspořádány do sloupců a musí být stejný počet sloupců pro nová_x jako argument pole_x. Všechny poznámky v tomto článku jsou platné i v případě, že data nejsou uspořádána ve sloupcích, ale je pouze snazší diskutovat tento jediný případ (nejčastěji používaných).

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

V tomto článku používá příklady zobrazit jak LINTREND funkce LINREGRESE a upozorňují na problémy s funkcí LINREGRESE v aplikaci Microsoft Excel 2002 a dřívějších verzích aplikace Excel. Tyto problémy způsobovaly problémy s funkcí LINTREND. Zatímco kód funkce LINTREND nebyla přepsána pro aplikaci Excel 2003 a vyšších verzích aplikace Excel, byly provedeny rozsáhlé změny (a zlepšení), v kódu funkce LINREGRESE.

TREND efektivně volá funkci LINREGRESE, 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 pro vás. Proto musí vědět o problémech při provádění funkce LINREGRESE.

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

Další informace získáte kliknutím na následující číslo v článku databáze Microsoft Knowledge Base:

828533 popis funkce LINREGRESE v aplikaci Excel 2003 a Excel 2004 for Mac



Protože se zaměřuje na numerické problémy v aplikaci Excel 2002 a dřívějších verzích aplikace Excel, v tomto článku nemá mnoho praktických příkladů funkce LINTREND. Soubor nápovědy je TREND obsahuje užitečné příklady.

Syntaxe

TREND(known_y's, known_x's, new_x's, constant)
Argumenty pole_y, pole_x a nová_x musí být matice nebo rozsahu buněk s odpovídajícími rozměry. Pokud pole_y tvoří jeden sloupec krát m řádků, argument pole_x by c sloupců krát m řádků, přičemž c je větší než nebo rovno jedné. Všimněte si, že c je počet prognostických proměnných; m je počet datových bodů. Oblast nová_x musí tvořit c sloupců krát r řádků, kde jsou větší než nebo rovno jedné. (Podobné vztahy týkající se rozměrů musí platit, pokud údaje stanovené v řádcích namísto sloupce.) Konstanta je logický argument, který musí být nastavena na hodnotu TRUE nebo FALSE (nebo 0 nebo 1, který aplikace Excel interpretuje jako NEPRAVDA nebo PRAVDA, respektive). Poslední tři argumenty funkce LINTREND jsou nepovinné. naleznete v souboru nápovědy k funkci LINTREND možnostech vynechání druhého argumentu a třetí argument. Čtvrtý argument vynechání je interpretován jako PRAVDA.

Nejčastějším využitím TREND zahrnuje dva rozsahy buněk obsahujících data, například TREND (a1: A100, B1:F100, B101:F108, TRUE). Všimněte si, že 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 jsou 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. Existuje osm dalších hypotetických subjektů které chcete pomocí funkce LINTREND vypočítat předpovídané hodnoty y.

Příklad použití

Příklad sešitu aplikace Excel je k dispozici ke znázornění 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) pro aplikaci Excel 2002 a dřívější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 funkci LINREGRESE.

Pro ilustraci kolinearity TREND, vytvořte prázdný list aplikace Excel, zkopírujte následující tabulku, vyberte buňku A1 v listu aplikace Excel a potom vložit položky tak, aby vyplnil v následující tabulce A1:K35 buněk v listu.
y:x:
1121
2341
3451
4671
5781
Nová x:911
1214
Funkce LINTREND při použití sloupců B a C:předem v aplikaci Excel 2003 hodnoty:Hodnoty v aplikaci Excel 2003 a vyšších verzích aplikace Excel:
=TREND(A2:A6,B2:C6,B7:C8,TRUE)#NUM!6.15789473684211
=TREND(A2:A6,B2:C6,B7:C8,TRUE)#NUM!8.13157894736842
Funkce LINTREND při použití pouze sloupce B:
=TREND(A2:A6,B2:B6,B7:B8,TRUE)6.15789473684216.15789473684211
=TREND(A2:A6,B2:B6,B7:B8,TRUE)8.131578947368428.13157894736842
Transformované hodnoty v aplikaci Excel 2003 a vyšších verzích aplikace Excel LINREGRESE
Použití sloupců B a CPoužití sloupce B:
= K24*1 + J24*B7 + I24*C7=J31*1+I31*B7
=K24*1 + J24*B8 + I24*C8=J31*1 +I31*B8
Funkce LINREGRESE při použití sloupců B a C:předem v aplikaci Excel 2003 hodnoty:Hodnoty v aplikaci Excel 2003 a vyšších verzích aplikace Excel:
=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!00.6578947368421050.236842105263158
=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!00.0438596491228070.206652964726136
=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!0.9868421052631580.209426954145848#N/A
=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!2253#N/A
=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)=LINEST(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!9.868421052631580.131578947368421#N/A
Funkce LINREGRESE při použití pouze sloupce B:
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)0.6578947368421050.2368421052631590.6578947368421050.236842105263158
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)0.04385964912280710.2066529647261360.0438596491228070.206652964726136
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)0.9868421052631580.2094269541458480.9868421052631580.209426954145848
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)224.99999999999932253
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)9.868421052631580.1315789473684219.868421052631580.131578947368421
Po vložení této tabulky do nového listu aplikace Excel, klepněte na tlačítko
Možnosti vloženía potom klepněte na tlačítko Přizpůsobit formátování cíli. Stále vybrán vložený rozsah použijte jeden z následujících postupů odpovídající verzi aplikace Excel, kterou používáte:
  • V aplikaci Microsoft Office Excel 2007 a 2010 klepněte na kartu Domů , klepněte na tlačítko Formát ve skupině buňky a potom klepněte na tlačítko Přizpůsobit šířku sloupců.
  • V aplikaci Excel 2003 přejděte na
    Sloupce v nabídce Formát a potom klepněte na tlačítko
    Přizpůsobit.
Data pro funkce LINTREND jsou 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.) Dvou různých modelů pro oba v dřívějších verzích aplikace Excel a novějších verzích aplikace Excel výsledky funkce LINTREND jsou uvedeny v buňkách E10:E16 a I10:116 buněk v uvedeném pořadí. Výsledky v buňkách a10: a16 budou odpovídat verzi aplikace Excel, kterou používáte. Nyní tento článek se zaměřuje na výsledky v aplikaci Excel 2003 a vyšších verzích aplikace Excel při vyšetřování, jak funkce LINTREND volá funkci LINREGRESE a jak používá její výsledky.

Funkcí LINTREND a LINREGRESE lze zobrazit jako interakci takto:
  1. Volání funkce LINTREND (pole_y, pole_x, nová_x, konstantu).
  2. Funkce LINTREND volá funkci LINREGRESE (pole_y, pole_x, konstanta hodnotu TRUE).
  3. Regresní koeficienty z tohoto volání funkce LINREGRESE jsou získány; Tyto koeficienty jsou zobrazeny v prvním řádku výstupní tabulky funkce LINREGRESE je.
  4. Pro každý nová_x řádek předpovídané hodnoty y je vypočítáváno na koeficientů funkce LINREGRESE a hodnot argumentu nová_x v daném řádku.
  5. Hodnoty vypočítané v kroku 4 je vrácena pro výstupní tabulky funkce LINREGRESE řádek do příslušné buňky.
Je-li funkce LINTREND vrátit správné výsledky, potom funkce LINREGRESE měl generovat správné výsledky v kroku 3. Koeficienty jsou zobrazeny v sloupce s prognostickými proměnnými kolineární.

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ů ostatním, c1, c2 a dalších sloupců. Sloupec c je často označován jako redundantní, protože informace, které obsahuje lze zkonstruovat z sloupce c1, c2 a dalších sloupců. Základním principem při výskytu kolinearity je to, že výsledky by měly být ovlivněn zahrnutím nebo vynecháním redundantního sloupce z původní data. Vzhledem k tomu, že funkce LINREGRESE v aplikaci Excel 2002 a dřívějších verzích aplikace Excel není vyhledat ke kolinearitě, 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ů ostatním, c1, c2 a dalších sloupců. "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 c1, c2 a dalších sloupců. "velmi malý" může být menší než 10^(-12) např.

V prvním modelu v řádcích 10 až 12 používá sloupce B a C jako prognostické a aplikace Excel vymodelovala konstantu (poslední argument je nastaven na hodnotu TRUE). Aplikace Excel potom v podstatě vloží další s prognostickými proměnnými sloupec, který vypadá stejně jako buňkách D2: D6. Je snadné si všimněte si, že položky 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. Došlo tedy ke kolinearitě protože sloupec C je součtem násobků:
  • Sloupec B
  • Aplikace Excel další sloupec 1s, který je vložen, vzhledem k tomu, že třetí argument funkce LINREGRESE (který je stejný jako poslední argument funkce LINTREND) byl vynechán nebo PRAVDA ("normální" případ)
To způsobuje numerické problémy, že aplikace Excel 2002 a dřívějších verzích aplikace Excel nedokážou vypočítat výsledky a výstupní tabulka funkce LINTREND je plná #NUM!.

Druhý model v řádcích 14 až 16, je takový, který dokáže úspěšně zpracovat libovolná verze aplikace Excel. Je-li ke kolinearitě a znovu požádat o aplikace Excel vymodelovala konstantu b. Tento model je zde zahrnut ze dvou důvodů.

Nejprve je pravděpodobně nejtypičtější v praktické: ke kolinearitě nedochází. Tyto případy jsou správně zpracovávány ve všech verzích aplikace Excel. Je třeba vědět, že nejsou pravděpodobné dojít, pokud máte starší verzi aplikace Excel v nejobvyklejším praktickém případě numerické problémy reassuring.

Za druhé tento příklad slouží k porovnání chování aplikace Excel 2003 a vyšších verzích 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 odebere sloupec, který je součtem násobků jiných, z modelu a zobrazí výstrahu zprávu jako "sloupec C je lineárně závislý na jiných sloupcích s prognostickými proměnnými a byl odebrán z analýzy."

V aplikaci Excel 2003 a vyšších verzích aplikace Excel tato zpráva je dopravena v oznámení nebo textový řetězec, ale ve výstupní tabulce funkce LINREGRESE. Funkce LINTREND nemá žádný mechanismus pro takové zprávy doručíte. Ve výstupní tabulce funkce LINREGRESE regresní koeficient s hodnotou 0 a standardní chybou 0 odpovídá koeficientu sloupce odebraného z modelu. Výstupní tabulky funkce LINREGRESE jsou obsaženy v řádcích 23 až 35 odpovídá výstupu funkce LINTREND v řádcích 10 až 16. Položky v buňkách I24:I25 zobrazit sloupec odstraněny nadbytečné s prognostickými proměnnými. V tomto případě funkce LINREGRESE rozhodli jste se odebrat sloupec C (koeficienty v buňkách I24, J24, K24 odpovídají sloupci C, B a v aplikaci Excel konstantní sloupců, v uvedeném pořadí). Když ke kolinearitě, může být odebrán libovolný a je libovolný výběr.

Ve druhém modelu v řádcích 30 až 35 je ke kolinearitě a žádný ze sloupců není odebrán. Uvidíte, že předpovídané hodnoty y jsou v obou modelech shodné. K tomuto problému dochází, 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 představují žádné přidané při hledání nejlepší metodou nejmenších čtverců.

Také pokud je zkoumání výstupu funkce LINREGRESE v aplikaci Excel 2003 a vyšších verzích aplikace Excel v buňkách I23:K35, zjistíte, že poslední tři řádky výstupní tabulky jsou stejné a položky v buňkách I31:J32 a J24: K25. Tento příklad ukazuje, že jsou stejné výsledky získané při sloupec C obsažen v modelu ale rozpoznán jako redundantní (výstup v buňkách I24:K28) jako když byl sloupec C odstraněny před spuštěním funkce LINREGRESE (výstup v buňkách I31: J35). To vyhovuje základním principem při výskytu kolinearity.

V buňkách A18: C21 v tomto článku používá data pro aplikaci Excel 2003 a vyšších verzích aplikace Excel pro znázornění, jak 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 buněk, uvidíte, jak jsou koeficienty funkce LINREGRESE kombinovány s daty argumentu nová_x v buňkách B7: C8 pro každou ze dvou modelů (použití sloupců B a C jako prognostické; použití pouze sloupce B jako prognostické).

Kolinearita je funkcí LINREGRESE v aplikaci Excel 2003 a vyšších verzích aplikace Excel identifikována díky zcela odlišný přístup k řešení pro regresní koeficienty. Tato metoda se nazývá rozklad QR. V článku věnovaném funkci LINREGRESE popisuje návod algoritmu rozkladu QR malý příklad.

Shrnutí výsledků v dřívějších verzích aplikace Excel

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

Funkce LINREGRESE byla vypočtena 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 stupeň nahodilosti. Někdy zaokrouhlení problémy byly natolik závažné, že funkce LINREGRESE byla plná jeho výstupní tabulka #NUM!.

Pokud je ve velké většině případů v praxi, si můžete být jisti, že zde nejsou kolineární (nebo téměř kolineární) prognostickými, potom funkce LINREGRESE obvykle poskytovala přijatelné výsledky. Proto pokud používáte TREND, lze podobně podobnou jistotu přijatelných výsledků Pokud si nejste jisti, zda neobsahuje sloupce s prognostickými proměnnými kolineární (nebo téměř kolineární).

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

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 s Excel 2002 a dřívějších verzích aplikace Excel, které jsou popsány v tomto článku bylo opraveno pro aplikaci Excel 2003 a vyšších verzích aplikace Excel.

Závěry

Vzhledem k tomu, že je výrazně vylepšena funkce LINREGRESE v aplikaci Excel 2003 a vyšších verzích aplikace Excel byl vylepšen výkon TRENDU. Pokud používáte dřívější verzi aplikace Excel, ověřte, že sloupce s prognostickými proměnnými nejsou kolineární, před použitím funkce LINTREND.

Velká část materiálu, který je zobrazen v tomto článku a v článku věnovaném funkci LINREGRESE může při zběžném uživatelé aplikace Excel 2002 a dřívějších verzích aplikace Excel. Uvědomte si však, že k problému s kolinearitou je problém v malém procentu případů. Pokud ke kolinearitě dřívější verze aplikace Excel poskytuje přijatelné výsledky funkce LINTREND.

Naštěstí, vylepšení funkce LINREGRESE rovněž příznivý vliv na nástroj pro lineární regresní analýzy analytické nástroje (který volá funkci LINREGRESE) a dvě související funkce aplikace Excel: funkce LOGLINREGRESE a LOGLINTREND.


Klíčová slova: Vzorec Trend růstu funkce LINREGRESE, LOGLINREGRESE XL2003 XL2007 XL2010



Vlastnosti

ID článku: 828801 - Poslední kontrola: 16. 1. 2017 - Revize: 2

Váš názor